Problems adding a Data adapter ODBC with Access database

bianconero

Member
Joined
Jun 8, 2005
Messages
7
Programming Experience
Beginner
Hi, im coding this billing program. so i try and install an ODBC data adapter and it gives me the following error:
Could not determine which columns uniquely identify the rows for "tbl_produit". on the UPDATE and DELETE Statement, I checked my primary key and i do have a primary key etablished on the table. Can anyone help me !, thank you
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
Hi,
Certain error msg indicates that you don't have any primary key specified for the table no matter your claim. :(
Well, i asume you have used T-SQL (CREATE TABLE ... ) to define a column in your table as primary key. This time try to do the same through SQL enterprise manager ... i'm waiting for your feedback

Cheers ;)
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
Oh my gosh i've missed your pointer within title ("with Access database") :eek:

Well, probably issue comes from duplicated column names ( i assume you have 2 or more tables that have same names). Solution: either change the name of column/s or append the name of the table in front of the column name i.e. myTable.myColumn

Cheers ;)
 

bianconero

Member
Joined
Jun 8, 2005
Messages
7
Programming Experience
Beginner
i only have 4 tables, and in each tables , each word in the columns have a lettrer of the table at the end
example:
my tables:
.................tbl_product
.....................................columns: name_P
.....................................number_P
.....................................code_P
.................tbl_employe
.....................................name_E
.....................................pass_E
.................tbl_transaction
.....................................code_T
.................tbl_buyer
......................................name_B

so im sure i dont have any duplicate names, i really cant find anything.
 

bianconero

Member
Joined
Jun 8, 2005
Messages
7
Programming Experience
Beginner
i wrote:

SELECT
tbl_produit.*
FROM
tbl_produit


but i need the update n delete statement, because i need to be able to change data in the database.

if u have no idea, do u know any other way to insert my database.
someone told me about a xml or ini file, but i dont no idea, do u know.

thanx a lot
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
commnet ça va copain? quelles sont les chances?

Well we can fix your problem just i need some time cuz i have big event in saturday so i'm afraid i'd have no time to help much here as i'll have to prepare for the day :D

Btw, i have posted many attachments about this subject (Insert/Update/Delete from within Access) so, take a look around maybe you'll find something useful

Cheers ;)
 

bianconero

Member
Joined
Jun 8, 2005
Messages
7
Programming Experience
Beginner
thanx for the link KULROM !!

SELECT Nom_E, Pass_E, Type_E, User_E FROM tbl_employe

i got the OleDb to work. not with ODBC but never mind, ill jsut do it with oleDb, but i have of problem taht i can update my database . this is my scrip, can u chekc it out.
thx
this a billing program, i got an inventory section to add items. so i did a button to SAVE the modifications, UPDATE.Dataset

oleDb Conection = con_L
oleDb Adapter = adp_L
oleDb Dataset = Data_L
table: tbl_employe


PrivateSub Ajoutlogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load

TextBox1.Text = getNom()

con_L.Open()

adp_L.Fill(Data_L)

Me.BindingContext(Data_L, "tbl_employe").Position = 0




PrivateSub Btn_rec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_rec.Click

Data_L.AcceptChanges()

Data_L.EndInit()

adp_L.Update(Data_L) ' this section doesnt work, doesnt update with my Database

Data_L.Clear() ' this section works, it clears my dataset OKAY

adp_L.Fill(Data_L, "tbl_employe")

EndSub

 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
Firstly, you are SUPPOSED to use OleDb for Access, NOT Odbc.

Secondly, you are calling AcceptChanges on your DataSet before calling Update on your OleDbDataAdapter. This tells the DataSet that there are no changes to commit to the database so nothing happens when you call Update. Calling Update accepts changes to the DataSet implicitly if the update succeeds so you don't need to call AcceptChanges.
 

bianconero

Member
Joined
Jun 8, 2005
Messages
7
Programming Experience
Beginner
i tryed removing the aceptChange, still doesnt update my database. now i tryed this , but it gives me an error:

error: ' when it gets to adapter.update(Data_set)= adp_L.update(data_L)
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Update requires the DeleteCommand to have a connection object. The Connection property of the DeleteCommand has not been initialized.



' The form LOAD
Private Sub Ajoutlogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
TextBox1.Text = getNom()

typetxt.Hide()

con_L.Open()

adp_L.Fill(Data_L)

Me.BindingContext(Data_L, "tbl_employe").Position = 0

' DELETE BUTTON
Private
Sub Bn_Efface_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bn_Efface.Click
Dim REPONSE As MsgBoxResult

REPONSE = MsgBox("Voulez vous vraiment effacer " & usertxt.Text & " ?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Delete")

If REPONSE = vbNo Then Exit Sub

DV.Delete(DV.Find(usertxt.Text))

Data_L.Tables("tbl_employe").Rows.RemoveAt(Me.BindingContext(Data_L, "tbl_employe").Position

adp_L.Update(Data_L)


Me.BindingContext(Data_L, "tbl_employe").Position = 0





let me know if you can help me out.
thanx a lot

 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
This is a positive step because it means that the adapter has found at least one row that needs to be deleted, whereas before, the call to AcceptChanges was preventing that.

The error is occurring because you haven't assigned a connection to your delete command. Just like the select command of the adapter, the delete, insert and update commands need a connection to a database. The connection is a property of the commands themselves and not of the adapter, so it is not assumed that the delete, insert and update commands will use the same connection as the select command. You haven't shown the code where your delete command is created so I'll cover all the options. If you have created your adapter in the designer, expand the DeleteCommand property and set its Connection property. If your connection is not created until run time you can set this property in code. If you are creating your delete command in code, all the following code snippets achieve the same thing:
VB.NET:
[color=Blue]Dim[/color] deleteCommand [color=Blue]As New[/color] OleDbCommand(SQLString, connection)

adp_L.DeleteCommand = deleteCommand
VB.NET:
[color=Blue]Dim[/color] deleteCommand [color=Blue]As New[/color] OleDbCommand

deleteCommand.CommandText = SQLString
deleteCommand.Connection = connection
adp_L.DeleteCommand = deleteCommand
VB.NET:
adp_L.DeleteCommand = [color=Blue]New[/color] OleDbCommand(SQLString, connection)
VB.NET:
adp_L.DeleteCommand = [color=Blue]New[/color] OleDbCommand
adp_L.DeleteCommand.CommandText = SQLString
adp_L.DeleteCommand.Connection = connection
SQLString is assumed to be a String that contains a valid SQL delete statement and connection is assumed to be a valid OleDbConnection object, probably the same one that was assigned to the SelectCommand of the data adapter when it was created.

You will need to do the same thing for the InsertCommand and UpdateCommand properties of the data adpater if you are inserting new rows and updating existing rows.
 
Top Bottom