SQL variable returning 0

Question

When I run the following SQL statement in SQL Server Management Studio it returns a count of 2

SELECT COUNT(*) 
FROM Daisy_Copy2 
WHERE ChargeCode = '1';

But for some reason when I run the following VB.net code the result variable returns a 0 and doesn't identify that duplicate codes exist.

Dim result As Integer
Using cmdb = New SqlCommand("SELECT COUNT(*) FROM Daisy_Copy2 WHERE ChargeCode = '1'", conn)

Int(result = cmdb.ExecuteScalar())

If result > 1 Then
   MessageBox.Show("Duplicate Codes Exist!", "Billing", _
   MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
   MsgBox(result)
End If
End Using

Can anyone help me understand why?

Any help greatly appreciated.

Thank you for your replies, I have changed the code from ExecuteNonQuery to Int(result = cmdb.ExecuteScalar()), but it still returns 0 ? I am new to VB, so I may have the syntax wrong? Thanks — user3580480
– user3580480, Commented May 14, 2014 at 19:46

Morten Anderson · Accepted Answer · 2014-05-14 19:55:34Z

2

Instead of ExecuteNonQuery you should use ExecuteScalar

Dim result As Integer = CInt(cmd.ExecuteScalar())

edited May 14, 2014 at 19:55

answered May 14, 2014 at 19:17

Morten Anderson

2,29115 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joachim Isaksson · Accepted Answer · 2014-05-14 19:17:30Z

2

ExecuteNonQuery is normally used for updates or inserts that don't leave a result, so it returns an integer telling you how many rows were affected, not the result itself.

What you most likely are meaning to use is ExecuteScalar which returns the first column of the first row in the result set returned by the query, in this case the integer containing your count.

answered May 14, 2014 at 19:17

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Comments

Aqil · Accepted Answer · 2014-05-14 19:57:46Z

0

this is just a way you can use:

Dim Sqlda = New SqlDataAdapter("SELECT COUNT(*) AS tCount FROM Daisy_Copy2 WHERE ChargeCode=1", conn)
Dim sqlds = New DataSet



Sqlda.Fill(sqlds, "Daisy_Copy2")

Dim tblRow As DataRow
For Each tblRow In sqlds.Tables("Daisy_Copy2").Rows
    MsgBox(tblRow("tCount").ToString())
Next

use below link to read more about it

System.Data.SqlClient Namespace

Good luck

answered May 14, 2014 at 19:57

Aqil

3722 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL variable returning 0

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related