Results 1 to 5 of 5

Thread: [2005] Help with "non-zero Size" error?

  1. #1

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    [2005] Help with "non-zero Size" error?

    I have this error when trying to update the access database from my vb program. I have been stuck at this for days now! argh...anyone know what this error means?



    System.InvalidOperationException was unhandled
    Message="OleDbCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size."
    Source="System.Data"
    StackTrace:
    at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow)
    at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
    at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
    at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
    at Well_Hours_Input_Tool.frmAdd.btnUpdate_Click(Object sender, EventArgs e) in C:\Documents and Settings\andy\Desktop\VB.Net\Well Hours Input Tool\Well Hours Input Tool\Form1.vb:line 57
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.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.IMsoCompo nentManager.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 Well_Hours_Input_Tool.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()
    If you find my thread helpful, please remember to rate me

  2. #2
    Lively Member
    Join Date
    Sep 2007
    Location
    Texas
    Posts
    98

    Re: [2005] Help with "non-zero Size" error?

    Looks like you are inserting a null value into a field that does not allow nulls. Zero length is AccessSpeak for null value. Check your update statement or change the table to allow non-zero values.
    Working in VB2005 and SQL2005

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [2005] Help with "non-zero Size" error?

    Maybe you could show us the code you're executing when this error occurs?

  4. #4

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Help with "non-zero Size" error?

    Code:
        Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
            If Me.cboField.SelectedIndex = -1 Then
                Exit Sub
            End If
    
            Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=;Data Source=" & DataSourcePath)
            Dim strSQL As String
                 strSQL = "Select gasday, field, wellnumber, drillnumber, goldenwell, hoursonline, causeofloss, sourceofloss, comments FROM [WellsOnline] WHERE gasday = @Datey AND field = @field"
    
    
            MainAdapter = New OleDbDataAdapter(strSQL, connection)
            MainAdapter.SelectCommand.Parameters.Add("Datey", OleDbType.Date)
            MainAdapter.SelectCommand.Parameters.Add("field", OleDbType.Char)
            MainAdapter.SelectCommand.Parameters("Datey").Value = Me.dtpGasDay.Value.Date
            MainAdapter.SelectCommand.Parameters("field").Value = Me.cboField.SelectedText
    
    
            Dim builder As New OleDbCommandBuilder(MainAdapter)
            MainAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            dt = New DataTable
            dt.Clear()
            'Retrieve the data.
            MainAdapter.Fill(dt)
            Me.dgvData.DataSource = dt
            'Dim dc As DataGridViewColumn
            'dc = Me.dgvData.Columns(1)
            'dc.HeaderText = "Hours Online"
            'dc = Me.dgvData.Columns(0)
            'dc.HeaderText = "Well"
    
    
            Dim i As Integer
    
            If dt.Rows.Count < 1 Then
                Me.lblCheckDate.Text = "No Data"
                Me.lblCheckDate.BackColor = Color.Red
                'So if there is no data, then populate it!
                'ID	gasday	field	wellnumber	drillnumber	goldenwell	hoursonline	causeofloss	sourceofloss	comments
                For i = 0 To Me.clbWells.Items.Count - 1
                    Dim dr As DataRow
                    dr = dt.NewRow
                    dr.Item(0) = Me.dtpGasDay.Value.Date.ToShortDateString
                    dr.Item(1) = Me.cboField.Text
                    dr.Item(2) = Me.clbWells.Items.Item(i).ToString
                    dt.Rows.Add(dr)
                Next
            Else
                Me.lblCheckDate.Text = "Data Exists"
                Me.lblCheckDate.BackColor = Color.Green
            End If
    And then i am updating the table

    Code:
        Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
            If IsNothing(dt.GetChanges) Then
                MsgBox("no changes")
            Else
                MsgBox("Rows changed = " & dt.GetChanges.Rows.Count)
                MainAdapter.Update(dt)
            End If
    
    
        End Sub
    Seems like i will have to manually construct the insert line.
    If you find my thread helpful, please remember to rate me

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [2005] Help with "non-zero Size" error?

    I'm not sure if this will fix the issue but I VERY much doubt you are using the correct property here:
    Code:
    MainAdapter.SelectCommand.Parameters("field").Value = Me.cboField.SelectedText
    Given that the SelectedText property of the ComboBox class behaves in exactly the same way as the SelectedText property of the TextBox class, I think you need to ask yourself whether SelectedText is actually the correct member there. I think you'll find that should be:
    vb.net Code:
    1. MainAdapter.SelectCommand.Parameters("field").Value = Me.cboField.GetItemText(Me.cboField.SelectedItem)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width