[Ozi]

hey people, if anyone feels they can assist me with my query please contact me as soon as possible

i have a simple query, i have data sent to another form, and then the data values sent to the other form are then saved to one of my database tables.
the first connection to the first table works fine, however i need to send the remaining data to the remaining 2 tables.

here is the code that seems to be working fine when connected to one table

hope this helps thankyou

	Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

		'Create Connection for customer table'

		Dim Conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Sahir Osman\My Documents\Business I.T Year 2\test\executivetravel.mdb")

		Conn.Open()

		Dim Command As New System.Data.OleDb.OleDbCommand("insert into Customer(firstname, surname, dateofbirth, telephone, email, passportno )values('" & lblfirstname.Text & "', '" & lblsurname.Text & "', '" & lblDOB.Text & "', " & lblcontact.Text & ", '" & lblemail.Text & "', " & lblpassport.Text & " )", Conn)
		
Command.ExecuteNonQuery()

		Conn.Close()
	End Sub
End Class

[KeyWiz]

Can you post the code that is NOT working?

[Ozi]

Their isnt another code. i need to know how i can allow the remaining data on my page to be sent to the remaining tables by one save button.

so for e.g the code provided above will save to the Customer table with the values from the lables on the current page, however i have more lables with data for the remaining tables for e.g activity table.
how would i start my next connection string, or would it be included in the same connection string?

for e.g. insert into customer values lblfirstname.text
i want a new connection to the activity table e.g. insert into activity values lbltennis.text
but dont know how to do that using the save button aswell, because the save button will execute that command

hope that helps.

[Jayman]

Same connection string. The connection string merely forms a connection to the database. All the table are accessible once you form a connection.

You just need to write each SQL statement for each table and execute each one in turn before closing the connection.

[Ozi]

hi could you please send me an example of how i would implement this. because i seem to be having difficulties doing so. very grateful for your moral support people

[Jayman]

Post your updated code that you have completed so far and we will help you get it working.

[Ozi]

hi thankyou so much for you assistance so far really appreciate it been trying to crack this one for time

here is the code but it still doesnt seem to be working hope u can identify some errors

  Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
		'Create Connection for customer table'
		Dim Conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Sahir Osman\My Documents\Business I.T Year 2\test\executivetravel.mdb")
		Conn.Open()
		Dim Command As New System.Data.OleDb.OleDbCommand("insert into Customer(firstname, surname, dateofbirth, telephone, email, passportno )values('" & lblfirstname.Text & "', '" & lblsurname.Text & "', '" & lblDOB.Text & "', " & lblcontact.Text & ", '" & lblemail.Text & "', " & lblpassport.Text & " )", Conn)
		Command.ExecuteNonQuery()
		Dim Command As New System.Data.OleDb.OleDbCommand("insert into Booking(Adults, Youths, Childs, airport, traveldate, destination, holidaytotal )values('" & lbladult.Text & "', '" & lblyouth.Text & "', '" & lblinfant.Text & "', '" & lblairport.Text & "', '" & lbldepart.Text & "', '" & lbldestination.Text & "', " & lblbill.Text & " )", Conn)
		Command.ExecuteNonQuery()
		Conn.Close()
	End Sub

please can you show me my little error because i cant spot it thankyou :-)

[Jayman]

Is all the data that you are entering into your tables String data?

If so, you are missing a few apostrophes in your insert statements.

If some of it is numerical, then you will need to convert the value from the textbox into its proper value before inserting into table.

What are the attributes of your tables?

[Ozi]

For some reason the Dim command new line has an error, and also the attributes have been named after the attributes in my tables i have already checked this through. can someone please see why this aint working. ive bee trying for ages thankyou for all you help so far people. :-)
p.s i have made the relevant changes

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
		'Create Connection for customer table'
		Dim Conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Sahir Osman\My Documents\Business I.T Year 2\test\executivetravel.mdb")
		Conn.Open()
		Dim Command As New System.Data.OleDb.OleDbCommand("insert into Customer(firstname, surname, dateofbirth, telephone, email, passportno )values('" & lblfirstname.Text & "', '" & lblsurname.Text & "', '" & lblDOB.Text & "', " & lblcontact.Text & ", '" & lblemail.Text & "', " & lblpassport.Text & " )", Conn)
		Command.ExecuteNonQuery()
		Dim Command As New System.Data.OleDb.OleDbCommand("insert into Booking(Adults, Youths, Childs, airport, traveldate, destination, holidaytotal )values(" & lbladult.Text & ", " & lblyouth.Text & ", " & lblinfant.Text & ", '" & lblairport.Text & "', '" & lbldepart.Text & "', '" & lbldestination.Text & "', " & lblbill.Text & " )", Conn)
		Command.ExecuteNonQuery()
		Conn.Close()
	End Sub

edit: added [code] tags ~ jayman9

[Jayman]

First of all please do not make multiple posts with the same question. You only need to post your question once. We have a lot of members with a lot of questions, so be patient.

Please use [ code] tags as indicated in the forum rules to make your posted code more readable.

Since you never answered my question about what are the column attributes for your database. I am going to leave that problem for later, but your SQL statements have some issues.

The reason you are getting the error is you cannot declare an object twice with the same name using a DIM statement. You have already created the object, all you need to do is assign the new SQL statement to it. There are methods available for you to do this.

Here are the methods available for OleDbCommand object.

You need to use the CommandText method to assign a new SQL statement to the object.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
'Create Connection for customer table'
Dim Conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Sahir Osman\My Documents\Business I.T Year 2\test\executivetravel.mdb")
Conn.Open()
Dim Command As New System.Data.OleDb.OleDbCommand("insert into Customer(firstname, surname, dateofbirth, telephone, email, passportno )values('" & lblfirstname.Text & "', '" & lblsurname.Text & "', '" & lblDOB.Text & "', " & lblcontact.Text & ", '" & lblemail.Text & "', " & lblpassport.Text & " )", Conn)
Command.ExecuteNonQuery()

Command.CommandText = "insert into Booking(Adults, Youths, Childs, airport, traveldate, destination, holidaytotal )values(" & lbladult.Text & ", " & lblyouth.Text & ", " & lblinfant.Text & ", '" & lblairport.Text & "', '" & lbldepart.Text & "', '" & lbldestination.Text & "', " & lblbill.Text & " )"

Command.ExecuteNonQuery()
Conn.Close()
End Sub

[Ozi]

hi there, sorry for any inconvinience. my attributes for the columns can be viewed here, they are the same as the connection string. i tried to test my system using your innovated code, however to my dsitress it still seems to have constraints executing the code and gives me an error on the first execution line.
here is the code, hope you can help me.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
'Create Connection for customer table'
Dim Conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Sahir Osman\My Documents\Business I.T Year 2\test\executivetravel.mdb")
Conn.Open()
Dim Command As New System.Data.OleDb.OleDbCommand("insert into Customer(firstname, surname, dateofbirth, telephone, email, passportno )values('" & lblfirstname.Text & "', '" & lblsurname.Text & "', '" & lblDOB.Text & "', " & lblcontact.Text & ", '" & lblemail.Text & "', " & lblpassport.Text & " )", Conn)
Command.ExecuteNonQuery()

Command.CommandText = "insert into Booking(Adults, Youths ) values( " & lbladult.Text & ", " & lblyouth.Text & " ) "
Command.ExecuteNonQuery()

Conn.Close()

thankyou Jayman :-)

[Ozi]

hi there sorry would just like to add, [the Command.ExecuteNonQuery()] on the first line shows a data mismatch error on the code.
the attributes can be seen in the connection strings. thankyou

[Jayman]

Post the exact error message you are getting please.

[Ozi]

the error message states that

[You cannot add or change a record because a related record is required in table 'Customer'.]

the code is

[Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
'Create Connection for customer table'
Dim Conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Sahir Osman\My Documents\Business I.T Year 2\test\executivetravel.mdb")
Conn.Open()
Dim Command As New System.Data.OleDb.OleDbCommand("insert into Customer(firstname, surname, dateofbirth, telephone, email, passportno )values('" & lblfirstname.Text & "', '" & lblsurname.Text & "', '" & lblDOB.Text & "', " & lblcontact.Text & ", '" & lblemail.Text & "', " & lblpassport.Text & " )", Conn)
Command.ExecuteNonQuery()

Command.CommandText = "insert into Booking(Adults, Youths, Childs, airport, traveldate, destination, holidaytotal )values(" & lbladult.Text & ", " & lblyouth.Text & ", " & lblinfant.Text & ", '" & lblairport.Text & "', '" & lbldepart.Text & "', '" & lbldestination.Text & "', " & lblbill.Text & " )"

Command.ExecuteNonQuery()[u] Conn.Close()]

the code highlighted in bold and underlined is where the error is

this si where im facing the constraints

thankyou