ADODB.RecordSet object give me an empty value when asked for some fields.
i'm having some issues with an ADODB.RecordSet object. With Classic ASP and VBScript, i'm trying to recover data from this particular object. it is connected to and local SQL Server Express 2008 R2, and it was openned with an statement like this:
where cc and keyword22 are used for search purposes only. they both are simple "%" in my tests. objConn as you guess, are an ADODB.Connection object that should be working just fine, since it's used for all pages of the site and the problem is only in one page.
strSQL1 ="SELECT * from ((manutint mi LEFT OUTER JOIN manutint_codDoc md ON mi.mi_documento=md.doc_cod) LEFT OUTER JOIN cliente cl ON mi.mi_cliente=cl.cli_cod) LEFT OUTER JOIN fornecedores fr on mi.mi_encaminhadoa=fr.frn_cod and mi.mi_encaminhadoa = fr.frn_cod WHERE mi.mi_status<>4 and "
&_ " mi.mi_clientecc LIKE '"&cc&"' AND mi.mi_entrada LIKE '"&keyword22&"' order by mi.mi_cod"
Set objRS1 = Server.CreateObject("ADODB.RecordSet")
objRS1.Open strSQL1, objConn
The page code is awful big, so it would be no good to post it all. the bit that should work but dont is:
This code is used to fill an table with the data of the objRS1, which is our ADODB.RecordSet object.
if Not objRS1.EOF Then
Do While Not objRS1.EOF
Response.Write "<td>" & objRS1("cli_nome")& "</td>"
Response.Write "<td>" & objRS1("mi_dataentrada")& "</td>"
Response.Write "<td >" & objRS1("frn_nome")& "</td>"
Response.Write "<td >" & objRS1("mi_equipamento")& "</td>"
Response.Write "<td >" & objRS1("mi_ns")& "</td>"
Response.Write "<td >" & objRS1("doc_desc") &"</td>"
Response.Write "<td >" & objRS1("mi_numdocumento") &"</td>"
this code has no erros and should work with no problem, but exactly the fields "doc_desc" and "mi_numdocumento" are showed as "", empty values. Of course I looked into the database tables and executed the exactly same SQL query in MS SQL Management Studio to check if there was data into the database in the first place, but it was ok. Also tried to search some kind of difference between the fields that work and those that don't, to no avail. also tried to do some IsNull() and other checks with the values given by objRS1, and they returned false. it as if the field's content was an "", but it is not.
Did someone has ever seen something like this? is there some kind of test that i could do to track down the problem that i still didn't?
any help or suggestion is apreciated. even an small guess. already lost 4 hours into this.
I've had the same problem; here is the solution I found...
Guys, I ran in to the same problem with VBScript and ADO - not in ASP, just standalong VBScript, but the same problem. Here is what I find that triggers it, and I'm convinced it's something within ADO. The scenario is complicated, but I've had this happen to me in more than one instance.
Consider the following SQL that is being executed:
The order of the column names is important, as you will see later.
select name, address_1, city, state, zip, country, my_flag from tablename
In looping through the resulting recordset, I am looping through the Fields collection of each row in the recordset (i'm building a CSV file if it helps you to know the purpose here);
However, one of the fields I need, I need to pull explicitly, BEFORE the For Each...Next loop; you can see it above the Set oFields line in the code above (and note the trailing comment that speaks to this problem); I've extracted it below for clarity:
Do While not oRs.Eof
vReportLine = ""
iRowNum = iRowNum + 1
' *** loop through fields in this row to build detail line
vMyFlag = cStr(oRs("my_flag")) ' need to do this outside of loop or it screws up the position in the loop...
Set oFields = oRs.Fields
For Each oField In oFields ' loop through all fields in the row
vFieldValue = oField.Value
Next ' For each oField in oFields
Loop ' Do While not oRs.Eof
. vMyFlag = cStr(oRs("my_flag")) ' need to do this outside of loop or it screws up the position in the loop...
When I run my script, I find that what happens is the field (aka column) preceding the column that I explicitly retrieve by name (my_flag) returns blank when being retrieved from the collection in the For...Each loop. In this example, the column named 'country' is returned as blank in vFieldValue when it gets to that item in the collection.
My solution was to re-order the columns in the select, to put the column that I had to retrieve explicitly, first in the list:
there being no column/field before the first one, everything worked out.
select my_flag, name, address_1, city, state, zip, country from tablename
I am not sure why this is, but I have seen this in other scenarios besides this implementation on other systems, with different versions of SQL and ADO.
I hope this helps, or that someone can comment on this.