Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

ASP/ADO/SQL Half of Record Vanishes

cag666cag666 Member Posts: 2
We're converting our Access databases to MS SQL Server. I'm currently working on ASP applications using server-side VBScript, connected to SQL via ADODB objects. I've run into the following problem, and I can't find an answer. One newsgroup had a message asking the same question, but it was asked in 10/2001 and still hadn't been answered :)

I open a recordset using the following syntax:
--------
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("ConnectionString")
sql = "SELECT * FROM TableX WHERE RecordID = '" & Request.QueryString("RecordID") & "';"
Set RSET = Conn.Execute(sql)
--------
The result is a single record which passes the 'If RSET.EOF else' test.

I then move to the first record with RSET.MoveFirst (just in case it returns multiple records) and display the fields with calls to RSET("FieldName").

Some of the fields display with no problem. Further down the page, fields I know are populated are returning a "" or NULL value. Data type doesn't seem to matter. It's happened with bit fields, ntext, nvarchar, etc.

Have tried removing all fields from any tables to see if table nesting was causing some obscure problem. No joy.

Even more strange: If I go back near the beginning of the page and call one of the 'missing' fields, it displays properly there -and- further down in the page where it wasn't showing up before. This does not seem to happen consistently, though. Calling all the fields at the beginning of the operation and assigning them to vbscript variables was unsuccessful.

Help? Thanks!

Comments

  • Anjuna MoonAnjuna Moon Member Posts: 89
    I found that importing from Access to MSSQL often messes up the datatypes, and these have to be changed afterwards to work properly in MSSQL. Also the order of the fields can have an importance when it comes to MSSQL used using ASP.
    Could you give me a list of the order the fields are stored in your table in MSSQL together with their datatype, something like:

    1 Field1 INT
    2 Field2 VARCHAR
    3 Field3 TEXT
    ....

    Then I know more to perhaps answer your question.

  • cag666cag666 Member Posts: 2
    Thank you for the help! It turned out that it was a problem with the order of fields. If I assume a forward-only cursor and load the fields into variables in that order, it works fine. Going to do a little more experimentation with the cursor type.

    Thanks, again!

    -Laurie

    : I found that importing from Access to MSSQL often messes up the datatypes, and these have to be changed afterwards to work properly in MSSQL. Also the order of the fields can have an importance when it comes to MSSQL used using ASP.
    : Could you give me a list of the order the fields are stored in your table in MSSQL together with their datatype, something like:
    :
    : 1 Field1 INT
    : 2 Field2 VARCHAR
    : 3 Field3 TEXT
    : ....
    :
    : Then I know more to perhaps answer your question.
    :
    :

  • Andre YoungAndre Young USAMember Posts: 0

    _____ < http://forcoder.org /> free ebooks and video tutorials about / C Ruby Visual Basic .NET Go C++ Python C# PL/SQL JavaScript Delphi Scratch Visual Basic PHP Objective-C R Java Swift MATLAB Assembly Perl F# ML Kotlin Erlang LabVIEW SAS COBOL Bash Awk Rust Julia Crystal Dart Lua D Scala Logo Transact-SQL Hack VBScript FoxPro Clojure Fortran Lisp Ada Apex Scheme Alice ABAP Prolog / ________

Sign In or Register to comment.