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!!

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.
You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David