Click here to Skip to main content
15,886,873 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Friends,
sorry for a lengthy description.

I want to UPDATE Age in Sql server 2008 Table which is an Int datatype using C#, asp dot net 2010 by selecting Month/Day/Year in TextBox which has TextMode="Date"
Here is the .aspx
ASP
<asp:textbox id="txtDOB" textmode="Date" onchange="DateSelectionChanged()" runat="server">
<asp:textbox id="txtAge" runat="server" readonly="true">


Age is calculated using JavaScript and displayed the value in TextBox named txtAge

C#
function DateSelectionChanged() {
            var today = new Date();
            var dob = new Date(document.getElementById('<%=txtDOB.ClientID%>').value);
            var months = (today.getMonth() - dob.getMonth() + (12 * (today.getFullYear() - dob.getFullYear())));
            document.getElementById('<%=txtAge.ClientID%>').value = Math.round(months / 12);
        }

So far so good
I have a .aspx page which displays existing DOB,Age etc., from sql server table using c# as below

C#
protected void Page_Load(object sender, EventArgs e)
    {
	///some session code		
		
        if (!IsPostBack)
        {
			
            MultiView1.ActiveViewIndex = 0;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
            con.Open();
            SqlDataReader myReader = null;
            SqlCommand myCommand = new SqlCommand("select * from Student where stdname='" + Session["SessionStdname"] + "'", con);
            myReader = myCommand.ExecuteReader();

            while (myReader.Read())
            {
		.....
		.......
		.........
                txtstdDOB.Text = (myReader["DOB"].ToString());
                txtAge.Text = (myReader["Age"].ToString());
		....
		...
	    }
		
	    con.Close();
	}

Now I am updating these values in Sql Server Table using Stored Procedure as follows

SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[STUDENT_UPDATE]
@STDID AS INT,
...
....
.....
@DOB AS DATETIME,
@AGE AS INT,
...
....
...
AS
BEGIN
UPDATE STUDENT SET BIRTHDATE=@DOB,AGE=@AGE WHERE SID=@STDID
END
GO

Now the Button Click Event in code behind goes as

C#
protected void btnStudent_Click1(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
        SqlCommand sqlComm = new SqlCommand();
        sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
	sqlComm.CommandText = "STUDENT_UPDATE";
	.....
	......
	.....
	sqlComm.Parameters.Add("@DOB", txtDOB.Text);
	sqlComm.Parameters.Add("@Age", SqlDbType.VarChar).Value = txtAge.Text;
	.....
	......
	.....	

	sqlComm.Connection = con;
        try
        {
            con.Open();
            sqlComm.ExecuteNonQuery();
        }

        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
     }

Now MY Problem is the Value is Read properly (MyReader) and displayed in the txtAge with some old data I inserted directly into the table Age column for testing purpose, but is NOT UPDATED in the Table after changing DOB in txtDOB, everything else does. The table has Int datatype for AGE Column.
I dont know what is happening and how to convert to Int.
I have to convert the txtAge value to int and update it in table.
I also tried
sqlComm.Parameters.Add("@Age", SqlDbType.Int).Value = Convert.ToInt16(txtAge.Text);

Nothing Happened.
Please Help me.
Thanks
Ravi

What I have tried:

I also tried
sqlComm.Parameters.Add("@Age", SqlDbType.Int).Value = Convert.ToInt16(txtAge.Text);
Posted
Updated 17-Aug-21 7:45am
v2
Comments
Richard Deeming 17-Aug-21 12:31pm    
"select * from Student where stdname='" + Session["SessionStdname"] + "'"

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

You already know how to use parameters - you've done it in your btnStudent_Click1 method.
ravitv 23-Aug-21 3:11am    
I tried this Richard

txtAgeNew.Attributes.Add("readonly", "readonly");

in the form load event
and it works.
Thanks again Richard.

As you have already tried, you should convert the age ton integer before passing it to the procedure.

Beyond that, you wrote that nothing happens when you call the procedure. If that is the case the key part is how you handle @STDID parameter. Since @STDID is used in your WHERE clause, chances are that you're passing a wrong value for it. This causes a situation where you try to update a none existent row or update a wrong row and this looks like nothing happens on the row you're looking at.

You haven't posted that part of the code so in your C# code, have a look at the way you define @STDID parameter, ensure that it contains the correct value and also ensure that it's properly converted to integer prior to calling the procedure.
 
Share this answer
 
Comments
ravitv 23-Aug-21 3:10am    
Thanks Wendelius,
I came across a forum that suggested to write a code in form load event as below
protected void Page_Load(object sender, EventArgs e)
{
txtAgeNew.Attributes.Add("readonly", "readonly");
....
......
.......


with design .aspx as
<asp:textbox id="txtAge" runat="server" visible="true" readonly="true">

and it works....!!!!!!!

The conversion nvarchar to int etc...nothing happened.
Anyway thanks Wendelius
ravitv 24-Aug-21 23:44pm    
Sorry, please replace txtAge to txtAgeNew
Don't even try to store an Age in a database: it is out of date the instant you store it as Age is a "moving feast" - it changes as time moves on. Someone can be 17 at this instant, and 18 a second later.

Instead, store a DATETIME value which is the birthdate and work out the age when you need it rather than trying to store it and assume it is correct at any point in the future!

This may help: Working with Age: it's not the same as a TimeSpan![^]
 
Share this answer
 
Comments
ravitv 18-Aug-21 3:05am    
I agree with you OriginalGriff, I made a mistake, I should follow your solution from my next project.
Thanks
Ravi

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900