Question Parameterised queries - correct format for Access select query

[ballybeg]

Hi there again fellow vb'ers. Thanks to many posts on this site and the web, I have made much progess in my .net upskilling. Thanks in particular to Jim McI for his great codebank on database access.

One area I have not been able to get on top of is the use of parameterised queries.

I have built the following function and it works fine. I am just a bit confused about where I should be adding the parameters...seems like duplication in my code - the sql statement and command text are the same. so should I be using a parameter.add statement.

I know that the sql place holders for access look like this: "SELECT MyBackOdds, MyLayOdds FROM tblMyOdds WHERE NSWCode = ? AND RaceNumber = ?" but I dont know where to insert the values passed into the function...should I be using a parameter.add statement and if so how?

 Public Function fnGetMyOddsRunners(ByVal NSWCode As String, ByVal RaceNumber As Integer) As TBBusinessClasses.clsRace
        Dim AccessConnection As New OleDb.OleDbConnection
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ToteBetter\ToteBetter.accdb"
        Dim sql As String
        Dim intRunners As Integer
        Dim intCount As Integer
        Dim command As New OleDb.OleDbCommand
        Dim ds As New DataSet
        Dim oclsRunner As New TBBusinessClasses.clsRace

        AccessConnection.ConnectionString = connectionString
        command.Connection = AccessConnection
        AccessConnection.Open()

        sql = "SELECT MyBackOdds, MyLayOdds FROM tblMyOdds WHERE NSWCode = '" & NSWCode & "' AND RaceNumber = " & RaceNumber & ""
        command.CommandText = "SELECT MyBackOdds, MyLayOdds FROM tblMyOdds WHERE NSWCode = '" & NSWCode & "' AND RaceNumber = " & RaceNumber & ""
        command.ExecuteNonQuery()

        Dim da As New OleDb.OleDbDataAdapter(sql, AccessConnection)
        Dim cmdbldr As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
        Using AccessConnection
            da.Fill(ds, "tblMyOdds")
            intRunners = ds.Tables(0).Rows.Count - 1
            For intCount = 0 To intRunners
                oclsRunner.clsrunner.Add(New clsRunner With {.MyBackOdds = ds.Tables(0).Rows(intCount)(0), .MyLayOdds = ds.Tables(0).Rows(intCount)(1)})
            Next intCount
        End Using
        Return oclsRunner
        AccessConnection.Close()
        Form1.DGVRunners.Rows.Clear()
        Form1.TblRaceListTableAdapter.FillByNTJ(Form1.ToteBetterDataSet.tblRaceList)
    End Function

While this "works", I would like to start off learning the right way, any pointers on the *correct * process most welcome

ballybeg

 

[jmcilhinney]

Follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

 

[ballybeg]

Changes after adding parameters

Thanks Jim,

As suggested I read up up your blog link and have changed this line:

sql = "SELECT MyBackOdds, MyLayOdds FROM tblMyOdds WHERE NSWCode = '" & NSWCode & "' AND RaceNumber = " & RaceNumber & ""
to this
sql = "SELECT MyBackOdds, MyLayOdds FROM tblMyOdds WHERE (NSWCode = ? ) AND (RaceNumber = ?)"

and added the parameters thus:

command.CommandText = sql
        command.Parameters.AddWithValue("NSWCode", NSWCode)
        command.Parameters.AddWithValue("RaceNumber", RaceNumber)
        command.ExecuteNonQuery()

        Dim da As New OleDb.OleDbDataAdapter(sql, AccessConnection)
        Dim cmdbldr As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
        Using AccessConnection
            da.Fill(ds, "tblMyOdds")    [COLOR=#ff0000]<-----error hits here[/COLOR]

This executes fine, but I get an error when trying to fill the data adapter - the usual "No value given for one or more required parameters" error.
I have checked all the spellling, names etc, but can't find where the error is - the first SQL (with the concatenation) works but the latter does not

Any clues?

ballybeg

 

[ballybeg]

Solved - parameters instead of concatenation

After some trial and error, I realised that I had to pass the dataAdapter the parameters as well, so I broke it down:

 Dim da As New OleDb.OleDbDataAdapter
        Dim selectCMD As OleDbCommand = New OleDbCommand(sql, AccessConnection)
        da.SelectCommand = selectCMD
        selectCMD.Parameters.AddWithValue("NSWCode", NSWCode)
        selectCMD.Parameters.AddWithValue("RaceNumber", RaceNumber)

now it works fine - thanks again Jim

ballybeg