Member Avatar for ste713

Can someone help stop my head ache! Im sure its very simple (it is with php).

I have run one query to get a users current cash

SQL = "SELECT * FROM money WHERE id='fred'
Set Recordset = Server.CreateObject("ADODB.Recordset")
Recordset.Open SQL,Connection
Recordset("cash")

This is correct and pulls the value I want, which is 10,000.

Now I try to make a new recordset, to pull another users current cash

newSQL = "SELECT * FROM money WHERE id='George'
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open newSQL,Connection
rs("cash")

Now this pulls the same value as the last recordset, 10,000. Which is wrong.

  • 4 Contributors
  • 3 Replies
  • 112 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by jcarbillon

Dani AI

Generated

Short, practical diagnosis and a small checklist for the classic ASP/ADO symptom where a second query returns the same field value as the first.

A few things that commonly cause this (and are consistent with points made by , and ): the script is still reading from the first Recordset object (variable mix-up or typo), the second query actually returns the same row/value, or the code never checks for EOF and therefore reads stale data. The simplest triage is to confirm which SQL actually ran and which recordset variable is being read.

Checklist (do these in order)

  • Add Option Explicit and declare recordset variables so misspellings are caught.
  • Echo the SQL text being executed and run that exact SQL in the database client (SSMS) to verify the expected row is returned.
  • After opening a recordset check rs.EOF before accessing fields. Read fields explicitly (for example, rs.Fields("Balance").Value) from the correct variable name.
  • Close and Set ... = Nothing on the first recordset before opening a second, or use a dedicated helper function that opens, reads, and immediately closes.
  • Avoid SELECT *; request only the needed column and use a parameterized command to prevent injection and accidental matching.

Example pattern (fetch a single scalar safely)

Function FetchBalance(conn, userKey)
  Dim cmd, rs, result
  Set cmd = Server.CreateObject("ADODB.Command")
  Set cmd.ActiveConnection = conn
  cmd.CommandText = "SELECT balance FROM wallet WHERE user_key = ?"
  cmd.CommandType = 1
  cmd.Parameters.Append cmd.CreateParameter("p1", 200, 1, 50, userKey)
  Set rs = cmd.Execute()
  If Not rs.EOF Then result = rs(0) Else result = Null
  rs.Close
  Set rs = Nothing
  Set cmd = Nothing
  FetchBalance = result
End Function

Final notes: enumerating matching rows (as suggested) is useful when confirming whether multiple records share the same value. Closing and nulling ADO objects and testing the SQL directly in the database are the fastest ways to eliminate code-side mistakes.

Recommended Answers

All 3 Replies

Member Avatar for ArtistScope

It's not possible unless George also has 10,000.

Maybe he got a loan from Fred?

Member Avatar for reygcalantaol

Please review your code, you my have a typo on the second code, or george might have the same cash value with fred.

Member Avatar for jcarbillon

create a loop and query all records having 10,000 cash, then check if fred and goerge displays on the result

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.