update fails with command object even though SQL statement works in RDMS

[rvermeulen]

Hi folks,

 

I'm trying to update an Access 2000 table with my asp.net application. I've debugged the statement and the whole statement works with my command object, except when I add the last column to update. I get a Syntax error in UPDATE statement.

 

However, if I cut and paste the statement into Access and run it, it updates the table.

 

here is my code if anyone see's something wrong with it.

 

Much appreciated.

Thanks !

Rick

 

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

 

conn.Open()

Dim strSQL As New StringBuilder(275)

 

 

strSQL.Append("UPDATE SALES set productid = " & ddlbProducts.SelectedItem.Value.ToString & ", Jan = " & txtJanuary.Text.ToString & ", Feb = " & txtFebruary.Text.ToString & ", Mar = " & txtMarch.Text.ToString & ", Apr = " & txtApril.Text.ToString & ", May = " & txtMay.Text.ToString & ", June = " & txtJune.Text.ToString)

strSQL.Append(", July = " & txtJuly.Text.ToString & ", Aug = " & txtAugust.Text.ToString & ", Sept = " & txtSeptember.Text.ToString & ", Oct = " & txtOctober.Text.ToString & ", Nov = " & txtNovember.Text.ToString & ", Dec = " & txtDecember.Text.ToString)

strSQL.Append(" where ID = " & LabelID.Text.ToString)

 

Dim cmdUpdate As New OleDbCommand(strSQL.ToString, conn)

 

cmdUpdate.ExecuteNonQuery()

conn.Close()

 

End Sub

 

It fails when I add the Dec column. If I comment that column out and run it through asp.net, it updates correctly.

 

Any suggestions ?

[Travis]

This might be the problem:

 

Dim strSQL As New StringBuilder(275 )

 

You have stated that the variable will only hold 275 characters. Its possible that the strSQL is being truncated.

[rvermeulen]

Travis,

 

I checked the strsql.length and it's only 157 characters so I know it's not being truncated.

 

Any other thoughts ?

 

Rick