Question Textbox value - count is 0

[iseeyou]

Hi folks,

I have a form on a page called exports.aspx with a textbox called txtBarcode where I type in a barcode. On the page postback I have the SQL statement below to count how many rows in the DB that has the Barcode.

A short version of my codebehind is:

---------------------------------------------------------------------------------
Dim actualStockQty As Integer 'A barcode can contain both numbers and letters
.
.
.
Using myConnection2 As New SqlConnection(ConnectionString)
Dim getQtyinstock As New SqlCommand()
getQtyinstock.Connection = myConnection
getQtyinstock.CommandText = "SELECT COUNT([ID]) AS actualStockQty FROM [imports] WHERE [Barcode] = '" & txtBarcode.Text & "'"
myConnection.Open()
getQtyinstock.ExecuteNonQuery()
End Using

If actualStockQty > "0" Then
msgbox(actualStockQty)
Else
msgbox("Not in stock")
End If
.
.
.
----------------------------------------------------

The field in the SQL DB is a nvarchar(50).

I always get the value 0 for actualStockQty even though there is 15pcs in the database with the given Barcode.
I can´t see anything wrong in the code above and I have no more ideas of what´s wrong. Can it be something with the value from the textbox in the form that causes this?

Can someone please help me? Is my SQL command wrong or is it the way that I handle the typed in value in txtBarcode?

Thanks alot!

 

[Menthos]

Hi,

By the look of it you're using ExecuteNonQuery() when you should be using ExecuteScalar().

If you check the definition for ExecuteNonQuery(), you'll see that it 'returns the number of rows effected' (or words to that effect) - basically if you ran an update query using this, it would tell you the number of rows you altered.

ExecuteScalar() returns the first column of the first row of the query you run - your COUNT query will be returning one value, so you'll want to be using something along the lines of actualStockQty=getQtyinstock.ExecuteScalar()

Hope that helps

 

[iseeyou]

Grait, that works like a charm! You are the man, thanks alot!!