No data returned

[enak]

I have an Access 2000 database that I have put a Windows Form (VB.Net) frontend on.

It all works fine until now. I have a report that I am trying to build where I have to retrieve account type data and from there retrieve account managers info.

I use a datatable as part of a dataset to retrieve the account type info. This works great. Then I try to retrieve the account manager info into the same dataset but a different table. I don't get any data.

I can run the query in Access and it runs fine.

Any clue as to why this is happening?

Thanks,
enak

 

[jebenson]

could you show us some code, such as the SQL used for the queries, and the code for opening your dataadapter(s) and filling your datasets?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[enak]

Here is the query:

Select (tblAcctMgr.fname & " " & tblAcctMgr.lname) AS Mgr, tblAccount.ID
From tblAcctMgr,tblAccount
Where tblAccount.ClassificationID = 3 And tblAcctMgr.ClassificationID Like "*3*"
AND tblAcctMgr.ID = tblAccount.AccountMgrID

Here is the code that I use to get the data:

Dim da As New OleDb.OleDbDataAdapter(sSQL, cnn)
'Dim da As New SqlClient.SqlDataAdapter(sSQL, cnn)

Try
da.Fill(ds, sTableName)
Return True
Catch ex As Exception
'Err.Raise(vbObjectError, , ex.Message)
sSQL = Err.Description
Return False
Throw ex
End Try


I have also tried to use a datareader like this:

Try
cnn.Open()
myCMD = New OleDb.OleDbCommand(sSQL, cnn)

dr = myCMD.ExecuteReader(CommandBehavior.Default)
sSQL = "success"
Return dr
Catch ex As Exception
Throw ex
End Try


I really hope you can see something because I can't

 

[RoyVidar]

The SQL you show, is copy pasted from Access, isn't it?

Do you use the stored query within Access, or are you using dynamic SQL? If the latter, try changing the wildcards from * to % (and the double quotes to single quotes, though I don't know if that's entirely necessary)

Roy-Vidar

 

[chiph]

Select (tblAcctMgr.fname & " " & tblAcctMgr.lname) AS Mgr, tblAccount.ID 
From tblAcctMgr,tblAccount
Where tblAccount.ClassificationID = 3 And tblAcctMgr.ClassificationID Like "*3*" 
AND tblAcctMgr.ID = tblAccount.AccountMgrID

I forget, does Access use single-quotes or double quotes for string literals? Try this just in case:

Select (tblAcctMgr.fname & ' ' & tblAcctMgr.lname) AS Mgr, tblAccount.ID 
From tblAcctMgr,tblAccount
Where tblAccount.ClassificationID = 3 And tblAcctMgr.ClassificationID Like '*3*' 
AND tblAcctMgr.ID = tblAccount.AccountMgrID

Chip H.




____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[enak]

Thanks for the suggestions. I changed the * to % and it worked fine because I am using embedded SQL.