|
 |
asp_databases thread: Syntax Error when adding a new field to record
Message #1 by rosenzl@o... on Wed, 16 Jan 2002 16:29:37
|
|
Can somebody give me the syntax to add a 3rd field to the insert into
statement's values? Everything I tried failed. It works fine without the
CustomerID. The new field is a numeric intCustomerID and does exist in the
Access database as the last column. I need it following dDate.
Thanks for your help
Larry
If RS.BOF And RS.EOF Then
'Add a new One
SQL = "Insert into tblSchedule (" &_
"ResourceID, " &_
"ScheduleDate, " &_
"CustomerID) "
SQL = SQL & "VALUES ( " & intResourceID & ", #" & dDate & "#)"
End If
Conn.Execute(SQL)
Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 16 Jan 2002 17:37:14 +0100
|
|
SQL = "Insert into tblSchedule (" &_
"ResourceID, " &_
"ScheduleDate, " &_
"CustomerID) "
SQL = SQL & "VALUES ( " & intResourceID & ", #" & dDate & "#, " &
intCustomerID & ")"
You don't have to worry about the order of the columns in the database as
long as you use the syntax you use here (telling first the field names, then
the values to add).
If you're still having problems, try and response.write the sql string,
examine it and copy it into the query analyzer.
'****RESPONSE.WRITE SQL STRING****
SQL = SQL & "VALUES ... ...")"
response.write SQL
response.end
'*********************************
-Kim
> -----Original Message-----
> From: rosenzl@o... [mailto:rosenzl@o...]
> Sent: 16. januar 2002 16:30
> To: ASP Databases
> Subject: [asp_databases] Syntax Error when adding a new field to record
>
>
> Can somebody give me the syntax to add a 3rd field to the insert into
> statement's values? Everything I tried failed. It works fine without the
> CustomerID. The new field is a numeric intCustomerID and does exist in the
> Access database as the last column. I need it following dDate.
>
> Thanks for your help
>
> Larry
>
> If RS.BOF And RS.EOF Then
> 'Add a new One
> SQL = "Insert into tblSchedule (" &_
> "ResourceID, " &_
> "ScheduleDate, " &_
> "CustomerID) "
> SQL = SQL & "VALUES ( " & intResourceID & ", #" & dDate & "#)"
> End If
> Conn.Execute(SQL)
>
> $subst('Email.Unsub').
>
Message #3 by "Drew, Ron" <RDrew@B...> on Wed, 16 Jan 2002 12:54:11 -0500
|
|
Try
If RS.BOF And RS.EOF Then
'Add a new One
SQL =3D "Insert into tblSchedule (" &_
"ResourceID, " &_
"ScheduleDate, " &_
"CustomerID) " &_
"VALUES (" & intResourceID & ", #" & dDate &_
"#, " & intCustomerID & ")"
End If
Conn.Execute(SQL)
-----Original Message-----
From: rosenzl@o... [mailto:rosenzl@o...]
Sent: Wednesday, January 16, 2002 11:30 AM
To: ASP Databases
Subject: [asp_databases] Syntax Error when adding a new field to record
Can somebody give me the syntax to add a 3rd field to the insert into
statement's values? Everything I tried failed. It works fine without the
CustomerID. The new field is a numeric intCustomerID and does exist in
the Access database as the last column. I need it following dDate.
Thanks for your help
Larry
If RS.BOF And RS.EOF Then
'Add a new One
SQL =3D "Insert into tblSchedule (" &_
"ResourceID, " &_
"ScheduleDate, " &_
"CustomerID) "
SQL =3D SQL & "VALUES ( " & intResourceID & ", #" & dDate & "#)"
End If
Conn.Execute(SQL)
$subst('Email.Unsub').
Message #4 by rosenzl@o... on Sat, 19 Jan 2002 14:08:54
|
|
Ron, Thank you for your help. It did work. Now I have the same problem
with the intcustomerid in the Set RS =: Could you please see what's wrong
with the syntax? The statement works fine without the customerid.
Thanks again, You da man
Larry
Set RS = Conn.Execute("Select ScheduleID FROM tblSchedule where ResourceID
= " _
& intResourceID & " And ScheduleDate = #" & dDate & "#" And customerid = "
& intcustomerid)
> Try
> If RS.BOF And RS.EOF Then
> 'Add a new One
> SQL =3D "Insert into tblSchedule (" &_
> "ResourceID, " &_
> "ScheduleDate, " &_
> "CustomerID) " &_
> "VALUES (" & intResourceID & ", #" & dDate &_
> "#, " & intCustomerID & ")"
> End If
> Conn.Execute(SQL)
>
>
> -----Original Message-----
> From: rosenzl@o... [mailto:rosenzl@o...]
> Sent: Wednesday, January 16, 2002 11:30 AM
> To: ASP Databases
> Subject: [asp_databases] Syntax Error when adding a new field to record
>
>
> Can somebody give me the syntax to add a 3rd field to the insert into
> statement's values? Everything I tried failed. It works fine without the
> CustomerID. The new field is a numeric intCustomerID and does exist in
> the Access database as the last column. I need it following dDate.
>
> Thanks for your help
>
> Larry
>
> If RS.BOF And RS.EOF Then
> 'Add a new One
> SQL =3D "Insert into tblSchedule (" &_
> "ResourceID, " &_
> "ScheduleDate, " &_
> "CustomerID) "
> SQL =3D SQL & "VALUES ( " & intResourceID & ", #" & dDate & "#)"
> End If
> Conn.Execute(SQL)
>
> $subst('Email.Unsub').
Message #5 by lrosenzweig <rosenzl@o...> on Sat, 19 Jan 2002 14:41:10 -0500
|
|
-----Original Message-----
From: lrosenzweig [mailto:rosenzl@o...]
Sent: Saturday, January 19, 2002 12:16 PM
To: Giovanni Salucci
Subject: RE: Syntax Error when adding a new field to record
Giovanni, Yes, intcustomerid does have a value. I know this because when a
record does get inserted (The CustomerID is populated). It's only when I add
the customerid to the Where clause that I have the problem. I get "Syntax or
Scripting error". Below is a printout from my database. This works fine,
when I do not include the CustomerID in the Where clause.
ScheduleID ResourceID ScheduleDate CustomerID
17 Dr. John Smith M.D. 1/17/2002 21
-----Original Message-----
From: Giovanni Salucci [mailto:g.salucci@n...]
Sent: Saturday, January 19, 2002 12:05 PM
To: lrosenzweig
Subject: R: Syntax Error when adding a new field to record
you cannot see the exact message of error?
the only thing i see: are you sure that intcustomerid has a value
before the query?
-----Messaggio originale-----
Da: lrosenzweig [mailto:rosenzl@o...]
Inviato: sabato 19 gennaio 2002 17.41
A: Giovanni Salucci
Oggetto: RE: Syntax Error when adding a new field to record
Salucci, Below is all the code involved. Please notice the commented out
line following "Set RS". When I use that line insead of the one below it,
all works fine. When I use the line below the commened line, I get "Server
or Scripting error".
'Check Parent Schedule Record to see if a new one needs to be added.
'(Must be done for New and Existing ScheduleDetail Updates)
If bSQLServer Then
Set RS = Conn.Execute("Select ScheduleID FROM
tblSchedule where
ResourceID = " _
& intResourceID & " And ScheduleDate = '" &
dDate & "'")
Else
Set RS = Conn.Execute("Select ScheduleID FROM
tblSchedule where
ResourceID = " _
' & intResourceID & " And ScheduleDate = #" &
dDate & "#")
& intResourceID & " And ScheduleDate = #" &
dDate & "# And
customerid = " & intcustomerid)
End If
If RS.BOF And RS.EOF Then
'Add a new One
SQL = "Insert into tblSchedule (" &_
"ResourceID, " &_
"ScheduleDate, " &_
"CustomerID) "
If bSQLServer Then
SQL = SQL & "VALUES ( " &
intResourceID & ", '" & dDate & "', " &
intCustomerID & ")"
Else
SQL = SQL & "VALUES ( " &
intResourceID & ", #" & dDate & "#, " &
intCustomerID & ")"
End If
Conn.Execute(SQL)
-----Original Message-----
From: Giovanni Salucci [mailto:g.salucci@n...]
Sent: Saturday, January 19, 2002 11:26 AM
To: rosenzl@o...
Subject: RE: Syntax Error when adding a new field to record
Importance: High
try with this structure (SQL line in one single line), Conn is your
connection :
SQL = " Select ScheduleID FROM tblSchedule where ResourceID = " &
intResourceID & " And ScheduleDate = #" & dDate & "# And customerid = " &
intcustomerid
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, Conn, 1, 1
RS.movefirst
Do while not RS.EOF
..........
RS.movenext
Loop
RS.Close
If you can explain what you want, i can help you...
please give me the error you receive...
In order to test the sql statement before execute, if this is a routine
page,
you can create ANOTHER page with the same connection data, and test it, or
test the statement direct to the database (access o SQL???)
HTH
Giovanni
-----Messaggio originale-----
Da: rosenzl@o... [mailto:rosenzl@o...]
Inviato: sabato 19 gennaio 2002 14.09
A: ASP Databases
Oggetto: [asp_databases] RE: Syntax Error when adding a new field to
record
Salucci, Thanks for your help, but this page cannot be displayed, it just
executes routines. I took out the # as you requested, but I still get a
scripting error. Did you look at the last part of the statement? Any other
ideas?
Thanks,
Larry
Message #6 by "Drew, Ron" <RDrew@B...> on Tue, 22 Jan 2002 14:07:47 -0500
|
|
Comment out and add the following and send the results.
' Conn.Execute(SQL)
Response.write SQL
-----Original Message-----
From: rosenzl@o... [mailto:rosenzl@o...]
Sent: Saturday, January 19, 2002 9:09 AM
To: ASP Databases
Subject: [asp_databases] RE: Syntax Error when adding a new field to
record
Ron, Thank you for your help. It did work. Now I have the same problem
with the intcustomerid in the Set RS =3D: Could you please see what's
wrong
with the syntax? The statement works fine without the customerid.
Thanks again, You da man
Larry
Set RS =3D Conn.Execute("Select ScheduleID FROM tblSchedule where
ResourceID
=3D " _
& intResourceID & " And ScheduleDate =3D #" & dDate & "#" And customerid
=3D
"
& intcustomerid)
> Try
> If RS.BOF And RS.EOF Then
> 'Add a new One
> SQL =3D3D "Insert into tblSchedule (" &_
> "ResourceID, " &_
> "ScheduleDate, " &_
> "CustomerID) " &_
> "VALUES (" & intResourceID & ", #" & dDate &_
> "#, " & intCustomerID & ")"
> End If
> Conn.Execute(SQL)
>
>
> -----Original Message-----
> From: rosenzl@o... [mailto:rosenzl@o...]=3D20
> Sent: Wednesday, January 16, 2002 11:30 AM
> To: ASP Databases
> Subject: [asp_databases] Syntax Error when adding a new field to
> record
>
>
> Can somebody give me the syntax to add a 3rd field to the insert into
> statement's values? Everything I tried failed. It works fine without
> the CustomerID. The new field is a numeric intCustomerID and does
> exist in the Access database as the last column. I need it following
> dDate.=3D20
>
> Thanks for your help
>
> Larry
>
> If RS.BOF And RS.EOF Then
> 'Add a new One
> SQL =3D3D "Insert into tblSchedule (" &_
> "ResourceID, " &_
> "ScheduleDate, " &_
> "CustomerID) "
> SQL =3D3D SQL & "VALUES ( " & intResourceID & ", #" & dDate &
"#)"
> End If
> Conn.Execute(SQL)
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or=3D20 to
> unsubscribe send a blank email to
> $subst('Email.Unsub').
$subst('Email.Unsub').
Message #7 by "Drew, Ron" <RDrew@B...> on Tue, 22 Jan 2002 14:09:31 -0500
|
|
Sorry, I did not read it correctly...I would build the variable for the
select and then execute it. That way you can do the response.write on
the variable to check the syntax
-----Original Message-----
From: rosenzl@o... [mailto:rosenzl@o...]
Sent: Saturday, January 19, 2002 9:09 AM
To: ASP Databases
Subject: [asp_databases] RE: Syntax Error when adding a new field to
record
Ron, Thank you for your help. It did work. Now I have the same problem
with the intcustomerid in the Set RS =3D: Could you please see what's
wrong
with the syntax? The statement works fine without the customerid.
Thanks again, You da man
Larry
Set RS =3D Conn.Execute("Select ScheduleID FROM tblSchedule where
ResourceID
=3D " _
& intResourceID & " And ScheduleDate =3D #" & dDate & "#" And customerid
=3D
"
& intcustomerid)
> Try
> If RS.BOF And RS.EOF Then
> 'Add a new One
> SQL =3D3D "Insert into tblSchedule (" &_
> "ResourceID, " &_
> "ScheduleDate, " &_
> "CustomerID) " &_
> "VALUES (" & intResourceID & ", #" & dDate &_
> "#, " & intCustomerID & ")"
> End If
> Conn.Execute(SQL)
>
>
> -----Original Message-----
> From: rosenzl@o... [mailto:rosenzl@o...]=3D20
> Sent: Wednesday, January 16, 2002 11:30 AM
> To: ASP Databases
> Subject: [asp_databases] Syntax Error when adding a new field to
> record
>
>
> Can somebody give me the syntax to add a 3rd field to the insert into
> statement's values? Everything I tried failed. It works fine without
> the CustomerID. The new field is a numeric intCustomerID and does
> exist in the Access database as the last column. I need it following
> dDate.=3D20
>
> Thanks for your help
>
> Larry
>
> If RS.BOF And RS.EOF Then
> 'Add a new One
> SQL =3D3D "Insert into tblSchedule (" &_
> "ResourceID, " &_
> "ScheduleDate, " &_
> "CustomerID) "
> SQL =3D3D SQL & "VALUES ( " & intResourceID & ", #" & dDate &
"#)"
> End If
> Conn.Execute(SQL)
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or=3D20 to
> unsubscribe send a blank email to
> $subst('Email.Unsub').
$subst('Email.Unsub').
|
|
|
View Cart
