Why are number of query values and destination fields not the same?

[dexter delleva]

here is my code. dont seem to know whats the problem. i have 12 data fields in access excluding the ID field.

Dim strCon As String = "Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\dexter\Documents\desktop\csci14\database\Museum-final.mdb"
        Dim dSet As New DataSet
        Dim M_I As String
        Dim Fname As String
        Dim Lname As String
        Dim Age As Integer
        Dim Address As String
        Dim Email As String
        Dim Tel_Cell As String
        Dim status As String
        Dim Bill As Integer
        Dim Nat As String
        Dim Vis_no As Integer
        Dim DoV As String
 
        M_I = M_ITextBox.Text
        Fname = FirstNameTextBox.Text
        Lname = LastNameTextBox.Text
        Age = Val(AgeTextBox.Text)
        Address = AddressTextBox.Text
        Email = EmailTextBox.Text
        Tel_Cell = Tel_CellTextBox.Text
        status = StatusTextBox.Text
        Bill = Val(BillTextBox.Text)
        Nat = NationalityTextBox.Text
        Vis_no = Val(Visitor_NoTextBox.Text)
        DoV = DateTimePicker1.Text
 
        Try
            cn = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\dexter\Documents\Visual Studio 2010\Projects\Admission_form\Admission_form\Museum-final.mdb")
 
            cmd = New OleDbCommand("select * FROM Visitor", cn)
            cmd.CommandText = ("INSERT into Visitor ([Visitor_No],[FirstName],[M_I],[LastName],[Address],[Age],[email],[Tel/Cell],[Status],[Bill],[Nationality],[DateOfVisit]) values(" & Vis_no & "," & Fname & "," & M_I & "," & Lname & "," & Address & "," & Age & "," & Email & "," & Tel_Cell & "," & status & "," & Bill & "," & Nat & "," & DoV & ")")
            cn.Open()
            cmd.ExecuteNonQuery()
            clearTxtBox()
 
            cn.Close()
            cn.Dispose()
        Catch ex As System.Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
        End Try
 

[Stewart Ross]

At line 33 in the VALUES part of your statement you will need to enclose each and every text value in single quotes, like this:

values(" & Vis_no & ", '" & Fname & "','" & M_I & "','" & Lname & "','" & Address & "'," & Age & ",'" & Email & "','" & Tel_Cell & "','" & status & "'," & Bill & ",'" & Nat & "'," & DoV & ")")

Values inserted as string literals MUST be delimited by single quotes on either side of the literal value. I've shown examples of how to do this above, but I might not have caught them all so you'll have to make sure that you do so. Numeric values do not need delimiting, but please be aware that telephone numbers are stored as text strings so they require delimiting, as shown above.

-Stewart

[dexter delleva]

hello. tnx for your reply. i tried it but still got an error:

    Syntax error (missing operator)in query expression 
     ",0,'dex','bvn,",",0,",",",0,",'Monday'.

ive entered "dex" in FirstnameTexbox and bvn in M_Itextbox.the monday appears bcos ive set the value of DateOvtextbox to the value of the datetimepicker1 and set it to text as to my code above"

[Stewart Ross]

I added code tags to the error message you quoted above to make the characters shown clearer. If you look at the 'bvn value you will see that it is missing its closing single quote.

As I said in my first reply, values must be enclosed by single quotes, and this one clearly is not.

There also appear to be various double quotes orphaned later in your string, and the example use of single-quotes which I provided would not have resulted in the orphaned double quotes you show in the error statement.

Please bear in mind that in building the SQL statement the parts within double quotes belong to the SQL string itself. The single quotes delimit string literal values supplied from your controls or variables. These delimiters are within the double-quoted string sections, and typically start after the comma for the previous field, and end before the comma for the one after that, like this

"VALUES(" & var1 & ", '" & var2 & "', '" & var3 & "')"

You must make sure that you have a SQL string which is entirely correct in format. You may have to break it down further so that you can be certain that each part of the substring is correctly formed.
-Stewart