add the user info at vb to access

[Xiao_1]

syntax error in INSERT INTO statement what is that mean?

[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.

[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.

[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()

[Xiao_1]

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

[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

[Xiao_1]

like this !
1202a89aab68f5dc48f6214961bde255

[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

[pritaeas]

It's the single quote in your address. It needs escaping.

[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

[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.

[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.

[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

[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

[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.

[Xiao_1]

what is it mean?

error on Result = cmd.ExecuteNonQuery()

[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.

[Xiao_1]

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

[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