CooperTupelo
asked on
I error out when I ask my datatable to select rows that are equal to a search argument.
Here is some code
If x = 1 Then
CurrRow = dt.Select("IDNUM = " & Search, "IDNUM")
Else
If x = 2 Then
CurrRow = dt.Select("CHECKNUM = " & Search, "CHECKNUM")
Else
If x = 3 Then
CurrRow = dt.Select("DATE" Is Search, "DATE")
End If
End If
End If
The first line is comparing whats in 'search' to the column idnum. No problems and works perfectly. The second line that is done exactly the same gives me this error:
Cannot perform '=' operation on System.String and System.Int32
I have tried all kinds of combinations for this and cant figure it out. The third line happens the same way. It just shows yet another way that I tried. The only thing that I can think of is that in the table these columns come from.........IDNUM is defined numeric......Checknum and DATE are defined as text. Does this have something to do with it? If it does, how do I do a select statement and compare it to a text defined field? I cannot change the table.
If x = 1 Then
CurrRow = dt.Select("IDNUM = " & Search, "IDNUM")
Else
If x = 2 Then
CurrRow = dt.Select("CHECKNUM = " & Search, "CHECKNUM")
Else
If x = 3 Then
CurrRow = dt.Select("DATE" Is Search, "DATE")
End If
End If
End If
The first line is comparing whats in 'search' to the column idnum. No problems and works perfectly. The second line that is done exactly the same gives me this error:
Cannot perform '=' operation on System.String and System.Int32
I have tried all kinds of combinations for this and cant figure it out. The third line happens the same way. It just shows yet another way that I tried. The only thing that I can think of is that in the table these columns come from.........IDNUM is defined numeric......Checknum and DATE are defined as text. Does this have something to do with it? If it does, how do I do a select statement and compare it to a text defined field? I cannot change the table.
you need to cast the int to a string on the 2nd and 3rd Select's so that the data types being compared are the same.
If x = 1 Then
CurrRow = dt.Select("IDNUM = " & Search, "IDNUM")
Else
If x = 2 Then
CurrRow = dt.Select("CHECKNUM = " & Search.ToString(), "CHECKNUM")
Else
If x = 3 Then
CurrRow = dt.Select("DATE" Is Search.ToString(), "DATE")
End If
End If
End If
CurrRow = dt.Select("IDNUM = " & Search, "IDNUM")
Else
If x = 2 Then
CurrRow = dt.Select("CHECKNUM = " & Search.ToString(), "CHECKNUM")
Else
If x = 3 Then
CurrRow = dt.Select("DATE" Is Search.ToString(), "DATE")
End If
End If
End If
What is the DataType of "CHECKNUM" in the table ?
"Checknum and DATE are defined as text"
I am only assuming Search is a number type, probably int, since IDNUM is numeric and that line works
ASKER
Nope......Search is defined as string
I get the same error that way.
I get the same error that way.
what is the value of search?
ASKER
40-022229
it pulls a value from the previous window. The value is typed in from the user
it happens to be a check number
I have been testing with a known good check number 40-022229
it pulls a value from the previous window. The value is typed in from the user
it happens to be a check number
I have been testing with a known good check number 40-022229
ASKER
Does anyone know?? Please HELP!!
I think the problem is related to checknum and date being defined as text. if you could convert them to varchar that might help. if not, try doing appropriate casting. you could try variations of the code to track down exactly what is causing it. test your variables. make sure everything's right.
ASKER
I know that those two variables being defined as text if the problem. But I cannot change that and this is coming from an Access table. How can I do the above code with those two variables defined as text?
i just realized you can't use an = opperator on a TEXT field in SQL. try using LIKE instead
If x = 1 Then
CurrRow = dt.Select("IDNUM = " & Search, "IDNUM")
Else
If x = 2 Then
CurrRow = dt.Select("CHECKNUM LIKE " & Search, "CHECKNUM")
Else
If x = 3 Then
CurrRow = dt.Select("DATE LIKE " & Search, "DATE")
End If
End If
End If
If x = 1 Then
CurrRow = dt.Select("IDNUM = " & Search, "IDNUM")
Else
If x = 2 Then
CurrRow = dt.Select("CHECKNUM LIKE " & Search, "CHECKNUM")
Else
If x = 3 Then
CurrRow = dt.Select("DATE LIKE " & Search, "DATE")
End If
End If
End If
ASKER
That dont work either. It gives me this error
Cannot perform 'Like' operation on System.String and System.Int32
Cannot perform 'Like' operation on System.String and System.Int32
ok so even though CHECKNUM and DATE are text, .net is storing them as strings. i don't get the int though. if search is not an int, where is the int??
also, why were you using Is Search in this line: CurrRow = dt.Select("DATE" Is Search, "DATE") ?
also, why were you using Is Search in this line: CurrRow = dt.Select("DATE" Is Search, "DATE") ?
ASKER
I was using is just to try something different. I just posted it that way to show that I tried that also.
IDNUM is defined as a number in the Access table. CHECKNUM and DATE are both defined as text. I dont understand it either. You'd think with Search dimmed as a string that it would be harder to get IDNUM to compare to it then the two text fields.
IDNUM is defined as a number in the Access table. CHECKNUM and DATE are both defined as text. I dont understand it either. You'd think with Search dimmed as a string that it would be harder to get IDNUM to compare to it then the two text fields.
you could try to get any type of Select to work with CHECKNUM
dt.Select("CHECKNUM = 40-022229")
see if you can get anything to work and we'll go from there
dt.Select("CHECKNUM = 40-022229")
see if you can get anything to work and we'll go from there
ASKER
This works
CurrRow = dt.Select("CHECKNUM = '40-022229'", "CHECKNUM")
but this does not
CurrRow = dt.Select("CHECKNUM = " & "40-022229", "CHECKNUM")
CurrRow = dt.Select("CHECKNUM = '40-022229'", "CHECKNUM")
but this does not
CurrRow = dt.Select("CHECKNUM = " & "40-022229", "CHECKNUM")
crazy. how about
Dim strSelect As String = "CHECKNUM = " & "40-022229"
CurrRow = dt.Select(strSelect, "CHECKNUM")
Dim strSelect As String = "CHECKNUM = " & "40-022229"
CurrRow = dt.Select(strSelect, "CHECKNUM")
ASKER
That gives the same error.
and this doesnt?
Dim strSelect As String = "CHECKNUM = 40-022229"
CurrRow = dt.Select(strSelect, "CHECKNUM")
Dim strSelect As String = "CHECKNUM = 40-022229"
CurrRow = dt.Select(strSelect, "CHECKNUM")
ASKER
Nope that works.
ASKER
This does work.....I just tried it.
Dim strSelect As String = "CHECKNUM = " & "'40-022229'"
CurrRow = dt.Select(strSelect, "CHECKNUM")
You have to have the apostrophes around the 40-022229
Dim strSelect As String = "CHECKNUM = " & "'40-022229'"
CurrRow = dt.Select(strSelect, "CHECKNUM")
You have to have the apostrophes around the 40-022229
something strange is going on.. i dont know what to tell you. you could try different methods of building your string. use a StringBuilder.. use seperate variables and try concatenating?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YEAH!!! It works!!!!
You tha Man!!
You tha Man!!
glad i could help, sorry it took so long to figure out that tiny little error. those are the worst