UPDATE in loop?

I'm storing a calculation in a variable and I want to then take that variable
and update the db right after it is calculated. I can get all 172 records to
show the overall calculation on the page, but not in the db.

I'm not having any success, it keeps all of the db records at .000

Below is my code, TIA.


sos1 = number field in db

teamid = 1
recs = 172

For x = teamid to recs

<%
winper = 0

id = teamid
count = 1
games = 0

overall = winper/games ' code not shown will calculate this

Response.Write("")
Response.Write(RSsched.Fields("Team") & " " &
FormatNumber(overall,3,0))
Response.Write("")

Response.Write("")

SQLUPDATE="UPDATE Teams SET "
SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid

conn.execute(SQLUPDATE)

teamid = teamid + 1

Next

%>

Re: UPDATE in loop?

ks wrote:
> I'm storing a calculation in a variable and I want to then take that
> variable and update the db

What database?

> right after it is calculated. I can get
> all 172 records to show the overall calculation on the page, but not
> in the db.
>
> I'm not having any success, it keeps all of the db records at .000
>
> Below is my code, TIA.
>
>
> sos1 = number field in db
>
> teamid = 1
> recs = 172
>
> For x = teamid to recs
>
> <%
> winper = 0
>
> id = teamid
> count = 1
> games = 0
>
> overall = winper/games ' code not shown will calculate this
>
> Response.Write("")
> Response.Write(RSsched.Fields("Team") &
> " " & FormatNumber(overall,3,0))
> Response.Write("")
>
> Response.Write("")
>
> SQLUPDATE="UPDATE Teams SET "
> SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
> SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid
>

If you response.write the contents of this variable and run the resulting
sql statement in your database, does it give you the result you desire?



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: UPDATE in loop?

I can see the value(s) that I want from the variable "overall" on the asp
page. The problem that I'm having is that it doesn't update the db. Should
I try using an INSERT instead of an UPDATE?


"Bob Barrows [MVP]" wrote:

> ks wrote:
> > I'm storing a calculation in a variable and I want to then take that
> > variable and update the db
>
> What database?
>
> > right after it is calculated. I can get
> > all 172 records to show the overall calculation on the page, but not
> > in the db.
> >
> > I'm not having any success, it keeps all of the db records at .000
> >
> > Below is my code, TIA.
> >
> >
> > sos1 = number field in db
> >
> > teamid = 1
> > recs = 172
> >
> > For x = teamid to recs
> >
> > <%
> > winper = 0
> >
> > id = teamid
> > count = 1
> > games = 0
> >
> > overall = winper/games ' code not shown will calculate this
> >
> > Response.Write("")
> > Response.Write(RSsched.Fields("Team") &
> > " " & FormatNumber(overall,3,0))
> > Response.Write("")
> >
> > Response.Write("")
> >
> > SQLUPDATE="UPDATE Teams SET "
> > SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
> > SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid
> >
>
> If you response.write the contents of this variable and run the resulting
> sql statement in your database, does it give you the result you desire?
>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: UPDATE in loop?

ks wrote:
> I can see the value(s) that I want from the variable "overall" on the
> asp page.

I don't believe that answers the question that I asked. The only way to
debug a sql statement is to see what it is. And the only way to see the
result of a concatenation operation is to print out that result.

You also have not answered the "what database?" question.

> The problem that I'm having is that it doesn't update the
> db. Should I try using an INSERT instead of an UPDATE?
>

I don't know. Are we talking about updating an existing record, or inserting
a new record?

>
> "Bob Barrows [MVP]" wrote:
>
>> ks wrote:
>>> I'm storing a calculation in a variable and I want to then take that
>>> variable and update the db
>>
>> What database?
>>
>>> right after it is calculated. I can get
>>> all 172 records to show the overall calculation on the page, but not
>>> in the db.
>>>
>>> I'm not having any success, it keeps all of the db records at .000
>>>
>>> Below is my code, TIA.
>>>
>>>
>>> sos1 = number field in db
>>>
>>> teamid = 1
>>> recs = 172
>>>
>>> For x = teamid to recs
>>>
>>> <%
>>> winper = 0
>>>
>>> id = teamid
>>> count = 1
>>> games = 0
>>>
>>> overall = winper/games ' code not shown will calculate this
>>>
>>> Response.Write("")
>>> Response.Write(RSsched.Fields("Team") &
>>> " " & FormatNumber(overall,3,0))
>>> Response.Write("")
>>>
>>> Response.Write("")
>>>
>>> SQLUPDATE="UPDATE Teams SET "
>>> SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
>>> SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid
>>>
>>
>> If you response.write the contents of this variable and run the
>> resulting sql statement in your database, does it give you the
>> result you desire?
>>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: UPDATE in loop?

This is an Access database.

Here is the link to the page that displays the calculations.

http://www33.brinkster.com/keithshar/new/soscalc.asp


I'm trying to update the default db number (sos1) for each record with the
number calculated from this page using that SQLUPDATE statement before the
teamid increases.

I can post more code if needed.


"Bob Barrows [MVP]" wrote:

> ks wrote:
> > I can see the value(s) that I want from the variable "overall" on the
> > asp page.
>
> I don't believe that answers the question that I asked. The only way to
> debug a sql statement is to see what it is. And the only way to see the
> result of a concatenation operation is to print out that result.
>
> You also have not answered the "what database?" question.
>
> > The problem that I'm having is that it doesn't update the
> > db. Should I try using an INSERT instead of an UPDATE?
> >
>
> I don't know. Are we talking about updating an existing record, or inserting
> a new record?
>
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> ks wrote:
> >>> I'm storing a calculation in a variable and I want to then take that
> >>> variable and update the db
> >>
> >> What database?
> >>
> >>> right after it is calculated. I can get
> >>> all 172 records to show the overall calculation on the page, but not
> >>> in the db.
> >>>
> >>> I'm not having any success, it keeps all of the db records at .000
> >>>
> >>> Below is my code, TIA.
> >>>
> >>>
> >>> sos1 = number field in db
> >>>
> >>> teamid = 1
> >>> recs = 172
> >>>
> >>> For x = teamid to recs
> >>>
> >>> <%
> >>> winper = 0
> >>>
> >>> id = teamid
> >>> count = 1
> >>> games = 0
> >>>
> >>> overall = winper/games ' code not shown will calculate this
> >>>
> >>> Response.Write("")
> >>> Response.Write(RSsched.Fields("Team") &
> >>> " " & FormatNumber(overall,3,0))
> >>> Response.Write("")
> >>>
> >>> Response.Write("")
> >>>
> >>> SQLUPDATE="UPDATE Teams SET "
> >>> SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
> >>> SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid
> >>>
> >>
> >> If you response.write the contents of this variable and run the
> >> resulting sql statement in your database, does it give you the
> >> result you desire?
> >>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: UPDATE in loop?

Look. We can get no further without seeing the sql that results from the
concatenation.
Do a Response.Write SQLUPDATE followed by Response.End and look at the
result.

ks wrote:
> This is an Access database.
>
> Here is the link to the page that displays the calculations.
>
> http://www33.brinkster.com/keithshar/new/soscalc.asp
>
>
> I'm trying to update the default db number (sos1) for each record
> with the number calculated from this page using that SQLUPDATE
> statement before the teamid increases.
>
> I can post more code if needed.
>
>
> "Bob Barrows [MVP]" wrote:
>
>> ks wrote:
>>> I can see the value(s) that I want from the variable "overall" on
>>> the asp page.
>>
>> I don't believe that answers the question that I asked. The only way
>> to debug a sql statement is to see what it is. And the only way to
>> see the result of a concatenation operation is to print out that
>> result.
>>
>> You also have not answered the "what database?" question.
>>
>>> The problem that I'm having is that it doesn't update the
>>> db. Should I try using an INSERT instead of an UPDATE?
>>>
>>
>> I don't know. Are we talking about updating an existing record, or
>> inserting a new record?
>>
>>>
>>> "Bob Barrows [MVP]" wrote:
>>>
>>>> ks wrote:
>>>>> I'm storing a calculation in a variable and I want to then take
>>>>> that variable and update the db
>>>>
>>>> What database?
>>>>
>>>>> right after it is calculated. I can get
>>>>> all 172 records to show the overall calculation on the page, but
>>>>> not in the db.
>>>>>
>>>>> I'm not having any success, it keeps all of the db records at .000
>>>>>
>>>>> Below is my code, TIA.
>>>>>
>>>>>
>>>>> sos1 = number field in db
>>>>>
>>>>> teamid = 1
>>>>> recs = 172
>>>>>
>>>>> For x = teamid to recs
>>>>>
>>>>> <%
>>>>> winper = 0
>>>>>
>>>>> id = teamid
>>>>> count = 1
>>>>> games = 0
>>>>>
>>>>> overall = winper/games ' code not shown will calculate this
>>>>>
>>>>> Response.Write("")
>>>>> Response.Write(RSsched.Fields("Team") &
>>>>> " " & FormatNumber(overall,3,0))
>>>>> Response.Write("")
>>>>>
>>>>> Response.Write("")
>>>>>
>>>>> SQLUPDATE="UPDATE Teams SET "
>>>>> SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
>>>>> SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid
>>>>>
>>>>
>>>> If you response.write the contents of this variable and run the
>>>> resulting sql statement in your database, does it give you the
>>>> result you desire?
>>>>
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: UPDATE in loop?

This is what I'm getting on that page.

1040 Slashers .491
UPDATE Teams05 SET sos1 = '.491' WHERE TeamID = 1


This is after I changed the code to include a format of the number.

Thanks.

"Bob Barrows [MVP]" wrote:

> Look. We can get no further without seeing the sql that results from the
> concatenation.
> Do a Response.Write SQLUPDATE followed by Response.End and look at the
> result.
>
> ks wrote:
> > This is an Access database.
> >
> > Here is the link to the page that displays the calculations.
> >
> > http://www33.brinkster.com/keithshar/new/soscalc.asp
> >
> >
> > I'm trying to update the default db number (sos1) for each record
> > with the number calculated from this page using that SQLUPDATE
> > statement before the teamid increases.
> >
> > I can post more code if needed.
> >
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> ks wrote:
> >>> I can see the value(s) that I want from the variable "overall" on
> >>> the asp page.
> >>
> >> I don't believe that answers the question that I asked. The only way
> >> to debug a sql statement is to see what it is. And the only way to
> >> see the result of a concatenation operation is to print out that
> >> result.
> >>
> >> You also have not answered the "what database?" question.
> >>
> >>> The problem that I'm having is that it doesn't update the
> >>> db. Should I try using an INSERT instead of an UPDATE?
> >>>
> >>
> >> I don't know. Are we talking about updating an existing record, or
> >> inserting a new record?
> >>
> >>>
> >>> "Bob Barrows [MVP]" wrote:
> >>>
> >>>> ks wrote:
> >>>>> I'm storing a calculation in a variable and I want to then take
> >>>>> that variable and update the db
> >>>>
> >>>> What database?
> >>>>
> >>>>> right after it is calculated. I can get
> >>>>> all 172 records to show the overall calculation on the page, but
> >>>>> not in the db.
> >>>>>
> >>>>> I'm not having any success, it keeps all of the db records at .000
> >>>>>
> >>>>> Below is my code, TIA.
> >>>>>
> >>>>>
> >>>>> sos1 = number field in db
> >>>>>
> >>>>> teamid = 1
> >>>>> recs = 172
> >>>>>
> >>>>> For x = teamid to recs
> >>>>>
> >>>>> <%
> >>>>> winper = 0
> >>>>>
> >>>>> id = teamid
> >>>>> count = 1
> >>>>> games = 0
> >>>>>
> >>>>> overall = winper/games ' code not shown will calculate this
> >>>>>
> >>>>> Response.Write("")
> >>>>> Response.Write(RSsched.Fields("Team") &
> >>>>> " " & FormatNumber(overall,3,0))
> >>>>> Response.Write("")
> >>>>>
> >>>>> Response.Write("")
> >>>>>
> >>>>> SQLUPDATE="UPDATE Teams SET "
> >>>>> SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
> >>>>> SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid
> >>>>>
> >>>>
> >>>> If you response.write the contents of this variable and run the
> >>>> resulting sql statement in your database, does it give you the
> >>>> result you desire?
> >>>>
> >>
> >> --
> >> Microsoft MVP -- ASP/ASP.NET
> >> Please reply to the newsgroup. The email account listed in my From
> >> header is my spam trap, so I don't check it very often. You will get
> >> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: UPDATE in loop?

OK, now we're getting somewhere. If you open your database in Access, create
a new query in Design view, switch to SQL View, paste in that sql statement
and attempt to run it, does it do what you want?

Wait. Is "1040 Slashers .491" part of the SQLUPDATE variable? Or is that
the result of an earlier response.write?

ks wrote:
> This is what I'm getting on that page.
>
> 1040 Slashers .491
> UPDATE Teams05 SET sos1 = '.491' WHERE TeamID = 1
>
>
> This is after I changed the code to include a format of the number.
>
> Thanks.
>
> "Bob Barrows [MVP]" wrote:
>
>> Look. We can get no further without seeing the sql that results from
>> the concatenation.
>> Do a Response.Write SQLUPDATE followed by Response.End and look at
>> the result.
>>
>> ks wrote:
>>> This is an Access database.
>>>
>>> Here is the link to the page that displays the calculations.
>>>
>>> http://www33.brinkster.com/keithshar/new/soscalc.asp
>>>
>>>
>>> I'm trying to update the default db number (sos1) for each record
>>> with the number calculated from this page using that SQLUPDATE
>>> statement before the teamid increases.
>>>
>>> I can post more code if needed.
>>>
>>>
>>> "Bob Barrows [MVP]" wrote:
>>>
>>>> ks wrote:
>>>>> I can see the value(s) that I want from the variable "overall" on
>>>>> the asp page.
>>>>
>>>> I don't believe that answers the question that I asked. The only
>>>> way to debug a sql statement is to see what it is. And the only
>>>> way to see the result of a concatenation operation is to print out
>>>> that result.
>>>>
>>>> You also have not answered the "what database?" question.
>>>>
>>>>> The problem that I'm having is that it doesn't update the
>>>>> db. Should I try using an INSERT instead of an UPDATE?
>>>>>
>>>>
>>>> I don't know. Are we talking about updating an existing record, or
>>>> inserting a new record?
>>>>
>>>>>
>>>>> "Bob Barrows [MVP]" wrote:
>>>>>
>>>>>> ks wrote:
>>>>>>> I'm storing a calculation in a variable and I want to then take
>>>>>>> that variable and update the db
>>>>>>
>>>>>> What database?
>>>>>>
>>>>>>> right after it is calculated. I can get
>>>>>>> all 172 records to show the overall calculation on the page, but
>>>>>>> not in the db.
>>>>>>>
>>>>>>> I'm not having any success, it keeps all of the db records at
>>>>>>> .000
>>>>>>>
>>>>>>> Below is my code, TIA.
>>>>>>>
>>>>>>>
>>>>>>> sos1 = number field in db
>>>>>>>
>>>>>>> teamid = 1
>>>>>>> recs = 172
>>>>>>>
>>>>>>> For x = teamid to recs
>>>>>>>
>>>>>>> <%
>>>>>>> winper = 0
>>>>>>>
>>>>>>> id = teamid
>>>>>>> count = 1
>>>>>>> games = 0
>>>>>>>
>>>>>>> overall = winper/games ' code not shown will calculate this
>>>>>>>
>>>>>>> Response.Write("")
>>>>>>> Response.Write(RSsched.Fields("Team") &
>>>>>>> " " & FormatNumber(overall,3,0))
>>>>>>> Response.Write("")
>>>>>>>
>>>>>>> Response.Write("")
>>>>>>>
>>>>>>> SQLUPDATE="UPDATE Teams SET "
>>>>>>> SQLUPDATE=SQLUPDATE & "sos1 = '" & overall & "' "
>>>>>>> SQLUPDATE=SQLUPDATE & "WHERE TeamID = " & teamid
>>>>>>>
>>>>>>
>>>>>> If you response.write the contents of this variable and run the
>>>>>> resulting sql statement in your database, does it give you the
>>>>>> result you desire?
>>>>>>
>>>>
>>>> --
>>>> Microsoft MVP -- ASP/ASP.NET
>>>> Please reply to the newsgroup. The email account listed in my From
>>>> header is my spam trap, so I don't check it very often. You will
>>>> get a quicker response by posting to the newsgroup.
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: UPDATE in loop?

That's from an earlier response.write. It's not going to work in Access
because the calculation won't work in Access.

Here is the code for the whole page.


<%

' Beginning of code for SOS

teamid = 1

SQLrecs = "SELECT COUNT(TeamID) FROM Teams05"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath(I left this out)

Set RSrecs = server.createobject("ADODB.Recordset")
Set RSrecs = conn.execute(SQLrecs)

recs = RSrecs.Fields(0)

For x = teamid to recs

SQL = "SELECT * FROM Games05 WHERE AwayTeam = " & teamid & " OR HomeTeam = "
& teamid

Set RS = server.createobject("ADODB.Recordset")
set RS=conn.execute(SQL)

SQLsched = "SELECT * FROM Teams05 WHERE TeamID = " & teamid

Set RSsched = server.createobject("ADODB.Recordset")
Set RSsched = conn.execute(SQLsched)

%>

Re: UPDATE in loop?

ks wrote:
> That's from an earlier response.write. It's not going to work in
> Access because the calculation won't work in Access.

What calculation? There's no calculation in that sql statement.
>

>>> UPDATE Teams05 SET sos1 = '.491' WHERE TeamID = 1

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"