CODEWITHSUNDEEP
c#sqlsql-server
brianforan
brianforan

Reputation: 182

Two ExecuteScalar then query

I'm looking for a way to check if a customer ID exists in the Customer table, and if it exists in the table being queried.

If it doesn't exist in the Customer table or it already exists in the table being queried (or it is in Customer but also already exists in table in question) then I don't want it to insert the data.

I thought that maybe I could do if..if code - more code - if..if code - else, but that didn't work. Here is what I mean:

cmd.CommandText = "SELECT COUNT(*) FROM Customer WHERE customer_id = @cid";
    cmd.Connection = conn;

    conn.Open();
    int count = (int)cmd.ExecuteScalar();
    conn.Close();

    if (count < 1)
    {
        System.Web.HttpContext.Current.Response.Write("<script>alert('Customer ID does not exist and will not be submitted')</script>");
        tb0.Focus();
    }

    cmd.CommandText = "SELECT COUNT(*) FROM table_name WHERE customer_id = @cid";
    cmd.Connection = conn;

    conn.Open();
    int count2 = (int)cmd.ExecuteScalar();
    conn.Close();
    if(count2 > 0)
    {
        System.Web.HttpContext.Current.Response.Write("<script>alert('Customer ID already exists in table_name and will not be submitted')</script>");
        tb0.Focus();
    }


    else
    {
        cmd.CommandText = "INSERT INTO table_name VALUES(@cid, @val1, @val2, @val3, @val4, @val5)";
        cmd.Connection = conn;

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

Unless I comment one out then the second one will work and the first I guess gets ignored? I'm not really sure but they both work if the other is commented out

Errors from answer:

Incorrect syntax near the keyword 'THEN'. Incorrect syntax near the keyword 'ELSE'. Incorrect syntax near the keyword 'THEN'. Incorrect syntax near the keyword 'ELSE'.

@"IF NOT EXISTS (SELECT * FROM Customer WHERE customer_id = @cid) THEN BEGIN
    SELECT 'NO_CUSTOMER' as Result 
END ELSE IF EXISTS (SELECT * FROM table_name WHERE customer_id = @cid) THEN BEGIN
    SELECT 'ALREADY_RECORD' as Result
END ELSE BEGIN
    INSERT INTO table_name VALUES(@cid, @val1, @val2, @val3, @val4, @val5);
    SELECT 'SUCCESS' AS Result;
END";

Upvotes: 0

Views: 106

Answers (1)

Dave Markle
Dave Markle

Reputation: 97701

Why not try something a bit simpler, like this?

    cmd = conn.CreateCommand();
cmd.CommandText =
  @"IF NOT EXISTS (SELECT * FROM Customer WHERE customer_id = @cid) THEN BEGIN
        SELECT 'NO_CUSTOMER' as Result 
    END ELSE IF EXISTS (SELECT * FROM table_name WHERE customer_id = @cid) THEN BEGIN
        SELECT 'ALREADY_RECORD' as Result
    END ELSE BEGIN
        INSERT INTO table_name VALUES(@cid, @val1, @val2, @val3, @val4, @val5);
        SELECT 'SUCCESS' AS Result;
    END";

cmd.Parameters.AddWithValue(@cid, ...);        
cmd.Parameters.AddWithValue(@val1, ...);
cmd.Parameters.AddWithValue(@val2, ...);
...
switch((string)cmd.ExecuteScalar()) {
    case "NO_CUSTOMER":
        System.Web.HttpContext.Current.Response.Write("<script>alert('Customer ID does not exist and will not be submitted')</script>");
    tb0.Focus();
        break;
        case "ALREADY_RECORD":
        System.Web.HttpContext.Current.Response.Write("<script>alert('Customer ID already exists in table_name and will not be submitted')</script>");
    tb0.Focus();
        break;
}

Upvotes: 2

Related Questions