Multiple Insert from listbox - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Insert from listbox

Author

Topic

Quentin
Starting Member

12 Posts

Posted - 2011-07-05 : 05:02:42

I have a listbox which displays all the HoleIDRef's. If I select one HoleIdRef and run the following INSERT statement, all works well.

INSERT DESPATCHDETAILS (DESPATCHNO,NAME,VALUE) VALUES('{2. Enter Despatch Number}','HoleIdRef','{1. Select Hole ID}')

If I select multiple HoleIdRef's from the list box I obviosuly get an error because there are too many values in '{1. Select Hole ID}'.

The error is "INSERT DESPATCHDETAILS (DESPATCHNO,NAME,VALUE) VALUES('WUG003226','HoleIdRef','Z70_E_900_SLT1_002','Z70_E_904_042')"
"There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement."

Is it possible in T-SQL to loop an INSERT for the number of items selected in the listbox.

Thanks

lappin
Posting Yak Master

182 Posts

Posted - 2011-07-05 : 05:58:55

Create a stored procedure which can handle multiple values and call this in the code behind sub. You can either pass a comma separated string containing the values or an xml string, which the procedure uses to do insert /update.
Lots of examples on web of both methods. Here is one:
http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 13:12:58

see

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Quentin
Starting Member

12 Posts

Posted - 2011-07-19 : 19:31:40

Thnaks, that script almost works. However, I am restricted in how I can pass the information.
Basically because of listbox parameters, I pass the list as
INSERT DESPATCHDETAILS (DESPATCHNO,NAME,VALUE) VALUES('WUG003231','HoleIdRef','B1 1103 SLT B_001','B1 1110 SLOT A1_001')
as you can see, the values get passed as 'B1 1103 SLT B_001','B1 1110 SLOT A1_001' how do I split this in the FUNCTION as currently I get the error "Procedure or function dbo.SplitHole has too many arguments specified."

Thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-19 : 20:01:36

We are the database guys and we do not care about a listbox; that is the front end and it does not exists in SQL.

Do this with a long parameter list. You can pass up to 2000+ parameters in T-SQL, which is more than you probably will ever need. The compiler will do all that error checking that the query version and the procedural code simply do not have unless you write a full parser with the standard error codes. You can now pass local variables to your procedure; you can pass other data types and get automatic conversions, etc. XML just gives you a problem maintaining mixed code and poor performance. In short, this is just good software engineering.

CREATE PROCEDURE LongList
(@p1 INTEGER = NULL,
@p2 INTEGER = NULL,
@p3 INTEGER = NULL,
@p4 INTEGER = NULL,
@p5 INTEGER = NULL)

x IN (SELECT parm
FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X(parm)
WHERE parm IS NOT NULL;

You get all the advantages of the real compiler and can do all kinds of things with the values.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 22:55:57

quote:
Originally posted by jcelko

We are the database guys and we do not care about a listbox; that is the front end and it does not exists in SQL.

Do this with a long parameter list. You can pass up to 2000+ parameters in T-SQL, which is more than you probably will ever need. The compiler will do all that error checking that the query version and the procedural code simply do not have unless you write a full parser with the standard error codes. You can now pass local variables to your procedure; you can pass other data types and get automatic conversions, etc. XML just gives you a problem maintaining mixed code and poor performance. In short, this is just good software engineering.

CREATE PROCEDURE LongList
(@p1 INTEGER = NULL,
@p2 INTEGER = NULL,
@p3 INTEGER = NULL,
@p4 INTEGER = NULL,
@p5 INTEGER = NULL)

x IN (SELECT parm
FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X(parm)
WHERE parm IS NOT NULL;

You get all the advantages of the real compiler and can do all kinds of things with the values.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

You've got to be kidding!

So...from your other posts where you say "Good programmers don't use table variables" (which is laughable) and this one, you're trying to tell people that passing 2,000+ paramaters is preferable to a single table variable?

I beg to differ.

Also..."A real compiler" REALLY?!! Wow...I never knew that the SQL Server Query Engine compiled the statement down to machine code. Know why I didn;t know that? Because IT DOESN'T.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 06:31:09

quote:
Originally posted by Quentin

Thnaks, that script almost works. However, I am restricted in how I can pass the information.
Basically because of listbox parameters, I pass the list as
INSERT DESPATCHDETAILS (DESPATCHNO,NAME,VALUE) VALUES('WUG003231','HoleIdRef','B1 1103 SLT B_001','B1 1110 SLOT A1_001')
as you can see, the values get passed as 'B1 1103 SLT B_001','B1 1110 SLOT A1_001' how do I split this in the FUNCTION as currently I get the error "Procedure or function dbo.SplitHole has too many arguments specified."

Thanks

why not you pass them as 'B1 1103 SLT B_001,B1 1110 SLOT A1_001' etc instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Quentin
Starting Member

12 Posts

Posted - 2011-07-20 : 17:36:25

quote:
Originally posted by visakh16

Unfortunately, I am unable to control the formatting.
The program we use controls the output and I am unable to modify it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-20 : 18:41:10

The best solution is to use a tabled-valued parameter. But, if you are restricted by some third-party program on the way it passes to SQL then you'll have to pick another option. What does: 'B1 1103 SLT B_001','B1 1110 SLOT A1_001' represent? Is that 2 values selected in your list?

Quentin
Starting Member

12 Posts

Posted - 2011-07-21 : 02:49:23

yes that is two values. Because of the limitations of the program, it supplies listed items as quoted and delimited by commas. It is very frustrating as I'm sure you can understand.

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 03:06:06

try capturing all values in a variable and passing from it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-21 : 11:32:24

Yeah, that sounds all bad. If you can't capture in a variable as Visakh suggested, you might be able to use one of Joe's "tricks" and add up to 2000 parameters.. ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources