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

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.

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

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.

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:

MainAdapter.SelectCommand.Parameters("field").Value = Me.cboField.GetItemText(Me.cboField.SelectedItem)