0

So I'm trying to create a windows form that collects injury data for employees and then writes it to an access database so I can print to forms and send to insurance and format for OSHA etc.

I have mostly short text, checkboxes and long text fields in my access database.

Everything worked great, and I deployed a beta copy to run some tests with another person and it immediately started throwing Data Type Mismatch errors. NOTHING has changed. I brought it back down to try to figure out what the problem field was. I have been going line by line to see which field was the problem child. It seems to be the long text fields are the problem children, but only sometimes. They'll work. Then I move on to the next field and test, and it works, then I move on and the next field, and I get the error again. So I naturally assume it's the line I'm currently working on. I'll try everything and then say screw it and start backtracking, and realize it was one of the earlier fields that's causing the problem. I literally did nothing to them.
I have rebuilt the tables in Access. I have renamed the columns 15 million ways. I know the data type works with it, b/c it worked with it 5 minutes before. I'm trying to write string values to the long text fields. They're working ok on my other forms. I've tried using short text fields in access. But I really need long text for what I'm doing as they're describing what happened and will need more than 255 characters.

I'm on the verge of completely re-creating this form to see if that will exorcise the gremlins.

Has anyone else had any experience with this? I'm starting to feel like this is a glitch in the software.

Thanks in advance.

Private Function ReportInj(path As String)
        If String.IsNullOrEmpty(path) Then path = "C:\Users\Public\Documents\EmpManager\Placeholder Images\NoSig.jpg"

        Dim buffer As Byte()

        Using img As Image = Image.FromFile(path)

            Dim ms As New MemoryStream()
            img.Save(ms, img.RawFormat)
            buffer = ms.GetBuffer()
        End Using

        access.AddParam("@Today", Today)
        access.AddParam("@EmpName", EmpName)
        access.AddParam("@EmpID", lblEmpID.Text)
        access.AddParam("@Add", txtAddress1.Text + " " + txtAddress2.Text)
        access.AddParam("@City", City)
        access.AddParam("@State", St)
        access.AddParam("@Zip", Zip)
        access.AddParam("@Phone", txtPhone.Text)
        access.AddParam("@DOB", lblDOB.Text)
        access.AddParam("@M", Male)
        access.AddParam("@F", Female)
        access.AddParam("@SSN", lblSSN.Text)
        access.AddParam("@HDate", lblHDate.Text)
        access.AddParam("@DOA", dpDOA.Value.Date)
        access.AddParam("@TOA", TOA)
        access.AddParam("@Part", txtBodyPt.Text)
        access.AddParam("@Before", txtBefore.Text)
        access.AddParam("@Cause", txtCause.Text)
        'access.AddParam("@Prior", Prior)
        'access.AddParam("@Witness", Witness)
        'access.AddParam("@Who", txtWho.Text)
        'access.AddParam("@Boss", cbxBoss.SelectedItem)

        'access.AddParam("@Sig", buffer)
        'access.AddParam("@FileLoc", lblSigFileLoc.Text)
        'access.AddParam("@Job", cbxJob.Text)
        'access.AddParam("@Shift", cbxShift.Text)
        'access.AddParam("@Where", Where)


        access.ExecQuery("INSERT INTO AccEmp1(ReportDate, EmpName, EmpID, Address, City, State, Zip, " &
                                        "Phone, DOB, Male, Female, SSN, HireDate, AccidentDate, AccTime, BodyPt, BeforeDoing, " &
                                        "WhatDidDamage)" &
                         "VALUES (@Today, @EmpName1, @EmpID, @Add, @City, @State, @Zip, " &
                                        "@Phone, @DOB, @M, @F, @SSN, @HDate, @DOA, @TOA, @Part, @Before," &
                                        "@Cause) ")

        'This is all my notes where i'm taking stuff out and adding stuff back in above. 
        ', DoingBefore" &
        '                 ", PriorInjury, Witness, [Names], Supervisor, EmpSig, SigFileLoc, JobTitle, ShiftStart, AccLoc
        ' " &
        '                 ", @Prior, @Witness, @Who, @Boss, @Sig, @FileLoc, @Job, @Shift, @Where

        ' On Errors - Return false
        If Not String.IsNullOrEmpty(access.exception) Then MsgBox(access.exception) : Return False
        'On Success - Return True
        Return True
    End Function

and my control I call at the beginning that gives access to the database

Imports System.Data.OleDb
Public Class dbControl
    'CREATE YOUR DB CONNECTION
    Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                         "Data Source=\\thebrain\shared\Database-DO NOT TOUCH!!\NewEmpManager\EmpManager.accdb;Persist Security Info=True;Jet OLEDB:Database Password=*****")

    ' PREPARE DB COMMAND
    Private DBCmd As OleDbCommand

    'DB DATA
    Public DBDA As OleDbDataAdapter
    Public DBDT As DataTable

    ' QUERY PARAMETERS
    Public Params As New List(Of OleDbParameter)

    ' QUERY STATISTICS
    Public RecordCount As Integer
    Public exception As String

    Public Sub ExecQuery(Query As String)
        ' RESET QUERY STATS
        RecordCount = 0
        exception = ""

        Try
            ' OPEN A CONNECTION
            DBCon.Open()

            ' CREATE DB COMMAND
            DBCmd = New OleDbCommand(Query, DBCon)

            ' LOAD PARAMS INTO DB COMMAND
            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

            ' CLEAR PARAMS LIST
            Params.Clear()

            ' EXECUTE COMMAND AND FILL DATATABLE
            DBDT = New DataTable
            DBDA = New OleDbDataAdapter(DBCmd)
            RecordCount = DBDA.Fill(DBDT)

        Catch ex As Exception
            exception = ex.Message
        End Try

        'CLOSE YOUR CONNECTION
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub

    'INCLUDE QUERY & COMMAND PARAMETERS
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New OleDbParameter(Name, Value)
        Params.Add(NewParam)
    End Sub
End Class

enter image description here

  • Are you using Long Text for anything over 255 characters? Are you making sure none of your input is greater that 255 character when you are inserting into a Short Text field? – Fawlty Jun 29 '22 at 16:31
  • 1
    Try to remove one by one the fields and the parameters until the error disappears. Side note, the Paramenters collection has an AddRange method. No need to have a Foreach and add them one by one. – Steve Jun 29 '22 at 16:41
  • `OleDbConnection` shouldn't be a class variable. It should be part of a [Using statement](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement) - the connection should be opened/closed each time. Also [OleDb](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb?view=netframework-4.8) doesn't use named parameters, so you may as well as use `?` in the Values part of the insert statement so that you don't forget this. Using a name where the parameter is added (to the command) can be useful for debugging though. – Tu deschizi eu inchid Jun 29 '22 at 16:48
  • Because OLE DB doesn't use named parameters, the order in which the parameters are added is important. If a parameter is used more than once in the statement, it needs to be added again. – Tu deschizi eu inchid Jun 29 '22 at 16:50
  • There's some code in this [post](https://stackoverflow.com/a/69638011/10024425) which may be helpful. Look under "Create a class (name: HelperAccess.vb)". Start by looking at functions `TblOfficialsInsert` and `TblOfficialsExecuteNonQuery`. – Tu deschizi eu inchid Jun 29 '22 at 16:56
  • The information about OLE DB using positional parameters can be found in [OleDbParameter](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbparameter?view=netframework-4.8#remarks) – Tu deschizi eu inchid Jun 29 '22 at 17:04
  • Steve- That's what I've been doing. I've even rebuilt the table 1 field at a time. I test and they work, so I move on to the next parameter or the one after that, and suddenly that one doesn't want to work again. So far everything through "Accident Time" isn't giving me problems. After that, the fields don't want to cooperate after I stop focusing on them. I have a bunch of toddlers for fields that want my undivided attention apparently. – Catherine Wisian Jun 29 '22 at 17:29
  • Right now for testing, i'm just putting in mumbo jumbo alk;dsjf;ds so it'll be like 10 characters max. – Catherine Wisian Jun 29 '22 at 17:31
  • So you know what is the field that gives you the problem. AccidentDate is a datetime field, but you use AddWith passing a Text property from a label. And a Text property is a string. Now Access needs to convert that string to a date that it can understood and it fails. What is the text value of that label? – Steve Jun 29 '22 at 19:06
  • that one has worked. I'm now rebuilding the whole thing again, and rebuilding the table a field at a time, as I add it to the command. I've already fixed those. they weren't causing problems before, but just in case. I'm not to the point of where i've had the problems before. so here goes. – Catherine Wisian Jun 29 '22 at 19:27
  • So I rebuilt the whole thing again, including the form so in case there was a glitch there it's gone. I did fix those formatting errors where text fields were going to dates, although they were coming in as dates so they shouldn't have caused a big issue and they were working pretty solidly, but who knows. I've gotten everything re-done now and things are working (so far) I'm just gonna blame COVID brain. Thanks everyone for the help. – Catherine Wisian Jun 29 '22 at 19:48
  • 1
    Pay a lot of attention to string passed to fill date fields. They are never right. When you need a date in the database pass a date as parameter. – Steve Jun 29 '22 at 19:58
  • 2
    I would consider maybe using the OleDbParameterCollection.Add(String, OleDbType) overload, that way you can 100% control the types that you're working with. The Add(String, Object) is in all possibility doing some implicit and unexpected type casting based on the value you are passing in – Hursey Jun 29 '22 at 20:13

0 Answers0