CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Replace or substring

    Hi all,

    I will be very graceful if somebody could help me. I have to chose from 2 checkboxlist(Checkboxlist1, Checkboxlist2) some checkbox, with those I make the interogation in sql. The problem is that if I will select the second box from Checkboxlist1 and the second and the third boxes from Checkboxlist2, i will get in my select something like this:
    select ,b.number from table a join table b where a.name in (,six,one). How can i remove those commas from there?

    Thanks!

    ariana

  2. #2
    Join Date
    Oct 2001
    Posts
    80

    Re: Replace or substring

    string sql = "select ,b.number from table a join table b where a.name in (,six,one)".Replace(",", "");

  3. #3
    Join Date
    Aug 2006
    Posts
    4

    Re: Replace or substring

    Hy,

    I was trying that but this replace all the commas in the string and I receive that:
    string sql = "select b.number from table a join table b where a.name in (sixone)" and I want to receive this one:

    string sql = "select b.number from table a join table b where a.name in (six,one)".

    Thank you !

  4. #4
    Join Date
    Oct 2001
    Posts
    80

    Re: Replace or substring

    huh? It's a bit confusing now...

    Honestly, it looks like there is a logical error in your function that creates the SQL syntax. Maybe you should be wondering why the commas are there in the first place instead of patching it up afterwards.

    I'm guessing here, but it looks like you are adding strings appended with a comma to an already existing string. If the string to add is empty, you still add the comma, which is not good.

    I'd help out, if you posted some more code so I can see what you are doing.

  5. #5
    Join Date
    Aug 2006
    Posts
    4

    Re: Replace or substring

    This is the code:

    if (CheckBox1.Checked)

    {
    strResult = (strResult
    + "t.name");
    }

    if (CheckBox2.Checked)

    {

    strResult = (strResult
    + ", " + "t.last");

    }

    if (CheckBox3.Checked)

    {

    strResult = (strResult
    + ", " + "count(t.this)");

    }
    if (CheckBoxList1.Items[0].Selected)
    {
    strResult1 = (strResult1 + "'Mt'");
    }
    if (CheckBoxList1.Items[1].Selected)
    {
    strResult1 = (strResult1 + "," + "'MP'");
    }

    if (CheckBoxList1.Items[2].Selected)
    {
    strResult1 = (strResult1 + "," + "'MG'");
    }
    if (CheckBoxList1.Items[3].Selected)
    {
    strResult1 = (strResult1 + "," + "'Mk'");
    }

    if (strResult1 == "," + strResult1)
    { strResult1= strResult1.Substring(1,strResult1.Length-1);}

    string connString = "data source=10.......; user id = ...; password =...;" +
    "initial catalog=....";

    string sql=("select " + strResult + " from t join d d on d.id=t.id where t.year = '" + this.ddlAn.SelectedItem.Text+ "' and d.name in (" +strResult1+ ") group by t.this");

  6. #6
    Join Date
    Oct 2001
    Posts
    80

    Re: Replace or substring

    It's exactly what I was saying in my last post. You add a comma to an empty string. Imagine checkbox1 is not checked but checkbox2 is.

    Start : strResult = ""
    checkbox1 is not checked so strResult is still strResult = ""
    checkbox2 is checked, so you add a comma and "t.last" to and empty string, resulting in strResult = ", t.last"

    I'll post a fix in a few minutes

  7. #7
    Join Date
    Oct 2001
    Posts
    80

    Re: Replace or substring

    Code:
          if ( CheckBox1.Checked )
          {
            strResult += "t.name" ;
          }
    
          if ( CheckBox2.Checked )
          {
            if ( CheckBox1.Checked ) // add comma only if checkbox1 was checked
               strResult += ", " ;
    
            strResult += "t.last" ;
          }
    
          if (CheckBox3.Checked)
          {
            if ( CheckBox1.Checked || CheckBox2.Checked )// add comma only if checkbox1 or checkbox2 was checked
               strResult += ", " ;
           
            strResult += "count(t.this)";
          }
    
          if ( CheckBoxList1.Items[0].Selected )
          {
            strResult1 += "'Mt'" ;
          }
          if (CheckBoxList1.Items[1].Selected)
          {
            if ( CheckBoxList1.Items[0].Selected )  //add comma only if CheckBoxList1.Items[0].Selected
               strResult1 += "," ;
              
            strResult1 += "'MP'" ;
          }
    
          if (CheckBoxList1.Items[2].Selected)
          {
            if ( CheckBoxList1.Items[ 0 ].Selected || CheckBoxList1.Items[ 1 ].Selected )  // and so on
               strResult1 += "," ;
              
            strResult1 += "'MG'" ;
          }
    
          if (CheckBoxList1.Items[3].Selected)
          {
            if ( CheckBoxList1.Items[ 0 ].Selected || CheckBoxList1.Items[ 1 ].Selected || CheckBoxList1.Items[ 2 ].Selected )
                strResult1 += "," ;
            
            strResult1 += "'Mk'" ;
          }
    
          // this is never true and will never run. Suppose strResult1 = "example"
          //then your check is: if ( "example" == ",example")
          if ( strResult1 == "," + strResult1 )  
          { 
            strResult1 = strResult1.Substring( 1, strResult1.Length - 1 );
          }
    
          string connString = "data source=10.......; user id = ...; password =...;" +
            "initial catalog=....";
    
          string sql=("select " + strResult + " from t join d d on d.id=t.id where t.year = '" + this.ddlAn.SelectedItem.Text+ "' and d.name in (" +strResult1+ ") group by t.this");
    I haven't tested this, but this is the way you should go. Hope this helped.

    Read the comments!
    Last edited by bewa; August 30th, 2006 at 07:55 AM.

  8. #8
    Join Date
    Aug 2006
    Posts
    4

    Re: Replace or substring

    Thanks you very much. It's working now.

    Thanks again!

    ariana

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured