locked
"Quotation marks" / conacatenating / passwords .... RRS feed

  • Question

  • User-1884300999 posted

    Hi, I hope one of you can help me with this....

     I have an asp.net dropdown list where users select their height:
     

                <asp:DropDownList CssClass="style1" id="txtHeight" runat="server" width="200px">
                                <asp:ListItem value="" text="Select from list" />
                                <asp:ListItem value="I'd rather not say" text="I'd rather not say" />                           
                                <asp:ListItem value="under 4' 7&quot; "  " text="under 4' 7"  " />                           
                                <asp:ListItem value="under 4' 8&quot; " text="under 4' 8"" />                           
                                <asp:ListItem value="under 4' 9&quot; " text="under 4' 9" " />
                                <asp:ListItem .... blah blah etc etc.......
                    </asp:DropDownList>

     

     I'm using &quot; to represent " in the height values to avoid syntax errors when the page runs. however, when i try to store this info in an Access database, i get a

    Syntax error (missing operator) in query expression ...blah blah blah...

    I need to strore the values in this format so that they are displayed properly when retrieved from the database. How can i get around this?

    I'm fairly new to asp.net and Access so any help would be greatly appreciated.

     
    Thanks id advance
     

    Monday, June 2, 2008 6:10 AM

Answers

  • User1191518856 posted

    You could use the Session object to store this data after the first postback. Example:

    Session("password") = txtPass.Value

    Then, at your last screen you can retrieve the value like this:

    Dim password As String = Session("password").ToString()
      
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 3, 2008 10:21 AM

All replies

  • User1191518856 posted

    You get this error because you haven't escaped the value to insert in a correct manner. If the value contains an apostrophe, the database will be confused since this character is used to denote the limits of a string. To come around this, I recommend you to start using parametrized queries, providing the values as OleDbParameters to the OleDbCommand.Parameters collection. This way, you will get a couple of benefits; more readable code, protection against SQL injections and automatic escaping of special characters.

    See http://www.mikesdotnetting.com/Article.aspx?ArticleID=26 for examples on how to use parametrized queries with MS Access.

    Monday, June 2, 2008 6:53 AM
  • User-1884300999 posted

    Thank you for your reply.

    I had a look at the link but to be honest i'm a bit confused as to what i need to change to get this to work.

    Most of my code is cobbled together from other examples and tutorials, so my understanding of how it actually works is pretty limited.

    If somebody has a minute could you take a look and maybe point out where i need to make the changes?


     

     

     <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDB" %>
    <%@ Page Language="VB" Debug="true" %>


    <html>
    <head>
    <link type="text/css" rel="stylesheet" href="style.css">
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">


           
    <script runat="server">

    sub OnBtnSendClicked (s As Object, e As EventArgs)
        Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("my_database.mdb") & ";"
       
        Dim MySQL as string = "INSERT INTO my_table ([Height]) VALUES ('" & txtHeight.Text & "')"
       
        Dim MyConn as New OleDBConnection (strConn)
        Dim cmd as New OleDBCommand (MySQL, MyConn)
        MyConn.Open ()
        cmd.ExecuteNonQuery ()
        MyConn.Close ()
       
    end sub

       
    </script>

    </head>
    <body>


    <form runat="server">

            <asp:DropDownList CssClass="style1" ID="txtHeight" runat="server" Width="200px">
                  <asp:ListItem Value="" Text="Select from list" />           
                  <asp:ListItem Value="I would rather not say" Text="I'd rather not say" />           
                  <asp:ListItem Value="4'7&quot;  " Text="4'7&quot; " />           
                  <asp:ListItem Value="4'8&quot;  " Text="4'8&quot;  " />           
                  <asp:ListItem Value="4'9&quot;  " Text="4'9&quot;  " />           
                  <asp:ListItem Value="5'0&quot;  " Text="5'0&quot;  " />           
                  <asp:ListItem Value="5'1&quot;  " Text="5'1&quot;  " />           
             
                  <asp:ListItem Value="over 6'6&quot;  " Text="over 6'6&quot;  " />           
            </asp:DropDownList>


    <br><br>


    <asp:button id="btnSend" onclick="OnBtnSendClicked" runat="server" text="Send"></asp:button>

    </form>

    </body>
    </html>

     
    Thank you for any help you can give.

    3rror404
     

    Monday, June 2, 2008 8:35 AM
  • User1191518856 posted

    Try this:

    Dim MySQL as string = "INSERT INTO my_table ([Height]) VALUES (@Height)"
        
    Dim MyConn as New OleDBConnection (strConn)
    Dim cmd as New OleDBCommand (MySQL, MyConn)
    cmd.Parameters.AddWithValue ("@Height", txtHeight.Text)
    MyConn.Open ()
    cmd.ExecuteNonQuery ()
    MyConn.Close ()
      
    Monday, June 2, 2008 1:14 PM
  • User-1884300999 posted

    Thanks Johram, that worked perfectly.

    One more question though....

    Below is the code so far. Is it possible to combine the the three figures from txtDOBday, txtDOBmonth and txtDOByear in to one database entry in the format 02.04.1963 ?  

     
     
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDB" %>
    <%@ Page Language="VB" Debug="true" %>


    <html>
    <head>
    <link type="text/css" rel="stylesheet" href="style.css">
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

    <script runat="server">

    sub OnBtnSendClicked (s As Object, e As EventArgs)
    Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("my_database.mdb") & ";" Dim MySQL as string = "INSERT INTO my_table ([Height]) VALUES (@Height)" Dim MyConn as New OleDBConnection (strConn)
    Dim cmd as New OleDBCommand (MySQL, MyConn)
    cmd.Parameters.AddWithValue ("@Height", txtHeight.Text)

    MyConn.Open ()
    cmd.ExecuteNonQuery ()
    MyConn.Close ()

    end sub


    </script>

    </head>
    <body>


    <form runat="server">

    <asp:DropDownList CssClass="style1" ID="txtHeight" runat="server" Width="200px">
    <asp:ListItem Value="" Text="Select from list" />
    <asp:ListItem Value="I would rather not say" Text="I'd rather not say" /> <asp:ListItem Value="4'7" " Text="4'7" " /> <asp:ListItem Value="4'8" " Text="4'8" " /> <asp:ListItem Value="4'9" " Text="4'9" " /> <asp:ListItem Value="5'0" " Text="5'0" " /> <asp:ListItem Value="5'1" " Text="5'1" " /> <asp:ListItem Value="5'2" " Text="5'2" " /> <asp:ListItem Value="5'3" " Text="5'3" " /> <asp:ListItem Value="5'4" " Text="5'4" " /> <asp:ListItem Value="5'5" " Text="5'5" " /> <asp:ListItem Value="5'6" " Text="5'6" " /> <asp:ListItem Value="5'7" " Text="5'7" " /> <asp:ListItem Value="5'8" " Text="5'8" " /> <asp:ListItem Value="5'9" " Text="5'9" " /> <asp:ListItem Value="5'10" " Text="5'10" " /> <asp:ListItem Value="5'11" " Text="5'11" " /> <asp:ListItem Value="6'0" " Text="6'0" " /> <asp:ListItem Value="6'1" " Text="6'1" " /> <asp:ListItem Value="6'2" " Text="6'2" " /> <asp:ListItem Value="6'3" " Text="6'3" " /> <asp:ListItem Value="6'4" " Text="6'4" " /> <asp:ListItem Value="6'5" " Text="6'5" " /> <asp:ListItem Value="6'6" " Text="6'6" " /> <asp:ListItem Value="over 6'6" " Text="over 6'6" " /> </asp:DropDownList> <br><br> <asp:DropDownList CssClass="style1" id="txtDOBday" runat="server">
    <asp:ListItem Selected="True" Value="" Text="Day"/>
    <asp:ListItem Value="01" Text="01" />
    <asp:ListItem Value="02" Text="02" />
    <asp:ListItem Value="03" Text="03" />
    <asp:ListItem Value="04" Text="04" />
    <asp:ListItem Value="05" Text="05" />
    <asp:ListItem Value="06" Text="06" />
    <asp:ListItem Value="07" Text="07" />
    </asp:DropDownList>

    <asp:DropDownList CssClass="style1" id="txtDOBmonth" runat="server">
    <asp:ListItem Selected="True" Value="" Text="Month"/>
    <asp:ListItem Value="01" Text="01" />
    <asp:ListItem Value="02" Text="02" />
    <asp:ListItem Value="03" Text="03" />
    <asp:ListItem Value="04" Text="04" />
    <asp:ListItem Value="05" Text="05" />
    <asp:ListItem Value="06" Text="06" />
    <asp:ListItem Value="07" Text="07" />
    </asp:DropDownList>


    <asp:DropDownList CssClass="style1" id="txtDOByear" runat="server">
    <asp:ListItem Selected="True" Value="" Text="Year"/>
    <asp:ListItem Value="1960" Text="1960" />
    <asp:ListItem Value="1961" Text="1961" />
    <asp:ListItem Value="1962" Text="1962" />
    <asp:ListItem Value="1963" Text="1963" />
    <asp:ListItem Value="1964" Text="1964" />
    <asp:ListItem Value="1965" Text="1965" />
    <asp:ListItem Value="1966" Text="1966" />
    </asp:DropDownList>


    <br><br>
    <asp:button id="btnSend" onclick="OnBtnSendClicked" runat="server" font-names="Tahoma" text="Send"></asp:button>

    </form>

    </body>
    </html>
     
     
    Tuesday, June 3, 2008 6:57 AM
  • User1191518856 posted

    Sure you can. You could build a parameter by concatenating the values together.

    Dim cmd as New OleDBCommand ("INSERT INTO Sometable (DOB) VALUES (@DOB)", MyConn)
    cmd.Parameters.AddWithValue ("@DOB", string.Format("{0}.{1}.{2}", txtDOBday.SelectedValue, txtDOBmonth.SelectedValue, txtDOByear.SelectedValue))

    Also, you might want to consider revising your control naming. txtDOByear is not really appropriate for a DropDownList. If you like to stick with the hungarian notation (where you prefix the name with type), please use the correct type. txt-prefix is typically used for asp:TextBox controls. DropDownList usually have a ddl prefix.

    Tuesday, June 3, 2008 7:19 AM
  • User-1884300999 posted

    Thank you. Spot on again.

    Since you are being so helpful, i wonder if you could help with another problem i've just found?

    I have a text box to collect a password when users register.

    This is part of a multi-page form using <asp:panel>'s spanning 6 pages.

    This works as an ordanary text box, but when I set the "TextMode" to "password", the form doesnt save the password when the next panel loads and so doesnt get stored in the database.

    Can I store the password in memory untill the rest of the form is complete and then save to the database with the rest of the info? 

    Here's the (simplified) code.....

     

     

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDB" %>
    <%@ Page Language="VB" Debug="true" %>


    <html>
    <head>
    <link type="text/css" rel="stylesheet" href="style.css">
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

    <script runat="server">

    sub OnBtnSendClicked (s As Object, e As EventArgs)
    Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("my_database.mdb") & ";" Dim MySQL as string = "INSERT INTO my_table ([Pass]) VALUES (@Pass)" Dim MyConn as New OleDBConnection (strConn)
    Dim cmd as New OleDBCommand (MySQL, MyConn)
    cmd.Parameters.AddWithValue ("@Pass", txtPass.Text)

    MyConn.Open ()
    cmd.ExecuteNonQuery ()
    MyConn.Close ()

    end sub

    Sub Page_Load(sender As Object, e As EventArgs)
    ' If this is first run set page number to 1 If Not Page.IsPostBack Then PageNumber.Text = 1 End If ShowCurrentPage() End Sub


    Sub
    btnNext_OnClick(sender As Object, e As EventArgs)
    PageNumber.Text = PageNumber.Text + 1
    ShowCurrentPage()
    End Sub

    Sub
    ShowCurrentPage()
    panel1.Visible = False panel2.Visible = False

    Select Case
    PageNumber.Text
    Case 1
    panel1.Visible = True


    Case
    2
    panel2.Visible = True
    End Select
    End Sub
    </script> </head> <body> <form runat="server">

    <asp:Literal id="PageNumber" runat="server" Visible = "False" />

    <asp:panel id="panel1" runat="server">

    <asp:TextBox CssClass="style1" id="txtPass" runat="server" Width="200px" TextMode="password"/>

    <asp:button id="btnNext" onclick="btnNext_OnClick" runat="server" font-names="Tahoma" text="Next"></asp:button>

    </asp:panel>

    <asp:panel id="panel2" runat="server">

    HELLO!
    <br><br>
    <asp:button id="btnSend" onclick="OnBtnSendClicked" runat="server" font-names="Tahoma" text="Send"></asp:button>
    </asp:panel>
    </form>

    </body>
    </html>
     
     
    Tuesday, June 3, 2008 9:27 AM
  • User1191518856 posted

    You could use the Session object to store this data after the first postback. Example:

    Session("password") = txtPass.Value

    Then, at your last screen you can retrieve the value like this:

    Dim password As String = Session("password").ToString()
      
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 3, 2008 10:21 AM
  • User-1884300999 posted

     Hi again, I'm back with another one of my incredibly stupid questions!

    Thanks for the session sugestion. I've been away from this project for a couple of weeks but i'm getting pressured to make some progress with it. I've been having a play with sessions but i'm a little stuck.

    So far, i can store a password to the database from the first and second panel, but the third just saves a blank entry.

    I'm not sure where to put the   Dim password As String = Session("password").ToString()

    Here's my code.....

     

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDB" %>
    <%@ Page Language="VB" Debug="true" %>


    <html>
    <head>
    <link type="text/css" rel="stylesheet" href="style.css">
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

    <script runat="server">

    sub OnBtnSendClicked (s As Object, e As EventArgs)
    Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("my_database.mdb") & ";" Dim MySQL as string = "INSERT INTO my_table ([Pass]) VALUES (@Pass)" Dim MyConn as New OleDBConnection (strConn)
    Dim cmd as New OleDBCommand (MySQL, MyConn)
    cmd.Parameters.AddWithValue ("@Pass", session("txtPassword").tostring)

    MyConn.Open ()
    cmd.ExecuteNonQuery ()
    MyConn.Close ()

    end sub

    Sub Page_Load(sender As Object, e As EventArgs)
    ' If this is first run set page number to 1 If Not Page.IsPostBack Then PageNumber.Text = 1 End If ShowCurrentPage() End Sub


    Sub
    btnNext_OnClick(sender As Object, e As EventArgs)
    PageNumber.Text = PageNumber.Text + 1
    ShowCurrentPage()
    session("txtPassword") = txtPass.Text
    End Sub

    Sub
    ShowCurrentPage()
    panel1.Visible = False panel2.Visible = False panel3.Visible = False

    Select Case
    PageNumber.Text
    Case 1
    panel1.Visible = True

    Case
    2
    panel2.Visible = True

    Case
    3
    panel3.Visible = True

    End Select
    End Sub
    </script> </head> <body> <form runat="server">

    <asp:Literal id="PageNumber" runat="server" Visible = "False" />

    <asp:panel id="panel1" runat="server">

    <asp:TextBox CssClass="style1" id="txtPass" runat="server" Width="200px" TextMode="password"/>

    </asp:panel>

    <asp:panel id="panel2" runat="server">

    Click next...

    <br><br>

    </asp:panel>

    <asp:panel id="panel3" runat="server">

    HELLO!
    <br><br>

    </asp:panel>

    <asp:button id="btnNext" onclick="btnNext_OnClick" runat="server" font-names="Tahoma" text="Next"></asp:button>
    <asp:button id="btnSend" onclick="OnBtnSendClicked" runat="server" font-names="Tahoma" text="Send"></asp:button>

    </form>

    </body>
    </html>

      ---------------------------------

    Please help!!  

    Wednesday, June 18, 2008 7:15 AM
  • User-1884300999 posted

     bump!
     

    Tuesday, June 24, 2008 10:27 AM
  • User-821857111 posted

     bump!
     
     

    When you have a new question, you should start a new post, really.  Your original question (which is the title of this thread) was resolved, so you should have marked the answer, then moved on to a new post altogether. 

     

    Tuesday, June 24, 2008 4:17 PM