Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringSQL query vbscript vs TSQL

SQL query vbscript vs TSQL

Printed from: Snitz™ Forums 2000
URL: https://forum.snitz.com/forumTopic/Posts/69247?pagenum=1
Printed on: 05 November 2025, 06:50

Topic

Author : StephenD
Subject : SQL query vbscript vs TSQL
Posted : 24 March 2010, 09:57
Message :

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?

Replies ...

Reply Author: HuwR
Replied on: 24 March 2010, 11:04
Message:

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

Reply Author: StephenD
Replied on: 24 March 2010, 11:07
Message:

Ah... the penny drops ... getrows then array etc... Doh! :)

Reply Author: StephenD
Replied on: 24 March 2010, 11:49
Message:

I'm still struggling with this:

Code:

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.

Reply Author: HuwR
Replied on: 24 March 2010, 14:16
Message:

you are continually overwriting customerArray rather than appending to it, try something like this

Code:


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)

Reply Author: StephenD
Replied on: 24 March 2010, 17:37
Message:

Awesome thanks!