email.txt error when saving to database

[Galbatorix]

hi,
I am using an access database and i have a form with a text box for entering the email address. I keep on getting errors each time i save to the database, below is the code i use

  Try
            sql = "INSERT INTO customers (fname,lname,mobileNo,email,countryID,IDTypeID,identification,roomNo,dateIn,daysIn,dateOut,noOfAdults,noOfChildren,adultTotal,childrenTotal,Total)" & _
                                "VALUES(" & txtfname.Text & "," & txtlname.Text & "," & txtphone.Text & "," & txtemail.Text.ToLower.ToString & "" & _
                                "," & cbocountry.SelectedIndex & "," & cboIdType.SelectedIndex & "," & txtId.Text & "," & rumNum & "" & _
                                "," & dtpCheckIn.Value.ToString & "," & txtDaysStaying.Text & "," & dtpCheckOut.Value.ToString & "" & _
                                "," & txtNoOfAdults.Text & "," & txtNoOfChildren.Text & "," & txtAdultTotal.Text & "" & _
                                "," & txtChildrenTotal.Text & "," & txtTotalAmount.Text & ")"
            Using cmdInsert As New OleDb.OleDbCommand(sql, con)
                connectDatabase()
                cmdInsert.ExecuteNonQuery()
                DisconnectDB()
                dachk.Update(dsChk, "client")
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Clipboard.SetText(ex.ToString)
        End Try

and here is exception error i am getting

System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '[e-mail]'.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at Albergo.frmCheckIn.saveToDB() in C:\Users\gman\Documents\Visual Studio 2008\Projects\Albergo\Albergo\frmCheckIn.vb:line 76

Help would definitely be grateful

[Reverend Jim]

You need to have single quotes around text fields. For example

INSERT INTO myTable (lname,fname) VALUES(Flintstone,Fred)

is not valid but

INSERT INTO myTable (lname,fname) VALUES('Flintstone','Fred')

is. Also, you should be using parameterized queries. It's not only more secure but it makes the code easier to read. See here for sample code.

[G_Waddell]

Hi,

I'm seeing lots of text values being passed in but no string delimiters e.g.

'Like you have
sql ="INSERT INTO MYtable(MyField, MyField2) VALUES(" &MyValue &", " &MyValue2 &")" 

'Example where MyField is a text (string type Field) and MyField2 isnumeric -spot the difference
sql ="INSERT INTO MyTable(MyField, MyField2) VALUES('" &MyValue &"', " &MyValue2 &")"

Also, it should be pointed out that you should really try to use (at the least,) parameterised queries to avoid SQL injection attacks (where nasty people "sneak" SQL commands into your inputs to delete tables etc.)

This link, Working with OLEDB parameters will show you how to use parameters with OLEDB - As you are using a Text command though you can not name your parameters so you must add them in the correct order e.g.

'We don't need to use delimiter as parameters will specifiy their Type
SQL ="INSERT INTO Employee(FirstName, LastName, StartName) VALUES(?, ?, ?)"

dim Cmd as new oleDBCommand(con)

With Cmd
    .CommandType = CommandType.Text
    .Text = sql
    'Now I will demonstrate adding the parameters
    'Using .AddwithValue this will use the value to decide the parameter Type
    .parameters.AddWithValue("@FirstName",Trim(txtFirstName.Text))
    .parameters.AddWithValue("@LastName", Trim(TxtLastName.Text))
    'Now I'll use .Add to specify the type exactly in this case convert to date from a string
    .parameters.add("@StartDate", OleDbType.Date).Value = Trim(TxtStartDate.Text)
End With

Cmd.Execute()

[Galbatorix]

Thanks reverend Jim, you were right. Wadell, i am checking out the working with oledb parameters and i will get back to you