Having problems with Multiple-step OLE DB

[Kevin Andreshak]

Aloha,

I'm learning .asp on the fly while trying to move this project over from
access to MySQL.

this is my DSN:

sDSN = "DSN=****;UID=****;PWD=*****"

I am connecting to a MySQL database using .asp.

Below is my code:

________________________________________________

Set Conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT ScoresID, EventID, TimeStamp, Completed, MSL, Setup, Saved,
SavedDate, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37,
38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, ImportantArray FROM Scores WHERE
ScoresID = " & Request.Form("ID")

Conn.Open sDSN
rs.Open SQL, Conn, adOpenStatic, adLockPessimistic

rs("1") = request.form("1")
rs("2") = request.form("2")
rs("3") = request.form("3")
rs("4") = request.form("4")
rs("5") = request.form("5")
rs("6") = request.form("6")
rs("7") = request.form("7")
rs("8") = request.form("8")
rs("9") = request.form("9")
rs("10") = request.form("10")

rs("11") = request.form("11")
rs("12") = request.form("12")
rs("13") = request.form("13")
rs("14") = request.form("14")
rs("15") = request.form("15")
rs("16") = request.form("16")
rs("17") = request.form("17")
rs("18") = request.form("18")
rs("19") = request.form("19")
rs("20") = request.form("20")

rs("21") = request.form("21")
rs("22") = request.form("22")
rs("23") = request.form("23")
rs("24") = request.form("24")
rs("25") = request.form("25")
rs("26") = request.form("26")
rs("27") = request.form("27")
rs("28") = request.form("28")
rs("29") = request.form("29")
rs("30") = request.form("30")

rs("31") = request.form("31")
rs("32") = request.form("32")
rs("33") = request.form("33")
rs("34") = request.form("34")
rs("35") = request.form("35")
rs("36") = request.form("36")
rs("37") = request.form("37")
rs("38") = request.form("38")
rs("39") = request.form("39")
rs("40") = request.form("40")

rs("41") = request.form("41")
rs("42") = request.form("42")
rs("43") = request.form("43")
rs("44") = request.form("44")
rs("45") = request.form("45")
rs("46") = request.form("46")
rs("47") = request.form("47")
rs("48") = request.form("48")

rs("ImportantArray") = sImportantArray

rs("Saved") = True
rs("SavedDate") = Now()

for each item in request.form
if request.form(item) = "2" then
nSameCount=nSameCount + 1
end if
next


for each item in request.form
if request.form(item) = "1" then
ntoomany=ntoomany + 1
end if
next


rs.update

rs.close
set rs=nothing

__________________________________________________ __

I am trying to run the page, and I'm getting the following Error:
____________________________________

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.

/bm/bm_test/response.asp, line 66
_____________________________________


LINE 66 is " rs("1") = request.form("1") "

the query above has all the table fields

the field 1 is an INT(11)

All this code worked for Access, but I'm having to move it to MySQL.

Should I modify my field names?

thx for any help you can give me.




BELOW is the table config I grabbed from MySQL:

__________________

#
# Table structure for table 'Scores'
#

CREATE TABLE Scores (
ScoresID int(10) unsigned NOT NULL auto_increment,
EventID int(11) default NULL,
TimeStamp datetime default NULL,
Completed enum('True','False') default NULL,
MSL int(11) default NULL,
SetUp datetime default NULL,
Saved enum('True','False') default NULL,
SavedDate datetime default NULL,
1 int(11) default NULL,
2 int(11) default NULL,
3 int(11) default NULL,
4 int(11) default NULL,
5 int(11) default NULL,
6 int(11) default NULL,
7 int(11) default NULL,
8 int(11) default NULL,
9 int(11) default NULL,
10 int(11) default NULL,
11 int(11) default NULL,
12 int(11) default NULL,
13 int(11) default NULL,
14 int(11) default NULL,
15 int(11) default NULL,
16 int(11) default NULL,
17 int(11) default NULL,
18 int(11) default NULL,
19 int(11) default NULL,
20 int(11) default NULL,
21 int(11) default NULL,
22 int(11) default NULL,
23 int(11) default NULL,
24 int(11) default NULL,
25 int(11) default NULL,
26 int(11) default NULL,
27 int(11) default NULL,
28 int(11) default NULL,
29 int(11) default NULL,
30 int(11) default NULL,
31 int(11) default NULL,
32 int(11) default NULL,
33 int(11) default NULL,
34 int(11) default NULL,
35 int(11) default NULL,
36 int(11) default NULL,
37 int(11) default NULL,
38 int(11) default NULL,
39 int(11) default NULL,
40 int(11) default NULL,
41 int(11) default NULL,
42 int(11) default NULL,
43 int(11) default NULL,
44 int(11) default NULL,
45 int(11) default NULL,
46 int(11) default NULL,
47 int(11) default NULL,
48 int(11) default NULL,
ImportantArray varchar(255) default NULL,
PRIMARY KEY (ScoresID)
) TYPE=MyISAM;


Thanks in advance.

Kevin Andreshak

[Aaron Bertrand [MVP]]

> sDSN = "DSN=****;UID=****;PWD=*****"

Ugh, do you need to use a DSN?

> SQL = "SELECT ScoresID, EventID, TimeStamp, Completed, MSL, Setup, Saved,
> SavedDate, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36,

37,

> 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, ImportantArray FROM Scores

WHERE

> ScoresID = " & Request.Form("ID")

Do you really have columns named 1, 2, 3, 4, 5, ...? Have you read anything
about normalization and sane database design? Are you sure
Request.Form("ID") has a value?

> rs("1") = request.form("1")

Are you trying to update an existing row? You should use an UPDATE
statement, instead of all this recordset activity...

> rs("2") = request.form("2")
> rs("3") = request.form("3")
> rs("4") = request.form("4")

If you really must do it this way, you could use a loop instead of
hardcoding each of these declarations...

> rs("ImportantArray") = sImportantArray

Where did sImportantArray come from?

> rs("Saved") = True

Are you sure MySQL knows what the keyword "True" means? I suggest using 1
or 0 instead of the keywords True and False.

A

[Kevin Andreshak]

Thanks for the quick response Aaron.

Just want you to know that this is my first experience with .asp.

The main problem I am having is.. I did not create any of this code, and my

lack of .asp knowledge has driven me to seek help.

It is my job to take whatI have and make it work with MySQL.

All this code currently works in ACCESS.

Goal is to make it work in MySQL.

I have tried to answer to your response the best I can below.

thanks again. =)



"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message

news:<uzDKUT$TDHA.2308@TK2MSFTNGP12.phx.gbl>...

> > sDSN = "DSN=****;UID=****;PWD=*****"

>

> Ugh, do you need to use a DSN?

The hosting company requires it for all MySQL usage.

> > SQL = "SELECT ScoresID, EventID, TimeStamp, Completed, MSL, Setup,

Saved,

> > SavedDate, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,

18,

> > 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36,

> 37,

> > 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, ImportantArray FROM Scores

> WHERE

> > ScoresID = " & Request.Form("ID")

>

> Do you really have columns named 1, 2, 3, 4, 5, ...? Have you read

anything

> about normalization and sane database design? Are you sure

> Request.Form("ID") has a value?

>

I totally agree with you on the column names, however the original

programmer uses this in other pages as a way to create Array.

Yes Request.Form("ID") has a value.

> > rs("1") = request.form("1")

>

> Are you trying to update an existing row? You should use an UPDATE

> statement, instead of all this recordset activity...

I am trying to update the column "1" with the request.form("1")

I have an rs.update at the end of the listing.

Could you explain what you mean?

> > rs("2") = request.form("2")

> > rs("3") = request.form("3")

> > rs("4") = request.form("4")

>

> If you really must do it this way, you could use a loop instead of

> hardcoding each of these declarations...

I guess I would make a loop, if i could figure out how to get just one line

of it to read fine.. /frown

It always crashes on the first line of the rs("1") = request.form("1")

> > rs("ImportantArray") = sImportantArray

>

> Where did sImportantArray come from?

There is an array made from the answers.. I just put that in. so you would

see the end of my CONN. Sorry that I included it.

> > rs("Saved") = True

>

> Are you sure MySQL knows what the keyword "True" means? I suggest using 1

> or 0 instead of the keywords True and False.

I can try to work this in for sure.



Thanks again Aarron.

I spent over 20 hours on modifications to the .asp pages to make this thing

work..

Only about 10% of errors I can't figure out what to do.. this one being one

of them.

Kevin Andreshak

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).

Version: 6.0.502 / Virus Database: 300 - Release Date: 7/18/2003

---

Incoming mail is certified Virus Free.

Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).

Version: 6.0.502 / Virus Database: 300 - Release Date: 7/18/2003

[Tom B]

Request.Form("1") may be coming in as a variant of subtype string, which
could be failing.

An update query is what you want to do as it appears that you are only
trying to update a record in the database. You don't appear to be using the
returned recordset for anything.

Example (not tested)
Dim SQL
Dim formField1
Dim formField2

formField1= CInt(Request.Form("1"))
formField2=CInt(Request.Form("2"))
SQL=" UPDATE Scores Set 1=" & formField1 & ", 2=" & formField2 ......... &
"WHERE ScoresID=" & Request.Form("ID")

Conn.Execute SQL

As I've not tested this, I'd be concerned that "setting" 1=3 could be a
problem. Is it possible to fix the database?
As you are converting from Access to mySQL, I would think now is the time to
do it.
Perhaps you could create a query with the name Scores which returns the
fields you've got now, so that the rest of the pages work?

"Kevin Andreshak" <kevin@odenex.com> wrote in message
news:effcL7$TDHA.2196@TK2MSFTNGP12.phx.gbl...

> Thanks for the quick response Aaron.
>
> Just want you to know that this is my first experience with .asp.
>
> The main problem I am having is.. I did not create any of this code, and

my

>
> lack of .asp knowledge has driven me to seek help.
>
> It is my job to take whatI have and make it work with MySQL.
>
> All this code currently works in ACCESS.
>
> Goal is to make it work in MySQL.
>
> I have tried to answer to your response the best I can below.
>
> thanks again. =)
>
>
>
> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
>
> news:<uzDKUT$TDHA.2308@TK2MSFTNGP12.phx.gbl>...
>

> > > sDSN = "DSN=****;UID=****;PWD=*****"

>

> >

>

> > Ugh, do you need to use a DSN?

>
>
>
> The hosting company requires it for all MySQL usage.
>
>
>

> > > SQL = "SELECT ScoresID, EventID, TimeStamp, Completed, MSL, Setup,

>
> Saved,
>

> > > SavedDate, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,

>
> 18,
>

> > > 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35,

36,

>

> > 37,

>

> > > 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, ImportantArray FROM Scores

>

> > WHERE

>

> > > ScoresID = " & Request.Form("ID")

>

> >

>

> > Do you really have columns named 1, 2, 3, 4, 5, ...? Have you read

>
> anything
>

> > about normalization and sane database design? Are you sure

>

> > Request.Form("ID") has a value?

>

> >

>
> I totally agree with you on the column names, however the original
>
> programmer uses this in other pages as a way to create Array.
>
> Yes Request.Form("ID") has a value.
>
>
>
>
>
>
>

> > > rs("1") = request.form("1")

>

> >

>

> > Are you trying to update an existing row? You should use an UPDATE

>

> > statement, instead of all this recordset activity...

>
> I am trying to update the column "1" with the request.form("1")
>
> I have an rs.update at the end of the listing.
>
> Could you explain what you mean?
>
>
>
>
>
>
>

> > > rs("2") = request.form("2")

>

> > > rs("3") = request.form("3")

>

> > > rs("4") = request.form("4")

>

> >

>

> > If you really must do it this way, you could use a loop instead of

>

> > hardcoding each of these declarations...

>
>
>
> I guess I would make a loop, if i could figure out how to get just one

line

>
> of it to read fine.. /frown
>
> It always crashes on the first line of the rs("1") = request.form("1")
>
>
>

> > > rs("ImportantArray") = sImportantArray

>

> >

>

> > Where did sImportantArray come from?

>
>
>
> There is an array made from the answers.. I just put that in. so you would
>
> see the end of my CONN. Sorry that I included it.
>
>
>
>
>
>
>

> > > rs("Saved") = True

>

> >

>

> > Are you sure MySQL knows what the keyword "True" means? I suggest using

1

>

> > or 0 instead of the keywords True and False.

>
> I can try to work this in for sure.
>
>
>
> Thanks again Aarron.
>
> I spent over 20 hours on modifications to the .asp pages to make this

thing

>
> work..
>
> Only about 10% of errors I can't figure out what to do.. this one being

one

>
> of them.
>
> Kevin Andreshak
>
> ---
>
> Outgoing mail is certified Virus Free.
>
> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
>
> Version: 6.0.502 / Virus Database: 300 - Release Date: 7/18/2003
>
> ---
>
> Incoming mail is certified Virus Free.
>
> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
>
> Version: 6.0.502 / Virus Database: 300 - Release Date: 7/18/2003
>
>

[Jeff Cochran]

On Mon, 21 Jul 2003 17:11:58 -1000, "Kevin Andreshak"
<kevin@odenex.com> wrote:

>Thanks for the quick response Aaron.
>Just want you to know that this is my first experience with .asp.
>The main problem I am having is.. I did not create any of this code, and my
>lack of .asp knowledge has driven me to seek help.
>It is my job to take whatI have and make it work with MySQL.
>All this code currently works in ACCESS.
>Goal is to make it work in MySQL.

Well, how about telling us what error you get that makes you think it
*isn't* working in MySQL?

The obvious is whether or not you changed the DSN for MySQL (still
wouldn't use a DSN...) and whether the MySQL table has all the exact
same elements as the Access one did. Permissions I assume are set the
same/correctly, though I may be assuming too much.

>I have tried to answer to your response the best I can below.
>thanks again. =)
>
>
>
>"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
>
>news:<uzDKUT$TDHA.2308@TK2MSFTNGP12.phx.gbl>...
>

>> > sDSN = "DSN=****;UID=****;PWD=*****"

>> Ugh, do you need to use a DSN?

>The hosting company requires it for all MySQL usage.

Strange hosting company. But you may be able to use ADO in spite of
your host.

>> > SQL = "SELECT ScoresID, EventID, TimeStamp, Completed, MSL, Setup,

>Saved,

>> > SavedDate, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,

>18,

>> > 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36,

>> 37,

>> > 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, ImportantArray FROM Scores

>> WHERE

>> > ScoresID = " & Request.Form("ID")

>> Do you really have columns named 1, 2, 3, 4, 5, ...? Have you read

>anything

>> about normalization and sane database design? Are you sure
>> Request.Form("ID") has a value?

>I totally agree with you on the column names, however the original
>programmer uses this in other pages as a way to create Array.
>Yes Request.Form("ID") has a value.

>> > rs("1") = request.form("1")

>> Are you trying to update an existing row? You should use an UPDATE
>> statement, instead of all this recordset activity...

>I am trying to update the column "1" with the request.form("1")
>I have an rs.update at the end of the listing.
>Could you explain what you mean?

Use a SQL UPDATE statement instead of the rs.update. Something like:

Column1 = request.form("1")
SQL = "UPDATE Scores SET 1 = " & column1 & " WHERE ScoresID = " &
Request.Form("ID")

>> > rs("2") = request.form("2")
>> > rs("3") = request.form("3")
>> > rs("4") = request.form("4")

>> If you really must do it this way, you could use a loop instead of
>> hardcoding each of these declarations...

>I guess I would make a loop, if i could figure out how to get just one line
>of it to read fine.. /frown

Show us what you've tried. I'm not sure playing with the record set
is the way to go, but a loop should be perfectly simple here.

>It always crashes on the first line of the rs("1") = request.form("1")

What is "it" referring to, your loop? The script in general? And by
crashes, do you mean it dumps with an error, locks the system, spews
smoke and flames from the keyboard...?

>> > rs("ImportantArray") = sImportantArray

>> Where did sImportantArray come from?

>There is an array made from the answers.. I just put that in. so you would
>see the end of my CONN. Sorry that I included it.

>> > rs("Saved") = True

>> Are you sure MySQL knows what the keyword "True" means? I suggest using 1
>> or 0 instead of the keywords True and False.

>I can try to work this in for sure.

>I spent over 20 hours on modifications to the .asp pages to make this thing
>work..

Ugh. How about breaking it into chunks, and it might help if we had
the original working code to look at.

>Only about 10% of errors I can't figure out what to do.. this one being one
>of them.

Which one is "this one"?

One trick would be to use Response.Write to output your variables and
SQL statements to make sure you are really using what you think you
are. And if it's just a conversion from Access to MySQL, the code
other than the DSN and some SQL should be identical.

Jeff