Solved

How to solve the problem of 'Failed to convert parameter value from a String to a DateTime'?

Posted on 2008-06-10
36
1,392 Views
Last Modified: 2008-06-25
I am using a stored procedure to insert into the database.But when i am inserting the records its giving me error like the one above.Can anyone suggest me how to solve this problem.
try
			{
				SqlCommand cmd = new SqlCommand("insertcomp", con);
				cmd.CommandType = CommandType.StoredProcedure;
				pclientcode=new SqlParameter("clientcode",SqlDbType.VarChar);
				pclientname=new SqlParameter("clientname",SqlDbType.VarChar);
				pstatus=new SqlParameter("status",SqlDbType.VarChar);
				pcompanyaddress=new SqlParameter("companyaddress",SqlDbType.VarChar);
				pbillingaddress=new SqlParameter("billingaddress",SqlDbType.VarChar);
				pbillto=new SqlParameter("billto",SqlDbType.VarChar);
				ptelephone=new SqlParameter("telephone",SqlDbType.VarChar);
				pfascimile=new SqlParameter("fascimile",SqlDbType.VarChar);
				pbusinesstype=new SqlParameter("businesstype",SqlDbType.VarChar);
				pstaffstrength=new SqlParameter("staffstrength",SqlDbType.Int);
				premarks=new SqlParameter("remarks",SqlDbType.VarChar);
				pstartdate=new SqlParameter("startdate",SqlDbType.DateTime);
				preceiveddate=new SqlParameter("receiveddate",SqlDbType.DateTime);
				psuspenddate=new SqlParameter("suspenddate",SqlDbType.DateTime);
				pterminatedate=new SqlParameter("terminatedate",SqlDbType.DateTime);
				precognitionmode=new SqlParameter("recognitionmode",SqlDbType.VarChar);
				pinsurance=new SqlParameter("insurance",SqlDbType.Bit);
				pforeignnumber=new SqlParameter("foreignnumber",SqlDbType.Bit);
				prcare=new SqlParameter("rcare",SqlDbType.Bit);
				pempno=new SqlParameter("empno",SqlDbType.Bit);
				pmediaccess=new SqlParameter("mediaccess",SqlDbType.Bit);
				pcharacter=new SqlParameter("character",SqlDbType.Bit);
				pcheckmedilimit=new SqlParameter("checkmedilimit",SqlDbType.Bit);
				pcheckpolicyno=new SqlParameter("checkpolicyno",SqlDbType.Bit);
				psubsidiaryof=new SqlParameter("subsidiaryof",SqlDbType.VarChar);
 
			
				pclientcode.Direction=ParameterDirection.Input;
				pclientname.Direction=ParameterDirection.Input;
				pcompanyaddress.Direction=ParameterDirection.Input;
				pbillingaddress.Direction=ParameterDirection.Input;
				pbillto.Direction=ParameterDirection.Input;
				ptelephone.Direction=ParameterDirection.Input;
				pfascimile.Direction=ParameterDirection.Input;
				pbusinesstype.Direction=ParameterDirection.Input;
				pstaffstrength.Direction=ParameterDirection.Input;
				premarks.Direction=ParameterDirection.Input;
				pstartdate.Direction=ParameterDirection.Input;
				preceiveddate.Direction=ParameterDirection.Input;
				psuspenddate.Direction=ParameterDirection.Input;
				pterminatedate.Direction=ParameterDirection.Input;
				precognitionmode.Direction=ParameterDirection.Input;
				pinsurance.Direction=ParameterDirection.Input;
				pforeignnumber.Direction=ParameterDirection.Input;
				prcare.Direction=ParameterDirection.Input;
				pempno.Direction=ParameterDirection.Input;
				pmediaccess.Direction=ParameterDirection.Input;
				pcharacter.Direction=ParameterDirection.Input;
				pcheckmedilimit.Direction=ParameterDirection.Input;
				pcheckpolicyno.Direction=ParameterDirection.Input;
				psubsidiaryof.Direction=ParameterDirection.Input;
 
				condition();
                condition2();
				condition3();
				condition4();
				condition5();
				condition6();
				condition7();
                condition8();
 
 
				pclientcode.Value=TextBox1.Text;
				pclientname.Value=TextBox2.Text;
				pcompanyaddress.Value=TextBox3.Text;
				pbillingaddress.Value=TextBox4.Text;
				pbillto.Value=TextBox5.Text;
				ptelephone.Value=TextBox6.Text;
				pfascimile.Value=TextBox7.Text;
				pbusinesstype.Value=DropDownList3.SelectedItem.Value;
				pstaffstrength.Value=int.Parse(TextBox10.Text);
				premarks.Value=TextBox12.Text;
                pstartdate.Value = TextBox13.Value;
                preceiveddate.Value =TextBox15.Value;
                psuspenddate.Value = TextBox14.Value;
                pterminatedate.Value =TextBox16.Value;
				precognitionmode.Value=TextBox11.Text;
				pinsurance.Value=var6;
				pforeignnumber.Value=var1;
				prcare.Value=var8;
				pempno.Value=var2;
				pmediaccess.Value=var4;
				pcharacter.Value=var3;
				pcheckmedilimit.Value=var5;
				pcheckpolicyno.Value=var7;
				psubsidiaryof.Value=TextBox8.Text;
				
				
				cmd.Parameters.Add(pclientcode);
				cmd.Parameters.Add(pclientname);
				cmd.Parameters.Add(pcompanyaddress);
				cmd.Parameters.Add(pbillingaddress);
				cmd.Parameters.Add(pbillto);
				cmd.Parameters.Add(ptelephone);
				cmd.Parameters.Add(pfascimile);
				cmd.Parameters.Add(pbusinesstype);
				cmd.Parameters.Add(pstaffstrength);
				cmd.Parameters.Add(premarks);
				cmd.Parameters.Add(pstartdate);
				cmd.Parameters.Add(preceiveddate);
				cmd.Parameters.Add(psuspenddate);
				cmd.Parameters.Add(pterminatedate);
				cmd.Parameters.Add(precognitionmode);
				cmd.Parameters.Add(pinsurance);
				cmd.Parameters.Add(pforeignnumber);
				cmd.Parameters.Add(prcare);
				cmd.Parameters.Add(pempno);
				cmd.Parameters.Add(pmediaccess);
				cmd.Parameters.Add(pcharacter);
				cmd.Parameters.Add(pcheckmedilimit);
				cmd.Parameters.Add(pcheckpolicyno);
				cmd.Parameters.Add(psubsidiaryof);
				
				cmd.ExecuteNonQuery();
				
 
			}
			catch(Exception ex)
			{
				Label1.Text=ex.Message;
			}

Open in new window

0
Comment
Question by:tryokane
  • 16
  • 11
  • 8
  • +1
36 Comments
 
LVL 12

Expert Comment

by:jandromeda
ID: 21757343
Try this.

pstartdate.Value = DateTime.Parse(TextBox13.Value);
0
 
LVL 2

Author Comment

by:tryokane
ID: 21757359
No i also tried this but its again giving error like 'String was not recognized as a valid DateTime'.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21757364
in which format does user enter date in the text format ?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 2

Author Comment

by:tryokane
ID: 21757532
In string format.I am actually taking an html textbox for inputting.Is there anything wrong with the html text box format,but i observed during debugging that it is coming in the string format like "23-08-2008".I am not getting where i am struck with.
<table width="700" align="center" bgColor="seashell">
					<tr>
						<td><b>Company Code</b></td>
						<td><asp:textbox id="TextBox1" Runat="server"></asp:textbox></td>
						<td><asp:dropdownlist id="DropDownList1" Runat="server"></asp:dropdownlist></td>
						<td><asp:button id="Button1" Runat="server" BorderColor="Blue" BackColor="#C0FFFF" Font-Bold="True"
								Text="Search"></asp:button></td>
					</tr>
					<tr>
						<td><b>Company Name</b></td>
						<td><asp:textbox id="TextBox2" Runat="server"></asp:textbox></td>
						<td><asp:dropdownlist id="DropDownList2" Runat="server"></asp:dropdownlist></td>
						<td><asp:button id="Button2" Runat="server" BorderColor="Blue" BackColor="#C0FFFF" Font-Bold="True"
								Text="Search" ForeColor="#000040"></asp:button></td>
					</tr>
					<tr>
						<td colSpan="4"></td>
					</tr>
					<tr>
						<td><b>Company Address</b></td>
						<td colSpan="3"><asp:textbox id="TextBox3" Runat="server" TextMode="MultiLine"></asp:textbox></td>
					</tr>
					<tr>
						<td><b>Billing Address</b></td>
						<td colSpan="3"><asp:textbox id="TextBox4" Runat="server" TextMode="MultiLine"></asp:textbox></td>
					</tr>
					<tr>
						<td><b>Attention Bill To</b></td>
						<td colSpan="3"><asp:textbox id="TextBox5" Runat="server"></asp:textbox></td>
					</tr>
					<tr>
						<td colSpan="4"></td>
					</tr>
					<tr>
						<td><b>Telephone</b></td>
						<td><asp:textbox id="TextBox6" Runat="server"></asp:textbox></td>
						<td><b>Fascimile</b></td>
						<td><asp:textbox id="TextBox7" Runat="server"></asp:textbox></td>
					</tr>
					<tr>
						<td colSpan="4"><asp:checkbox id="CheckBox1" Runat="server" Font-Bold="True" Text="Foreign Number"></asp:checkbox></td>
					</tr>
					<tr>
						<td><b>Subsidiary Of</b></td>
						<td><asp:textbox id="TextBox8" Runat="server"></asp:textbox></td>
						<td colSpan="2"><asp:textbox id="TextBox9" Runat="server"></asp:textbox></td>
					</tr>
					<tr>
						<td style="HEIGHT: 1px"><b>Business Type</b></td>
						<td colSpan="3" style="HEIGHT: 1px"><asp:dropdownlist id="DropDownList3" Runat="server" >
								<asp:ListItem Value="Select">Select</asp:ListItem>
								<asp:ListItem Value="AirLines">AirLines</asp:ListItem>
								<asp:ListItem Value="Business">Business</asp:ListItem>
								<asp:ListItem Value="IT Company">IT Company</asp:ListItem>
							</asp:dropdownlist></td>
					</tr>
					<tr>
						<td><b>Staff Strength</b></td>
						<td><asp:textbox id="TextBox10" Runat="server"></asp:textbox></td>
						<td colSpan="2"><asp:checkbox id="CheckBox8" Runat="server" Font-Bold="True" Text="RCare"></asp:checkbox></td>
					</tr>
					<tr>
						<td><b>Remarks(Internal)</b></td>
						<td colSpan="3"><asp:textbox id="TextBox12" Runat="server" TextMode="MultiLine"></asp:textbox></td>
					</tr>
					<tr>
						<td colSpan="4"></td>
					</tr>
					<tr>
						<td style="HEIGHT: 15px"><b>Start Date</b></td>
						<td style="HEIGHT: 15px" colSpan="3"><input id="TextBox13" type="text" size="25" runat="server">
							<a href="javascript:NewCal('TextBox13','ddmmyyyy')"><img height="16" alt="Pick a date" src="cal.gif" width="16" border="0"></a></td>
					</tr>
					<tr>
						<td><b>Suspend Date</b></td>
						<td colSpan="3"><input id="TextBox14" type="text" size="25" runat="server"> <a href="javascript:NewCal('TextBox14','ddmmyyyy')">
								<img src="cal.gif" width="16" height="16" border="0" alt="Pick a date"></a></td>
					</tr>
					<tr>
						<td style="HEIGHT: 18px"><b>Reactivate Date</b></td>
						<td style="HEIGHT: 18px" colSpan="3"><input id="TextBox15" type="text" size="25" runat="server">
							<a href="javascript:NewCal('TextBox15','ddmmyyyy')"><img src="cal.gif" width="16" height="16" border="0" alt="Pick a date"></a></td>
					</tr>
					<tr>
						<td style="HEIGHT: 21px"><b>Terminate Date</b></td>
						<td style="HEIGHT: 21px" colSpan="3"><input id="TextBox16" type="text" size="25" runat="server">
							<a href="javascript:NewCal('TextBox16','ddmmyyyy')"><img src="cal.gif" width="16" height="16" border="0" alt="Pick a date"></a></td>
					</tr>
					<tr>
						<td><b>Recognition Mode</b></td>
						<td colSpan="3"><asp:textbox id="TextBox11" Runat="server" TextMode="MultiLine"></asp:textbox><asp:button id="Button3" Runat="server" BorderColor="Blue" BackColor="#C0FFFF" Font-Bold="True"
								Text="Search"></asp:button></td>
					</tr>
					<tr>
						<td colSpan="2"><asp:checkbox id="CheckBox2" Runat="server" Font-Bold="True" Text="Employee Number"></asp:checkbox></td>
						<td colSpan="2"><asp:checkbox id="CheckBox3" Runat="server" Font-Bold="True" Text="Character"></asp:checkbox></td>
					</tr>
					<tr>
						<td colSpan="2"><asp:checkbox id="CheckBox4" Runat="server" Font-Bold="True" Text="Medi Access"></asp:checkbox></td>
						<td colSpan="2"><asp:checkbox id="CheckBox5" Runat="server" Font-Bold="True" Text="Check MediLimit"></asp:checkbox></td>
					</tr>
					<tr>
						<td colSpan="2"><asp:checkbox id="CheckBox6" Runat="server" Font-Bold="True" Text="Insurance"></asp:checkbox></td>
						<td colSpan="2"><asp:checkbox id="CheckBox7" Runat="server" Font-Bold="True" Text="Check PolicyNumber"></asp:checkbox></td>
					</tr>
					<tr>
						<td align="center" colSpan="4"><asp:label id="Label1" Runat="server" Font-Bold="True" ForeColor="Red"></asp:label></td>
					</tr>
					<tr align="center">
						<td><asp:button id="Button4" Runat="server" BorderColor="Blue" BackColor="#C0FFFF" Font-Bold="True"
								Text="Add" Width="70" OnClick="Button4_Click"></asp:button></td>
						<td><asp:button id="Button5" Runat="server" BorderColor="Blue" BackColor="#C0FFFF" Font-Bold="True"
								Text="Clear" Width="70"></asp:button></td>
						<td colSpan="2"><asp:button id="Button6" Runat="server" BorderColor="Blue" BackColor="#C0FFFF" Font-Bold="True"
								Text="Exit" Width="70"></asp:button></td>
					</tr>
					<tr>
						<td></td>
					</tr>
				</table>	

Open in new window

0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21757547
change to
DateTime.ParseExactTextBox13.Value, "dd-MM-yyyy", null);
and try
0
 
LVL 2

Author Comment

by:tryokane
ID: 21757574
This is the error which i got after trying the above conversion,'String was not recognized as a valid DateTime'.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21757589
are u sure u are getting the date in the format 28-08-2008 ? if not u must change the string "dd-MM-yyyy" to suit ur format
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21757610
try

dd-mmm-yyyy like (28-Aug-2008)
0
 
LVL 2

Author Comment

by:tryokane
ID: 21757644
I tried this also but the same error.The field that i took in the database was datetime.Is this anything to do with the error.
0
 
LVL 2

Author Comment

by:tryokane
ID: 21776219
Any body who can help me on this problem?
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21776251
can u post ho ur code looks now...

only that line would do.. and the exact error ur getting
0
 
LVL 2

Author Comment

by:tryokane
ID: 21776426
'String was not recognized as a valid DateTime' is the error i get.The line of code is the one below:
DateTime dt1=Convert.ToDateTime(TextBox13.Text);
                pstartdate.Value = dt1;
                DateTime dt2=Convert.ToDateTime(TextBox14.Text);
                psuspenddate.Value = dt2;
                DateTime dt3 = Convert.ToDateTime(TextBox15.Text);
                preceiveddate.Value = dt3;
                DateTime dt4= Convert.ToDateTime(TextBox16.Text);
                pterminatedate.Value = dt4;
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21776459
change Convert.ToDateTime(TextBox13.Text);

to

DateTime.ParseExact(TextBox13.Value, "dd-MM-yyyy", null);
and similar;y for others and see if u are getting the same error

0
 
LVL 2

Author Comment

by:tryokane
ID: 21776490
Still the same error.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21776515
have u changed for all the datetime parses
also put a mesage box before datetime.parse
MessageBox.Show(TextBox13.Value);

and see in what format the value is coming
0
 
LVL 2

Author Comment

by:tryokane
ID: 21776536
I did all the testing using debugger but "dd/mm/yyyy" is the format which is coming inthe textbox.value.No still the same error.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21776540
DateTime.ParseExact(TextBox13.Value, "dd/MM/yyyy", null);
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21776855
wat you are getting in TextBox13.Text ??
if it is not a valid string you may get null values
0
 
LVL 2

Author Comment

by:tryokane
ID: 21777047
I am getting string format of date i.e "12/3/2008"
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21777062
if thats is the format

then the format u shud give when parsing is "dd/MM/yyyy"

i.e

DateTime.ParseExact(TextBox13.Value, "dd/MM/yyyy", null);
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21777129
"dd/M/yyyy"
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21777136
DateTime.ParseExact(TextBox13.Value, "dd/M/yyyy", null);
0
 
LVL 2

Author Comment

by:tryokane
ID: 21777160
No,Still the same error!
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21777195
DateTime obj = DateTime.ParseExact(TextBox13.Value,dd/M/yyyy",null)

pstartdate.Value = obj.ToString("dd/MMM/yyyy");

apply to all datetime field
0
 
LVL 2

Author Comment

by:tryokane
ID: 21777253
No,Not Yet Solved.Do I need to change the format in the database itself?
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21777259
no that doesnt matter.. u are getting this error when  u are trying to parse the date in dont net and has nothing to do with the SQL server
0
 
LVL 2

Author Comment

by:tryokane
ID: 21777291
How will i be able to solve this error?The below code is able to insert into database sometimes and sometimes its giving me error like the above?
//DateTime dt1 = DateTime.Parse(TextBox13.Text);
               // pstartdate.Value = dt1;
               //DateTime dt2 = DateTime.Parse(TextBox14.Text);
               // psuspenddate.Value = dt2;
               //DateTime dt3 = DateTime.Parse(TextBox15.Text);
               // preceiveddate.Value = dt3;
               //DateTime dt4 = DateTime.Parse(TextBox16.Text);
               // pterminatedate.Value = dt4;

Open in new window

0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21777321
ok when textboxex dont have value it will show error right?

Bcoz it will assign null values to parameter! to avoid this

check
if (dt4  == null)
{
pterminatedate.Value = "";
}
else
{
pterminatedate.Value = dt4.ToString("dd/MMM/yyyy");
}
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21777322
pterminatedate.Value =DBnull.Value;
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21777325
pterminatedate.Value =DBnull.Value;
not pterminatedate.Value = "';
0
 
LVL 2

Author Comment

by:tryokane
ID: 21777357
No no there is a value always  in the textbox.I mean that even though there is date in the text box it is giving error.
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21777368
if you are using
DateTime obj = DateTime.ParseExact(TextBox13.Value,dd/M/yyyy",null)

pstartdate.Value = obj.ToString("dd/MMM/yyyy");


then make sure that you are getting date in

12/3/2008 and not 12/03/2008

if you are getting date as 12/03/2008 then you should use like

DateTime obj = DateTime.ParseExact(TextBox13.Value,dd/MM/yyyy",null)
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21777376
DateTime obj = DateTime.ParseExact(TextBox13.Value,"dd/MM/yyyy",null)

Note : MM and M in format
0
 
LVL 2

Author Comment

by:tryokane
ID: 21790540
No it is not at all converting into the above format but directly raising an exception whenever it is encountering datetime.parseexact format.
0
 
LVL 2

Author Comment

by:tryokane
ID: 21790688
Anyone who can solve this problem???
0
 
LVL 2

Accepted Solution

by:
tryokane earned 0 total points
ID: 21800923
Ok i solved it using a function called convertdatefromstring.Thanks for all your suggestions.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
C# bracket error 3 30
REXEX help Part 2 2 23
Example works but why do I need to click twice on button 4 37
jQuery - following an example but can I save data server side? 20 49
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question