Click here to Skip to main content
15,921,959 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi
am trying to save records to table can any one tel me what exactly problem with my code

my system date time format
dd-MM-yyyy


sql tbl_agreement1


ID	int	Unchecked
Party1	nvarchar(50)	Checked
Party1ADD	nvarchar(50)	Checked
Party2	nvarchar(50)	Checked
Party2add	nvarchar(50)
Capacity	nvarchar(50)	
Agper	nvarchar(50)	
AGtop	nvarchar(50)	
AGplace	nvarchar(50)	
Tnco	nvarchar(50)	
Status	nvarchar(50)	
Endno	nvarchar(50)	
Mulahiq	varchar(50)	
Qarar	nvarchar(50)	
entered	nvarchar(MAX)	
username	nvarchar(MAX)	
[update]	nvarchar(MAX)	
[last update date]	datetime2(0)	
laspusername	nvarchar(MAX)	
[entered-date]	datetime2(0)	
AGend	datetime	
AGdt	datetime	
Enddt	datetime	


//button click
 protected void Button2_Click(object sender, EventArgs e)
    {
        string dt = DateTime.Now.ToShortDateString();
        
        try
        {

            DAL.InsertAgreement3(TextBox1.Text, TextBox2.Text,TextBox3.Text,TextBox4.Text,TextBox5.Text,TextBox6.Text,TextBox7.Text,TextBox8.Text,TextBox9.Text,TextBox10.Text,TextBox12.Text,TextBox11.Text,TextBox13.Text,TextBox14.Text,TextBox15.Text,TextBox16.Text, Session["username"].ToString());
            info.Style.Add("display", "block");
            lblerror.Visible = true;
            lblerror.Text = "Successfuly Inserted Record...";

            Response.Redirect("Agreement3.aspx");
            //clearTxt();
            
        }
        catch (Exception ex)
        {
            info.Style.Add("display", "block");
            lblerror.Visible = true;
            lblerror.Text = "Error Occured while adding.....!!!!! " + " " + ex.Message;

        }
}



//DAL 

<pre>

 public static void InsertAgreement3(string Agdt, string party1, string partyadd, string party2, string party2add, string capacity, string agper, string agtop, string agplace, string Termscon, string Agend, string Status, string EndNo, string Enddate, string Mulahiq, string Qarar, string uname)
    {
        DateTime dt;
        string sdt = System.DateTime.Now.ToString();
        string sdt1 = DateTime.Now.ToShortDateString();

        SqlConnection sqlconn = new SqlConnection(ConnString);
        try
        {
            sqlconn.Open();
            string Query = "INSERT INTO [tbl_agreement1]([AGdt],[Party1],[Party1ADD],[Party2],[Party2ADD],[Capacity],[AGper],[AGtop],[AGPlace],[Tnco],[AGend],[Status],[Endno],[Enddt],[Mulahiq],[Qarar],[entered],[entered-date],[username]) VALUES (N'" + DateTime.Parse(Agdt).ToString(new CultureInfo("en-US")) + "',N'" + party1 + "',N'" + partyadd + "',N'" + party2 + "',N'" + party2add + "',N'" + capacity + "',N'" + agper + "',N'" + agtop + "',N'" + agplace + "',N'" + Termscon + "',N'" + DateTime.Parse(Agend).ToString(new CultureInfo("en-US")) + "',N'" + Status + "',N'" + EndNo + "',N'" + DateTime.Parse(Enddate).ToString(new CultureInfo("en-US")) + "',N'" + Mulahiq + "',N'" + Qarar + "',N'" + "New Record Added" + "',N'" + DateTime.Parse(sdt).ToString(new CultureInfo("en-US")) + "',N'" + uname + "' )";
            SqlCommand sqlcomm = new SqlCommand(Query, sqlconn);
            sqlcomm.ExecuteNonQuery();
        }
        catch (Exception ex)//getting error here
        {

        }
        finally
        {
            sqlconn.Close();
        }





can any one suggest me for this m not able to save records...

thank you...
Posted
Updated 13-Nov-13 6:43am
v2
Comments
Richard C Bishop 13-Nov-13 12:41pm    
You need to debug your application and look at all the values that are getting parsed into a datetime.
Thanks7872 13-Nov-13 12:44pm    
Remove try catch blocks,run it and you will find the line where exactly you have an error. Remove above all the code and put the code relevant to that part only.
ythisbug 13-Nov-13 13:29pm    
OK THANK YOU

1 solution

It doesn't matter what your systems date/time format is set to: SQL normally uses ISO standard yyyy-MM-dd so your system setting is irrelevant, particularly if this is a web based application as the ASP.NET part of you question tags implies - the cleint may well be using a different system altogether, and you should be aware of that.

Now, lets try to fix it a bit...in order of importance:

First things first: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Second thing: Why the heck are you parsing a string to a datetime, in order to convert it to a (possibly different) string and pass it to SQL where it needs to be converted back into a DateTime value again? Instead, use a parametrized query, and pass the DateTime value directly as a DateTime - that way, SQL will always understand it, and you problem partly disappears.

Thirdly: Why are you starting with a DateTime value, converting it to a string getting it again (and hoping it wasn't midnight or it all falls apart later) and then converting it back to a DataTime at the second point? Read the current time once. And once only. Stor it in a DateTime, and pass it to SQL as a DateTime.

Fourthly: Validate your inputs before they get here: convert the user entered Enddate value to a DateTime when he inputs it, and report problems there instead of passing a bad format date string through to code this deep - where you can't report a problem directly because you don't know what UI to expect (if any). So convert it as early as possible, and pass DateTime values round, rather than strings.
 
Share this answer
 

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