Vb database connectivity

vb.net database connectivity

Page 1 of 1

13 Replies - 5209 Views - Last Post: 28 December 2006 - 09:52 AM Rate Topic: ***** 1 Votes

#1 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Vb database connectivity

Posted 19 December 2006 - 12:13 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Vb database connectivity

#2 KeyWiz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 8
  • View blog
  • Posts: 438
  • Joined: 26-October 06

Re: Vb database connectivity

Posted 19 December 2006 - 12:55 PM

Can you post the code that is NOT working?
Was This Post Helpful? 0
  • +
  • -

#3 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Re: Vb database connectivity

Posted 19 December 2006 - 02:53 PM

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. :D
Was This Post Helpful? 0
  • +
  • -

#4 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Vb database connectivity

Posted 19 December 2006 - 03:33 PM

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.
Was This Post Helpful? 0
  • +
  • -

#5 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Re: Vb database connectivity

Posted 20 December 2006 - 03:38 PM

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
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Vb database connectivity

Posted 20 December 2006 - 05:05 PM

Post your updated code that you have completed so far and we will help you get it working.
Was This Post Helpful? 0
  • +
  • -

#7 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Re: Vb database connectivity

Posted 21 December 2006 - 07:08 AM

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 :-)
Was This Post Helpful? 0
  • +
  • -

#8 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Vb database connectivity

Posted 21 December 2006 - 11:24 AM

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?
Was This Post Helpful? 0
  • +
  • -

#9 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Re: Vb database connectivity

Posted 22 December 2006 - 01:50 PM

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
Was This Post Helpful? 0
  • +
  • -

#10 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Vb database connectivity

Posted 23 December 2006 - 02:39 PM

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


Was This Post Helpful? 0
  • +
  • -

#11 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Re: Vb database connectivity

Posted 25 December 2006 - 10:51 AM

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 :-)
Was This Post Helpful? 0
  • +
  • -

#12 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Re: Vb database connectivity

Posted 25 December 2006 - 10:58 AM

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
Was This Post Helpful? 0
  • +
  • -

#13 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Vb database connectivity

Posted 25 December 2006 - 01:04 PM

Post the exact error message you are getting please.
Was This Post Helpful? 0
  • +
  • -

#14 Ozi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-December 06

Re: Vb database connectivity

Posted 28 December 2006 - 09:52 AM

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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1