conversion failed when converting datetime from character string

Question

1.00/5 (2 votes)

See more:

Dear Friends

i am trying to upload the file from my computer and want tp stored of replace the existing data in database .. But i am getting this error
Need your help

My Stored procedure

SQL

alter procedure Usb_fileupload   
               
(                
@Sub_Code varchar(50),      
@Activedate varchar(50)                
)                
as                
begin               
    if((select count(*) from tblActiveSubBroker where Sub_Code=@Sub_Code)<1  and @Sub_Code<>'')          
       
insert                
 into                
tblActiveSubBroker                
(   
Sub_Code,      
Activedate                
)                
values                
(    

@Sub_Code,      
convert(datetime,@Activedate,103) 
)                
  end

and My .cs code

C#

protected void btnsave_Click1(object sender, EventArgs e)
    {
        Label1.Visible = true;
        string tbname = "tblActiveSubBroker"; //dataset .. used 2 stored. then move dataset to .. grid..
        string filename = Path.GetFileName(FileUpload1.FileName);
        try
        {
            string fileName = string.Empty;
            if (FileUpload1.HasFile)
            {

                fileName = FileUpload1.PostedFile.FileName;

                string file_ext = Path.GetExtension(fileName);
                if (file_ext.Trim().ToLower() == ".csv")
                {
                    FileUpload1.Controls.Clear();

                    string line = null; int i = 0;
                    string imgMap;
                    imgMap = ConfigurationSettings.AppSettings["imageFolderMap"].ToString();

                    //  save file on server then upload serfile on database and delete server file 
                     strPath = imgMap + Path.GetFileName(fileName);
                    if (!Directory.Exists(imgMap))
                    {

                        Directory.CreateDirectory(imgMap);
                    }
                    //else
                    //{
                    //    Directory.Delete(imgMap);
                    //    Directory.CreateDirectory(imgMap);

                    //}

                    FileUpload1.PostedFile.SaveAs(strPath);


                    using (StreamReader sr = File.OpenText(strPath))
                    {
                        while ((line = sr.ReadLine()) != null)
                        {
                            string[] data = line.Split(',');
                            if (data.Length > 0)
                            {
                                if (i == 0)
                                {

                                    foreach (object item in data)
                                    {
                                        {

                                            dt.Columns.Add(new DataColumn());

                                        }
                                    }

                                    i++;
                                }
                                DataRow row = dt.NewRow();
                                row.ItemArray = data;
                                dt.Rows.Add(row);

                            }
                        }
                     }






                     int count = dt.Rows.Count;
                     int index = 0;
                     //int currentindex = 0;


                     for (index = 0; index < count; index++)
                     {
                         string code = dt.Rows[index][1].ToString();
                         string date = dt.Rows[index][2].ToString();
                         con = new SqlConnection("Data Source=192.168.0.187;Initial Catalog=mis;Persist Security Info=True;User ID=sa;Password=ms");
                         cmd.Connection = con;
                         con.Open();

                         SqlCommand cmd1 = new SqlCommand("Usb_fileupload", con);
                         cmd1.CommandType = System.Data.CommandType.StoredProcedure;
                         cmd1.CommandTimeout = 10000;
                         SqlDataAdapter da = new SqlDataAdapter(cmd1);
                         cmd1.Parameters.Clear();
                         cmd1.Parameters.AddWithValue("@Sub_Code", SqlDbType.VarChar).Value = code;
                        
                         cmd1.Parameters.AddWithValue("@Activedate", SqlDbType.VarChar).Value = date;
                         cmd1.ExecuteNonQuery();
                         con.Close();
                         Label1.Text = "Data Inserted successfully";



                     }

              }
                else
                {

                    Label1.Text = "Error in Inserting Data";

                }
            }


        }
        catch (Exception Ex)
        {
           
            Label1.Visible = true;
            Label1.Text = "Error in Inserting Data";
           
        }
        finally
        {

            con.Close();
            dt.Clear();

            System.IO.File.Exists(strPath);
            System.IO.File.GetAccessControl(strPath);
            System.IO.File.Delete(strPath);
            Label1.Visible = true;
            Label1.Text = "Data Inserted Successfully";
        }


    }

    }

I am getting the error on date

conversion failed when converting datetime from character string

Posted 16-Jul-12 19:31pm

pratham2587

Updated 16-Jul-12 19:34pm

Sebastian T Xavier

v2

Add a Solution

Comments

Sebastian T Xavier 17-Jul-12 1:35am

Please do spell check.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sanwar_mal_jat · Answer 1 · 2012-07-16T19:41:00

Solution 3

Hello change this line as like ....

cmd1.Parameters.AddWithValue("@Activedate", SqlDbType.DateTime).Value = date;

Posted 16-Jul-12 19:41pm

sanwar_mal_jat

Comments

pratham2587 18-Jul-12 1:50am

still issue is not resolved PL need help to solve the problem ..As i am new to Asp.net PLease help

regards
OPratham
Thanks in advance

sanwar_mal_jat 18-Jul-12 2:21am

Hello thanks for rewrite.
You can try it as like...........
protected void btnsave_Click1(object sender, EventArgs e)
{
Label1.Visible = true;
string tbname = "tblActiveSubBroker"; //dataset .. used 2 stored. then move dataset to .. grid..
string filename = Path.GetFileName(FileUpload1.FileName);
try
{
string fileName = string.Empty;
if (FileUpload1.HasFile)
{

fileName = FileUpload1.PostedFile.FileName;

string file_ext = Path.GetExtension(fileName);
if (file_ext.Trim().ToLower() == ".csv")
{
FileUpload1.Controls.Clear();

string line = null; int i = 0;
string imgMap;
imgMap = ConfigurationSettings.AppSettings["imageFolderMap"].ToString();

// save file on server then upload serfile on database and delete server file
strPath = imgMap + Path.GetFileName(fileName);
if (!Directory.Exists(imgMap))
{

Directory.CreateDirectory(imgMap);
}
//else
//{
// Directory.Delete(imgMap);
// Directory.CreateDirectory(imgMap);

//}

FileUpload1.PostedFile.SaveAs(strPath);

using (StreamReader sr = File.OpenText(strPath))
{
while ((line = sr.ReadLine()) != null)
{
string[] data = line.Split(',');
if (data.Length > 0)
{
if (i == 0)
{

foreach (object item in data)
{
{

dt.Columns.Add(new DataColumn());

}
}

i++;
}
DataRow row = dt.NewRow();
row.ItemArray = data;
dt.Rows.Add(row);

}
}
}

int count = dt.Rows.Count;
int index = 0;
//int currentindex = 0;

for (index = 0; index < count; index++)
{
string code = dt.Rows[index][1].ToString();
string date = dt.Rows[index][2].ToString(); // Your Code

con = new SqlConnection("Data Source=192.168.0.187;Initial Catalog=mis;Persist Security Info=True;User ID=sa;Password=ms");
cmd.Connection = con;
con.Open();

SqlCommand cmd1 = new SqlCommand("Usb_fileupload", con);
cmd1.CommandType = System.Data.CommandType.StoredProcedure;
cmd1.CommandTimeout = 10000;
SqlDataAdapter da = new SqlDataAdapter(cmd1);
cmd1.Parameters.Clear();
cmd1.Parameters.AddWithValue("@Sub_Code", SqlDbType.VarChar).Value = code;

cmd1.Parameters.AddWithValue("@Activedate", SqlDbType.VarChar).Value =Convert.ToDateTime( date);
cmd1.ExecuteNonQuery();
con.Close();
Label1.Text = "Data Inserted successfully";

}

}
//Past here renaming code

sanwar_mal_jat 18-Jul-12 2:24am

And use your stored procedure as like .................

alter procedure Usb_fileupload

(
@Sub_Code varchar(50),
-- @Activedate varchar(50) Your Code
@Activedate datetime
)
as
begin
if((select count(*) from tblActiveSubBroker where Sub_Code=@Sub_Code)<1 and @Sub_Code<>'')

insert
into
tblActiveSubBroker
(
Sub_Code,
Activedate
)
values
(

@Sub_Code,
--convert(datetime,@Activedate,103) your code
@Activedate
)
end

pratham2587 18-Jul-12 2:57am

thanks sanwar mal jat

But it is still showing same error Pls help

sanwar_mal_jat 18-Jul-12 3:02am

can u tell me result of date in this code ----- string date = dt.Rows[index][2].ToString();

pratham2587 18-Jul-12 3:04am

as i am trying to debug the code it is not coming on that debugger it is directly going on catch and giving the above error

sanwar_mal_jat 18-Jul-12 3:13am

Apply your Break point here Label1.Visible = true; and then debug and tell me where throw in catch block...

pratham2587 18-Jul-12 3:23am

using (StreamReader sr = File.OpenText(strPath))
{
while ((line = sr.ReadLine()) != null)
{
string[] data = line.Split(',');
if (data.Length > 0)
{
if (i == 0)
{

foreach (object item in data)
{
{

dt.Columns.Add(new DataColumn());

}
}

i++;
}
DataRow row = dt.NewRow();
row.ItemArray = data;
dt.Rows.Add(row);

}
}
}

After Debugging it goes till this code and after jumps directly to catch statement showing thata error.....

StianSandberg · Answer 2 · 2012-07-16T19:41:00

You are sending your date as a varchar

C#

cmd1.Parameters.AddWithValue("@Activedate", SqlDbType.VarChar).Value = date;

and putting @tblActiveSubBroker into your procedure as varchar. Then you try to convert it to a date on insert to table.
This is not a good approach.You should do the converting in your code before sending it to sql.

C#

var myDate = DateTime.Parse("whatever");
cmd1.Parameters.AddWithValue("@Activedate", SqlDbType.DateTime).Value = myDate ;

Sebastian T Xavier · Answer 3 · 2012-07-16T19:37:00

Solution 1

Hi,

Can you change the type of @Activedate to datetime and see whether it is getting solved. You need to make corresponding type changes in your C# code also.

Regards
Sebastian

Posted 16-Jul-12 19:37pm

Sebastian T Xavier

Updated 16-Jul-12 19:41pm

v2

Comments

pratham2587 17-Jul-12 1:42am

Tried but showing same error....

conversion failed when converting datetime from character string

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us