Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 9th, 2007, 03:00 PM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default INSERT Multiple rows to database at one time

Hi All.

I am using .asp classic NOT .NET!

I have a database called "oddlistorders" that users add to it when they shop around and select certain products. Once done, they can view thier cart which is the "oddlistorders" database.

I then need to take all that they ordered which each row has a unique user ID and customer code and pass it to another database called "oddlistprocessed".

They problem is, it works fine if thwy only order ONE item (one row of data), but if they order more than one item, I cant UPDATE more than one row at a time.

Here is what I use now which works perfectly for ONE item:
<%
Dim quantity
quantity = Request.Form("quantity")


sql= "Update oddlist Set quantity='"& quantity & "' WHERE user_ID=" & ID

Rs.Open sql, Conn
Conn.Close
Set Rs=Nothing
Set Conn = Nothing

%>

Thanks!!
Reply With Quote
  #2 (permalink)  
Old April 9th, 2007, 03:19 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Well there is your problem right there: You are calling an UPDATE statement and not an INSERT statement!

Anyway, I can't trouble shoot this any farther since your code doesn't match what you explained above as being your problem. However, to move alot of records at one time I would do something like:

INSERT INTO [TABLE](column,column,column,column)
SELECT column,column,column,column FROM [anotherTable] WHERE id = [value]

That will insert all values returned via your select and place them in the table specified in the INSERT.

Lastly, 2 things:
First: That seriously looks like a valid query string! You don't want to do that since, for example, if I had query browser or Administrator installed on this PC I could log into your server! (Hint hint, edit your above post and remove those values)
Second: Don't cross post; it doesn't increase your chances of getting an answer at all.


================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #3 (permalink)  
Old April 9th, 2007, 03:28 PM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oops. My bad here....

I AM using UPDATE, not INSERT. I already have a quanity the new database in which I want to write over to the new deducted quantity, but again I have multiple rows.
Reply With Quote
  #4 (permalink)  
Old April 9th, 2007, 03:41 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

If you can't update more then one row, it has to do with your WHERE statement. An UPDATE statement is cascading, for lack of a better term, which means that if you just call Update [table] set column = [value] it will update your entire table.

So, by you saying that the update statement is only updating one record in your database, its telling me that there is only 1 row in the database that matches your criteria that you have specified in the WHERE clause.

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #5 (permalink)  
Old April 9th, 2007, 03:46 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

After re-reading your post again, I have a question for you: You said "...each row has a unique user ID..." so that means that each row you are trying to update has a different value specified for user_ID correct?

So if the rows have ids of
1 3 5 and 9 and you need to update them all you could simply do something like
UPDATE [table] set column = [value] WHERE user_ID in(1, 3, 5, 9)
and that would update all 4 rows.

You could also do .....WHERE user_ID = 1 and user_id=3 etc but that is cumbersome.
hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #6 (permalink)  
Old April 9th, 2007, 03:52 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Quote:
quote:You could also do .....WHERE user_ID = 1 and user_id=3 etc but that is cumbersome
Not only cumbersome, but fruitless as well.

The chance of having a user _id that is both 1 and 3 at the same is zero.... ;)

(Sorry, couldn't resist..... )

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
Reply With Quote
  #7 (permalink)  
Old April 10th, 2007, 06:52 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Agreed Imar. But the way his one post read was that each row has a unique user_id, so to me it would seem that he is using that column as a PK and, it seems, the user_id column in the database is no way related to the actual person doing something on the web app, however, that is just speculation on my part! ;]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #8 (permalink)  
Old April 10th, 2007, 08:17 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

I know, and I understand. However, I think changing AND to OR would have gotten you / the OP what you wanted....

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
Reply With Quote
  #9 (permalink)  
Old April 10th, 2007, 08:21 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

LOL. Wow, I can't believe I over looked that....need more coffee....;]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #10 (permalink)  
Old April 10th, 2007, 08:39 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok gentlemen,
I think we are making progress here, BUT... How would I dynamically call in the rows user_ID in my where statement then? You say "WHERE user_ID = 1 OR user_id=3", but I dont know if its 1, 2, 3, 4, 5 etc.....

I need all the rows that have their own user_ID number to UPDATE the EXACT matched user_ID number in the other database and just change the new quanity field.


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
About Insert Multiple Rows of GridView to Databas divekar.vishal ASP.NET 2.0 Professional 0 April 30th, 2008 12:28 AM
Creating Multiple Rows at 1 Time BrianWren Pro VB Databases 0 February 22nd, 2008 01:37 PM
INSERT multiple rows to a table nvillare Oracle 7 November 3rd, 2007 04:47 AM
INSERT Multiple rows to database melkin Classic ASP Professional 3 April 10th, 2007 09:03 AM
insert rows in database in a bulk from grid harrysingh26 ASP.NET 2.0 Professional 6 March 6th, 2006 11:46 AM



All times are GMT -4. The time now is 08:08 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.