Insert/Update in asp.net and SQL SERVER 2008

Question

I am trying to insert a record if requested prodName doesnot exist in database. If it exists I want to update the value of quantity attribute. I have used the following it neither inserts nor Updates any record. I get following exception:

ExecuteScalar requires an open and available Connection. The connection's current state is closed

This is the code

public static void manageStock(CompanyStock stock)
    {
        ///// Check if record exists/////////
        cmd = new SqlCommand("select count(*) from tblStock where prodName=@prodName", con);
        cmd.Parameters.AddWithValue("@prodName", stock.prodName);
        con.Open();
        Int32 count = (Int32)cmd.ExecuteScalar(); //returns null if doesnt exist
        con.Close();

        if (count > 0)
        {
            cmd = new SqlCommand("update tblStock set quantity = @quantity where prodName=@prodName", con);
            cmd.Parameters.AddWithValue("@prodName", stock.prodName);
            cmd.Parameters.AddWithValue("@quantity", stock.quantity);
        }
        else
        {
            cmd = new SqlCommand("insert into tblStock(prodName,quantity) values (@prodName, @quantity)", con);
            cmd.Parameters.AddWithValue("@prodName",stock.prodName);
            cmd.Parameters.AddWithValue("@quantity",stock.quantity);
        }

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }

        finally
        {
            con.Close();
        }
    }
}

Edited I edited my code. It works fine now. I had to open my connection before executing ExecuteScalar But I want to know the standard way of writing this opening and closing stuff. It looks kind of haphazard. How can I improve this?

I believe you need to open the connection before your execute the first command object. I am not sure how your code executes. If this is indeed the code, it will always execute the INSERT into tblStock command. — abhi, Apr 09 '14 at 17:40
What is wrong with your code is that you do not handle your exceptions so you cannot see error messages that might tell you what the problem really is. — Pleun, Apr 09 '14 at 17:43
@Pleun With no `catch`, though, won't they just bubble up? Though it sounds like there's probably something further on the stack swallowing somewhere. — Andrew Barber, Apr 09 '14 at 17:51

score 0 · Answer 1 · answered Apr 09 '14 at 17:38

0

You can use Convert.ToInt32() method for converting the result into integer value.

if the value is null it converts it into 0.

Try This:

int count = Convert.ToInt32(cmd.ExecuteScalar());

answered Apr 09 '14 at 17:38

Sudhakar Tillapudi

25,935
5
37
67

score 0 · Answer 2 · edited Apr 09 '14 at 17:50

0

Consider using MERGE clause in sql-server. Here is a good Microsoft article you can use.

edited Apr 09 '14 at 17:50

abhi

3,082
6
47
73

answered Apr 09 '14 at 17:41

Abhi

824
9
8

score 0 · Answer 3 · answered Apr 09 '14 at 17:59

0

What does it do when you step through the code?

In some SQL collations (Latin1_General_BIN for example), variables are case sensitive. In your first select statement you have @ProdName in your query and @prodName in your parameters collection. If you have a case sensitive collation, you're never getting past this part. Right-click on the database in Management Studio and click Properties to find the collation.

answered Apr 09 '14 at 17:59

InbetweenWeekends

1,405
3
23
28

After re-reading your edit, on your SELECT line, you're supplying it with a `con` connection, but I don't see that it was opened. – InbetweenWeekends Apr 09 '14 at 18:16
I opened it. Problem is solved now but it doesnot look neat. Please check – Insafian Apr 09 '14 at 18:20
That's the way it works. Maybe look into writing a database wrapper http://stackoverflow.com/questions/3553846/c-sharp-database-wrapper-design – InbetweenWeekends Apr 09 '14 at 18:25

makemoney2010 · Answer 4 · 2014-04-09T18:33:59.193

Error say that there's no connection.May u check first of all that issue so

Check connection and if is not null and exist at this point check it con.State = Open or any other value. I connection state is closed open it.But first of all where is connections declaration ? i don't see it in your code.

TRY THIS :

//USING THE STATEMNET USING IT WILL TAKE CARE TO DISPOSE CONNECTION AND PLACE TRY CATCH WITHIN PROCS

{
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings("connectionString"))) {
    if (cnn.State == System.Data.ConnectionState.Closed)
        cnn.Open();
    using (SqlCommand cmd = new SqlCommand()) {
        try {
            cmd.Connection = cnn;
            cmd.CommandText = "YOUR SQL STATEMENT";
            int I = Convert.ToInt32(cmd.ExecuteNonQuery);

            if (I > 0)
            {
                cmd.CommandText = "YOUR SQL STATEMENT";
            //ADDITIONAL PARAMTERES
            } 
            else
            {
                cmd.CommandText = "YOUR SQL STATEMENT";
                //ADDITIONAL PARAMETERS
            }
            cmd.ExecuteNonQuery();
            } 
              catch (Exception ex) 
            {
            Response.Write(ex.Message);
            }
        }
    }
}

take a look to my solution it was more simple and safe.Never left connection open remeber :). It it match your goal make this as answers — makemoney2010, Apr 09 '14 at 18:35

score 0 · Answer 5 · edited Oct 09 '16 at 10:27

You can try this code. First write a stored procedure:

CREATE PROCEDURE sprocquanupdateinsert
    @prodName nvarchar(250),
    @quantity int
AS
BEGIN
    UPDATE tblStock 
    SET quantity = @quantity 
    WHERE prodName = @prodName

    IF @@ROWCOUNT = 0
        INSERT INTO tblStock(prodName, quantity) 
        VALUES (@prodName, @quantity)
END
GO

Then in code behind you can use this

using (conn)
{
    SqlCommand cmd = new SqlCommand("sprocquanupdateinsert", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@prodName", stock.prodName);
    cmd.Parameters.AddWithValue("@quantity", stock.quantity);

    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

Insert/Update in asp.net and SQL SERVER 2008

5 Answers5