locked
Same query works in access but not in asp.net code? RRS feed

  • Question

  • User-1557221765 posted

     I have query in my code, which supposed to insert new row into a table in database:

    INSERT INTO Customer(Login,Password,fullName,addressStreet,addressTown,addressZip,Telephone,Email,Age) VALUES ('dobrozloczynca','zleiniedobre','Daniel','148 City Road','Sheffield','S2 5HN','07725900339','daniel.dzc@gmail.com','25')

     

    I am getting that string based on few textboxes.

    Now, when i paste that string into access query sql view and run it, it works fine. But it do not when i run it from my asp.net form...

    Thats how i connect to database:

     

    connStr = "PROVIDER = Microsoft.jet.OLEDB.4.0;Data Source = D:\booking_system.mdb"
    Dim connection As New Data.OleDb.OleDbConnection(connStr)
    Dim insertOledbCommand As New Data.OleDb.OleDbCommand
    dataAdapter = New Data.OleDb.OleDbDataAdapter
    
    sSql = "INSERT INTO ... //////here i create the string, which you have already seen
    
    LabelDebug.Text = sSql
    
    connection.Open()
    
    insertOledbCommand.Connection = connection
    insertOledbCommand.CommandText = sSql
    dataAdapter.InsertCommand = insertOledbCommand
    dataAdapter.InsertCommand.ExecuteNonQuery()
    connection.Close() 

      

    I do not think i am missing anything, and like i said- it is working in access itself.

    Oh one more thing! Login is primary key in table Customer.

    Any nideas?o.O

    Thursday, April 23, 2009 8:06 AM

Answers

  • User-1226263862 posted

    I don't work much with table adapters but It looks like your syntax is a little off, try this:

    Dim sSql as String = "INSERT INTO ......"
    Dim insertOledbCmd As OleDbCommand = New OleDbCommanD(sSql, connection)
    conection.Open()
    insertOledbCmd.ExecuteNonQuery()

    Now if you paramaratize your insert statement you will have to add those parameters too like this (sorry for sql not access)

     

    Protected Function ProcessSiteImportFile(ByVal SiteData As DataSet) As String
            Dim sqlConnectionString As String = connStr
            Dim connection As SqlConnection = New SqlConnection(sqlConnectionString)
    
            Dim command As SqlCommand = New SqlCommand("INSERT INTO dbo.iad_mstr (iad_addr1,iad_addr2,iad_addr3,iad_city,iad_pcode,iad_cell,iad_phone,iad_ext,iad_fax,iad_email,iad_created_by, iad_co_id) " _
                & "VALUES (@Address1,@Address2,@Address3,@City,@PostalCode,@Cell,@Phone,@Extension,@Fax,@Email,@CreatedBy,@CompanyID);SELECT SCOPE_IDENTITY()", connection)
    
            Dim newSiteID As String
            Dim companyID As Int64
            Try
                For i As Integer = 0 To SiteData.Tables(0).Rows.Count - 1
                    connection.Open()
                    command.Parameters.AddWithValue("@Address1", Left(SiteData.Tables(0).Rows(i).Item("Address 1").ToString(), 50))
                    command.Parameters.AddWithValue("@Address2", Left(SiteData.Tables(0).Rows(i).Item("Address 2").ToString(), 50))
                    command.Parameters.AddWithValue("@Address3", Left(SiteData.Tables(0).Rows(i).Item("Address 3").ToString(), 50))
                    command.Parameters.AddWithValue("@City", Left(SiteData.Tables(0).Rows(i).Item("City").ToString(), 30))
                    command.Parameters.AddWithValue("@PostalCode", Left(SiteData.Tables(0).Rows(i).Item("Postal Code").ToString(), 10))
                    command.Parameters.AddWithValue("@Phone", Left(SiteData.Tables(0).Rows(i).Item("Phone").ToString(), 20))
                    command.Parameters.AddWithValue("@Extension", Left(SiteData.Tables(0).Rows(i).Item("Extension").ToString(), 20))
                    command.Parameters.AddWithValue("@Cell", Left(SiteData.Tables(0).Rows(i).Item("Mobile Phone").ToString(), 20))
                    command.Parameters.AddWithValue("@Fax", Left(SiteData.Tables(0).Rows(i).Item("Fax").ToString(), 20))
                    command.Parameters.AddWithValue("@Email", Left(SiteData.Tables(0).Rows(i).Item("E-Mail").ToString(), 50))
                    command.Parameters.AddWithValue("@CreatedBy", Me.intUserID)
                    command.Parameters.AddWithValue("@CompanyID", companyID)
                    newSiteID = command.ExecuteScalar()
                    command.Parameters.Clear()
                    connection.Close()
                Next
                command.Dispose()
                Return ""
            Catch ex As Exception
                Return ex.Message + "<br />" + ex.StackTrace
            Finally
                If connection.State = ConnectionState.Open Then
                    connection.Close()
                End If
            End Try
        End Function
     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 23, 2009 10:45 AM
  • User-1199946673 posted

    In an SQL query, you should not use reserved words, an if you do, you should enclose the reserved word in brackets. In this example, password is a reserved word , so you should write [password]

    When working with a MDB file in an ASP.NET environment, you're actualy dealing with a Jet Database, not an Access Database. A list of reserved words in Jet can be found here. More information on Syntax errors can be found here

    Instead of building a query by concatenating a string or using a stringbuilder, you could better use parameterized queries:

    http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

    And one remark on your database design. Storing an age is ussualy not good practice, because the age will change in time. It makes more sence to store the birthday, so you could alway calculate the age!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 23, 2009 12:44 PM

All replies

  • User-1226263862 posted

    I don't work much with table adapters but It looks like your syntax is a little off, try this:

    Dim sSql as String = "INSERT INTO ......"
    Dim insertOledbCmd As OleDbCommand = New OleDbCommanD(sSql, connection)
    conection.Open()
    insertOledbCmd.ExecuteNonQuery()

    Now if you paramaratize your insert statement you will have to add those parameters too like this (sorry for sql not access)

     

    Protected Function ProcessSiteImportFile(ByVal SiteData As DataSet) As String
            Dim sqlConnectionString As String = connStr
            Dim connection As SqlConnection = New SqlConnection(sqlConnectionString)
    
            Dim command As SqlCommand = New SqlCommand("INSERT INTO dbo.iad_mstr (iad_addr1,iad_addr2,iad_addr3,iad_city,iad_pcode,iad_cell,iad_phone,iad_ext,iad_fax,iad_email,iad_created_by, iad_co_id) " _
                & "VALUES (@Address1,@Address2,@Address3,@City,@PostalCode,@Cell,@Phone,@Extension,@Fax,@Email,@CreatedBy,@CompanyID);SELECT SCOPE_IDENTITY()", connection)
    
            Dim newSiteID As String
            Dim companyID As Int64
            Try
                For i As Integer = 0 To SiteData.Tables(0).Rows.Count - 1
                    connection.Open()
                    command.Parameters.AddWithValue("@Address1", Left(SiteData.Tables(0).Rows(i).Item("Address 1").ToString(), 50))
                    command.Parameters.AddWithValue("@Address2", Left(SiteData.Tables(0).Rows(i).Item("Address 2").ToString(), 50))
                    command.Parameters.AddWithValue("@Address3", Left(SiteData.Tables(0).Rows(i).Item("Address 3").ToString(), 50))
                    command.Parameters.AddWithValue("@City", Left(SiteData.Tables(0).Rows(i).Item("City").ToString(), 30))
                    command.Parameters.AddWithValue("@PostalCode", Left(SiteData.Tables(0).Rows(i).Item("Postal Code").ToString(), 10))
                    command.Parameters.AddWithValue("@Phone", Left(SiteData.Tables(0).Rows(i).Item("Phone").ToString(), 20))
                    command.Parameters.AddWithValue("@Extension", Left(SiteData.Tables(0).Rows(i).Item("Extension").ToString(), 20))
                    command.Parameters.AddWithValue("@Cell", Left(SiteData.Tables(0).Rows(i).Item("Mobile Phone").ToString(), 20))
                    command.Parameters.AddWithValue("@Fax", Left(SiteData.Tables(0).Rows(i).Item("Fax").ToString(), 20))
                    command.Parameters.AddWithValue("@Email", Left(SiteData.Tables(0).Rows(i).Item("E-Mail").ToString(), 50))
                    command.Parameters.AddWithValue("@CreatedBy", Me.intUserID)
                    command.Parameters.AddWithValue("@CompanyID", companyID)
                    newSiteID = command.ExecuteScalar()
                    command.Parameters.Clear()
                    connection.Close()
                Next
                command.Dispose()
                Return ""
            Catch ex As Exception
                Return ex.Message + "<br />" + ex.StackTrace
            Finally
                If connection.State = ConnectionState.Open Then
                    connection.Close()
                End If
            End Try
        End Function
     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 23, 2009 10:45 AM
  • User-1199946673 posted

    In an SQL query, you should not use reserved words, an if you do, you should enclose the reserved word in brackets. In this example, password is a reserved word , so you should write [password]

    When working with a MDB file in an ASP.NET environment, you're actualy dealing with a Jet Database, not an Access Database. A list of reserved words in Jet can be found here. More information on Syntax errors can be found here

    Instead of building a query by concatenating a string or using a stringbuilder, you could better use parameterized queries:

    http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

    And one remark on your database design. Storing an age is ussualy not good practice, because the age will change in time. It makes more sence to store the birthday, so you could alway calculate the age!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 23, 2009 12:44 PM
  • User-1557221765 posted

    I have found what the problem was and soorted out my tables so I do not use reserved words before even that post was approved by moderator:)  Thanks anyway for answers!

    Thursday, April 23, 2009 7:34 PM
  • User-1199946673 posted

    I have found what the problem was and soorted out my tables so I do not use reserved words before even that post was approved by moderator:)  Thanks anyway for answers!

    I'm not sure what you're trying to say? The problem was quit clear. When using a reserved word (in this case password) as a fieldname (or a tablename), you should enclose it in brackets. If you don't, an error will be raised!

    You can recreate this by creating a table named test. and add only 1 field named password into it. If you run the following sql statement in Access, a record will be added:

    INSERT INTO test (password) VALUES ('test')

    However, if you create a webpage in ASP.NET, add a label called ErrorLabel on it and run the following code:

    Using conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("connectionString").ConnectionString)
    Using command As New OleDbCommand("INSERT INTO test (password) VALUES ('test')", conn)
    Try
    conn.Open()
    command.ExecuteNonQuery()
    Catch ex As Exception
    ErrorLabel.Text = ex.Message
    End Try
    End Using
    End Using

    You will see that it throws an error. When you change the SQL query in

    INSERT INTO test ([password]) VALUES ('test')

    A record will be added...

     

    Thursday, April 23, 2009 9:32 PM
  • User-1557221765 posted

     What I am trying to say is that i did noot know that 'password' is a reserved word. I have found a list of reserved words while I was researching the problem. instead of putting that reserved word between brackets [], i have changed field name in my table, what solved the problem.

    Friday, April 24, 2009 6:50 AM