PDA

View Full Version : Solved: ADO not returning value of field



Pirke
01-20-2006, 05:27 AM
Hi all,

a few days back I asked about how to connect in VBa with ODBC to an sql-database. Ken and Rembo helped out with setting up an ADODB.connection.

With the first table in the database this works without problems. But when I used the same style of code to connect to another table in the same database, I only get the fieldname of the field, and not the value of the field. I can get some other information back (like recordcount, status, ActualSize etc) but when i 'ask' for the value, all I get is the name.

Does anybody have any idea what I do wrong?

the code:
(all variables are declared (public) in a separate module, and the strings for the connection also)


Sub GetNdaBasic()
Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset
Dim sql As String
Set conn = New ADODB.Connection
strConnection = strDriver & strServer & strDatabase & strUID & strPWD & strOption
conn.ConnectionString = strConnection
sql = "SELECT 'id', 'statuslevel' FROM `nda_basic` WHERE (`id` = '10');"
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, conn
str1 = rs.Fields(0).Name
str2 = rs.Fields(1).Name
str3 = rs.Fields(0).Value
str4 = rs.Fields(1).Value

MsgBox str1
MsgBox str2
MsgBox str3
MsgBox str4



rs.Close


conn.Close


End Sub

str1 and str3 are the same, and str2 and str4 are also the same

Thanks for any input on this one!

Peter

Rembo
01-22-2006, 09:16 AM
Hello Peter,



.. But when I used the same style of code to connect to another table in the same database, I only get the fieldname of the field, and not the value of the field...

You might want to try add the line:

rs.MoveFirst

It forces the cursor to the first record of the rs object.
In VBA:


...

rs.CursorLocation = adUseClient
rs.Open sql, conn

'Move to the first record
rs.MoveFirst

...


Regards,

Remco

Pirke
01-23-2006, 12:37 AM
Remco,

I tried that (and also .MoveNext), but problem stays the same (.name and .value return the same result, this is the name of the column/field, not the value of the field)


Peter

Pirke
01-23-2006, 02:50 AM
All,

problem is solved, I found out that I was using the wrong quote-sing in the sql statement (I used ' and it should be `)

thanks for any time you put into looking for the solution

Peter

Rembo
01-23-2006, 02:41 PM
Hello Peter,


problem is solved, I found out that I was using the wrong quote-sing in the sql statement (I used ' and it should be `)

Oh yes, SQL is very picky on what characters you use. Glad you sorted it.

Cheers,

Rembo

koreazheng
01-24-2006, 05:56 PM
Probably the ".fields.value(0)" or ".fields.value(1)" is a NULL. You should use a isNULL() and a cstr() to avoid the error.