-
August 30th, 2006, 06:02 AM
#1
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
-
August 30th, 2006, 06:08 AM
#2
Re: Replace or substring
string sql = "select ,b.number from table a join table b where a.name in (,six,one)".Replace(",", "");
-
August 30th, 2006, 06:35 AM
#3
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 !
-
August 30th, 2006, 06:54 AM
#4
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.
-
August 30th, 2006, 07:06 AM
#5
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");
-
August 30th, 2006, 07:30 AM
#6
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
-
August 30th, 2006, 07:46 AM
#7
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.
-
August 30th, 2006, 07:57 AM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|