Cast Problem

If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01") from the submitted form.

SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,
OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTime
FROM ClinicalSpecimen
WHERE (SpecimenID = CAST('7575-01' AS VARCHAR(50)))
ORDER BY SpecimenID DESC

However, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found... Can someone please explain what I am missing here...

MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

*"AccessionPresent" is the value of the text field retrieved from the form.

I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.

Thank you for any or all assistance !!!

Looks like you have a blank value that you are trying to convert to int. Check for NULL/Blanks in your application before you CAST as varchar.

|||

Instead of:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

Try:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST('" & AccessionPresent &"' AS VARCHAR(50))"

Remove the Paren's, also as shark said, do something like if string.isnullorempty(AccessionPresent) then put a dummy value their or whatever

|||

Try this (use this query in your MySQL):

1SELECT *2FROM ClinicalSpecimen3WHERE SpecimenID =CAST(4ISNULL(5 ('" & AccessionPresent & "')6 , -1-- you will get -1 for Null cases7 )8AS VARCHAR(50)9 )1011-- I splited the query to make it more readable (you can put it in one line)

Hope this will help.

Good luck.

|||

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't.

Try:

Dim conn as new SqlConnection(...)

Dim cmd as new SqlCommand("SELECT * FROM ClinicalSpecimen WHERE CAST(SpecimenID AS varchar(50))=@.SpecimenID",conn)

cmd.Parameters.Add("@.SpecimenID",SqlDbType.varchar).Value=AccessionPresent

Note, this also removes the SQL Injection problem you had.

|||

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

|||

ndinakar:

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

Yes, good catch Motley

How we did not realise that!!