How to check DB table values in SQL stored procedure with C#

Question

May I know how to check the values in a database table when there is no exist data?

I wrote this SQL script and C# code to test the condition statement. But it doesn't work.

In SQL stored procedures I have tried Select * from Table and check if the condition is 0

DECLARE @CheckFindQTY FLOAT 

SET @CheckFindQTY = (SELECT INV_QTY FROM OTH_INV_QTY_LOC 
                     WHERE INV_ID = @INV_ID 
                       AND INV_LOCATION = @INV_LOCATION  
                       AND INV_QTY = @INV_QTY)

IF @CheckFindQTY = 0
BEGIN
    SELECT @RecordFound = 3
END

And also I tried checking if the condition is NULL

DECLARE @CheckFindQTY FLOAT 

SET @CheckFindQTY = (SELECT INV_QTY 
                     FROM OTH_INV_QTY_LOC 
                     WHERE INV_ID = @INV_ID 
                       AND INV_LOCATION = @INV_LOCATION  
                       AND INV_QTY = @INV_QTY)

IF @CheckFindQTY = NULL
BEGIN
     SELECT @RecordFound = 3
END

Then I query the condition in C# if the condition is == 3 then show alert message

int recordFound = Convert.ToInt32(_cmd.Parameters["@RecordFound"].Value);

if (recordFound == 3)
{
   ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('There is not QTY in Location, please check again!!')", true);
}

Are you trying to check if a record with certain INV_ID, INV_LOCATION and INV_QTY exists? — System Down, Jan 03 '14 at 00:49
Take a look at `ExecuteScalar` http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx — Christian Phillips, Jan 03 '14 at 00:51
Yes, i want check the certain INV_ID, INV_LOCATION and INV_QTY exists,if there is empty then show alert message — Caulson Chua, Jan 03 '14 at 01:00

score 2 · Answer 1 · answered Jan 03 '14 at 02:16

2

DECLARE @CheckFindQTY as FLOAT = NULL;
SELECT @CheckFindQTY = INV_QTY FROM OTH_INV_QTY_LOC WHERE INV_ID=@INV_ID AND INV_LOCATION = @INV_LOCATION AND INV_QTY=@INV_QTY;

IF @CheckFindQTY is NULL
  SELECT @RecordFound = 3

Note that the test for a NULL value is IS NULL, not = NULL.

answered Jan 03 '14 at 02:16

HABO

15,314
5
39
57

I tried it, but it doesn't work, it didn't pop up the alert message. Is it need to use execute scalar ? – Caulson Chua Jan 03 '14 at 02:43
@CaulsonChua - Your SQL doesn't return a value. You could use `select @CheckFindQty as CheckFindQty, @RecordFound as RecordFound` to return a single row with two columns. `ExecuteScalar` would give you the first value from the first row rather than the complete recordset. – HABO Jan 03 '14 at 04:24
Dear All, thanks for advise, i found out the solution use execute scalar. – Caulson Chua Jan 03 '14 at 07:25

How to check DB table values in SQL stored procedure with C#

1 Answers1