locked
reg: linking of ms access db with asp.net web application(Syntax error in string in query expression) RRS feed

  • Question

  • User1046783394 posted

    hi all,

           i am new to asp.net programming i am trying to connect asp.net web application with ms access 2007 database. i have taken with two text boxes and when i enter some data in it and press submit button the data must load in access db i created with same fields.

    my code is :

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\NEW.accdb;User Id=admin;Password=;")
            Dim cmd As New OleDbCommand
            With cmd
                .CommandText = "INSERT INTO emp1(Empno,Ename) VALUES(" & Me.TextBox1.Text & ",'" & Me.TextBox2.Text & ");"
                .Connection = cn
                .Connection.Open()
                .ExecuteNonQuery()
                .Connection.Close()
                .Dispose()
            End With
            cn.Dispose()

    End Sub

    my error is 

    Syntax error in string in query expression

    can u suggest me what is the correct way of inserting data into access db and what is the 

    Syntax error in string in query expression i am using


    <input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">

    <input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">

    Monday, June 7, 2010 7:33 AM

Answers

  • User1508394307 posted

    String values in the insert statement must be placed within ' ' 

    that is your statement should look like 

    INSERT INTO emp1(Empno,Ename) VALUES('x', 'y')

    if you debug your statement you will see that your code produced only

    INSERT INTO emp1(Empno,Ename) VALUES(x, 'y);

    and that why you get the error

    if your first column (Empno) expected numeric values only then you should add ' at the end of your statement for ename

    .CommandText = "INSERT INTO emp1(Empno,Ename) VALUES(" & Me.TextBox1.Text & ",'" & Me.TextBox2.Text & "');" 

    otherwise you would need ' ' also for Empno.

    Note, that you should aware about sql injections and replace some characters in the statement to protect your database...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 7, 2010 8:01 AM

All replies

  • User1508394307 posted

    String values in the insert statement must be placed within ' ' 

    that is your statement should look like 

    INSERT INTO emp1(Empno,Ename) VALUES('x', 'y')

    if you debug your statement you will see that your code produced only

    INSERT INTO emp1(Empno,Ename) VALUES(x, 'y);

    and that why you get the error

    if your first column (Empno) expected numeric values only then you should add ' at the end of your statement for ename

    .CommandText = "INSERT INTO emp1(Empno,Ename) VALUES(" & Me.TextBox1.Text & ",'" & Me.TextBox2.Text & "');" 

    otherwise you would need ' ' also for Empno.

    Note, that you should aware about sql injections and replace some characters in the statement to protect your database...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 7, 2010 8:01 AM
  • User-1199946673 posted

    Note, that you should aware about sql injections and replace some characters in the statement to protect your database...
     

    Using parameterized queries is a better option!

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Monday, June 7, 2010 12:20 PM
  • User1508394307 posted

    Yes, I agree with the last comment.

    Monday, June 7, 2010 2:35 PM