Link to home
Start Free TrialLog in
Avatar of Tony Gardner
Tony GardnerFlag for United States of America

asked on

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).
Avatar of Tony Gardner
Tony Gardner
Flag of United States of America image

ASKER

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?
Avatar of Scott McDaniel (EE MVE )
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?
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
I'm looking for the SQL Statement from the cmd object.
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.
This:

MsgBox(CmdTxt)

Show a screenshot of the msgbox
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.
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Tony Gardner
Tony Gardner
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.