CODEWITHSUNDEEP
asp.net.netasp.net-mvc
Maira Alvi
Maira Alvi

Reputation: 1

I need solution for this, regarding the exception which I get in my code

This is my piece of code by which I am trying to insert data into my database. I didn't receive any syntax errors, but it does not submits the data.

 Sub submit(ByVal sender As Object, ByVal e As EventArgs)
    Dim objConn As New OleDbConnection
    Dim objCmd As New OleDbCommand
    Dim strConnString, strSQL As String

    strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("Mydatabase.mdb") & ""

    strSQL = "INSERT INTO tblRecord (CNIC,Names,City,Address,Number) " & " VALUES " & " ('" & cnic.Text & "','" & name.Text & "','" & city.Text & "','" & postal.Text & "','" & phone.Text & "')"
    objConn.ConnectionString = strConnString
    objConn.Open()

    With objCmd
        .Connection = objConn
        .CommandText = strSQL
        .CommandType = CommandType.Text
    End With

    Try
        objCmd.ExecuteNonQuery()
        Label1.Text = "Record Inserted Sucessfully."
        Label1.Visible = True
    Catch ex As Exception
        Label1.Visible = True
        Label1.Text = "Record Cannot Insert" & ex.ToString()
    End Try

    objConn.Close()

End Sub

This is the exception which I get:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ASP.registration_aspx.submit(Object sender, EventArgs e) in C:\Documents and Settings\Maira Alvi\My Documents\Visual Studio 2005\WebSites\WebSite3\Registration.aspx:line 32

Upvotes: 0

Views: 124

Answers (2)

pete
pete

Reputation: 25081

My guess is that one of your input fields contains an unescaped character (e.g., '). Switching over to a parameterized query not only solves that problem, but helps defend against other problems (like SQL Injection).

//change query to use parameters
strSQL = "INSERT INTO tblRecord (CNIC,Names,City,Address,Number) VALUES (?,?,?,?,?);"

...

With objCmd
    .Connection = objConn
    .CommandText = strSQL
    .CommandType = CommandType.Text
    //add parameters using inputs for values
    .Parameters.Add("?", OleDbType.VarChar, 50).Value = cnic.Text; //assumes varchar(50)
    .Parameters.Add("?", OleDbType.VarChar, 50).Value = name.Text; //assumes varchar(50)
    .Parameters.Add("?", OleDbType.VarChar, 50).Value = city.Text; //assumes varchar(50)
    .Parameters.Add("?", OleDbType.VarChar, 50).Value = postal.Text; //assumes varchar(50)
    .Parameters.Add("?", OleDbType.VarChar, 50).Value = phone.Text; //assumes varchar(50)
End With

Upvotes: 1

CodeRedick
CodeRedick

Reputation: 7415

You DO have a syntax error, it says so right in your exception! Take a look at the string you're building for the SQL Insert statement. I'm pretty sure there's a ' missing at the end somewhere.

Basically though, that's a very bad way to build your SQL statement. It could be improved by using string.Format, but even then you would be open to having SQL Injection attacks.

It's much better to add the values using Parameters. Here's an example of how to do it right.

Upvotes: 0

Related Questions