Data type mismatch in criteria expression

[bloukewer]

Hi everyone.

I get the error: "Data type mismatch in criteria expression", whenever I try to insert a record that contains an empty string value in one of it's fields. I'm using MS Access as the database, and I know the error is due to the fact that Access is very stronly typed. Is there a way to insert an empty string without resorting to 'evil' ways such as replacing it with a space? Can I turn of the strong typing, some way or the other?

Regards,
Bloukewer

 

[jmcilhinney]

How exactly are you trying to insert this value? What is the column type and what is the parameter type? Also, what does your SQL look like?

 

[bloukewer]

OK, here follows the statement that creates the stored procedure. The stored procedure creates successfully and displays under queries:

makeSP("CREATE PROC procDeductionInsert(iIsPretax YesNo,iClearanceNo text,iAmount Double,iName text,iDescription text,iCode text,iPSNO Long) AS " & _
"INSERT INTO Deduction(IsPretax,ClearanceNo,[Amount],[Name],[Description],

,PSNO) " & _
        "VALUES(iIsPretax,iClearanceNo,iAmount,iName,iDescription,iCode,iPSNO);")[/QUOTE] 

Here follows the procedure that passes the parameters through as an arraylist to the next procedure that executes the stored procedure:
 [QUOTE]   Public Sub insertDeduction(ByVal aDeduction As DEDUCTION)
        Dim al As New ArrayList
        al.Add(New OleDbParameter("iIsPretax", aDeduction.ISPRETAX))
        al.Add(New OleDbParameter("iClearanceNo", aDeduction.CLEARANCENO))
        al.Add(New OleDbParameter("iName", aDeduction.NAME))
        al.Add(New OleDbParameter("iDescription", aDeduction.DESCRIPTION))
        al.Add(New OleDbParameter("iCode", aDeduction.CODE))
        al.Add(New OleDbParameter("iPSNO", aDeduction.PSNO))
        al.Add(New OleDbParameter("iAmount", aDeduction.AMOUNT))
        ExecuteNQSP("procDeductionInsert", al)
    End Sub[/QUOTE] 

And here is the sub called ExecuteNQSP that takes the stored procedure name as parameter and also the arraylist of parameters to pass to the stored procedure itself. This sub is thus responsible for running the SP that was created earlier:
 [QUOTE] Private Sub ExecuteNQSP(ByVal ProcName As String, ByVal SPParams As ArrayList)
        Dim cmd As New OleDbCommand
        Dim prm As OleDbParameter
        cmd.Parameters.Clear()
        For Each prm In SPParams
            cmd.Parameters.Add(prm)
        Next
        cmd.Connection = con
        cmd.CommandText = "EXECUTE " & ProcName
        Try
            con.Open()
        Catch
            con.Close()
            con.Open()
        End Try
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub[/QUOTE] 

The fields for table Deduction is as follows:
 [QUOTE]ID : Autonumber
IsPretax : YesNo
ClearanceNo : Text
Amount : Number,Double
Name : Text
Description : Text
Code : Text
PSNO : Number,Long Integer
[/QUOTE] 
Note:  I did this in about roughly one hundred and thirty other cases, but this is the only one that is giving me troubles.

 

[jmcilhinney]

Sorry to have been incommunicado for a while. I wanted to wait until I had time to sit down and read this properly. Are you trying to write this empty string to one of the fields that is not of type Text? I would have thought that that would be the only way you would get a data type mismatch. The only other thing I can think of is that you have set the Allow Zero Length property to No, so an empty string is not allowed.

 

[bloukewer]

Thanks, but this same error popped up on several more places. Now I am not sure anymore whether it is the "empty strings" causing this problem. I have been pulling my hair out about this because it has really been slowing down my progress.

Another problem: when setting the input and display formats for a date-field in a ms access table, it works fine. However, some tables automatically "deletes" this newly entered formats where as others don't. Could this be a glitch in Access?

 

[jmcilhinney]

With regards to your Data Type Mismatch, I'd suggest specifying an OleDbType when you create your parameters rather than allowing the type to be defined implicitly by the value. That way, if you are assigning a value of the wrong type you should get an exception at the point you create the parameter rather than when you try to execute the procedure.

With regards to the date format, I've never seen that happen before so I can't help there. It's always a good idea to check the MS Knowledge Base if you suspect a potential bug in MS software.

 

[bloukewer]

OK, thanks. I'll try that and post again if that doesn't solve the problem.

Regarding Access: That is not the only possible bug I know of. When I copy a database (that contains stored procedures) from one computer to another, the stored procedures does not show (under queries) of the database, yet my VB.Net program is able to successfully call them up. Strange... but true.

 

[bloukewer]

Nope. I tried passing the oledb data type through the oledbparameter constructor, but unfortunately this did not do the trick. I still get the same error message.

I think Microsoft should hire me to do product testing. If there's a bug, I WILL stumble accross it.

 

[bloukewer]

OK, problem solved. Apparently it's just VB's way of telling me that the parameter's must be passed in the same order that it was specified in during the SP's creation.