N
nicholas
I have a page that changes some data in an sql-server database: it inserts
the categories for a selected product.
The user checks some categories in a tree (with checkboxes).
These are the categories where the selected product belongs to.
These checked categories are listed in a textbox named "theIDs" (ex:
25,116,420)
In the texbox named "chk_counter" there is the number of selected
categories: in this example: 3
So, in the table, I first delete all records containing the productID and
then I insert all the checked categories.
So, if the ID of the product is 14 these records will be inserted:
productID categoryID
14 25
14 116
14 420
Now, all this works, but it is very very very long.
On my broadband connection, it takes up to 15 seconds.
On a normal connection (ex: GPRS) it can take more than a minute...
I don't understand why it takes so long??
I put my code and the stored procedures below.
If you could have a look and tell me what could be the reason, I would
really appreciate it !
Thx a lot,
Nic
Here is my code when the user clicks on the insert button:
=======================================
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)
myConnection.Open()
'delete all the productscats
Dim myCommand0 As New SqlCommand("spdeleteproductscats", myConnection)
myCommand0.CommandType = CommandType.StoredProcedure
Dim Parameter0 As SqlParameter
Parameter0 = New SqlParameter("@productID", SqlDbType.Int, 4)
Parameter0.Direction = ParameterDirection.Input
Parameter0.Value = ctype(request.querystring("productID"),integer)
myCommand0.Parameters.Add(Parameter0)
myCommand0.ExecuteNonQuery
'insert all the selected productscats
Dim i as integer
Dim mycounter = ctype(chk_counter.text,integer)
Dim MyString, MyArray
MyString = theIDs.text
MyArray = Split(MyString,",")
For i=0 to mycounter-1
Dim myCommand As New SqlCommand("spaddproductscats", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim Parameter As SqlParameter
Parameter = New SqlParameter("@productID", SqlDbType.Int, 4)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = ctype(request.querystring("productID"),integer)
myCommand.Parameters.Add(Parameter)
Parameter = New SqlParameter("@categoryID", SqlDbType.Int, 4)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = MyArray(i)
myCommand.Parameters.Add(Parameter)
myCommand.ExecuteNonQuery
Next
myConnection.Close()
tblform.visible = false
processing.visible=true
lbldone.text = "Modifications done !"
End Sub
============ end of code =================
Here are the stored procedures:
=====================
======== spaddproductscats ===========
CREATE PROCEDURE spaddproductscats
(@productID int, @categoryID int)
AS
-- insert all the new selected categories
INSERT INTO tbl_productscategories (productID, categoryID) SELECT
@productID ,@categoryID
Return
GO
========== end of spaddproductscats ===========
=========== spdeleteproductscats ============
CREATE PROCEDURE spdeleteproductscats
@productID int
AS
-- delete all the previous selected categories
DELETE FROM tbl_productscategories
WHERE tbl_productscategories.productID = @productID
Return
GO
============ end of spdeleteproductscats ===========
the categories for a selected product.
The user checks some categories in a tree (with checkboxes).
These are the categories where the selected product belongs to.
These checked categories are listed in a textbox named "theIDs" (ex:
25,116,420)
In the texbox named "chk_counter" there is the number of selected
categories: in this example: 3
So, in the table, I first delete all records containing the productID and
then I insert all the checked categories.
So, if the ID of the product is 14 these records will be inserted:
productID categoryID
14 25
14 116
14 420
Now, all this works, but it is very very very long.
On my broadband connection, it takes up to 15 seconds.
On a normal connection (ex: GPRS) it can take more than a minute...
I don't understand why it takes so long??
I put my code and the stored procedures below.
If you could have a look and tell me what could be the reason, I would
really appreciate it !
Thx a lot,
Nic
Here is my code when the user clicks on the insert button:
=======================================
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)
myConnection.Open()
'delete all the productscats
Dim myCommand0 As New SqlCommand("spdeleteproductscats", myConnection)
myCommand0.CommandType = CommandType.StoredProcedure
Dim Parameter0 As SqlParameter
Parameter0 = New SqlParameter("@productID", SqlDbType.Int, 4)
Parameter0.Direction = ParameterDirection.Input
Parameter0.Value = ctype(request.querystring("productID"),integer)
myCommand0.Parameters.Add(Parameter0)
myCommand0.ExecuteNonQuery
'insert all the selected productscats
Dim i as integer
Dim mycounter = ctype(chk_counter.text,integer)
Dim MyString, MyArray
MyString = theIDs.text
MyArray = Split(MyString,",")
For i=0 to mycounter-1
Dim myCommand As New SqlCommand("spaddproductscats", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim Parameter As SqlParameter
Parameter = New SqlParameter("@productID", SqlDbType.Int, 4)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = ctype(request.querystring("productID"),integer)
myCommand.Parameters.Add(Parameter)
Parameter = New SqlParameter("@categoryID", SqlDbType.Int, 4)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = MyArray(i)
myCommand.Parameters.Add(Parameter)
myCommand.ExecuteNonQuery
Next
myConnection.Close()
tblform.visible = false
processing.visible=true
lbldone.text = "Modifications done !"
End Sub
============ end of code =================
Here are the stored procedures:
=====================
======== spaddproductscats ===========
CREATE PROCEDURE spaddproductscats
(@productID int, @categoryID int)
AS
-- insert all the new selected categories
INSERT INTO tbl_productscategories (productID, categoryID) SELECT
@productID ,@categoryID
Return
GO
========== end of spaddproductscats ===========
=========== spdeleteproductscats ============
CREATE PROCEDURE spdeleteproductscats
@productID int
AS
-- delete all the previous selected categories
DELETE FROM tbl_productscategories
WHERE tbl_productscategories.productID = @productID
Return
GO
============ end of spdeleteproductscats ===========