ADODB.Connection in an ASP Page VBScript subroutine

[Heggr]

I am working on creating a new ASP page that needs to populate a Select object after three other fields have been populated. I am calling a VBScript subroutine to do this from the OnClick for the specified Select object. The problem I am having is that the code will not create the ADODB Connection. I know the username and password work and if I were to create the connection in the main body of loading the page it would connect. From everything I have read and found on the internet I should be able to do this via the subroutine but can not figure out why it is not working. Any help on this would be creately appreciated.

Sub LoadPoList(FieldNo,strConnect)
 
    Do until frmAddInvoice("cmbConsPO" & FieldNo).length = 0
        frmAddInvoice("cmbConsPO" & FieldNo).remove 0
    loop
 
    Max = FieldNo + 5
    value = FieldNo
 
    strConn  = "Driver={SQL Server}; Server=" & MyServer  & ";Database=" & MyDB  & "; Uid=" & MyUserName & ";"
    strConn = strConn &  " Pwd=" & MyPassWord & ";"
    Set myConn = Server.CreateObject("ADODB.Connection")
    myConn.Open strConn
 
    UseOwner = Trim(document.frmAddInvoice.cmbOwner.value)
    UseDestination = Trim(document.frmAddInvoice.cmbDestination.value)
    UsePart = Trim(document.all("txtGTW" & FieldNo).Value)
    strSQL = "SELECT DISTINCT ReferenceID FROM InventoryTransactions WHERE (TransactionType = 'Shipment') AND (Invoice IS NULL) AND (Destination = '" & UseDestination & "') AND (COID = " & UseOwner & ") AND (PartNumber = '" & UsePart & "')"
    set rds =  conn.execute(strSQL, dbOpenDynaset)
    Do While not rds.eof
    AddToDropDown(FieldNo,"")
    Do While Not rds.eof
        AddToDropDown(FieldNo,ReferenceID)
        rds.movenext
    loop
    rds.close
    set rds = nothing
    set myConn = nothing
End Sub
 

[jhardman]

so it's giving you an error at the setMyconn line? Everything looks good to me there. Try writing out the strConn variable to see if it looks right.

Jared

[Heggr]

Yes I have tried doing that and it does look correct.

I have talked with a co-worker and he seems to think it is because the manipulation of recordsets has to be done on the server side and NOT the client side. Do you know if this is correct? If this is correct do you know if it is possible to create the recordset on the server and then pass back the whole recordset as a variable back to the client side so it can be manipulated?

[jhardman]

Yes I have tried doing that and it does look correct.

I have talked with a co-worker and he seems to think it is because the manipulation of recordsets has to be done on the server side and NOT the client side. Do you know if this is correct? If this is correct do you know if it is possible to create the recordset on the server and then pass back the whole recordset as a variable back to the client side so it can be manipulated?

Ah, yes. I thought you were using this server-side. All ASP scripts run exclusively on the server and trying to get them to work on the client will fail in one way or another.

Probably the easiest way to do this nowadays is to open the whole recordset and export it as an XML file which can be manipulated ad nauseum by different components and even browsers. It's hard for me to know what you might want to manipulate on the client-side, since I am used to doing all my manipulations on the server, but this is doable. Let me know if this points you in the right direction.

Jared