Question update issue

[nkotbox]

I am creating a database frontend with vb.net. The database is in access. I can update a record if I do not update any text fields. (comment all the text fields out of the sql)
Also, a common SerialNO will be like 055555. When I update this field, it changes to 55555 even though in the database it is a text field. Can someone help with the text filed issues.

DT1 = System.Convert.ToDateTime(CLSDateTB.Text)

Dim sqlstr As String = "UPDATE [Call Records] SET zaxis = " & ZaxisCheck.Checked
        sqlstr = sqlstr & ", assistgas = " & AssistCheck.Checked
        sqlstr = sqlstr & ", homing = " & EncoderCheck.Checked
        sqlstr = sqlstr & ", cutting = " & CuttingCheck.Checked
        sqlstr = sqlstr & ", nesting = " & NestingCheck.Checked
        sqlstr = sqlstr & ", electrical = " & ElecCheck.Checked
        sqlstr = sqlstr & ", pallets = " & PalletCheck.Checked
        sqlstr = sqlstr & ", resonator = " & ResonatorCheck.Checked
        sqlstr = sqlstr & ", mmhs = " & MMHSCheck.Checked
        sqlstr = sqlstr & ", chiller = " & ChillerCheck.Checked
        sqlstr = sqlstr & ", balltxfer = " & BallXferCheck.Checked
        sqlstr = sqlstr & ", SerialNO = " & serialnumbTB.Text
        sqlstr = sqlstr & ", Log = " & CLSLogTB.Text
        sqlstr = sqlstr & ", dt = " & "'DT1'"
        sqlstr = sqlstr & ", drives = " & DrivesCheck.Checked
        sqlstr = sqlstr & ", optics = " & OpticsCheck.Checked
        sqlstr = sqlstr & ", controls = " & ControlsHCheck.Checked
        sqlstr = sqlstr & ", other = " & OtherCheck.Checked
        sqlstr = sqlstr & ", Contact = " & CLSContactTB.Text
        sqlstr = sqlstr & ", users = " & usersTB.text
        sqlstr = sqlstr & ", [Serviceman call in] = " & ServicemanCallInCheck.Checked
        sqlstr = sqlstr & ", [Deformable Optic] = " & DeformCheck.Checked
        sqlstr = sqlstr & ", Deformable = " & DeformCheck.Checked
        sqlstr = sqlstr & ", [Software(CNC/PLC)] = " & SoftwareCheck.Checked
        sqlstr = sqlstr & ", [Cutting Head] = " & CuttingHeadCheck.Checked
        sqlstr = sqlstr & ", parts = " & PartsCheck.Checked
        sqlstr = sqlstr & " WHERE [Call RecordsID] = " & CallID
        sqlstr = sqlstr
        Using cmd As New OleDb.OleDbCommand(sqlstr, Login.con)
            cmd.ExecuteNonQuery()
        End Using

 

[jmcilhinney]

Don't ever use string concatenation to insert variables into SQL code. Always use parameters. Issues like this just don't come up when you do it the right way. For more information, follow the Blog link in my signature and check out my post on ADO.NET parameters.

 

[nkotbox]

database

Your site is very informative, but I still have an issue. The error I get is error in Update statement. Below is my code and I have copied the update statement as well.

 Dim sql As String = "UPDATE [Call Records] SET (zaxis, assistgas, homing, cutting, nesting, electrical, pallets, resonator, mmhs, chiller, balltxfer, SerialNO, Log, dt, drives, optics, controls, other, Contact, users, [Serviceman call in], [Deformable Optic], Deformable, [Software(CNC/PLC)], [Cutting Head], parts) " & _
                     "VALUES (@zaxis, @assistgas, @homing, @cutting, @nesting, @electrical, @pallets, @resonator, @mmhs, @chiller, @balltxfer, @SerialNO, @Log, @dt, @drives, @optics, @controls, @other, @Contact, @users, @Servicemancallin, @DeformableOptic, @Deformable, @Software, @CuttingHead, @parts) WHERE [Call RecordsID] =  @CallID"
        Dim myCommand As New OleDb.OleDbCommand(sql, Login.con)

        With myCommand.Parameters
            .AddWithValue("@zaxis", ZaxisCheck.Checked)
            .AddWithValue("@assistgas", AssistCheck.Checked)
            .AddWithValue("@homing", EncoderCheck.Checked)
            .AddWithValue("@cutting", CuttingCheck.Checked)
            .AddWithValue("@nesting", NestingCheck.Checked)
            .AddWithValue("@electrical", ElecCheck.Checked)
            .AddWithValue("@pallets", PalletCheck.Checked)
            .AddWithValue("@resonator", ResonatorCheck.Checked)
            .AddWithValue("@mmhs", MMHSCheck.Checked)
            .AddWithValue("@chiller", ChillerCheck.Checked)
            .AddWithValue("@balltxfer", BallXferCheck.Checked)
            .AddWithValue("@SerialNO", CLSSerialTB.Text)
            .AddWithValue("@Log", CLSLogTB.Text)
            .AddWithValue("@dt", CDate(CLSDateTB.Text))
            .AddWithValue("@drives", DrivesCheck.Checked)
            .AddWithValue("@optics", OpticsCheck.Checked)
            .AddWithValue("@controls", ControlsHCheck.Checked)
            .AddWithValue("@other", OtherCheck.Checked)
            .AddWithValue("@Contact", CLSContactTB.Text)
            .AddWithValue("@users", CLSUsersTB.Text)
            .AddWithValue("@Servicemancallin", ServicemanCallInCheck.Checked)
            .AddWithValue("@DeformableOptic", DeformCheck.Checked)
            .AddWithValue("@Deformable", DeformCheck.Checked)
            .AddWithValue("@Software", SoftwareCheck.Checked)
            .AddWithValue("@CuttingHead", CuttingHeadCheck.Checked)
            .AddWithValue("@parts", PartsCheck.Checked)
            .AddWithValue("@CallID", CInt(CallID))
        End With
        MsgBox(sql.ToString)
        Try
            myCommand.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.ToString & " " & ex.Message)
        End Try

Here is the sql string

UPDATE [Call Records] SET (zaxis, assistgas, homing, cutting, nesting, electrical, pallets, resonator, mmhs, chiller, balltxfer, SerialNO, Log, dt, drives, optics, controls, other, Contact, users, [Serviceman call in], [Deformable Optic], Deformable, [Software(CNC/PLC)], [Cutting Head], parts) VALUES (@zaxis, @assistgas, @homing, @cutting, @nesting, @electrical, @pallets, @resonator, @mmhs, @chiller, @balltxfer, @SerialNO, @Log, @dt, @drives, @optics, @controls, @other, @Contact, @users, @Servicemancallin, @DeformableOptic, @Deformable, @Software, @CuttingHead, @parts) WHERE [Call RecordsID] =  @CallID

Thanks for any help. I cannot see any issue unless it does not like the database fields with spaces.

 

[JohnH]

Take a closer look at the syntax: SQL UPDATE Statement

With Access that don't support named parameters you must also make sure there are same number of parameter objects as in query and that they are added in exact same order.

 

[nkotbox]

Are you saying that an access database will not accept using parameters?

 

[jmcilhinney]

What JohnH is saying is that you are using the wrong syntax for an UPDATE statement. You had the right syntax to begin with and then you butchered it into half and UPDATE and half an INSERT. There is no VALUES clause in an UPDATE statement. Go back to your original code and just put a parameter into each place where you're concatenation a variable into the String.

 

[nkotbox]

Will you please provide a small example as I am somewhat confused. The more I read the more confused I seem to be. I dont need a long example, maybe just include 1 variable and the where clause so that I can understand clearly. TIA.

 

[nkotbox]

OK, So I finally have the syntax that works. Is this the best way to do it or should I research a better way?

Private Sub UpdateDBase()
        Dim sql As String = "UPDATE [Call Records] SET zaxis = @zaxis, assistgas = @assistgas, homing = @homing, cutting = @cutting, nesting = @nesting, electrical = @electrical, pallets = @pallets, resonator = @resonator, mmhs = @mmhs, chiller = @chiller, balltxfer =  @balltxfer, SerialNO = @SerialNO, Log = @Log, dt = @dt, drives = @drives, optics = @optics, controls = @controls, other = @other, Contact = @Contact, users = @users, [Serviceman call in] = @Servicemancallin, [Deformable Optic] = @DeformableOptic, Deformable = @Deformable, [Software(CNC/PLC)] = @Software, [Cutting Head] = @CuttingHead, parts = @parts " & _
         "WHERE [Call RecordsID] =  " & CallID          
        Dim myCommand As New OleDb.OleDbCommand(sql, Login.con)
        DT1 = CDate(CLSDateTB.Text)
        With myCommand.Parameters
            .AddWithValue("@zaxis", ZaxisCheck.Checked)
            .AddWithValue("@assistgas", AssistCheck.Checked)
            .AddWithValue("@homing", EncoderCheck.Checked)
            .AddWithValue("@cutting", CuttingCheck.Checked)
            .AddWithValue("@nesting", NestingCheck.Checked)
            .AddWithValue("@electrical", ElecCheck.Checked)
            .AddWithValue("@pallets", PalletCheck.Checked)
            .AddWithValue("@resonator", ResonatorCheck.Checked)
            .AddWithValue("@mmhs", MMHSCheck.Checked)
            .AddWithValue("@chiller", ChillerCheck.Checked)
            .AddWithValue("@balltxfer", BallXferCheck.Checked)
            .AddWithValue("@SerialNO", CLSSerialTB.Text)
            .AddWithValue("@Log", CLSLogTB.Text)
            .AddWithValue("@dt", CDate(CLSDateTB.Text))
            .AddWithValue("@drives", DrivesCheck.Checked)
            .AddWithValue("@optics", OpticsCheck.Checked)
            .AddWithValue("@controls", ControlsHCheck.Checked)
            .AddWithValue("@other", OtherCheck.Checked)
            .AddWithValue("@Contact", CLSContactTB.Text)
            .AddWithValue("@users", CLSUsersTB.Text)
            .AddWithValue("@Servicemancallin", ServicemanCallInCheck.Checked)
            .AddWithValue("@DeformableOptic", DeformCheck.Checked)
            .AddWithValue("@Deformable", DeformCheck.Checked)
            .AddWithValue("@Software", SoftwareCheck.Checked)
            .AddWithValue("@CuttingHead", CuttingHeadCheck.Checked)
            .AddWithValue("@parts", PartsCheck.Checked)

        End With
        MsgBox(sql.ToString)
        Try
            myCommand.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.ToString & " " & ex.Message)
        End Try