[RESOLVED] syntax error help!

[angelica]

hi ,

I have a bit of a parameter problem which I cant spot!

1. I am populating a combobox a list of booknames/ folNos/ barcodes depending on which radio button is clicked using the BookID

2. Then with a button I execute a query to give me the balance. Somehow the bookID paramater is not being passed to this query. here is my code, and it gives this error.:
---------------------------------
Incorrect Syntax near @checkbook

---------------------------------

Private Sub TbViewStockbalance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TbViewStockbalance.Enter
        StockBalanceOption()
    End Sub
    Private Sub StockBalanceOption()
        Dim strQuery As String
        Try
            'If Not cboBalance1.SelectedItem Is Nothing Then
            strQuery = "Select bookName, bookID, folNo, barCode FROM tb_book where tb_book.obsolete='False'"

            Dim da As New SqlClient.SqlDataAdapter(strQuery, MyConn)
            Dim dtbook As New DataTable
            MyConn.Open()
            da.Fill(dtbook)
            cboBalance1.DataSource = dtbook

            If rbtitleB1.Checked = True Then
                cboBalance1.DisplayMember = "bookName"
            End If
            If rbfolioB1.Checked = True Then
                cboBalance1.DisplayMember = "folNo"
            End If
            If rbbarcodeB1.Checked = True Then
                cboBalance1.DisplayMember = "barCode"
            End If

            cboBalance1.ValueMember = "bookID"
            MyConn.Close()
            'End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            MyConn.Close()
        End Try

    End Sub
    Private Sub StockBalanceOptionSelected()
        Dim strQuery As String
        Try
            If Not cboBalance1.SelectedItem Is Nothing Then
                strQuery = "Select bookName, bookID, folNo, barCode FROM tb_book where tb_book.obsolete='False'"

                Dim da As New SqlClient.SqlDataAdapter(strQuery, MyConn)
                Dim dtbook As New DataTable
                MyConn.Open()
                da.Fill(dtbook)
                cboBalance1.DataSource = dtbook

                If rbtitleB1.Checked = True Then
                    cboBalance1.DisplayMember = "bookName"
                End If
                If rbfolioB1.Checked = True Then
                    cboBalance1.DisplayMember = "folNo"
                End If
                If rbbarcodeB1.Checked = True Then
                    cboBalance1.DisplayMember = "barCode"
                End If

                cboBalance1.ValueMember = "bookID"

                MyConn.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            MyConn.Close()
        End Try

    End Sub
    Private Sub radioButtonsChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboBalance1.SelectedIndexChanged, rbtitleB1.CheckedChanged, rbbarcodeB1.CheckedChanged, rbfolioB1.CheckedChanged
        txtviewBalance.Text = ""
        StockBalanceOptionSelected()
    End Sub


    Private Sub btnViewBalance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewBalBk.Click
        GetCurrentBalance(cboBalance1, txtviewBalance)
    End Sub

This is in a Module :

Public Sub GetCurrentBalance(ByVal cbobook As ComboBox, ByVal txtbox As TextBox)
        Dim sql As String = ("Select stockBalance from tb_book where bookID = @checkbook")
        Dim cmd As New SqlCommand(sql, MyConn)
        'cmd.Parameters.AddWithValue("@checkBook", cbobook.SelectedValue)
        Try
            MyConn.Open()

            txtbox.Text = (cmd.ExecuteScalar()).ToString   'displaying the data from the table
            cmd.Parameters.Clear()
            MyConn.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
            MyConn.Close()
        End Try

    End Sub

[stanav]

Why did you commented out the line that add parameter to your command's parameters collection?

[angelica]

Hi,

I will correct it. My mistake when I was trying to find the error. But even if I uncomment it I get the same error. It seems the value of the cboBalance.selected value is not passing on to @checkbook.

[dolot]

Put a breakpoint on the line where you add the @checkbook parameter. I suspect that cbobook.selectedvalue is returning an empty string.

You may need to check to make sure the user has selected a value before running GetCurrentBalance.

Alternatively, you may want to try cboBalance.SelectedItem.tostring instead of selectedvalue.

Hope this helps.

[stanav]

You can always verify what cbobook.SelectedValue returns, right?
As for the code, I don't see anything that could raise the flag. Have you tried passing in a hard-coded value to the parameter? If this works, cbobook.SelectedValue is the culprit. Also, you should set the DisplayMember and ValueMember of your bound combobox before setting its DataSource property. Also use If... ElseIf block instead of 3 separate If blocks.

               If rbtitleB1.Checked = True Then
                    cboBalance1.DisplayMember = "bookName"
                
                ElseIf rbfolioB1.Checked = True Then
                    cboBalance1.DisplayMember = "folNo"
                
                ElseIf rbbarcodeB1.Checked = True Then
                    cboBalance1.DisplayMember = "barCode"
                End If

                cboBalance1.ValueMember = "bookID"

                cboBalance1.DataSource = dtbook

[angelica]

Hi ,

the passing of the value inthe cboBalance1.selectedvalue to @checkbook was the trouble. I hardcoded and its working fine now.

Thanks .