damn sql...

Printable View

damn sql...

Hey. I am working on a project in SQL Server & VB.NET. On the web form there is a single line textbox and a mutli-line textbox. When you click the button it’s supposed to update a entry in the SQL Database. But when I try using this code it won’t work, or it will but it will be all strange??!??!

VB Code:

Dim strRedirect As String
        strRedirect = Request("uid")
        Dim tempstringa As String = TextBox1.Text
        Dim tempstringb As String = TextBox2.Text
        Dim cmd As SqlCommand
        Dim cnn As SqlConnection
        cnn = New SqlConnection("server=localhost;user=dex2;pwd=password1;database=pocketdotnet;")
        Dim tempstringc As String = "UPDATE " & Me.Session.Item("workgroup") & "News set title='%%%%', disc='%%%%%' where uid='" & strRedirect & "'"
        tempstringc = Replace(tempstringc, "'%%%%'", "'" + Server.HtmlEncode(tempstringa) + "'")
        tempstringc = Replace(tempstringc, "'%%%%%'", "'" + Server.HtmlEncode(tempstringb) + "'")
        cmd = New SqlCommand(Server.HtmlEncode(tempstringc), cnn)
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()

well, you don't need the HtmlEncode stuff. ADO.NET has good support for parameters in inline sql, so I'd use those instead of replacing stuff, makes your code a little more readable. Maybe try building your sql something like this:

VB Code:

Dim uid As String = Request.QueryString.Item("uid")
Dim connString As String = "server=localhost;user=dex2;pwd=password1;database=pocketdotnet;"
Dim cn As New SqlConnection(connString)
Dim cmdText As New StringBuilder
cmdText.Append(String.Format("Update {0}News Set", Me.Session.Item("workgroup").ToString()))
cmdText.Append("title = @title ")
cmdText.Append(", disc = @disc ")
cmdText.Append("Where uid = @uid")
Dim cmd As New SqlCommand(cmdText.ToString(), cn)
cmd.Parameters.Add(New SqlParameter("@title", TextBox1.Text))
cmd.Parameters.Add(New SqlParameter("@disc", TextBox2.Text))
cmd.Parameters.Add(New SqlParameter("@uid", uid))

Dec 6th, 2003, 05:41 PM
area91

That works well but it doesn’t work if one of the textbox’s contains multiple lines.

Thanks!

Somethin else must be wrong cuz that does work with multiline textboxes. Here's a little tester:

VB Code:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Text" %>
<script language="vb" runat="server">
Protected Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    Dim connString As String = "user id=sa;password=sa;database=Scratch;server=DeathAngel;"
    Dim cn As New SqlConnection(connString)
    Dim cmdText As New StringBuilder()
    cmdText.Append ("Insert Into MultiLine ( value ) Values ( @value )")
    Dim cmd As New SqlCommand(cmdText.ToString(), cn)
    cmd.Parameters.Add(new SqlParameter("@value", TextBox1.Text))
    cmd.Connection.Open()
    cmd.ExecuteNonQuery()
    cmd.Connection.Close()
End Sub
</script>
<html>
    <body>
        <form runat="server">
            <asp:TextBox ID="TextBox1" Runat="server" 
                TextMode="MultiLine" Columns="40" Rows="10"/><br/>
            <asp:Button ID="btnSave" Runat="server" Text="Save" OnClick="btnSave_Click"/>
        </form>
    </body>
</html>

and the table being inserted into looks like this:

Code:

create table MultiLine

(

                [id] int not null identity primary key

        ,        value varchar ( 200 )

)

Dec 6th, 2003, 08:52 PM
area91

Insert works just fine, it's update that is not working!?!?!
Dec 6th, 2003, 11:14 PM
pvb
hmmm, changed to an update and it worked fine:
VB Code:
```
cmdText.Append ("Update MultiLine Set value = @value where [id] = 1")
```
Maybe post the new update code that's not working.