[RESOLVED] erro in insert colon from textbox to DBaccess

[nader]

Here is a code to insert data to DBaccess from textbox. When I write a colon(') in the textbox to add to DB it caused error and show me this Message "Syntax error in string in query expression '''')'

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
       & "Data Source =" & Application.StartupPath & "\Store.mdb ")
        Dim cmd As New OleDbCommand
        cmd.Connection = conn
 
        'Add text to DB
        cmd.CommandText = "INSERT INTO TBinMaterial(inMaterial)  " & "values ('" & txtAddM.Text & "')  "
        cmd.CommandType = CommandType.Text
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

[gep13]

Hey,

You are going to want to avoid putting your variables directly into your query string like that, you are going to want to use Parameters on the command object instead:

Dim SqlString As String = "Insert Into TBinMaterial(inMaterial) Values (?)"
Using conn As New OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\Store.mdb ")
  Using cmd As New OleDbCommand(SqlString, conn)
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("inMaterial", txtAddM.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

Hope that helps!!

Gary

[nader]

I couldn't understand but here is your code I tried and casue same problem.

Dim SqlString As String = "INSERT INTO TBinMaterial(inMaterial)  " & "values ('" & Form2.txtAddM.Text & "')  "
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
             & "Data Source =" & Application.StartupPath & "\Store.mdb ")
        Dim cmd As New OleDbCommand(SqlString, conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("inMaterial", Form2.txtAddM.Text)
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

[gep13]

Hey,

I causes the same problem, because you are still putting the textbox.text directly into your query string. Doing this means that your query is subject to SQL injection, and that is a security risk.

In the example that I posted, the query became:

Insert Into TBinMaterial(inMaterial) Values (?)

Rather than what you have:

INSERT INTO TBinMaterial(inMaterial)  " & "values ('" & Form2.txtAddM.Text & "')

By doing it the way that I have suggested, when the query executes, the ? in the query is replaced with the value in the corresponding parameter. i.e. if txtAddM.Text had "nader" in it, then when the query is executed, it would actually have:

INSERT INTO TBinMaterial(inMaterial) values (nader)

You see what I mean.

Try directly replacing your code with what I have posted, and let me know if the same thing in happening.

Also, I notice that you have not used the Using Statements that I suggested. Using these statements mean that you don't have to explicitly call the conn.Close() as once the Using Statement finishes, it disposes on the connection. Just an idea, but in my opinion it makes the code easier to read.

Gary

[nader]

Thank you for help!

[gep13]

Not a problem at all. Happy to help.

Gary