Database Table Update

[squashjunkie]

Hi,

I am trying to update two fields in a database at the same time with the following code......

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click

Dim con As OleDb.OleDbConnection = OleDbConnection2

Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand

Dim paramdaystilldue As New OleDb.OleDbParameter

Dim paramsysdocid As New OleDb.OleDbParameter

Dim paramspare As New OleDb.OleDbParameter

Dim upSYSDOCID As Integer = CInt(lblsysdocid.Text)

Dim upDAYSTILLDUE As Integer = CInt(LBLDUEDAYS.Text)

cmd.Connection = con

With paramsysdocid

.ParameterName = "sysdocid"

.OleDbType = OleDb.OleDbType.Integer

.Size = 5

.Value = upSYSDOCID

End With

cmd.Parameters.Add(paramsysdocid)

With paramspare

.ParameterName = "Spare"

.OleDbType = OleDb.OleDbType.BSTR

.Size = 32

.Value = "U"

End With

cmd.Parameters.Add(paramspare)

With paramdaystilldue

.ParameterName = "DAYSTILLDUE"

.OleDbType = OleDb.OleDbType.Integer

.Size = 5

.Value = upDAYSTILLDUE

End With

cmd.Parameters.Add(paramdaystilldue)

MsgBox("Update Database?", MsgBoxStyle.OKCancel, "Update")

If MsgBoxResult.OK = 1 Then

con.Open()

cmd.CommandText = "UPDATE ARTRAN SET DAYSTILLDUE = " & CStr(LBLDUEDAYS.Text) & " WHERE SYSDOCID = " & CStr(lblsysdocid.Text)

cmd.CommandText = "UPDATE ARTRAN SET Spare = " & "U" & " WHERE SYSDOCID = " & CStr(lblsysdocid.Text)

cmd.ExecuteNonQuery()

con.Close()

End If

End Sub


However the code is failing at the UPDATE ARTRAN SPARE comand line. If I rem this line out, the update works fine, but with it in, the update crashes. The SPARE field is a string field, and the DAYSTILLDUE field is a numeric field.

Can anyone help?

TIA

P.S. My first project so I must apologise for the scrappy code and "extra" lines!

 

[Gambit_NI]

strings need to be enclosed in single quotes, numbers dont

cmd.CommandText = "UPDATE ARTRAN SET Spare = " & "'U'" & " WHERE SYSDOCID = " & CStr(lblsysdocid.Text)

at the minute your statment reads
UPDATE ARTRAN SET Spare=U WHERE SYSDOCID=SomeNumber

you want it to read
UPDATE ARTRAN SET Spare='U' WHERE SYSDOCID=SomeNumber

you should probably write it like this
cmd.CommandText = "UPDATE ARTRAN SET Spare = 'U' WHERE SYSDOCID = " & CInt(lblsysdocid.Text)

 

[squashjunkie]

Thx for the reply, that does work, always the very small parts which make it kinda frustrating sometimes!

 

[TechGnome]

A couple of things scream at me....
1) Why create the parameter objects if you don't even use them?
2) In one line you set the CommandText, and then in the next line, you set it again. You do realize that by doing that, the first command WILL NOT execute. This means that only Spare will be set and not DAYSTILLDUE.

Tg