SELECT statements with substitution varibables with access via VB .NET 2003

[pete_484]

Hi, I wonder if anyone can help me. I am trying to connect to an access database in VB .NET 2003 with a query that contains a substitution variable (lets the user enter in a parameter which determines rows selected by the query) but I am having problems. I am using..

Microsoft Jet 4.0 OLE DB Provider, 
with an OleDbDataAdapter and a
OleDbConnection.

I have tried to write the query using the OleDbDataAdapter to generate a dataset. I used the query builder in the configuration wizard of the data adapter to write this query...

SELECT CustomerID, CopyID, DateOut, DateDue, DateReturned, 
0.5 * (DateReturned - DateDue) AS Fines, FinesPayed 
FROM LoanRes 
WHERE CustomerID = [enter customerID]

note this query works in access. I am then using the dataadapter to fill the dataset when the form loads.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.OleDbDataAdapter1.Fill(Me.LoanRes)
    End Sub

I have set up a data grid on the form and set its DataSource to be filled by the dataset. I know you can do all of this with writing VB code, and not using any graphical wizards or components from the data/windows forms toolbox. I am getting an

unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

when I try to load the form. This is because I am trying to use a substitution variable, but when the query runs, no parameter can be entered.

Can anyone please help me, and show me how to do this in VB code, I know it can all be done, I just dont know how. Cheers.

 

[Sergey Smagin]

Hi, I think your query should be written as follows:

 SELECT CustomerID, CopyID, DateOut, DateDue, DateReturned, 
0.5 * (DateReturned - DateDue) AS Fines, FinesPayed 
FROM LoanRes 
WHERE CustomerID = :CustomerID

or

 SELECT CustomerID, CopyID, DateOut, DateDue, DateReturned, 
0.5 * (DateReturned - DateDue) AS Fines, FinesPayed 
FROM LoanRes 
WHERE CustomerID = ?

Anyway you'll have to write some code so the user can enter the value for your parameter, because there will be no message box appeared automatically like it was in MS Access.

You can find an example of applying parameter values to the OleDbCommand object in the VB.NET demo project of Active Query Builder ActiveX Edition (see the 'Demos\VBNetDemo\ParametersForm.vb' file).
(Direct download link)

Hope this helps.

 

[cjard]

Following Sergeys second code advice

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
[B]        Dim s as String = InputBox("Enter customer ID")[/B]
[B]        Me.OleDbDataAdapter1.SelectCommand.Parameters(0).Value = s[/B]
        Me.OleDbDataAdapter1.Fill(Me.LoanRes)
    End Sub

read the dw1 link in my signature

VB.NET <> Access!