I use following coding to insert the multiple records.
but it give syntax error line 62
astrdate and aenddate data type is datetime in the sql server 2000
table.


for x = 1 to Request.Form("empno").Count

next

for x = 1 to Request.Form("empno").Count

sql = "insert into assignments2 (empno,aposit,astrdate,aenddate,acontract) values ("

sql = sql & "'" & Request.Form("empno").Item(x) & "','" & aposit & "',' & astrdate & ',' & aenddate & ','" & acontract & "')"

cn.Execute sql // line 62

next


Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting datetime from character string.
/sas/projassign/add_passignedit.asp, line 62


Please help to solve the error.

Mateen

How you are getting astrdate and aenddate values? Are they some values taken from db ?

While inserting date into SQL server, you have to take month, date and year parts of date (to be inserted) and then form a string in mm/dd/yyyy format. Then insert that string with ' as delimiter.

So try this

"'" & month(astrdate) & "/" & day(astrdate) & "/" & year(astrdate) & "'"

instead of "'" & astrdate & "'".

|||ly try

"'" & month(aenddate) & "/" & day(aenddate) & "/" & year(aenddate) & "'"

instead of "'" & aenddate & "'".

If your database is MS Access, you have to use # as the delimiter instead of '.
i.e use

"#" & month(astrdate) & "/" & day(astrdate) & "/" & year(astrdate) & "#"

and

"#" & month(aenddate) & "/" & day(aenddate) & "/" & year(aenddate) & "#"

I also find you have omitted some number of " in the following line. Please check that also.

sql = sql & "'" & Request.Form("empno").Item(x) & "','" & aposit & "',' & astrdate & ',' & aenddate & ','" & acontract & "')"

As you have stated that you use sql server 2000 and as per the error states, I could see that the SQL statement is not constructed as required in case of those date values(astrdate and aenddate).

In short, you are missing 4 double quotes within the code marked in red.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks for response.
astrdate, aenddate values getting from the db.
I have to insert multiple records. I retrieve previous multiple records
from db, after changing dates etc, I insert the multiple records to the tables.


I usw following coding in first page (to insert the multiple records, retrieve previous records just change the dates etc and insert).

Response.Write("<td>")
Response.Write("<input type=text size=5 id=empno name=empno value='" & rs2("empno") &"' font face=arial size=3>")
Response.Write("</td>")

Response.Write("<td>")
Response.Write("<input type=text size=22 id=aposit name=aposit value='" & rs2("aposit") &"' font face=arial size=3>")
Response.Write("</td>")

Response.Write("<td>")
Response.Write("<input type=text size=10 id=acontract name=acontract value='" & rs2("acontract") &"' font face=arial size=3>")
Response.Write("</td>")

Response.Write("<td>")
Response.Write("<input type=text size=10 id=astrdate name=astrdate value='" & rs2("astrdate") &"' font face=arial size=3>")
Response.Write("</td>")


Response.Write("<td>")
Response.Write("<input type=text size=10 id=aenddate name=aenddate value='" & rs2("aenddate") &"' font face=arial size=3>")
Response.Write("</td>")

........


after update the multiple records, I click insert button to insert records, it move to second page.
which coding like this.

empno=Request.Form("empno")
aposit=Request.Form("aposit")
acontract=Request.Form("acontract")
astrdate=Request.Form("astrdate")
aenddate=Request.Form("aenddate")

for x = 1 to Request.Form("empno").Count

next

for x = 1 to Request.Form("empno").Count

    sql = "insert into assignments2 (empno,aposit,astrdate,aendate,acontract) values ("
    sql = sql & " ' " & Request.Form("empno").Item(x) & "','" & aposit & "',' " & month(astrdate) & "/" & day(astrdate) & "/" & year(astrdate) & " ',' " & month(aenddate) & "/" &
                           day(aenddate) & "/" & year(aenddate) & " ','" & acontract & " ' )"

    cn.Execute sql // line 50

next

it give following error.

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'astrdate'
/sas/projassign/add_passignedit.asp, line 50

when I use this.

for x = 1 to Request.Form("empno").Count

    sql = "insert into assignments2 (empno,aposit,astrdate,aenddate,acontract) values ("
    sql = sql & "'" & Request.Form("empno").Item(x) & "',' " & aposit & " ',' " & astrdate & " ',' " & aenddate & " ','" & acontract & "')"

    cn.Execute sql // line 50

next

it give this as previous error.

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting datetime from character string.
/sas/projassign/add_passignedit.asp, line 50


astrdate,aenddate data type in sql server 2000 is datetime.
this problem only in to insert multiple records. when I insert one record
there is no error, same date datatype have use.


regards.

Mateen

Can you post the values here that comes in those two variables?

Looks like you got to use convert function to convert that to yyyy-mm-dd format to solve that.

Cheers!

_________________________
- Vijay G
Strive for Perfection

You are taking astrdate and aenddate from a form. isn't it? You are using textboxes to take these values.

In a textbox one can enter date in any fashion he wants. You have to impose some format restrictions for it.

Also you have to make sure that illegal dates are not given(31/04/2004).

You have to make sure that the value entered for these fields are in proper date format. Then you can cut the values from these values to obtain date, month and year parts.

An another reason you are getting the error is that the the value of request.form("astrdate") or request.form("aenddate") may be empty. Are you checking this and validating before trying to insert into database. I think you should.

Best of luck.

Madhu

------------------------------------------
ps. I didn't understand the purpose of this loop.

for x = 1 to Request.Form("empno").Count

next

I copy paste the received values from the first page.


22972312 , 82108 , 36699 , 81967 , 03068 , 08979 , 34996 , 34753 , 37747 , 82073 229, 221, 229, Main Office, 229, 229, 982, 2002, Main Office, Main OfficeResident Engineer, Quantity Surveyor, Surveyor, Inspector (As-built), Inspector, Inspector, Technician, Secretary, Clerk / Expeditor, Driver8/1/2004, 8/1/2004, 8/1/2004, 8/1/2004, 8/1/2004, 8/1/2004, 8/1/2004, 8/1/2004, 8/1/2004, 8/1/20048/31/2004, 8/31/2004, 8/31/2004, 8/31/2004, 8/31/2004, 8/31/2004, 8/31/2004, 8/31/2004, 8/31/2004, 8/31/2004First, Second, First, First, Second, First, First, Second, Second, First


ie In the one Contract No. 229 different employees are working,
I have to change the dates etc. and remaining data almost same. and then insert the records.

I also try to use yyyy/mm/dd but same error type mismatch ie.

sql = "insert into assignments2 (empno,aposit,astrdate,aendate,acontract) values ("
    sql = sql & "'" & Request.Form("empno").Item(x) & "','" & aposit & "','" & year(astrdate) & "/" & month(astrdate) & "/" & day(astrdate) & "','" & year(aenddate) & "/" & month(aenddate) & "/" & day(aenddate) & "','" & acontract & "')"



Mateen

Could you please print the query (sql) and post it ?

Query is

ssql2="SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract,

assignments.astrdate, assignments.aenddate,assignments.cshift
FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno)
INNER JOIN assignments ON employees.empno=assignments.empno
where approvals.contractno='"& objpagingRS("contractno") &" '
and aenddate between '"&astrdate&"' and '"&aenddate&"' ;

set rs2=cn.Execute(ssql2)


Mateen