[nimesh]

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.

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.

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

[venami]

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 & "')"

[nimesh]

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.

[nimesh]

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?

[nimesh]

Now I'm stuck in Update section