Bound Dropdown List not populating into sql table on save

I have two dropdownlists that are bound to two columns in a sql table1. When I select an item within the dropdownlists and click my save button, the text in the dropdownlist does not appear in the sql table2.

The textbox data as well as the other dropdownlists that are not bound to a sql table, are saving into my sql table2.

I am using a stored procedure in sql to save the data from my webform to sql.

Stored Procedure:

USE [OnCallWeb]
GO
/****** Object:  StoredProcedure [dbo].[DispatchRolesCreateOrUpdate]    Script Date: 6/8/2023 10:10:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[DispatchRolesCreateOrUpdate]
@ID int,
@Name nvarchar(max),
@Position nvarchar(max),
@Roles nvarchar(max),
@Status nvarchar(max),
@DispatcherCovering nvarchar(max)
AS
BEGIN
IF(@ID=0)
BEGIN
INSERT INTO Dispatcher_Roles(Name,Position,Roles,Status,DispatcherCovering)
VALUES(@Name,@Position,@Roles,@Status,@DispatcherCovering)
END
ELSE
BEGIN
UPDATE Dispatcher_Roles
SET
Name = @Name,
Position = @Position,
Roles = @Roles,
Status = @Status,
DispatcherCovering = @DispatcherCovering
Where ID= @ID
END
END

My CS code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace OnCallWeb.Dispatch_Policies
{
public partial class WorkstationRoles : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(@"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Integrated Security=true;");
protected void Page_Load(object sender, EventArgs e)
{
string mainconn = ConfigurationManager.ConnectionStrings["Reporting_SystemConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
string sqlquery = "select [ID],[Emp_ID] + '  ' + [Emp_Name] as FullName from [dbo].[Personnel] where [JurisdictionID] = 99";
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlconn.Open();
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlcomm);
sda.Fill(ds);
DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "FullName";
DropDownList1.DataValueField = "ID";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, new ListItem("", "0"));
DropDownList4.DataSource = ds;
DropDownList4.DataTextField = "FullName";
DropDownList4.DataValueField = "ID";
DropDownList4.DataBind();
DropDownList4.Items.Insert(0, new ListItem("", "0"));
sqlconn.Close();
if (!IsPostBack)
{
btnDelete.Enabled = false;
}
}
protected void btnClear_Click(object sender, EventArgs e)
{
Clear();
}
public void Clear()
{
ID.Value = "";
DropDownList1.Items.Clear();
DropDownList2.Items.Clear();
TextBox1.Text = "";
DropDownList3.Items.Clear();
DropDownList4.Items.Clear();
lblSuccessMessage.Text = lblErrorMessage.Text = "";
btnSave.Text = "Save";
btnDelete.Enabled = false;
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (sqlcon.State == ConnectionState.Closed)
sqlcon.Open();
SqlCommand sqlCmd = new SqlCommand("DispatchRolesCreateOrUpdate",sqlcon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@ID",(ID.Value==""?0:Convert.ToInt32(ID.Value)));
sqlCmd.Parameters.AddWithValue("@Name",DropDownList1.SelectedItem.Text);
sqlCmd.Parameters.AddWithValue("@Position",DropDownList2.Text.Trim());
sqlCmd.Parameters.AddWithValue("@Roles",TextBox1.Text.Trim());
sqlCmd.Parameters.AddWithValue("@Status",DropDownList3.Text.Trim());
sqlCmd.Parameters.AddWithValue("@DispatcherCovering",DropDownList4.SelectedItem.Text);
sqlCmd.ExecuteNonQuery();
sqlcon.Close();
Clear();
if (ID.Value == "")
lblSuccessMessage.Text = "Saved Successfully";
else
lblSuccessMessage.Text = "Updated Successfully";
}
}
}

I have tried changing the addwithvalues SelectedItem.Text to SelectedItem.Value which did not resolve the issue.

The simple answer?

You have to place your FIRST time page load code, the code that loads + set's up the dropdown lists inside of the !IsPostBack.

Remember, on any button click, or even controls with autopostback = true, then a page post back occurs.

So, in your case, page load runs, dropdowns are loaded up.

When a user clicks a button, page load will fire again, and then your button stub code runs.

However, since on page load, you re-load the dropdown controls again, then the user's choice will be lost.

So place your down down list "loading" code inside of the REAL first page load (inside of the !IsPostBack code stub, since then the dropdown lists will not be re-loaded each page post-back).

Thus, the user choices and values will not be over-write each time a button is clicked.

You should also go project->settings, and use the wizards to create your connection string.

Eg here:

So, with above knowledge in mind, and my suggesting to remove the connection string stuff out of code (since it will be rather difficult to change all those hard coded connection strings in code), then I suggest this code:

And I have a global (static) class with my helper routines, they are this:

    public static DataTable MyRstP(SqlCommand cmdSQL)
{
DataTable rstData = new DataTable();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (cmdSQL)
{
cmdSQL.Connection = conn;
conn.Open();
rstData.Load(cmdSQL.ExecuteReader());
}
}
return rstData;
}
public static void MyRstE(SqlCommand cmdSQL)
{
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (cmdSQL)
{
cmdSQL.Connection = conn;
conn.Open();
cmdSQL.ExecuteNonQuery();
}
}
}

So, I have ONE location for the connection and even the use of the SQL command object. (not only is this nice and and handy while coding, it also means you can easy change the connection string at deployment time).

So, now your code becomes this:

    protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadData();
btnDelete.Enabled = false;
}
}
void LoadData()
{
string sqlquery 
= @"select [ID],[Emp_ID] + '  ' + [Emp_Name] as FullName from 
[dbo].[Personnel] where [JurisdictionID] = 99";
SqlCommand cmdSQL = new SqlCommand(sqlquery);
DataTable dt = General.MyRstP(cmdSQL);
DropDownList1.DataSource = dt;
DropDownList1.DataTextField = "FullName";
DropDownList1.DataValueField = "ID";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, new ListItem("", "0"));
DropDownList4.DataSource = dt;
DropDownList4.DataTextField = "FullName";
DropDownList4.DataValueField = "ID";
DropDownList4.DataBind();
DropDownList4.Items.Insert(0, new ListItem("", "0"));
}
protected void btnSave_Click(object sender, EventArgs e)
{
SqlCommand sqlCmd = new SqlCommand("DispatchRolesCreateOrUpdate");
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add("@ID", SqlDbType.Int).Value = (ID.Value == "" ? 0 : Convert.ToInt32(ID.Value));
sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value =  DropDownList1.SelectedItem.Text;
sqlCmd.Parameters.Add("@Position", SqlDbType.NVarChar).Value =  DropDownList2.Text.Trim();
sqlCmd.Parameters.Add("@Roles", SqlDbType.NVarChar).Value = TextBox1.Text;  
sqlCmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value =  DropDownList3.Text;
sqlCmd.Parameters.Add("@DispatcherCovering",SqlDbType.NVarChar).Value =  DropDownList4.SelectedItem.Text);
General.MyRstE(sqlCmd);
if (ID.Value == "")
lblSuccessMessage.Text = "Saved Successfully";
else
lblSuccessMessage.Text = "Updated Successfully";
}

So, check the !IsPostBack. You can only load ONE time and on FIRST page load the dropdown controls. If you re-load dropdown's each time as you have, then the user's selections will be lost on each post-back.

Edit: Different connection strings are required.

Since you have to deal with MORE then one connection string, then I suggest this code for the 2 helper routines:

    public static DataTable MyRstP(SqlCommand cmdSQL, string sCon = "")
{
DataTable rstData = new DataTable();
if (sCon == "")
sCon = Properties.Settings.Default.TEST4;
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (cmdSQL)
{
cmdSQL.Connection = conn;
conn.Open();
rstData.Load(cmdSQL.ExecuteReader());
}
}
return rstData;
}
public static void MyRstE(SqlCommand cmdSQL, string sCon = "")
{
if (sCon == "")
sCon = Properties.Settings.Default.TEST4;
using (SqlConnection conn = new SqlConnection(sCon))
{
using (cmdSQL)
{
cmdSQL.Connection = conn;
conn.Open();
cmdSQL.ExecuteNonQuery();
}
}
}

So, now, for 99% of your code, then you can/have/use/enjoy the main overall connection for the given database.

HOWEVER, now, for cases in which you want to use a different connection, then you can go

  MyRstE(cmdSQL,Properties.Settings.Default.MyOtherDB)