using simple sql select statement to assign value to variable 1

[ije]

I am trying to assign the results of a a SQL select statement to a variable, but the result is always blank ie it appears no value is returned.

I am only a casual vbscripter and thought someone could assist?

Below is the code I am using, which at the moment I would be happy to have the returned value displayed in a messagebox. BTW, the SQL statement works when used in Query Analyser.

Thanks in advance heros.

Dim conn, orecordset
Dim sop_number,agency,message,selectSQL

sop_number= cstr(wscript.arguments(0))
agency= cstr(wscript.arguments(1))

Set conn = createobject("ADODB.Connection&quot
conn.open "dsn=heat-sdg;uid=sa;pwd=benice;database=SDG"

SelectSQL= "select filepath from filetable where sop_number ='" & trim(sop_number) & "' and agency='" & trim(agency) & "'"

Set oRecordSet = CreateObject("ADODB.Recordset&quot
oRecordSet.Open SelectSQL, Conn

If oRecordSet.RecordCount =1 Then
message.Value = oRecordSet("filepath&quot.Value
End If

oRecordSet.Close
Conn.Close

Set oRecordSet = Nothing
Set Conn = Nothing

msgbox"The value of message is: " & message

 

[PHV]

1)Comment out all Error Resume Next instructions, if any.
2)If you want to use the RecordCount property, you have to choose the proper cursor type, like this:
oRecordSet.Open SelectSQL, Conn, 1
3)Not sure that message.value will not raise an error
To display a message box, try something like this:
MsgBox oRecordSet("filepath&quot.Value

Hope This Help
PH.

 

[ije]

thanks PH - i am not quite I understand ( due to my inexperience I am sure! ). Would you have a simple example you are able to share?

Many thanks
ije

 

[dilettante]

I'm not sure myself about the default CursorType and support for RecordCount, but have you tried replacing:

If oRecordSet.RecordCount =1 Then
message.Value = oRecordSet("filepath&quot.Value
End If

By:

oRecordSet.MoveFirst
message = oRecordSet("filepath&quot.Value

This does 3 things:

1.) Avoids messing with cursors right now.
2.) Corrects the error in setting "message."
3.) Does the MoveFirst which is probably going to be required anyway.

I'm too tired to look up CursorTypes and RecordCount support right now, but you ought to check that out too.

 

[ije]

thanks all

I have it working with:

Set oRecordSet = CreateObject("ADODB.Recordset&quot
oRecordSet.Open SelectSQL, Conn
message= oRecordSet.fields("filepath&quot

this works fine, but does not cater for situations where it finds no records or multiple records. That is the next bit i guess for me....

 

[dilettante]

Microsoft says:

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

Quote from:

http://msdn.microsoft.com/library/d...ry/en-us/ado270/htm/mdapro05_9.asp?frame=true

You probably want an adOpenStatic (which is 3) cursor type in your case. As suggested by PHV you can also use an adOpenKeySet (1) cursor type. The real issue here is whether or not your table is undergoing concurrent deletes that you care about. When "harvesting" a few records for reporting a static cursor is probably going to be fastest. If you are using a cursor location of adUseClient (3) you have no choice - you have to use a static cursor to accomplish what you want here. The default is adUseServer (2) but for an Access database for example it usually makes sense to go with a server side cursor.

http://msdn.microsoft.com/library/d.../en-us/ado270/htm/mdaenumac_15.asp?frame=true

Microsoft says:

As a general rule, you should use server-side cursors for working with Access databases, and client-side cursors when working with SQL Server databases. However, although using client-side cursors with Access databases generates additional overhead to cache records and doesn't expose all of the functionality of the Jet database engine, you still may want to use client-side cursors with Access databases if you are working with a remote database, or if you need to ensure uniformity of behavior when working with multiple data sources.

Quote from:

http://msdn.microsoft.com/library/d...tml/deovrrecordsetobjectbasics.asp?frame=true

The test for an empty Recordset is if .BOF and .EOF are both true.

Microsoft says:

If you open a Recordset object containing no records, the BOF and EOF properties are set to True (see the RecordCount property for more information about this state of a Recordset). When you open a Recordset object that contains at least one record, the first record is the current record and the BOF and EOF properties are False.

Quote from:

http://msdn.microsoft.com/library/d...ry/en-us/ado270/htm/mdapro01_7.asp?frame=true

These things can be looked up easily in the MDAC 2.6 SDK. Here is a download link (12MB or so):

http://www.microsoft.com/downloads/...8d-9bbb-4ca2-9b03-4ee000db37ff&DisplayLang=en

Much of this and more information is available in the MSDN Library CDs or online at MSDN Library Online:

http://msdn.microsoft.com/library/default.asp