[czach]

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

[Unorthodox]

What are you trying to do with this:

sql = "SELECT * FROM Product, Customer"

You realize this is doing a full join, right?

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

czach, 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

[czach]

ooops... hehe..
uhm.. could you tell me what full join is?... if you dont mind... hehehe

[czach]

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....

[Unorthodox]

Do the two tables have the same fields?

[czach]

no they dont... 1 contains the info of the product and the other contains the info of the customer...

[Unorthodox]

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

[czach]

thanks a lot!!....