command.execute with SQL update or insert string returns no recordset

[harmmeijer]

I am using the following command
mycommand.commandText = “UPDATE myTable SET myField=’myvalue’ WHERE myIndex=5
set myRecordset = mycommand.execute(1,,adCmdText)
The recordset myRecordset has got a field count of zero but the record with the myIndex of 5 is updated, the same goes for an INSERT sql command the new record is made but myRecordset has got zero fields.
I really need the recordset (in the INSERT especially) because after the INSERT sql command I need to put some binary stuff in that record and I need the myIndex of the new record.

 

[woyler]

What data exactly are you trying to populate the recordset with? You can return the number of records affected by including a variable(long) in the command execute statement:

cmd.Execute lRecordsAffected, , adCmdText And adExecuteNoRecords

 

[sidanshu]

Other way around could be - If possible you can make use of the rimary key of that table to further process the record.what i mean is that after inserting the record you can read its primarykey value in a variable
(using var1 = recordset!fieldname )and then do anything with that record based on that variable value- say update field1 ... from table1 where <primarykeyname> = variable1.
hope that helps.
Anshu

 

[woyler]

My question is if you are inserting a record into the database, then you already know the data. Why would you need to retrieve the data you just inserted unless there are triggers or something of that nature on the table?

 

[harmmeijer]

The primary key is an autonumber (myIndex) editing data with an SQL update string is not a problem (I know the myIndex).
Using the INSERT SQL is a problem because I do not know the myIndex but after using INSERT to put in ascii data I have to put in a picture wich is uploaded to the server and put in by another function so I have to know the myIndex (primary key) of the record created by the INSERT SQL.

 

[woyler]

Your question was misleading for me because in your original post you said your command text was:
mycommand.commandText = “UPDATE myTable SET myField=’myvalue’ WHERE myIndex=5
which would mean that you knew the value of MyIndex.

So, you want to be able to insert a new record, thereby generating a new auto incrementing id(Your Prmary Key), return that new ID and update another table based on that ID returned?

 

[harmmeijer]

Correct, I made a common function SQL generator and a common Command executer function. The functions DELETE and UPDATE are not a problem but INSERT is for example:
mysql = &quot;INSERT....&quot;
myCommand.CommandText = mysql 'generating a new record when executed
myRecordset = myCommand.execute(1,,adCmdText) 'record affected 1 executing the command therefor adding one record wich works ok and putting that record in myRecordset wich does not work.
Maybe it just does not work, when I use a select * from ... SQL string the myRecordset is filled but nothing happens with the database.

 

[sidanshu]

if your number generator is not a random no generator and gives you auto incrementing values - or just valuesin an increasing order - u can get the index number using select statement after update...trying to get the maximum index no i.e.
select max(primarykey) into :variable from......

try thatif possible for your case.
Anshu

 

[woyler]

You cant use a command object to execute an Insert SQL statement and return the entire new record in one call using the execute method as you are now. What kind of database are you using? We can set up a stored procedure for you to use if its not access

 

[harmmeijer]

That`s what my workmate suggested we are using MS SQL server. He suggested we should use SQL statements to change, delete and add records instead of a recordset. It works fine for delete and change but not for adding records.
The problem is I am not sure if I can just take the highest index number in the database after the update statement. Several people might update the database at the same time but as it is just 5 people at the most I will try to get the record with the highest index.

I still think it is strange the UPDATE and INSERT SQL command does not return a recordset.

 

[harmmeijer]

The answer I was looking for is this one:
the sql string should be like this
SET NOCOUNT ON;INSERT mytable (myfield,...)
VALUES ('myvalue',...)
SELECT @@IDENTITY AS 'Identity';SET NOCOUNT OFF
the recordset cannot be opened with the command.execute but with the rs.open method.