Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    11

    Unanswered: Syntax error (missing operator) in query expression 'sai',sss''

    hi,

    i am new to asp.net i am trying to develop an asp.net application i,e registration form in asp.net as user interface and ms access 2007 database as backend.i am trying to make aconnection to ms access 2007 db and save the data entered by the user in asp.net application.

    i have saved my access db 2007 file in 'D drive' and my code in application is:

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=D:\example\access.accdb;Persist Security Info=False; User Id=;Password=;")
    Dim cmd As New OleDbCommand
    With cmd
    .CommandText = "insert into Table1([name_of_dealer],[dealer_id],[address],[mobile_no]) values(" & Me.TextBox1.Text & _
    "'," & Me.TextBox2.Text & "'," & Me.TextBox3.Text & "'," & Me.TextBox4.Text & ")"
    .Connection = cn
    .Connection.Open()
    .ExecuteNonQuery()
    .Connection.Close()
    .Dispose()
    End With
    cn.Dispose()



    End Sub
    End Class

    i am facing error saying

    Syntax error (missing operator) in query expression 'sai',sss''. so please kindly help me regarding this.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must enclose text values between single quotes (') for passing them as parameters in a SQL statement, so it should be
    Code:
    VALUES('" & Me.TextBox1.Text & "', '" & ...
    at least for every column of Table1 that is defined as Text.
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    11

    server error in project :overflow

    hi,
    thanks i have used single quotes as u said VALUES('" & Me.TextBox1.Text & "', '" & ... in my code but i am facing the error saying that overflow
    Exception Details: System.Data.OleDb.OleDbException: Overflow
    so please try to help me out.
    and my modified code is :
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=D:\example\access.accdb;Persist Security Info=False; User Id=admin;Password=;")
    Dim cmd As New OleDbCommand
    With cmd
    .CommandText = "insert into Table1([name_of_dealer],[dealer_id],[address],[mobile_no]) values('" & Me.TextBox1.Text & _
    "','" & Me.TextBox2.Text & "','" & Me.TextBox3.Text & "','" & Me.TextBox4.Text & "')"
    .Connection = cn
    .Connection.Open()
    .ExecuteNonQuery()
    .Connection.Close()
    .Dispose()
    End With
    cn.Dispose()
    End Sub
    End Class

    please try to help me out.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    rather than how you SQL looks I'd strongly suggest you put a watch/debug on the SQL. what you are after ios to find what is actually being sent to the SQL engine (Acccess 2007 in this case).
    my suspicion is that your single/double quotes are out of kilter, or you are putting quotes around a numeric column. that would fit with the 'overflow' message

    if this was VB or VBA I'd use the chr() function, probably chr$(34) to insert a " in place of hardcodoing such a value..... just to make the code more readable, I don't do ASP.NET so I don't knwo what the equivalent would be


    another thin to bear in mind that the ' character is legal in some names eg O'Callaghan and so on. Ive yet to come accross a name using the " sysmbol. so you may need to sanitise your user input to trap for such an occurance
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •