• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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)
  • 6
  • 2
1 Solution
what error do you get?
jampostAuthor Commented:
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

    End Sub
jampostAuthor Commented:

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”      
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

jampostAuthor Commented:

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"
       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()
jampostAuthor Commented:

"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.
jampostAuthor Commented:
This is the line that the error always points to:

TableAdapter needs the UpdateCommand to save the changes. This command is automatically created if your select command has a primary key field. Does the table have a primary key? Is it included in Select statement?
jampostAuthor Commented:
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.
Glad to help :-)

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now