|
 |
asp_databases thread: R: RE: Syntax Error when adding a new field to record
Message #1 by "Giovanni Salucci" <g.salucci@n...> on Sat, 19 Jan 2002 15:29:26 +0100
|
|
this line
& intResourceID & " And ScheduleDate = #" & dDate & "#" And customerid = "
must be
& intResourceID & " And ScheduleDate = #" & dDate & "# And customerid = "
in order to avoid this errors, i suggest to declare your sql statement
in one line, then test it with a response.write before execute it... :)
SQL = "Select ScheduleID FROM tblSchedule where ..... "
response.write SQL
'set rs = Conn.Execute(SQL)
HTH
-----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
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').
$subst('Email.Unsub').
Message #2 by lrosenzweig <rosenzl@o...> on Sat, 19 Jan 2002 10:46:44 -0500
|
|
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
-----Original Message-----
From: Giovanni Salucci [mailto:g.salucci@n...]
Sent: Saturday, January 19, 2002 9:29 AM
To: ASP Databases
Subject: [asp_databases] R: RE: Syntax Error when adding a new field to
record
this line
& intResourceID & " And ScheduleDate = #" & dDate & "#" And customerid = "
must be
& intResourceID & " And ScheduleDate = #" & dDate & "# And customerid = "
in order to avoid this errors, i suggest to declare your sql statement
in one line, then test it with a response.write before execute it... :)
SQL = "Select ScheduleID FROM tblSchedule where ..... "
response.write SQL
'set rs = Conn.Execute(SQL)
HTH
-----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
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').
$subst('Email.Unsub').
$subst('Email.Unsub').
Message #3 by "Kim Iwan Hansen" <kimiwan@k...> on Sat, 19 Jan 2002 17:25:14 +0100
|
|
SQL = "Select ScheduleID FROM tblSchedule where" &_
" ResourceID = " & intResourceID &_
" And ScheduleDate = #" & dDate & "#" &_
" And customerid = " & intcustomerid & ");"
'response.write SQL 'for troubleshooting
'response.end 'for troubleshooting
Set RS = Conn.Execute(SQL) 'run the sql string
response.write'ing your sql string often serves as a great help when you get
errors that are directly or indirectly related to it. you can add
response.end right after it to prevent the rest of the page executing (as it
could well cause additional errors when you leave out the database call)
-Kim
> -----Original Message-----
> From: lrosenzweig [mailto:rosenzl@o...]
> Sent: 19. januar 2002 16:47
> To: ASP Databases
> Subject: [asp_databases] RE: R: 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
>
> -----Original Message-----
> From: Giovanni Salucci [mailto:g.salucci@n...]
> Sent: Saturday, January 19, 2002 9:29 AM
> To: ASP Databases
> Subject: [asp_databases] R: RE: Syntax Error when adding a new field to
> record
>
>
> this line
> & intResourceID & " And ScheduleDate = #" & dDate & "#" And customerid = "
>
> must be
>
> & intResourceID & " And ScheduleDate = #" & dDate & "# And customerid = "
>
> in order to avoid this errors, i suggest to declare your sql statement
> in one line, then test it with a response.write before execute it... :)
>
> SQL = "Select ScheduleID FROM tblSchedule where ..... "
> response.write SQL
> 'set rs = Conn.Execute(SQL)
>
>
> HTH
>
> -----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
>
>
> 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').
>
> $subst('Email.Unsub').
>
>
> $subst('Email.Unsub').
>
>
> $subst('Email.Unsub').
>
Message #4 by rosenzl@o... on Sat, 19 Jan 2002 19:56:29
|
|
Giovanni, I replied to your prior email and it was rejected - not sent.
Here is what I said!
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?
>
> this line
> & intResourceID & " And ScheduleDate = #" & dDate & "#" And customerid
= "
>
> must be
>
> & intResourceID & " And ScheduleDate = #" & dDate & "# And customerid = "
>
> in order to avoid this errors, i suggest to declare your sql statement
> in one line, then test it with a response.write before execute it... :)
>
> SQL = "Select ScheduleID FROM tblSchedule where ..... "
> response.write SQL
> 'set rs = Conn.Execute(SQL)
>
>
> HTH
>
> -----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
>
>
> 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').
>
> $subst('Email.Unsub').
>
Message #5 by "Giovanni Salucci" <g.salucci@n...> on Sat, 19 Jan 2002 22:04:48 +0100
|
|
hey, the example you post me, ResourceID is not an int, but a text fields...
please control the structure of db....
ciao!
Giovanni
-----Messaggio originale-----
Da: lrosenzweig [mailto:rosenzl@o...]
Inviato: sabato 19 gennaio 2002 20.41
A: ASP Databases
Oggetto: [asp_databases] RE: Syntax Error when adding a new field to
record
-----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
$subst('Email.Unsub').
Message #6 by lrosenzweig <rosenzl@o...> on Sat, 19 Jan 2002 16:31:58 -0500
|
|
Giovanni, the primary issue is the intcustomerid/CustomerID - When added to
the Where clause, then I have the problem.
ResourceID has a data type = Number, field size = Long Integer, Reguired
No, Indexed = Yes(Duplicates OK).
CustomerID has a data type = Number, field size = Long Integer, Reguired
No, Indexed = No
1. Without the CustomerID in the where clause, all works fine.
2. I have also noticed that the ResourceID in the columns are text, but I
have no explanation as to why. Just prior to the Insert Into statement, I
did the following; Knowing what the value should be
If intResourceID = 4
Response.end
End If
It hit a true condition and my normal page was blank. The actual value in
the field is the resourcename. I have no idea why.
I was going to work on that later. I don't feel it has anything to do with
the CustomerID. I inherited the application and have been making changes to
it for better functionality.
ResourceID ResourceName
1 Conference Room A1
2 Computer Projector
3 Auditorium
4 Dr. John Smith M.D.
5 Dinner Reservations
6 Tanning Bed
7 Moped Rental
8 Jet Ski Rental
9 Hygienist
Thanks for your help, I am all ears!
Larry
-----Original Message-----
From: Giovanni Salucci [mailto:g.salucci@n...]
Sent: Saturday, January 19, 2002 4:05 PM
To: ASP Databases
Subject: [asp_databases] R: RE: Syntax Error when adding a new field to
record
hey, the example you post me, ResourceID is not an int, but a text fields...
please control the structure of db....
ciao!
Giovanni
-----Messaggio originale-----
Da: lrosenzweig [mailto:rosenzl@o...]
Inviato: sabato 19 gennaio 2002 20.41
A: ASP Databases
Oggetto: [asp_databases] RE: Syntax Error when adding a new field to
record
-----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
$subst('Email.Unsub').
$subst('Email.Unsub').
Message #7 by rosenzl@o... on Sun, 20 Jan 2002 18:47:54
|
|
Giovanni, each email you send to me from your own email address, causes a
problem for me to reply. I get Permanent Delivery Failure. I need you to
respond via wrox. This is the last info you sent me. I will put my
comments after it.
ou write:
I have also noticed that the ResourceID in the columns are text, but I
have no explanation as to why.....
i will try a little debug of your query: are you sure the same not happen
to the field CustomeriD?
The fact that whithout the where statement on customerid the programs run
it menas that the problem is on the definition of this field or in the
query.
can you insert a response.write
response.end
before running the query? if not, can you create a NEW asp page that
do the same things and execute it????
if so, please write the exact error code....
HTH
Giovanni
This is what I did, just prior to the Query;
response.write intresourceid
response.write dDate
response.write intcustomerid
response.end
It displayed 4 for the intresourceid, which is correct.
It displayed 01/21/2002 for the dDate, which is correct.
It displayed 21 for the intcustomerid, which is correct.
Then I cut and pasted the same response.writes and response.end after the
insert was committed. The results were correct, However, the resourceid in
the database has the resourcename. It makes no sense to me. All of this
was done, without including the customerid in the Where clause.
HELP!
Larry
> Giovanni, the primary issue is the intcustomerid/CustomerID - When added
to
> the Where clause, then I have the problem.
>
> ResourceID has a data type = Number, field size = Long Integer, Reguired
> No, Indexed = Yes(Duplicates OK).
> CustomerID has a data type = Number, field size = Long Integer, Reguired
> No, Indexed = No
>
> 1. Without the CustomerID in the where clause, all works fine.
> 2. I have also noticed that the ResourceID in the columns are text, but I
> have no explanation as to why. Just prior to the Insert Into statement, I
> did the following; Knowing what the value should be
>
> If intResourceID = 4
> Response.end
> End If
> It hit a true condition and my normal page was blank. The actual
value in
> the field is the resourcename. I have no idea why.
> I was going to work on that later. I don't feel it has anything to
do with
> the CustomerID. I inherited the application and have been making changes
to
> it for better functionality.
>
> ResourceID ResourceName
> 1 Conference Room A1
> 2 Computer Projector
> 3 Auditorium
> 4 Dr. John Smith M.D.
> 5 Dinner Reservations
> 6 Tanning Bed
> 7 Moped Rental
> 8 Jet Ski Rental
> 9 Hygienist
>
>
> Thanks for your help, I am all ears!
>
> Larry
>
>
> -----Original Message-----
> From: Giovanni Salucci [mailto:g.salucci@n...]
> Sent: Saturday, January 19, 2002 4:05 PM
> To: ASP Databases
> Subject: [asp_databases] R: RE: Syntax Error when adding a new field to
> record
>
> hey, the example you post me, ResourceID is not an int, but a text
fields...
> please control the structure of db....
>
> ciao!
> Giovanni
>
>
>
> -----Messaggio originale-----
> Da: lrosenzweig [mailto:rosenzl@o...]
> Inviato: sabato 19 gennaio 2002 20.41
> A: ASP Databases
> Oggetto: [asp_databases] RE: Syntax Error when adding a new field to
> record
>
>
>
>
> -----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
>
>
> $subst('Email.Unsub').
>
>
> $subst('Email.Unsub').
>
Message #8 by rosenzl@o... on Sun, 20 Jan 2002 20:17:35
|
|
Larry responded to Giovanni with:
If I put the customerid in the search I get "Server or Syntax Error". This
occurs before any of the code is executed. Therefore, any Response.Write
will be ignored. I am using an Access 2000 Database. The structure is as
follows:
Tblcustomers is parent of Tblscheduledetails 1 to many
Tblschedule is Parent of Tblscheduledetails 1 to many
Tblresources is parent of Tblschedule 1 to many
Let me know if you think this syntax is OK;
Set RS = Conn.Execute("Select ScheduleID FROM tblSchedule where ResourceID
= " _
intResourceID & " And ScheduleDate = #" & dDate & "# And customerid = " &
intcustomerid)
As you know, the customerid is populated fine in the tblschedule record,
if I don't use the customerid in the Where clause.
Larry
Giovanni said;
Try to do this
1) add a response.write sql before execute your sql
and post me the result
2) what database are you using? Can you see the structure of tables
involved in your query?
3) add a response.write "query executed" and response.end just after
the query, to control if the error is really due to the line we are
discussing...
tell me the results!
Ciao
> Giovanni, each email you send to me from your own email address, causes
a
> problem for me to reply. I get Permanent Delivery Failure. I need you to
> respond via wrox. This is the last info you sent me. I will put my
> comments after it.
>
> You write:
> I have also noticed that the ResourceID in the columns are text, but I
> have no explanation as to why.....
>
> i will try a little debug of your query: are you sure the same not happen
> to the field CustomeriD?
> The fact that whithout the where statement on customerid the programs run
> it menas that the problem is on the definition of this field or in the
> query.
>
> can you insert a response.write
> response.end
>
> before running the query? if not, can you create a NEW asp page that
> do the same things and execute it????
>
> if so, please write the exact error code....
>
> HTH
> Giovanni
>
>
> This is what I did, just prior to the Query;
>
> response.write intresourceid
> response.write dDate
> response.write intcustomerid
> response.end
>
> It displayed 4 for the intresourceid, which is correct.
> It displayed 01/21/2002 for the dDate, which is correct.
> It displayed 21 for the intcustomerid, which is correct.
>
> Then I cut and pasted the same response.writes and response.end after
the
> insert was committed. The results were correct, However, the resourceid
in
> the database has the resourcename. It makes no sense to me. All of this
> was done, without including the customerid in the Where clause.
>
> HELP!
>
> Larry
>
>
>
>
>
>
>
> > Giovanni, the primary issue is the intcustomerid/CustomerID - When
added
> to
> > the Where clause, then I have the problem.
> >
> > ResourceID has a data type = Number, field size = Long Integer,
Reguired
>
> > No, Indexed = Yes(Duplicates OK).
> > CustomerID has a data type = Number, field size = Long Integer,
Reguired
>
> > No, Indexed = No
> >
> > 1. Without the CustomerID in the where clause, all works fine.
> > 2. I have also noticed that the ResourceID in the columns are text,
but I
> > have no explanation as to why. Just prior to the Insert Into
statement, I
> > did the following; Knowing what the value should be
> >
> > If intResourceID = 4
> > Response.end
> > End If
> > It hit a true condition and my normal page was blank. The actual
> value in
> > the field is the resourcename. I have no idea why.
> > I was going to work on that later. I don't feel it has anything to
> do with
> > the CustomerID. I inherited the application and have been making
changes
> to
> > it for better functionality.
> >
> > ResourceID ResourceName
> > 1 Conference Room A1
> > 2 Computer Projector
> > 3 Auditorium
> > 4 Dr. John Smith M.D.
> > 5 Dinner Reservations
> > 6 Tanning Bed
> > 7 Moped Rental
> > 8 Jet Ski Rental
> > 9 Hygienist
> >
> >
> > Thanks for your help, I am all ears!
> >
> > Larry
> >
> >
> > -----Original Message-----
> > From: Giovanni Salucci [mailto:g.salucci@n...]
> > Sent: Saturday, January 19, 2002 4:05 PM
> > To: ASP Databases
> > Subject: [asp_databases] R: RE: Syntax Error when adding a new field to
> > record
> >
> > hey, the example you post me, ResourceID is not an int, but a text
> fields...
> > please control the structure of db....
> >
> > ciao!
> > Giovanni
> >
> >
> >
> > -----Messaggio originale-----
> > Da: lrosenzweig [mailto:rosenzl@o...]
> > Inviato: sabato 19 gennaio 2002 20.41
> > A: ASP Databases
> > Oggetto: [asp_databases] RE: Syntax Error when adding a new field to
> > record
> >
> >
> >
> >
> > -----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
> >
> >
> > $subst('Email.Unsub').
> >
> >
> > $subst('Email.Unsub').
> >
|
|
|
View Cart
