Update status based on condition in ASP.Net using C# and VB.Net

Last Reply 7 months ago By dharmendr

Posted 7 months ago

I have dropdownlist which contains: Misc Secondary Visitors

Say,suppose user selected status Misc from the dropdownlist(combolist) and saved it. If he wanted to change the status from Misc to Seconday then it should change.But Suppose user selected status Secondary from dropdownlist and saved it. if he wanted to change the status from secondary to Misc then it should not be change.(As Secondary status has the higher precedence).

i tried the following query but its not working:

update cmp set cmp.status=case when cmp.status='Misc' then 'Secondary' else cmp.status end

But its not working.

You are viewing reply posted by: dharmendr 7 months ago.
Posted 7 months ago

Hi chetan,

You need to first check the status. If the status is not Secondary then update with Secondary.

Check the below example. You need to change where condition in the query for update the particular record as per your requirement.

HTML

<asp:TextBox runat="server" ID="txtId" />
<asp:DropDownList runat="server" ID="ddlVisitors">
    <asp:ListItem Values="" Text="Select" />
    <asp:ListItem Values="Misc" Text="Misc" />
    <asp:ListItem Values="Secondary" Text="Secondary" />
</asp:DropDownList>
<asp:Button Text="Save" OnClick="Save" runat="server" />

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void Save(object sender, EventArgs e)
{
    if (GetStatus(Convert.ToInt32(txtId.Text.Trim())).ToLower() != "secondary")
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("UPDATE cmp SET cmp.status= @Status WHERE Id = @Id", con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim());
                cmd.Parameters.AddWithValue("@Status", ddlVisitors.SelectedItem.Text.Trim());
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

private string GetStatus(int id)
{
    string status = "";
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT Status FROM cmp WHERE Id = @Id", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@Id", id);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                status = dr["Status"].ToString();
            }
            con.Close();
        }
    }

    return status;
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    If GetStatus(Convert.ToInt32(txtId.Text.Trim())).ToLower() <> "secondary" Then
        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
            Using cmd As SqlCommand = New SqlCommand("UPDATE cmp SET cmp.status= @Status WHERE Id = @Id", con)
                con.Open()
                cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim())
                cmd.Parameters.AddWithValue("@Status", ddlVisitors.SelectedItem.Text.Trim())
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End If
End Sub

Private Function GetStatus(ByVal id As Integer) As String
    Dim status As String = ""
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("SELECT Status FROM cmp WHERE Id = @Id", con)
            con.Open()
            cmd.Parameters.AddWithValue("@Id", id)
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            If dr.Read() Then
                status = dr("Status").ToString()
            End If
            con.Close()
        End Using
    End Using
    Return status
End Function