I'm using the below code to update values from the web page to the access database. And I'm getting the error of "Syntax error in INSERT INTO statement". When I directly run the insert statement (which I get from this code) in the access database, the record gets added without any error. There is one more column of ID which is an autonumber field so I'm not taking it here. Code: Protected Sub AddNewSQLCode(ByVal sender As Object, ByVal e As EventArgs) Dim strOwner As String = Chr(34) & Session("loginname") & Chr(34) Dim strNodeText As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewNode"), TextBox).Text & Chr(34) Dim strSQLCode As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewSQL"), TextBox).Text & Chr(34) Dim strParam As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewParam"), TextBox).Text & Chr(34) Dim strComment As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewComm"), TextBox).Text & Chr(34) Try strSql = "insert into myreports(Owner, Node, IsTree, SQLCode, Param, Comment) values(" & strOwner & ", " & strNodeText & ", 0, " & strSQLCode & ", " & strParam & ", " & strComment & ")" con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("./App_Data/Reports.mdb") & ";User Id=admin;Password=;") com = New OleDbCommand(strSql, con) com.Connection.Open() com.ExecuteNonQuery() com.Connection.Close() disp_myrep() Catch msg = "<h1 class=""ErrMsg"">Error Encountered: </h1>" & "<br/><b><font color=""red"">Error Source: " & Err.Source & "<br/>Error Description: " & Err.Description & "</font></b>" msg = msg & strSql End Try If con.State = ConnectionState.Open Then con.Close() End If End Sub Similar code for my another program is working but I'm unable to trace where the problem is. I also tried another approach as in the below code, but this also gave the same error. Code: Protected Sub AddNewSQLCode(ByVal sender As Object, ByVal e As EventArgs) Dim strOwner As String = Chr(34) & Session("loginname") & Chr(34) Dim strNodeText As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewNode"), TextBox).Text & Chr(34) Dim strSQLCode As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewSQL"), TextBox).Text & Chr(34) Dim strParam As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewParam"), TextBox).Text & Chr(34) Dim strComment As String = Chr(34) & DirectCast(grdReport.FooterRow.FindControl("txtNewComm"), TextBox).Text & Chr(34) Try strSql = "insert into myreports(Owner, Node, IsTree, SQLCode, Param, Comment) values(@owner, @node, 0, @sql, @param, @comm)" con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("./App_Data/Reports.mdb") & ";User Id=admin;Password=;") com = New OleDbCommand(strSql, con) com.CommandText = strSql com.Parameters.AddWithValue("@owner", strOwner) com.Parameters.AddWithValue("@node", strNodeText) com.Parameters.AddWithValue("@sql", strSQLCode) com.Parameters.AddWithValue("@param", strParam) com.Parameters.AddWithValue("@comm", strComment) com.Connection.Open() com.ExecuteNonQuery() com.Connection.Close() disp_myrep() Catch msg = "<h1 class=""ErrMsg"">Error Encountered: </h1>" & "<br/><b><font color=""red"">Error Source: " & Err.Source & "<br/>Error Description: " & Err.Description & "</font></b>" msg = msg & strSql End Try If con.State = ConnectionState.Open Then con.Close() End If End Sub
Hi, I think you are not adding the quotes for the string that you are inserting into the database. Just try adding quotes like this for your string values: strSql = "insert into myreports(Owner, Node, IsTree, SQLCode, Param, Comment) values('" & strOwner & "', '" & strNodeText & "', 0, '" & strSQLCode & "', '" & strParam & "', '" & strComment & "')"
I'm doing that using chr(34) Dim strOwner As String = Chr(34) & Session("loginname") & Chr(34) I tried both ways but it did not work.
The autonumber field ID was giving problem. When I included it in the insert statement, the same code ran without any problems. so currently I'm fetching the max(ID) before the insert command to make things work. It's weird but this thing got working after 2-3 months of wait. Finally after giving up I posted my problem here. Does anyone know why this could have occurred?