vba get value from sql server recordset

Question

My code to retrieve value is as below:

Sub UploadData()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

Dim strConn As String
Dim sql As String

strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=.\sql2000;INITIAL CATALOG=EquityDB;INTEGRATED    SECURITY=sspi;"

cn.Open strConn

sql = "select * from  EquityDB.dbo.table1 where field1 = '" & Replace(Range("d1").Value, "'", "''") & "'"

 rs.Open sql, cn, adOpenDynamic, adLockOptimistic
  GetData = rs.Fields(0).Value


 If Not GetData = "" Then

 cn.Execute sql001

 Else

 cn.Execute sql002

End If

sql001 is an insert, and sql002 is an update

When I run the macro, I got error saying operation is not allowed when the record is open for the line

    rs.Open sql, cn, adOpenDynamic, adLockOptimistic

If I change

   If Not GetData = "" Then

to

   If Not GetData Is Null Then

I get error saying "object required" with the line

   If Not GetData Is Null Then

Any advice on how to fix the bug would be great!

1) What is the actual error you are getting and which line is causing it? 2) it should be If Not IsEmpty(GetData) then — user2140173
– user2140173, Commented Oct 16, 2014 at 15:10
Also explain what you are trying to achieve. Create a new record when no record is found and updating the record or records when it is\they are found? My guess is that the update part goes wrong. — Barry
– Barry, Commented Oct 16, 2014 at 15:13
Thanks for all of your advice! I was actually trying to do a loop, I guess I just need to clear the rs each time — user3344443
– user3344443, Commented Oct 16, 2014 at 18:26

JNevill · Accepted Answer · 2014-10-16 15:37:52Z

4

To test if anything was returned into your recordset, instead of:

 GetData = rs.Fields(0).Value    

 If Not GetData = "" Then

Use:

If not(rs.eof and rs.bof) then

This will return true if the recordset is not empty.

answered Oct 16, 2014 at 15:37

JNevill

50.6k4 gold badges46 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

vba get value from sql server recordset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related