SQL Statement

[astonmak]

Hi all,

I have the following code and send it to the SQL Server as batch :

Dim loCMD As New ADODB.Command
Dim loRS As New ADODB.Recordset
loCMD.ActiveConnection = CN
loCMD.CommandText = "INSERT INTO tableA (field1) VALUES ('valueabc');" & _
"SELECT field1 FROM tableA;"
loCMD.Cancel
Set loRS = loCMD.Execute

After this command ran, the 'valueabc' have been inserted into 'tableA', but it would not return the query "SELECT field1 FROM tableA;" to client side, I have tried to put 'GO' between this 2 command, but it doesn't work, I think 'GO' command only allow in stored procedure.

So, is there any way can return the query in a batch process, or I have to do it into 2 times?


Thanks
Aston

 

[SemperFiDownUnda]

It probably does but what you are doing is getting multiple tds's coming down to the recordset. Try the Recordset.NextRecordset method. Your just looking at the first recordset in the set of recordsets.

Kind of like doing a .MoveNext but on the recordset level.

 

[SonOfEmidec1100]

Because you have an update as the first statement the query will not return a recordset. I would use a stored proc and a recordset object.

However you should be able to do it your way using this sql

loCMD.CommandText = "SET NOCOUNT ON;INSERT INTO tableA (field1) VALUES ('valueabc');" & _
"SET NOCOUNT OFF; SELECT field FROM tableA"

and you should use this to declare the objects

Dim loCMD As ADODB.Command
Dim loRS As ADODB.Recordset

Set loCMD = New ADODB.Command
Set loRS = New ADODB.Recordset

 

[astonmak]

Hi SemperFiDownUnda,

I have put the "loRS.NextRecordSet" after the Execute command, but the system keep saying something about "Cannot found element/filed in RecordSet Object" (it means return nothing).

Is there any property able to check the count of recordset reside at RecordSet Object?

Thanks
Aston

 

[astonmak]

Hi SonOfEmidec1100,

Is the key at "SET NOCOUNT ON/OFF" ?

Thanks for your help
Aston

 

[SemperFiDownUnda]

I agree with SonOfEmidec1100 this is best for a stored proc.
Combine this with astonmak suggestion your stored proc might look like this

CREATE PROCEDURE blah
@sValue VARCHAR(200)
AS
SET NOCOUNT ON
INSERT INTO tableA (field1) VALUES (@sValue )
SET NOCOUNT OFF
SELECT field1 FROM tableA

That being said why do you feel this HAS to be done as a batch? if you did this

Dim loCMD As New ADODB.Command
Dim loRS As New ADODB.Recordset
loCMD.ActiveConnection = CN
loCMD.CommandText = "INSERT INTO tableA (field1) VALUES ('valueabc')" 
loCMD.Execute , , adExecuteNoRecords
loCMD.CommandText = "SELECT field1 FROM tableA;"
Set loRS = loCMD.Execute()

it will should work fine. Either way should be ok.
Also why do you put in a loCMD.Cancel?

 

[astonmak]

Hi SemperFiDownUnda,

Really thanks for your help, and valuable advise.

"That being said why do you feel this HAS to be done as a batch? if you did this"
Actually, I'm going to insert the data from Grid, so there should have more than one insert command, then I'd like the system get a "BILL NO." at the same and store into the database at the same time, afterall return the "BILL NO" back to the application by select statement. In the case of data being integrated, so I will do it in a batch (what i mean is send those command together at a single command.execute, is that what you guys called "Batch Process", or there is other definition?)

"Also why do you put in a loCMD.Cancel?"
Cause I have experienced that the previous command still running at the background, and not allow me to run the next command, so now everytime I run the execute, I would the cancel command at the front of execute. (but do u think it is necessary?)

Nice to have a discussion with u.


Best Regards
Aston

 

[SemperFiDownUnda]

You just created that command object. It has nothing running in the background.

a Batch is a unit of work that SQL compiles and optimises as a single unit. In SQL Sever the "GO" keyword tells SQL server that it must process what it has been sent. Some things have to occur in a batch before other statements can use them. Big example is Data Definition statements (ie ALTER TABLE etc)

I'm still unclear what you want to do.
If you need to add a record and get back a ID generated by an indentity column then a stored proc is your best bet and just call it for every record you are adding and return the identity that was just created. If this is what you need to do I can show the code for the stored proc.

 

[astonmak]

Hi SemperFiDownUnda,

We have a sales order in the application, and users will be input their data to a MSHFlexGrid(there wil be more than one record to be store at a single time), and the sales order no will be garther from a table called "RecordNo". In the case of data integration, the programmer will be send those SQL commands at a single time.

Once the data updated sucessfully, the application will return the sales order no. and load the data again to the screen by the returned no. If the update is fail, the whole update process will be aborted. And the code now is hosting at the application side as following :

Dim loCMD As New ADODB.Command
Dim loRS As New ADODB.Recordset
loCMD.ActiveConnection = CN
loCMD.CommandText =
"SET NOCOUNT ON;" & _
"BEGIN TRAN;" & _
"DECLARE @lcRecordNo varchar(10);" & _
"UPDATE recordno WITH (HOLDLOCK,ROWLOCK) SET sono=sono+1 WHERE docname='SALESORDER';" & _
"SELECT @lcRecordNo=sono FROM recordno WHERE docno='SALESORDER';" & _
"INSERT INTO tableA (sono,field1) VALUES (@lcRecordNo,'value1')" & _
...
"INSERT INTO tableA (sono,field1) VALUES (@lcRecordNo,'valueNN')" & _
"COMMIT TRAN;" & _
"SET NOCOUNT OFF;" & _
"SELECT @lcRecordNo as recordno;"

SET rs = loCMD.Execute()


How is this code? Is it foolish? BUT I wondering that is it better to host those code in the application or put it into the STORED PROC. Another question is : The number of records are flexible, sometimes are 2, sometimes are 99, how the code will be conduct in STORED PROC.

Thanks
Aston