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

Posted on 2011-10-23
Last Modified: 2012-05-12
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)
Question by:jampost
    LVL 15

    Expert Comment

    what error do you get?

    Author Comment

    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

    Author Comment


    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”      

    Author Comment


    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()

    Author Comment


    "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.

    Author Comment

    This is the line that the error always points to:

    LVL 83

    Accepted Solution

    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?

    Author Comment

    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.
    LVL 83

    Expert Comment

    Glad to help :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now