[RESOLVED] Using parameters

[paulorton]

I'm trying to fill an array from a query on a table but I don't know how to turn the variable "CentreName" into a parameter of the query (obviously the sql string I presently have doesn't work).

Public Function db_GetClassDays(ByVal ManagerName, ByVal CentreName) As Array

        Dim Conn As OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim SQL As String
        Dim ConnectionString As String
        Dim ClassDays(gMaxCentres) As String

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & My.Settings.AppPath & "\" & ManagerName & "_FCLManager2011_Centres.mdb"
        SQL = "SELECT * FROM tblCentre_Details WHERE Centre_Name = CentreName ORDER by ID"

        Conn = New OleDbConnection(ConnectionString)
        Try
            Conn.Open()
            da = New OleDbDataAdapter(SQL, Conn)
            da.Fill(ds)
            da.Dispose()
            Conn.Close()

           'now some code here to fill ClassDays with data from the dataset

        Catch ex As Exception

            MsgBox("Cannot open connection ! ")
        End Try

        Return ClassDays

    End Function

[paulorton]

OK, I've altered my code slightly :-

Public Function db_GetClassDays(ByVal ManagerName, ByVal CentreName) As Array

        Dim Conn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim da As OleDbDataAdapter
        Dim ds As New DataSet
        Dim ConnectionString As String
        Dim SQL As String
        Dim ClassDays(gMaxCentres) As String

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & My.Settings.AppPath & "\" & ManagerName & "_FCLManager2011_Centres.mdb"
        SQL = "SELECT * FROM tblCentre_Details WHERE Centre_Name = @Centre_Name ORDER by ID"
        Conn = New OleDbConnection(ConnectionString)

        Try            
            cmd = New OleDbCommand(SQL, Conn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Centre_Name", "Centre_Name")
            Conn.Open()
            da = New OleDbDataAdapter(SQL, Conn)
            da.Fill(ds)
            da.Dispose()
            Conn.Close()
            
            If ds.Tables(0).Rows(0).Item("Monday_Class") Then
                CentreName(0) = "Mon"
            End If

        Catch ex As Exception
            MsgBox("Cannot open connection ! ")
        End Try

        Return ClassDays

Execution stops after the highlighted line with the same error I was getting before - "No value given for one or more required parameters."

Could someone please show me where my code is going wrong?

[jmcilhinney]

You are adding a parameter to a command that you never use and not adding a parameter to the command you do use. You are creating an OleDbCommand explicitly and adding a parameter to it, but you then create an OleDbDataAdapter that doesn't use that command. If you want to create the command explicitly then you have to use that command:

Dim connection As New OleDbConnection("connection string here")
Dim command As New OleDbCommand(sql, connection)
Dim adapter As New OleDbDataAdapter(command)

Otherwise, you have to use the command that the adapter creates fro you:

Dim connection As New OleDbConnection("connection string here")
Dim adapter As New OleDbDataAdapte(sql, connection)
 
adapter.SelectCommand.Parameters.AddWithValue("@ColumnName", value)

[paulorton]

That works fine - thank you once again! I had tried messing around with "SelectCommand" couldn't get the syntax right. You live and learn!