SqlDataReader not returning correct results 1

[Halliarse]

Hi

I'm writing vb using VS2012 and I'm accessing an SQL database to retrieve total stock items by Stock Code to create a movements report between the database and the 3rd party warehouse.

I first check to see if that stock item exists on the database and if it does, I then sum the records into TotalQuantity...that's where the problem arises! My test database only has 4 records on it and the record that it falls over with is definitely on the database and does have a stock value!

My code is as follows:

cmd.CommandText = "Select * from Stock_Items WHERE Stock_Code = '" & Stock_Code & "'"
reader = cmd.ExecuteReader

If reader.HasRows Then
cmd.CommandText = "SELECT SUM(Stock_Quantity) as TotalQuantity from Stock_Items WHERE Stock_Code = '" & Stock_Code & "'"
reader = cmd.ExecuteReader
[highlight #CC0000]SQL_Stock_Quantity = reader.Item("TotalQuantity")[/highlight]
Invalid_Record = False
Else
Invalid_Record = True
End If
End If

The line highlighted returns an error stating that there are no rows...how can that be when I am validating that rows exist in the first selection command?

Any help to resolve and ease my frustration would be well received!

Thanks

Steve

 

[ChrisHirst]

.how can that be when I am validating that rows exist in the first selection command?

Because you reset "header" in the line above that one.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[Halliarse]

Hi Chris

Thanks for responding.

Surely that shouldn't make a difference? Are you suggesting that by just removing the line above the highlighted line it will work?

Thanks

Steve

 

[ChrisHirst]

If you redefine a value AFTER testing it, the test is no longer valid.

You wouldn't expect the following

if x <> 0 then
   x = 0
   y = 2/x
end if

To NOT return a divide by zero error because x was NOT equal to 0 when tested.

You are doing a similar thing, just because your first query returns at least ONE record does not mean that your second query does.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[MarkSweetland]

If the Stock_Quantity column contains NULL the Sum aggregate will ignore the rows. The "Select *" will return the row(s) by applying the where clause and returning the results, whereas NULLs are eliminated by the SUM() function. Although in scalar arithmetic NULL added to anything equals NULL.




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[jebenson]

Try this for your SQL:

"SELECT SUM(Iif(IsNull(Stock_Quantity,0)=0,0,Stock_Quantity)) as TotalQuantity from Stock_Items WHERE Stock_Code = '" & Stock_Code & "'"

If any of your stock values are NULL, they will be replaced by 0.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Halliarse]

Hi Jebensen

My code now reads as such:

cmd.CommandText = "Select * from Stock_Items WHERE Stock_Code = '" & Stock_Code & "'"

reader = cmd.ExecuteReader

If reader.HasRows Then
reader.Close()
cmd.CommandText = "SELECT SUM(Iif(IsNull(Stock_Quantity,0)=0,0,Stock_Quantity)) as TotalQuantity from Stock_Items WHERE Stock_Code = '" & Stock_Code & "'"

reader = cmd.ExecuteReader

SQL_Stock_Quantity = reader.Item("TotalQuantity")
Invalid_Record = False
Else
Invalid_Record = True
End If

The first command / read is used to determine if records exist, the second set collates the total stock, however, when stock is found, the code falls over on the second read with 'Incorrect syntax near =', again which makes no sense to me as the initial reader command appears to work!

Am I doing something very basically wrong?

Thanks

Steve

reader.Close()

 

[ChrisHirst]

Why are you even making two queries?

Make the query you want results for, if that query returns results just use the results it returned, why waste processing time with TWO queries when you only want results from the second one?

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[Halliarse]

Chris

I made 2 queries because I do not understand why the first returns a result and the second doesn't...based on the data, they should both return a single row!

I have changed the code as you suggest to 1 query and the code is as follows;

cmd.CommandText = "SELECT SUM(Stock_Quantity) as TotalQuantity from Stock_Items WHERE Stock_Code = '" & Stock_Code & "'"
reader = cmd.ExecuteReader

if reader.HasRows then
SQL_Stock_Quantity = reader.item("TotalQuantity")
Invalid_Record = False
Else
Invalid_Record = True
End If

The first record that is checked on the database table, definitely does not exist but seems to satisfy the HasRows test. I have run the SQL query manually within the Server Management Studio and it does in fact return TotalQuantity as NULL, however, when the SQL_Stock_Quantity line of code is run, it informs me that an Invalid attempt to read when no data is present.

Tell me what is wrong here please because I like you, did originally think that a 1 query statement should work!

Kind regards

Steve

 

[jebenson]

Try this:

cmd.CommandText = "SELECT SUM(Stock_Quantity) as TotalQuantity from Stock_Items WHERE Stock_Code = '" & Stock_Code & "'"

If Not IsDbNull(cmd.ExecuteScalar) Then
SQL_Stock_Quantity = cmd.ExecuteScalar
End If



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[ChrisHirst]

Your second query returns a single value as a variable, whereas your first query will return a full recordset of ALL columns in the row/record that matches the criteria and that is probaly the difference.


Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[Amesville]

For the sake of argument, I get the point about one query not necessarily validating the results from the next. But assuming his data has values in all four records for Stock_Quantity, shouldn't it still work regardless? I'm having a hard time understanding why it does not.

 

[ChrisHirst]

Unfortunately we cannot and should not assume that the column does have valid numeric values for all of the rows, if only ONE row/record contains a NUL the SUM(column) query will return a NUL so the variable will not be a "row" and therefore the HasRows property will be false.



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum