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.

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

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

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.

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") + "')"

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.

Is id your primary key?

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

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.

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.