Run time error '-2147217913 (80040e07)': Data type mismatch in criteria expression.

stephenlecompte
10-18-2004, 10:05 AM
I'm running Visual Basic ADODB code on a Access 2000 .mdb database.
I keep getting a data type mismatch in criteria expression using the following code: It highlights cn.Execute gUpdate

Anybody have some idea as to what might be causing it?
I've tried removing my table and renaming it to PROJ and that didn't work (without the dashes and underscores)
In my immediate window - I can do a "print iAuto" and it comes up with 2183.
There is a 2183 in my Auto field (which happens to be my Autonumber field in table PROJ_EQ-CPB.) There is a Deployed field which happens to be Boolean. For row 2183, the Deployed field is already checked off as True but I wouldn't think that would matter on a UPDATE statement. Any replies would be much appreciated.

Dim iAuto As Integer
iAuto = CPBRow.Fields("Auto")
Set cn = Nothing
Set cn = New ADODB.Connection
cn.ConnectionString = vntNameconnection & ";" & _
"Data Source=" & vntServer & vntNamedata & ";" & vntEndconnection
cn.Open
gUpdate = "UPDATE [PROJ_EQ-CPB] SET [Deployed] = True WHERE [Auto] = '" & iAuto & "'"
cn.Execute gUpdate
Set cn = Nothing

Granty
10-18-2004, 10:17 AM
I'm running Visual Basic ADODB code on a Access 2000 .mdb database.
I keep getting a data type mismatch in criteria expression using the following code: It highlights cn.Execute gUpdate

Anybody have some idea as to what might be causing it?
I've tried removing my table and renaming it to PROJ and that didn't work (without the dashes and underscores)
In my immediate window - I can do a "print iAuto" and it comes up with 2183.
There is a 2183 in my Auto field (which happens to be my Autonumber field in table PROJ_EQ-CPB.) There is a Deployed field which happens to be Boolean. For row 2183, the Deployed field is already checked off as True but I wouldn't think that would matter on a UPDATE statement. Any replies would be much appreciated.

Dim iAuto As Integer
iAuto = CPBRow.Fields("Auto")
Set cn = Nothing
Set cn = New ADODB.Connection
cn.ConnectionString = vntNameconnection & ";" & _
"Data Source=" & vntServer & vntNamedata & ";" & vntEndconnection
cn.Open
gUpdate = "UPDATE [PROJ_EQ-CPB] SET [Deployed] = True WHERE [Auto] = '" & iAuto & "'"
cn.Execute gUpdate
Set cn = Nothing


What data types are [Deployed], [Auto] and iAuto?

stephenlecompte
10-18-2004, 10:36 AM
What data types are [Deployed], [Auto] and iAuto?

Deployed is Yes/No (Data Type)
Auto is Long Integer (Autonumber)
iAuto is Integer

It would seem I need to change iAuto to:

Dim lAuto As Long

and change all iAuto as lAuto...
I've done that and still it gives me the same error.

Granty
10-18-2004, 10:46 AM
Numeric datatypes need no single quotes.

gUpdate = "UPDATE [PROJ_EQ-CPB] SET [Deployed] = True WHERE [Auto] = " & iAuto

HardCode
10-18-2004, 11:19 AM
If Auto is a long integer in the database, you should make your VB variable iAuto into a Long (lAuto).

stephenlecompte
10-18-2004, 12:31 PM
Numeric datatypes need no single quotes.

gUpdate = "UPDATE [PROJ_EQ-CPB] SET [Deployed] = True WHERE [Auto] = " & iAuto


Thanks for everyone's replies! Both are making it work.

stephenlecompte
07-06-2005, 02:45 PM
I've got the following to take info from an Access database and place it in SQL Server but...
one column is an image data type only.
right where [Cutsheet] = that's the column name for the image data type with a length of 16 - but my problem is that it will not take the image correctly since I'm using a single quote ' for a data type with Text. I need to use the proper symbol for an Image data type.

Any suggestions, hard code?


SysCmd acSysCmdSetStatus, "Importing Cutsheets data..."
g_sSelect = "SELECT [Alt_Code], [Cutsheets] FROM PROJ_ME WHERE [Cutsheets] <> Null"
Set g_rstData = CurrentDb.OpenRecordset(g_sSelect)
Do Until g_rstData.EOF
sSelect = "UPDATE [TMP_PROJ_ME] SET "
sSelect = sSelect & "[Cutsheet_URL] = '" & g_rstData.Fields("Cutsheets") & "'"
sSelect = sSelect & " , [Cutsheet_Modify_Date] = " & Date
sSelect = sSelect & " , [Cutsheet] = '" & g_rstData.Fields("Cutsheets") & "'"
sSelect = sSelect & " WHERE [Alt_Code] = " & g_rstData.Fields("Alt_Code")
cnSQLData.Execute sSelect
g_rstData.MoveNext
Loop
g_rstData.Close
Set g_rstData = Nothing

HardCode
07-06-2005, 03:08 PM
Are you required to store the actual image in the database? Could you instead store the path to the image on the DB server? This will greatly reduce the time it takes to back up the database each night. It will also keep the size of the DB down.

stephenlecompte
07-06-2005, 03:25 PM
Are you required to store the actual image in the database? Could you instead store the path to the image on the DB server? This will greatly reduce the time it takes to back up the database each night. It will also keep the size of the DB down.

Oh yes, I agree with you.
We received this ASP.NET web application which uses C# - which I am not too familiar yet at all to adjust too many things.
So just to get it working and to know that I'm on the right track - I would at least like to start out by getting a couple of images into SQL Server and then when it starts ballooning too bad - I could go back and get a C# translator and adjust the proper code. I mean we're stuck with this application for now from this consulting firm that recommended this option for us. Sometimes adding in an image in SQL Server like this becomes a necessary option.

I was hoping the SELECT statement to add in an image would be simple or maybe do I need another type of syntax?

Also just an fyi - this company probably took the suggestion of the 3rd option of storing the image as binary (using it in SQL Server instead of Access)
http://www.jamiessoftware.tk/articles/handlingimages.html
Please take a look at it when you have a chance.

I can't download anything here at work. That's why I'm unable to look at the examples - thank you!

ALSO - after doing some extended research, I'm stumbling over the following code:
SysCmd acSysCmdSetStatus, "Importing PROJ_ME BLOB (actual image) data..."
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
sSelect = "SELECT [Cutsheet], [Cutsheet_URL] FROM [TMP_PROJ_ME] WHERE Left([Cutsheet_URL], 1) = 'I'"
rs.Open sSelect, cnSQLData, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
sText = rs.Fields("Cutsheet_URL")
'MsgBox rs.Fields("Cutsheet_URL")
Set st = New ADODB.Stream
st.Type = adTypeText
'sSelect = "SELECT [Cutsheet], [Cutsheet_URL] FROM [TMP_PROJ_ME] WHERE [Cutsheet_URL] = '" & rs.Fields("Cutsheet_URL") & "'"
st.Open 'sSelect, cnSQLData, adOpenStreamFromRecord
'If Not st.EOS Then
st.LoadFromFile sText
rs.Fields("Cutsheet").Value = st.ReadText
rs.Update
'End If
Set st = Nothing
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

In harmony with this website's application of the matter.
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx

But I keep getting confused between the examples.
The error I'm getting now is Run-time error '-2147217887 (80040e21)

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
with the highlight on:
rs.Fields("Cutsheet").Value = st.ReadText

Any help is appreciated - looking forward tomorrow.

MKoslof
07-07-2005, 04:17 PM
If you want to add a new record into a SQL Server database that is a blob or image type, review this example:

http://www.xtremevbtalk.com/showthread.php?t=169654

Now, if you are trying to EDIT an EXISTING field value, what is the defined field type? Are you using varbinary? And you are looping and replacing each binary field in the table, is that correct?

stephenlecompte
07-12-2005, 10:13 AM
If you want to add a new record into a SQL Server database that is a blob or image type, review this example:

http://www.xtremevbtalk.com/showthread.php?t=169654

Now, if you are trying to EDIT an EXISTING field value, what is the defined field type? Are you using varbinary? And you are looping and replacing each binary field in the table, is that correct?

I just want to sincerely thank you for answering this post with the above. I don't think you realize how much it means to me. It would have been hard for me to find the above information even though I came close. I did lookup similiar information on MSDN that provided an example similiar to the above post but did not have the wisdom to apply it correctly in the venue that I was trying to do.

Everyone's time is precious and as a professional I'm sure that you are very busy as well. I just want to sincerely thank you for the time you've taken out to answer my reply. I see your name on a lot of replies when I do searches and I urge you to keep doing so because your knowledge (and wisdom) is very much appreciated!

stephenlecompte
08-06-2005, 04:21 PM
I finally figured out what I need to do with the following FIRST series of code. What I was trying to do next is late bind the stream for other users. This is so my entire program would not shut down because of a reference error missing on their machines.

I mark the following with 2 cases of example. The one part that works when at least 2.5 Microsoft ADO is declared as a reference and the second part where I'm trying to use late binding. The code runs smoothly just except for the fact that no objects are being stored in my SQL Server.

Please any comments are much appreciated! Basically real briefly - the background is that we have had several .pdfs that we load into SQL as objects for our clients to pull up individually in a web environment. (And yes I do know that it would be a lot less trouble by just putting in the path - its just something we're stuck with as a company until I learn C#)

Code that works with early binding:


Dim st As ADODB.Stream
Dim sText As String

Dim iLen As Integer
Dim pFSe As Object
Dim sCutMark As String
SysCmd acSysCmdSetStatus, "Importing PROJ_ME BLOB (actual image) data..."
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
sSelect = "SELECT [Cutsheet], [Cutsheet_URL] FROM [TMP_PROJ_ME] WHERE Left([Cutsheet_URL], 1) = 'I'"
rs.Open sSelect, cnSQLData, 2, 4
'rs.Open sSelect, cnSQLData, adOpenDynamic, adLockBatchOptimistic
If Not rs.EOF Then
rs.MoveLast
iRec = rs.RecordCount
End If
rs.Close

g_lFile = FreeFile
cnSQLData.CursorLocation = 3
'rs.Open sSelect, cnSQLData, 2, 4, 1
rs.Open sSelect, cnSQLData, adOpenDynamic, adLockOptimistic, adCmdText
Do Until rs.EOF

iCounter = iCounter + 1
iLen = Len(rs.Fields("Cutsheet_URL"))
If iLen > 150 Then iLen = 150
Set st = New ADODB.Stream
'st.Type = 1
st.Type = adTypeBinary
st.Open
sText = rs.Fields("Cutsheet_URL")
st.LoadFromFile sText
With rs
.Fields("Cutsheet").Value = st.Read
.Update
.MoveNext
End With
Set st = Nothing
Loop
rs.Close
Set rs = Nothing

iCounter = 0


Code that refuses to work with late binding:


Dim st As Object 'ADODB.Stream
Dim sText As String

Dim iLen As Integer
Dim pFSe As Object
Dim sCutMark As String
SysCmd acSysCmdSetStatus, "Importing PROJ_ME BLOB (actual image) data..."

Dim rs As Object
'Dim rs As ADODB.Recordset
'Set rs = New ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
sSelect = "SELECT [Cutsheet], [Cutsheet_URL] FROM [TMP_PROJ_ME] WHERE Left([Cutsheet_URL], 1) = 'I'"
rs.Open sSelect, cnSQLData, 2, 4
'rs.Open sSelect, cnSQLData, adOpenDynamic, adLockBatchOptimistic
If Not rs.EOF Then
rs.MoveLast
iRec = rs.RecordCount
End If
rs.Close

g_lFile = FreeFile
cnSQLData.CursorLocation = 3
rs.Open sSelect, cnSQLData, 2, 4, 1
'rs.Open sSelect, cnSQLData, adOpenDynamic, adLockOptimistic, adCmdText
Do Until rs.EOF

iCounter = iCounter + 1
iLen = Len(rs.Fields("Cutsheet_URL"))
If iLen > 150 Then iLen = 150

Set st = CreateObject("ADODB.Stream")
st.Type = 1
'st.Type = adTypeBinary
st.Open
sText = rs.Fields("Cutsheet_URL")
st.LoadFromFile sText
With rs
.Fields("Cutsheet").Value = st.Read
.Update
.MoveNext
End With
Set st = Nothing
Loop
rs.Close
Set rs = Nothing

iCounter = 0

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum