Problem using a variable in an sql quary

[Ednt]

Hi All,

can anyone please tell me what's wrong in this code

I'm trying to get some data from an access database and show them in a CheckedListBox
The sql quary includes a variable('sport') and it's values is passed by the user

when I run the following code an exception is thrown at the line
adStudentList.Fill(dsStudentList, "Register")
and it says 'no value given for one or more required parameters'

Dim adStudentList As OleDbDataAdapter
Dim dsStudentList As New DataSet

        Dim sport As String
        sport = cmbSportList.SelectedItem

        Try
            cnColors.Open()
        Catch ex As Exception
            MessageBox.Show("Error in connecting")
        End Try

        adStudentList = New OleDbDataAdapter("SELECT student_no                 
        FROM register WHERE sport_ID =" & sport, cnColors)
        adStudentList.Fill(dsStudentList, "Register")

        Dim i As Integer
        If dsStudentList.Tables("Register").Rows.Count > 0 Then
            For i = 0 To dsStudentList.Tables("Register").Rows.Count - 1
                clbStudentList.Items.Add(CStr(dsStudentList.Tables("Register").Rows(i).Item("student_no")), False)

            Next
        End If

thanks

[kvprajapati]

>and it says 'no value given for one or more required parameters

Verify columns and table name in your database.

[Ednt]

Hi adapost,

It is like below
Table: Register
Columns: student_no ,sport_ID

I’m new to vb.net. After some searching effort I found it’s working in the following way.
I think when a variable is used in sql quary a parameter should be passed to the command object to identify that variable.

Dim adStudentList As New OleDbDataAdapter
    Dim cmStudentList As OleDbCommand
    Dim selectSql As String
    Dim dsStudentList As New DataSet
    Dim sport As String
    sport = cmbSportList.SelectedItem 

        Try
            cnColors.Open()
        Catch ex As Exception
            MessageBox.Show("Error in connecting")
        End Try


        Dim selectSql As String = "SELECT student_no FROM  Register  WHERE sport_ID = ?"
        cmStudentList = New OleDbCommand(selectSql, cnColors)
        adStudentList.SelectCommand = cmStudentList
        cmStudentList.Parameters.Add("@sport_ID", OleDbType.VarChar, 10).Value = sport
        adStudentList.Fill(dsStudentList, "Register")


        Dim i As Integer
        If dsStudentList.Tables("Register").Rows.Count > 0 Then
            For i = 0 To dsStudentList.Tables("Register").Rows.Count - 1
                clbStudentList.Items.Add(CStr(dsStudentList.Tables("Register").Rows(i).Item("student_no")), False)
            Next
        End If

Hope this would give a clue to other who have similer problems.

[kvprajapati]

Try this,

Dim adStudentList As New OleDbDataAdapter
    Dim cmStudentList As OleDbCommand
    Dim selectSql As String
    Dim dsStudentList As New DataSet
    Dim sport As String
    sport = cmbSportList.SelectedItem 

    Dim selectSql As String = "SELECT student_no FROM  Register  WHERE sport_ID =@sport_ID"
    cmStudentList = New OleDbCommand(selectSql, cnColors)
    adStudentList.SelectCommand = cmStudentList
    cmStudentList.Parameters.Add("@sport_ID", OleDbType.VarChar, 10).Value = sport

    adStudentList.Fill(dsStudentList, "Register")


    Dim i As Integer
    If dsStudentList.Tables("Register").Rows.Count > 0 Then
            For i = 0 To dsStudentList.Tables("Register").Rows.Count - 1
                clbStudentList.Items.Add(CStr(dsStudentList.Tables("Register").Rows(i).Item("student_no")), False)
            Next
        End If