SQL Stored Procedure From Excel

Hi,

I have a stored Procedure that updates only 1 row (No changes in the count of rows).

I am using the below code to execute the Stored Procedure.

Please Login or Register  to view this content.

Is there a way I come to know If the Stored Proc Executed properly?

Is there a way of knowing if it is running as desired at all?

You can pass a variable to either of Command.Execute method Or Connection.Execute methods as an argument Recordsaffected. After execution t will hold the number of affected records.
You can also use option adExecuteNoRecords to tell the provider not to construct Recordset Object if it is not a row-returning query

Originally Posted by Kyle123

What does the sproc return? What database are you using?

Hi Kyle,

The Stor Proc does not return anything.. All it does is update the Value of a Row in the Table to Zero..

Originally Posted by buran

You can pass a variable to either of Command.Execute method Or Connection.Execute methods as an argument Recordsaffected. After execution t will hold the number of affected records.
You can also use option adExecuteNoRecords to tell the provider not to construct Recordset Object if it is not a row-returning query

Hi Buran,

Actually I am learning ADO hence I am not understanding very clearly what exactly is to be done..

Can you please clarify..

First of all, I don't see the Execute method of SQLCommand, so I'm not sure how do you actually use this code to run the stored proc.
Second - I'm not able to test, so below code may have errors

Please Login or Register  to view this content.

Originally Posted by buran

First of all, I don't see the Execute method of SQLCommand, so I'm not sure how do you actually use this code to run the stored proc.

Actually I tried using the Execute method, which gave a run time error when I ran the code..

Let me see how this code works.. I will get back in some time..

Cheers!

What is your DB?

I tried it, but It returns error on the Execute method..

Are you asking the DB Name? If yes, it is DB_Common

No, I mean MS SQL, MySQL, Oracle, etc...
also what is the exact error msg?

Its SQL.

The error screenshot -

Originally Posted by NeedForExcel

Its SQL.

SQL is the language, it comes in different flavours and implementations. The underlying database could be MS SQL Server, MySQL, Oracle, Ms Access, sqlite PostgreSQL, to name a few.
I'm not sure about the double dots in your SQL statement. I, as well as Kyle123, am asking about that.
For example in MS SQL it's enough to supply it's stored procedure name to command object CommandText property. In this case you should use

Please Login or Register  to view this content.

However in your code you use SQL string, so I think

Please Login or Register  to view this content.

That is something, together with the 2 dots in the sql string, that I didn't notice

Hi Buran,

Its MS SQL (SSMS)...

And, Double dots were new to me too, but that is how it is used in the place where I work..

And yes, If I use .CommandType = adCmdText, do I still need the Execute method?

Originally Posted by NeedForExcel

Hi Buran,

And yes, If I use .CommandType = adCmdText, do I still need the Execute method?

yes, you should use Execute

I'm pretty sure that MSSQL sprocs always return an int. If nothing is specified or null is returned it's 0, your sproc should really have a return value to indicate the outcome - so you need to change that.

Then it's simply:

Please Login or Register  to view this content.

Your sproc should then have a line:

Please Login or Register  to view this content.

That returns the number of rows affected

Thank you Buran & Kyle as always..

Guess I'll need a guy with some SQL Experience to help me create that output parameter.

Marking the thread solved as of now, but will get back in case I need further assistance..

So did it work?

Originally Posted by buran

So did it work?

Not as of yet.. Trying real hard..

Originally Posted by Kyle123

It's only that one line, will look something like:

EDIt had to attach as it's SQL (firewall doesn't like it)

Not sure If I am allowed to do that.. Let me check it out..

By the way, can you guide me as to where I can get some reading material on ADODB?

Alright!

Thank You for the help

Hi Kyle & Buran,

I managed to add to the Stored Proc as required..

Please Login or Register  to view this content.

And My Final ADO Code is

Please Login or Register  to view this content.

But still not working.. After the .Execute Method, it jumps to the CloseConnection label..

Shouldn't your SourceQuery variable just be the name of the procedure?

Please Login or Register  to view this content.