asp_databases thread: R: Re: R: RE: R: RE: Syntax Error when adding a new field to- record
then, we know that resourceid is text, so intResourceiD is not an int:
try this code
SQL = "Select ScheduleID FROM tblSchedule where ResourceID = '" _
& intResourceID & "' And ........
good luck!
-----Messaggio originale-----
Da: rosenzl@o... [mailto:rosenzl@o...]
Inviato: domenica 20 gennaio 2002 21.04
A: ASP Databases
Oggetto: [asp_databases] Re: R: RE: R: RE: Syntax Error when adding a
new field to- record
Larry Said:
I did the Query DB using the actual numbers to what was on my local
machine. I plugged in ResourceID = 4, ScheduleDate = 01/17/2002, and
CustomerID = 21.
SELECT tblSchedule.ResourceID, tblSchedule.ScheduleDate,
tblSchedule.CustomerID
FROM tblSchedule
WHERE (((tblSchedule.ResourceID)=[enter resourceid]) AND
((tblSchedule.ScheduleDate)=[enter date]) AND ((tblSchedule.CustomerID)
[enter customerid]));
This was the result!
ResourceID ScheduleDate CustomerID
Dr. John Smith M.D. 1/17/2002 21
Now what?
> can you have the access db on your machine?
> if so, try to create this query in the db query, to see
> if the error is due to sql or to asp...
>
>
>
> -----Messaggio originale-----
> Da: rosenzl@o... [mailto:rosenzl@o...]
> Inviato: domenica 20 gennaio 2002 20.18
> A: ASP Databases
> Oggetto: [asp_databases] RE: R: RE: Syntax Error when adding a new field
> to- record
>
>
> 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').
> > >
>
> $subst('Email.Unsub').
>
$subst('Email.Unsub').
View Cart
