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