I am fairly new to the ASP.NET, and now getting into the depths of database interaction!
This is probabaly a really easy solution, but i want a simple example of how to update a field in database? I have got a insert and select working as at the moment i have a page that shows the details on the page and then i want the user to change something
then click update and then it would update the database and refresh the page with the new data.
Thanks for your reply, i have changed it to my fields and connection now:
int
rowsAffected;
SqlConnection conn =
new
SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString);
SqlCommand cmd =
new
SqlCommand("UPDATE [Patient] SET [PatientRegisteredLocationName] = @PatientRegisteredLocationName
WHERE [PatientID] = 1", conn);
cmd.Parameters.AddWithValue("PatientRegisteredLocationName", PatientRegisteredLocation.SelectedValue);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
finally
{
if (conn !=
null) { conn.Close(); }
}
but it still does not update the database, any ideas? PatientRegisteredLocation is a dropdown list with the selectedvalue form the database, and this is the field i want to change
I set the field i wanted to change in the query without the parameter to 1 and it updated fine, the problem is that its not picking up my selected index properly!
I'm using this parameter, and PatientRegisteredLocation.SelectedValue is always 2, shouldn't it change when you select a different list item?
The SelectedValue property should reflect the current value at that moment. I'm not sure why you aren't seeing your currently selected value in the update. Your issue has nothing to do with the update itself.
int rowsAffected;
SqlConnection conn =
new
SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString);
SqlCommand cmd =
new
SqlCommand("UPDATE [Patient] SET [PatientRegisteredLocationCode] = @PatientRegisteredLocationCode
WHERE [PatientID] = 1", conn);
int test = PatientRegisteredLocation.SelectedIndex+1;
cmd.Parameters.AddWithValue("PatientRegisteredLocationCode", PatientRegisteredLocation.SelectedItem);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
finally
{
if (conn !=
null) { conn.Close(); }
}
}
This value then stays permanent the whole time, is this the correct way to select a certain list item from drop down list using the data from the database?
Meek2000
Member
2 Points
11 Posts
Updating a SQL database using c#.net
Aug 11, 2008 02:45 PM|LINK
I am fairly new to the ASP.NET, and now getting into the depths of database interaction!
This is probabaly a really easy solution, but i want a simple example of how to update a field in database? I have got a insert and select working as at the moment i have a page that shows the details on the page and then i want the user to change something then click update and then it would update the database and refresh the page with the new data.
Thanks
Mike
ecbruck
All-Star
98783 Points
9691 Posts
Moderator
Re: Updating a SQL database using c#.net
Aug 11, 2008 02:47 PM|LINK
Here's an example of an update method for you:
private bool Update(int shipperID, string companyName, string phone) { int rowsAffected = -1; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("UPDATE [Shippers] SET [CompanyName] = @CompanyName, [Phone] = @Phone WHERE ([ShipperID] = @ShipperID)", conn); cmd.Parameters.AddWithValue("CompanyName", companyName); cmd.Parameters.AddWithValue("Phone", phone); cmd.Parameters.AddWithValue("ShipperID", shipperID); try { conn.Open(); rowsAffected = cmd.ExecuteNonQuery(); } finally { if (conn != null) { conn.Close(); } } return rowsAffected.Equals(1); }Microsoft MVP - ASP.NET
Meek2000
Member
2 Points
11 Posts
Re: Updating a SQL database using c#.net
Aug 11, 2008 03:01 PM|LINK
Thanks for your reply, i have changed it to my fields and connection now:
int
rowsAffected; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString); SqlCommand cmd = new SqlCommand("UPDATE [Patient] SET [PatientRegisteredLocationName] = @PatientRegisteredLocationName WHERE [PatientID] = 1", conn); cmd.Parameters.AddWithValue("PatientRegisteredLocationName", PatientRegisteredLocation.SelectedValue); try{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
finally{
if (conn != null) { conn.Close(); }}
but it still does not update the database, any ideas? PatientRegisteredLocation is a dropdown list with the selectedvalue form the database, and this is the field i want to change
Thanks
ecbruck
All-Star
98783 Points
9691 Posts
Moderator
Re: Updating a SQL database using c#.net
Aug 11, 2008 03:22 PM|LINK
Is an error being throwm? You might want to add some code to the Catch portion of the Try/Catch. Other than that, I don't see anything obvious.
Microsoft MVP - ASP.NET
Meek2000
Member
2 Points
11 Posts
Re: Updating a SQL database using c#.net
Aug 11, 2008 03:31 PM|LINK
I set the field i wanted to change in the query without the parameter to 1 and it updated fine, the problem is that its not picking up my selected index properly!
I'm using this parameter, and PatientRegisteredLocation.SelectedValue is always 2, shouldn't it change when you select a different list item?
cmd.Parameters.AddWithValue("PatientRegisteredLocationCode", PatientRegisteredLocation.SelectedValue);
Cheers
ecbruck
All-Star
98783 Points
9691 Posts
Moderator
Re: Updating a SQL database using c#.net
Aug 11, 2008 03:42 PM|LINK
The SelectedValue property should reflect the current value at that moment. I'm not sure why you aren't seeing your currently selected value in the update. Your issue has nothing to do with the update itself.
Microsoft MVP - ASP.NET
Meek2000
Member
2 Points
11 Posts
Re: Updating a SQL database using c#.net
Aug 11, 2008 03:49 PM|LINK
I think it might be because when i load the page i am telling the drop down list to select the value from the database here is my whole page's code:
protected void Page_Load(object sender, EventArgs e){
string connectionString = ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ToString(); SqlConnection mySqlConnection = new SqlConnection(connectionString); string cmd = "SELECT * FROM Patient WHERE PatientID = 1";SqlCommand mySqlCommand = mySqlConnection.CreateCommand();mySqlCommand.CommandText = cmd;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();mySqlConnection.Open();
string dataTableName = "Patient";mySqlDataAdapter.Fill(myDataSet, dataTableName);
DataTable myDataTable = myDataSet.Tables[dataTableName]; foreach (DataRow myDataRow in myDataTable.Rows){
PatientName.Text = (myDataRow["PatientName"]).ToString();PatientRegisteredLocation.Items.FindByValue((myDataRow["PatientRegisteredLocationCode"]).ToString()).Selected = true;}
mySqlConnection.Close();
}
protected void Button1_Click(object sender, EventArgs e){
int rowsAffected; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString); SqlCommand cmd = new SqlCommand("UPDATE [Patient] SET [PatientRegisteredLocationCode] = @PatientRegisteredLocationCode WHERE [PatientID] = 1", conn); int test = PatientRegisteredLocation.SelectedIndex+1; cmd.Parameters.AddWithValue("PatientRegisteredLocationCode", PatientRegisteredLocation.SelectedItem); try{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
finally{
if (conn != null) { conn.Close(); }}
}
This value then stays permanent the whole time, is this the correct way to select a certain list item from drop down list using the data from the database?
Cheers
Meek2000
Member
2 Points
11 Posts
Re: Updating a SQL database using c#.net
Aug 11, 2008 03:56 PM|LINK
Problem solved, moved it out of Page_Load and moved it into Page_Init it now works perfectly, thank you very much for your help.
Mike