incorrect syntax near "=" in vb.net

Question

I am a beginner and really need help. I want to display data from the database and assign the values to the textboxes and a combobox on a form, but I get this error

Incorrect syntax near "="

It appears is on this line

myreader = cmd.ExecuteReader

Please - any help?

 Sub ref()
        Dim conn As New SqlConnection
        conn.ConnectionString = ("Data Source=.;Initial Catalog=UEW_ADMISSION_CHEAKER;Integrated Security=True")
        conn.Open()

        Dim strsql As String
        strsql = "SELECT ProgName,MaleCuteOff,FemaleCutOff from CutOff_Point where ProgName=" + cmbCourse.SelectedItem + ""

        Dim cmd As New SqlCommand(strsql, conn)

        Dim myreader As SqlDataReader
        myreader = cmd.ExecuteReader

        myreader.Read()

        txtFemale.Text = myreader("FemaleCutOff")
        txtMale.Text = myreader("MaleCuteOff")
        conn.Close()

    End Sub

Assuming SelectedItem is a string, you're missing the leading and trailing single quotes ... where ProgName='" + cmbCourse.SelectedItem + "'" — John Cappelletti, Jun 29 '20 at 19:40
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Jun 29 '20 at 19:48
Use an SQL parameter for the value. So, `Dim strsql As String = "SELECT ProgName,MaleCuteOff,FemaleCutOff FROM CutOff_Point WHERE ProgName = @ProgName"` and then `cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@ProgName", .SqlDbType = SqlDbType.VarChar, .Size = 99, .Value = cmbCourse.SelectedItem})` (adjust the SqlDbType and Size to match the database column). — Andrew Morton, Jun 29 '20 at 19:50
@AndrewMorton what about the FemaleCutOff and MaleCutOff with their datatype int? — ennkay, Jun 29 '20 at 20:53
@ennkay 1) You could try `txtFemale.Text = myreader.GetInt32(2).ToString()`. 2) Also, make sure to use [`Option Strict On`](https://stackoverflow.com/a/29985039/1115360) as it will let Visual Studio guide you to using correct data types. — Andrew Morton, Jun 29 '20 at 21:15

score 0 · Answer 1 · edited Jun 30 '20 at 04:30

0

First of all this Block of Code is not OK. You could use :

Using....End Using Method.

SqlCommand.Parameters Property for security issues.

Connection Strings and Configuration Files for security issues.

Allow me to rewrite your Code using the above methods.

Private Sub RetrieveAndDisplayCutOff()
    Dim sbMale As New StringBuilder
    Dim sbFemale As New StringBuilder
    Dim strsql As String =
            "SELECT MaleCutOff,FemaleCutOff FROM CutOff_Point WHERE ProgName = @ComboItem"
    Using conn As New SqlConnection("Data Source=.;Initial Catalog=UEW_ADMISSION_CHEAKER;Integrated Security=True"),
         CMD As New SqlCommand(strsql, conn)
        CMD.Parameters.Add("@ComboItem", SqlDbType.VarChar).Value = ComboBox1.SelectedItem.ToString
        conn.Open()
        Using MyReader As SqlDataReader = CMD.ExecuteReader
            While MyReader.Read 'Returns False if no more rows
                'OP mentioned in comments that these fields were int
                sbMale.AppendLine(MyReader.GetInt32(0).ToString)
                sbFemale.AppendLine(MyReader.GetInt32(1).ToString)
            End While
        End Using
    End Using
    txtMale.Text = sbMale.ToString
    txtFemale.Text = sbFemale.ToString
End Sub

edited Jun 30 '20 at 04:30

Mary

14,926
3
18
27

answered Jun 29 '20 at 20:50

evry1falls

194
4
15

1

thanks very much but this was the error i had..... The parameterized query '(@ComboItem varchar(8000))SELECT ProgName,MaleCuteOff,FemaleCutO' expects the parameter '@ComboItem', which was not supplied. – ennkay Jun 29 '20 at 21:18
This code can be written much more concisely. The constructor for the connection can take the connection string as a parameter. The constructor for the command can take the command text and the connection as parameters. `CommandType.Text` is the default and does not need to be explicitly coded. – Mary Jun 29 '20 at 23:25
@Mary, you are free to edit the Code and share your point. – evry1falls Jun 29 '20 at 23:27
@Mary, by the way, I was only giving an example of implementation. – evry1falls Jun 29 '20 at 23:29
I really don't like to start butchering other peoples code. – Mary Jun 29 '20 at 23:30
In the While loop, why keep overwriting the text boxes? – Mary Jun 29 '20 at 23:31
What you refer to is not the OP's problem. That code would not cause an error. The error is caused by what is explained in the first comment by John Cappelletti . – Mary Jun 29 '20 at 23:33
@Mary, - What do you mean by *overwriting the textboxes?*. - Yea, you are right about 2nd one, it was not the problem – evry1falls Jun 30 '20 at 00:02
You are assigning a new value to the text boxes on each iteration of the loop. It is not a good idea to update the user interface while holding the connection open. – Mary Jun 30 '20 at 00:10
@Mary, Suggested Code would be then ? – evry1falls Jun 30 '20 at 00:23
@Mary, yea that looks even better. Thank you – evry1falls Jun 30 '20 at 09:31

score 0 · Answer 2 · answered Jun 29 '20 at 21:37

You should always use SQL parameters to pass parameters to SQL - it avoids embarrasing problems like single quotes breaking the query and deliberate SQL injection attacks.

It's probably best to make sure that there is a selected value before trying to use it.

Some things, e.g. database connections, use "unmanaged resources" and it is necessary to use the Dispose() method to make sure that things are cleaned up afterwards. The Using statement is a convenient way to get the computer to take care of that for you.

I didn't see a need for the query to return the value that was passed to it (ProgName).

You will need to adjust the .SqlDbType and .Size to match the database column.

Option Strict On

' ... other code

Sub Ref()
    If cmbCourse.SelectedIndex >= 0 Then
        Dim sql As String = "SELECT MaleCuteOff, FemaleCutOff FROM CutOff_Point WHERE ProgName = @ProgName"
        Dim connStr = "Data Source=.\;Initial Catalog=UEW_ADMISSION_CHEAKER;Integrated Security=True"

        Using conn As New SqlConnection(connStr),
           cmd As New SqlCommand(sql, conn)

            cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@ProgName", .SqlDbType = SqlDbType.VarChar, .Size = 99, .Value = cmbCourse.SelectedItem})

            conn.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader()

            If rdr.HasRows Then
                rdr.Read()
                txtFemale.Text = rdr.GetInt32(0).ToString()
                txtMale.Text = rdr.GetInt32(1).ToString()
            End If

        End Using
    End If

End Sub

P.S. Shouldn't UEW_ADMISSION_CHEAKER be UEW_ADMISSION_CHECKER? It's best to have things spelt correctly as it is easier to type them.

@ennkay In that case there is probably no record matching the condition in the database. — Andrew Morton, Jun 30 '20 at 08:21

incorrect syntax near "=" in vb.net

2 Answers2