Need Assistance Corrected a Data Type Mismatch Error for INSERT Statement

Hello Experts.

I need some assistance with solving an error when trying to do an INSERT to a OleDb table. Ideally, it would be helpful if I could learn how to get more useful information back from the error handler, but I've never learned how to do that. The error detail is as follows:
System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147217913
  HResult=-2147217913
  Message=Data type mismatch in criteria expression.
  Source=Microsoft JET Database Engine
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at SNAP.frmSNAP.CreateMatchesRecord() in C:\Users\tony.gardner\Documents\Visual Studio 2013\Projects\SNAP\SNAP\frmSnapMain.vb:line 1342
       at SNAP.frmSNAP.PreMatchCheckList() in C:\Users\tony.gardner\Documents\Visual Studio 2013\Projects\SNAP\SNAP\frmSnapMain.vb:line 978
       at SNAP.frmSNAP.btnStartMain_Click(Object sender, EventArgs e) in C:\Users\tony.gardner\Documents\Visual Studio 2013\Projects\SNAP\SNAP\frmSnapMain.vb:line 963
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.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(IntPtr 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 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at SNAP.My.MyApplication.Main(String[] Args) in :line 81
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly 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.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Open in new window


Just so you know, the table's key field was originally setup as an integer but I needed to change it to a string. I'm fairly certain that I've found all the places where this needed to be corrected, but you never know. Here's the code I'm trying to run. The error occurs on the ExecuteNonQuery line. I tried stepping into this, but I don't know how to get to the code inside this "black box", so I just don't know which field has a Data Type Mismatch:
    Private Sub CreateMatchesRecord()
        Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapterMatches)
        Dim CmdTxt As String = command_builder.GetInsertCommand.CommandText
        Using con As New OleDbConnection(My.Settings.SNAPConnectionString)
            con.Open()
            Using cmd As New OleDbCommand
                cmd.Connection = con
                Dim MatchKey As String = DateTime.Now.ToString("yyMMdd") & "-" & Format(MN, "00")
                Dim CurrDate As DateTime = DateTime.Now.ToString("d")
                Dim SchedKey As Integer = GetCurrSchedKey(CurrDate)
                If SchedKey = 0 Then
                    Dim MsgOpts As MsgBoxStyle = vbYesNo + vbQuestion + vbDefaultButton2
                    Dim MsgText As String = "No APA events are Scheduled for today. Create a practice event?"
                    If MsgBox(MsgText, MsgOpts, "SNAP - Practice Match") = vbNo Then Exit Sub
                End If
                Dim Match_No As Integer = MN
                Dim Play_1_ID As Integer = CInt(PT(PN, 0).Text), Play_2_ID As Integer = CInt(PT(PN, 1).Text)
                Dim Team_1_ID As Integer = CInt(PT(NO, 0).Text), Team_2_ID As Integer = CInt(PT(NO, 1).Text)
                Dim Play_1_SL As Integer = CInt(PT(SK, 0).Text), Play_2_SL As Integer = CInt(PT(SK, 1).Text)
                Dim Play_1_DS As Integer = 0, Play_2_DS As Integer = 0
                Dim Play_1_MP As Integer = 0, Play_2_MP As Integer = 0
                Dim TotInning As Integer = 0
                Dim Beg_Time As DateTime = DateTime.Now, End_Time As DateTime = DateTime.Now
                Dim Play_1_TP As Integer = 0, Play_2_TP As Integer = 0
                Dim Play_1_TO As Integer = 0, Play_2_TO As Integer = 0
                cmd.CommandText = "INSERT INTO Matches (Match_Key, Schedule_Key, Match_No, " _
                  & "Player_1_ID, Player_2_ID, Team_1_ID, Team_2_ID, Player_1_SL, Player_2_SL, " _
                  & "Player_1_DS, Player_2_DS, Player_1_MP, Player_2_MP, Player_1_TP, Player_2_TP, " _
                  & "Player_1_TO, Player_2_TO, Total_Innings, Start_Time, End_Time) " _
                  & "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                MsgBox(CmdTxt)
                cmd.Parameters.Add(New OleDbParameter("Match_Key", MatchKey))
                cmd.Parameters.Add(New OleDbParameter("Schedule_Key", SchedKey))
                cmd.Parameters.Add(New OleDbParameter("Match_No", Match_No))
                cmd.Parameters.Add(New OleDbParameter("Player_1_ID", Play_1_ID))
                cmd.Parameters.Add(New OleDbParameter("Player_2_ID", Play_2_ID))
                cmd.Parameters.Add(New OleDbParameter("Team_1_ID", Team_1_ID))
                cmd.Parameters.Add(New OleDbParameter("Team_2_ID", Team_2_ID))
                cmd.Parameters.Add(New OleDbParameter("Player_1_SL", Play_1_SL))
                cmd.Parameters.Add(New OleDbParameter("Player_2_SL", Play_2_SL))
                cmd.Parameters.Add(New OleDbParameter("Player_1_DS", Play_1_DS))
                cmd.Parameters.Add(New OleDbParameter("Player_2_DS", Play_2_DS))
                cmd.Parameters.Add(New OleDbParameter("Player_1_MP", Play_1_MP))
                cmd.Parameters.Add(New OleDbParameter("Player_2_MP", Play_2_MP))
                cmd.Parameters.Add(New OleDbParameter("Player_1_TP", Play_1_TP))
                cmd.Parameters.Add(New OleDbParameter("Player_2_TP", Play_2_TP))
                cmd.Parameters.Add(New OleDbParameter("Player_1_TO", Play_1_TO))
                cmd.Parameters.Add(New OleDbParameter("Player_2_TO", Play_2_TO))
                cmd.Parameters.Add(New OleDbParameter("Total_Innings", TotInning))
                cmd.Parameters.Add(New OleDbParameter("Start_Time", Beg_Time))
                cmd.Parameters.Add(New OleDbParameter("End_Time", End_Time))
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

Open in new window

You'll also note that I've created an OleDbDataAdapter so I can see what the correct CommandText string is.

Okay, so using the DataSet designer, I can see that Match_Key is a String, Begin/End Date are DateTime and all other fields are Integers. I also found that had to click on the MatchesTableAdapter and dig through each of the Commands as they also have a data type (found under Parameters).
Tony GardnerSr. Programmer/AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tony GardnerSr. Programmer/AnalystAuthor Commented:
Quick Update:
I did find that the OleDbParameter Collection Editor still had Match_Key as an Integer, but when I click OleDbType, 'String' is not an option. I've tried VarChar and Char without success. There's also one called "BSTR" (Binary String?). What the correct data type I should choose?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There's no way for us to know which parameter is the wrong datatype, since we don't know your table schema.

Can you post the populated CommandText?
0
Tony GardnerSr. Programmer/AnalystAuthor Commented:
Certainly. Let me know if I'm off base, but I thought this segment of XML in SNAPDataSet.xsd would be relevant:
                <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>INSERT INTO `Matches` (`Match_Key`, `Schedule_Key`, `Match_No`, `Player_1_ID`, `Player_2_ID`, `Team_1_ID`, `Team_2_ID`, `Player_1_SL`, `Player_2_SL`, `Player_1_DS`, `Player_2_DS`, `Player_1_MP`, `Player_2_MP`, `Player_1_TP`, `Player_2_TP`, `Player_1_TO`, `Player_2_TO`, `Total_Innings`, `Start_Time`, `End_Time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="String" Direction="Input" ParameterName="Match_Key" Precision="0" ProviderType="VarWChar" Scale="0" Size="0" SourceColumn="Match_Key" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Schedule_Key" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Schedule_Key" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Match_No" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Match_No" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Player_1_ID" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Player_1_ID" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Player_2_ID" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Player_2_ID" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Team_1_ID" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Team_1_ID" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Team_2_ID" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Team_2_ID" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Player_1_SL" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Player_1_SL" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="Player_2_SL" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="Player_2_SL" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="Player_1_DS" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="Player_1_DS" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="Player_2_DS" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="Player_2_DS" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="Player_1_MP" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="Player_1_MP" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="Player_2_MP" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="Player_2_MP" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Player_1_TP" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Player_1_TP" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Player_2_TP" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Player_2_TP" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Player_1_TO" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Player_1_TO" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="Player_2_TO" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Player_2_TO" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="Total_Innings" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="Total_Innings" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="Start_Time" Precision="0" ProviderType="Date" Scale="0" Size="0" SourceColumn="Start_Time" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="End_Time" Precision="0" ProviderType="Date" Scale="0" Size="0" SourceColumn="End_Time" SourceColumnNullMapping="false" SourceVersion="Current" />
                    </Parameters>
                  </DbCommand>
                </InsertCommand>

Open in new window

I've also attached some pictures of the original Access table definition, Data Designer and Table Adapter properties.
Matches-Table.pdf
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm looking for the SQL Statement from the cmd object.
0
Tony GardnerSr. Programmer/AnalystAuthor Commented:
Hmmm... Not sure that I'm understanding your request, Scott. The actual SQL command that is to be executed is hidden -- or at least, I don't know where to find it. None of the properties or methods appear to give that type of info.

I was able to browse the cmd object and its myriad of properties using the debugger, and can see all the variables I'm sending, and I can even see a more detailed error message buried in there:
ServerVersionNormalized = 'DirectCast(DirectCast(cmd.events.parent, System.Data.Common.DbCommand).Connection, System.Data.OleDb.OleDbConnection)._innerConnection.ServerVersionNormalized' threw an exception of type 'System.NotSupportedException'

So, if there are no other methods that I can use for identifying the source of this type of error, I'm not sure where to go from here.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This:

MsgBox(CmdTxt)

Show a screenshot of the msgbox
0
Tony GardnerSr. Programmer/AnalystAuthor Commented:
My apologies, Scott. That was the first thing I thought you might be asking for, but since your essentially had the output from the CommandText XML tag, and the fact that it didn't have any Data Type info, that it wasn't relevant.

Was the "ServerVersionNormalized" error text not helpful? I also still need to know if VarChar is okay to use since String is not an option on the OleDb.

Worst case scenario, we can remove the OleDbConnection, and that is likely to allow the transaction to complete.
Output from MsgBox(CmdTxt)
0
Tony GardnerSr. Programmer/AnalystAuthor Commented:
SOLUTION IDENTIFIED:

When including dates/times in an OleDbParameter, the variable specified in the corresponding INSERT/UPDATE must be defined as an OLE Automation date. The following were the key code changes required to successfully execute the INSERT statement (and would also work in the originally submitted UPDATE code snippet:
                Dim Beg_Time As Double = DateTime.Now.ToOADate
                Dim End_Time As Double = DateTime.Now.ToOADate

Open in new window


That's it!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony GardnerSr. Programmer/AnalystAuthor Commented:
As Scott initially stated, there is simply no way for him to know which of my 20 fields were causing the Data Type Mismatch error.

In the end, the only way to find the error was through lengthy exploration and hard work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.