1

I've been experimenting with what I can do with a database, but I'm unfamiliar with aspects like storing a query result in a variable. I've used this snippet to query my database with more than one record, but the result I keep getting is zero. Can someone explain how this works? Here's my snippet...

str = "SELECT COUNT(Nickname) FROM Backup"
cmd.CommandText = str
cmd.Connection = myConn
myConn.Open()
cntPlns = cmd.ExecuteNonQuery()
MessageBox.Show(cntPlns.ToString)

The connection is there, but my result always comes out zero...thanks for any suggestions.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
KGCole
  • 11
  • 7
  • This doesn't seem like VBA code to me. Please note that VB.NET and VBA are two different languages. – 41686d6564 stands w. Palestine Sep 17 '18 at 04:56
  • Point worth noting, Ahmed. Though my form was set up in Visual Studio as VBA...so not sure which would apply. – KGCole Sep 17 '18 at 05:04
  • Not familiar with ADODB, Nick. But it may be what I'm looking for. I'll have to use the Intellisense to guide me on the syntax....also, I'll take your suggestion about using Count(*)... – KGCole Sep 17 '18 at 05:06
  • Oh you're right, this probably is in all likelihood .Net, not VBA. Please clarify. Posting all code helps – Nick.Mc Sep 17 '18 at 05:08
  • If it's VB.Net as tagged, then forget about ADODB – Nick.Mc Sep 17 '18 at 05:08
  • In this case here's the duplicate, but I can't vote to close again: https://stackoverflow.com/questions/10577174/how-do-i-retrieve-the-result-of-an-ado-net-sqlcommand – Nick.Mc Sep 17 '18 at 05:10
  • Well, I looked up ADODB.Recordset in Visual Basic, and it didn't have a reference in Intellisense. So I'm not sure where to go with it from there. I know my database select is correct. – KGCole Sep 17 '18 at 05:12
  • I checked my setup for VS, and the Project I set up was definitely .Net framework. So I guess that's the answer to the code....it's VB.Net. – KGCole Sep 17 '18 at 05:16

2 Answers2

2

See ExecuteNonQuery doesn't return results for ...nonquery is the wrong cmd.

Try this>>

str = "SELECT COUNT(Nickname) as myCount FROM Backup"
cmd.CommandText = str
cmd.Connection = myConn
myConn.Open()
Using cntPlns As SqlDataReader = command.ExecuteReader
        If cntPlns.Read Then
             MessageBox.Show(cntPlns.GetInt32(0).ToString)  ' the first column
        End If
End Using
donPablo
  • 1,937
  • 1
  • 13
  • 18
  • You're absolutely correct. There are a number of ways in VB.Net to read a query result ... but `ExecuteNonQuery` is *NOT* one of them ;) – paulsm4 Sep 17 '18 at 05:26
  • For a single value, it's more appropriate to call `ExecuteScalar`. If you were to use a data reader though, at least pass `CommandBehavior.SingleResult` to optimise it. Also, there's no point testing the result of `Read` in this case because it can't possibly be `False`. Also, you have provided C# code for a question specifically tagged VB.NET. – jmcilhinney Sep 17 '18 at 05:26
  • That's true, C# doesn't help me. Though I will probably branch into learning that after I'm more proficient at VB.Net – KGCole Sep 17 '18 at 05:36
  • revised to vb.net – donPablo Sep 17 '18 at 05:37
  • @KGCole - please consider upvoting, and "accepting" the response you find most helpful. – paulsm4 Sep 17 '18 at 16:05
1

You should be calling ExecuteScalar, not ExecuteNonQuery. ExecuteNonQuery is for executing a non-query, i.e. a SQL statement that does not produce a result set. ExecuteScalar is for executing a query and retrieving a scalar, i.e. a single value, which comes from the first column of the first row of the result set, whether or not that result set contains more data.

For the record, you get zero every time because ExecuteNonQuery returns the number of rows affected by the SQL statement, where affected means changed. A SELECT statement affects no rows so zero is the correct result.

It might serve you well to check out my ADO.NET examples to see what objects and members to use where and when.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • Thanks for explaining what ExecuteScalar. I can see how I was getting the 0 value before. I did run the cmd query as: cntPlns = Convert.ToInt32(cmd.ExecuteScalar()). and got the correct result. I'd like to know what the conversion ToInt32 means.... – KGCole Sep 17 '18 at 05:39
  • The return type of `ExecuteScalar` is `Object` because it can return any type of object that can be stored in a database. If you have `Option Strict On`, which you pretty much always should, you can't assign an `Object` reference to an `Integer` variable so you need to cast/convert from `Object` to `Integer`. Given that the actual object already is an `Integer`, but boxed inside an `Object` reference, it would actually be more appropriate to use `CInt` for a cast than `Convert.ToInt32` because you're not actually converting anything. – jmcilhinney Sep 17 '18 at 05:53
  • Oddest thing showing up in my Google browser above the regular VB Forums banner when following the ADO link. A list of PHP errors. Thought you would like to know, if it isn't just me. – Mary Sep 17 '18 at 10:19
  • Thanks for the explanation of the conversion jmc. Good info to know. – KGCole Sep 17 '18 at 14:00
  • lol...it wasn't just you Mary. But I didn't pay it much mind. Getting good info on all this. Problem solved at this point. – KGCole Sep 17 '18 at 14:04
  • @Mary, VBForums has been having some issues since a recent vBulletin upgrade. It's a known issue but they're having trouble sorting it. – jmcilhinney Sep 17 '18 at 14:30