Error converting datatype varchar to datetime

Question

2.00/5 (1 vote)

See more:

SQL-Server-2005

hello in my project am getting an error that:

Error converting datatype varchar to datetime"

in my table, in logic and in SP the DOB and DOA are declared as datetime.
instead of putting dob.text , i have tried,dob.ToString(),it is also not working. and also i have tried pd.doa=datetime.parse...it is also getting error.

How to solve this error.

Here is my code

                            pd.FName = txtFname.Text;
                            pd.MName = txtMName.Text;
                            pd.LName = txtLName.Text;
                            pd.DOB = Convert.ToDateTime(dtpdob.Text);


                            int year1 = dtpdob.Value.Year;
                            int year2 = DateTime.Now.Year;

                            if ((year1 == year2) | (year2 < year1))
                            {
                                CommonFunctions.Error("Please Enter a valid Date of Birth!!!");
                                dtpdob.Focus();
                                return;
                            }

                            int NewAge = year2 - year1;

                            pd.SID = Convert.ToInt16(txtsid.Text);
                            pd.Sex = cmbSex.SelectedItem.ToString();
                            lblage.Text = NewAge.ToString();
                            pd.Age = NewAge;
                            pd.Address = txtAddress1.Text;
                            pd.SPName = cmbSponser.SelectedItem.ToString();

                            pd.FathName = txtFathName.Text;
                            pd.MothName = txtMothName.Text;
                            pd.MothOccu = txtMothoccu.Text;
                            pd.FathOccu = txtfathOccu.Text;
                            pd.Fam1 = txtFam1.Text;
                            pd.Fam2 = txtFam2.Text;
                            pd.Fam3 = txtFam3.Text;
                            pd.Fam4 = txtFam4.Text;
                            pd.Fam5 = txtFam5.Text;
                            pd.Fam6 = txtFam6.Text;
                            pd.Fam7 = txtFam7.Text;
                            pd.Fam8 = txtFam8.Text;


                            pd.FamCond = txtcond.Text;
                            pd.FamNotes = txtnotes.Text;
                            pd.Status = txtstatus.Text;
                            pd.DOA = Convert.ToDateTime(doa.Text);


                            pd.EntryDate = Convert.ToDateTime(DateTime.Now.ToString());

public static bool Update(PersonelData person)
     {
         const string UpdateDetails = "exec[UPDATE_BULK_INFO]@sid,@FName,@MName,@LName,@DOB,@sex,@age,@address,@SponserName,@DOA,@FathName,@FathOccu,@MothName,@MothOccu,@Fam1,@Fam2,@Fam3,@Fam4,@Fam5,@Fam6,@Fam7,@Fam8,@FamCond,@FamNotes,@Status";

         DAL.Method method = new DAL.SqlDataBase();
         method.SelectQuery(UpdateDetails);
         
         method.AddParameter("@sid", person.SID);
         method.AddParameter("@FName", person.FName);
         method.AddParameter("@MName", person.MName);
         method.AddParameter("@LName", person.LName);
         method.AddParameter("@DOB", person.DOB);
         method.AddParameter("@sex", person.Sex);
         method.AddParameter("@age", person.Age);
         method.AddParameter("@address", person.Address);
         method.AddParameter("@SponserName", person.SPName);
         method.AddParameter("@DOA", person.DOA);
         method.AddParameter("@FathName", person.FathName);
         method.AddParameter("@FathOccu", person.FathOccu);
         method.AddParameter("@MothName", person.MothName);
         method.AddParameter("@MothOccu", person.MothOccu);
         method.AddParameter("@Fam1", person.Fam1);
         method.AddParameter("@Fam2", person.Fam2);
         method.AddParameter("@Fam3", person.Fam3);
         method.AddParameter("@Fam4", person.Fam4);
         method.AddParameter("@Fam5", person.Fam5);
         method.AddParameter("@Fam6", person.Fam6);
         method.AddParameter("@Fam7", person.Fam7);
         method.AddParameter("@Fam8", person.Fam8);
         method.AddParameter("@FamCond", person.FamCond);
         method.AddParameter("@FamNotes", person.FamNotes);
         method.AddParameter("@Status", person.Status);
         //method.AddParameter("@EntryDate", person.EntryDate);

         int ins = method.ExecuteQuery();

         if (ins > 0)
         {
             return true;
         }

         return false;
     }

Posted 14-Nov-13 8:18am

Member 10370658

Updated 14-Nov-13 8:36am

Richard C Bishop

v3

Add a Solution

Comments

RedDk 14-Nov-13 14:34pm

The TSQL that underlies this C# procedural is referred to as "const string UpdateDetails". In this string "person.DOB" has assigned a (datetime) value type. The actual error saying "varchar" is coming from the TSQL layer. Surmise that somewhere in the table definitions, the type is varchar. So trying to BULK INSERT a "datetime" witout first using CAST will be difficult. Try CAST(<datetime> AS [varchar]). I would advise eventually changing the table varchar to datetime though; to answer your question.

Member 10370658 14-Nov-13 14:49pm

i will show my table details ;there three tables they are

USE [IMS]
GO
/****** Object: Table [dbo].[tbl_Family] Script Date: 11/15/2013 13:19:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Family](
[FID] [int] IDENTITY(1,1) NOT NULL,
[FathName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MothName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam1] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam2] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam3] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam4] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam5] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam6] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam7] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fam8] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [int] NOT NULL,
[FathOccu] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MothOccu] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_Family] PRIMARY KEY CLUSTERED
(
[FID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Family] WITH CHECK ADD CONSTRAINT [FK_tbl_Family_tbl_Personel] FOREIGN KEY([SID])
REFERENCES [dbo].[tbl_Personel] ([SID])
GO
ALTER TABLE [dbo].[tbl_Family] CHECK CONSTRAINT [FK_tbl_Family_tbl_Personel]

USE [IMS]
GO
/****** Object: Table [dbo].[tbl_Personel] Script Date: 11/15/2013 13:19:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Personel](
[PID] [int] IDENTITY(1,1) NOT NULL,
[SID] [int] NOT NULL,
[FName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DOB] [datetime] NULL,
[Sex] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Age] [int] NULL,
[Address] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SponserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EntryDate] [datetime] NULL,
[DOA] [datetime] NULL,
CONSTRAINT [PK_tbl_Personel] PRIMARY KEY CLUSTERED
(
[SID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [IMS]
GO
/****** Object: Table [dbo].[tbl_others] Script Date: 11/15/2013 13:19:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_others](
[SID] [int] NOT NULL,
[OID] [int] IDENTITY(1,1) NOT NULL,
[FamCond] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FamNotes] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_others] PRIMARY KEY CLUSTERED
(
[OID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_others] WITH CHECK ADD CONSTRAINT [FK_tbl_others_tbl_Personel] FOREIGN KEY([SID])
REFERENCES [dbo].[tbl_Personel] ([SID])
GO
ALTER TABLE [dbo].[tbl_others] CHECK CONSTRAINT [FK_tbl_others_tbl_Personel]

Member 10370658 14-Nov-13 14:51pm

Here in my table ,i have declared the field as datetime.

Member 10370658 14-Nov-13 14:56pm

And also when am executing the storedprocedure it doesnt shows any error..it simply inserts the date.

Member 10370658 14-Nov-13 14:57pm

Here is my Stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <nidheesh>
-- Create date: <05-11-2013>
-- Description: <insert personel="" data="">
-- =============================================
ALTER PROCEDURE [dbo].[UPDATE_BULK_INFO]

@sid int,
@FName varchar(50),
@MName varchar(50),
@LName varchar (50),
@DOB datetime,
@sex varchar(50),
@age int,
@address varchar(50),
@SponserName varchar(50),

@FathName varchar(50),
@FathOccu varchar(50),
@MothName varchar(50),
@MothOccu varchar(50),
@Fam1 varchar(150),
@Fam2 varchar(150),
@Fam3 varchar(150),
@Fam4 varchar(150),
@Fam5 varchar(150),
@Fam6 varchar(150),
@Fam7 varchar(150),
@Fam8 varchar(150),

@FamCond varchar(150),
@FamNotes varchar(150),
@Status varchar(150),
@DOA datetime

AS
BEGIN
UPDATE tbl_Personel set FName=@FName,
MName=@MName,
LName=@LName,
DOB=@DOB,
Sex=@sex,
Age=@age,
Address=@address,
SponserName=@SponserName,
DOA=@DOA where SID=@sid

UPDATE tbl_Family set FathName=@FathName,
MothName=@MothName,
FathOccu=@FathOccu,
MothOccu=@MothOccu,
Fam1=@Fam1,
Fam2=@Fam2,
Fam3=@Fam3,
Fam4=@Fam4,
Fam5=@Fam5,
Fam6=@Fam6,
Fam7=@Fam7,
Fam8=@Fam8 where SID=@SID

UPDATE tbl_others set FamCond=@FamCond,
FamNotes=@FamNotes,
Status=@Status where SID=@SID
END

Thava Rajan 14-Nov-13 23:50pm

could you please provide the datatype of DOB and DOA in personaldata class

Thava Rajan 14-Nov-13 23:52pm

hope fully you got problem in designing the class not the table

Member 10370658 15-Nov-13 2:59am

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BAL
{
public class PersonelData
{
public int SID { get; set; }
public string FName { get;set;}
public string MName{get;set;}
public string LName{get;set;}
public DateTime DOB{get;set;}
public string Sex{get;set;}
public int Age{get;set;}
public string Address { get; set; }

public string SPCNum1 { get; set; }
public string SPCNum2 { get; set; }
public string SPAddress { get; set; }
public string SPName { get; set; }

public string FathName { get; set; }
public string MothName { get; set; }
public string Fam1 { get; set; }
public string Fam2 { get; set; }
public string Fam3 { get; set; }
public string Fam4 { get; set; }
public string Fam5 { get; set; }
public string Fam6 { get; set; }
public string Fam7 { get; set; }
public string Fam8 { get; set; }

public string FamCond { get; set; }
public string FamNotes { get; set; }
public string Status { get; set; }

public DateTime EntryDate { get; set; }
public DateTime DOA { get; set; }

public string FathOccu { get; set; }
public string MothOccu { get; set; }

}
}

1 solution

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)

Sergey Alexandrovich Kryukov · Answer 1 · 2013-11-14T08:39:00

Solution 1

This question reflects one of the biggest fallacies of the beginners these days: a trend to work with strings representing data instead of data itself. If you have some data related to date/time, never use varchar; use some data type like date, datetime, etc.:
http://technet.microsoft.com/en-us/library/ms187594%28v=sql.90%29.aspx[^],
http://technet.microsoft.com/en-us/library/ms180878%28v=sql.90%29.aspx[^].

—SA

Posted 14-Nov-13 8:39am

Sergey Alexandrovich Kryukov

Comments

Member 10370658 14-Nov-13 14:52pm

i didnt use varchar anywhere...in my table it is datetime not varchar

Sergey Alexandrovich Kryukov 14-Nov-13 15:07pm

Then don't ask such questions. Your question title says: "varchar".
—SA

Member 10370658 14-Nov-13 21:08pm

:) My question says varchar means,i posted my entire code can u able to findout where i defined varchar instead of Datetime?.Its a conversion problem.

Sergey Alexandrovich Kryukov 14-Nov-13 23:31pm

Okay, in your code in comment there are a log of varchar. Some of them should not be there. As simple as that.
—SA

Member 10370658 15-Nov-13 3:00am

Can u please explain ?i didnt get u

Sergey Alexandrovich Kryukov 15-Nov-13 3:33am

What exactly to explain? If a column is varchar but used as time (data), you need to change the datatype to an appropriate one.
—SA

Member 10370658 15-Nov-13 9:40am

Okay .I understood.
hello can u say how to write the code for eg:
pd.DOB = Convert.ToDateTime(dtpdob.Text);

pd.DOB = Convert.ToDateTime(dtpdob.Tostring());

here which code is correct

Sergey Alexandrovich Kryukov 15-Nov-13 10:23am

In general, none of them. First may or may not work, depending of format.
To make it really work, use methods of System.DateTime: Parse, ParseExact, TryParse, TryParseExact. All together, there are many, they depend on culture and/or format string, culture can be current or parsed as parameter. Only then your can control parsing depending on input format.

Second is just gibberish: like converting time to string and then back...

—SA

Member 10370658 15-Nov-13 14:28pm

i got it i used second method .now its working fine.
:)

Sergey Alexandrovich Kryukov 15-Nov-13 14:50pm

Great. Will you accept my answer formally then?
—SA

Member 10370658 15-Nov-13 21:57pm

Ya ...Sure...
Acceppted
:)

Sergey Alexandrovich Kryukov 15-Nov-13 22:10pm

Great. Good luck, call again.
—SA