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_content (sender As Object, e As EventArgs)

Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionString")
Dim myConnection As SQLConnection
myConnection = New SQLConnection(MyConnectionString)

Dim myCommand As New SqlCommand("spaddshopscats", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim Parameter As SqlParameter

Parameter = New SqlParameter("@shopID", SqlDbType.Int, 4)
myCommand.Parameters.Add(Parameter)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = ctype(request.querystring("shopID"),integer)

Parameter = New SqlParameter("@categoryID", SqlDbType.VarChar, 2000)
myCommand.Parameters.Add(Parameter)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = categoryID.text


myConnection.Open()
myCommand.ExecuteNonQuery()

myConnection.Close()

End Sub

 

[Nick Stansbury]

What error are you getting?

 

[nicholas]

The problem was in my stored procedure: in my second line I had "@categoryID
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]

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.

 

[nicholas]

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

Thanks a lot,
Nic