-1

getting syntax error into insert i don't know why :(

Private Sub Button1_Click(By Val sender As System.Object, By Val e As System.EventArgs) Handles Button1.Click

    If txtContact.Text.Trim.Length = 0 Then
        MessageBox.Show("Enter Contact Number.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        Exit Sub
    End If
    If ComboBox1.Text.Trim.Length = 0 Then
        MessageBox.Show("Please Select Agent", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        Exit Sub
    End If
    If ComboBox2.Text.Trim.Length = 0 Then
        MessageBox.Show("Please Select Agent", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        Exit Sub
    End If

    If ComboBox44.Text.Trim.Length = 0 Then
        MessageBox.Show("Please Select Agent", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        Exit Sub
    End If

    Dim sqlCommand As String
    sqlCommand = "INSERT INTO Personss (names, contactnumero, emailadds, pages, projects, agents, sources, months, days, years, times, agents1, transactions, months1, days1, years1, times1, agents2, transactions1, months2, days2, years2, times2, agents3, months3, days3, years3, remarks, months4, days4, years5, remarks1, months5, days5, years5, remarks2 ) VALUES ('" & txtbname.Text & "', '" & txtContact.Text & "', '" & txtEmail.Text & "','" & txtWeb.Text & "', '" & ComboBox2.Text & "', '" & ComboBox1.Text & "', '" & ComboBox44.Text & "', '" & cmbMonth.Text & "', '" & cmbDay.Text & "', '" & cmbYear.Text & "', '" & cmbYearTime.Text & "', '" & Combobox4.Text & "' , '" & ComboBox10.Text & "' , '" & cmbMonth1.Text & "' , '" & cmbDay1.Text & "' , '" & cmbYear1.Text & "' , '" & cmbYearTime2.Text & "' , '" & ComboBox5.Text & "' , '" & ComboBox11.Text & "' , '" & cmbMonth2.Text & "' , '" & cmbDay2.Text & "' , '" & cmbYear2.Text & "', '" & cmbYearTime3.Text & "', '" & cmbMonth3.Text & "', '" & cmbDay3.Text & "', '" & cmbYear3.Text & "', '" & TextBox2.Text & "', '" & cmbMonth4.Text & "', '" & cmbDay4.Text & "', '" & cmbYear4.Text & "', '" & TextBox3.Text & "', '" & cmbMonth5.Text & "', '" & cmdDay5.Text & "','" & cmbYear5.Text & "' , '" & TextBox4.Text & "')"
    If performNonQuery(connectionString, sqlCommand) Then

        MessageBox.Show("Successfully Updated", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        Me.Hide()
        Mainpage.Show()
    Else
        MessageBox.Show("Please Fill Up Completely", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If
End Sub
3
Contributors
2
Replies
14
Views
1 Year
Discussion Span
Last Post by Reverend Jim
0

It could be the data type of some of your database fields. For instance you have fields called months, days, years ,age, etc. These could be defined as type Integer. If that is the case then you need to leave out the single quotes in your statement:

sqlCommand = "INSERT INTO Personss (names, months, days, years) VALUES ('" & txtbname.Text & "', " & cmbMonth.Text & ", " & cmbDay.Text & ", " & cmbYear.Text & ")"

Also, if you have a date type the single quotes need to be replaced with #. For example.
sqlCommand = "INSERT INTO Personss (date) VALUES (#" & dateValue.Text & "#)"

Hope this helps!

0

The trouble with posting

sqlCommand = "INSERT INTO Personss (names, contactnumero, emailadds, pages, projects, agents, sources, months, days, years, times, agents1, transactions, months1, days1, years1, times1, agents2, transactions1, months2, days2, years2, times2, agents3, months3, days3, years3, remarks, months4, days4, years5, remarks1, months5, days5, years5, remarks2 ) VALUES ('" & txtbname.Text & "', '" & txtContact.Text & "', '" & txtEmail.Text & "','" & txtWeb.Text & "', '" & ComboBox2.Text & "', '" & ComboBox1.Text & "', '" & ComboBox44.Text & "', '" & cmbMonth.Text & "', '" & cmbDay.Text & "', '" & cmbYear.Text & "', '" & cmbYearTime.Text & "', '" & Combobox4.Text & "' , '" & ComboBox10.Text & "' , '" & cmbMonth1.Text & "' , '" & cmbDay1.Text & "' , '" & cmbYear1.Text & "' , '" & cmbYearTime2.Text & "' , '" & ComboBox5.Text & "' , '" & ComboBox11.Text & "' , '" & cmbMonth2.Text & "' , '" & cmbDay2.Text & "' , '" & cmbYear2.Text & "', '" & cmbYearTime3.Text & "', '" & cmbMonth3.Text & "', '" & cmbDay3.Text & "', '" & cmbYear3.Text & "', '" & TextBox2.Text & "', '" & cmbMonth4.Text & "', '" & cmbDay4.Text & "', '" & cmbYear4.Text & "', '" & TextBox3.Text & "', '" & cmbMonth5.Text & "', '" & cmdDay5.Text & "','" & cmbYear5.Text & "' , '" & TextBox4.Text & "')"

and asking "where is the syntax error" is that without knowing the values of all of the controls you are patching in, it is impossible to know what the actual query is. It may be a mismatched quote but I am certainly not going to wade through multiple lines of concatenations to determine if a quote is mismatched or misused. I've responded to dozens of SQL query questions with "you should be using parameterized queries". That would make your code easier to read and debug. At the very least if you had done

sqlCommand = "INSERT INTO Personss (names, contactnumero, emailadds, pages, projects, agents, sources, months, days, years, times, agents1, transactions, months1, days1, years1, times1, agents2, transactions1, months2, days2, years2, times2, agents3, months3, days3, years3, remarks, months4, days4, years5, remarks1, months5, days5, years5, remarks2 ) VALUES ('" & txtbname.Text & "', '" & txtContact.Text & "', '" & txtEmail.Text & "','" & txtWeb.Text & "', '" & ComboBox2.Text & "', '" & ComboBox1.Text & "', '" & ComboBox44.Text & "', '" & cmbMonth.Text & "', '" & cmbDay.Text & "', '" & cmbYear.Text & "', '" & cmbYearTime.Text & "', '" & Combobox4.Text & "' , '" & ComboBox10.Text & "' , '" & cmbMonth1.Text & "' , '" & cmbDay1.Text & "' , '" & cmbYear1.Text & "' , '" & cmbYearTime2.Text & "' , '" & ComboBox5.Text & "' , '" & ComboBox11.Text & "' , '" & cmbMonth2.Text & "' , '" & cmbDay2.Text & "' , '" & cmbYear2.Text & "', '" & cmbYearTime3.Text & "', '" & cmbMonth3.Text & "', '" & cmbDay3.Text & "', '" & cmbYear3.Text & "', '" & TextBox2.Text & "', '" & cmbMonth4.Text & "', '" & cmbDay4.Text & "', '" & cmbYear4.Text & "', '" & TextBox3.Text & "', '" & cmbMonth5.Text & "', '" & cmdDay5.Text & "','" & cmbYear5.Text & "' , '" & TextBox4.Text & "')"

Debug.WriteLine(sqlCommand)

then posted the output we would have had a reasonable chance to spot the error. However, even seeing that would not tell us if a field is mismatched to the underlying type of a particular column.

Edited by Reverend Jim

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.