populating a combo box in vbscript

[tibor]

hi all,

im not too good with the vbscript. i am trying to populate a combo box using a sql query. here is the code that i have

dim cDB
dim oRec
 
 
 
set cDB = Session.AppDatabase
Set oRec = cDB.OpenRecordset("Select distinct dbo.vluCustomerAddrBilling.AddrName, dbo.tPA00175.chrJobName FROM dbo.vluCustomerAddrBilling INNER JOIN dbo.tPA00175 ON dbo.vluCustomerAddrBilling.CustKey = dbo.tPA00175.CustKey WHERE (dbo.tPA00175.chrJobName = '"&Form.Controls("txtProjectName")&"')",1,0)
 
Do Until oRec.EOF
     combobox1.AddItem oRec("AddrName")
     oRec.MoveNext
     Loop
     oRec.Close

i am getting the error message with the oRec.EOF. this is the way to do it in vb6 but what is the syntax for vb script.

thanks alot
tibor

[tibor]

i get this error

object doesnt support this property or method: 'oRec.eof'

[tibor]

ok i figured out the loop issue but now im getting a not so major error, its more of an annoyance.

here is the code that resolved my eof problem

Do Until EOF=true
     Form.Controls("ComboBox1").AddItem oRec.Field("AddrName")
     oRec.MoveNext
     Loop
     oRec.Close

ok now i have an error message popping up saying

Attempt to scroll past end or before beginning of data.

the loop works but it adds the last value twice as i would imagine thats why i get the message. so what is it that is causing it to do this??

tibor

[aconybeare]

Tibor,

Try this -

If Not oRec.BOF And Not oRec.EOF Then
  Do While Not oRec.EOF
     combobox1.AddItem oRec("AddrName")
     oRec.MoveNext
  Loop
End If
oRec.Close

[tibor]

still gives me the same error.

[aconybeare]

Try opening your recordset like this -

Dim sSQL
Dim cn
Dim cnStr
Dim oRec
 
cnStr="driver={SQL Server};server=SERVER_NAME; database=DB_NAME"
 
Set cn=Server.CreateObject("ADODB.Connection")
cn.open cnStr
 
sSQL="Select distinct dbo.vluCustomerAddrBilling.AddrName, dbo.tPA00175.chrJobName" & _
    " FROM dbo.vluCustomerAddrBilling INNER JOIN dbo.tPA00175 ON dbo.vluCustomerAddrBilling.CustKey = dbo.tPA00175.CustKey" & _
    " WHERE (dbo.tPA00175.chrJobName = '" & Form.Controls("txtProjectName") & "')"
 
Set oRec=cn.execute(sSQL)
 
If Not oRec.BOF And Not oRec.EOF Then
  Do While Not oRec.EOF
     combobox1.AddItem oRec("AddrName")
     oRec.MoveNext
  Loop
End If
oRec.Close