how to retrieve data from database into textbox and save the edited data from the same textbox to database

Question

i m working on a web application that stores user information, i want to get the userdetails from the database into textboxes where i can make changes to the data and use a button to store the data from the same text boxes to the same table of the database.it is like updating/ editing user profile so far i am able to get the data into textboxes but i am unable to update the data. the page submits the data but there is no change in the data base

the html & code i am using is:

<div id="pagebody" style="width: 80%; height: 120%; margin: auto;">
    <div id="personalinfodiv" class="try" align="center" style="background-color: white; width: 80%; border: 1px solid gray; margin: auto">
        <h3 align="center" >Personal information</h3>
        <table>
            <tr><td><asp:Label ID="fname"  runat="server" Text="First Name  :" Font-Bold="true" ForeColor="GrayText"></asp:Label></td>  <td><asp:TextBox cssClass="textbox1"  ID="firstnametext" runat="server"></asp:TextBox></td></tr>
            <tr><td><asp:Label ID="lname" runat="server" Text="Last Name  :" Font-Bold="true" ForeColor="GrayText"></asp:Label></td>  <td><asp:TextBox cssClass="textbox1" ID="lastnametext" runat="server"></asp:TextBox></td></tr>
            <tr><td><asp:Label ID="gender" runat="server" Text="Gender  :" Font-Bold="true" ForeColor="GrayText"></asp:Label></td>  <td><asp:DropDownList cssClass="textbox1" ID="gendertext" runat="server" DataSourceID="SqlDataSource1" DataTextField="Gender" DataValueField="Gender"><asp:ListItem Text="Male" Value="1"></asp:ListItem><asp:ListItem Text="Female" Value="2"></asp:ListItem></asp:DropDownList>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Koshur %>" SelectCommand="SELECT [Gender] FROM [userdetails] WHERE ([Username] = @Username)">
                    <SelectParameters>
                        <asp:QueryStringParameter Name="Username" QueryStringField="user" Type="String" />
                    </SelectParameters>
                </asp:SqlDataSource>
                </td></tr>
            <tr><td><asp:Label ID="dob" runat="server" Text="Date of Birth  :" Font-Bold="true" ForeColor="GrayText"></asp:Label></td>  <td><asp:TextBox cssClass="textbox1" ID="dobtext" runat="server"></asp:TextBox>
                </td></tr>
            <tr><td><asp:Label ID="Contactno" runat="server" Text="Contact No :" Font-Bold="true" ForeColor="GrayText"></asp:Label></td>  <td><asp:TextBox cssClass="textbox1" ID="contacttext" runat="server"></asp:TextBox>

                </td></tr>
        </table>

C# for data retrieval :

string CSs = ConfigurationManager.ConnectionStrings["Koshur"].ConnectionString;
using (SqlConnection conn = new SqlConnection(CSs))
{
    string query = "select * from userdetails where Username='" + HttpContext.Current.User.Identity.Name.ToString() + "';";
    SqlCommand cmd = new SqlCommand(query, conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds, "userdetails");
    firstnametext.Text=ds.Tables["userdetails"].Rows[0]["Firstname"].ToString();
    lastnametext.Text = ds.Tables["userdetails"].Rows[0]["Lastname"].ToString();
    dobtext.Text = ds.Tables["userdetails"].Rows[0]["Dateofbirth"].ToString();
    contacttext.Text = ds.Tables["userdetails"].Rows[0]["ContactNO"].ToString();
}

c# to update the data into table and i m using a stored procedure for this

protected void savecontinue_Click(object sender, EventArgs e)
{
   // Response.Redirect("test.aspx?q=" + firstnametext.Text +"&" + lastnametext.Text);
   string ct = ConfigurationManager.ConnectionStrings["Koshur"].ConnectionString;
   using (SqlConnection con = new SqlConnection(ct))
   {
       SqlCommand cmnd = new SqlCommand("spupdateuserprofiledetails", con);
       cmnd.CommandType = CommandType.StoredProcedure;

       SqlParameter first = new SqlParameter("@Firstname", firstnametext.Text);
       SqlParameter last =new SqlParameter("@Lastname", lastnametext.Text);

       SqlParameter dobb=new SqlParameter("@Dateofbirth", dobtext.Text);
       SqlParameter connt=new SqlParameter("@ContactNo", contacttext.Text);
       SqlParameter userna = new SqlParameter("@Username", HttpContext.Current.User.Identity.Name.ToString());

       cmnd.Parameters.Add(first);
       cmnd.Parameters.Add(last);
       cmnd.Parameters.Add(dobb);
       cmnd.Parameters.Add(connt);
       cmnd.Parameters.Add(userna);

       con.Open();
       cmnd.ExecuteNonQuery();
   }
}
}

this is my stored procedure

create proc spupdateuserprofiledetails
@Firstname varchar(100),
@Lastname Varchar(100),
@Dateofbirth varchar(100),
@ContactNo varchar(100),
@Username varchar(100)
as
begin
update Userdetails
set Firstname=@Firstname,Lastname=@Lastname,Dateofbirth=@Dateofbirth,ContactNO=@ContactNo 
where Username=@Username

end

Answer 1

It took quite a lot of time but finally I was able to get the answer. It was a just a lucky guess.

I was able to solve my problem by wrapping up data retrieval code:

if (!IsPostBack)
{
    string CSs = ConfigurationManager.ConnectionStrings["Koshur"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(CSs))
    {
        string query = "select * from userdetails where Username='" + 
            HttpContext.Current.User.Identity.Name.ToString() + "';";
        SqlCommand cmd = new SqlCommand(query, conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "userdetails");
        firstnametext.Text = ds.Tables["userdetails"].Rows[0]["Firstname"].ToString();
        lastnametext.Text = ds.Tables["userdetails"].Rows[0]["Lastname"].ToString();
        dobtext.Text = ds.Tables["userdetails"].Rows[0]["Dateofbirth"].ToString();
        contacttext.Text = ds.Tables["userdetails"].Rows[0]["ContactNO"].ToString();
    }
}

This worked fine. I am able to retrieve the data perfectly and also able to save it to the database using the same textboxes. that was my basic requirement. I DID NOT CHANGE ANY CODE FOR SAVING THE DATA INTO THE DATABASE (i.e the on save button event).

Answer 2

I think it has something to do with the way you add parameter for the SqlCommand collection. Or it might happen that the parameter value (Username) you pass to the stored procedure doesn't match with the database value you have specified in the condition. May be check the value of both @Username and the value in the database for the UserName column doesn't contain any white spaces.

Try the below code and see if it helps. You can use AddWithValue which does the implicit conversion of types and it replaces the SqlParameterCollection.Add method. Here is the link where you can study the different between the two.

using (SqlConnection con = new SqlConnection(ct))
{
   using (SqlCommand cmnd = new SqlCommand("spupdateuserprofiledetails", con))
   { 
      cmnd.CommandType = CommandType.StoredProcedure;

      string userName  = HttpContext.Current.User.Identity.Name.ToString();
      cmnd.Parameters.AddWithValue("@FirstName", firstnametext.Text));
      cmnd.Parameters.AddWithValue("@LastName", lastnametext.Text));
      cmnd.Parameters.AddWithValue("@Dateofbirth", dobtext.Text));
      cmnd.Parameters.AddWithValue("@ContactNo", contacttext.Text));
      cmnd.Parameters.AddWithValue("@Username", userName));

      // Or If you are planning to use the `Add` method instead of the `AddWithValues` then make sure you explicitly specify the type of the parameter you pass to the stored procedure.
      // cmnd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = firstnametext.Text;
      // cmnd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lastnametext.Text;
      // and so on

      con.Open();
      cmnd.ExecuteNonQuery();
   }
}