Jump to content


  • Posts

  • Joined

  • Last visited

Everything posted by Yahweh

  1. You're right, you're query is unacceptably large, and it will be extremely slow searching just a few thousand records.You should put a fulltext index on your table, something like this: ALTER TABLE tab1ADD FULLTEXT INDEX search_all_fields (name, type, subtype, fullfeature, quickfeature) Now you can search all of your fields with this short SQL query (and its MUCH faster as well): Select *FROM tab1WHERE MATCH (name, type, subtype, fullfeature, quickfeature) AGAINST ('search string') The nice thing about fulltext search is that you can sort your results by relevance without any performance hit. The only real limitation is that you can't search for words less than 4 characters, which means the fulltext search might ignore some important 3-letter acronyms on your site, but otherwise I think its exactly what you need to search against any number of fields.Note: MySQL and SQL Server support the syntax above. MS Access probably doesn't, but you shouldn't be using Access if you plan to have a serious website at all.
  2. Instead of using a DataGrid, you're better off using a GridView. A GridView lets you add a delete column in just a single line of code.A DataGrid doesn't have a built-in delete function, you have to write one yourself. I found a good article with the code you need to add a delete column:http://www.aspnetpro.com/newsletterarticle...p200303wf_l.asp
  3. Dynamic SQL isn't a T-SQL trick, and it can't be created with stored procedures. "Dynamic SQL" is just a fancy way of saying ad hoc SQL written in server side code on your website.The real advantage of server-side SQL come when you need to use a lot of user-selected options to create an SQL statement, specifically when you don't know what the WHERE condition will look like ahead of time, such as in a site search where a user has the option to search many different optional fields and sort the fields in any order.Here is an example of a site search in VB.Net which uses dynamically generated SQL queries:Backend code:Private Sub Search (sender as object, e as eventargs) Handles Button.Click Dim strSQL as String = _ String.Format(_ "Select * from {0} [searchString] ORDER BY {1} {2};", _ dropDownTable.Replace("'", "''"), _ dropDownORDERBY.Replace("'", "''"), _ dropDownSortOrder.Replace("'", "''") _ ) 'I'm using string.Replace("'", "'') to escape my SQL string, because 'otherwise its vulnerable to SQL injection if txtSearchString.Length > 0 then strSQL = Regex.Replace(strSQL, _ Regex.Escape("[searchString]"), _ String.Format("WHERE Title = '%{0}%', _ txtSearchString.Replace("'", "''")), _ RegexOptions.IgnoreCase) else strSQL = Regex.Replace(strSQL, _ Regex.Escape("[searchString]"), _ "", _ RegexOptions.IgnoreCase) end if 'Resulting string is a valid SQL string Dim sqlComm As New SqlCommand(strSQL, sqlConn) Dim r As SqlDataReader = sqlComm.ExecuteReader() While r.Read() '*** '*** 'output all of your fields here, or bind it something on the page '*** '*** End While r.Close()End Sub Front end HTML: <script runat="server"> <p> Select a table to search:<br> <asp:DropDownList ID="dropDownTable" runat="server"> <asp:ListItem Selected="True">Orders</asp:ListItem> <asp:ListItem>Customers</asp:ListItem> <asp:ListItem>Cats</asp:ListItem> </asp:DropDownList> </p> <p> Find a record containing:<br> <asp:Textbox ID="txtSearchString" runat="server"> </p> <p> Sort results by:<br> <asp:DropDownList ID="dropDownORDERBY" runat="server"> <asp:ListItem Selected="True">Title</asp:ListItem> <asp:ListItem>Last Updated</asp:ListItem> <asp:ListItem>ID</asp:ListItem> </asp:DropDownList> <asp:DropDownList ID="dropDownSortOrder" runat="server"> <asp:ListItem Selected="True">Ascending</asp:ListItem> <asp:ListItem>Descending</asp:ListItem> </asp:DropDownList> </p> <asp:Button runat="server" name="Button" text="Search"></script>
  4. Inserting a record with multiple values is easy:INSERT INTO table (field1, field2, field3)VALUES (val1a, val1b, val1c)WHERE condition = @condition; If you want to insert multiple records, its practically the same thing, except you seperate the values for each record by a comma: INSERT INTO table (field1, field2, field3)VALUES (val1a, val1b, val1c), (val2a, val2b, val2c), (val3a, val3b, val3c), (val4a, val4b, val4c)WHERE condition = @condition;
  5. Yahweh

    Join table

    The best programming practice is simply executing two queries, something like this: -- This is written in T-SQLint @class;-- Get the first@class = Select class from t3 where condition = @conditionif @class = 4 begin select name from t1 endif @class = 10 begin select name from t2 endreturn
  6. Yahweh


    .Net has a fantastic regex construct called "balanced expressions" that are very useful for solving this problem. You only need a single regex.replace command to replace the text properly.Here is the expression I use:(?> <script> (?<LEVEL>) # On opening paren push level | </script> (?<-LEVEL>) # On closing paren pop level | \{(.*?)\} # Match any char except <script> or </script>)(?(LEVEL)(?!)) # If level exists then fail Feel free to test this expression at Regex Tester.The VB.Net code for it looks like this: Private Function myFunction(ByVal strInput As String) As String Dim strExpression As String = _ "(?>" & vbNewLine & _ " <script> (?<LEVEL>) # On opening paren push level" & vbNewLine & _ " |" & vbNewLine & _ " </script> (?<-LEVEL>) # On closing paren pop level" & vbNewLine & _ " |" & vbNewLine & _ " \{(.*?)\} # Match any char except <script> or </script>" & vbNewLine & _ ")" & vbNewLine & _ "(?(LEVEL)(?!)) # If level exists then fail" & vbNewLine Return System.Text.RegularExpressions.Regex.Replace(strInput, strExpression, "<%=$1%>", System.Text.RegularExpressions.RegexOptions.IgnoreCase Or System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace).ToString() End Function Input: {something} here<script> function myFunction() { //some js code }</script><div>{sText1}<br><br>{sText2}<br><br>{PhotoGallery}</div><script> function anotherFunction() { //some js code }</script>{rock} Output: <%=something%> here<script> function myFunction() { //some js code }</script><div><%=sText1%><br><br><%=sText2%><br><br><%=PhotoGallery%></div><script> function anotherFunction() { //some js code }</script><%=rock%>
  7. Yahweh


    Given knowledge of the Hubble Deep Field Photos, and the fact that there are trillions of galaxies containing hundreds of trillions of stars in the universe and many of them have their own planet systems, its almost mindboggling that some people today still believe that Earth is the center of the universe and the only place with life. As if the other billions of lightyears of space outside of the earth are completely sterile... Press F11 to maximize your browser window.
  8. Yahweh

    Code Reuse

    This is probably an elementary problem, but my Google searches didn't reveal any good answers.I have this function for escaping SQL string: Public Function SafeSQL(strString as String) as String Return replace(strString, "'", "''", 1, -1, CompareMethod.Text)End Function I want to put that code in another file so it can be included globally on all of my pages or make it available selected pages without using the classic asp "<!--#include-->" statement, but I don't know the ASP.Net syntax. Any help would be appreciated
  9. I've got another problem: My server is currently running .NET Framework 1.1 on my machine, but I want to run .NET Framework 2.0 (I've already downloaded the latest version). How do I tell my server to compile pages under the 2.0 framework?Note: Some of the solutions I've seen online are specific to IIS 6.0, however I'm not running that program as my webserver (it didn't come with my WinXP Home edition). I'm using the micro-webserver that comes with WebMatrix.
  10. I'm creating a website where registered users have their own folder to store files. I plan to create the directories like this: /root /images /includes /users /bob /joe /jack /yahweh This way, all the files are stored at www.site.com/users/username/.However, those kinds of urls are tacky and ugly, I want users to be able to type www.site.com/username/ and get to the same place. I can't use username.site.com, because my host is cheap and doesn't make ASP.Net available for subdomains.I think virtual directories can be simulated with 404 pages, but I'm not sure how to get started. Is there a painless way to forward all www.site.com/username hits to their corresponding www.site.com/user/username directory?
  11. The line in red is causing an error. The shorthand syntax If some_variable Then only works for numeric or boolean values. The variable Bunny is a string, not a number, so its going to give you a syntax error.
  12. As a general rule of thumb, you should sanitize your data before you put it in the database. Use Server.HTMLEncode, like this:'... open database, get dataresponse.write Server.HTMLEncode(RS("some_field"))'...close database Or use it like this when you're updating your database: '... open database for updatingRS.Open SQL, Conn, 3, 3 RS("some_field1") = Server.HTMLEncode(some_variable) RS("some_field2") = Server.HTMLEncode(some_other_variable) RS("some_field3") = Server.HTMLEncode(another_variable) RS.UpdateRS.Close'...close database In my experience, I haven't had any problems HTMLEncoding my data when as soon as I response.write it to the screen, as opposed to encoding it before I put it in my database. Sometimes, encoding data when you update the database produces odd results, for instance if you take this text: <B>Hello world</B>. And HTMLEncode it before you update your record, it will be stored in the database like this: <B>Hello world</B>. But when you open that field again and make changes to it, theres the possibility that it will get re-encoded, like this: &lt;B&gt;Hello world&lt;/B&gt; That is obviously bad, it destroys your HTML. So I recommend encoding the database as soon as you response.write it to the window.
  13. Ah, gotcha. Its virtually identical code to what I wrote above. Try this:<%Dim Conn, RS, SQL, XSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")'Set X = Server.CreateObject("ADODB.Field")'You may or may not need to create and ADO Field object. I don't think you do,'but if the code below doesn't work, try uncommenting it and see what'happens.SQL = "Select * From Articles"Conn.Open your_connection_stringRS.Open SQL, Conn 1, 1 'This do loop goes through all of the records in your database If Not RS.Eof then For Each X in RS.Fields Response.write X.Name & "<br>" Next End ifRS.CloseConn.CloseSet Conn = nothingSet RS = nothing%> That should print out all of the field names without having to specify them.
  14. I'm not exactly sure what you're asking, but it sounds like this:You have a form for users to search your database. If a user types "bunny", it will search all records containing the word bunny; if a user types "id=5", it will bring up the appropriate article so the user can modify it.Most likely, you're going to use something like this: search.asp<% Option Explicit %><html><body><form action="search.asp" method="post"> <input type="text" name="search"><br> <input type="submit" value="Search"></form><%Dim Search, Conn, RS, SQLDim Regex, Match, MatchesSearch = cstr(Request("search"))if Search <> "" then 'Just going to perform a quick regex to see if the user typed "ID=" in the 'search field. Set Regex = New Regexp Regex.Pattern = "^ID=(\d+)$" Regex.IgnoreCase = True Regex.Global = True Regex.Multiline = True If Regex.Test(Search) then Set Matches = Regex.Execute(Search) For Each Match in Matches SQL = "Select * From your_table WHERE ID = " & Match.SubMatches(0) Next else SQL = "Select * From your_table WHERE SearchField Like '%" & Search & "%'" End if Set Conn = Server.CreateObject("ADODB.Connection") Set RS = Server.CreateObject("ADODB.RecordSet") Conn.Open your_connection_string RS.Open SQL, Conn, 1, 1 'display form data, and be sure to include a hidden field with the RS("ID") 'so you can edit the form record. RS.Close Conn.Close Set Conn = nothing Set RS = nothingEnd if%></body></html>
  15. Yahweh

    chk if founded in db

    Matar,You've got a lot of errors in your code. The code in red is wrong. First, the syntax for opening a table looks like this:RecordSet.Open sql_query, connection[, cursortype, locktype]. (Cursortype and locktype are optional.) That means, the statement RS1.Open "reg" opens your your reg table, by executeing the SQL query "reg", which returns all the rows and all the columns from your reg table. You probably don't want to do that, it eats up all of your server resources and it causes the page to load very very slowly.You wouldn't be able to open up your table in the first place, because you've declared a variable called Conn1, but you're trying to open your recordset on a variable called Conn. You're going to get a "connection not open" error. I'm not sure why you are trying to open two recordsets and two connections at the same time, you only need one.Also, after you open up all of the contents from your reg table in the first line, you perform another SQL query directly afterward. So you are performing two queries, when you only need one. More importantly, you've already opened a recordset with RS.Open, and now you are trying to perform another query with Conn.Execute without closing your other recordset, so you are going to get a "cannot execute query on open recordset" error. Also note that you aren't using correct SQL syntax in your SQL statement; you use "SELECT * form" when you should be using "SELECT * from".Then, if your new query returns a match, you forward the user to another page without closing your recordset or connection (thats bad).You should rewrite the code in red to this:Dim Conn1, RS1, sql1Set Conn1 = Server.CreateObject("ADODB.Connection")conn1.Provider="Microsoft.Jet.OLEDB.4.0"conn1.Open(Server.Mappath("/webdata/atc.mdb"))set rs1 = Server.CreateObject("ADODB.recordset")sql1 = "select * from reg where wh_card = '"& Request.Form("wh_card") & "'"RS1.open sql1,Conn1, 3, 3 'its better to explicitly state your cursortype and locktypesif not rs1.eof then 'closing recordset and connection before forwarding to another page. rs1.close conn.close set rs1 = nothing set conn = nothing response.Redirect ("error.asp?error=duplicate")end if By this point in your code, you've already got a recordset and a connection already open. You don't need to open another recordset, and if you try to open another connection on your Conn variable without closing it, then you're going to get a "connection already open" error.You don't need any of the code in red, you should delete it. However, since you're going to be deleting it, you need to change call of your RS fieldnames below to RS1, so that they match your existing recordset variable.rs1.AddNewrs1.Fields("Name") = Request.Form("name")rs1.Fields("avg_sec") = Request.Form("avg_sec")rs1.Fields("sec_sec") = Request.Form("sec_sec")rs1.Fields("sec_year") = Request.Form("sec_year")rs1.Fields("sec_sou") = Request.Form("sec_sou")rs1.Fields("last_sch") = Request.Form("last_sch")rs1.Fields("gen") = Request.Form("gen")rs1.Fields("mat_stu") = Request.Form("mat_stu")rs1.Fields("rel") = Request.Form("rel")rs1.Fields("pob") = Request.Form("pob")rs1.Fields("dob") = Request.Form("dop")rs1.Fields("moth_name") = Request.Form("moth_name")rs1.Fields("nat") = Request.Form("nat")rs1.Fields("org_cou") = Request.Form("org_cou")rs1.Fields("address") = Request.Form("address")rs1.Fields("tel") = Request.Form("tel")rs1.Fields("post_add") = Request.Form("post_add")rs1.Fields("po") = Request.Form("po")rs1.Fields("dad_name") = Request.Form("dad_name")rs1.Fields("dad_job") = Request.Form("dad_job")rs1.Fields("dad_rela") = Request.Form("dad_rela")rs1.Fields("work_un") = Request.Form("work_un")rs1.Fields("unjob_no") = Request.Form("unjob_no")rs1.Fields("flag_serv") = Request.Form("flag_serv")rs1.Fields("flagserv_no") = Request.Form("flagserv_no")rs1.Fields("wh_card") = Request.Form("wh_card")rs1.Fields("wh_so") = Request.Form("wh_so")rs1.Fields("shg") = Request.Form("shg")rs1.Fields("shg_no") = Request.Form("shg_no")rs1.Fields("any_un") = Request.Form("any_un")rs1.Fields("any_name") = Request.Form("any_name")rs1.Fields("any_cen") = Request.Form("any_cen")rs1.Fields("op1") = Request.Form("op1")rs1.Fields("op2") = Request.Form("op2")rs1.Fields("op3") = Request.Form("op3")rs1.Fields("op4") = Request.Form("op4")rs1.Updaters1.CloseSet rs1 = NothingSet Conn1 = NothingResponse.Redirect "thx.asp"%>
  16. CStr converts a variable to the string datatype. If you don't do this, then you get incorrect results, such as:Dim Bunny 'this is an unintialized variable, its set to NullIf Bunny <> "" then 'do somethingEnd if Because the variable Bunny is Null, it doesn't make sense to say Bunny <> "". Its not technically an empty string because its a Null value.Similarly, Dim BunnyBunny = 25If Bunny <> "" Then 'Do somethingEnd If This will throw a syntax error, because bunny is set to the Integer datatype and the If statement is trying to perform a string comparison on it.The CStr function automatically converts Nulls to empty strings, and converts Numbers to a Strings. Dim Bunny 'NullBunny = Cstr(Bunny) 'Bunny is ""Bunny = 25 'Bunny is 25Bunny = Cstr(Bunny) 'Bunny is "25" There are two ways you can do this: with regular expressions or with the IsNumeric function.Here is the IsNumeric method:Dim BunnyBunny = 25Response.write IsNumeric(Bunny) 'Returns TrueBunny = "this is a string"Response.write IsNumeric(Bunny) 'Returns FalseBunny = "250284"Response.write IsNumeric(Bunny) 'Returns TrueBunny = "25%"Response.write IsNumeric(Bunny) 'Returns TrueBunny = "%25%"Reponse.write IsNumeric(Bunny) 'Returns False, because %25% isn't a valid numberBunny = "$0.99"Response.write IsNumeric(Bunny) 'Returns TrueBunny = "630^2 + 2"Response.write IsNumeric(Bunny) 'Returns false. Although it looks like a valid 'number, the IsNumeric function can't evaluate expressions Anything that you can covert with the CInt(), CSng(), and CDbl() functions will return true with the IsNumeric() function.You can also check datatypes (and also perform pattern matching) using regular expressions. I prefer this method because I have a lot more control over the type of comparison that I'm performing, and I can do much more sophisticated pattern matches. Dim Regex, BunnySet Regex = New RegexpRegex.Global = TrueRegex.Multiline = TrueRegex.IgnoreCase = TrueRegex.Pattern = "^\d+$" 'Matches a string of 1 or more digitsBunny = 25Response.write Regex.Test(Bunny) 'Returns trueBunny = 25.20Response.write Regex.Test(Bunny) 'Returns falseBunny = "Response.write Regex.Test(Bunny) 'Returns falseBunny = 25.20Response.write Regex.Test(Bunny) 'Returns falseBunny = "$2.00"Response.write Regex.Test(Bunny) 'Returns falseBunny = "02128751237249712900023948209481789571358120421345780124"Response.write Regex.Test(Bunny) 'Returns trueBunny = ""Response.write Regex.Test(Bunny) 'Returns falseBunny = 252Regex.Pattern = "^\d+\.\d{2}$" 'finds a pattern of any number of digits, followed 'by a ".", followed by exactly 2 more digitsResponse.write Regex.Test(Bunny) 'Returns falseBunny = "9925.58"Response.write Regex.Test(Bunny) 'Returns TrueBunny = "2525.680"Response.write Regex.Test(Bunny) 'Returns falseBunny = "2250A"Response.write Regex.Test(Bunny) 'Returns falseBunny = "#FF00CC"Regex.Pattern = "^(?:#|0x)?[0-9a-fA-F]{1,6}$" 'matches any valid 'hexadecimal pattern, such as 0xFFCC00, #AAAA99, 205, 29a. The 'hex value must be between 1 and 6 characters.Response.write Regex.Test(Bunny) 'Returns trueBunny = 25Response.write Regex.Test(Bunny) 'Returns trueBunny = "0x0260205934"Response.write Regex.Test(Bunny) 'Returns false, too many charactersBunny = "hello world"Response.write Regex.Test(Bunny) 'Returns false As long as you know what pattern you're searching for, you can build a regex to find that pattern, so regular expressions are 1000 times more powerful than the IsNumeric function.
  17. The error says this:Microsoft VBScript compilation error '800a0409' Unterminated string constant /signup1.asp, line 44 "Username = '" & SQLSecurity(Username) & "' Limit 1;----------------------------------------------------^ This is easy to fix. Just terminate the string constant. Add a quote to the end of that line like this: "Username = '" & SQLSecurity(Username) & "' Limit 1;" Also, you shouldn't post your connection string on the internet where anyone can read it, otherwise they can get into your database and destroy everything.
  18. There's no difference at all, both work just the same. The only difference is the readability of your code (I personally like to see "adOpenStatic" as opposed to "3", its more descriptive). I apologize, but I don't know what you're asking, or what you're trying to get your script to do.
  19. The error is pretty simple, you're not connecting to Yahoo's smtp servers. According to Yahoo, their smtp servers are located at smtp.mail.yahoo.com.So at the very least, you should change this line: .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yahoo.com" To this: .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.yahoo.com" You may also need to set your your port to 587, simply by adding this line directly after the one above: .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587'if port 587 doesn't work, try port 25 If that doesn't work, try looking at this massive 8 part FAQ on troubleshooting CDO errors.
  20. Yahweh

    Help me!

    You can't be serious. No one has access to that code except the authors of that site, and no one is seriously going to take the time to write and build an entire music website for you for free (much less on a messageboard).If you want a music website, learn to program ASP yourself. Or, you can pay someone else to build a site for you (I'll build you a website for US$100, how about it?).
  21. I can't troubleshoot the error without knowing what it is, so copy and paste the error exactly as it appears on the page, and I'll explain how to fix it. You don't have to use that code as-is. Just replace that line with the connection string you need to connect to your database. For example:Conn.Open "PROVIDER=MSDASQL;" & _ "driver={MySQL ODBC 3.51 Driver};" & _ "server=localhost;" & _ "option=18475;" & _ "uid=username;pwd=password;database=databasename;" Alternatively, you can create a variable holding your connection string somewhere else on your page, like this: Dim ConnStringConnString = "PROVIDER=MSDASQL;" & _ "driver={MySQL ODBC 3.51 Driver};" & _ "server=localhost;" & _ "option=18475;" & _ "uid=username;pwd=password;database=databasename;"...Conn.Open ConnString
  22. Yes, there are actually a couple of different ways to get the fields from your table.First, lets imagine that we had the following table that stores blog entries, or something similar:Articles--------ArticleID Author Content DatePublished Category Rating1 Yahweh Kittens rock... 2006-05-01 1 52 Yahweh Vote Green... 2006-05-01 1 4.53 Bob Hello world... 2006-05-02 3 44 Joe My blog suc... 2006-05-03 2 55 Bob Two cows... 2006-05-03 1 4.16 Jane Once upon ... 2006-05-06 3 4.8 Method 1: For Each loopTo get any single record without having to type the name of your table, you can use a For Each loop: <%Dim Conn, RS, SQL, XSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")SQL = "Select * From Articles"Conn.Open your_connection_stringRS.Open SQL, Conn 1, 1 'This do loop goes through all of the records in your database Do until rs.eof 'This For Each loop iterates through each field in the record response.write "<p>" For Each X in RS.Fields 'The variable X holds the value of your field Response.Write X & "<br>" Next Response.Write "</p>" rs.Movenext LoopRS.CloseConn.CloseSet Conn = nothingSet RS = nothing%> Method 2: GetRowsI prefer using the GetRows method to get the fields I need without explicitly specifying them, because I have a little more control over the way data is displayed than a For Each loop. Basically, it crams the results from my query into a 2-dimensional array, then I can loop through the array as usual to display the information I need.The array is constructed like this:(number of fields)(number of rows)Remember that arrays start at zero, so that array(3)(5) corresponds to the 4th field and 6th row of your recordset. <%Dim Conn, RS, SQL, arrData, iRows, iRecordSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")'These numbes will become useful below:' 0 1 2 3 4 5SQL = "Select ArticleID, Author, Content, DatePublished, Category, Rating " & _ "From Articles"Conn.Open your_connection_stringRS.Open SQL, Conn 1, 1 arrData = RS.GetRows()RS.CloseConn.CloseSet Conn = nothingSet RS = nothing'Notice that I can cram my recordset into an array and close my connection'immediately.iRows = UBound(arrData, 2) 'This gets the maximum number from the second 'subscript in the arrData array, which happens to correspond to the 'number of rows For iRecord = 0 to iRows response.write "<p>" response.write arrData(0, iRecord) & "<br>" 'Prints the articleID response.write arrData(1, iRecord) & "<br>" 'Prints the author response.write arrData(2, iRecord) & "<br>" 'Prints the content response.write arrData(3, iRecord) & "<br>" 'Prints the datepublished response.write arrData(4, iRecord) & "<br>" 'Prints the category response.write arrData(5, iRecord) & "<br>" 'Prints the rating response.write "</p>" 'Notice that each # in arrData(#, iRecord) corresponds to the numbers I marked 'above. This is because when the two dimensional array is created, it 'numbers off each field in the order that the fields are specified. If you 'added another field to your SQL statement, the numbers for first subscript 'would change correspondingly. 'The variable iRecord is the row number of records in the recordset. This loop 'iterates through all of the records as the iRecord variable increments.Next%> If you didn't want to print arrData(#, iRecord) a dozen times, you could use this code: Dim iRows, iRecord, iFields, iFieldNumberiFields = UBound(arrData, 1) 'get the number of fieldsiRows = UBound(arrData, 2) 'get the number of rowsFor iRecord = 0 to iRows response.write "<p>" For iFieldNumber = 0 to iFields response.write arrData(iFieldNumber, iRecord) & "<br>" Next response.write "</p>"Next Using the GetRows method does have its advantages over the RS(fieldname) method in terms of speed. Either method specified above will work just fine. I personally prefer the GetRows method, because its faster and more logical. First, create your page with form fields you want to send, some like this:display.asp:<html><body>Note that you are submitting this page to itself.<form action="display.asp" method="post"> <select name="fields" multiple="true"> <option value="articleID">ArticleID</option> <option value="author">ArticleID</option> <option value="content">ArticleID</option> <option value="datepublished">datepublished</option> <option value="rating">Rating</option> </select> <input type="submit"></form><%Dim myFieldsDim Conn, RS, SQL, arrData, iRows, iRecord, iFields, iFieldCountermyFields = cstr(request("fields"))if myFields <> "" then 'Your myFields variable is automatically formatted as a comma-seperated 'list. You don't need to do any parsing to it. 'Getting table data and putting it into an array Set Conn = Server.CreateObject("ADODB.Connection") Set RS = Server.CreateObject("ADODB.RecordSet") SQL = "Select " & myFields & " from Articles" Conn.Open your_connection_string RS.Open SQL, Conn, 1, 1 arrData = RS.GetRows() RS.Close Conn.Close Set Conn = nothing Set RS = nothing 'Getting dimensions of array iRows = UBound(arrData, 2) iFields = UBound(arrData, 1) 'Now generating the table response.write "<table>" 'Going to be doing some clever formatting to the myFields variable. 'I'll be replacing all of the commas in the list with header tags. myFields = Replace(myFields, ",", "</th><th>", 1, -1, vbBinaryCompare) response.write "<tr><th>" & myFields & "</th></tr>" For iRecord = 0 to iRows response.write "<tr>" For iFieldCounter = 0 to iFields response.write "<td>" resopnse.write arrData(iFieldCounter, iRecord) response.write "</td>" Next 'Add any extra cells to the table here. response.write "</tr>" Next response.write "</table>"End if%></body></html> Now you have completely customized and auto-formatted table.
  23. Signup and Login pages are fundamentally no different from any other kind database action. Signup pages add a new record to your database, and Login pages are just a select statement. I'm going to show you just the simple signup method, as opposed to a system that requires email confirmation of new accounts.First step: You need to have a user table where you store user information. Usually, the table will look like this:Users-----ID Int, Not Null, Autoincrement } These three fields are theUsername Varchar 25, Not Null } the bare minimum you need for a Password Varchar 25, Not Null } user table.IPAddress Varchar 15, NullEmail Varchar 60, NullDateRegistered DateTime, Not Null For now, passwords are going to be stored in your database as plain text. Usually, itsa security risk to stored passwords as plain text, we'd normally use a one-way hash like MD5 to encrypt the passwords; but the code for that process is rather lengthy, and for the most part, it's pretty rare for a person to break into your database.Disclaimer: I've written the code below without testing it, so it may contain errors, so don't be worried if ASP doesn't work on the first try.Now, you can create a simple signup page, called signup.asp: <% Option Explicit %><html><body><h1>Signup</h1><% Dim msgmsg = request("msg")if cstr(msg) <> "" then%><font color="red"><%=Server.HTMLEncode(msg)%></font><%end if%> <form action="signup1.asp" method="post"> <input type="text" name="username" value=<%=Server.HTMLEncode(request("Username"))%>><br> <input type="password" name="password"><br> <input type="submit" value="Register"><br> <small>(Cookie must be enabled to complete registration.)</small> </form></body></html> Notice that I have a little code embedded in the page. I've put that code there because, in case there is a problem during registration, the use can be forwarded back.For a page called signup1.asp, we are going to perform a couple of different processes: first, we are going to check that the username and password are non-empty strings, and then we are going to check that they there is no other user in the table with an identical username. Signup1.asp: <% Option ExplicitFunction SQLSecurity(myStr) 'This eliminates the risk of SQL injection SQLSecurity = Replace(myStr, "'", "''", 1, -1, vbBinaryCompare)End FunctionDim Username, PasswordUsername = cstr(request("username")) 'the cstr function explicitly casts thePassword = cstr(request("Password")) 'variable as a string.'Validating username and password.If Username = "" then response.redirect "signup.asp?msg=Invalid username."elseif Password = "" then response.redirect "signup.asp?msg=Invalid password.&username=" & username'elseif Len(username) < 3 then 'this checks to see that the username at least 3' 'characters long' response.redirect "signup.asp?msg=Username must be at least" & _ ' "3 characters long.&username=" & username'elseif Len(password) < 3 then 'this checks to see that the password at least 3' 'characters long' response.redirect "signup.asp?msg=Password must be at least" & _ ' "3 characters long.&username=" & usernameEnd ifelseif Len(username) > 25 then response.redirect "signup.asp?msg=Username cannot be longer than" & _ "25 characters.&username=" & usernameelseif Len(password) > 25 then response.redirect "signup.asp?msg=Password cannot be longer than" & _ "25 characters.&username=" & usernameEnd if'I've commented out the minimum username and password validations. Just'remove the apostrophes before the appropriate lines if you want to use those'validations.Dim Conn, RS, SQL, SuccessfulSet Conn = Server.CreateObject("ADODB.Connection")Set Conn = Server.CreateObject("ADODB.RecordSet")SQL = "Select Username, Password, IPAddress, Email, DateRegistered FROM Users WHERE " & _ "Username = '" & SQLSecurity(Username) & "' Limit 1;"Conn.Open your_connection_stringRS.Open SQL, Conn, 3, 3 If RS.EOF then 'RS.EOF means no results returned from the SQL query. In this case, since 'no results were returned the query executed above, then it means there 'is no user in the Users table with the same username. So, we can insert the 'new username. 'Adding new account to the Users table RS.AddNew RS("Username") = Username RS("Password") = Password RS("IPAddress") = Request.ServerVariables("REMOTE_ADDR") RS("DateRegistered") = Now RS.Update 'Setting cookies for the user. These cookies will expire in 1 year. Response.Cookies("account")("username") = Username Response.Cookies("account")("password") = Password Response.Cookies("account").Expires = DateAdd("m", 12, Now()) Successful = True else 'If a result is found for the query above, that means a user already exists 'with the chosen username, so we have to forward the user back to the 'signup page and ask him or her to choose another name. Successful = False End ifRS.CloseConn.CloseSet Conn = nothingSet RS = nothingIf Successful = True then%> <html> <body> <h1>Congrats!</h1> <p>Your account has been successfully created, <%=Request.Cookies("account")("username")%> :)</p> <p>Please go back to the <a href="default.asp">home page</a>, or for free to edit your <a href="profile.asp">profile</a>.</p> </body> </html><%else response.redirect "signup.asp?msg=Username is already taken." & _ "&username=" & usernameEnd if%> Profile pages are really simple. All they do is display and update data in a table. The do this in a few different stages:1. Validate userID. This example is set up to display profiles by userID, so that profile.asp?id=5 and profile.asp?id=252 show different profiles, so we have to check that the specified ID is a valid integer.2. If ID is a valid integer, then we execute a query to see that a user with the specified ID actually exists.3. If the user exists, we pull the information out of the database and store it into variables for later use.4. Display a page with the profile information in the correct positions. Checks are added so that, if a user doesn't exist, a "User doesn't exist" screen will be displayed instead.Profile.asp (this is set up to display profiles by userID, so that profile.asp?id=5 and profile.asp?id=252 show different profiles): <% Option ExplicitFunction SQLSecurity(myStr) 'This eliminates the risk of SQL injection SQLSecurity = Replace(myStr, "'", "''", 1, -1, vbBinaryCompare)End FunctionDim ID, UserFoundDim Username, Email, DateRegisteredDim Conn, RS, SQLID = request("ID")'Validating that ID is both non-empty and is a valid number. If the userID is 'invalid, it is set to 0. As a consequence of the user table's design, there is no'user in the table with an ID of 0, so no results will be returned by the query.If cst(ID) = "" then ID = 0elseif IsNumeric(ID) = False then ID = 0else ID = cint(ID) 'Expliciting casting ID to integer ShowProfile = TrueEnd ifSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")SQL = "SELECT Username, Email, DateRegistered FROM Users " & _ "WHERE ID = " & SQLSecurity(ID) & ";"Conn.Open your_connection_stringRS.Open SQL, Conn, 1, 1If not RS.EOF then 'If a record is found Username = RS("Username") Email = RS("Email") DateRegistered = RS("DateRegistered") UserFound = Trueelse UserFound = FalseEnd ifRS.CloseConn.CloseSet Conn = nothingSet RS = nothingIf UserFound = True then%> <html> <head> <title><%=Username%>'s profile</title> </head> <body> <h1>Profile</h1> Username: <b><%=Username%></b><br> Email: <a href="mailto:<%=email%>"><%=email%><br> Registered since <%=DateRegistered%> </body> </html><%else%> <html> <head> <title>Oops!</title> </head> <body> Profile not found. </body> </html><%End if%> Note, sometimes pages can have a lot more than just profiles on them. For instance, you might have menus, headers, footers, and other information on your page. If this is the case, then you can boost ASP performance by creating your page like this: <html> <head> <title><% if UserFound = True then %> <%=Username%>'s profile <% else %> Profile not found <% end if %></title> </head> <body> <!-- header here --> <!-- menu here --> <h1>Profile</h1> <% if UserFound = True then %> Username: <b><%=Username%></b><br> Email: <a href="mailto:<%=email%>"><%=email%><br> Registered since <%=DateRegistered%> <% else %> User not found. <% end if %> <!-- footer here --> </body> </html> Finally, login pages are created in an almost identical fashion above: login.asp: <% Option Explicit %><html><body><h1>Login</h1><% Dim msgmsg = request("msg")if cstr(msg) <> "" then%><font color="red"><%=Server.HTMLEncode(msg)%></font><%end if%> <form action="login1.asp" method="post"> <input type="text" name="username" value=<%=Server.HTMLEncode(request("Username"))%>><br> <input type="password" name="password"><br> <input type="submit" value="Register"><br> <small>(Cookie must be enabled to login.)</small> </form></body></html> Login1.asp: <% Option ExplicitFunction SQLSecurity(myStr) 'This eliminates the risk of SQL injection SQLSecurity = Replace(myStr, "'", "''", 1, -1, vbBinaryCompare)End FunctionDim Username, PasswordDim Conn, RS, SQLUsername = cstr(request("username"))Password = cstr(request("password"))'Notice that, unlike Signup1.asp, we don't have to validate the username or'password for correct length or type.Set Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")SQL = "Select ID, Username, Password From Users WHERE " & _ "Username = '" & SQLSecurity(Username) & "' AND " & _ "Password = '" & SQLSecurity(Password) & "';"RS.Open SQL, Conn, 1, 1 If not RS.EOF then 'Record found 'Setting cookies for the user. These cookies will expire in 1 year. Response.Cookies("account")("username") = RS("Username") Response.Cookies("account")("password") = RS("Password") Response.Cookies("account").Expires = DateAdd("m", 12, Now()) response.redirect "profile.asp?id=" & RS("ID") else response.redirect "login.asp?msg=Username or password incorrect&" _ "username=" & username End ifRS.CloseSet Conn = nothingSet RS = nothing%> Notice that, on each page, the SQLSecurity function is included near the top of them. Its good to use that function for any user input to the database, but copying and pasting the same code over and over on each page isn't very smart, because if for some reason you need to make a change to the function, then you'd have to make identical changes on each page where the function exists. So instead, we just create an include file where the function sits. Include files are exactly like regular asp pages, except you don't put Option Explicit at the top of them. Example: functions.asp: <%Function SQLSecurity(myStr) 'This eliminates the risk of SQL injection SQLSecurity = Replace(myStr, "'", "''", 1, -1, vbBinaryCompare)End Function%> Now, you just include that page into any other asp page like this: someotherpage.asp: <% Option Explicit%><!--#include file="functions.asp"--><%'ASP code%> Notice that the include file is outside between the two <% and %> delimeters.Include files are especially useful with headers and footers. Almost always, I create websites that use a standard template, so I don't have to reuse the same code over and over on all of my pages. I usually create a header.asp and footer.asp file like this:header.asp: <% Option ExplicitDim Conn, RS, SQL%><html><head><title>Site Title</title></head><body><div align="center"> <a href="default.asp"> <img src="logo.gif" width="600" height="150" alt="Home"> </a></div><div align="left" style="width : 300px; background : #FF0000"><b>Menu</b><ul> <li><a href="folder.asp?id=1">Kitties</a></li> <li><a href="folder.asp?id=2">Bunnies</a></li> <li><a href="folder.asp?id=3">About This Site</a></li></ul></div> footer.asp: <div align="center"><a href="folder.asp?id=3">About</a> |<a href="folder.asp?id=20">Contact Me</a><div align="center">Copyright © 2006 by Yahweh.</div></body></html> For each new page, I create, I can reuse those templates over and over like this, somepage.asp: <!--#include file="header.asp"-->Page content goes here.<!--#include file="footer.asp"--> By using Option Explicit in my header.asp page, I don't have to add that statement to the top of any of my other pages. Similarly, by declaring my Conn, RS, and SQL variables, I don't have to redeclare them on any pages where header.asp is included. This saves me a lot of code (usually, I would put all of my functions and global variables in one include file called functions.asp, and put all of my HTML templates in other files like header.asp and footer.asp).And yes, you can have include files in your include files like this: functions.asp <% Option ExplicitFunction SQLSecurity(myStr) 'This eliminates the risk of SQL injection SQLSecurity = Replace(myStr, "'", "''", 1, -1, vbBinaryCompare)End Function%> header.asp: <!--#include file="functions.asp"-->HTML template goes here. Anotherpage.asp: <!--#include file="header.asp"-->
  24. I've figured out regexes and can work with them pretty easily now. Now, I'm having difficulty working with ADO.Net.Basically, I want to recreate these processes from ASP to ASP.Net: 'Create a new recordConn.OpenRS.Open "Select * From MyTable WHERE 0=1", Conn, 3, 3 RS.AddNew RS("Field1") = SomeValue RS("Field2") = AnotherValue RS("Field3") = FinalValue RS.UpdateRS.CloseConn.Close'Updating a recordConn.OpenRS.Open "Select * From MyTable WHERE ID = 5", Conn, 3, 3 RS("Field1") = SomeValue RS("Field2") = AnotherValue RS("Field3") = FinalValue RS.UpdateRS.CloseConn.Close Notice that I can update and add new records without using UPDATE and INSERT INTO sql commands. I like that method because I don't have to escape any of the values I put into my database, its much clearer to read than the SQL equivalent, and it gives me a little more control over what gets updated.However, I've searched and searched, but all the tutorials I've seen online update databases with SQL commands. I want to use the same ADO.Net equivalent to the above process (using SQL Server), so how do I programmatically create and update with ADO.Net, without using sql INSERT INTO and UPDATE?
  25. So if wanted to select rows 10000 to 10020, I'd have to use something like this:select top 10 * from(select top 10020 *from tblContactorder by LastName asc) dtorder by LastName desc I think it would be murder on my site site to be constantly selecting 10s of 10000s, or even millions of rows, when I only need 10.
  • Create New...