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

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">

Answer 1

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

Answer 2

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

Answer 3

User1508394307 posted

Yes, I agree with the last comment.