Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." 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 Basics 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
  #31 (permalink)  
Old June 10th, 2004, 09:26 AM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The array seems to work fine and it only sent it to the top 5 which I wanted but I checked the DB and the Mailsent are all still set to 0. Won't this statement update the databse? I want to make sure that it is working before I try sending it to all.
Does this have to go inside my loop as well?

Dim SQL
SQL = "UPDATE ORNewsletterTest SET Mailsent=1 WHERE NewsletterId IN (" & NewsletterId & ")"

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason
Reply With Quote
  #32 (permalink)  
Old June 10th, 2004, 11:51 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

Are you sure you're executing the code? Did you take out the Response.End part?

If that doesn't help, can you post the relevant code for the database update? And the output of the Response.Write statement.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #33 (permalink)  
Old June 10th, 2004, 12:09 PM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dim SQL2
    SQL2= "Update ORNewsletterTest set Mailsent=1 where NewsletterId in (" & NewsletterId & ")"

    Response.Write(SQL2)

I have this code after the loop and it seems to hold the proper data
SQL2=Update ORNewsletterTest set Mailsent=1 where NewsletterId in (1, 2, 3, 4, 5)

But I always use a statment like this to update a database, but I don't know how to make it work with an array.

    SQLStr="SELECT * FROM ORNewsletter where NewsletterId in (" & NewsletterId & ")"
        RS.Open SQLStr,Application("DBConn"),1,2,&H0001

        RS("NewsletterId")= NewsletterId
        RS.Update

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason
Reply With Quote
  #34 (permalink)  
Old June 10th, 2004, 01: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

Ah, that explains a lot.

The easiest way to do that is to use the Execute method of the connection object:
Code:
Dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open YourConnectionStringHere
oConn.Execute(SQL2)
oConn.Close
Set oConn = Nothing
Take a look at the ADO Programmer's Guide. There is a lot of useful information in there: http://msdn.microsoft.com/library/en...mmersguide.asp

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #35 (permalink)  
Old June 10th, 2004, 08:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yes Morpheus,

You already would have a connection object initialized. So you got to just add this EXECUTE thing next to your SQL2 statement to get those rows updated with mailsent=1

Code:
SQL2= "Update ORNewsletterTest set Mailsent=1 where NewsletterId in (" & NewsletterId & ")"
YOUR_Connection_Object.Execute(SQL2)
The statement in blue was not in your code, so you weren't able to update all those rows that were processed for newsletter.

I think you are almost done with it, just this step to go.
Cheers!

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #36 (permalink)  
Old June 11th, 2004, 07:33 AM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

    SQL2= "Update ORNewsletterTest set Mailsent=1 where NewsletterId in (" & NewsletterId & ")"


    Dim oConn
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open SQL2, oConn, 1, 2, &H0001
    oConn.Execute(SQL2)
    oConn.Close
    Set oConn = Nothing

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'Open'

Can you explain why I am getting this error? I am not sure what to do , do I need a recordset object as well or does this look right to you?

Thanks

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason
Reply With Quote
  #37 (permalink)  
Old June 11th, 2004, 09:00 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

No, you don't. You can use the exact code I gave you:
Code:
Dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open YourConnectionStringHere
oConn.Execute(SQL2)
oConn.Close
Set oConn = Nothing
All you need to do is change the YourConnectionStringHere variable to a valid connection string. There is no point in passing a connection object to the Open method of a Connection ;)

Imar
Reply With Quote
  #38 (permalink)  
Old June 11th, 2004, 09:41 AM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is the example I got from the MSDN library

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sConn As String
Dim sSQL as String

sConn = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _
          "Initial Catalog='Northwind';Integrated Security='SSPI';"

' Open a connection.
Set oConn = New ADODB.Connection
oConn.Open sConn

' Make a query over the connection.
sSQL = "SELECT ProductID, ProductName, CategoryID, UnitPrice " & _
             "FROM Products"
Set oRs = New ADODB.Recordset
oRs.Open sSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText

MsgBox oRs.RecordCount

' Close the connection.
oConn.Close
Set oConn = Nothing

The blue line is opening the recordset, but I want to just open the connection and execute the sql.

Do I need the red line as well, because once I log in the connection is made to the database. I am now gettting an error on this line

    oConn.Execute(SQL2)
that says

ADODB.Connection error '800a0e78'

Operation is not allowed when the object is closed




-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason
Reply With Quote
  #39 (permalink)  
Old June 11th, 2004, 11:01 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'll be more than happy to repeat the same stuff over and over again ;)
You don't need a recordset; all you need is my code with a suitable connection string. Since you're not retrieving records (you're using an UPDATE statement and not a SELECT statement, there is nothing to store in a recordset. The Execute method of the Connection object (see the link to the MSDN documentation I gave you earlier), just executes a SQL command you pass it. You are confusing connection objects (oConn in my example) with connection strings (YourConnectionStringHere in my example).

Try this:
Code:
Dim sConn
sConn = "Provider=SQLOLEDB;Data Source=MySqlServer;" & _
  "Initial Catalog=Northwind;Integrated Security=SSPI;"
Dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open sConn
oConn.Execute(SQL2)
oConn.Close
Set oConn = Nothing
The connection string you posted (and that I reused) is probably not going to work. It points to a SQL server called MySqlServer with the "default" NorthWind database. You probably should get your own connection string from another page that already runs fine. Maybe you need an Access string, or a SQL Server string; depends on the database you're using.

May I suggest you get some good books about ASP, databases, T-SQL etc and do some reading? You could have finished this application last week if you'd done some research in relevant books. I don't mind helping people, but I do mind doing other people's work. With a better understanding of the basic principle behind web-based and database driven applications, things become much easier for you.
I am not trying to be rude; I also didn't give you up; I am just trying to give you some good suggestions.

Cheers,

Imar
Reply With Quote
  #40 (permalink)  
Old June 11th, 2004, 11:11 AM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Imar, I understand exactly what you are saying and I apperciate the time you take to help me :). I guess I just thought I understood it better than I did. I think I can handle the SQL side of it but the asp is new to me. What books for asp would you recomend? also would it be a great advantage for me to learn asp.net?

Thanks again for your help, you have been more than fair ;)

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason
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
Newsletter Mechanism vivek_inos ASP.NET 1.0 and 1.1 Professional 1 September 22nd, 2007 07:38 AM
Where is the newsletter option luisjeronimo BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 July 20th, 2007 03:16 PM
newsletter vantoko BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 February 28th, 2007 03:28 AM
Newsletter sydneyausguy BOOK: Professional DotNetNuke ASP.NET Portals ISBN: 0-7645-9563-6 1 August 19th, 2005 05:23 PM
help with newsletter question AStuntz Beginning PHP 1 May 13th, 2004 06:54 PM



All times are GMT -4. The time now is 06:11 PM.


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