SQL Server and RecordSet dissolves

[Komatose]

Hi there!

I upgraded from Access Database to SQL Server.

Everything went fine. But now I have a big problem. Using ASP Scripts (and ODBC) to get all data from my database I connect to the database like

set dbConnect = Server.CreateObject("ADODB.Connection&quot
dbConnect.open "ODBC_entry", "user", "pass"
set RecordSet=dbConnect.execute("select * from table&quot

(or alternatively)
Set RecordSet = Server.CreateObject( "ADODB.Recordset" )
RecordSet.Open "select * from table", dbConnect

Now: This table consists of 38 columns. If I do things like

response.write RecordSet(&quot;Column1&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column2&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column3&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column4&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column5&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column6&quot&&quot;<BR>&quot;

everything wents OK. But if I do something like:

response.write RecordSet(&quot;Column10&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column1&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column20&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column7&quot&&quot;<BR>&quot;

it gets weird. RecordSet seems to be empty BEFORE the record I recently accessed. So after printing column 10 there's no more column 1 to column 9. Everything empty. Mostly I get an error when accessing f.e. Column1 then.

But if I do something like

select Column10, Column1, Column20, Column7 from Table

and then use

response.write RecordSet(&quot;Column10&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column1&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column20&quot&&quot;<BR>&quot;
response.write RecordSet(&quot;Column7&quot&&quot;<BR>&quot;

there's NO ERROR! Hence it's the column order!

Anybody ever had such a problem too? I can't carry on with my scripts since I alway get errors or empty lines. And changing every Select-Statement in every script is nearly impossible since the project consists of more than 400 different scripts and huge databases!

Anybody help me? Do you know any sollution or encountered the same failure?

Thank you for your help.

 

[swilliams]

Are you using the ordinal positions of the columns or the column names??

If you are using the ordinal positions(RecordSet(0), RecordSet(1),...) try using the column names (RecordSet(&quot;ID&quot, RecordSet(&quot;FirstName&quot,...)

Simon

 

[Komatose]

I'm using the names.
But I found a solution - well at least an expanation.
See Knowledge Base Article Q175239 at

http://support.microsoft.com/support/kb/articles/Q175/2/39.ASP

Thanks anyway.