asked on

SQL Server 2008 database will not update in one of the tables included in a Visual Basic 2008 program written with Visual Studio 2008

I am trying to create a "new stored procedure" for Updating the database. This is my SQL Statement in the Table Adapter: (But, not working)

UPDATE EmpPayrollInfo

Set EmployeeNumber = @EmployeeNumber, EmpFirstName = @EmpFirstName, EmpLastName = @EmpLastName, HourlyAmount = @HourlyAmount, PieceRate = @PieceRate, Deductions_a = @Deductions_a, Deductions_b = @Deductions_b, Deductions_c = @Deductions_c, Deductions_d =@Deductions_d, Deductions_e = @Deductions_e, Fed_File_Married_Single = @Fed_File_Married_Single, Fed_Allowances = @Fed_Allowances, Fed_Extra_Withholding = @Fed_Extra_Withholding, AdvanceEarnedInc_Credit = @AdvanceEarnedInc_Credit, State_Allowances = @State_Allowances, State_File_Married_Single = @State_File_Married_Single, State_Extra_Withholding = @State_Extra_Withholding, County_PercentageRate = @County_PercentageRate, Notes = @Notes

Declare @EmployeeNumber varchar(50)
Declare @EmpFirstName varchar(50)
Declare @EmpLastName varchar(50)
Declare @HourlyAmount decimal(18, 2)
Declare @PieceRate Bit
Declare @Deductions_a decimal(18, 2)
Declare @Deductions_b decimal(18, 2)
Declare @Deductions_c decimal(18, 2)
Declare @Deductions_d decimal(18, 2)
Declare @Deductions_e decimal(18, 2)
Declare @Fed_File_Married_Single varchar(50)
Declare @Fed_Allowances Int
Declare @Fed_Extra_Withholding decimal(18, 2)
Declare @AdvanceEarnedInc_Credit Bit
Declare @State_Allowances Int
Declare @State_File_Married_Single varchar(50)
Declare @State_Extra_Withholding decimal(18, 2)
Declare @County_PercentageRate varchar(MAX)
Declare @Notes varchar(MAX)

Eyal

what error do you get?

jampost

ASKER

Error: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

This is unaltered Navigator code in the VB 2008 program.

Public Class Payroll

Private Sub EmpPayrollInfoBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EmpPayrollInfoBindingNavigatorSaveItem.Click
Me.Validate()
Me.EmpPayrollInfoBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.EmpPayInfoDataSet)

End Sub

jampost

ASKER

Eyal,

Maybe I have incorrect syntax for declaring? I just got this error message when I tried to save changes in the table adapter.

Must declare the scalar variable “@EmployeeNumber”

jampost

ASKER

Exception Detail -- after trying to save during debug:

System.InvalidOperationException was unhandled
Message="Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
Source="Starling Payroll"
StackTrace:
at Starling_Payroll.EmpPayInfoDataSetTableAdapters.TableAdapterManager.UpdateAll(EmpPayInfoDataSet dataSet) in C:\Users\Joe\Documents\Visual Studio 2008\Projects\Starling Payroll\Starling Payroll\EmpPayInfoDataSet.Designer.vb:line 1918
at Starling_Payroll.EmpPayInfo.EmpPayrollInfoBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Users\Joe\Documents\Visual Studio 2008\Projects\Starling Payroll\Starling Payroll\EmpPayInfo.vb:line 6
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Starling_Payroll.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

jampost

ASKER

"Refresh the data table" at the table adapter is always unchecked. If you put a check in it, it will uncheck its self the second you leave it.

jampost

ASKER

This is the line that the error always points to:

Me.TableAdapterManager.UpdateAll(Me.EmpPayInfoDataSet)

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jampost

ASKER

Code Cruiser,

When I first read your comment, I was thinking "of cource I created a primary key. So I check anyway. I typed Primary Key in the first column and set it for Int, but........forgot to actually make it a primary key. Safe to say I will never make that mistake again. Thank you.

Nasir Razzaq

Glad to help :-)