4

I am attempting to get data out of an access database in VB within visual studio 2010.

I am creating a select query at runtime which should return 1 row. I am then collecting the data from the returned row and placing it on a screen.

This the code that generates the query.

Dim ls_querystring as string

ls_querystring = "SELECT Horses.Name, "
ls_querystring = ls_querystring & "Horses.Horseid, "
ls_querystring = ls_querystring & "Horses.Regno, "
ls_querystring = ls_querystring & "Horses.HorseType, "
ls_querystring = ls_querystring & "Horses.Sex, "
ls_querystring = ls_querystring & "Horses.Colour, "
ls_querystring = ls_querystring & "Horses.Ownerid, "
ls_querystring = ls_querystring & "Horses.Notes, "
ls_querystring = ls_querystring & "Horses.Sireno, "
ls_querystring = ls_querystring & "Horses.Damno, "
ls_querystring = ls_querystring & "Horses.Birthdate, "
ls_querystring = ls_querystring & "Horses.DNA, "
ls_querystring = ls_querystring & "Horses.Inscribe, "
ls_querystring = ls_querystring & "Horses.Revised, "
ls_querystring = ls_querystring & "Horses_1.Name as SireName, "
ls_querystring = ls_querystring & "Horses_2.Name as DamName, "
ls_querystring = ls_querystring & "Horses.IMAGE, "
ls_querystring = ls_querystring & "Associates.Name1 as Breeder "
ls_querystring = ls_querystring & " FROM ((Horses INNER JOIN Horses AS 
Horses_1 ON Horses.Sireno = Horses_1.Regno) INNER JOIN Horses AS Horses_2 "
ls_querystring = ls_querystring & " ON Horses.Damno = Horses_2.Regno) INNER 
JOIN Associates ON Horses.Breederid = Associates.AssocID "
ls_querystring = ls_querystring & " WHERE Horses.Regno =  " 
& Chr(34) & ls_RegNo & Chr(34)

The variable ls_Regno is defined as a string and has a value when ls_querystring is created.

When this runs ls_querystring contains:

SELECT Horses.Name, Horses.Horseid, Horses.Regno, Horses.HorseType, 
Horses.Sex, Horses.Colour, Horses.Ownerid, 
Horses.Notes, Horses.Sireno, Horses.Damno, Horses.Birthdate, Horses.DNA, 
Horses.Inscribe, 
Horses.Revised, Horses_1.Name as SireName, Horses_2.Name as DamName, 
Horses.IMAGE, 
Associates.Name1 as Breeder  
FROM ((Horses INNER JOIN Horses AS Horses_1 ON Horses.Sireno = 
Horses_1.Regno)
INNER JOIN Horses AS Horses_2  ON Horses.Damno = Horses_2.Regno) 
INNER JOIN Associates ON Horses.Breederid = Associates.AssocID  
WHERE Horses.Regno =  "SO0324"

If i run this against the Access database it returns the expected single row.

However when i include it in the following code it fails with the no value given for one or more required parameters issue message at the da.Fill(datat) statement.

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\Web\horsebook.mdb")
Dim datat As New DataTable
Dim da As New OleDbDataAdapter(ls_querystring, cn)

da.Fill(datat)
da.Dispose()
cn.Dispose()

If I replace the query generated by the ls_querystring process with a benign script such as "Select * from Horse" it works with no issue. So it would seem that the issue is in the query generated however the fac that it works as expected if just run in access is confusing me.

So it would appear that there is a disconnect between a query that Access accepts and that is submitted to the same database via an oleDbDataAdapter.

I'd appreciate it if anyone could see the error in my ways.

Thanks

Andre
  • 26,751
  • 7
  • 36
  • 80
destino
  • 51
  • 4
  • Could it be because you are missing a parameter when using `Fill()`? [MSDN](https://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.fill(v=vs.110).aspx) doesn't show a valid overload that accepts just a DataTable. Also, make sure everything is [spelled right and of correct case](https://stackoverflow.com/questions/2378763/no-value-given-for-one-or-more-required-parameters?rq=1). Maybe you have a column name reference in the SELECT incorrectly spelled. – Timothy G. May 31 '17 at 11:46
  • 2
    `.Name` - Name is a reserved word, try using `.[Name]` or (better) avoiding this column name. This may apply for `.IMAGE` too. – Andre May 31 '17 at 11:51
  • 3
    P.S. +1 for including the actual resulting SQL, so few people do this when asking questions about dynamic SQL. – Andre May 31 '17 at 11:53
  • 2
    I strongly recommend that you learn how to use XML literals. It would make your SQL code significantly more readable. You really ought to be using a parameter for that `Regno` value too. – jmcilhinney May 31 '17 at 12:34
  • Thanks for all your suggestions. I've spent the last 4 hours trying each of your suggestions. In answer to Timothy G. I have this exact same syntax elsewhere in this app and it is happy with the syntax of the fill() statement. – destino Jun 01 '17 at 04:17

1 Answers1

1

Thanks for all your suggestions. I've spent the last 4 hours trying each of your suggestions. In answer to Timothy G. I have this exact same syntax elsewhere in this app and it is happy with the syntax of the fill() statement.

In answer to Andre's comment I have used Name and IMAGE in other parts of the app and there is no issue with this. I have changed the names of these fields and I still have the issue.

I had a brief look at XMLliterals and I guess its what you are most comfortable with. This is the way i have been creating run time queries for 15 years and so i guess its difficult to teach an old dog new tricks. I'll have to disagree that it makes your SQL more readable. I experience XML code in my day job and i find it incredibly verbose and often overly complicates simple tasks. We'll just have to agree to disagree :)

To cut to the chase I have found the issue and it was my fault. It came down to what Timothy G suggested, a simple spelling issue complicated by the fact that I was running the app against one database and testing the query against a copy of the same database.

this was the culprit

Associates.Name1 as Breeder

its should be

Associates.FamilyName as Breeder

Again thankyou all.

Peter

destino
  • 51
  • 4