Problems adding a Data adapter ODBC with Access database

[bianconero]

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]

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]

Oh my gosh i've missed your pointer within title ("with Access database")

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]

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.

 

[kulrom]

Are you trying to update primary key field maybe? check in your code ... Cheers

 

[bianconero]

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]

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

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]

alright, thanx a lot for the help, ill keep on looking

 

[kulrom]

Well, i just did attach an example about insert/update/delete queries ... take a look at that http://www.vbdotnetforums.com/showthread.php?t=3549

Cheers

 

[jmcilhinney]

Firstly, use OleDb for Access rather than ODBC.
Secondly, what is the SQL code you are using to update and delete?

 

[bianconero]

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]

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]

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]

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:

[color=Blue]Dim[/color] deleteCommand [color=Blue]As New[/color] OleDbCommand(SQLString, connection)

adp_L.DeleteCommand = deleteCommand

[color=Blue]Dim[/color] deleteCommand [color=Blue]As New[/color] OleDbCommand

deleteCommand.CommandText = SQLString
deleteCommand.Connection = connection
adp_L.DeleteCommand = deleteCommand

adp_L.DeleteCommand = [color=Blue]New[/color] OleDbCommand(SQLString, connection)

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.

 

[bianconero]

im working on it this week, i was off work for some days, ill let u know by tomorow if it works okay