Call connection.

Guys its a little tricky question...

I do not know how many of you are familiar of Stored Procedure calling conventions from VB. But there are two ways I am interested in right now ....

1. Either create the command object and add the SP parameters to it using append method ..

2. or directly call the SP using connection object.

connection.spname param, recordset

where spname = stored procedure name
param = parameter
Recordset = Result Recordset (SP Return)

Now the thing is in this method i have to hardcode the stored procedure name after connection. to make it work.

Is there a way to pass a string variable containing the store procedure name instead of hardcoding the stored procedure name itself ?

I hope i explained myself properly.

I usually opt for option 1. And yes, you can use a string var to hold the name of the SP and using it to set the CommandName.

I think it depends on the case. If you dbase is stored locally, it shouldn't matter, but if on a server, I would always use a stored procedure. This seems to take less time and conjested networks when returning the data back to the end user...

I think you guys did not get me ...

I'll explain again.

I am only interested in method 2 right now ..

I want to pass the stored procedure name to connection object ..

conn.spname

now spname has to be a hardcoded string value. I want to pass a string variable instead of a hardcoded value. That string variable contains the name of the stored procedure. But what it does is it assumes that string variable name itself as a stored procedure name and it does not takes the values inside it.

Any solution ???

are you talking about doing this...


conn.execute "Stored Proc <var1>, <var2>"


if you use the execute method of the connection object you can execute any SQL Script. This is assuming your using ADO

Quote:

---

Originally posted by techyspecy
I think you guys did not get me ...

I'll explain again.

I am only interested in method 2 right now ..

I want to pass the stored procedure name to connection object ..

conn.spname

now spname has to be a hardcoded string value. I want to pass a string variable instead of a hardcoded value. That string variable contains the name of the stored procedure. But what it does is it assumes that string variable name itself as a stored procedure name and it does not takes the values inside it.

Any solution ???

---

conn.spname I've never seen that method..... I've seen it as conn.execute spname

Quote:

---

Originally posted by techgnome
conn.spname I've never seen that method..... I've seen it as conn.execute spname

---

Its not a method.

conn.YourstoredProcedureName ParameterList, RecordsetToHoldtheReturnData

Quote:

---

Originally posted by techyspecy
Its not a method.

conn.YourstoredProcedureName ParameterList, RecordsetToHoldtheReturnData

---

Right, I understood what you meant.... I was just saying that I have never seen that before......

Quote:

---

Originally posted by techgnome
Right, I understood what you meant.... I was just saying that I have never seen that before......

---

Well - You have now. :D