7 Replies - 3239 Views - Last Post: 08 February 2011 - 03:33 PM Rate Topic: -----

#1 kmc5117  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 15
  • Joined: 22-October 10

Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 02:06 PM

So I've tried 2 different methods and each is giving me similar problems when joining the lines together.

The goal is to build a SQL Query from lines in a multiline text box. Where the result will look like;

SELECT * FROM TEST WHERE ITEM IN('Line 1','Line 2','Line 3','...');

But for some reason I keep getting the following with String.Join

SELECT * FROM TEST WHERE ITEM IN('Line 1','','Line 2','','Line 3','','Line 4','','Line 5');


Here is my .aspx file

    <asp:TextBox ID="TxtTest" runat="server" Height="250px" TextMode="MultiLine" 
        Width="134px" Wrap="False" MaxLength="7"></asp:TextBox>

        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>


    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>

    <asp:Button ID="ButtonTest" runat="server" Text="Button" Width="128px" 
            onclick="ButtonTest_Click" />
    </div>
    </form>



And here is my Code behind

    protected void ButtonTest_Click(object sender, EventArgs e)
    {
        StringBuilder test = new StringBuilder();
        test.Append("Select * FROM TEST WHERE ITEM IN ('");

        string s = TxtTest.Text;
        
        
        // Split string on newlines

        string[] lines = s.Split(Environment.NewLine.ToCharArray());

        //recombine the array elements with ','
        string result = String.Join("','", lines);

        test.Append(result);
        
        test.Append("');"); //closes SQL statement
                
        Label1.Text = test.ToString();
    }



Does anyone know why it is doing the ',' twice between the parts during runtime and how to correct it? I also tried it using StringBuilder with Append but it was giving me a similar issue.

TIA

This post has been edited by kmc5117: 08 February 2011 - 02:08 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Help Building a SQL Query string from a MultiLine Textbox

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,437
  • Joined: 23-August 08

Re: Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 02:10 PM

See also this previous topic.
Was This Post Helpful? 0
  • +
  • -

#3 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4437
  • View blog
  • Posts: 7,719
  • Joined: 08-June 10

Re: Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 02:26 PM

That's from the same person. Should these threads be merged?
Was This Post Helpful? 0
  • +
  • -

#4 kmc5117  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 15
  • Joined: 22-October 10

Re: Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 02:31 PM

View PostinsertAlias, on 08 February 2011 - 02:26 PM, said:

That's from the same person. Should these threads be merged?


The thread started out as an ASP.NET question and then I ended up getting stuck with this problem with the button click event so I posted it here in the C# forum. Sorry if I should have reference the old thread.
Was This Post Helpful? 0
  • +
  • -

#5 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4437
  • View blog
  • Posts: 7,719
  • Joined: 08-June 10

Re: Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 02:41 PM

It's because you have empty strings in your list. It looks like your lines are actually double spaced. Try making your Split like this:

s.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);

This post has been edited by insertAlias: 08 February 2011 - 02:42 PM

Was This Post Helpful? 1
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,437
  • Joined: 23-August 08

Re: Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 02:53 PM

View PostinsertAlias, on 08 February 2011 - 04:26 PM, said:

That's from the same person. Should these threads be merged?


Nah, I just added that for context/history.
Was This Post Helpful? 0
  • +
  • -

#7 kmc5117  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 15
  • Joined: 22-October 10

Re: Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 02:57 PM

View PostinsertAlias, on 08 February 2011 - 02:41 PM, said:

It's because you have empty strings in your list. It looks like your lines are actually double spaced. Try making your Split like this:

s.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);


Wow I would have never figured. Are multiline text boxes double space by default and is it a property you can set?

In any case thank you!
Was This Post Helpful? 0
  • +
  • -

#8 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4437
  • View blog
  • Posts: 7,719
  • Joined: 08-June 10

Re: Help Building a SQL Query string from a MultiLine Textbox

Posted 08 February 2011 - 03:33 PM

Quote

Are multiline text boxes double space by default and is it a property you can set?


Honestly, I have no idea. It just was obvious that there were extra entries in your string array.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1