 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ASP / Database Access / November 2007taking data from classic asp to sql server 2000
This is the first time I have worked with classic asp. I would like to see an example of how to take data from a textbox on a form and update thr data into a sql server 2000 database. This web page will only be using javascript, vbscript, html, and ans sql server 2000. > This is the first time I have worked with classic asp. I would like to see an > example of how to take data from a textbox on a form and update thr data into > a sql server 2000 database. This web page will only be using javascript, > vbscript, html, and ans sql server 2000. Will this help? It uses an MS-Access database. <% @Language="VBScript" %> <% Option Explicit '* Const cASP = "text2mdb.asp" Const cMDB = "text2mdb.mdb" Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Const cSQL = "INSERT INTO [myTable] (myField) VALUES (?)" '* Dim strTXT strTXT = Request.Form("myInput") '* If strTXT <> "" Then Dim strSQL strSQL = Replace(cSQL,"?",strTXT) Dim objADO Set objADO = Server.CreateObject("ADODB.Connection") objADO.Open cDSN & Server.MapPath(cMDB) Dim objRST Set objRST = objADO.Execute(strSQL) Set objRST = Nothing Set objADO = Nothing Response.Write "<li>" & strTXT End If %> <html> <head> <title><%=cASP%></title> </head> <body> <form action="<%=cASP%>" method="post"> <input type="text" name="myInput" size="50" maxlength="50"> <input type="submit" value="Submit"> </form> </body> </html> McKirahan: This helps alot! However, what would you put in "<%=cASP%>" ? Would you have the page call itself again? Thanks! > > This is the first time I have worked with classic asp. I would like to see > an [quoted text clipped - 40 lines] > </body> > </html> > McKirahan: > > This helps alot! However, what would you put in "<%=cASP%>" ? Would you > have the page call itself again? "Const cASP" declares the name of the ASP page. Yes, exactly -- it does call itself. [snip] >> This is the first time I have worked with classic asp. I would like >> to see an example of how to take data from a textbox on a form and [quoted text clipped - 7 lines] > '* > Const cSQL = "INSERT INTO [myTable] (myField) VALUES (?)" Why does this need to be a constant? At least it appears you are going to be using a parameter. > '* > Dim strTXT [quoted text clipped - 3 lines] > Dim strSQL > strSQL = Replace(cSQL,"?",strTXT) tsk, tsk ... why aren't you using parameters?  SignatureMicrosoft 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" > >> This is the first time I have worked with classic asp. I would like > >> to see an example of how to take data from a textbox on a form and [quoted text clipped - 10 lines] > Why does this need to be a constant? At least it appears you are going to be > using a parameter. It doesn't need to be. An alternative would be: strSQL = "INSERT INTO [myTable] (myField) VALUES (" & strTXT & ")" > > '* > > Dim strTXT [quoted text clipped - 5 lines] > > tsk, tsk ... why aren't you using parameters? It was just a simple example. What would you have the OP do? I don't think you mean this: URL:http://www.devguru.com/Technologies/jetsql/quickref/parameters.html > -- > 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" >>>> This is the first time I have worked with classic asp. I would like >>>> to see an example of how to take data from a textbox on a form and [quoted text clipped - 15 lines] > strSQL = "INSERT INTO [myTable] (myField) VALUES (" & strTXT > & ")" Ughh! Why would dynamic (concatenated) sql be the alternative to using a constant? Why not this: Dim strSQL strSQL ="INSERT INTO [myTable] (myField) VALUES (?)" I wasn't questioning the use of a parameter marker vs a dynamic sql statement, I was just slightly curious about your decision to assign your original string to a constant rather than a variable? If you had a good reason for it, i could have learned something. >>> '* >>> Dim strTXT [quoted text clipped - 7 lines] > > It was just a simple example. That's the problem with most of the examples found on the web: "Simple" techniques lead to insecure coding habits that leave websites vulnerable to hackers using sql injection. We both know you would not code it this way; so why advise a beginner to do it this way? Sorry to sound overcritical, but this is one of my pet peeves, which has been directed at my own posts occasionally. > What would you have the OP do? I don't think you mean this: URL:http://www.devguru.com/Technologies/jetsql/quickref/parameters.html No. I consider that to be relevant for saved parameter queries rather than ad hoc statements. No, I'm talking about this simple technique: dim cmd, arParms arParms = Array(strTXT) Dim strSQL strSQL ="INSERT INTO [myTable] (myField) VALUES (?)" set cmd=createobject("adodb.command") cmd.commandtext=strSQL set cmd.activeconnection = objADO cmd.commandtype = 1 'adCmdText cmd.execute ,arParms, 128 '128=adExecuteNoRecords Using parameters instead of dynamic sql avoids sql injection, as well as the necessity to process inputs to handle embedded delimiters. Sure, it's a few extra lines of code, but that's a small price to pay for the benefits gained. SignatureMicrosoft 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. [snip] > >>> Const cSQL = "INSERT INTO [myTable] (myField) VALUES (?)" > >> [quoted text clipped - 16 lines] > your original string to a constant rather than a variable? If you had a > good reason for it, i could have learned something. I was trying to avoid word-wrap :) [snip] > >> tsk, tsk ... why aren't you using parameters? > > [quoted text clipped - 29 lines] > it's a few extra lines of code, but that's a small price to pay for the > benefits gained. Thanks for the advice -- I've been doing bad things for a long time. I rewrote my solution incorporating your approach. <% @Language="VBScript" %> <% Option Explicit '* Const cASP = "text2mdb.asp" Const cMDB = "text2mdb.mdb" Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Const adCmdText = 1 Const adExecuteNoRecords = 128 '* Dim strTXT strTXT = Request.Form("myInput") '* If strTXT <> "" Then Dim arrCMD arrCMD = Array(strTXT) Dim strSQL strSQL = "INSERT INTO [myTable] (myField) VALUES (?)" Dim objADO Set objADO = Server.CreateObject("ADODB.Connection") objADO.Open cDSN & Server.MapPath(cMDB) Dim objCMD Set objCMD = Server.CreateObject("ADODB.Command") objCMD.CommandText = strSQL Set objCMD.ActiveConnection = objADO objCMD.CommandType = adCmdText objCMD.Execute ,arrCMD,adExecuteNoRecords Set objCMD = Nothing Set objADO = Nothing '* Response.Write "<li>" & strSQL End If %> <html> <head> <title><%=cASP%></title> </head> <body> <form action="<%=cASP%>" method="post"> <input type="text" name="myInput" size="50" maxlength="50"> <input type="submit" value="Submit"> </form> </body> </html> I do have a question, according to DevGuru (http://www.devguru.com/technologies/ado/8517.asp) (if I read correctly) the following should work but doesn't: Set objCMD.ActiveConnection = cDSN & Server.MapPath(cMDB) Why does it fail with the following error? Microsoft VBScript runtime error '800a01a8' Object required: 'ActiveConnection' To the OP; for the SQL Server value for "cDSN" visit: Connection String Home Page http://www.carlprothman.net/Default.aspx?tabid=81 > I do have a question, according to DevGuru > (http://www.devguru.com/technologies/ado/8517.asp) [quoted text clipped - 4 lines] > Microsoft VBScript runtime error '800a01a8' > Object required: 'ActiveConnection' "Set" is used to assign an object to a variable. The expression "cDSN & Server.MapPath(cMDB)" results in a scalar string, not an object. Best practice is to initialize and open an explicit Connection object: dim cn set cn=createobject("adodb.connection") cn.open cDSN & Server.MapPath(cMDB) which is assigned to ActiveConnection using "Set" rather than assigning a string to the ActiveConnection property (without the "Set" keyword). The latter method causes an implicit connection to be created and opened, which is bad because it can defeat connection pooling. SignatureMicrosoft 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. > > I do have a question, according to DevGuru > > (http://www.devguru.com/technologies/ado/8517.asp) [quoted text clipped - 17 lines] > The latter method causes an implicit connection to be created and > opened, which is bad because it can defeat connection pooling. So DevGuru's example is wrong? Set objCommand.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=ADOData.mdb" Or is it my variation that I thought was equivalent? Thanks. >>> I do have a question, according to DevGuru >>> (http://www.devguru.com/technologies/ado/8517.asp) [quoted text clipped - 24 lines] > Set objCommand.ActiveConnection = > "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=ADOData.mdb" Yes, it's wrong. You cannot use "Set" to assign a string to a property, SignatureMicrosoft 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. Gazing into my crystal ball I observed =?Utf-8?B?V2VuZHkgRWxpemFiZXRo?= <WendyElizabeth@discussions.microsoft.com> writing in news:49B8F090-69E8-426D-836D-FA49CF4FF1EF@microsoft.com: > This is the first time I have worked with classic asp. I would like to > see an example of how to take data from a textbox on a form and update > thr data into a sql server 2000 database. This web page will only be > using javascript, vbscript, html, and ans sql server 2000. In addition to what others have said, be sure to validate the user's input server side. Take a look at http://intraproducts.com/usenet/requiredform.asp for some ways to do this. SignatureAdrienne Boswell at Home Arbpen Web Site Design Services http://www.cavalcade-of-coding.info Please respond to the group so others can share > In addition to what others have said, be sure to validate the user's > input server side. Take a look at > http://intraproducts.com/usenet/requiredform.asp for some ways to do > this. Hmm, I'm not sure I like the multiple loops through the Form collection. I would prefer localizing the data in a single loop rather than multiple time-consuming accesses of the Request object. And this: TheString = field & "= Request.Form(""" & field & """)" Execute(TheString) seems to be totally unnecessary, as well as CPU and memory-intensive. If I have time later, I will take a stab a revising this example to avoid these problems. But it at least introduces/reinforces the mindset that validation needs to be done on the server, even if it's also being done in the client. SignatureMicrosoft 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. >> In addition to what others have said, be sure to validate the user's >> input server side. Take a look at [quoted text clipped - 11 lines] > I have time later, I will take a stab a revising this example to avoid > these problems. I would really like that if you did that. That's a snippet I use a lot. > But it at least introduces/reinforces the mindset that validation needs > to be done on the server, even if it's also being done in the client. And you can't guarantee that client side validation is even available. SignatureAdrienne Boswell at Home Arbpen Web Site Design Services http://www.cavalcade-of-coding.info Please respond to the group so others can share If you have a chance to make the code more efficient, I would appreciate seeing what changes can be made. Thanks! > >> In addition to what others have said, be sure to validate the user's > >> input server side. Take a look at [quoted text clipped - 22 lines] > > And you can't guarantee that client side validation is even available. >> If I have time later, I will take a stab a >> revising this example to avoid these problems. > > I would really like that if you did that. That's a snippet I use a > lot. OK. give this a try: <%@ Language=VBScript %> <% option explicit%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" lang="en"> <head> <title>Sample Form</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <% 'The ASP form script needs to be in the head section BEFORE the style 'element dim ix, field, inputvalue, message, required, requiredm dim firstname, lastname, address, address2, city, state dim zip, phone, email,i dim FormAction dim xmlData, root, node, reqChars,reqChar, forbidChars dim key, arReq set xmldata=createobject("msxml2.domdocument") set xmlData.documentElement=xmlData.createElement("root") set root=xmlData.documentElement set node=xmlData.createElement("firstname") node.setAttribute "required","true" node.text="Your first name" root.appendchild node set node=xmlData.createElement("lastname") node.setAttribute "required","true" node.text="Your last name" root.appendchild node set node=xmlData.createElement("address") node.setAttribute "required","true" node.text="Your address" root.appendchild node set node=xmlData.createElement("address2") node.setAttribute "required","false" node.text="Optional" root.appendchild node set node=xmlData.createElement("city") node.setAttribute "required","true" node.text="Your city" root.appendchild node set node=xmlData.createElement("state") node.setAttribute "required","true" node.text="Your state" root.appendchild node set node=xmlData.createElement("zip") node.setAttribute "required","true" node.text="Your zipcode" root.appendchild node set node=xmlData.createElement("phone") node.setAttribute "required","true" node.text="Your phone number" root.appendchild node set node=xmlData.createElement("email") node.setAttribute "required","true" node.setAttribute "requiredchars","@|." node.setAttribute "forbiddenchars","www" node.text="Your email address" root.appendchild node FormAction = "" 'first determine whether this is from a post if ucase(request.servervariables("request_method")) = "POST" then 'now get the form values for each node in root.childNodes key=node.nodeName InputValue=Trim(Request.Form(key)) node.text = InputValue required=cbool(node.getAttribute("required")) if required then 'see if the user entered a value if InputValue = "" or left(InputValue,4) = "Your" then 'no value, so build the info message if len(requiredm) > 0 then requiredm=requiredm & "\n" & key & " is required" else requiredm=key & " is required" end if if FormAction="" then FormAction="#" & key else 'check for required characters reqChars=node.getAttribute("requiredchars") if len(reqChars) > 0 then arReq=split(reqChars,"|") for i = 0 to ubound(arReq) reqChar=arReq(i) if instr(inputvalue,reqChar)=0 then if FormAction="" then FormAction="#" & key if len(requiredm) > 0 then requiredm=requiredm & "\n" & key & _ " must contain the """ & reqChar & """ character" else requiredm=key & " must contain the """ & _ reqChar & """ character" end if end if next end if 'check for forbidden characters forbidChars=node.getAttribute("forbiddenchars") if len(forbidChars) > 0 then if instr(inputvalue,forbidChars)>0 then if FormAction="" then FormAction="#" & key if len(requiredm) > 0 then requiredm=requiredm & "\n" & key & _ " must not contain """ & reqChar & """" else requiredm=key & " must not contain """ & _ reqChar & """" end if end if end if end if end if next if len(requiredm) = 0 then 'process the data message = "Data submission successful" else message=requiredm end if if len(message) > 0 then %> <script type="text/javascript"> function init() { alert('<%=message%>'); if ('<%=formaction%>'!= '') {document.getElementById('<%=mid(FormAction,2)%>').focus();} } </script> <% message = "<div class=" & chr(034) & "message" & chr(034) & _ "><strong>" & replace(message,"\n","<br>") & "</strong></div>" end if end if firstname = root.selectSingleNode("firstname").text lastname = root.selectSingleNode("lastname").text address = root.selectSingleNode("address").text address2 = root.selectSingleNode("address2").text city = root.selectSingleNode("city").text state = root.selectSingleNode("state").text phone = root.selectSingleNode("phone").text email = root.selectSingleNode("email").text zip = root.selectSingleNode("zip").text %> <style type="text/css"> <!-- body { font-family:arial; background-color: #fff; color:#000; } /* This creates the structured "look" of the form - without tables! */ label { width: 15%; text-align: right; float: left; margin-right:.25em; display:block; } input { margin-bottom:.25em; } p.submit { text-align: center; } fieldset { border:1px solid #8c9f14; } legend { text-transform: capitalize; font-style: italic; font-weight: bold; } form br { clear: left; } /* The following will be replaced with the name of the required field. Change the colors to suit. */ <% if required <> "" then %> #<%=required%>1 { font-weight:bold; background-color: yellow; color:red } #<%=required%> { background-color: pink; color: black } <% end if %> /* These classes effect various elements in the form */ .message { font-weight:bold; color:red; background-color: #fff; text-align:center } .submit { text-align:center } .required { font-weight:bold; color: red } --> </style> </head> <body onload="<%if len(message)>0 then Response.Write "init()" %>"> <h1>Sample Form</h1> <!-- This bit is here to alert non js users that they will have to manually remove the contents of the fields --> <script type="text/javascript"> <!-- s="<p style='display:none'>You have Javascript enabled and the values of the fields will be removed on focus.</p>"; document.write (s); //--> </script> <noscript> <p>Since you do not have Javascript enabled, you will have to manually remove the contents of the fields. The values are there to provide examples only.</p> </noscript> <form method="post" id="testform" action="<%=request.servervariables("script_name")%>"> <fieldset><legend>Indicates <span class="required">required *</span> field</legend> <%=message%> <label for="firstname" id="firstname1">Firstname <span class="required">*</span>: </label> <input type="text" name="firstname" id="firstname" value="<%=firstname%>" title="Enter first name" onfocus="if(this.value == 'Your first name') this.value = '';" /> <br /> <label for="lastname" id="lastname1">Lastname <span class="required">*</span>: </label> <input type="text" name="lastname" id="lastname" value="<%=lastname%>" title="Enter last name" onfocus="if(this.value == 'Your last name') this.value = '';" /> <br /> <label for="address" id="address1">Address <span class="required">*</span>: </label> <input type="text" name="address" id="address" value="<%=address%>" title="Enter address" onfocus="if(this.value == 'Your address') this.value = '';" /> <br /> <label for="address2" id="address21">Address2: </label> <input type="text" name="address2" id="address2" value="<%=address2%>" title="Enter second line of address" onfocus="if(this.value == 'Optional') this.value = '';" /><br /> <label for="city" id="city1">City <span class="required">*</span>: </label> <input type="text" name="city" id="city" value="<%=city%>" title="Enter city" onfocus="if(this.value == 'Your city') this.value = '';" /><br /> <label for="state" id="state1">State <span class="required">*</span>: </label> <input type="text" name="state" id="state" value="<%=state%>" title="Enter state" onfocus="if(this.value == 'Your state') this.value = '';" /><br /> <label for="zip" id="zip1">Zip <span class="required">*</span>: </label> <input type="text" name="zip" id="zip" value="<%=zip%>" title="Enter zip" onfocus="if(this.value == 'Your zipcode') this.value = '';" /><br /> <label for="phone" id="phone1">Phone <span class="required">*</span>: </label> <input type="text" name="phone" id="phone" value="<%=phone%>" title="Enter phone" onfocus="if(this.value == 'Your phone number') this.value = '';" /> <br /> <label for="email" id="email1">Email <span class="required">*</span>: </label> <input type="text" name="email" id="email" value="<%=email%>" title="Enter email" onfocus="if(this.value == 'Your email address') this.value = '';" /> <br /> <p class="submit"><input type="submit" value="Submit" /></p> <% if required <> "" then %> <%=message%> <%end if%> </fieldset> </form> </body> </html> SignatureMicrosoft 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.