Member Avatar for Xiao_1

syntax error in INSERT INTO statement what is that mean?

  • 4 Contributors
  • 18 Replies
  • 243 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Santanu.Das
Member Avatar for pritaeas

That means that the query you are trying to execute is faulty.

Show the query if you need more information about what exactly is wrong.

Member Avatar for Santanu.Das

Syntax -->The grammatical rules and structural patterns governing the ordered use of appropriate words and symbols for issuing commands, writing code, etc., in a particular software application or programming language.

If you do not follow or write properly the above-mentioned grammatical rules and structural patterns, it causes for syntax error.
You did not follow the rules properly to create your "Insert Into Statement" in your previous post. So it occurs an error.

Member Avatar for Xiao_1 
 Dim sql As String = "INSERT INTO tbl_info ([AdminNo],UserName, [Course Title], ICNo, Gender, Address, [Data of Birth], TelNo, Email, Type) values ('" & txtname.Text & "', '" & txtadmin.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "','" & txtbirth.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
        sqlCom.Connection = Conn

        ***Dim result As Integer = sqlCom.ExecuteNonQuery***
Syntax error(missing error) in query expression "233 sangkeng st #6-12 (s'451233'), but in address i anyhow write like **ggghh** it can get it  

        sqlCom.Dispose()
        Conn.Close()
Member Avatar for Xiao_1

what is that mean?
i try it and find it nothing worng the word or spelling

Member Avatar for Santanu.Das

Do not understand about your error.
Pls. Post a picture of your exception message.
What is this "233 sangkeng st #6-12 (s'451233'),? and the line

but in address i anyhow write like ggghh it can get it

Member Avatar for Xiao_1

"233 sangkeng st #6-12 (s'451233'), is a address cant add in my access database

but if i write diiffere like this address gghh it can add it to access database

Member Avatar for Xiao_1

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    If String.IsNullOrWhiteSpace(txtadmin.Text) Or String.IsNullOrWhiteSpace(txtname.Text) Or String.IsNullOrWhiteSpace(txtcourse.Text) Or String.IsNullOrWhiteSpace(txtic.Text) Or String.IsNullOrWhiteSpace(txtgender.Text) Or String.IsNullOrWhiteSpace(txtaddress.Text) Or String.IsNullOrWhiteSpace(txtbirth.Text) Or String.IsNullOrWhiteSpace(txttel.Text) Or String.IsNullOrWhiteSpace(txtemail.Text) Or String.IsNullOrWhiteSpace(txttpye.Text) Then
        MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If

    Dim Conn As System.Data.OleDb.OleDbConnection
    Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"
    Conn = New System.Data.OleDb.OleDbConnection(ConnectionString)


    Try
    If Conn.State = ConnectionState.Open Then Conn.Close()
    Conn.Open()

    Dim sql As String = "INSERT INTO tbl_info ([AdminNo],UserName, [Course Title], ICNo, Gender, Address, [Data of Birth], TelNo, Email, Type) values ('" & txtname.Text & "', '" & txtadmin.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "','" & txtbirth.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"
    Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
    sqlCom.Connection = Conn

    Dim result As Integer = sqlCom.ExecuteNonQuery

    sqlCom.Dispose()
    Conn.Close()

    If result > 0 Then
        MessageBox.Show("Successfully created.")
    Else
        MessageBox.Show("Failure to create.")
    End If

    txtname.Text = ""
    txtadmin.Text = ""
    txtcourse.Text = ""
    txtic.Text = ""
    txtgender.Text = ""
    txtaddress.Text = ""
    txtbirth.Text = ""
    txttel.Text = ""
    txtemail.Text = ""
    txttpye.Text = ""
    txtadmin.Focus()



    Catch ex As Exception
    MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

End Class

Edited by Xiao_1
Member Avatar for Santanu.Das

Your codes occur an exception because you can never attach any quatation mark withen your supplid value to store in database. A single quatation is a syntax symbol in SQL. If you attach it manually in your data to store, SQL does not differ between syntax and data value.
But, you can attach a quatation mark withen the data value if you use parameterized SQL query Statement.

Member Avatar for Santanu.Das

Here, your SQL statement is an example of straightforward SQL statement.
But, by a straightforward SQL statement you could not insert any special characters like quotation mark or percentage symbol in your database.

I am taking here an example. Like

CMD.CommandText =
”UPDATE Customers SET CompanyName = ”’ &
txtCompany.Text & ””’ &
”WHERE CustomerID = ”’ & txtID.Text & ””’

Here,
txtCompany.Text = ’B’s Beverages’
and
txtID.Text = ’BSBEV’

If you attempt to execute this statement, SQL Server will reject it because it produces a syntax error.

The single quote is used to delimit literals, and there should be an even number of single quotes in the statement. The compiler determines that there’s an unclosed quotation mark in the statement and doesn’t execute it. If the same statement was written as a parameterized query, such as the following, you could pass the same company name to the statement:

CMD.CommandText = ”UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @ID”

CMD.Parameters.Add(”@CompanyName”, SqlDbType.VarChar, 40).Value = ”B’s Beverages”

CMD.Parameters.Add(”@ID”, SqlDbType.Char, 5).Value = ”BSBEV”

CMD.ExecuteNonQuery

The same is true for other special characters, such as the percentage symbol. It’s possible to escape the special symbols; you can replace the single-quote mark with two consecutive single quotes, but the most elegant method of handling special characters, such as quotation marks, percent signs, and so on, is to use parameterized queries. You just assign a string to the parameter and don’t have to worry about escaping any characters; the Command object will take care of all necessary substitutions.

Member Avatar for Xiao_1

is it like this:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    If String.IsNullOrWhiteSpace(txtadmin.Text) Or String.IsNullOrWhiteSpace(txtname.Text) Or String.IsNullOrWhiteSpace(txtcourse.Text) Or String.IsNullOrWhiteSpace(txtic.Text) Or String.IsNullOrWhiteSpace(txtgender.Text) Or String.IsNullOrWhiteSpace(txtaddress.Text) Or String.IsNullOrWhiteSpace(txtbirth.Text) Or String.IsNullOrWhiteSpace(txttel.Text) Or String.IsNullOrWhiteSpace(txtemail.Text) Or String.IsNullOrWhiteSpace(txttpye.Text) Then
        MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If

Using conn As New System.Data.OleDb.OleDbConnection

Dim OleDbConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"

Dim cmd As New System.Data.OleDb.OleDbCommand(sql, conn)
Dim Result As Integer
Dim sql As String = "INSERT INTO tbl_info ([AdminNo],UserName, [Course Title], ICNo, Gender"

cmd.Parameters.Add("AdminNo", SqlDbType.VarChar, 5).Value = txtadmin.Text

cmd.Parameters.Add("UserName", SqlDbType.VarChar, 12).Value = txtname.Text

cmd.Parameters.Add("Course Title", SqlDbType.VarChar, 20).Value = txtcourse.Text

cmd.Parameters.Add("ICNo", SqlDbType.VarChar, 5).Value = txtic.Text

cmd.Parameters.Add("Gender", SqlDbType.Char, 1).Value = txtgender.Text

cmd.Parameters.Add("Address", SqlDbType.VarChar, 40).Value = txtaddress.Text

cmd.Parameters.Add("Date of Birth", SqlDbType.Date).Value = txtbirth.Text

cmd.Parameters.Add("TelNo", SqlDbType.VarChar, 12).Value = txttel.Text

cmd.Parameters.Add("Email", SqlDbType.VarChar, 30).Value = txtemail.Text

cmd.Parameters.Add("Type", SqlDbType.VarChar, 5).Value = txttpye.Text

        conn.Open()
        Result = cmd.ExecuteNonQuery()




        If Result > 0 Then
            MessageBox.Show("Successfully created.")
        Else
            MessageBox.Show("Failure to create.")
        End If
    End Using
      conn.Close()
       If result > 0 Then
        MessageBox.Show("Successfully created.")
    Else
        MessageBox.Show("Failure to create.")
    End If

    txtname.Text = ""
    txtadmin.Text = ""
    txtcourse.Text = ""
    txtic.Text = ""
    txtgender.Text = ""
    txtaddress.Text = ""
    txtbirth.Text = ""
    txttel.Text = ""
    txtemail.Text = ""
    txttpye.Text = ""
    txtadmin.Focus()



    'Catch ex As Exception
    'MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    'End Try
End Sub

but sitll have some error on it

Edited by Xiao_1
Member Avatar for Santanu.Das

Ohhh! I am sorry. It was not in mind that you were using an OleDb Connection.
You SQL Statement is wrong.

I do here some modification in your code, it can help you. Use the symbol"@" at the begining of the name, when you are creating a parameter. This is convention and also syntactical.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    If String.IsNullOrWhiteSpace(txtadmin.Text) Or String.IsNullOrWhiteSpace(txtname.Text) Or String.IsNullOrWhiteSpace(txtcourse.Text) Or String.IsNullOrWhiteSpace(txtic.Text) Or String.IsNullOrWhiteSpace(txtgender.Text) Or String.IsNullOrWhiteSpace(txtaddress.Text) Or String.IsNullOrWhiteSpace(txtbirth.Text) Or String.IsNullOrWhiteSpace(txttel.Text) Or String.IsNullOrWhiteSpace(txtemail.Text) Or String.IsNullOrWhiteSpace(txttpye.Text) Then
        MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If

Using conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb")

    Dim Result As Integer
    Dim sql As String = "INSERT INTO tbl_info ([AdminNo],UserName, [Course Title], ICNo, Gender, Address, [Data of Birth], TelNo, Email, Type) values (@AdminNo, @UserName, @CourseTitle, @ICNo, @Gender, @Address, @DateofBirth, @TelNo, @Email, @Type)"

    Dim cmd As New System.Data.OleDb.OleDbCommand(sql, conn)


    cmd.Parameters.Add("@AdminNo", OleDbType.VarChar, 5).Value = txtadmin.Text

    cmd.Parameters.Add("@UserName", OleDbType.VarChar, 12).Value = txtname.Text

    cmd.Parameters.Add("@CourseTitle", OleDbType.VarChar, 20).Value = txtcourse.Text

    cmd.Parameters.Add("@ICNo", OleDbType.VarChar, 5).Value = txtic.Text

    cmd.Parameters.Add("@Gender", OleDbType.Char, 1).Value = txtgender.Text

    cmd.Parameters.Add("@Address", OleDbType.VarChar, 40).Value = txtaddress.Text

    cmd.Parameters.Add("@DateofBirth", OleDbType.Date).Value = txtbirth.Text

    cmd.Parameters.Add("@TelNo", OleDbType.VarChar, 12).Value = txttel.Text

    cmd.Parameters.Add("@Email", OleDbType.VarChar, 30).Value = txtemail.Text

    cmd.Parameters.Add("@Type", OleDbType.VarChar, 5).Value = txttpye.Text

        conn.Open()
        Result = cmd.ExecuteNonQuery()
        If Result > 0 Then
            MessageBox.Show("Successfully created.")
        Else
            MessageBox.Show("Failure to create.")
        End If

cmd.Parameters.Clear()
cmd.Dispose()
conn.Close()


If result > 0 Then
   MessageBox.Show("Successfully created.")
Else
   MessageBox.Show("Failure to create.")
End If

End Using

txtname.Text = ""
txtadmin.Text = ""
txtcourse.Text = ""
txtic.Text = ""
txtgender.Text = ""
txtaddress.Text = ""
txtbirth.Text = ""
txttel.Text = ""
txtemail.Text = ""
txttpye.Text = ""
txtadmin.Focus()
'Catch ex As Exception
'MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
'End Try
End Sub
Edited by Santanu.Das
Member Avatar for Reverend Jim

For future reference, when you post

Dim sql As String = "INSERT INTO tbl_info ([AdminNo],UserName, [Course Title], ICNo, Gender, Address, [Data of Birth], TelNo, Email, Type) values ('" & txtname.Text & "', '" & txtadmin.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "','" & txtbirth.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"

we have no easy way of determining what the syntax error might be because it could depend on the values of the various textboxes that you are concatenating. If you are going to post the above code you should also post the result of

MsgBox(sql)

so that we can see the actual query string being passed to the database engine.

Member Avatar for Xiao_1

what is it mean?

error on Result = cmd.ExecuteNonQuery()

Presentation.gif 162.72 KB
Edited by Xiao_1
Member Avatar for Santanu.Das

cmd.Parameters.Add("@DateofBirth", OleDbType.Date).Value = txtbirth.Text

How do you insert a text value in a Date Field?

Convert the text type to date type.

cmd.Parameters.Add("@DateofBirth", OleDbType.Date).Value = cDate(txtbirth.Text)

It would be more helpfull if you use MaskTextBox control or DateTimePicker Control to input date type value in the place of a TextBox control.

Member Avatar for Xiao_1

what do mean by date not valid i ready checked the speilling nothing worng..!!

Presentation1.gif 178.66 KB
Member Avatar for Santanu.Das

You can do it easily if you take a DateTimePicker Control instead of txtbirth textbox. In property window set its all properties as you desire.
and put the code

cmd.Parameters.Add("@DateofBirth", OleDbType.Date).Value = DateTimePicker1.Value
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.