Jump to content

Yahweh

Members
  • Posts

    186
  • Joined

  • Last visited

Everything posted by Yahweh

  1. Yahweh

    select reverse

    I think it would be nice if SQL had a syntax that allows you to say "Select incrementer as seats_available from some_table where incrementer between 1 and 10".I don't think you can actually get SQL to select a dynamic range like that, unless you have a table that lists all the possible seats. You probably need to handle your output by serverside code, by running through a loop of occupied seats and displaying only the unfilled seats.
  2. Yahweh

    New window

    Why would you need to do this serverside? Why not just use HTML to open your processing page in a new window?[preprocess.asp]<form action="process.asp" target="_blank">...<input type="submit" value="Go"></form> If you need to close your processing window and/or redirect the opener to another page, just use a java script: [process.asp]<%...' stuff...%><body onLoad="java script:opener.location='finish.asp';self.close();">
  3. Are you writing your own forum software (if so, what language)? Or installing pre-existing software like vBulletin?Writing your own software is a little more time consuming, but I've written plenty of boards, its not terribly difficult from a programming point of view.
  4. Yahweh

    CMS

    First, there's no earthly reason why you have to do anything in your code behind.If you can't upgrade .Net 2.0 and use the <asp:menu> control, you can create dynamic menus with bulleted list control:<asp:BulletedList ID="BulletedList1" runat="server" DisplayMode="HyperLink" DataSourceID="SqlDataSource1" DataTextField="Title" DataValueField="ID"></asp:BulletedList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="Select ID, Title From Table"></asp:SqlDataSource> That will display a bulleted list of hyperlinks.If you want to use a repeater, its virtually the same thing: <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <div><a href="page.aspx?id=<%# DataBinder.Eval(Container.DataItem, "ID") %>"> <%# DataBinder.Eval(Container.DataItem, "Title") %></a></div> </ItemTemplate></asp:Repeater><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="Select ID, Title From Table"></asp:SqlDataSource> The methods above are for simple, linear menus. If you need nested lists or display a tree list, then you run into issues: SQL doesn't know how to create hiearchial data. I've run into that problem, and I found a good workaround here. Basically, you set two fields in your database, a "depth" field and a "lineage field". An example table looks like this: Some_Blog-----ID Category Parent Depth Lineage1 Computers 0 0 /1/2 Programming 1 1 /1/2/3 Cooking 0 0 /3/4 Parts 2 2 /1/2/4/5 Websites 1 1 /1/5/6 Languages 2 2 /1/2/6/7 Monitor 4 3 /1/2/4/7/8 Vegan 3 1 /3/8/9 VB.Net 6 3 /1/2/6/9/10 Soups 3 1 /3/10/11 C++ 6 3 /1/2/6/11/ Searching, sorting, and displaying that kind of table in a list is very easy. When you sort by Lineage ASC, you get a perfectly hiearchial list, and you can sepearate levels by indenting your fields based on the depth. Its easiest to do that with a repeater: <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <div style="margin-left:"<%# DataBinder.Eval(Container.DataItem, "depth") %>em"> <a href="page.aspx?id=<%# DataBinder.Eval(Container.DataItem, "ID") %>"> <%# DataBinder.Eval(Container.DataItem, "Category") %></a> </div> </ItemTemplate></asp:Repeater><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="SELECT ID, Category, Depth FROM Some_Blog ORDER BY Lineage"></asp:SqlDataSource> I use that kind of code and table structure on my own sites. The nice part about the code above is that it displays a data tree in a SINGLE query (and its very fast), whereas using nested repeaters require dozens and dozens of queries.
  5. The problem is obvious:Your markup reads like this:<select name="category" id="category" style="width:130px;">...<input name="Blog_01_Category" type="hidden" value="<%=rsCategories("Blog_01_Category")%>" /> But your code says this: strRequestBlog_01Category = Replace(CSTR(Request.Form("Blog_01_Category")), "'", "''", 1, -1, 1) You're dropdown list is called "category", but you don't ever Request.Form that value in your code. The element "Blog_01_Category" is your hidden element, which is apparently static. Change your code to this: strRequestBlog_01Category = Replace(CSTR(Request.Form("category")), "'", "''", 1, -1, 1) Just a syntax error in your SQL, you're missing a comma between 'beer' and 'c':'beer' 'c', '', 1, '', '', 'right' [...] ^ | needs a comma
  6. Without seeing the HTML code alongside the ASP, its hard to tell what's wrong. But the best way to troubleshoot this kind is error is setting your form's method to "get", so that can see your querystring. You never know, your error could be something really simple like mispelling the name of your dropdown list.If your querystring looks something "page.asp?Blog_01_Title=Hello&Blog_01_Category=&Blog_01_Intro=world", then probably the error has something to do with your HTML code. Your dropdown list should look like this: <select name="Blog_01_Category" size="10"><option value="1">Cat 1</option><option value="2">Cat 2</option><option value="3">Cat 3</option><option value="4">Cat 4</option><option value="5">Cat 5</option><option value="6">Cat 6</option></select> If you don't name your dropdown list, or set values in each of the options, then it isn't passed in the querystring collection.My last suggestion is to make sure your SQL code is correct. Your category field is probably an integer, but you have a line of code reading: strEnterSQL = strEnterSQL & "'" & strRequestBlog_01Category & "', " That's going to put single quotes around your Category, but MySQL can't implicitly convert string values to integer values. That kind of conversion would raise an error in other databases, but MySQL is apparently silencing the error. Try changing your code to this: strEnterSQL = strEnterSQL & strRequestBlog_01Category & ", "
  7. Use this: SELECT uni, rank as area_rank FROM ranking WHERE uni = 'Impreial' The "as area_rank" is an alias. It just renames your column to something else.You can also use this to display more rows: SELECT uni, rank as area_rank FROM ranking ORDER BY area_rank ASC
  8. Yes, I was trying to modify phpBB. I originally started using phpBB2 because it was free and popular, but the out-of-the-box version has a notorious reputation for security holes, so I followed the instructions on phpBBHacks.com to add a security mod. After my board was flooded with spam, I modified to add a CAPCHA, which elimiated 80% of the spam. Then I noticed the software is missing important features like a file upload, so I modified for that.I had 6 or 8 templates, which made it extremely difficult to update, because each template has to be modified seperately. Due to the difficulty of modifying phpBB, a mod was actually created specifically for the purpose of installing other mods... unfortunately, that auto-installer only works for the default phpBB template, and only works if the default template is unmodded. I eventually reduced down my templates to 1, just so I wouldn't have to spend hours and hours adding a single new mod to the software.Usually, I'm a huge advocate of the open source community, but only when the community can produce good software. No, my software isn't going to be released. It was written specifically for my site, not as an enterprise application.
  9. Quibble: I don't think most browsers will convert "<tagger>Blah</tagger>" to a comment. They'll usually display the word "Blah", so it would be the HTML equivalent of "<span>Blah</span>".
  10. I like the default layout, it looks very nice.But from a programming point of view, I hate phpBB with a vengeance. Simply the worst board software I've ever used. In addition to the templating system being impossibly complex, the structure of the database tables being misengineered, the sql queries abusing the JOIN to no end, my worst pet peeve had to be the bbCodes.- The bbCode module is 600+ lines long, all of the bbCode regexes are handtyped. You can't simply call a function called bbCodeParser and have it produce a regex for you, you have to type it yourself. Because regexes are handtyped, there is no consistent format to bbCodes or bbCode processing; that problem is especially evident in the awkward inconsistencies in bbCode formats, such as {size=12} and {quote="username"} (notice the parameter for the QUOTE code must appear in quotation marks or the tag will not process, but the parameter in the SIZE tag must not appear in quotation marks or it will not process).- its nearly impossible to add a new bbCode. The process requires editing 6 different files, adding 50 lines of code, writing your own regex, and hoping everything went smoothly (which is usually not the case).- On top of that, the parser it suffers from a bug where it can't parse nested bbCodes properly.After I apologized to my site's contributors for the 50th time about the shoddy board software and being unable to fix it, I decided I had enough of phpBB and I wrote my own software. (I'm happy to say that in the board system I wrote,called yBoard, my yCode module is 40 lines long, yCodes can be created with a single line of code or by adding a single record to the database, and it handles nested codes the way it should.)
  11. For Each student As String In studentInfo Console.WriteLine(student) Next See MSDN: Console.WriteLine(object): You're telling your program to print your student object, but it doesn't know how to format each field in the object. By default, it prints it out in serialized format, which means you have a line break after each field.Its better to use .Net's formatting to take care of the alignment of text for you. I recommend rewriting your Sub like this:Sub writeData() Console.WriteLine("Student Grade Report: 11/29/2006") Console.WriteLine("How to read this screen:") Console.Write("{0,-20}{1,-20}{2,-20}", "Name:", "Grade Average:", "Grade") Console.WriteLine("") For Each student In studentInfo 'don't cast student as String Console.WriteLine("{0,-20}{1,-20}{2,-20}", _ student.firstName & " " & student.LastName, _ student.examAverage, _ student.examGrade) Next End Sub
  12. I can show you the code, but if you want to understand it, then you have to understand that literally, the "VALUES (...)" clause is a recordset, fundamentally no different from the recordset returned by any SQL query. That might not mean much to you, until you realize that the following two queries are identical:INSERT INTO table (field1)VALUES ('monkey');INSERT INTO table (field1)SELECT 'monkey'; -- the query "SELECT 'monkey'" returns a single record with a single field containing the value 'monkey' As you can probably guess, its not that hard to alter many columns, its not different than writing a select statement with many columns. The following two queries are identical: INSERT INTO table1 (field1, field2, field3, field4)VALUES ('1', 'monkey', 'jesh', 'aspnetguy')INSERT INTO table1 (field1, field2, field3, field4)SELECT field1, field2, field3, field4FROM sometableWHERE [conditions] For your application, you might use this code: INSERT INTO acc_list (ID, article_title, date_added)SELECT Max(b.ID + 1), 'Article on monkeys', Now()FROM acc_list b Its very simple, and very intuitive. Just be aware of two things:1) Your select statement should contain exactly the number fields that you are updating.2) You will insert exactly the number of rows returned by your select statement. If your select statement returns 12 rows, you will insert 12 new rows into your table.
  13. Yahweh

    asp strip_tags()

    It just occurred to me, another way to get rid of cross site scripting is using something like this: Function sanitize_input(someString) santize_input = Server.HTMLEncode(someString)End Function That converts >, <, " (double quotes), and a few other characters to their harmless ascii equivalents, >, &lt, &quote;.
  14. That depends on what the dates are and which date you want to select.If you have records with duplicate names and surnames, and each record has the same date, then you don't need to change the query much at all.SELECT DISTINCT name, surname, `date` FROM table Otherwise, if the date is different for each record, then you need to use a join: SELECT DISTINCT a.name, a.surname, Max(b.`date`) FROM table aJOIN table b ON (a.id = b.id)GROUP BY a.name, a.surname Note: I haven't tested the queries above. I don't know if it will attempt to return rows with distinct max/min dates or not.
  15. Use this: INSERT INTO acc_list (ID)SELECT Max(b.ID + 1)FROM acc_list b Notice that the query above does not have a "VALUES (...)" clause. It doesn't need one because its using a SELECT.
  16. Yahweh

    Exclusive Join?

    I imagine that you have two tables like this: gallery_table-------------------------galleryID galleryTitle1 2007-01-012 2007-01-053 2007-02-174 2007-02-21image_tableimageID galleryID (foreign key goes back to gallery_table.galleryID)1 12 13 34 45 36 17 28 39 1 Its a helluva lot easier to add a "totalImages" column to your gallery table, and perform a simple "SELECT * FROM gallery_table" to get the number of images in each gallery. When you have 10s of 1000s of records, your queries begin to lag using a subquery or complex join.But, if you really want to go the route of using a join, you can do it two ways:Use a subquery: SELECT g.galleryid, g.gallerytitle, ( Select Count(*) FROM image_table i WHERE g.galleryID = i.galleryID ) as totalImagesFROM gallery_table g Use a join: SELECT g.galleryID, g.gallerytitle, i.Count(*) as totalImagesFROM gallery_table gLEFT JOIN image_table i ON (g.galleryID = i.galleryID)GROUP BY g.galleryID, g.galleryTitle The GROUP BY is needed because the count(*) is an aggregate function. As long as you're comparing galleryID's, then you won't get any records from your image table with a null value, so you don't need to use an INNER JOIN.
  17. This will work: SELECT DISTINCT name, surname FROM table;
  18. Most newbie programmers use Access. Then, when they get good at programming and move on to big projects, and wanting to build medium to high traffic websites, they claw out their eyes because Access just doesn't cut it. Then they switch to MySQL or SQL Server.If you want to use Microsoft technologies, go to ASP.Net. Classic ASP is essentially a dead language now, and the future of all web development is moving in a .Net direction. I recommend using SQL Server, but MySQL usually comes for free on most hosts.
  19. Yahweh

    asp strip_tags()

    You need to define your own strip_tags() function. You can strip tags easily with regex. Public Function strip_tags(someString) Dim myRegex Set myRegex = new regexp myRegex.global = True myRegex.IgnoreCase = true myRegex.Multiline = true myRegex.Pattern = "<[^> ]+[\s\S]*?>" 'matches any valid tag strip_tags = myRegex.Replace(someString, "")End Function You might need a more powerful regex, because that won't catch malformed tags. However, it should be good enough to get you started.
  20. 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.
  21. 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
  22. 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>
  23. 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;
  24. 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
×
×
  • Create New...