By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,564 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,564 IT Pros & Developers. It's quick & easy.

insert stored procedure

P: n/a
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("ConnectionStrin g")
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
Nov 18 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
What error are you getting?
"nicholas" <mu********@hotmail.com> wrote in message
news:OC**************@TK2MSFTNGP12.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_content (sender As Object, e As EventArgs)

Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionStrin g")
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

Nov 18 '05 #2

P: n/a
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" <ni************@sage-removepartners.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
What error are you getting?
"nicholas" <mu********@hotmail.com> wrote in message
news:OC**************@TK2MSFTNGP12.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_content (sender As Object, e As EventArgs)

Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionStrin g")
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


Nov 18 '05 #3

P: n/a
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.

Nov 18 '05 #4

P: n/a
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**************@tk2msftngp13.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.


Nov 18 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.