vb6 : what's wrong with this tsql procedure call?

Question

I have the following procedure call in vb6:

dim rs as adodb.command 
dim cnn as adodb.connection 

with rs
    set .activeconnection = cnn 
    .CommandType = adCmdStoredProc 
    .CommandText = "sp_qryUpdate" 
    .CommandTimeout = 0 
    .Parameters("@uidbatch").value = lngBatchID 
    .Execute , , adExecuteNoRecords 
end with 

I receive the error "Object variable or with block variable not set"

the error is thrown after the .activeconnection is set

any ideas what this means? the connection string i'm receiving is correct.

Answer 1

Strange, rs is normally used to indicate a record set rather than a command but to each their own, I guess.

I see some potential issues. I'm not entirely certain of the difference since my exposure to VB6 was pleasantly brief but all the code I did had:

dim rs as new adodb.recordset

in it (i.e., with the new keyword). That may be causing a problem.

I also assume you have some code between that second dim and the with, yes?

Because otherwise, you're not actually opening a connection for use.

There's some tutorial code here which may be of assistance. I'd say "Enjoy!" but I don't think that's an option :-)

Answer 2

you must create an instance of the adodb.command and adodb.connection before of use it.

try this

dim rs as New adodb.command 
dim cnn as New adodb.connection 

Answer 3

You need to create the rs and the cnn as the others say, but also you need to add a new parameter to the parameters collection.

.Parameters("@uidbatch").value

This assumes that it exists already.

Answer 4

You can do all that manual parameter definition, but it's much easier to just use rs.Parameters.Refresh. That will correctly populate the parameters collection automatically.

Also, while it is correct that you have to instantiate the object variables, doing it on the same line is not to be recommended. Unlike in .Net, this does not actually instantiate the variable. Rather, every time the variable is referenced, the runtime checks to see if an instance exists and creates one if not. This creates unnecessary overhead. Best practice is to use the Set keyword on a new line.