Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2008
    Posts
    49

    Unanswered: entering data into access database using asp

    hi,

    i am having problems trying to add data into a table using asp.net. I have created a page which has a form which allows cusomer deatils to be entered. when the submitt button is clicked the data in the text boxes should go into the database.

    but i keep getting the error "Could not find file"

    the "conn.Open()" is highlighted.

    here is the code:


    Imports System.Data.OleDb
    Partial Class _Default
    Inherits System.Web.UI.Page
    Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("AA Database.mdb")


    Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click


    Dim query As String = "Insert Into Customer (Title, Surname) Values (?,?)"

    Using conn As New OleDbConnection(strConn)
    Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("Title", CusTitle.Text)
    cmd.Parameters.AddWithValue("Surname", CusSurname.Text)
    'cmd.Parameters.AddWithValue("Surname", surname.Text)
    'cmd.Parameters.AddWithValue("Address", address.Text)
    'cmd.Parameters.AddWithValue("Town", town.Text)
    'cmd.Parameters.AddWithValue("City", city.Text)
    'cmd.Parameters.AddWithValue("Postcode", pc.Text)
    'cmd.Parameters.AddWithValue("TelephoneNo", telephone.Text)
    'cmd.Parameters.AddWithValue("eMail", email.Text)

    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()

    End Using
    End Using




    End Sub
    End Class










    any help would be much appreciated, thanks

  2. #2
    Join Date
    Feb 2008
    Posts
    49
    hi, i moved the db file out of the application data folder and it works now.
    However i can add data into 6 of the 9 feilds in the customer table.

    the last 3 feilds Tel No, Emaill Address and Driving Licience No are causing problems. no matter what value i enter for them i keep getting the error 'Syntax error in INSERT INTO statement.'

    When i remove these 3 feilds from the code all the remining feilds work fine.

    here is the code:

    Imports System.Data.OleDb
    Partial Class _Default
    Inherits System.Web.UI.Page
    Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("AA Database.mdb")





    Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click


    Dim query As String = "Insert Into Customer (Title, Surname, Forename, DOB, Address, Postcode, Tel No, Email Address, Driving Licience No) Values (?,?,?,?,?,?,?,?,?)"

    Using conn As New OleDbConnection(strConn)
    Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("Title", CusTitle.Text)
    cmd.Parameters.AddWithValue("Surname", CusSurname.Text)
    cmd.Parameters.AddWithValue("Forename", CusForename.Text)
    cmd.Parameters.AddWithValue("DOB", CusDOB.Text)
    cmd.Parameters.AddWithValue("Address", CusAddress.Text)
    cmd.Parameters.AddWithValue("Postcode", CusPostcode.Text)
    cmd.Parameters.AddWithValue("Tel No", CusTelNo.Text)
    cmd.Parameters.AddWithValue("Email Address", CusEmailAddress.Text)
    cmd.Parameters.AddWithValue("Driving Licience No", CusDrivingLicienceNo.Text)

    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()

    End Using
    End Using




    End Sub
    End Class

  3. #3
    Join Date
    Feb 2008
    Posts
    49
    i think that the problem is that the 3 feilds which are causing problems all have spaces between them eg. Tel No, Email Address and Driving Licience No.

    i know there should not be spaces between them in the first place, but if i change it now in the db it will mean loosing records and the forms stop working.

    is there any way around this?

    many thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try surrounding the names with square parens
    E.g.
    Code:
    cmd.Parameters.AddWithValue("[Email Address]", CusEmailAddress.Text)
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2008
    Posts
    49
    thank you very much thats works perfectly.

  6. #6
    Join Date
    Feb 2008
    Posts
    49
    would you have any idea on how to insert data into the bookings table aswell as the customer table?

    thanks

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Two insert commands.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2008
    Posts
    49
    hi i tried to use two executee queries but it does not work, how can i get the auto number from booking to appear itself when the customer makes a booking?

    thanks

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    They generate on their own, so you simply ommit it from your SQL
    i.e
    Code:
    INSERT INTO myTable (id, name) VALUES(1, 'george')
    becomes
    Code:
    INSERT INTO myTable(name) VALUES('george')
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2008
    Posts
    49
    hi but im not trying to get the auto number for the customer table , that will come itsled once i eneter customer name etc, i have a booking table whch has an autonumber for booking id, the customer table is related to bookins so once a customer makes a booking the cust no automatically appears in booking table for that record.

    asp.net does not seem to reconise the relationships

    thanks

  11. #11
    Join Date
    Feb 2008
    Posts
    49
    hi,

    i have a date in the bookings table. im trying to enter data into it, every other feild in the booking table accepts data. but the date feilds comes up with syntax error. ive removed all validation from access and using a text box to put the value into

    the textbox is called ddate.text ive tried others too.

    here is the code:


    Using cmd2 As New OleDbCommand(query3, conn)
    cmd2.Parameters.AddWithValue("[Customer No]", ID)
    cmd2.Parameters.AddWithValue("Date", ddate.Text)
    cmd2.Parameters.AddWithValue("[Booking Status]", bks.Text)
    'cmd2.Parameters.AddWithValue("Time", time.Text)
    cmd2.ExecuteNonQuery()

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by aa_86
    hi but im not trying to get the auto number for the customer table , that will come itsled once i eneter customer name etc, i have a booking table whch has an autonumber for booking id, the customer table is related to bookins so once a customer makes a booking the cust no automatically appears in booking table for that record.

    asp.net does not seem to reconise the relationships

    thanks
    Retrieve NewID after Insert (using MS ACCESS)
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try
    Code:
    cmd2.Parameters.AddWithValue("Date", "#" & ddate.Text & "#")
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2008
    Posts
    49
    hi i can retrieve the booking id thats the no the problem i can retrieve it and make it go to the booking datils table. i can insert into every feild for customer, booking and booking table, apart from date and time feild which is in the booking tbale.

    you can see from my code that i am using a textbox called ddate.text and inserting it in the same way as all the other feilds but no matter what i enter into this feild i get a syntax error

    thanks

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    See post 13
    George
    Home | Blog

Posting Permissions

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