Snitz Forums 2000 - SQL query vbscript vs TSQL

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Snitz Forums 2000 MOD-Group

MOD Add-On Forum (W/O Code)

SQL query vbscript vs TSQL

New Topic

Reply to Topic

Printer Friendly

Author

Topic

StephenD
Senior Member

Australia
1044 Posts

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?

HuwR
Forum Admin

United Kingdom
20584 Posts

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

StephenD
Senior Member

Australia
1044 Posts

Posted - 24 March 2010 : 11:07:40

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

StephenD
Senior Member

Australia
1044 Posts

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.

HuwR
Forum Admin

United Kingdom
20584 Posts

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
Senior Member

Australia
1044 Posts

Posted - 24 March 2010 : 17:37:38

Awesome thanks!

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.1 seconds.