why it said "must declare scalar @sect"?

[kank]

I used this code (@sect in sql statement) in other forms, it works but why in this form it said "must declare scalar @sect" don't understand :-( pls help.

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        sect = cmbSect.Text
        dept = cmbDept.Text
        sqlconn.Open()
        If sect = Nothing Then
            MessageBox.Show("Please input Section")
            sqlconn.Close()
            Exit Sub
        End If
        If dept = Nothing Then
            MessageBox.Show("Please input Department")
            sqlconn.Close()
            Exit Sub
        End If

        Dim SQLCmd As New SqlCommand()
        SQLCmd = sqlconn.CreateCommand
        SQLCmd.CommandText = "SELECT * FROM tblNew_IDL where section =@sect and department =@dept"
        SQLCmd.Parameters.AddWithValue("@sect", sect)
        SQLCmd.Parameters.AddWithValue("@dept", dept)
        da = New SqlDataAdapter(SQLCmd.CommandText, sqlconn)
        ds = New DataSet()
        Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
        da.Fill(ds, "New")
        bsource.DataSource = ds.Tables("New")
        DataGridView1.DataSource = bsource
        sqlconn.Close()
    End Sub

Thanks a lot

 

[jmcilhinney]

You start by creating a SqlCommand object and adding parameters to it. You then create a SqlDataAdapter but you don't pass your existing SqlCommand to it. You only pass the String containing the SQL code, so the adapter creates its own SqlCommand. You don't add any parameters to that one, hence the error.

You should either create the SqlCommand first and then pass it to the SqlDataAdapter constructor or else create the SqlDataAdapter first and then get the SqlCommand from its SelectCommand property. If you want to use CreateCommand then you must go with the first option.