I am trying to get data from an access database in VB in visual studio 2010.
I create a select query at runtime that should return 1 row. Then I collect data from the returned row and put it on the screen.
This code generates a request.
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 ls_Regno variable is defined as a string and is relevant when creating ls_querystring.
When this is done, 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 for an Access database, it returns the expected one row.
However, when I include it in the following code, it fails with no value set for one or more error messages for the required parameters in 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()
, ls_querystring, benign script, , "Select * from Horse", . , , , , , , , , , .
, , , Access , oleDbDataAdapter.
, - .