Select statement column retrieval order

[techlock]

Hi, I am new to this forum.

I use the following code in my .aspx file to access a MS-SQL Server 2005 database. But I encounter a very strange bug. If I change the selection order of columns in my query (like column2, column1, column3) then some values are not retrieved from the database. Has anyone else come across such an issue? Pls. help.

dim conn, rs, sql
conn = Server.CreateObject("ADODB.Connection")
conn.open("DSN=dsnname; user id=uid; pwd=pass")
rs = Server.CreateObject("ADODB.RecordSet")

sql = "select a.aname, b.bname, c.cname from a left outer join b on a.ID = b.aID 
left outer join c on c.aID = a.ID and c.bID = b.ID 
where a.ID = " & Session("aID") & " and b.ID = " & bID & " and c.ID = " & cID & ""

rs = conn.execute(sql)

Dim aname, bname, cname
aname = rs("aname").value
bname = rs("bname").value
cname = rs("cname").value

For ex: The query may fetch aname and bname. But cname might not be retrieved. But if I change the select statement as c.cname, a.aname, b.bname then I MAY get the required records.

 

[jmcilhinney]

That would be because you're using outer joins.

Also, I strongly recommend that you dump ADO in .NET apps and use ADO.NET. I also strongly recommend that you stop using string concatenation to build SQL statements and use parameters instead.

 

[techlock]

Ok. I will try that. Thanx for ur help.