Data type mismatch in criteria expression.

Question

1.00/5 (1 vote)

See more:

I am trying the insert into statement i searched online , no errors found but when i run it

((((Data type mismatch in criteria expression.)))))

'this error appears
what may the cause be?

What I have tried:

VB

'this is what i have tried

Dim sql As String = "INSERT INTO Elementary VALUES(@SchoolName, @SchoodID, @KM, @KF,@KT,@G1M,@G1F,@G1T,@G2M,@G2F,@G2T,@G3M,@G3F,@G3T,@G4M,@G4F,@G4T,@G5M,@G5F,@G5T,@G6M,@G6F,@G6T,@TM,@TF,@GT,@SY,@District,@Field2)"
        Using conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\plan.mdb")
            Using sqlCom = New System.Data.OleDb.OleDbCommand(sql, conn)
                conn.Open()
                sqlCom.Parameters.Add("@name", OleDbType.VarWChar).Value = SchoolNameTextBox.Text
                sqlCom.Parameters.Add("@pass", OleDbType.VarWChar).Value = SchoodIDTextBox.Text
                sqlCom.Parameters.Add("@sec", OleDbType.VarWChar).Value = KMTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = KFTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = KTTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G1MTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G1FTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G1TTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G2MTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G2FTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G2TTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G3MTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G3FTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G3TTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G4MTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G4FTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G4TTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G5MTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G5FTextBox.Text
                 sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G5TTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G6MTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G6FTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G6TTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = TMTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = TFTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = GTTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = SYTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = DistrictTextBox.Text
                sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = Field2TextBox.Text

                Dim icount As Integer = sqlCom.ExecuteNonQuery
            End Using
        End Using

Posted 9-Aug-20 17:53pm

Beginner213456

Updated 9-Aug-20 20:12pm

Patrice T

v3

Add a Solution

2 solutions

Solution 1

Quote:
((((Data type mismatch in criteria expression.)))))

No idea since I don't see any 'criteria' in this code.
But I have strong feeling that when some parameters appear in a query,

VB

Dim sql As String = "INSERT INTO Elementary VALUES(@SchoolName, @SchoodID, @KM, KF, @KT, @G1M, @G1F, @G1T, @G2M, @G2F, @G2T, @G3M, @G3F, @G3T, @G4M, @G4F, @G4T, @G5M, @G5F, @G5T, @G6M, @G6F, @G6T, @TM, @TF, @GT, @SY, @District, @Field2)"

the parameters definition must match in names and position.

VB

sqlCom.Parameters.Add("@name", OleDbType.VarWChar).Value = SchoolNameTextBox.Text
sqlCom.Parameters.Add("@pass", OleDbType.VarWChar).Value = SchoodIDTextBox.Text
sqlCom.Parameters.Add("@sec", OleDbType.VarWChar).Value = KMTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = KFTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = KTTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G1MTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G1FTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G1TTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G2MTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G2FTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G2TTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G3MTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G3FTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G3TTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G4MTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G4FTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G4TTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G5MTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G5FTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G5TTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G6MTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G6FTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = G6TTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = TMTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = TFTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = GTTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = SYTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = DistrictTextBox.Text
sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value = Field2TextBox.Text

Defining parameters with same name is certainly not the way to go.

Posted 9-Aug-20 18:17pm

Patrice T

Comments

Beginner213456 10-Aug-20 1:54am

I have checked and the parameters position and names are correct

Patrice T 10-Aug-20 2:01am

Do you mean that naming parameters "@sw" 26 times feel correct to you ?

Beginner213456 10-Aug-20 2:24am

sqlCom.Parameters.Add("@SchoolName", OleDbType.VarWChar).Value = SchoolNameTextBox.Text
sqlCom.Parameters.Add("@SchoodID", OleDbType.VarWChar).Value = SchoodIDTextBox.Text
sqlCom.Parameters.Add("@KM", OleDbType.VarWChar).Value = KMTextBox.Text
sqlCom.Parameters.Add("@KF", OleDbType.VarWChar).Value = KFTextBox.Text
sqlCom.Parameters.Add("@KT", OleDbType.VarWChar).Value = KTTextBox.Text
sqlCom.Parameters.Add("@G1M", OleDbType.VarWChar).Value = G1MTextBox.Text
sqlCom.Parameters.Add("@G1F", OleDbType.VarWChar).Value = G1FTextBox.Text
sqlCom.Parameters.Add("@G1T", OleDbType.VarWChar).Value = G1TTextBox.Text
sqlCom.Parameters.Add("@G2M", OleDbType.VarWChar).Value = G2MTextBox.Text
sqlCom.Parameters.Add("@G2F", OleDbType.VarWChar).Value = G2FTextBox.Text
sqlCom.Parameters.Add("@G2T", OleDbType.VarWChar).Value = G2TTextBox.Text
sqlCom.Parameters.Add("@G3M", OleDbType.VarWChar).Value = G3MTextBox.Text
sqlCom.Parameters.Add("@G3F", OleDbType.VarWChar).Value = G3FTextBox.Text
sqlCom.Parameters.Add("@G3T", OleDbType.VarWChar).Value = G3TTextBox.Text
sqlCom.Parameters.Add("@G4M", OleDbType.VarWChar).Value = G4MTextBox.Text
sqlCom.Parameters.Add("@G4F", OleDbType.VarWChar).Value = G4FTextBox.Text
sqlCom.Parameters.Add("@G4T", OleDbType.VarWChar).Value = G4TTextBox.Text
sqlCom.Parameters.Add("@G5M", OleDbType.VarWChar).Value = G5MTextBox.Text
sqlCom.Parameters.Add("@G5F", OleDbType.VarWChar).Value = G5FTextBox.Text
sqlCom.Parameters.Add("@G5T", OleDbType.VarWChar).Value = G5TTextBox.Text
sqlCom.Parameters.Add("@G6M", OleDbType.VarWChar).Value = G6MTextBox.Text
sqlCom.Parameters.Add("@G6F", OleDbType.VarWChar).Value = G6FTextBox.Text
sqlCom.Parameters.Add("@G6T", OleDbType.VarWChar).Value = G6TTextBox.Text
sqlCom.Parameters.Add("@TM", OleDbType.VarWChar).Value = TMTextBox.Text
sqlCom.Parameters.Add("@TF", OleDbType.VarWChar).Value = TFTextBox.Text
sqlCom.Parameters.Add("@GT", OleDbType.VarWChar).Value = GTTextBox.Text
sqlCom.Parameters.Add("@SY", OleDbType.VarWChar).Value = SYTextBox.Text
sqlCom.Parameters.Add("@District", OleDbType.VarWChar).Value = DistrictTextBox.Text
sqlCom.Parameters.Add("@Field2", OleDbType.VarWChar).Value = Field2TextBox.Text

i changed it to that but the error is still the same

Patrice T 10-Aug-20 2:34am

Use Improve question to update your question.
So that everyone can pay attention to this information.

Can you give exact error message ?
The problem is that insert do not have a criteria clause.

Beginner213456 10-Aug-20 2:54am

'Data type mismatch in criteria expression.
'that is the error message
' i have changed my code to

Dim sql As String = "INSERT INTO Elementary (SchoolName,SchoodID,KM,KF,KT,G1M,G1F,G1T,G2M,G2F,G2T,G3M,G3F,G3T,G4M,G4F,G4T,G5M,G5F,G5T,G6M,G6F,G6T,TM,TF,GT,SY,District,Field2) VALUES(@SchoolName, @SchoodID, @KM, @KF,@KT,@G1M,@G1F,@G1T,@G2M,@G2F,@G2T,@G3M,@G3F,@G3T,@G4M,@G4F,@G4T,@G5M,@G5F,@G5T,@G6M,@G6F,@G6T,@TM,@TF,@GT,@SY,@District,@Field2)"
Using conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\plan.mdb")
Using sqlCom = New System.Data.OleDb.OleDbCommand(sql, conn)
conn.Open()
sqlCom.Parameters.Add("@SchoolName", OleDbType.VarWChar).Value = SchoolNameTextBox.Text
sqlCom.Parameters.Add("@SchoodID", OleDbType.VarWChar).Value = SchoodIDTextBox.Text
sqlCom.Parameters.Add("@KM", OleDbType.VarWChar).Value = KMTextBox.Text
sqlCom.Parameters.Add("@KF", OleDbType.VarWChar).Value = KFTextBox.Text
sqlCom.Parameters.Add("@KT", OleDbType.VarWChar).Value = KTTextBox.Text
sqlCom.Parameters.Add("@G1M", OleDbType.VarWChar).Value = G1MTextBox.Text
sqlCom.Parameters.Add("@G1F", OleDbType.VarWChar).Value = G1FTextBox.Text
sqlCom.Parameters.Add("@G1T", OleDbType.VarWChar).Value = G1TTextBox.Text
sqlCom.Parameters.Add("@G2M", OleDbType.VarWChar).Value = G2MTextBox.Text
sqlCom.Parameters.Add("@G2F", OleDbType.VarWChar).Value = G2FTextBox.Text
sqlCom.Parameters.Add("@G2T", OleDbType.VarWChar).Value = G2TTextBox.Text
sqlCom.Parameters.Add("@G3M", OleDbType.VarWChar).Value = G3MTextBox.Text
sqlCom.Parameters.Add("@G3F", OleDbType.VarWChar).Value = G3FTextBox.Text
sqlCom.Parameters.Add("@G3T", OleDbType.VarWChar).Value = G3TTextBox.Text
sqlCom.Parameters.Add("@G4M", OleDbType.VarWChar).Value = G4MTextBox.Text
sqlCom.Parameters.Add("@G4F", OleDbType.VarWChar).Value = G4FTextBox.Text
sqlCom.Parameters.Add("@G4T", OleDbType.VarWChar).Value = G4TTextBox.Text
sqlCom.Parameters.Add("@G5M", OleDbType.VarWChar).Value = G5MTextBox.Text
sqlCom.Parameters.Add("@G5F", OleDbType.VarWChar).Value = G5FTextBox.Text
sqlCom.Parameters.Add("@G5T", OleDbType.VarWChar).Value = G5TTextBox.Text
sqlCom.Parameters.Add("@G6M", OleDbType.VarWChar).Value = G6MTextBox.Text
sqlCom.Parameters.Add("@G6F", OleDbType.VarWChar).Value = G6FTextBox.Text
sqlCom.Parameters.Add("@G6T", OleDbType.VarWChar).Value = G6TTextBox.Text
sqlCom.Parameters.Add("@TM", OleDbType.VarWChar).Value = TMTextBox.Text
sqlCom.Parameters.Add("@TF", OleDbType.VarWChar).Value = TFTextBox.Text
sqlCom.Parameters.Add("@GT", OleDbType.VarWChar).Value = GTTextBox.Text
sqlCom.Parameters.Add("@SY", OleDbType.VarWChar).Value = SYTextBox.Text
sqlCom.Parameters.Add("@District", OleDbType.VarWChar).Value = DistrictTextBox.Text
sqlCom.Parameters.Add("@Field2", OleDbType.VarWChar).Value = Field2TextBox.Text

Dim icount As Integer = sqlCom.ExecuteNonQuery
End Using
End Using

Patrice T 10-Aug-20 3:00am

Use Improve question to update your question.
So that everyone can pay attention to this information.

By the way, by accepting a solution, you say that problem is solved.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-08-09T20:12:00

Solution 2

Start by explicitly listing the columns the parameters should go into:

SQL

INSERT INTO MyTable (Column1, Column2) VALUES (@C1, @C2)

If you don't, then SQL doesn't know exactly what you want to do, so it inserts values in "current table order". So if you have an IDENTITY ID column at the front (and you probably should) it will try to write to that first for example.
You then need to provide the parameters with matching names:

VB

sqlCom.Parameters.Add("@SchoolName", OleDbType.VarWChar).Value = SchoolNameTextBox.Text
sqlCom.Parameters.Add("@SchoodID", OleDbType.VarWChar).Value = SchoodIDTextBox.Text
...

And your ID column - unless that is a Guid / UNIQUEIDENTIFIER column you should not be passing it at all to SQL: SQL Server is a multiuser database system, and prcisifying ID values in your presentation software is a very bad idea - it leads to two or more users specifying the same value and then data gets hopelessly corrupted. Use an IDENTITY column (or a UNIQUEIDENTIFIER) and let SQL sort out the value.

Posted 9-Aug-20 20:12pm

OriginalGriff

Comments

Beginner213456 10-Aug-20 2:31am

Dim sql As String = "INSERT INTO Elementary (SchoolName,SchoodID,KM,KF,KT,G1M,G1F,G1T,G2M,G2F,G2T,G3M,G3F,G3T,G4M,G4F,G4T,G5M,G5F,G5T,G6M,G6F,G6T,TM,TF,GT,SY,District,Field) VALUES(@SchoolName, @SchoodID, @KM, @KF,@KT,@G1M,@G1F,@G1T,@G2M,@G2F,@G2T,@G3M,@G3F,@G3T,@G4M,@G4F,@G4T,@G5M,@G5F,@G5T,@G6M,@G6F,@G6T,@TM,@TF,@GT,@SY,@District,@Field2)"

is this correct?

OriginalGriff 10-Aug-20 3:05am

It looks better, but I don't have access to your DB to check the column names.
A quick check with my text editor says they match up though:

SchoolName     @SchoolName   
SchoodID       @SchoodID    
KM             @KM          
KF             @KF          
KT             @KT           
G1M            @G1M          
G1F            @G1F          
G1T            @G1T          
G2M            @G2M          
G2F            @G2F          
G2T            @G2T          
G3M            @G3M          
G3F            @G3F          
G3T            @G3T          
G4M            @G4M          
G4F            @G4F          
G4T            @G4T          
G5M            @G5M          
G5F            @G5F          
G5T            @G5T          
G6M            @G6M          
G6F            @G6F          
G6T            @G6T          
TM             @TM           
TF             @TF           
GT             @GT           
SY             @SY           
District       @District     
Field          @Field2

Beginner213456 10-Aug-20 3:19am

Those are the only columns on my table database

or perhaps . there is something missing on my code?

Beginner213456 10-Aug-20 3:24am

Those are the only columns on my table database

or perhaps . there is something missing on my code?
or should i change my column properties to short text? because i used number

OriginalGriff 10-Aug-20 3:33am

If you have used number columns in the DB, then you have to pass actual numbers, not textbox content: use int.TryParse, double.TryParse, and so on as appropriate for the number column type to check and convert the textbox contents first, then only try to save to the DB if they all validate.
Never try to send unvalidated and unconverted user input to your DB, unless it's going to a text field. Once "bad data" gets into your DB, it's much, much harder to fix it!

Beginner213456 10-Aug-20 3:42am

'can you please show me some example?

Dim text As String = ""
Dim stringToInteger As Integer = Convert.ToInt32(TMTextBox.Text)

TMTextBox.Text = Convert.ToInt32(KMTextBox.Text) + Convert.ToInt32(G1MTextBox.Text) + Convert.ToInt32(G2MTextBox.Text) + Convert.ToInt32(G3MTextBox.Text) + Convert.ToInt32(G4MTextBox.Text) + Convert.ToInt32(G5MTextBox.Text) + Convert.ToInt32(G6MTextBox.Text)
TFTextBox.Text = Convert.ToInt32(KFTextBox.Text) + Convert.ToInt32(G1FTextBox.Text) + Convert.ToInt32(G2FTextBox.Text) + Convert.ToInt32(G3FTextBox.Text) + Convert.ToInt32(G4FTextBox.Text) + Convert.ToInt32(G5FTextBox.Text) + Convert.ToInt32(G6FTextBox.Text)
GTTextBox.Text = Convert.ToInt32(KTTextBox.Text) + Convert.ToInt32(G1TTextBox.Text) + Convert.ToInt32(G2TTextBox.Text) + Convert.ToInt32(G3TTextBox.Text) + Convert.ToInt32(G4TTextBox.Text) + Convert.ToInt32(G5TTextBox.Text) + Convert.ToInt32(G6TTextBox.Text)
MessageBox.Show("Data Computed")

'i used that code for the computation for totals but theres an error saying about string conversion to integer.

OriginalGriff 10-Aug-20 4:30am

What part of "int.TryParse" looks like "Convert.ToInt32"?

Beginner213456 10-Aug-20 5:01am

i changed convert.toint32 into int.tryparse
but it says overloan

OriginalGriff 10-Aug-20 6:08am

Did you perchance forget to read the documentation on how to use the TryParse methods?

Beginner213456 10-Aug-20 22:37pm

using System;

public class Example
{
public static void Main()
{
String[] values = { null, "160519", "9432.0", "16,667",
" -322 ", "+4302", "(100);", "01FA" };
foreach (var value in values)
{
int number;

bool success = Int32.TryParse(value, out number);
if (success)
{
Console.WriteLine("Converted '{0}' to {1}.", value, number);
}
else
{
Console.WriteLine("Attempted conversion of '{0}' failed.",
value ?? "<null>");
}
}
}
}
// The example displays the following output:
// Attempted conversion of '<null>' failed.
// Converted '160519' to 160519.
// Attempted conversion of '9432.0' failed.
// Attempted conversion of '16,667' failed.
// Converted ' -322 ' to -322.
// Converted '+4302' to 4302.
// Attempted conversion of '(100);' failed.
// Attempted conversion of '01FA' failed.

"This is code example i saw at a try parse documentation provided by microsoft
but i cant seem to find how to apply it on my code"

OriginalGriff 11-Aug-20 1:40am

1) That's C# code, and all your other code is VB. C# will not compile in a VB project. Go back to the MS documentation and select "VB" and teh code samples will change language.
2) If any of them fail, you don't just put up a message and carry on: you don;t even try to put anything to the DB.