Problems with parameters when pulling data from SQL

Question

Hello all,

I have the code Below:

 Private Sub CheckBatch()

        Dim conn As SqlConnection = New SqlConnection()
        conn.ConnectionString = "Data Source=My-PC\SQLEXPRESS;Initial Catalog=ACME;Integrated Security=True"
        conn.Open()

        Dim sqlBatch As String = "Select  _Date, _BatchClosed from BagandBatch WHERE  _Date= @_Date, _BatchClosed= @_BatchClosed"

        Dim comm As SqlCommand = New SqlCommand(sqlBatch, conn)

        Dim myparameter As SqlParameter = New SqlParameter

        Dim DateResult As String = myparameter.ParameterName = "@_Date"
        myparameter.Value = Date.Today

        Dim BatchResult As String = myparameter.ParameterName = "@_BatchClosed"
        myparameter.Value = "N"

        comm.ExecuteScalar().ToString()

        If DateResult <> "" And BatchResult = True Then
            Me.Visible = False
            txtEmpID.Text = String.Empty
            txtPassword.Text = String.Empty
            frmMainMenu.Show()
            frmMainMenu.btnBeginDay.Enabled = False
        Else
            Me.Visible = False
            txtEmpID.Text = String.Empty
            txtPassword.Text = String.Empty
            frmMainMenu.Show()
        End If

    End Sub

When I execute it tells me that Must declare the scalar variable "@_Date". But I thought that was already declared on my SqlBatch line.

Anyone can point me on the right direction here? I appreciate all the help.

Thank you.

Answer 1

two things i can see:

Dim sqlBatch As String = "Select  _Date, _BatchClosed from BagandBatch WHERE  _Date= @_Date AND _BatchClosed= @_BatchClosed"

Dim DateResult As String = myparameter.ParameterName = "@_Date"

that doesn't work how you think. try this:

myparameter.ParameterName = "@_Date"

---

thanks for any help

Answer 2

Thanks for the reply. But still doing the same thing. Even after I removed the variables. Here's what I

did

Dim conn As SqlConnection = New SqlConnection()
        conn.ConnectionString = "Data Source=LEODACOSTA-PC\SQLEXPRESS;Initial Catalog=ACME;Integrated Security=True"
        conn.Open()

        Dim sqlBatch As String = "Select  _Date, _BatchClosed from BagandBatch WHERE  _Date= @_Date, _BatchClosed= @_BatchClosed"

        Dim comm As SqlCommand = New SqlCommand(sqlBatch, conn)

        Dim myparameter As SqlParameter = New SqlParameter

        myparameter.ParameterName = "@_Date"
        myparameter.Value = Date.Today
        myparameter.ParameterName = "@_BatchClosed"
        myparameter.Value = "N"

        comm.ExecuteScalar()

        If myparameter.Value = True Then
            Me.Visible = False
            txtEmpID.Text = String.Empty
            txtPassword.Text = String.Empty
            frmMainMenu.Show()
            frmMainMenu.btnBeginDay.Enabled = False
        Else
            Me.Visible = False
            txtEmpID.Text = String.Empty
            txtPassword.Text = String.Empty
            frmMainMenu.Show()
        End If

Is it because I'm using multiple parameters? I didn't think that would cause it to throw an exception.

Thanks.

Answer 3

Dim sqlBatch As String = "Select  _Date, _BatchClosed from BagandBatch WHERE  _Date= @_Date AND _BatchClosed= @_BatchClosed"

---

thanks for any help

Answer 4

Still giving me the same error.

Thanks

Answer 5

You need to create a new sqlparameter for your second parameter.

The way you have it you're overwriting it

---

thanks for any help

Answer 6

Wow. I'm appalled about this. I added a second parameter, but still giving me the same error.

Answer 7

Wow. I'm appalled about this. I added a second parameter, but still giving me the same error.

ok i've had a better look at it + there are still a few errors.

what do you actually want to do with the results of the SELECT statement?

do you need to use them or you just want to test if that record exists?

---

thanks for any help

Answer 8

i'm assuming you just want to test it exists:

Dim conn As SqlConnection = New SqlConnection()
conn.ConnectionString = "Data Source=LEODACOSTA-PC\SQLEXPRESS;Initial Catalog=ACME;Integrated Security=True"
conn.Open()

Dim sqlBatch As String = "Select  COUNT(_Date) from BagandBatch WHERE  _Date= @_Date AND _BatchClosed= @_BatchClosed"

Dim comm As SqlCommand = New SqlCommand(sqlBatch, conn)

Dim myparameter As SqlParameter = New SqlParameter
myparameter.ParameterName = "@_Date"
myparameter.Value = Date.Today
comm.Parameters.Add(myparameter)
myparameter = New SqlParameter
myparameter.ParameterName = "@_BatchClosed"
myparameter.Value = "N"
comm.Parameters.Add(myparameter)

If CInt(comm.ExecuteScalar()) = 1 Then
    Me.Visible = False
    txtEmpID.Text = String.Empty
    txtPassword.Text = String.Empty
    frmMainMenu.Show()
    frmMainMenu.btnBeginDay.Enabled = False
Else
    Me.Visible = False
    txtEmpID.Text = String.Empty
    txtPassword.Text = String.Empty
    frmMainMenu.Show()
End If

---

thanks for any help

Answer 9

Thank you so much. That's exactly what I needed. I appreciate all the help...