 |
| Author |
Message |
tmeier
|
 Posted: Thu Feb 23 12:19:13 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a long
time and want to move the code to
ADO... I get a clueless error "-2147467259 Automation error - Unspecified
Error" when I run this code. The code is derived from two "simple" tables
with related data.
When I run the code in Access it works fine but not from VB. I downloaded
and installed the latest MDAC but it didn't help.
When I do "Select query" without (inner)join on either table I get the right
result...
Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close) are
boolean fields... Does boolean datatype pose any kind of challenge in
constructing ADO queries?
Dim sSQL as string
sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
tbl_CashierLogInOut.Close = False
Connection...
Dim cnnStr As String, sPath as string
Dim cN As New ADODB.Connection
Dim RsAR As New ADODB.Recordset
'Set Connection String and connect to DB
sPath = "c:\Database\PS\MainDb.mdb"
cN.CursorLocation = adUseClient
cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
cN.Open cnnStr
RsAR.Open sSQL, cN ' Open Read only recordset
'''Code bombs out here...
please help
Thanks in advance
Visual Studio299
|
| |
|
| |
|
JT
|
| Posted: Thu Feb 23 12:19:13 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Access is a toy. You should be using SQL Server MSDE or Express Edition.
>I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
>long
> time and want to move the code to
> ADO... I get a clueless error "-2147467259 Automation error - Unspecified
> Error" when I run this code. The code is derived from two "simple" tables
> with related data.
>
> When I run the code in Access it works fine but not from VB. I downloaded
> and installed the latest MDAC but it didn't help.
>
> When I do "Select query" without (inner)join on either table I get the
> right
> result...
> Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close) are
> boolean fields... Does boolean datatype pose any kind of challenge in
> constructing ADO queries?
>
>
> Dim sSQL as string
>
> sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
> FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> tbl_CashierLogInOut.Close = False
>
> Connection...
> Dim cnnStr As String, sPath as string
> Dim cN As New ADODB.Connection
> Dim RsAR As New ADODB.Recordset
> 'Set Connection String and connect to DB
> sPath = "c:\Database\PS\MainDb.mdb"
> cN.CursorLocation = adUseClient
> cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> cN.Open cnnStr
> RsAR.Open sSQL, cN ' Open Read only recordset
> '''Code bombs out here...
> please help
>
>
> Thanks in advance
>
>
|
| |
|
| |
|
Dan
|
| Posted: Thu Feb 23 12:40:27 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Not sure about Access but in SQL Server True is 1 and False is 0. Try
Dim sSQL as string
sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = 1 AND
tbl_CashierLogInOut.Close = 0
Dan Reber
>I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
>long
> time and want to move the code to
> ADO... I get a clueless error "-2147467259 Automation error - Unspecified
> Error" when I run this code. The code is derived from two "simple" tables
> with related data.
>
> When I run the code in Access it works fine but not from VB. I downloaded
> and installed the latest MDAC but it didn't help.
>
> When I do "Select query" without (inner)join on either table I get the
> right
> result...
> Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close) are
> boolean fields... Does boolean datatype pose any kind of challenge in
> constructing ADO queries?
>
>
> Dim sSQL as string
>
> sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
> FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> tbl_CashierLogInOut.Close = False
>
> Connection...
> Dim cnnStr As String, sPath as string
> Dim cN As New ADODB.Connection
> Dim RsAR As New ADODB.Recordset
> 'Set Connection String and connect to DB
> sPath = "c:\Database\PS\MainDb.mdb"
> cN.CursorLocation = adUseClient
> cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> cN.Open cnnStr
> RsAR.Open sSQL, cN ' Open Read only recordset
> '''Code bombs out here...
> please help
>
>
> Thanks in advance
>
>
|
| |
|
| |
|
JP
|
| Posted: Thu Feb 23 12:40:41 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Thanks JT... Well that's why I am moving the code to ADO so I can use SQL
database etc.. But I am stuck right now... I am suspecting the real problem
is True/False datatype.
> Access is a toy. You should be using SQL Server MSDE or Express Edition.
>
> >I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
> >long
> > time and want to move the code to
> > ADO... I get a clueless error "-2147467259 Automation error -
Unspecified
> > Error" when I run this code. The code is derived from two "simple"
tables
> > with related data.
> >
> > When I run the code in Access it works fine but not from VB. I
downloaded
> > and installed the latest MDAC but it didn't help.
> >
> > When I do "Select query" without (inner)join on either table I get the
> > right
> > result...
> > Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close)
are
> > boolean fields... Does boolean datatype pose any kind of challenge in
> > constructing ADO queries?
> >
> >
> > Dim sSQL as string
> >
> > sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> > tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> > tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> > tbl_CashierLogInOut.Close, tbl_Employees.FirstName,
tbl_Employees.LastName
> > FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> > tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> > tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> > tbl_CashierLogInOut.Close = False
> >
> > Connection...
> > Dim cnnStr As String, sPath as string
> > Dim cN As New ADODB.Connection
> > Dim RsAR As New ADODB.Recordset
> > 'Set Connection String and connect to DB
> > sPath = "c:\Database\PS\MainDb.mdb"
> > cN.CursorLocation = adUseClient
> > cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> > cN.Open cnnStr
> > RsAR.Open sSQL, cN ' Open Read only recordset
> > '''Code bombs out here...
> > please help
> >
> >
> > Thanks in advance
> >
> >
>
>
|
| |
|
| |
|
JP
|
| Posted: Thu Feb 23 12:50:25 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Thanks Dan. I already tried 1 for True and 0 for false... but I didn't
work... Shouldn't ADO translate datatypes of different databases it is
connecting to? Well still stuck... Thanks
> Not sure about Access but in SQL Server True is 1 and False is 0. Try
>
> Dim sSQL as string
>
> sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
> FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = 1 AND
> tbl_CashierLogInOut.Close = 0
>
> Dan Reber
>
>
> >I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
> >long
> > time and want to move the code to
> > ADO... I get a clueless error "-2147467259 Automation error -
Unspecified
> > Error" when I run this code. The code is derived from two "simple"
tables
> > with related data.
> >
> > When I run the code in Access it works fine but not from VB. I
downloaded
> > and installed the latest MDAC but it didn't help.
> >
> > When I do "Select query" without (inner)join on either table I get the
> > right
> > result...
> > Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close)
are
> > boolean fields... Does boolean datatype pose any kind of challenge in
> > constructing ADO queries?
> >
> >
> > Dim sSQL as string
> >
> > sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> > tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> > tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> > tbl_CashierLogInOut.Close, tbl_Employees.FirstName,
tbl_Employees.LastName
> > FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> > tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> > tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> > tbl_CashierLogInOut.Close = False
> >
> > Connection...
> > Dim cnnStr As String, sPath as string
> > Dim cN As New ADODB.Connection
> > Dim RsAR As New ADODB.Recordset
> > 'Set Connection String and connect to DB
> > sPath = "c:\Database\PS\MainDb.mdb"
> > cN.CursorLocation = adUseClient
> > cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> > cN.Open cnnStr
> > RsAR.Open sSQL, cN ' Open Read only recordset
> > '''Code bombs out here...
> > please help
> >
> >
> > Thanks in advance
> >
> >
>
>
|
| |
|
| |
|
Douglas
|
| Posted: Thu Feb 23 13:42:32 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Hi JP:
I suspect the True/False issue as well.
Perhaps try this:
tbl_CashierLogInOut.Open <> 0
or...
tbl_CashierLogInOut.Open = 0
hth,
Doug.
> Thanks JT... Well that's why I am moving the code to ADO so I can use SQL
> database etc.. But I am stuck right now... I am suspecting the real problem
> is True/False datatype.
>
> > Access is a toy. You should be using SQL Server MSDE or Express Edition.
> >
> > >I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
> > >long
> > > time and want to move the code to
> > > ADO... I get a clueless error "-2147467259 Automation error -
> Unspecified
> > > Error" when I run this code. The code is derived from two "simple"
> tables
> > > with related data.
> > >
> > > When I run the code in Access it works fine but not from VB. I
> downloaded
> > > and installed the latest MDAC but it didn't help.
> > >
> > > When I do "Select query" without (inner)join on either table I get the
> > > right
> > > result...
> > > Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close)
> are
> > > boolean fields... Does boolean datatype pose any kind of challenge in
> > > constructing ADO queries?
> > >
> > >
> > > Dim sSQL as string
> > >
> > > sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> > > tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> > > tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> > > tbl_CashierLogInOut.Close, tbl_Employees.FirstName,
> tbl_Employees.LastName
> > > FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> > > tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> > > tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> > > tbl_CashierLogInOut.Close = False
> > >
> > > Connection...
> > > Dim cnnStr As String, sPath as string
> > > Dim cN As New ADODB.Connection
> > > Dim RsAR As New ADODB.Recordset
> > > 'Set Connection String and connect to DB
> > > sPath = "c:\Database\PS\MainDb.mdb"
> > > cN.CursorLocation = adUseClient
> > > cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> > > cN.Open cnnStr
> > > RsAR.Open sSQL, cN ' Open Read only recordset
> > > '''Code bombs out here...
> > > please help
> > >
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>
>
|
| |
|
| |
|
Norman
|
| Posted: Thu Feb 23 13:43:46 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Your code looks OK to me. It is most likely caused by your "SELECT...FROM
WHERE..." statement.
So, why don't you simplify your "SELECT..." stahement and add more detailed
version gradually to debug the code?
You can start with a simple
"SELECT * FROM tblMyTable"
Then join the other tables one by one, then add WHERE clause...
>I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
>long
> time and want to move the code to
> ADO... I get a clueless error "-2147467259 Automation error - Unspecified
> Error" when I run this code. The code is derived from two "simple" tables
> with related data.
>
> When I run the code in Access it works fine but not from VB. I downloaded
> and installed the latest MDAC but it didn't help.
>
> When I do "Select query" without (inner)join on either table I get the
> right
> result...
> Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close) are
> boolean fields... Does boolean datatype pose any kind of challenge in
> constructing ADO queries?
>
>
> Dim sSQL as string
>
> sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
> FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> tbl_CashierLogInOut.Close = False
>
> Connection...
> Dim cnnStr As String, sPath as string
> Dim cN As New ADODB.Connection
> Dim RsAR As New ADODB.Recordset
> 'Set Connection String and connect to DB
> sPath = "c:\Database\PS\MainDb.mdb"
> cN.CursorLocation = adUseClient
> cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> cN.Open cnnStr
> RsAR.Open sSQL, cN ' Open Read only recordset
> '''Code bombs out here...
> please help
>
>
> Thanks in advance
>
>
|
| |
|
| |
|
JP
|
| Posted: Thu Feb 23 14:23:31 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Thanks norman... I did simplify the select statement... Now I figure the SQL
works if I exclude "AND tbl_CashierLogInOut.Open = True AND
tbl_CashierLogInOut.Close = False" So I think the problem has something to
do with specifying criteria for two boolean fields. Pain!
> Your code looks OK to me. It is most likely caused by your "SELECT...FROM
> WHERE..." statement.
> So, why don't you simplify your "SELECT..." stahement and add more
detailed
> version gradually to debug the code?
>
> You can start with a simple
>
> "SELECT * FROM tblMyTable"
>
> Then join the other tables one by one, then add WHERE clause...
>
> >I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
> >long
> > time and want to move the code to
> > ADO... I get a clueless error "-2147467259 Automation error -
Unspecified
> > Error" when I run this code. The code is derived from two "simple"
tables
> > with related data.
> >
> > When I run the code in Access it works fine but not from VB. I
downloaded
> > and installed the latest MDAC but it didn't help.
> >
> > When I do "Select query" without (inner)join on either table I get the
> > right
> > result...
> > Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close)
are
> > boolean fields... Does boolean datatype pose any kind of challenge in
> > constructing ADO queries?
> >
> >
> > Dim sSQL as string
> >
> > sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> > tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> > tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> > tbl_CashierLogInOut.Close, tbl_Employees.FirstName,
tbl_Employees.LastName
> > FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> > tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> > tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> > tbl_CashierLogInOut.Close = False
> >
> > Connection...
> > Dim cnnStr As String, sPath as string
> > Dim cN As New ADODB.Connection
> > Dim RsAR As New ADODB.Recordset
> > 'Set Connection String and connect to DB
> > sPath = "c:\Database\PS\MainDb.mdb"
> > cN.CursorLocation = adUseClient
> > cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> > cN.Open cnnStr
> > RsAR.Open sSQL, cN ' Open Read only recordset
> > '''Code bombs out here...
> > please help
> >
> >
> > Thanks in advance
> >
> >
>
>
|
| |
|
| |
|
F_clef
|
| Posted: Thu Feb 23 14:55:26 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
JP
It's been awhile... but I remember (back in my Access days) getting a
similar error and it being attributed to the nulls in the boolean fields.
Access dbs allow nulls for these types of fields, but SQL Server does not.
Make sure those boolean fields are either TRUE/FALSE (1/0), and sorry I could
not find the web article to support the fix for this issue -- if it is the
same issue.
Hope that helps.
> Thanks norman... I did simplify the select statement... Now I figure the SQL
> works if I exclude "AND tbl_CashierLogInOut.Open = True AND
> tbl_CashierLogInOut.Close = False" So I think the problem has something to
> do with specifying criteria for two boolean fields. Pain!
>
> > Your code looks OK to me. It is most likely caused by your "SELECT...FROM
> > WHERE..." statement.
> > So, why don't you simplify your "SELECT..." stahement and add more
> detailed
> > version gradually to debug the code?
> >
> > You can start with a simple
> >
> > "SELECT * FROM tblMyTable"
> >
> > Then join the other tables one by one, then add WHERE clause...
> >
> > >I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
> > >long
> > > time and want to move the code to
> > > ADO... I get a clueless error "-2147467259 Automation error -
> Unspecified
> > > Error" when I run this code. The code is derived from two "simple"
> tables
> > > with related data.
> > >
> > > When I run the code in Access it works fine but not from VB. I
> downloaded
> > > and installed the latest MDAC but it didn't help.
> > >
> > > When I do "Select query" without (inner)join on either table I get the
> > > right
> > > result...
> > > Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close)
> are
> > > boolean fields... Does boolean datatype pose any kind of challenge in
> > > constructing ADO queries?
> > >
> > >
> > > Dim sSQL as string
> > >
> > > sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> > > tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> > > tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> > > tbl_CashierLogInOut.Close, tbl_Employees.FirstName,
> tbl_Employees.LastName
> > > FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> > > tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> > > tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> > > tbl_CashierLogInOut.Close = False
> > >
> > > Connection...
> > > Dim cnnStr As String, sPath as string
> > > Dim cN As New ADODB.Connection
> > > Dim RsAR As New ADODB.Recordset
> > > 'Set Connection String and connect to DB
> > > sPath = "c:\Database\PS\MainDb.mdb"
> > > cN.CursorLocation = adUseClient
> > > cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> > > cN.Open cnnStr
> > > RsAR.Open sSQL, cN ' Open Read only recordset
> > > '''Code bombs out here...
> > > please help
> > >
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>
>
>
|
| |
|
| |
|
Jacob
|
| Posted: Thu Feb 23 15:18:11 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
I think your problem is in the naming of your fields.
What I can reproduce you can't use Open or Close as field names if you
want to execute SQL from VB-code. Executed direct from Visual Data
Manager it works, executed using ADO it fails.
If I'm correct changing Open to something like OpenCash and the same
for Close should solve your problem.
On Thu, 23 Feb 2006 12:42:46 -0500, "JP Bless"
>I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a long
>time and want to move the code to
>ADO... I get a clueless error "-2147467259 Automation error - Unspecified
>Error" when I run this code. The code is derived from two "simple" tables
>with related data.
>
>When I run the code in Access it works fine but not from VB. I downloaded
>and installed the latest MDAC but it didn't help.
>
>When I do "Select query" without (inner)join on either table I get the right
>result...
>Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close) are
>boolean fields... Does boolean datatype pose any kind of challenge in
>constructing ADO queries?
>
>
>Dim sSQL as string
>
>sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
>tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
>tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
>tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
>FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
>tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
>tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
>tbl_CashierLogInOut.Close = False
>
>Connection...
>Dim cnnStr As String, sPath as string
>Dim cN As New ADODB.Connection
>Dim RsAR As New ADODB.Recordset
> 'Set Connection String and connect to DB
> sPath = "c:\Database\PS\MainDb.mdb"
> cN.CursorLocation = adUseClient
> cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> cN.Open cnnStr
> RsAR.Open sSQL, cN ' Open Read only recordset
>'''Code bombs out here...
>please help
>
>
>Thanks in advance
>
--
To reply by mail remove NEWS from my emailadres.
|
| |
|
| |
|
JT
|
| Posted: Thu Feb 23 16:05:18 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
The SQL Server bit type does allow NULL.
> JP
>
> It's been awhile... but I remember (back in my Access days) getting a
> similar error and it being attributed to the nulls in the boolean fields.
> Access dbs allow nulls for these types of fields, but SQL Server does not.
> Make sure those boolean fields are either TRUE/FALSE (1/0), and sorry I
> could
> not find the web article to support the fix for this issue -- if it is the
> same issue.
>
> Hope that helps.
>
>
>> Thanks norman... I did simplify the select statement... Now I figure the
>> SQL
>> works if I exclude "AND tbl_CashierLogInOut.Open = True AND
>> tbl_CashierLogInOut.Close = False" So I think the problem has something
>> to
>> do with specifying criteria for two boolean fields. Pain!
>>
>> > Your code looks OK to me. It is most likely caused by your
>> > "SELECT...FROM
>> > WHERE..." statement.
>> > So, why don't you simplify your "SELECT..." stahement and add more
>> detailed
>> > version gradually to debug the code?
>> >
>> > You can start with a simple
>> >
>> > "SELECT * FROM tblMyTable"
>> >
>> > Then join the other tables one by one, then add WHERE clause...
>> >
>> > >I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for
>> > >a
>> > >long
>> > > time and want to move the code to
>> > > ADO... I get a clueless error "-2147467259 Automation error -
>> Unspecified
>> > > Error" when I run this code. The code is derived from two "simple"
>> tables
>> > > with related data.
>> > >
>> > > When I run the code in Access it works fine but not from VB. I
>> downloaded
>> > > and installed the latest MDAC but it didn't help.
>> > >
>> > > When I do "Select query" without (inner)join on either table I get
>> > > the
>> > > right
>> > > result...
>> > > Please note (tbl_CashierLogInOut.Open) AND
>> > > (tbl_CashierLogInOut.Close)
>> are
>> > > boolean fields... Does boolean datatype pose any kind of challenge in
>> > > constructing ADO queries?
>> > >
>> > >
>> > > Dim sSQL as string
>> > >
>> > > sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
>> > > tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
>> > > tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
>> > > tbl_CashierLogInOut.Close, tbl_Employees.FirstName,
>> tbl_Employees.LastName
>> > > FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
>> > > tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
>> > > tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True
>> > > AND
>> > > tbl_CashierLogInOut.Close = False
>> > >
>> > > Connection...
>> > > Dim cnnStr As String, sPath as string
>> > > Dim cN As New ADODB.Connection
>> > > Dim RsAR As New ADODB.Recordset
>> > > 'Set Connection String and connect to DB
>> > > sPath = "c:\Database\PS\MainDb.mdb"
>> > > cN.CursorLocation = adUseClient
>> > > cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
>> > > cN.Open cnnStr
>> > > RsAR.Open sSQL, cN ' Open Read only recordset
>> > > '''Code bombs out here...
>> > > please help
>> > >
>> > >
>> > > Thanks in advance
>> > >
>> > >
>> >
>> >
>>
>>
>>
|
| |
|
| |
|
JT
|
| Posted: Thu Feb 23 16:07:22 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
There is no boolean data type in SQL Server. Confirm that whatever data type
you are treating as boolean has only a range of 0, 1, or NULL.
>I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
>long
> time and want to move the code to
> ADO... I get a clueless error "-2147467259 Automation error - Unspecified
> Error" when I run this code. The code is derived from two "simple" tables
> with related data.
>
> When I run the code in Access it works fine but not from VB. I downloaded
> and installed the latest MDAC but it didn't help.
>
> When I do "Select query" without (inner)join on either table I get the
> right
> result...
> Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close) are
> boolean fields... Does boolean datatype pose any kind of challenge in
> constructing ADO queries?
>
>
> Dim sSQL as string
>
> sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
> FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> tbl_CashierLogInOut.Close = False
>
> Connection...
> Dim cnnStr As String, sPath as string
> Dim cN As New ADODB.Connection
> Dim RsAR As New ADODB.Recordset
> 'Set Connection String and connect to DB
> sPath = "c:\Database\PS\MainDb.mdb"
> cN.CursorLocation = adUseClient
> cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> cN.Open cnnStr
> RsAR.Open sSQL, cN ' Open Read only recordset
> '''Code bombs out here...
> please help
>
>
> Thanks in advance
>
>
|
| |
|
| |
|
JP
|
| Posted: Thu Feb 23 16:19:41 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Super!!! fixed the problem... I changed Open to DOpen and Close to DClose
both in Access and SQL and it worked. I had the code running OK for years
using DAO but had no idea same would be a a problem in ADO... Thanks Jacob
and those who helped!!!
> I think your problem is in the naming of your fields.
> What I can reproduce you can't use Open or Close as field names if you
> want to execute SQL from VB-code. Executed direct from Visual Data
> Manager it works, executed using ADO it fails.
> If I'm correct changing Open to something like OpenCash and the same
> for Close should solve your problem.
>
>
> On Thu, 23 Feb 2006 12:42:46 -0500, "JP Bless"
>
> >I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
long
> >time and want to move the code to
> >ADO... I get a clueless error "-2147467259 Automation error - Unspecified
> >Error" when I run this code. The code is derived from two "simple" tables
> >with related data.
> >
> >When I run the code in Access it works fine but not from VB. I downloaded
> >and installed the latest MDAC but it didn't help.
> >
> >When I do "Select query" without (inner)join on either table I get the
right
> >result...
> >Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close)
are
> >boolean fields... Does boolean datatype pose any kind of challenge in
> >constructing ADO queries?
> >
> >
> >Dim sSQL as string
> >
> >sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> >tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> >tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> >tbl_CashierLogInOut.Close, tbl_Employees.FirstName,
tbl_Employees.LastName
> >FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> >tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> >tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> >tbl_CashierLogInOut.Close = False
> >
> >Connection...
> >Dim cnnStr As String, sPath as string
> >Dim cN As New ADODB.Connection
> >Dim RsAR As New ADODB.Recordset
> > 'Set Connection String and connect to DB
> > sPath = "c:\Database\PS\MainDb.mdb"
> > cN.CursorLocation = adUseClient
> > cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> > cN.Open cnnStr
> > RsAR.Open sSQL, cN ' Open Read only recordset
> >'''Code bombs out here...
> >please help
> >
> >
> >Thanks in advance
> >
> --
> To reply by mail remove NEWS from my emailadres.
|
| |
|
| |
|
JP
|
| Posted: Thu Feb 23 16:23:57 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
>I think your problem is in the naming of your fields.
>What I can reproduce you can't use Open or Close as field names if you
>want to execute SQL from VB-code. Executed direct from Visual Data
>Manager it works, executed using ADO it fails.
>If I'm correct changing Open to something like OpenCash and the same
>for Close should solve your problem.
>Jacob
Super!!! fixed the problem... I changed Open to DOpen and Close to DClose
both in Access and my app SQL and it worked. I had the code running OK for
years using DAO but had no idea same would be a problem in ADO... Thanks
Jacob and all (JT, Doug, Norman, F_clef) who helped!!!
> I am using VB6/SP5 MDAC 2.8 SP1... I had this code running on DAO for a
long
> time and want to move the code to
> ADO... I get a clueless error "-2147467259 Automation error - Unspecified
> Error" when I run this code. The code is derived from two "simple" tables
> with related data.
>
> When I run the code in Access it works fine but not from VB. I downloaded
> and installed the latest MDAC but it didn't help.
>
> When I do "Select query" without (inner)join on either table I get the
right
> result...
> Please note (tbl_CashierLogInOut.Open) AND (tbl_CashierLogInOut.Close) are
> boolean fields... Does boolean datatype pose any kind of challenge in
> constructing ADO queries?
>
>
> Dim sSQL as string
>
> sSQL = "SELECT tbl_CashierLogInOut.CashierSessNum,
> tbl_CashierLogInOut.Terminal, tbl_CashierLogInOut.TranDayNum,
> tbl_CashierLogInOut.Cashier, tbl_CashierLogInOut.Open,
> tbl_CashierLogInOut.Close, tbl_Employees.FirstName, tbl_Employees.LastName
> FROM tbl_CashierLogInOut INNER JOIN tbl_Employees ON
> tbl_CashierLogInOut.Cashier = tbl_Employees.EmployeeNumber WHERE
> tbl_CashierLogInOut.Terminal='1' AND tbl_CashierLogInOut.Open = True AND
> tbl_CashierLogInOut.Close = False
>
> Connection...
> Dim cnnStr As String, sPath as string
> Dim cN As New ADODB.Connection
> Dim RsAR As New ADODB.Recordset
> 'Set Connection String and connect to DB
> sPath = "c:\Database\PS\MainDb.mdb"
> cN.CursorLocation = adUseClient
> cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
> cN.Open cnnStr
> RsAR.Open sSQL, cN ' Open Read only recordset
> '''Code bombs out here...
> please help
>
>
> Thanks in advance
>
>
|
| |
|
| |
|
ML
|
| Posted: Fri Feb 24 04:23:27 CST 2006 |
Top |
|
| |
|
ML
|
| Posted: Fri Feb 24 04:23:29 CST 2006 |
Top |
|
| |
|
JP
|
| Posted: Fri Feb 24 06:32:19 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Worked too... wow... Thanks ML
Just wondering is there any where I can find other keywords ADO "barks at"
when used in SQL?
Thanks
> How about simply enclosing column names in brackets [Close], [Open] ?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/
|
| |
|
| |
|
ML
|
| Posted: Fri Feb 24 07:04:27 CST 2006 |
Top |
|
| |
|
JP
|
| Posted: Fri Feb 24 08:25:35 CST 2006 |
Top |
|
| |
|
ML
|
| Posted: Fri Feb 24 08:38:28 CST 2006 |
Top |
|
| |
|
JT
|
| Posted: Fri Feb 24 08:54:32 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
The smaller a data type is, then the more subjective. For example, there are
many shades of red ...
> As does real life.
>
> "Was the car red? Yes or no!"
> "Don't know."
>
>
> ML
>
> ---
> http://milambda.blogspot.com/
|
| |
|
| |
|
ML
|
| Posted: Fri Feb 24 11:10:28 CST 2006 |
Top |
Visual Basic >> ADO VB/SQL help
Many shades of red? Not to me. ;)
But I do agree. Which still leaves room for the "don't know" or "not
important" or "not available" or NULL.
ML
---
http://milambda.blogspot.com/
|
| |
|
| |
|
| |
|