insert stored procedure

[nicholas]

Could someone tell me how to implement an INSERT on an aspx-page using a
stored procedure, in VB-code?

THX,
Nic

PS: I have been trying this, but it ain't working:

Sub insert_new_cont ent (sender As Object, e As EventArgs)

Dim MyConnectionStr ing as String =
ConfigurationSe ttings.AppSetti ngs("Connection String")
Dim myConnection As SQLConnection
myConnection = New SQLConnection(M yConnectionStri ng)

Dim myCommand As New SqlCommand("spa ddshopscats", myConnection)
myCommand.Comma ndType = CommandType.Sto redProcedure
Dim Parameter As SqlParameter

Parameter = New SqlParameter("@ shopID", SqlDbType.Int, 4)
myCommand.Param eters.Add(Param eter)
Parameter.Direc tion = ParameterDirect ion.Input
Parameter.Value = ctype(request.q uerystring("sho pID"),integer)

Parameter = New SqlParameter("@ categoryID", SqlDbType.VarCh ar, 2000)
myCommand.Param eters.Add(Param eter)
Parameter.Direc tion = ParameterDirect ion.Input
Parameter.Value = categoryID.text
myConnection.Op en()
myCommand.Execu teNonQuery()

myConnection.Cl ose()

End Sub

[Nick Stansbury]

What error are you getting?
"nicholas" <mu********@hot mail.com> wrote in message
news:OC******** ******@TK2MSFTN GP12.phx.gbl...

Could someone tell me how to implement an INSERT on an aspx-page using a
stored procedure, in VB-code?

THX,
Nic

PS: I have been trying this, but it ain't working:

Sub insert_new_cont ent (sender As Object, e As EventArgs)

Dim MyConnectionStr ing as String =
ConfigurationSe ttings.AppSetti ngs("Connection String")
Dim myConnection As SQLConnection
myConnection = New SQLConnection(M yConnectionStri ng)

Dim myCommand As New SqlCommand("spa ddshopscats", myConnection)
myCommand.Comma ndType = CommandType.Sto redProcedure
Dim Parameter As SqlParameter

Parameter = New SqlParameter("@ shopID", SqlDbType.Int, 4)
myCommand.Param eters.Add(Param eter)
Parameter.Direc tion = ParameterDirect ion.Input
Parameter.Value = ctype(request.q uerystring("sho pID"),integer)

Parameter = New SqlParameter("@ categoryID", SqlDbType.VarCh ar, 2000)
myCommand.Param eters.Add(Param eter)
Parameter.Direc tion = ParameterDirect ion.Input
Parameter.Value = categoryID.text
myConnection.Op en()
myCommand.Execu teNonQuery()

myConnection.Cl ose()

End Sub

[nicholas]

The problem was in my stored procedure: in my second line I had "@categoryI D
varchar (200)" => I removed "(200)" and it works now. But it is still not
working perfectly:

This is the stored procedure:
---------
CREATE PROCEDURE spaddshopscats

@shopID int, @categoryID varchar

AS

DECLARE @CatInsert varchar(2000)

SET @CatInsert = 'INSERT INTO tbl_shopscats (shopID, categoryID) SELECT ' +
CONVERT(varchar ,@shopID) + ', categoryID From tbl_categories Where
categoryID IN (' + @categoryID + ')'

exec (@CatInsert)

Return
GO
-----------

So, in @categoryID there is a string of category ID's separated by comma's
(ex: 1,25,78)
The stored procedure should insert for each of these values a new record
together with the id of the shop.

So if @shopID = 63

We should have these records inserted:
record1: shopID= 63 and categoryID=1
record2: shopID= 63 and categoryID=25
record3: shopID= 63 and categoryID=78
Now, it works for the categoryID = 1 but not for the others.

THX for your help,
Nic

"Nick Stansbury" <ni************ @sage-removepartners. com> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

What error are you getting?
"nicholas" <mu********@hot mail.com> wrote in message
news:OC******** ******@TK2MSFTN GP12.phx.gbl...

Could someone tell me how to implement an INSERT on an aspx-page using a
stored procedure, in VB-code?

THX,
Nic

PS: I have been trying this, but it ain't working:

Sub insert_new_cont ent (sender As Object, e As EventArgs)

Dim MyConnectionStr ing as String =
ConfigurationSe ttings.AppSetti ngs("Connection String")
Dim myConnection As SQLConnection
myConnection = New SQLConnection(M yConnectionStri ng)

Dim myCommand As New SqlCommand("spa ddshopscats", myConnection)
myCommand.Comma ndType = CommandType.Sto redProcedure
Dim Parameter As SqlParameter

Parameter = New SqlParameter("@ shopID", SqlDbType.Int, 4)
myCommand.Param eters.Add(Param eter)
Parameter.Direc tion = ParameterDirect ion.Input
Parameter.Value = ctype(request.q uerystring("sho pID"),integer)

Parameter = New SqlParameter("@ categoryID", SqlDbType.VarCh ar, 2000)
myCommand.Param eters.Add(Param eter)
Parameter.Direc tion = ParameterDirect ion.Input
Parameter.Value = categoryID.text
myConnection.Op en()
myCommand.Execu teNonQuery()

myConnection.Cl ose()

End Sub

[Nick Stansbury]

I can't see straight away why your code isn't working.

However your approach isn't ideal. From a performance perspective you're not
gaining much by doing this all in one go - from what I've read its opening
and closing the database connection that has the overhead. I'd adjust like
this:

Create Procedure spAddShopsCats
@ShopId int,
@CategoryId int
as

insert into tbl_shopsCats (ShopID, categoryID) VALUES (@ShopID, @CategoryID)

Then just exec the procedure once per category rather than try and get the
stored procedure to parse the string. If you really do want to do it all in
one go then post DDL (create table script etc.) and I'll have a longer look.

CREATE PROCEDURE spaddshopscats

@shopID int, @categoryID varchar

AS

DECLARE @CatInsert varchar(2000)

SET @CatInsert = 'INSERT INTO tbl_shopscats (shopID, categoryID) SELECT ' + CONVERT(varchar ,@shopID) + ', categoryID From tbl_categories Where
categoryID IN (' + @categoryID + ')'

exec (@CatInsert)

Return
GO
-----------

So, in @categoryID there is a string of category ID's separated by comma's
(ex: 1,25,78)
The stored procedure should insert for each of these values a new record
together with the id of the shop.

So if @shopID = 63

We should have these records inserted:
record1: shopID= 63 and categoryID=1
record2: shopID= 63 and categoryID=25
record3: shopID= 63 and categoryID=78
Now, it works for the categoryID = 1 but not for the others.

[nicholas]

Thanks for your help, but I changed my code completely.
Is better and safer and most important: it works !

Thanks a lot,
Nic

"Nick Stansbury" <ni************ @sage-removepartners. com> wrote in message
news:em******** ******@tk2msftn gp13.phx.gbl...

I can't see straight away why your code isn't working.

However your approach isn't ideal. From a performance perspective you're not gaining much by doing this all in one go - from what I've read its opening
and closing the database connection that has the overhead. I'd adjust like
this:

Create Procedure spAddShopsCats
@ShopId int,
@CategoryId int
as

insert into tbl_shopsCats (ShopID, categoryID) VALUES (@ShopID, @CategoryID)
Then just exec the procedure once per category rather than try and get the
stored procedure to parse the string. If you really do want to do it all in one go then post DDL (create table script etc.) and I'll have a longer look.

CREATE PROCEDURE spaddshopscats

@shopID int, @categoryID varchar

AS

DECLARE @CatInsert varchar(2000)

SET @CatInsert = 'INSERT INTO tbl_shopscats (shopID, categoryID) SELECT '

+

CONVERT(varchar ,@shopID) + ', categoryID From tbl_categories Where
categoryID IN (' + @categoryID + ')'

exec (@CatInsert)

Return
GO
-----------

So, in @categoryID there is a string of category ID's separated by

comma's (ex: 1,25,78)
The stored procedure should insert for each of these values a new record
together with the id of the shop.

So if @shopID = 63

We should have these records inserted:
record1: shopID= 63 and categoryID=1
record2: shopID= 63 and categoryID=25
record3: shopID= 63 and categoryID=78
Now, it works for the categoryID = 1 but not for the others.