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

In SQL Server, don't use the prefix "sp" for stored procs: the database engine looks for those in the system database first adding milliseconds to your processing time — Ruskin
– Ruskin, Commented Nov 4, 2014 at 13:28
If you have the full version of SQL Server, switch on Profiler (from tools) to see if the sp is being called — Ruskin
– Ruskin, Commented Nov 4, 2014 at 13:30
storedprocedure works fine, when i execute it in in sql server it updates the data, but in the webapplicatiton there is no updation,i get the previous data back — hannad rehman
– hannad rehman, Commented Nov 4, 2014 at 13:49

hannad rehman · Accepted Answer · 2014-12-24 16:27:21Z

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).

edited Dec 24, 2014 at 16:27

answered Dec 24, 2014 at 15:45

hannad rehman

4,4315 gold badges39 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

gunr2171 Over a year ago

I removed the additional answer from your post. If you have a new question, please post a new question. If this post is not a true solution to your problem, don't post this as an answer and instead edit your original question.

BCdotWEB Over a year ago

You should "encapsulate" SqlCommand and SqlDataAdapter in a using block; example.

hannad rehman Over a year ago

this is the answer for my problem, but i wanted to know what just happend with (!IsPostBack).i want an expert explanation. i mentioned it that it was a lucky guess. please answer. dont criticise

John Saunders Over a year ago

So, notice that the reason you didn't get this answer over a month ago is that you didn't show the context of your data retrieval code. If you had shown that it was inside of Page_Load, or called from Page_Load, you would have received immediate answers about if (!IsPostBack).

hannad rehman Over a year ago

i know. it is my fault actually. i should have mentioned it earlier.. can you explain what is the role of if (!IsPostBack) on data storage into database? it is confusing because pageload event is usend only for data retrieval

Community · Accepted Answer · 2017-05-23 12:27:17Z

0

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();
   }
}

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Nov 4, 2014 at 17:39

Dennis R

3,2471 gold badge21 silver badges24 bronze badges

2 Comments

hannad rehman Over a year ago

when i try to execute the sp in sql server it works fine.. the data is updated. but through the web page it dosent show any changes, also i have tried ADDWITHVALUE, it didnt work either. intitially i also thought that its not taking the username from "HttpContext.Current.User.Identity.Name.ToString());" i tried another method of finding wheather the data in textbox is realy updating or not for that i posted from the textbox to another page in its querystring, there also data is not changed..i have written the querystring code aswell in // section..

BCdotWEB Over a year ago

Please do not recommend using AddWithValue.

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related