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

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

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

Try surrounding the names with square parens
E.g.

thank you very much thats works perfectly.

would you have any idea on how to insert data into the bookings table aswell as the customer table?

thanks

Two insert commands.

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

They generate on their own, so you simply ommit it from your SQL
i.e
becomes

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

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()

Retrieve NewID after Insert (using MS ACCESS)

Try

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

See post 13