Code Forums - View Single Post

doobiwan · 05-16-2005, 11:04 AM

hola,

starting to play with databasing and w dynamic web design, and for one of my first projects i'm coding a simple blog app.

i have two questions that i'm hoping someone here can help with.

A> first, to retrieve the info from the blog, i have 2 different statements for 2 different user controls. the first (on the front page) selects the top 3 (newest) entries. the second (on the main blog page) selects all entries. below are the SQL statements:

select 3 newest: SELECT TOP 3 blog_date,blog_title,blog_entry FROM gsol_blog ORDER BY blog_id DESC;

select all: SELECT * blog_date,blog_title,blog_entry FROM gsol_blog ORDER BY blog_id DESC;

the SELECT TOP 3 statement does not work, can anyone tell me why? below is the error that i get:

System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-4.1.8-nt-max]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 blog_id AS blog_date,blog_title,blog_entry FROM gsol_blog ORDE at System.Data.Odbc.OdbcConnection.HandleError(Handle Ref hrHandle, SQL_HANDLE hType, RETCODE retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method) at System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior behavior) at ASP.blogReadtop3_aspx.Page_Load() in C:\Sites\Single9\doobiwankenobi\webroot\blogreadto p3.aspx:line 23

i honestly don't know much about SQL yet, but this statement looks valid to me (unless my brain is fried, which is a definate possibility).

B> second, i have a simple page that i wrote to create entries and post them to the blog table in my mySQL db. the INSERT statement works, but i cannot put apostrophes or certain other punctuation into my blog entry .i'm assuming that because of the way i've coded it, these punctuation marks are being interpreted into the SQL statement and throwing the code. i would love it if someone could advise me on a better way to code my input page so that punctuation in my entries would not affect the SQL statement when the page is submitted. below is the page code:

<%@ Page Language="VB" Debug="True" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Odbc" %>
<script runat="server">

Sub Page_load()

Dim timeStamp as string = dateTime.now.toString()
lbldate.text = timestamp

End Sub

Sub blogSubmit(s as object, e as eventArgs)
Dim connStr, qry As String
connStr = "Driver={MySQL ODBC 3.51 Driver};Server=****;uid=****;pwd=****;Database=*** *;"
qry = "INSERT INTO gsol_blog(blog_date,blog_title,blog_entry) VALUES ('"+lblDate.text+"','"+entryTitle.text+"', '"+entryBody.text+"')"

Try
Dim conn As OdbcConnection
conn = new OdbcConnection(connStr)

conn.Open()

Dim cmd As OdbcCommand
cmd = new OdbcCommand(qry, conn)
cmd.executeNonQuery()
conn.Close()
conn = Nothing
cmd = Nothing
entryTitle.text = ""
entryBody.text = ""
response.redirect("index.htm")
Catch ex As Exception
Response.Write(ex)
End Try
End Sub

</script>
<html>
<head>
<title>blog entry - test</title>
</head>
<body>
<form runat="server">
<asp:label id="lblDate" runat="server" /><br />
<asp:textbox id="entryTitle" runat="server"
textmode="singleline"
columns="50" /><br />
<asp:textbox id="entryBody" runat="server"
textmode="multiline"
columns="50"
rows="25" /><br />
<asp:button text="submit" onClick="blogSubmit" runat="server" />
<asp:label id="lblResults" runat="server" />

</form>
</body>
</html>

like i said, it's a really simple page and it works...but i'm sure that there is a better way to put the text values into the INSERT statement so that i don't have to restrict myself to not using punctuation that will cause problems.

i thank anyone in advance that has the time and the willingness to throw me a bone. this has been annoying me greatly all weekend. i'm sure it's something really simple and obvious, too....but i just can't figure it out.

once again, thanks for any help on this.

05-16-2005, 11:04 AM	#1 (permalink)
doobiwan Registered User Join Date: May 2005 Posts: 2	help with SQL and VB.NET hola, starting to play with databasing and w dynamic web design, and for one of my first projects i'm coding a simple blog app. i have two questions that i'm hoping someone here can help with. A> first, to retrieve the info from the blog, i have 2 different statements for 2 different user controls. the first (on the front page) selects the top 3 (newest) entries. the second (on the main blog page) selects all entries. below are the SQL statements: select 3 newest: SELECT TOP 3 blog_date,blog_title,blog_entry FROM gsol_blog ORDER BY blog_id DESC; select all: *SELECT blog_date,blog_title,blog_entry FROM gsol_blog ORDER BY blog_id DESC; the SELECT TOP 3 statement does not work, can anyone tell me why? below is the error that i get: System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-4.1.8-nt-max]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 blog_id AS blog_date,blog_title,blog_entry FROM gsol_blog ORDE at System.Data.Odbc.OdbcConnection.HandleError(Handle Ref hrHandle, SQL_HANDLE hType, RETCODE retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method) at System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior behavior) at ASP.blogReadtop3_aspx.Page_Load() in C:\Sites\Single9\doobiwankenobi\webroot\blogreadto p3.aspx:line 23 i honestly don't know much about SQL yet, but this statement looks valid to me (unless my brain is fried, which is a definate possibility). B> second, i have a simple page that i wrote to create entries and post them to the blog table in my mySQL db. the INSERT statement works, but i cannot put apostrophes or certain other punctuation into my blog entry .i'm assuming that because of the way i've coded it, these punctuation marks are being interpreted into the SQL statement and throwing the code. i would love it if someone could advise me on a better way to code my input page so that punctuation in my entries would not affect the SQL statement when the page is submitted. below is the page code: <%@ Page Language="VB" Debug="True" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.Odbc" %> <script runat="server"> Sub Page_load() Dim timeStamp as string = dateTime.now.toString() lbldate.text = timestamp End Sub Sub blogSubmit(s as object, e as eventArgs) Dim connStr, qry As String connStr = "Driver={MySQL ODBC 3.51 Driver};Server=;uid=;pwd=;Database=* *;" qry = "INSERT INTO gsol_blog(blog_date,blog_title,blog_entry) VALUES ('"+lblDate.text+"','"+entryTitle.text+"', '"+entryBody.text+"')" Try Dim conn As OdbcConnection conn = new OdbcConnection(connStr) conn.Open() Dim cmd As OdbcCommand cmd = new OdbcCommand(qry, conn) cmd.executeNonQuery() conn.Close() conn = Nothing cmd = Nothing entryTitle.text = "" entryBody.text = "" response.redirect("index.htm") Catch ex As Exception Response.Write(ex) End Try End Sub </script> <html> <head> <title>blog entry - test</title> </head> <body> <form runat="server"> <asp:label id="lblDate" runat="server" /><br /> <asp:textbox id="entryTitle" runat="server" textmode="singleline" columns="50" /><br /> <asp:textbox id="entryBody" runat="server" textmode="multiline" columns="50" rows="25" /><br /> <asp:button text="submit" onClick="blogSubmit" runat="server" /> <asp:label id="lblResults" runat="server" /> </form> </body> </html> like i said, it's a really simple page and it works...but i'm sure that there is a better way to put the text values into the INSERT statement so that i don't have to restrict myself to not using punctuation that will cause problems. i thank anyone in advance that has the time and the willingness to throw me a bone. this has been annoying me greatly all weekend. i'm sure it's something really simple and obvious, too....but i just can't figure it out. once again, thanks for any help on this.