i am using an Insert statement to insert into the Database from My aspx page, my statement is as below
Insert into LabResults(InvId,PatId,PatInitials,VisitId,VisitCode,AccId,Sex,DOB,ColDt,ColTm,TestGroup,TestName,NValue,CValue,Unit,RRange,NRRanL,NRRanH,Flags,Alerts,RepDt,Comments,RevHis,Lastupdatedon)values
('1','S1999','XYZ','1','Visit
1 (Day 0)','77100000','Female','1994-01-30
00:00:00.000','2012-02-26
00:00:00.000','1899-12-30
10:00:00.000','Biochemistry','Alkaline
phosphatase','521','NULL','U/L','35
- 104','35','104','H','HP','2012-03-01
00:00:00.000','NULL','Revision2-09March2012-Date
of Birth-Old value-30-Jan-1995-New value-30-Jan-1994.Reason for change-Site request.Revision1-01March2012-Gender-Old value-M-New value-F. Reason for change-Site request.NA: Result not available as specimen hemolysed. Incorrect Transportation-Specimen received
in frozen condition instead of ambient condition.','NULL')
on Debug i get an error on cmd.executeNonQuery like this: "Syntax error converting datetime from character string"
DateTime db=dob[1]+"/"+dob[0]+"/"+dob[2];//do same for all datatime
Insert into LabResults(InvId,PatId,PatInitials,VisitId,VisitCode,AccId,Sex,DOB,ColDt,ColTm,TestGroup,TestName,NValue,CValue,Unit,RRange,NRRanL,NRRanH,Flags,Alerts,RepDt,Comments,RevHis,Lastupdatedon)values
('1','S1999','XYZ','1','Visit
1 (Day 0)','77100000','Female','"+db+"','2012-02-26
00:00:00.000','1899-12-30
10:00:00.000','Biochemistry','Alkaline
phosphatase','521','NULL','U/L','35
- 104','35','104','H','HP','2012-03-01
00:00:00.000','NULL','Revision2-09March2012-Date
of Birth-Old value-30-Jan-1995-New value-30-Jan-1994.Reason for change-Site request.Revision1-01March2012-Gender-Old value-M-New value-F. Reason for change-Site request.NA: Result not available as specimen hemolysed. Incorrect Transportation-Specimen received
in frozen condition instead of ambient condition.','NULL')
aamir1010
Member
5 Points
25 Posts
Syntax error converting datetime from character string
Aug 03, 2012 04:36 AM|LINK
hi friends,
i am using an Insert statement to insert into the Database from My aspx page, my statement is as below
Insert into LabResults(InvId,PatId,PatInitials,VisitId,VisitCode,AccId,Sex,DOB,ColDt,ColTm,TestGroup,TestName,NValue,CValue,Unit,RRange,NRRanL,NRRanH,Flags,Alerts,RepDt,Comments,RevHis,Lastupdatedon) values ('1','S1999','XYZ','1','Visit 1 (Day 0)','77100000','Female','1994-01-30 00:00:00.000','2012-02-26 00:00:00.000','1899-12-30 10:00:00.000','Biochemistry','Alkaline phosphatase','521','NULL','U/L','35 - 104','35','104','H','HP','2012-03-01 00:00:00.000','NULL','Revision2-09March2012-Date of Birth-Old value-30-Jan-1995-New value-30-Jan-1994.Reason for change-Site request.Revision1-01March2012-Gender-Old value-M-New value-F. Reason for change-Site request.NA: Result not available as specimen hemolysed. Incorrect Transportation-Specimen received in frozen condition instead of ambient condition.','NULL')
on Debug i get an error on cmd.executeNonQuery like this: "Syntax error converting datetime from character string"
TimoYang
Contributor
3732 Points
1275 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 04:37 AM|LINK
Plz execute ur codes directly in the SQL management Studio first——
I suspect whether there's something wrong with your datetime——
Change to this：1994/01/30
Anish V.S.
Member
522 Points
123 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 04:40 AM|LINK
Date should be in mm/dd/yyyyy format
aamir1010
Member
5 Points
25 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 04:41 AM|LINK
hi timoYang,
yes i had executed the code in SQL management studio and get the same errro.
i am passing the query from .aspx web page as mentioned above,
how do i make change it in my query and execute it.
TimoYang
Contributor
3732 Points
1275 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 04:50 AM|LINK
Check ur DateTime formation——Plz just use:
MM/dd/yyyy
dd/MM/yyyy
aamir1010
Member
5 Points
25 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 05:02 AM|LINK
can u explain how to convert in Insert query the string field into datetime as i am passing it from .aspx page.
Anish V.S.
Member
522 Points
123 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 05:07 AM|LINK
string dt1="1994-01-30";
string[] dob=dt1.split('-');
DateTime db=dob[1]+"/"+dob[0]+"/"+dob[2];//do same for all datatime
Insert into LabResults(InvId,PatId,PatInitials,VisitId,VisitCode,AccId,Sex,DOB,ColDt,ColTm,TestGroup,TestName,NValue,CValue,Unit,RRange,NRRanL,NRRanH,Flags,Alerts,RepDt,Comments,RevHis,Lastupdatedon) values ('1','S1999','XYZ','1','Visit 1 (Day 0)','77100000','Female','"+db+"','2012-02-26 00:00:00.000','1899-12-30 10:00:00.000','Biochemistry','Alkaline phosphatase','521','NULL','U/L','35 - 104','35','104','H','HP','2012-03-01 00:00:00.000','NULL','Revision2-09March2012-Date of Birth-Old value-30-Jan-1995-New value-30-Jan-1994.Reason for change-Site request.Revision1-01March2012-Gender-Old value-M-New value-F. Reason for change-Site request.NA: Result not available as specimen hemolysed. Incorrect Transportation-Specimen received in frozen condition instead of ambient condition.','NULL')
aamir1010
Member
5 Points
25 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 05:12 AM|LINK
Anish,
the values which i am getting as string is from a dataset like : '"+ result.Tables["LabResults"].Rows[i].ItemArray.GetValue(4)+"'
so how to convert in such condition.
Anish V.S.
Member
522 Points
123 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 05:19 AM|LINK
simply do this,
Datetime dob=DateTime.Parse( result.Tables["LabResults"].Rows[i].ItemArray.GetValue(4));
and attach this value to query string
india.999
Member
225 Points
116 Posts
Re: Syntax error converting datetime from character string
Aug 03, 2012 05:21 AM|LINK
your insert problem finish or not? r u selecting now from database?