Tony Gardner
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:
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:
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).
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:
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
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).
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?
Can you post the populated CommandText?
ASKER
Certainly. Let me know if I'm off base, but I thought this segment of XML in SNAPDataSet.xsd would be relevant:
Matches-Table.pdf
<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>
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.
ASKER
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:
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.
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.pa rent, System.Data.Common.DbComma nd).Connec tion, System.Data.OleDb.OleDbCon nection)._ innerConne ction.Serv erVersionN ormalized' threw an exception of type 'System.NotSupportedExcept ion'
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
MsgBox(CmdTxt)
Show a screenshot of the msgbox
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
In the end, the only way to find the error was through lengthy exploration and hard work.
ASKER
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?