update requires a valid Deletecommand ? VB 2005 + ACCESS 2007

[kakakaka77]

Hi,
I have a ACCESS database has two tables: employee and timesheet. I am trying to creat VB 2005 datagridview to control the database. Two tables of database were put on two forms called employee and timesheet. when I use the EmployeeBindingNavigator Bar to control data with insert,delete or save, it works fine. But, when I try to use the same thing for timesheet form to delete a record on datagridview, after click delete when I click SAVE button, it gives out a error message "update requires a valid deleteCommand when pass datarow" .

This is PK in emploee table, no PK in timesheet table.

How can I correct this error?

Thank you !

 

[kakakaka77]

code for Save button as follow:

Private Sub EmployeeBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EmployeeBindingNavigatorSaveItem.Click
Me.Validate()
Me.EmployeeBindingSource.EndEdit()
Me.EmployeeTableAdapter.Update(Me.PayrollDataSet.employee)
MsgBox("Record Updated Successfully")
End Sub

 

[jebenson]

The BindingNavigator will create the DeleteCommand for you, but only if the table in question has a primary key. You stated that your timesheet table does not have a primary key, so the BindingNavigator did not create a DeleteCommand, which is why you get this error message.

Add a primary key to your timesheet table, then add the datasource again and drag the table to your form. The DeleteCommand should be created then.

Plus, it's just good database design to have a primary key in every table.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[kakakaka77]

Thank you, jebenson,
I recreate the database and setup the PK as you said it looks working now.

And I am trying to create a button on form employee, so when I click on it, the timesheet will open. What I want to do is open the timesheet with the data of the only employee I selected in the datagridview column.

what I did is create a query called fillbyid in timesheet tableadapter, when I click "timesheet" button which will call "timesheet.show()",and the form will call "fillbyid" to load form.

code for quey "fillbyid":

SELECT payroll.ID, payroll.Date_, payroll.Hours_, payroll.Circle
FROM payroll, employee
WHERE (payroll.ID = 'employee.idtextbox')

when I run it, there is nothing show up but a blank form.
so, do you have any idea about this?

Thank you very much!

 

[jebenson]

This bit:

WHERE (payroll.ID = 'employee.idtextbox')

means that it will select all records where payroll.ID is equal to the string "employee.idtextbox". I doubt that any of the records have "employee.idtextbox" as the ID. You need to use the value in the textbox, and concatenate it to the SQL:

"WHERE (payroll.ID = '" & employee.idtextbox.Text & "')"


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[kakakaka77]

Hi, thanks,
I changed the SQL statement as follow : (I recreate the project, "timesheet" is the same table as "payroll" before)

SELECT timesheet.ID, timesheet.Employee_ID, timesheet.Date_, timesheet.Hours, timesheet.Circle
FROM timesheet, employee
WHERE (timesheet.Employee_ID = '&employee.idtextbox.text&')

BUT it still just show up a blank form when I click on the "TIMESHEET" button which will call "timesheet.show()"
And the code for loading TIMESHEET form is :

Private Sub timesheet_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.TimesheetTableAdapter.FillByID(Me.Payroll_reportDataSet.timesheet)

End Sub

Any idea,guys?

thank you !

 

[jebenson]

Ok, you didn't do what I posted. You are using the literal string "&employee.idtextbox.text&" in your SQL. This means it will ONLY look for and ID that equals the literal text "&employee.idtextbox.text&".

You need to use the value in the textbox. The & operator in VB concatenates one string to another, like so:

Dim s As String = "One string" & " Another string"

s now contains the string "One string Another string"

So, to use the value in the textbox, do this:

"WHERE (payroll.ID = '" & employee.idtextbox.Text & "')"

This is: "WHERE (payroll.ID = <apostrophe<quote> & employee.idtextbox.Text & <quote><apostrophe><parenthesis><quote>

Note: the apostrophes are used if your ID is a string or character field. If it is numeric, don't use the apostrophes around the value.

Say the value in idtextbox.Text is "A1001". This would make your SQL into:

"WHERE (payroll.ID = 'A1001')"

If the next ID is A1002, when that is selected is will make the SQL:

"WHERE (payroll.ID = 'A1002')"

and so forth.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[kakakaka77]

Hi, I sorry I didnt do that right last time.

Now, My code is like this:

SELECT timesheet.ID, timesheet.Employee_ID, timesheet.Date_, timesheet.Hours, timesheet.Circle
FROM timesheet, employee
WHERE (timesheet.Employee_ID = '"&employee.idtextbox.text&"')

But it still just a blank form, any idea?
thank you!