Check the manual that corresponds to your mysql server version

[samuel terngu]

I have created a table in mysql database table(tblmysq) with id(autoincrement),name(string),branch(string),sex(string),and dat(datetime).The following error occurs when i try to insert a record:check the manual that corresponds to your mysql server version for the right syntax near line 1.
My lines of code are :

    Try
    conn = New MySqlConnection(conStr)

        conn.Open()
        'Dim sql As String = String.Format("INSERT INTO tblmysq(name,branch,sex,dat) VALUES('{0}','{1}','{2}',{3}, TextBox1.Text , TextBox2.Text,ComboBox1.SelectedItem,DateTimePicker1.Value)")

        Dim sql As String = "INSERT INTO tblmysq(name,branch,sex,[dat])VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox1.SelectedItem & "'," + Format(DateTimePicker1.Value, "yyyy-MM-dd") + ")"

        com = New MySqlCommand(sql, conn)

        If com.ExecuteNonQuery Then
            MessageBox.Show("Save")
        Else
            MessageBox.Show("Operation not successful")
        End If
    Catch ex As Exception
        MessageBox.Show("Error:" & ex.ToString())
    End Try


    When i enclose dat in square bracket i.e [dat], the error ends with the value such as:
    ...you have an error near '[dat] VALUES ('paul','phc','male',2013-11-16)'at line 1.
    Thanks for help in anticipation.

[deceptikon]

That's MSSQL syntax. MySQL uses back ticks around column names instead of brackets:

"INSERT INTO tblmysq(name,branch,sex,`dat`)VALUES...

[samuel terngu]

Thanks deceptikon but i encountered this error with the sql statement below: incorrect datetime value '1986' for column dat.

Dim sql As String = "INSERT INTO tblmysq(id,name,branch,sex,dat)VALUES ( " + TextBox3.Text + ",'" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox1.SelectedItem & "'," + Format(DateTimePicker1.Value, "yyyy-MM-dd") + ")"

The error:'id' does not have default value is displayed with the sql statement below:

Dim sql As String = "INSERT INTO tblmysq(name,branch,sex,dat)VALUES ( '" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox1.SelectedItem & "'," + Format(DateTimePicker1.Value, "yyyy-MM-dd") + ")"

Once again thanks to all of you there who are willing to help me resolve the problem.

[deceptikon]

I suspect it's becuse you're not wrapping the date string in single quotes. MySQL allows you to do that with any type, so it might be a good idea to just do it unconditionally:

Dim sql As String = "INSERT INTO tblmysq(id,name,branch,sex,dat)VALUES('" + TextBox3.Text + "','" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox1.SelectedItem & "','" + Format(DateTimePicker1.Value, "yyyy-MM-dd") + "')"

[samuel terngu]

Thanks,i added the date string in single quotes but i still encountered the error:
Mysql.Data.Mysqlclient.MysqlException:the field 'id' does not have a default value.

[deceptikon]

Is id your primary key?

[samuel terngu]

Yes id(autoincrement) is the first field in the database and it is my primary key.Thanks for your efforts.

[samuel terngu]

Great thanks to you,deceptikon,i have found lines of code below working perfectly:

 conn = New MySqlConnection(conStr)
        Try
            conn.Open()
            Dim sql As String = "INSERT INTO tblmysq(id,name,branch,sex,dat)VALUES(@id,@name,@branch,@sex,@dat)"

            com = New MySqlCommand(sql, conn)

            com.Parameters.AddWithValue("id", TextBox3.Text)
            com.Parameters.AddWithValue("name", TextBox1.Text)
            com.Parameters.AddWithValue("branch", TextBox2.Text)
            com.Parameters.AddWithValue("sex", ComboBox1.SelectedItem)
            com.Parameters.AddWithValue("dat", DateTimePicker1.Value)  'Format(DateTimePicker1.Value,"yyyy-MM-dd")only reduces part of time to zero(e.g 2013-11-15 00:00:00)


            If com.ExecuteNonQuery Then
                MessageBox.Show("Ok")
            Else
                MessageBox.Show("No")
            End If
        Catch ex As Exception
            MessageBox.Show("Error:" & ex.ToString())

        End Try

Once again my thanks go to all of you who are willing to contribute to the article.