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
sg1nx01Asked:
Who is Participating?
 
NetworkArchitekConnect With a Mentor Commented:
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
 
b1xml2Connect With a Mentor Commented:
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
 
SanclerConnect With a Mentor Commented:
... 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.