Database Update(2 primary keys)

  • Thread starter Thread starter DotNetJunkies User
  • Start date Start date
D

DotNetJunkies User

Hello everybody
Does anyone know how to update a table having 2 primary keys?(with MS Access). I am using datasets and update commands.

Thanks.
 
Hi DotNetJunkies,

I do not know your problem, but you can try this.

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds)

I hope this helps?

Cor

"> Hello everybody
Does anyone know how to update a table having 2 primary keys?(with MS
Click to expand...
Access). I am using datasets and update commands.
 
Hi,

You'll have to configure update command. What does your table look like and
what does you update command look like (i guess you need also an insert and
a delete command).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

DotNetJunkies User said:
Hello everybody
Does anyone know how to update a table having 2 primary keys?(with MS
Click to expand...
Access). I am using datasets and update commands.
Thanks.
Click to expand...
engine supports Post Alerts, Ratings, and Searching.
 
Hi
Yes i have all commands(insert, delete, etc). My update command looks like this:

Dim updateCmd as new OleDbCommand
Dim prm as oledbparameter
updateCmd.CommandText = "UPDATE tblAppCourse SET Qualified=?,Status=? WHERE App_id=? "(here i need to say WHERE App_id=? and Course_id=? but i got error when i did this)
updateCmd.Connection=myCnn1
prm=updateCmd.Parameters.Add("?",OleDbType.VarChar,15,"Qualified")
prm=updateCmd.Parameters.Add("?",OleDbType.VarChar,15,"Status")
prm=updateCmd.Parameters.Add("?",OleDbType.Integer,15, "App_id")

My table is as follows:
tblAppCourse(App_id, Course_id, Priority, Qualified), App_id and Course_id are the primary keys which uniquely identify a record.

i need to update a record where App_id=something AND course_id =something because an Applicant can choose many courses( 3 courses), so i can have three rows with same App_id, but the Course_id will differ. Currently what is happening is that when i write back Qualified to dbase , all the rows where the given id is specified are written to, but what i need is to write to a row which has a certain App_id AND a certain Course_id.
for example if i have
app_id Course_id Qualified
1 C001 yes
1 C002 no
1 C003 yes

i should be able to write backthe value of " qualified " for each course for that student, but for now what is happening is that i can specify only App_id in my update command, not Course_id, so if i write alue of Qualified, it write same value to all three rows, yes for all, or no for all of them.
So in my update command how can i accomodate two primary keys to identify a record.
Thanks
 
Hi,

DotNetJunkies User said:
Hi
Yes i have all commands(insert, delete, etc). My update command looks like this:

Dim updateCmd as new OleDbCommand
Dim prm as oledbparameter
updateCmd.CommandText = "UPDATE tblAppCourse SET Qualified=?,Status=?
Click to expand...
WHERE App_id=? "(here i need to say WHERE App_id=? and Course_id=? but i got
error when i did this)

Yes, you need that. When and what error do you get?
 
Hi Miha

as long as i had only app_id i did not get any error, but when i added course id to it, then i got the error. i don't remember the exact wordings but it meant that the format of the update command was wrong. i tried using AND as well as comma to specify two primary keys but it still gave me error. So i thought that there is another way of doing it. Do we need to use AND or comma to specify more than 1 primary key?
 
Hi Miha

as long as i had only app_id i did not get any error, but when i added course id to it, then i got the error. i don't remember the exact wordings but it meant that the format of the update command was wrong. i tried using AND as well as comma to specify two primary keys but it still gave me error. So i thought that there is another way of doing it. Do we need to use AND or comma to specify more than 1 primary key?
 
Hi
the exact error is "Input String was not in correct format" both when i use comma and AND.
 
Back
Top