unhandled exception of type system.data.sqlclient....

[honeypot]

Hi - im getting the following error message when running my program. It happens when i click the update button which is supposed to update the database:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

The code is:

Dim SQL As System.Data.SqlClient.SqlCommand

SQL = Me.SqlConnection1.CreateCommand

Dim Leg As Integer

Dim T As String
T = "Update Postbook Set name='" & txtName.Text & "',"
T = T & "contact = '" & txtContact.Text & "',"
T = T & "ref = '" & txtRef.Text & "',"
T = T & "File Ref = '" & txtFileRef.Text & "',"
Dim myAddress As Array
myAddress = Split(txtAddress.Text, vbCrLf)
'FILL ADDRESS1
If UBound(myAddress) > 0 Then
T = T & "address1='" & myAddress(0) & "',"
Else
T = T & "address1='',"
End If
'FILL ADDRESS2
If UBound(myAddress) >= 1 Then
T = T & "address2='" & myAddress(1) & "',"
Else
T = T & "address2='',"
End If
'FILL ADDRESS3
If UBound(myAddress) >= 2 Then
T = T & "address3='" & myAddress(2) & "',"
Else
T = T & "address3='',"
End If
'FILL ADDRESS4
If UBound(myAddress) >= 3 Then
T = T & "address4='" & myAddress(3) & "',"
Else
T = T & "address4='',"
End If

T = T & "subject = '" & txtSubject.Text & "',"
T = T & "section = '" & cboSection.Text & "',"
T = T & "rec_date = '" & dtp1.Value & "',"
T = T & "amount = '" & txtAmount.Text & "',"
T = T & "re-dir = '" & chkReDir.Text & "',"
If chkReDir.Checked Then
T = T & "CheckField='Yes,'"
Else
T = T & "CheckField='No,'"
End If
T = T & "officer = '" & cboOfficer.Text & "',"
T = T & "acknow_req = '" & chkAckReq.Checked & "',"
If chkAckReq.Checked Then
T = T & "CheckField='Yes,'"
Else
T = T & "CheckField='No,'"
End If

T = T & "acknow date = '" & dtpAck.Value & "',"
T = T & "ack_sent = '" & chkAckSent.Checked & "',"
If chkAckSent.Checked Then
T = T & "CheckField='Yes,'"
Else
T = T & "CheckField='No,'"
End If
T = T & "reply req'd = '" & chkRep.Checked & "',"
If chkRep.Checked Then
T = T & "CheckField='Yes,'"
Else
T = T & "CheckField='No,'"
End If
T = T & "reply date = '" & dtpRep.Value & "',"
T = T & "notes = '" & txtNotes.Text & "' "

T = T & "where record no = " & txtRecord.Text
SQL.CommandText = T

Me.SqlConnection1.Open()

SQL.ExecuteNonQuery()

 

[Kris11]

Which means there is a syntax error in your SQL.
I think you have the comma in wrong place everywhere...

Instead of this
If chkReDir.Checked Then
T = T & "CheckField='Yes,'"

It should be

If chkReDir.Checked Then
T = T & "CheckField='Yes',"

Try changing this in all occurance and should be OK then

-Kris

 

[honeypot]

thanx..but i changed the code and still have the same error

 

[SqueakinSweep]

Best way to debug this is to write out your generated SQL string to a Text File, then copy and paste the contents into Query Analyser. This will then generate an error which you can track down. Its impossible to help without knowing what field types are defined in your SQL Table.




Sweep
...if it works dont mess with it

 

[honeypot]

yeh ive done that, seems to be a problem with datetime, which ive changed, but its still playing up!! thank you for replying anyway

 

[honeypot]

ive done everything that you suggested and now get the error:

the conversion of a char data type to a datetime data type resulted in an out of range datetime value.

ive checked the date format on both sql server and in my program and as far as i can see they should be compatible. The data in the sql server database is formatted to datetime and as you can see in my code this is formatted to short date. They both display the same data. .. i dont know what else to do????

 

[honeypot]

oh its ok now....i just changed the datetime format in sqlserver to nvarchar and it works