|
 |
aspx thread: Stored Procedure, Single Quotes and VB.NET
Message #1 by "Mitchell, David" <David.Mitchell@p...> on Fri, 7 Feb 2003 16:11:38 -0500
|
|
Hi Everyone,
I am accessing output parameters in a stored procedure like this:
Dim cmd As New SqlCommand("sp_Get_Defect_Name",
_sqlConnection)
With cmd
.Parameters.Add("@defectID", 1)
.Parameters.Add("@inspectionType", "SCHOOL BUS AND BUS
INSPECTION CERTIFICATE")
.Parameters.Add("@defectType", SqlDbType.VarChar, 70)
.Parameters("@defectType").Direction
ParameterDirection.Output
.Parameters.Add("@defectCategory", SqlDbType.VarChar, 50)
.Parameters("@defectCategory").Direction
ParameterDirection.Output
.CommandType = CommandType.StoredProcedure
.ExecuteNonQuery()
End With
Dim defectType As String
Dim defectCategory As String
defectType = cmd.Parameters("@defectType").Value
defectCategory = cmd.Parameters("@defectCategory").Value
This works fine except for the fact that the second output param may have
single qoutes in it (such as "Driver's Controls"). When I debug this the
defectCategory is getting screwed up by the single quote and just showing up
as "Driver".
Any ideas on how I can escape the single quote in the above code?
Thanks,
Dave
Message #2 by "Nebojsa Gogic" <ngogic@r...> on Fri, 7 Feb 2003 23:16:58 -0500
|
|
Try to put SET QUOTED_IDENTIFIER OFF at the beginning of your stored
procedure.
Nebojsa Gogic
----- Original Message -----
From: "Mitchell, David" <David.Mitchell@p...>
To: "ASP.NET" <aspx@p...>
Sent: Friday, February 07, 2003 4:11 PM
Subject: [aspx] Stored Procedure, Single Quotes and VB.NET
> Hi Everyone,
>
> I am accessing output parameters in a stored procedure like this:
>
> Dim cmd As New SqlCommand("sp_Get_Defect_Name",
> _sqlConnection)
> With cmd
> .Parameters.Add("@defectID", 1)
> .Parameters.Add("@inspectionType", "SCHOOL BUS AND BUS
> INSPECTION CERTIFICATE")
> .Parameters.Add("@defectType", SqlDbType.VarChar, 70)
> .Parameters("@defectType").Direction
> ParameterDirection.Output
> .Parameters.Add("@defectCategory", SqlDbType.VarChar, 50)
> .Parameters("@defectCategory").Direction
> ParameterDirection.Output
> .CommandType = CommandType.StoredProcedure
> .ExecuteNonQuery()
> End With
> Dim defectType As String
> Dim defectCategory As String
> defectType = cmd.Parameters("@defectType").Value
> defectCategory = cmd.Parameters("@defectCategory").Value
>
> This works fine except for the fact that the second output param may have
> single qoutes in it (such as "Driver's Controls"). When I debug this the
> defectCategory is getting screwed up by the single quote and just showing
up
> as "Driver".
> Any ideas on how I can escape the single quote in the above code?
>
> Thanks,
>
> Dave
>
Message #3 by "Ken Schaefer" <ken@a...> on Mon, 10 Feb 2003 11:23:40 +1100
|
|
Just showing up as "Driver" where? If you are placing it into a HTML form
element, like so:
<input type='text' value='Driver's Controls'>
then this is a completely separate issue to SQL/database etc. What happens
if you use Response.Write() to output the value?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Mitchell, David" <David.Mitchell@p...>
Subject: [aspx] Stored Procedure, Single Quotes and VB.NET
: Hi Everyone,
:
: I am accessing output parameters in a stored procedure like this:
:
: Dim cmd As New SqlCommand("sp_Get_Defect_Name",
: _sqlConnection)
: With cmd
: .Parameters.Add("@defectID", 1)
: .Parameters.Add("@inspectionType", "SCHOOL BUS AND BUS
: INSPECTION CERTIFICATE")
: .Parameters.Add("@defectType", SqlDbType.VarChar, 70)
: .Parameters("@defectType").Direction
: ParameterDirection.Output
: .Parameters.Add("@defectCategory", SqlDbType.VarChar, 50)
: .Parameters("@defectCategory").Direction
: ParameterDirection.Output
: .CommandType = CommandType.StoredProcedure
: .ExecuteNonQuery()
: End With
: Dim defectType As String
: Dim defectCategory As String
: defectType = cmd.Parameters("@defectType").Value
: defectCategory = cmd.Parameters("@defectCategory").Value
:
: This works fine except for the fact that the second output param may have
: single qoutes in it (such as "Driver's Controls"). When I debug this the
: defectCategory is getting screwed up by the single quote and just showing
up
: as "Driver".
: Any ideas on how I can escape the single quote in the above code?
Message #4 by "Mitchell, David" <David.Mitchell@p...> on Mon, 10 Feb 2003 08:06:07 -0500
|
|
Hi Ken,
It shows up as "Driver" when I debug the class and check the value for the
driver variable at runtime.
Someone else on the list kindly suggested putting SET QUOTED_IDENTIFIER OFF
for my stored procedure. I'll give that a try.
Thanks!
Dave
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Sunday, February 09, 2003 7:24 PM
To: ASP.NET
Subject: [aspx] Re: Stored Procedure, Single Quotes and VB.NET
Just showing up as "Driver" where? If you are placing it into a HTML form
element, like so:
<input type='text' value='Driver's Controls'>
then this is a completely separate issue to SQL/database etc. What happens
if you use Response.Write() to output the value?
Cheers
Ken
Message #5 by Carlos Magalhaes <CarlosM@t...> on Mon, 10 Feb 2003 15:05:19 +0200
|
|
If you use a SQLParameter structured query instead of a SP it is
automatically set to SET QUOTED_IDENTIFIER OFF.
HTH
-----Original Message-----
From: Mitchell, David [mailto:David.Mitchell@p...]
Sent: 10 February 2003 03:06 PM
To: ASP.NET
Subject: [aspx] Re: Stored Procedure, Single Quotes and VB.NET
Hi Ken,
It shows up as "Driver" when I debug the class and check the value for the
driver variable at runtime.
Someone else on the list kindly suggested putting SET QUOTED_IDENTIFIER OFF
for my stored procedure. I'll give that a try.
Thanks!
Dave
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Sunday, February 09, 2003 7:24 PM
To: ASP.NET
Subject: [aspx] Re: Stored Procedure, Single Quotes and VB.NET
Just showing up as "Driver" where? If you are placing it into a HTML form
element, like so:
<input type='text' value='Driver's Controls'>
then this is a completely separate issue to SQL/database etc. What happens
if you use Response.Write() to output the value?
Cheers
Ken
|
|
|
View Cart
