Last post Jul 29, 2008 12:50 PM by Mikesdotnetting
Jul 29, 2008 09:54 AM|jimbo101974|LINK
I have a query set up for an Access database I've included the code below. The variable qryRes returns a number for me which is stored in the database. What I would like to be able to do is make sure that number is not equal to zero and if it isn't, subract
1 and write it back. What I have been trying to do is attempt to convert qryRes to an integer but this apparently is an illegal operation. Any thoughts on how I can go about this?
As advertised, the code
qryRes = chkCmd.ExecuteReader
Jul 29, 2008 10:13 AM|StefanPienaar|LINK
You can't convert a data reader to a integer well... because its a set of records, and not just one variable.
What you should rather do is, after executing the reader:
if (qryRes.Read()) then
Dim yourVar as Integer = reader.GetInt32(0)
Note I used an If statement. If you are expecting more than 1 row, then use a While Loop. You could also have use the following if you only want the first column of the first row:
Dim yourVar as Integer = Cint(cmd.ExecuteScalar())
Jul 29, 2008 10:36 AM|jimbo101974|LINK
Thank you. The very last line was all I needed. I'm going to do a little reading on what ExecuteScalar is and when it is appropriate. For now that chunk of code does exactly what I need.
Jul 29, 2008 12:50 PM|Mikesdotnetting|LINK
ExecuteScalar() - for returning one single value. Typically used for counts and returning IDs of newly added records.
ExecuteNonQuery() - for executing Insert, Update and Delete
ExecuteReader() - for returning more than one value in a row, or multiple rows.