CODEWITHSUNDEEP
sqlvb.net
eng.bassel
eng.bassel

Reputation: 77

Incorrect syntax near '#'. VB.net with SQL database

i'm trying to get the Max field where date = today using this code:

    Dim todaydate = Format(Today.Date, "dd/MM/yyyy")
    Dim sql1 As String = "Select max(snum) From tblbill where idate =  #" & todaydate & "# "
    Dim conn1 As SqlConnection = New SqlConnection(constr)
    Dim cmd1 As SqlCommand = New SqlCommand(sql1, conn1)
    conn1.Open()
    Dim dr1 As SqlDataReader = cmd1.ExecuteReader
    dr1.Read()
    If IsDBNull(dr1(0)) Then
        TextBox6.Text = 1
    Else
        TextBox6.Text = dr1(0) + 1
    End If
    dr1.Close()
    cmd1.Dispose()
    conn1.Close()

but when run the app i got this error: Incorrect syntax near '#'. may anyone help please!

Upvotes: 2

Views: 1125

Answers (2)

GarethD
GarethD

Reputation: 69769

First and foremost USE PARAMETERISED QUERIES, concatenating strings is vulnerable to malformed SQL, malicious SQL Injection, and conversion errors, in addition it stops query plan re-use because a new plan is created for every different value passed. This already solves your issue, because you do not need to worry about what qualifiers to use for what datatype (as pointed out in a comment you need to use ' instead of # which is for MS Access), it also means you don't need to worry about whether the format is DD/MM/YYYY or MM/DD/YYYY, you are telling the SqlCommand to expect a date, so regional settings will not affect anything.

Secondly, it is a good idea to use Using blocks to let your IDisposable objects clean themselves up:

Dim sql1 As String = "Select max(snum) From tblbill where idate =  @Date "
Using conn1 As New SqlConnection(constr)
Using cmd1 As New SqlCommand(sql1, conn1)

    cmd1.Parameters.Add("@Date", SqlDbType.DateTime).Value = Today.Date
    conn1.Open()

    Using dr1 As SqlDataReader = cmd1.ExecuteReader
        If IsDBNull(dr1(0)) Then
            TextBox6.Text = 1
        Else
            TextBox6.Text = dr1(0) + 1
        End If
    End Using

End Using

Upvotes: 9

A.J
A.J

Reputation: 382

You need to use a single quote instead of hash here, it would be better if you could use string.format as well (instead of manually concatenating)

Dim sql1 As String = String.Format("select max(snum) from tblbill where idate='{0}'",todaydate)

Upvotes: -3

Related Questions