7 Replies - 2965 Views - Last Post: 04 October 2009 - 11:51 PM Rate Topic: -----

#1 czach  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 49
  • Joined: 02-October 09

pls help! how do i add data from vb to multiple tables in a

Post icon  Posted 02 October 2009 - 07:37 PM

im a newbie at vb.net and i cant solve the errors that appears in my code..

i wanted to add data from a single form in vb to two tables in access but an error appears saying that the field customer_ID down to Contact_Number doesnt belong to the table record, though it really doesnt belong to that table.. but i already indicated the table for the customer_ID down to Contact_Number, which is Customer, in my sql statement...

pleeaase help!!! i cant seem to fix it... T-T..

Public Class Refill

	Dim connect As New OleDb.OleDbConnection
	Dim sql As String
	Dim ds As New DataSet
	Dim adapter As New OleDb.OleDbDataAdapter

Private Sub Savebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Savebtn.Click

		Dim cb As New OleDb.OleDbCommandBuilder(adapter)
		Dim dsNewRow As DataRow

		dsNewRow = ds.Tables("record").NewRow()

		dsNewRow.Item("Tank_Number") = TankNumber.Text
		dsNewRow.Item("Price") = Price.Text
		dsNewRow.Item("Customer_ID") = CustID.Text
		dsNewRow.Item("Customer_Address") = Address.Text
		dsNewRow.Item("First_name") = FirstName.Text
		dsNewRow.Item("Last_Name") = LastName.Text
		dsNewRow.Item("Contact_Number") = ContactNmbr.Text

		ds.Tables("record").Rows.Add(dsNewRow)
		adapter.Update(ds, "record")


		MsgBox("New Record added to the Database")


	End Sub

	Private Sub Refill_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		connect.ConnectionString = "PROVIDER = Microsoft.jet.OLEDB.4.0;Data Source = d:\Gas Petronas.mdb"

		connect.Open()

		sql = "SELECT * FROM Product, Customer"
		adapter = New OleDb.OleDbDataAdapter(sql, connect)
		adapter.Fill(ds, "record")

		connect.Close()

	End Sub
End Class


This post has been edited by czach: 03 October 2009 - 01:29 AM


Is This A Good Question/Topic? 0
  • +

Replies To: pls help! how do i add data from vb to multiple tables in a

#2 Unorthodox  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 02-October 09

Re: pls help! how do i add data from vb to multiple tables in a

Posted 02 October 2009 - 10:50 PM

What are you trying to do with this:

sql = "SELECT * FROM Product, Customer"

You realize this is doing a full join, right?

---------------------------------



View Postczach, on 2 Oct, 2009 - 06:37 PM, said:

im a newbie at vb.net and i cant solve the errors that appears in my code..

i wanted to add data from a single form in vb to two tables in access but an error appears saying that the field customer_ID down to Contact_Number doesnt belong to the table record, though it really doesnt belong to that table.. but i already indicated the table for the customer_ID down to Contact_Number, which is Customer, in my sql statement...

pleeaase help!!! i cant seem to fix it... T-T..

Public Class Refill

	Dim connect As New OleDb.OleDbConnection
	Dim sql As String
	Dim ds As New DataSet
	Dim adapter As New OleDb.OleDbDataAdapter

Private Sub Savebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Savebtn.Click

		Dim cb As New OleDb.OleDbCommandBuilder(adapter)
		Dim dsNewRow As DataRow

		dsNewRow = ds.Tables("record").NewRow()

		dsNewRow.Item("Tank_Number") = TankNumber.Text
		dsNewRow.Item("Price") = Price.Text
		dsNewRow.Item("Customer_ID") = CustID.Text
		dsNewRow.Item("Customer_Address") = Address.Text
		dsNewRow.Item("First_name") = FirstName.Text
		dsNewRow.Item("Last_Name") = LastName.Text
		dsNewRow.Item("Contact_Number") = ContactNmbr.Text

		ds.Tables("record").Rows.Add(dsNewRow)
		adapter.Update(ds, "record")


		MsgBox("New Record added to the Database")


	End Sub

	Private Sub Refill_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		connect.ConnectionString = "PROVIDER = Microsoft.jet.OLEDB.4.0;Data Source = d:\Gas Petronas.mdb"

		connect.Open()

		sql = "SELECT * FROM Product, Customer"
		adapter = New OleDb.OleDbDataAdapter(sql, connect)
		adapter.Fill(ds, "record")

		connect.Close()

	End Sub
End Class


Was This Post Helpful? 0
  • +
  • -

#3 czach  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 49
  • Joined: 02-October 09

Re: pls help! how do i add data from vb to multiple tables in a

Posted 03 October 2009 - 12:41 AM

ooops... hehe..
uhm.. could you tell me what full join is?... if you dont mind... hehehe
Was This Post Helpful? 0
  • +
  • -

#4 czach  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 49
  • Joined: 02-October 09

Re: pls help! how do i add data from vb to multiple tables in a

Posted 03 October 2009 - 12:59 AM

my problem really.. is i have 1 form which you input data on it and save it to two tables in access.. but i dont have a clue on how to do it... i can do it if it was only 1 table... so please help.... :)
Was This Post Helpful? 0
  • +
  • -

#5 Unorthodox  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 02-October 09

Re: pls help! how do i add data from vb to multiple tables in a

Posted 03 October 2009 - 01:06 AM

Do the two tables have the same fields?
Was This Post Helpful? 0
  • +
  • -

#6 czach  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 49
  • Joined: 02-October 09

Re: pls help! how do i add data from vb to multiple tables in a

Posted 03 October 2009 - 01:07 AM

no they dont... 1 contains the info of the product and the other contains the info of the customer...
Was This Post Helpful? 0
  • +
  • -

#7 Unorthodox  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 02-October 09

Re: pls help! how do i add data from vb to multiple tables in a

Posted 03 October 2009 - 10:20 AM

I think I understand what you are trying to do now. Here is what I would do to achieve what you are attempting.

Imports System.Data.OleDb
Public Class Refill
	Const ConnString As String = "PROVIDER = Microsoft.jet.OLEDB.4.0;Data Source = d:\Gas Petronas.mdb"
	Dim ConnOp As New OleDbConnection(ConnString)
	Private Sub Savebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Savebtn.Click
		ConnOp.Open()
		/*This first one inserts the values into the Customer table*/
		Dim InsCustomer As New OleDbCommand("INSERT INTO (Customer_ID, Customer_Address, First_name, Last_Name, Contact_Number)" & _
											"VALUES (?,?,?,?,?)", ConnOp)
		InsCustomer.Parameters.Add("Customer_ID", CustID.text)
		InsCustomer.Parameters.Add("Customer_Address", Address.Text)
		InsCustomer.Parameters.Add("First_name", FirstName.Text)
		InsCustomer.Parameters.Add("Last_Name", LastName.Text)
		InsCustomer.Parameters.Add("Contact_Number", ContactNmbr.Text)
		InsCustomer.ExecuteNonQuery()

		/*This second one inserts the values into the Product table*/
		Dim InsProduct As New OleDbCommand("INSERT INTO (Tank_Number, Price) VALUES (?, ?)", ConnOp)
		InsProduct.Parameters.Add("Tank_Number", TankNumber.Text)
		InsProduct.Parameters.Add("Price", Price.Text)
		InsProduct.ExecuteNonQuery()
		MsgBox("New Record added to the Database")
		ConnOp.Close()
	End Sub
End Class



Also, I would recommend putting some validation around those text boxes to ensure the information entered by the user matches the correct datatype, length and even contains information.

This post has been edited by Unorthodox: 03 October 2009 - 10:21 AM

Was This Post Helpful? 0
  • +
  • -

#8 czach  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 49
  • Joined: 02-October 09

Re: pls help! how do i add data from vb to multiple tables in a

Posted 04 October 2009 - 11:51 PM

thanks a lot!!.... :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1