Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 casting variables in ASP

Author  Topic 

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2002-12-18 : 17:35:10
I have an ASP form that will send all its content to a SQL Server 7.0 SPROC for an UPDATE. Most of the fields on the SQL end are smallint data type, some floats. I'm having a hard time figuring out how to CAST variables in ASP so the form fields arrive to the SPROC as the proper data type. I keep getting "unable to convert varchar to float" etc. errors.

I see that this is because I'm gathering all form fields according to the following example (the field below is a float on the SQL side):

strTapeNum = cStr(Request.Form("TapeNum"))

and then passing each variable to the SPROC in ASP page like this:

mySQL="sp_update_table " & strTapeNum &""

Of course, once this ASP processing script interfaces with the SPROC on the SQL side, it kicks back the type conversion errors.

I need to know how to properly use CASTING VARIABLES in ASP, like cInt and others (is there a cFloat??)

Can anyone point me to a good resource that describes CASTING variables in ASP or provide some code snips?

As always, thanks.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts
Posted - 2002-12-18 : 17:42:13
Use the ADO Command Object which allows you to add and define input and output parameters of different types.


CDbl() is what your look for I believe.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctcdblx.asp



Edited by - ValterBorges on 12/18/2002 17:45:32
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2002-12-18 : 18:07:15
I'll check that out. I should have added...

I can easily execute the update by using ADO, i.e. oRs.addnew, ors.update. Using these calls I don't have to worry about data types. The record gets written and that's that.

However, I'd like to move to all SPROCs to control my data updates in order to add an extra layer of security and gain server performance.

thx
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2002-12-18 : 22:03:18
OK, ASP101.com has a nice article describing the ADO parameters collection. This appears to be the solution to my problem (tip of the hat to Valter, who provided the guidance):

http://www.asp101.com/articles/wrox/asp30/26100903.asp

Using ADO parameters I can explicitly type each value I take from the ASP form before matching these data types to the @ parameters waiting on the SQL Server end.

HOWEVER...I'm having a hard time understanding how this would make my life easier. Yes, I'd like improved server performance and an extra layer of security with SPROCs, but, unless I'm missing something major, it's much easier for me to just INSERT and UPDATE my sql tables by coding directly in ASP, listing out all the strings collected from the form, opening a data conn, using ORS.ADDNEW, ORS.UPDATE to add/update records in the sql db.

Since vbscript deals in variants, I assume the issue of data type conversion is moot using ADO as noted above. However, if I use an SPROC, I have to code all the parameters in my ASP page, list out all the parameters in the SPROC, set permissions on the SPROC, etc.

My user base is small and my server relatively powerful, so I'm starting to think that performance gains associated with SPROCs do not outweigh the coding headaches. If I'm skating on thin ice here -- especially with respect to my use of ADO to update and insert -- please let me know.

thx







Edited by - steelkilt on 12/18/2002 22:04:55
Go to Top of Page

skillile
Posting Yak Master

208 Posts
Posted - 2002-12-18 : 22:06:54
EXAMPLE

set objcmd=server.createobject("ADODB.command")
with objcmd
.activeconnection=CONNECTION VAR
.commandtext = "PROCEDURE NAME GOES HERE"
.commandtype = adcmdstoredproc
'--set params
.parameters.append .createparameter("RETURN_VALUE", adinteger adparamreturnvalue)
.parameters.append .createparameter("@username", advarchar, adparaminput,20,username)
.execute()


try that


slow down to move faster...
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-12-18 : 22:38:15
It's natural to think that for a low-traffic site like you have now, the extra effort to use sprocs is not worth it. The problem is, the later you migrate to sprocs the harder it will be, and sooner or later you WILL need to use them. It's more a matter of getting into a good habit early (sprocs), even if it means more work at first, than whether you'll see an improvement in performance. Start now instead of later; you'll pick it up a lot faster than you think and you'll have all that intervening time to get better at writing them. And I'll tell ya, all you need is 1 or 2 instances where you made changes to a sproc WITHOUT needing to change any of the ADO/ASP code, and you'll never, ever go back to embedded SQL strings.

The major advantages to using a Command object is that the sproc/plan will be cached properly, and repeated calls will pull the execution plan from cache instead of recompiling it. Adding the parameters to a string and then executing the string doesn't always allow for that because the optimizer may not recognize that it's the same procedure the next time around. You also need to use a Command object if you have output variables that you want to return to ASP.

And I heartily recommend NOT using the Recordset Update or AddNew methods, because it makes a transition to sprocs much more difficult. Using sprocs in the first place will not interfere with using Update and AddNew in the future, however. And those methods are not always portable (you may not always use ADO, for example) but using sprocs on the server allows you to write universal code in practically any language on any platform with little or no modification. Also, the Update and AddNew methods are death to performance if you're NOT using disconnected recordsets with batch updates.
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2002-12-19 : 09:02:34
Thanks for the tips, everyone.

Rob, I definitely see your point, however I'm confused in one sense. You mention some of the drawbacks of writing out the parameters on the ASP page. Does this mean that your suggestion to use the ADO Command object results in a different type of coding approach than Skillile's above?

Could you post a small code example of the approach you're discussing here, i.e.

-- ASP page starts by gathering form fields
-- ASP page casts fields as proper data types
-- ASP page calls SPROC
-- snapshot of SPROC on SQL side.

I'd love to see how your approach differs from the ASP101 example I noted above.

thx
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-12-19 : 09:20:53
quote:
You mention some of the drawbacks of writing out the parameters on the ASP page. Does this mean that your suggestion to use the ADO Command object results in a different type of coding approach than Skillile's above?
I meant that this kind of coding:

sql="EXECUTE mySP @param1='" & request.form("name") & "', "
sql=sql & " @param2=" & request.form("age") & ", "
sql=sql & " @param3='" & request.form("address") & "' "
connectionObject.Execute sql

Is less efficient than using an ADO Command object:

set objcmd=server.createobject("ADODB.command")
with objcmd
.activeconnection=CONNECTION VAR
.commandtext = "mySP"
.commandtype = adcmdstoredproc
'--set params
.parameters.append .createparameter("@param1", advarchar, adparaminput,20,request.form("name"))
.parameters.append .createparameter("@param2", adint, adparaminput, ,request.form("phone"))
.parameters.append .createparameter("@param3", advarchar, adparaminput, 20,request.form("address"))
.execute()

I probably misread your post, I was thinking you were using the first syntax. I used the first method all the time, now I'm using Command objects like I should've been using. I did it because, hey, I'm lazy and the first method was more compact. But in time I found that it was harder to maintain the code, especially if I used optional parameters (which I do a lot) It also makes it A LOT easier for other poor bastards programmers to read my code.

Skillie's format works the same way as the ASP101 article, the "WITH objcmd" construct allows for neater code though. I *think* that it may also be more efficient because the object is only referenced once, but you'd have to look at VB/VBScript documentation to confirm that. You don't have to use WITH if you don't want to, it won't affect how the Command object performs.
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2002-12-19 : 11:17:50
Wow. The cmd object make a huge difference in terms of speed! Below is the code snippet I'm using. It's exactly what you all provided. One question, why is IE demanding "End With" following execute(). The script fails unless End With is tacked on at the end.

================================================================<%@ LANGUAGE="VBSCRIPT" %>

<!-- #include FILE="adovbs.inc" -->

<%


myDSN="test"

set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN


set objcmd=server.createobject("ADODB.command")
with objcmd
.activeconnection=myDSN
.commandtext = "sp_insert_record"
.commandtype = adcmdstoredproc
'--set params
.parameters.append .createparameter("@personID", adDouble, adparaminput,,request.form("personID"))
.parameters.append .createparameter("@badge", advarchar, adparaminput,10 ,request.form("badge"))
.parameters.append .createparameter("@ChartStatus", adsmallint, adparaminput,,request.form("ChartStatus"))
.parameters.append .createparameter("@location", adsmallint, adparaminput,,request.form("location"))

.execute()



End With


%>
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts
Posted - 2002-12-19 : 11:43:54
It's not ie it's the asp parser in IIS which tries to interpret your syntax as vbscript but the syntax is not correct without the End With when it can't parse it outputs html indicating an error which in turn ie displays on your screen.

End With indicates the end of With objcmd

The syntax is:

WITH object

'access object here

END WITH







Edited by - ValterBorges on 12/19/2002 11:45:37

Edited by - ValterBorges on 12/19/2002 11:46:55
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2002-12-19 : 12:06:08
boy, that was an obvious one. <red faced>
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2002-12-20 : 08:09:59
Ha ha, found it!!!
Found what?
This article. http://www.15seconds.com/Issue/010718.htm

It has been a long time since I looked at this but use the technique all the time. When combined with a chart, usually titled DataTypeEnum Values you can sweep away all your VBSCRIPT variable typing worries away.
Plus it gels nice with what the others have said, and you have found out about the Command Object. Warning though, this will allow you to forget lots of what you once knew or will know about the Command Object. Because it's scary automated. It depends on how flexible and focused your .ASP skills need to be. I only use 'em every couple of weeks for a few hours at a time, so I get dumber everytime I use it but when an extended development sessions comes around you can usually ramp back up close to a previous level of understanding.

ps. and reference Able Consulting website for the connection strings
which are forgotten daily too.

Edited by - sitka on 12/20/2002 08:14:00
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site