1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Unable to add records, syntax error in insert into statement

Discussion in 'ASP' started by nimesh, Oct 31, 2009.

  1. nimesh

    nimesh New Member

    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
    
     
  2. venami

    venami New Member

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

    nimesh New Member

    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.
     
  4. nimesh

    nimesh New Member

    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?
     
  5. nimesh

    nimesh New Member

    Now I'm stuck in Update section :(
     

Share This Page