Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 12th, 2004, 01:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default insert multiple records

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




  #2 (permalink)  
Old October 12th, 2004, 02:00 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

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) & "#"
  #3 (permalink)  
Old October 12th, 2004, 02:03 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

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 & "')"
  #4 (permalink)  
Old October 12th, 2004, 09:37 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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).

Code:
sql = sql & "'" & Request.Form("empno").Item(x) & "','" & aposit & "',' & astrdate & ',' & aenddate & ','" & acontract & "')"
' The code in RED would be assumed as constant here, which should be changed as

sql = sql & "'" & Request.Form("empno").Item(x) & "','" & aposit & "','" & astrdate & "','" & aenddate & "','" & acontract & "')"
In short, you are missing 4 double quotes within the code marked in red.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
  #5 (permalink)  
Old October 13th, 2004, 02:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






  #6 (permalink)  
Old October 13th, 2004, 02:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #7 (permalink)  
Old October 13th, 2004, 03:15 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

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
  #8 (permalink)  
Old October 13th, 2004, 03:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




  #9 (permalink)  
Old October 13th, 2004, 04:31 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Could you please print the query (sql) and post it ?
  #10 (permalink)  
Old October 13th, 2004, 05:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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









Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert multiple records from form lorirobn Access 2 April 9th, 2007 03:08 PM
insert multiple records into a table from values Deepak Chauhan Oracle 3 May 12th, 2006 10:35 PM
Insert multiple records mateenmohd Access 13 November 2nd, 2004 09:15 AM
Insert multiple records in a Inner Join Table rylemer Access VBA 0 July 19th, 2004 03:54 PM
Best way to insert multiple records koo9 ADO.NET 2 June 28th, 2003 08:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.