Issue related record set sheet in database connectivity...

Question

Hi, Please find below the code..

Function Connect_to_db(Byval mfgprt)

    Dim cnn,rss
    Set cnn = CreateObject("ADODB.Connection")
    Set rss = CreateObject("ADODB.recordset")
    cnn.ConnectionString = "DSN=QTPDSN;Description=desc;UID=;PWD=;APP=QuickTest Professional;WSID=;DATABASE=;"

    cnn.open
    rss = cnn.Execute (""select  UnitPrice  from ProductProfilePrices  where MfPartNumber ='" + mfgprt + "'")

    Connect_to_db=rss(0)
End Function

In this function, if I change a col name unit price in Query with '*' then it will return more than one value..in that case how to use rss .....

As if i'll do it(replace unit price with '*'),then while running it populates an error..in rss data fetching.. please by doing same modify the code.....

Thanks, Galstar

Answer 1

You can refer to the fields by name, but first rss should be an object, so use Set, also the string concatenator is & :

Set rss = cnn.Execute (""select  UnitPrice, Quantity  " _
& " from ProductProfilePrices  where MfPartNumber ='" & mfgprt & "'")

''Let us say that only one row is returned for mfgprt :

varUnitPrice = rss("UnitPrice")
varQuantity = rss("Quantity")

EDIT re comments

Connect_to_db "AAA", Val1, Val2
MsgBox Val1 & "  " & Val2


Function Connect_to_db(ByVal mfgprt, ByRef Val1, ByRef Val2)
Dim cnn, rss
Set cnn = CreateObject("ADODB.Connection")
Set rss = CreateObject("ADODB.recordset")
cnn.ConnectionString = "DSN=QTPDSN;Description=desc;" _
    & "UID=;PWD=;APP=QuickTest Professional;WSID=;DATABASE=;"

cnn.Open 
rss = cnn.Execute("select  UnitPrice, Quantity  " _
    & " from ProductProfilePrices  where MfPartNumber ='" & mfgprt & "'")

Val1 = rss(0)
Val2 = rss(1)
End Function