Update Query Results

[jondel81]

The following code works.

Dim db_cust, strSQLQuery
db_cust = Server.CreateObject("ADODB.Recordset")
strSQLQuery = "UPDATE CUST SET CITY = 'VIRGINIA BEACH', STATE = 'VA' WHERE (LEFT(ZIP_COD, 5) = '23455')
Try
   db_cust.open(strSQLQuery, MM_CALPMAIN_STRING, 3, 3)
   db_cust = Nothing
Catch exc As Exception
   Label1.Text = exc.ToString
   db_cust = Nothing
End Try

The database I am using is Pervasive.SQL 8.5. The following output is from the Pervasive.SQL Data Manager. Is there a way to get the code above to produce a similer output? I am mostly looking for "X row(s) were affected."

UPDATE CUST SET CITY = 'VIRGINIA BEACH', STATE = 'VA' WHERE (LEFT(ZIP_COD, 5) = '23455')
SQL statement(script) has executed successfully.
80 row(s) were affected.

------

http://www.carealotpets.com

 

[ca8msm]

I've never worked with a Pervasive.SQL 8.5 databse before but SQL Server has an @@IDENTITY feature where you can run a SQL statement such as:

SELECT @@IDENTITY AS AMOUNT

You simply run this SQL Statement using the same connection directly after you run your insert statement and the AMOUNT column should return the new identity that you inserted.

I don't know if this will be of any help to you as it is for SQL Server and for returning an identity, but it may guide you in the right direction as Pervasive may have a similar method.


----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[jubble]

Try declaring a command object and passing the query into the command object rather than the connection object
When you call Execute have an extra parameter to collect the number of rows affected.

myCommand.Execute l_iRowsAffected

That way you've got you're two pieces of information

1) if error didn't occur then command executed successfully
2) no. of rows affected

Just as a note, Pervasive SQL should offer a .Net data provider component that allows access to their database through ADO.Net (every database provider worth their salt now does). Once you've downloaded and installed this you can dump ADODB Recordsets which, although they were quite good a few years ago, are nothing short of cumbersome compared to ADO.NET.

If you use ADO.Net the Command object will return the number of rows affected when calling ExecuteNonQuery.

l_iRowsAffected=myCommand.ExecuteNonQuery