Insert multiple records using one stored procedure call. SQL SERVER 7
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?
Thanks, Gary
Gary
|
0 |
|
gbminnock
3/19/2008 2:55:20 PM
asp.net.sql-datasource
29906 articles. 
0 followers.
9 Replies
1026 Views
Gary,
I'm not clear on exactly what you're asking. 28 dropdowns equates to 28 parameters in your stored procedure if you're just inserting a record. Can you provide more details?
Bruce
Bruce
Please remember to click "Mark as Answer" on the posts that helped solve your issue.
|
|
0 |
|
BHendry
3/19/2008 4:01:20 PM
Ok Chuck, never mind about the dropdowns. All Im ask is how to insert multiple records into a database using a stored procedure.
28 records which have 6 parameters each.
Gary
Gary
|
|
0 |
|
gbminnock
3/19/2008 4:30:17 PM
I will assume your 2 requirements are to use a stored procedure and make only 1 round trip to the database server. If those are the only requirements, you can pass all 168 parameters to the stored procedure and have 28 unique INSERT statements in your stored procedure.
Bruce
Please remember to click "Mark as Answer" on the posts that helped solve your issue.
|
|
0 |
|
BHendry
3/19/2008 4:53:09 PM
I have to pass the stored procedure 128 parameters. Do you have an example? Would this not slow things down a bit?
Thanks,
Gary
Gary
|
|
0 |
|
gbminnock
3/19/2008 4:59:22 PM
Gary,
It's hard to give an opinion on the best approach when I don't have a clear picture of what you are trying to do. Maybe you could be more descriptive. Trying to pack everything into one stored procedure is not always the best idea, but it is possible.
Stored procedures often times provide performance gains because the database server only compiles them once when accessed by your application. Subsequent calls are faster because of that. There are some things that could cause sql to recompile them, so if that's what you're going for it will be good idea to read up on the subject.
Here's a link to a good site about sql performance and an older article about stored proc recompiles. http://www.sql-server-performance.com/articles/per/optimizing_sp_recompiles_p1.aspx.
And here's one on passing parameters to a stored proc from ADO: http://www.codeproject.com/KB/cs/simplecodeasp.aspx
Good luck!
Bruce
Please remember to click "Mark as Answer" on the posts that helped solve your issue.
|
|
0 |
|
BHendry
3/19/2008 6:10:00 PM
hi,
check this url Using XML To Send Multiple Rows At Once Into A Stored Procedure
http://weblogs.sqlteam.com/travisl/archive/2005/01/04/3931.aspx
Inserting multiple records using one stored procedure
http://www.vikramlakhotia.com/Inserting_multiple_records_using_one_stored_procedure.aspx
|
|
0 |
|
gopalanmani
3/19/2008 8:11:49 PM
And if you upgrade to SQL 2008 you can pass a table parameter!
Bruce
Please remember to click "Mark as Answer" on the posts that helped solve your issue.
|
|
0 |
|
BHendry
3/19/2008 8:25:45 PM
unfortunately this xml will not work with sql server 7
Gary
|
|
0 |
|
gbminnock
3/20/2008 9:38:19 AM
Use SqlCommand like sqlcommand cmd=new sqlcommand(ProceadureName)
Declare Command Type as SP
Use Datareader
Use While to insert all records in a single click or single proceadure.
If u have any doubts mail me: amjeyachandran@yahoo.com
|
|
0 |
|
JeyaChandran
7/29/2008 10:05:44 AM
Similar Artilces:
Force SQL Server to recompile stored procedures every time they run (SQL Server 7/2000)
This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for...
MS SQL Server 7.0 error when trying to insert or delete a record in sybase base from SQL 7.0 analyser
Hello do you do know why when i try to delete an existing sybase server record or insert or call a stocked procedure i get an error message : (with client sybase 12) SQL analyzer code for delete : "delete from DS_CATS_INT.cats_int.dbo.TmpProduct where productId='riri'" i get this error message : Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: You can't have multiple recordsets with this cursor type (in a transaction.)...
MS SQL Server 7.0 error when trying to insert or delete a record in sybase base from SQL 7.0 analyser
Hello do you do know why when i try to delete an existing sybase server record or insert or call a stocked procedure i get an error message : (with client sybase 12) SQL analyzer code for delete : "delete from DS_CATS_INT.cats_int.dbo.TmpProduct where productId='riri'" i get this error message : Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: You can't have multiple recordsets with this cursor type (in a transaction.)...
MS SQL Server 7.0 error when trying to insert or delete a record in sybase base from SQL 7.0 analyser
Hello do you do know why when i try to delete an existing sybase server record or insert or call a stocked procedure i get an error message : (with client sybase 12) SQL analyzer code for delete : "delete from DS_CATS_INT.cats_int.dbo.TmpProduct where productId='riri'" i get this error message : Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: You can't have multiple recordsets with this cursor type (in a transaction.)...
Calling a .NET dll from a SQL Server 2005 Stored Procedure
Setup: I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states. There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem: I have not yet figured out how to call the dll from the stored procedure. Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help. So you're seeking for using CLR Integration in SQL2005. You can also take a look at t...
OPENXML Insert Multiple Rows Stored Procedure in SQL Server 2000
Hello, I am attempting to insert multiple rows from an xml doc int a sql server 2000 table. I've been following the msdn guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_1cx8.asp I feel like I'm 99% there, everything works except that the xml document I am recieveing has attirbutes that a can't seem to parse. In testing if I rename my elements so there is no attributes it works great. Seems very fast. Here is my stored procedure <code>CREATE PROCEDURE dbo.InsertData(@XMLDOC text)AS DECLARE @XmlHandle i...
creating and calling SQL Server 7.0 stored procedure from PB
Using: PowerBuilder 6.5.1, MS SQL Server 7.0 Can anyone show me an example of how to create and call a SQL Server 7.0 stored procedure using PowerBuilder with IN and OUT parameters? ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp I have an example for you. I don't have much time right now, so I didn't translate from dutch to english. If you can't figure it out, let me know... Tom CREATE PROCEDURE [Code_Insert_Procedure] @cCode varchar(12), @cOmschrijving varchar(50), @nResultaat int OUTPUT AS /*****************...
how do i insert data into sql server using data from textboxes and sql connection and sql command
anyone can give me a simple explaination and code.. Thanx in advance Take a look @ Sample Code: How to add...Sushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb There are a lot of tutorials out there, but I know they can be a bit confusing as they all approach things slightly differently. Basically a nice simple version would go like this:' *** First set up your connectionDim conConnection As New SqlConnection("server=servername;database=yourdatabase;uid=yourname;pwd=yourpassword;")' *** This line takes care of your sommand text and command connection in one ...
Stored procedure problem after I move from sql server to sql server 2005
Hi, Guys I have a problem with a stored procedure. When I used sql express. there was no problem but after I change server from sql express to sql server 2005, I have an error message. The followings are source code and error message when I run program. Please help me to figure it out. This is a behind code in VB Dim tmpchkno As TextBox = FormView1.FindControl("txtchecknoin") Dim tmpchkdate As TextBox = FormView1.FindControl("txtcheckdatein") Dim sqlcon As New SqlClient.SqlConnection Dim sqlcmd...
MS SQl Server 7 stored procedures returning a record set and a value
I have a stored procedure that returns a record set and a value, it looks like this Create procedure sp_test as * * * * Select * from table Return @mvalue What should I do in PB ( version 6.5) to retrieve the recotd set and the value? Thanks Enrique Hey, I suppose you want to call this stored procedure in a script... Take a look at "Declare" and "Fetch" in pb help Greets, Davy "Enrique Perez" <eperezch@sympatico.ca> wrote in message news:Qug6aNF3BHA.298@forums.sybase.com... > I have a stored procedure that returns a ...
Using PFC treeview with MS SQL Server 7.0 Stored Procedures
This is how I code my treeview to build my tree's hierarchy. ls_sql = "Select value, result from tbldomainvalue WHERE domainid=13 AND value=3;" li_return = this.inv_levelsource.of_Register(1, "result", "", SQLCA, ls_sql, "") this.inv_levelsource.of_SetPictureColumn(1, "1") this.inv_levelsource.of_SetSelectedPictureColumn (1, "2") li_return = this.inv_levelsource.of_Register(2, "labname", "", "d_grid_lab_all", SQLCA, "") this.inv_levelsource.of_SetPictureColumn(2, "1"...
Writing Stored Procedure in .NET 1.1 and using in SQL Server 2005
Hi, I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0. Please refer some good tutorial for it. Regards,Imran GhaniImran Abdul Ghani SQL Server requires 2.0. The .net team made a number of changes to the memory manager and gc so .net could be hosted in SQL Server....
Best way to pass multiple records into Oracle Stored Procedure in one call from .NET
Hi, I have been looking to find optimal way to pass record set (which is collection of records to be inserted into an oracle table which containts about 100+ columns) as parameter to Oracle stored procedure. This way, I would not need to create parameter for each column and call the Stored Proc too many times. I am thinking to make use of some kind of data structure which can be interpretted in both Oracle and .NET sides,something similar to Table of Oracle Object type or Oracle Record(of type <Table>%RowType) . I did not find support for neither of ...
connecting to SQL Server 7 using ole db provider for Sql Server
I had problems trying to do this with a grid so I am now trying to add a new connection in the Server Explorer. I added all the neccessary info and when I hit "Test Connection" it is successful but when I ckick on "OK" I get an error "Object reference not set to an instance of an object". Anybody know what is wrong? Thanks....
This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for...
MS SQL Server 7.0 error when trying to insert or delete a record in sybase base from SQL 7.0 analyser
Hello do you do know why when i try to delete an existing sybase server record or insert or call a stocked procedure i get an error message : (with client sybase 12) SQL analyzer code for delete : "delete from DS_CATS_INT.cats_int.dbo.TmpProduct where productId='riri'" i get this error message : Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: You can't have multiple recordsets with this cursor type (in a transaction.)...
MS SQL Server 7.0 error when trying to insert or delete a record in sybase base from SQL 7.0 analyser
Hello do you do know why when i try to delete an existing sybase server record or insert or call a stocked procedure i get an error message : (with client sybase 12) SQL analyzer code for delete : "delete from DS_CATS_INT.cats_int.dbo.TmpProduct where productId='riri'" i get this error message : Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: You can't have multiple recordsets with this cursor type (in a transaction.)...
MS SQL Server 7.0 error when trying to insert or delete a record in sybase base from SQL 7.0 analyser
Hello do you do know why when i try to delete an existing sybase server record or insert or call a stocked procedure i get an error message : (with client sybase 12) SQL analyzer code for delete : "delete from DS_CATS_INT.cats_int.dbo.TmpProduct where productId='riri'" i get this error message : Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: You can't have multiple recordsets with this cursor type (in a transaction.)...
Calling a .NET dll from a SQL Server 2005 Stored Procedure
Setup: I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states. There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem: I have not yet figured out how to call the dll from the stored procedure. Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help. So you're seeking for using CLR Integration in SQL2005. You can also take a look at t...
OPENXML Insert Multiple Rows Stored Procedure in SQL Server 2000
Hello, I am attempting to insert multiple rows from an xml doc int a sql server 2000 table. I've been following the msdn guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_1cx8.asp I feel like I'm 99% there, everything works except that the xml document I am recieveing has attirbutes that a can't seem to parse. In testing if I rename my elements so there is no attributes it works great. Seems very fast. Here is my stored procedure <code>CREATE PROCEDURE dbo.InsertData(@XMLDOC text)AS DECLARE @XmlHandle i...
creating and calling SQL Server 7.0 stored procedure from PB
Using: PowerBuilder 6.5.1, MS SQL Server 7.0 Can anyone show me an example of how to create and call a SQL Server 7.0 stored procedure using PowerBuilder with IN and OUT parameters? ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp I have an example for you. I don't have much time right now, so I didn't translate from dutch to english. If you can't figure it out, let me know... Tom CREATE PROCEDURE [Code_Insert_Procedure] @cCode varchar(12), @cOmschrijving varchar(50), @nResultaat int OUTPUT AS /*****************...
how do i insert data into sql server using data from textboxes and sql connection and sql command
anyone can give me a simple explaination and code.. Thanx in advance Take a look @ Sample Code: How to add...Sushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb There are a lot of tutorials out there, but I know they can be a bit confusing as they all approach things slightly differently. Basically a nice simple version would go like this:' *** First set up your connectionDim conConnection As New SqlConnection("server=servername;database=yourdatabase;uid=yourname;pwd=yourpassword;")' *** This line takes care of your sommand text and command connection in one ...
Stored procedure problem after I move from sql server to sql server 2005
Hi, Guys I have a problem with a stored procedure. When I used sql express. there was no problem but after I change server from sql express to sql server 2005, I have an error message. The followings are source code and error message when I run program. Please help me to figure it out. This is a behind code in VB Dim tmpchkno As TextBox = FormView1.FindControl("txtchecknoin") Dim tmpchkdate As TextBox = FormView1.FindControl("txtcheckdatein") Dim sqlcon As New SqlClient.SqlConnection Dim sqlcmd...
MS SQl Server 7 stored procedures returning a record set and a value
I have a stored procedure that returns a record set and a value, it looks like this Create procedure sp_test as * * * * Select * from table Return @mvalue What should I do in PB ( version 6.5) to retrieve the recotd set and the value? Thanks Enrique Hey, I suppose you want to call this stored procedure in a script... Take a look at "Declare" and "Fetch" in pb help Greets, Davy "Enrique Perez" <eperezch@sympatico.ca> wrote in message news:Qug6aNF3BHA.298@forums.sybase.com... > I have a stored procedure that returns a ...
Using PFC treeview with MS SQL Server 7.0 Stored Procedures
This is how I code my treeview to build my tree's hierarchy. ls_sql = "Select value, result from tbldomainvalue WHERE domainid=13 AND value=3;" li_return = this.inv_levelsource.of_Register(1, "result", "", SQLCA, ls_sql, "") this.inv_levelsource.of_SetPictureColumn(1, "1") this.inv_levelsource.of_SetSelectedPictureColumn (1, "2") li_return = this.inv_levelsource.of_Register(2, "labname", "", "d_grid_lab_all", SQLCA, "") this.inv_levelsource.of_SetPictureColumn(2, "1"...
Writing Stored Procedure in .NET 1.1 and using in SQL Server 2005
Hi, I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0. Please refer some good tutorial for it. Regards,Imran GhaniImran Abdul Ghani SQL Server requires 2.0. The .net team made a number of changes to the memory manager and gc so .net could be hosted in SQL Server....
Best way to pass multiple records into Oracle Stored Procedure in one call from .NET
Hi, I have been looking to find optimal way to pass record set (which is collection of records to be inserted into an oracle table which containts about 100+ columns) as parameter to Oracle stored procedure. This way, I would not need to create parameter for each column and call the Stored Proc too many times. I am thinking to make use of some kind of data structure which can be interpretted in both Oracle and .NET sides,something similar to Table of Oracle Object type or Oracle Record(of type <Table>%RowType) . I did not find support for neither of ...
connecting to SQL Server 7 using ole db provider for Sql Server
I had problems trying to do this with a grid so I am now trying to add a new connection in the Server Explorer. I added all the neccessary info and when I hit "Test Connection" it is successful but when I ckick on "OK" I get an error "Object reference not set to an instance of an object". Anybody know what is wrong? Thanks....
Web resources about - Insert multiple records using one stored procedure call. SQL SERVER 7 - asp.net.sql-datasource
Parliamentary procedure - Wikipedia, the free encyclopedia... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...
Procedure is more a snap than a snipA QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...
Rushed cosmetic procedures a 'recipe for disaster'​When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...
Jetstar procedures under investigation after planes took off too heavyJetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...
Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?
What is some of the worst behavior you've witnesses by attorneys?
NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher ReportThe NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...
Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...
TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...
'Painless' dental cavity procedure regrows tooth enamel... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
Resources last updated: 12/28/2015 1:10:24 AM