Question with paramater passed to sp

[Guest]

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").Value =


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("','")

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").Value = 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