1,105,333 Community Members

email.txt error when saving to database

Member Avatar
Galbatorix
Junior Poster in Training
52 posts since Jan 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 5 [?]
Skill Endorsements: 0 [?]
 
0
 

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 'mwanikiphillip@gmail.com'.
   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

Member Avatar
Reverend Jim
Noli mentula
5,393 posts since Aug 2010
Reputation Points: 744 [?]
Q&As Helped to Solve: 643 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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.

Member Avatar
G_Waddell
Practically a Posting Shark
821 posts since Nov 2009
Reputation Points: 131 [?]
Q&As Helped to Solve: 137 [?]
Skill Endorsements: 13 [?]
 
0
 

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()
Member Avatar
Galbatorix
Junior Poster in Training
52 posts since Jan 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 5 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Question Answered as of 1 Year Ago by G_Waddell and Reverend Jim
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article