Go4Expert

Go4Expert (http://www.go4expert.com/)
-   ASP (http://www.go4expert.com/forums/asp/)
-   -   Unable to add records, syntax error in insert into statement (http://www.go4expert.com/forums/unable-add-records-syntax-error-insert-t19934/)

nimesh 31Oct2009 20:52

Unable to add records, syntax error in insert into statement
 
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: vb

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: vb

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 1Nov2009 17:02

Re: Unable to add records, syntax error in insert into statement
 
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 1Nov2009 21:01

Re: Unable to add records, syntax error in insert into statement
 
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 2Nov2009 02:19

Re: Unable to add records, syntax error in insert into statement
 
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 2Nov2009 03:09

Re: Unable to add records, syntax error in insert into statement
 
Now I'm stuck in Update section :(


All times are GMT +5.5. The time now is 15:46.