Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/O Code)
 SQL query vbscript vs TSQL

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!
Before posting, make sure you have read this topic!

Screensize:
UserName:
Password:
Format Mode:
Format: BoldItalicizedUnderlineStrikethrough Align LeftCenteredAlign Right Horizontal Rule Insert HyperlinkInsert EmailInsert Image Insert CodeInsert QuoteInsert List
   
Message:

* HTML is OFF
* Forum Code is ON
Smilies
Smile [:)] Big Smile [:D] Cool [8D] Blush [:I]
Tongue [:P] Evil [):] Wink [;)] Clown [:o)]
Black Eye [B)] Eight Ball [8] Frown [:(] Shy [8)]
Shocked [:0] Angry [:(!] Dead [xx(] Sleepy [|)]
Kisses [:X] Approve [^] Disapprove [V] Question [?]

 
   

T O P I C    R E V I E W
StephenD Posted - 24 March 2010 : 09:57:06
I'm having a brain snap on this basic query:

SELECT FORUM_ID, F_SUBJECT FROM FORUM_FORUM WHERE FORUM_ID IN (1,2,3) ORDER BY F_SUBJECT DESC

If I run it in TSQl I get 3 rows with Forum_Subject returned.

If I put the same query in an .asp page eg:

strSql = "SELECT FORUM_ID, F_SUBJECT FROM FORUM_FORUM WHERE FORUM_ID IN (" & clients & ") ORDER BY F_SUBJECT DESC "

Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

if not rs2.EOF then
customer = trim(rs2("F_SUBJECT"))
end if
rs2.Close
Set rs2 = Nothing

My query returns a single row or last record only. I know I am doing something blindingly obviously wrong to everyone but I've been looking at this a while now and can't see it. Any ideas?
5   L A T E S T    R E P L I E S    (Newest First)
StephenD Posted - 24 March 2010 : 17:37:38
Awesome thanks!
HuwR Posted - 24 March 2010 : 14:16:36
you are continually overwriting customerArray rather than appending to it, try something like this

For iRowLoop = 0 to UBound(customer, 2)
  For iColLoop = 0 to UBound(customer, 1)

customerArray = customerArray & ("'" & customer(iColLoop, iRowLoop) & "',")
response.Write customerArray

  Next
Next
customerArray = left(customerArray,len(customerArray)-1)
StephenD Posted - 24 March 2010 : 11:49:43
I'm still struggling with this:

strSql = "SELECT F_SUBJECT FROM FORUM_FORUM WHERE FORUM_ID IN (" & clients & ") ORDER BY F_SUBJECT DESC "
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

if not rs2.EOF then
   customer =  rs2.GetRows(adGetRowsRest)
For iRowLoop = 0 to UBound(customer, 2)
  For iColLoop = 0 to UBound(customer, 1)

customerArray = ("'" & customer(iColLoop, iRowLoop) & "',")
response.Write customerArray

customerArray = left(customerArray,len(customerArray)-1)
response.Write customerArray
  Next
Next

end if
rs2.Close
Set rs2 = Nothing

response.Write customerArray



I'm trying to get a string of Forum Subject Names available outside this bit of code as a variable in the following format eg. 'Forum_name1','Forum_name2','forum_Name3'

At my first response.write I get 'Forum_Name1','Forum_Name2','Forum_name3',

At my 2nd response.write I get 'Forum_Name1''Forum_Name2''Forum_name3'

At my 3rd response.write I get only 'Forum_Name1' ..single value

Which brings me back to where I started...

Where is my error please.
StephenD Posted - 24 March 2010 : 11:07:40
Ah... the penny drops ... getrows then array etc... Doh! :)
HuwR Posted - 24 March 2010 : 11:04:13
how do you know it returns a single row? you haven't even attempted to find out, this bit of code
if not rs2.EOF then
customer = trim(rs2("F_SUBJECT"))
end if
rs2.Close
Set rs2 = Nothing

will only ever return a single value, you haven't attempted to itterate through the results

Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000 Version 3.4.07