• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

Convert to integer within a where clause

The field in my Access table is a number field, therefore, when I try to run this statement, it gives me a "datatype mismatch" error.  Can somebody please tell me how to rewrite this statement with an integer instead of string?

With cmd
     For Each dr As DataRow in myDataSet.Tables(0).Rows
          .Connection = New oleDb.OleDbConnection(strConnMDB)
          .Connection.Open()
          .CommandText = "SELECT COUNT(*) AS MyCount FROM MyTable" & _
                                    " WHERE MyNumber = '" & dr.Item("MyNumber") & "'"
          dr.Item("ISSUES") = .ExecuteScalar
          .Connection.Close()
     Next
End With
0
sg1nx01
Asked:
sg1nx01
3 Solutions
 
b1xml2Commented:
With cmd
     For Each dr As DataRow in myDataSet.Tables(0).Rows
          .Connection = New oleDb.OleDbConnection(strConnMDB)
          .Connection.Open()
          .CommandText = "SELECT COUNT(*) AS MyCount FROM MyTable" & _
                                    " WHERE MyNumber = " & CStr(dr.Item("MyNumber"))
          dr.Item("ISSUES") = .ExecuteScalar
          .Connection.Close()
     Next
End With

just don't use quotes...
0
 
SanclerCommented:
... except that CStr(dr.Item("MyNumber")) will still be a string and the questioner wants an Integer.

Should it not be

" WHERE MyNumber = " & CInt(dr.Item("MyNumber"))

Roger
0
 
NetworkArchitekCommented:
You don't have to convert, whatever is in CommandText is a string anyway. You don't have ot convert at all. The only thing is to NOT use the single quotes on numbers.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SanclerCommented:
So

" WHERE MyNumber = " & dr.Item("MyNumber")

is enough?

Roger
0
 
amyhxuCommented:
answer for NetworkArchitek -- to Sancler: Yes.
0
 
b1xml2Commented:
Sancler, you are wrong:
 " WHERE MyNumber = '" & dr.Item("MyNumber") & "'" as in WHERE MyNumber = '4'

It will work in SQL Server, but there's no guarantee it'll work elsewhere esp. Access.

So, we drop the '"
also, Just for completeness,

  " WHERE MyNumber = " & CStr(dr.Item("MyNumber"))
0
 
sg1nx01Author Commented:
Actuallly, all three answers worked.  I accepted NetworkArchitek's because it was the most economical.  I gave the most points to Sancler because I, in my ignorance, did ask how to write the statement as a number.  You guys are great.  Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now