Question with paramater passed to sp

[Chris]

Hi
I have a problem populating my datagrid when the values passed have an apostrophie (') like this test's. Any other values work. This is my asp.net cod

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Loa
'Put user code to initialize the page her
Dim s As String = Request.QueryString("id").ToString(
If Not Page.IsPostBack The

Dim conPos As SqlConnectio
Dim cmdSel As SqlDataAdapte
Dim ds As DataSe

conPos = New SqlConnection("Server = prod01\enterprise;UID=sa;PWD=xxxx;Database=POS"
cmdSel = New SqlDataAdapter("LoadOrderRegulation", conPos

cmdSel.SelectCommand.CommandType = CommandType.StoredProcedur

cmdSel.SelectCommand.Parameters.Add(New SqlParameter("@productName", SqlDbType.VarChar, 15)
cmdSel.SelectCommand.Parameters("@productName").Va lue =
ds = New DataSe
cmdSel.Fill(ds, "Oreg"

DataGrid1.DataSource = ds.Tables("Oreg").DefaultVie
DataGrid1.DataBind(
End I

End Su
This is my stored pro

create procedure LoadOrderRegulation
@productName varchar(50
a

declare @productNumber varchar(10

exec POS_GetProductNumber @productName, @productNumber OUTPU

If len(@productNumber) = 1

Set @productNumber = '0000' + @productNumbe

If len(@productNumber) =

Set @productNumber = '000' + @productNumbe

If len(@productNumber) =

Set @productNumber = '00' + @productNumbe

If len(@productNumber) =

Set @productNumber = '0' + @productNumbe
select
POS_Product_Number
POS_Store_Number
POS_Account_Number
POS_Current_Draw
POS_Proposed_Draw
POS_Average_Sale
POS_Date_Regulated
POS_Adjustmen

from pos_order_regulation where POS_Product_Number = @productNumbe
retur

I think my stored proc is expecting test''s instead of test's. How can I handle this. If I execute the stored procedure from query analyzer like this

declare @datatest varchar(20

exec LoadOrderRegulation 'test''s', @datatest OUTPU

Select @datatest

it work

[Rick Spiewak]

Here's how I solve a similar problem, building a SQL query with
Stringbuilder (yes, soon to be a stored procedure...):

sbldr.Append(Me.shipTo_First_Name.Replace("'", "''")) : sbldr.Append("','")

"Chris" <an*******@discussions.microsoft.com> wrote in message
news:8A**********************************@microsof t.com...

Hi,
I have a problem populating my datagrid when the values passed have an apostrophie (') like this test's. Any other values work. This is my asp.net
code
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here

Dim s As String = Request.QueryString("id").ToString()
If Not Page.IsPostBack Then
Dim conPos As SqlConnection
Dim cmdSel As SqlDataAdapter
Dim ds As DataSet

conPos = New SqlConnection("Server = prod01\enterprise;UID=sa;PWD=xxxx;Database=POS")
cmdSel = New SqlDataAdapter("LoadOrderRegulation", conPos)

cmdSel.SelectCommand.CommandType = CommandType.StoredProcedure

cmdSel.SelectCommand.Parameters.Add(New SqlParameter("@productName", SqlDbType.VarChar, 15)) cmdSel.SelectCommand.Parameters("@productName").Va lue = s

ds = New DataSet
cmdSel.Fill(ds, "Oreg")

DataGrid1.DataSource = ds.Tables("Oreg").DefaultView
DataGrid1.DataBind()


End If

End Sub

This is my stored proc


create procedure LoadOrderRegulation(
@productName varchar(50)
)

as

declare @productNumber varchar(10)
exec POS_GetProductNumber @productName, @productNumber OUTPUT

If len(@productNumber) = 1

Set @productNumber = '0000' + @productNumber

If len(@productNumber) = 2
Set @productNumber = '000' + @productNumber

If len(@productNumber) = 3
Set @productNumber = '00' + @productNumber

If len(@productNumber) = 4
Set @productNumber = '0' + @productNumber


select
POS_Product_Number,
POS_Store_Number,
POS_Account_Number,
POS_Current_Draw,
POS_Proposed_Draw,
POS_Average_Sale,
POS_Date_Regulated,
POS_Adjustment

from pos_order_regulation where POS_Product_Number = @productNumber

return
I think my stored proc is expecting test''s instead of test's. How can I handle this. If I execute the stored procedure from query analyzer like this

declare @datatest varchar(20)

exec LoadOrderRegulation 'test''s', @datatest OUTPUT

Select @datatest

it works

[Rick Spiewak]

Here's how I solve a similar problem, building a SQL query with
Stringbuilder (yes, soon to be a stored procedure...):

sbldr.Append(Me.shipTo_First_Name.Replace("'", "''")) : sbldr.Append("','")

"Chris" <an*******@discussions.microsoft.com> wrote in message
news:8A**********************************@microsof t.com...

Hi,
I have a problem populating my datagrid when the values passed have an apostrophie (') like this test's. Any other values work. This is my asp.net
code
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here

Dim s As String = Request.QueryString("id").ToString()
If Not Page.IsPostBack Then
Dim conPos As SqlConnection
Dim cmdSel As SqlDataAdapter
Dim ds As DataSet

conPos = New SqlConnection("Server = prod01\enterprise;UID=sa;PWD=xxxx;Database=POS")
cmdSel = New SqlDataAdapter("LoadOrderRegulation", conPos)

cmdSel.SelectCommand.CommandType = CommandType.StoredProcedure

cmdSel.SelectCommand.Parameters.Add(New SqlParameter("@productName", SqlDbType.VarChar, 15)) cmdSel.SelectCommand.Parameters("@productName").Va lue = s

ds = New DataSet
cmdSel.Fill(ds, "Oreg")

DataGrid1.DataSource = ds.Tables("Oreg").DefaultView
DataGrid1.DataBind()


End If

End Sub

This is my stored proc


create procedure LoadOrderRegulation(
@productName varchar(50)
)

as

declare @productNumber varchar(10)
exec POS_GetProductNumber @productName, @productNumber OUTPUT

If len(@productNumber) = 1

Set @productNumber = '0000' + @productNumber

If len(@productNumber) = 2
Set @productNumber = '000' + @productNumber

If len(@productNumber) = 3
Set @productNumber = '00' + @productNumber

If len(@productNumber) = 4
Set @productNumber = '0' + @productNumber


select
POS_Product_Number,
POS_Store_Number,
POS_Account_Number,
POS_Current_Draw,
POS_Proposed_Draw,
POS_Average_Sale,
POS_Date_Regulated,
POS_Adjustment

from pos_order_regulation where POS_Product_Number = @productNumber

return
I think my stored proc is expecting test''s instead of test's. How can I handle this. If I execute the stored procedure from query analyzer like this

declare @datatest varchar(20)

exec LoadOrderRegulation 'test''s', @datatest OUTPUT

Select @datatest

it works