Conversion from string to type integer

[samuel terngu]

My UPDATE vb.net code below displays this error message:conversion from string "UPDATE tblstaff SET fName='name' " to type 'integer' is not valid.Microsoft.visualBasic

 conn.Open()
            If conn.State = ConnectionState.Open Then
                com = New OleDb.OleDbCommand("SELECT *FROM tblstaff  ")
                com.Connection = conn
                Dim cb As New OleDb.OleDbCommandBuilder(da)
                da.Fill(ds, "Eeestaffinfo")
                Dim sql As String = String.Format("UPDATE tblstaff SET fName='{1}', lName='{2}', dob={3}, eMail='{4}', mobileNo='{5}', designation='{6}', address='{7}', fAppointment={8}, fpromotion={9}, lpromotion={10}, publications='{11}', position='{12}', leaveStatus='{13}' WHERE pfNumber='{0}'", txtPfNo.Text, txtFname.Text, txtLname.Text, Format(dtpDob.Value, "dd/MM/yyyy"), txtEmail.Text, txtPno.Text, txtDesignat.Text, txtAddress.Text, Format(dtpFappoint.Value, "dd/MM/yyyy"), Format(dtpFpromo.Value, "dd/MM/yyyy"), Format(dtpLpromo.Value, "dd/MM/yyyy"), txtPublicatn.Text, txtPosition.Text, txtLstatus.Text)
                com.CommandType = sql
                com.CommandType = CommandType.Text
                com.Connection = conn
                com.ExecuteNonQuery()
                com.Dispose()
                'conn.Close()
                MessageBox.Show("Good")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message & " " & ex.Source)
        End Try

        conn.Close()

is pfNumber a number. Also, consider using stored procedures. You are wide open to a SQL injection attack.

http://xkcd.com/327/

[Reverend Jim]

Please add the following line after the creation of the string sql

Debug.WriteLine(sql)

And post the result here. In order to tell you what is wrong with the query we have to see the query. To avoid SQL injection attacks you should be using parameterized queries

[samuel terngu]

Reverend Jim,i ran the code:Debug.WriteLine(sql) and the error message was:

Syntax error in UPDATE statement.Microsoft office Access Database Engine.

Also note that it is when i change the first name or any other string in the textbox on my form that the error,i described earlier is shown.My pfNumber is the primary key which is a string (e.g PF/2662) while fName could be any name for e.g John.There is also no incremental field in my Access database.Thanks for ur desire to help me.

[Reverend Jim]

That error message is from the DBMS when you try to execute the query. I need to the see the value of sql before you execute the query. Posting the error message alone is as useful as saying "what is wrong with my code" without posting the code for us to see.

[samuel terngu]

This is the value of the query:

"UPDATE tblstaff SET fName='Trues', lName='Jethro', dob=30/12/1988, eMail='[e-mail]', mobileNo='08145450902', designation='Graduate Assistant', address='No.23 Inikpi street,High Level', fAppointment=29/06/2012, fpromotion=02/02/2013, lpromotion=07/05/2013, publications='Journal on finger print identification system,Microcontroller configuration.', position='Assistant HOD', leaveStatus='Study leave fo two years' WHERE pfNumber='PF/2664'"

Here i editted fName from True to Trues but when i debug ,i see "UPDATE tblstaff SET fName='Trues' to type 'integer' is not valid.Microsoft Access Database Engine".

I also want to tell you that i executed the value of the query in Ms Access by pasting it in the design query used in Access and True in the DBMS changed to Trues.So,that is the confusion.Thanks for being desirous to help me.

[Reverend Jim]

I think the format for a date in Access queries is #07/05/2013. As for the other fields, the numeric fields should not have single quotes. If the field mobileNo is defined as a numeric then it should not have single quotes. If it is defined as a string (judging by the leading zero I'm assuming it is) then your query string then becomes

UPDATE tblstaff 
   SET fName='Trues', 
       lName='Jethro', 
       dob=#30/12/1988#, 
       eMail='[e-mail]', 
       mobileNo='08145450902', 
       designation='Graduate Assistant', 
       address='No.23 Inikpi street,High Level', 
       fAppointment=#29/06/2012#, 
       fpromotion=#02/02/2013#, 
       lpromotion=#07/05/2013#, 
       publications='Journal on finger print identification system,Microcontroller configuration.', 
       position='Assistant HOD', 
       leaveStatus='Study leave fo two years' 
       WHERE pfNumber='PF/2664'

and your builder becomes

Dim sql As String = String.Format(" _
    "UPDATE tblstaff " _
    "   SET fName='{1}',lName='{2}', dob=#{3}#, eMail='{4}', " _
    "    mobileNo='{5}', designation='{6}', address='{7}', " _
    "    fAppointment=#{8}#, fpromotion=#{9}#, lpromotion=#{10}#, " _
    "    publications='{11}', position='{12}', leaveStatus='{13}' " _
    WHERE pfNumber='{0}'", _

    etc.

but you would be better off using parameterized queries. There is an example using OleDb here

[samuel terngu]

Reverend Jim, i thank you so much for your assistance but iam sorry,i made a mistake on the error displayed.The actual error messsage is:

"UPDATE tblstaff SET fName='Trues' to type 'integer' is not valid.Micosoft VisualBasic.

Meanwhile,i tried to use the query builder with the hash(#) sign between the date values as you directed but the same error message was displayed.

Again i would have tried the parameterized query as you suggested but iam not using listview control on my form.I nevigate through the DataSet table records and i try to update them in textboxes and datetime pickers.Once again,i appreciate your contributions.

[Reverend Jim]

I'll put together a parameterized query for you (it's raining all day and I'm bored) but in the meantime I want you to post the structure of your table so I can see the declared type of each field.

[Reverend Jim]

While I was building the query (which looks like this)

Dim cmd As New OleDbCommand("", conn)
cmd.CommandText = "UPDATE tblstaff " _
                & "   SET fName=?, lName=?, dob=?, eMail=?,  mobileNo=?, " _
                & "       designation=?, address=?, fAppointment=?,  " _
                & "       fpromotion=?, lpromotion=?, publications=?, " _
                & "       position=?,leaveStatus=? " _
                & " WHERE pfNumber=?"

cmd.Parameters.AddWithValue("@fname ", txtFname.Text)
cmd.Parameters.AddWithValue("@lname ", txtLname.Text)
cmd.Parameters.AddWithValue("@dob   ", dtpDob.Value)
cmd.Parameters.AddWithValue("@email ", txtEmail.Text)
cmd.Parameters.AddWithValue("@mobile", txtPno.Text)
cmd.Parameters.AddWithValue("@desig ", txtDesignat.Text)
cmd.Parameters.AddWithValue("@addr  ", txtAddress.Text)
cmd.Parameters.AddWithValue("@fappt ", dtpFappoint.Value)
cmd.Parameters.AddWithValue("@fpromo", dtpFpromo.Value)
cmd.Parameters.AddWithValue("@lpromo", dtpLpromo.Value)
cmd.Parameters.AddWithValue("@pubs  ", txtPublicatn.Text)
cmd.Parameters.AddWithValue("@posn  ", txtPosition.Text)
cmd.Parameters.AddWithValue("@leave ", txtLstatus.Text)
cmd.Parameters.AddWithValue("@pfno  ", txtPfNo.Text)

I noticed that your query was trying to format txtPfNo.Text into the first parameter which is fName='{1}'. That is definitely a problem because none of the parameters line up. Note that when using parameterized queries with SqlClient (which you are not), the "@names" can be added in any order. When using it with OleDb (which is what you would use), the AddWithValue calls must be made in the same order in which the "?" parameters appear in your query.

I cannot actually test this query because I do not have Access installed.

[samuel terngu]

Reverend Jim,i sincerely thank you for your contributions.I was able to resolve the impasse i had when i created a new ms access database file with .mdb extension.