Data Type Mismatch / SQL Issue 1

[JFrog]

Sorry for my very lack of understanding.... and Thanks for yours.

When I try and run the following SQL Statement I get the error message "Data Type mismatch"

("Update Mas90Data Set Result = 'REVRSL' Where CheckNumber = '" & Mas90CheckNumber & "'", dbconnection)

If I remove the "Where portion of the statement then it will run... but of course updates all the records and not just the ones I want.

 

[Qik3Coder]

This is actually a SQL question, but here.

You are having a data conversion issue. You don't need quotes around your variable value, but you will want to check for a value, otherwise you will get a syntax error.

("Update Mas90Data Set Result = 'REVRSL' Where CheckNumber = " & Mas90CheckNumber , dbconnection)

style recommendation:
You should build your sql statement as a string variable, because that is what it does anyway, but then you can inspect it before it gets sent.

So you wind up with something like:

Dim strSQL as New String("Update Mas90Data Set Result = 'REVRSL' Where CheckNumber = ")
strSQL += Mas90CheckNumber 
...(strSQL, dbconnection)

-The answer to your problem may not be the answer to your question.

 

[JFrog]

I am very very low skilled programmer... I dont understand the suggestion about building as a string varible. I mean I understand the concept... but not exactly ready to try it.

however I did try ("Update Mas90Data Set Result = 'REVRSL' Where CheckNumber = '" & Mas90CheckNumber, dbconnection)
and now I get
Syntax error in string query expression 'CheckNumber = '23023'

Thanks

 

[Qik3Coder]

JFrog,

Working with variables is a very basic concept.
When you are working with numbers and you add them it looks like this:

Dim a,b,c as integer
a = 3
b = 4
c = a + b
MsgBox("C = " + c.ToString)

Working with String variables is a similar bit of code that behaves slightly differently.

Dim a,b,c as String
a = "Hello,"
b = " World"
c = a + b
MsgBox("C = [" + c + "]")

VB.Net has a "shortcut operator" where you can leave off part of the equation and it can still figure it out.

Dim a,b,c as integer
a = 3
b = 4
c = a 
c += b
MsgBox("C = " + c.ToString)

The line "c = c + b" is the same as " c += b".


Now back to your regularly scheduled program...

No quotes around the check number. That is your problem.


you should be able to copy the first two lines of my example and paste it into your code.


-The answer to your problem may not be the answer to your question.

 

[JFrog]

I pasted this in and nothing happened... did I miss something? Nothing changed in my table...


Dim strSQL As New String("Update Mas90Data Set Result = 'REVRSL' Where CheckNumber = ")
strSQL += Mas90CheckNumber

 

[Qik3Coder]

JFrog,

I am not sure i want to know where you pasted them in...

You need to tell it to use the new variable that you created, or else it doesnt do you any good to have it.
I don't know exactly what you have, so i am going to try to guess.

This line, which i am assuming still exists:

("Update Mas90Data Set Result = 'REVRSL' Where CheckNumber = '" & Mas90CheckNumber & "'", dbconnection)

should now look like this:

(strSQL, dbconnection)

-The answer to your problem may not be the answer to your question.

 

[JFrog]

This is everything below... Sorry to keep pestering you. Maybe this is just out of my league... I can write VBA all day but this sux...


Public Class Form1
'Below is my attempt to handle Reversal in Mas90
'Should connect to DB then find Reversal flags....
'then find matching check and flag it for reversal as well in the result section
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim dbconnection As System.Data.OleDb.OleDbConnection
dbconnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data Source=U:\Jeremiah\Projects\Bank Recon\Database\ReconDB.mdb")
dbconnection.Open()

Dim SelectCmd As System.Data.OleDb.OleDbCommand
SelectCmd = New System.Data.OleDb.OleDbCommand("Select * from Mas90Data Where ReversalFlag = 'REVRSL'", dbconnection)
Dim dbreader As System.Data.OleDb.OleDbDataReader
dbreader = SelectCmd.ExecuteReader

Do While dbreader.Read
Dim Mas90CheckNumber As String
Dim Mas90CheckDate As Date
Dim Mas90Code As String
Dim Mas90Description As String
Dim Mas90CheckAmount As Single
Dim Mas90ReversalFlag As String
Dim Mas90Result
Mas90CheckNumber = dbreader("CheckNumber")
Mas90CheckDate = dbreader("CheckDate")
Mas90Code = dbreader("Code")
Mas90Description = dbreader("Description")
Mas90CheckAmount = dbreader("CheckAmount")
Mas90ReversalFlag = dbreader("ReversalFlag")
Mas90Result = dbreader("Result")
'Need to select from DB where CheckNumber = Mas90CheckNumber then Flag as REVRSL in Result Section
Dim UpdateCmd As System.Data.OleDb.OleDbCommand
Dim strSQL As New String("Update Mas90Data Set Result = 'REVRSL' Where CheckNumber = ")
strSQL += Mas90CheckNumber
UpdateCmd = New System.Data.OleDb.OleDbCommand(strSQL, dbconnection)
UpdateCmd.ExecuteNonQuery()
Loop
End Sub
End Class

Data mismatch error this way....

 

[Qik3Coder]

which line do you get the error? Can you paste the stack trace (when it "blows up" hover over the error and it will show you the exception. click view detail, expand the "stack trace" option, right click on it and select something like "view html". Paste that here.

What is the Check Number datatype in the database?

It doesnt seem to make sense, you pull the value right out, then use it to update, but it isnt kosher with it.

-The answer to your problem may not be the answer to your question.

 

[Sorwen]

I'm not saying this has to do with the error, but I had some strange errors some times if I used += with something that should be totally a string (in vs 2k5). I suggest trying &= instead.

-I hate Microsoft!
-Forever and always forward.

 

[JFrog]

I believe I had several things wrong with my SQL Statement and I really appreciate all of you guys taking the time to help me. In the end the problem was checknumber defined as a text field in the DB and once I switched this number field... all of your examples worked. I especially appreciate the Qik3Coder example about testing as string first. THANKS TO EVERYONE.