MySQL hit to batabase with ASP

ChrisScott · November 15th, 2005, 01:19 PM

EOF / BOF check requirements should be exactly the same, is your database content exactly the same on both platforms when you experience errors?

With the section of code in your original post, you should not need the check at all - the Do loop will only get executed if not .EOF anyway and you appear to have no other code requiring a record outside of this loop.

You are missing a comma in your Execute statement - adCmdText should be the last parameter.

With this particular piece of code, everything looks pretty nailed down once you make the above changes.

crmpicco · November 16th, 2005, 10:41 AM

Code:

set rsspecialinfo = con.execute("SELECT exp_mouse_over FROM `net_leg2` WHERE contract_id = '"& contractId &"' AND fare_id = '" & fareId & "'", adCmdText)

Hi again, chris.

yeah, ive got a comma after my query. is that ok above? i have googled adCmdText and checked MSDN, cant find many resources on it??

Picco

www.crmpicco.co.uk

ChrisScott · November 16th, 2005, 11:05 AM

Hey Picco,

You need two commas, the second optional parameter is a records affected variable - this can be useful when running update queries etc.

The MSDN description of the Execute method will explains the use of the third (options) parameter where you can specify adCmdText: http://msdn.microsoft.com/library/de...cnnexecute.asp

Cheers,

Chris

crmpicco · November 16th, 2005, 11:10 AM

Code:

set rsspecialinfo = con.execute("SELECT exp_mouse_over FROM `net_leg2` WHERE contract_id = '"& contractId &"' AND fare_id = '" & fareId & "'", , adCmdText)

that should work ok? am i right?

www.crmpicco.co.uk

ChrisScott · November 16th, 2005, 12:36 PM

Looks fine to me. You tried it?

crmpicco · November 18th, 2005, 05:56 AM

yeah, no errors. thanks

www.crmpicco.co.uk

crmpicco · November 24th, 2005, 05:48 AM

Code:

        rst = con.execute("UPDATE agencymarkup_air SET ad_eco_amt_netnet ='"&Request.form(airVal & "_ad_eco_amt_net")&"', "_
        &"ch_eco_amt_netnet ='"&Request.form(airVal & "_ch_eco_amt_net")&"',in_eco_amt_netnet ='"&Request.form(airVal & "_in_eco_amt_net")&"', "_
        &"ad_bus_amt_netnet ='"&Request.form(airVal & "_ad_bus_amt_net")&"',ch_bus_amt_netnet ='"&Request.form(airVal & "_ch_bus_amt_net")&"', "_
        &"in_bus_amt_netnet ='"&Request.form(airVal & "_in_bus_amt_net")&"',ad_fir_amt_netnet ='"&Request.form(airVal & "_ad_fir_amt_net")&"', "_
        &"ch_fir_amt_netnet ='"&Request.form(airVal & "_ch_fir_amt_net")&"',in_fir_amt_netnet ='"&Request.form(airVal & "_in_fir_amt_net")&"' "_
        &"WHERE company_id = '" & consolidatorid & "' AND agencyid = '"&agencyid&"' AND air_cd = '" & airVal & "'", ,adCmdText)

hi again chris,
i am still looking at ways to speed up my connection/execution to the mysql db. how does this query look?

cheers.

picco

www.crmpicco.co.uk

ChrisScott · November 24th, 2005, 07:10 AM

Hey Picco,

The first thing I would suggest is that you validate the data from the Request.Form, this will not speed things up, but imagine the havoc I could cause were I to submit a form with Request.form(airVal & "_ad_eco_amt_net") value of:

Code:

', companyid = 0, air_cd = '

The speed of this query will depend on how fast the db can find the rows you are updating, if it's slow, you may want to create indexes on some or all of the fields used in the WHERE clause.

HTH,

Chris

crmpicco · November 24th, 2005, 08:29 AM

by indexes, do you mean giving columns an alias?

sounds interesting....

www.crmpicco.co.uk

ChrisScott · November 24th, 2005, 10:09 AM

Nope, I mean having indexes in your database.

A db can use these to do look ups very quickly, a bit like an index of a book allowing you to search alphabetically rather than randomly through all the pages.

Have a look at this...

http://dev.mysql.com/doc/refman/5.0/...l-indexes.html