StringBuilder?

  • Thread starter Thread starter J Jones
  • Start date Start date
J

J Jones

I need to plug values into a condition like:

((E <= 100 And E > 79) And (F <= 59 And F > 39)) Or ((E <= 79 And E > 59)
And (F <= 39 And F > 19)) Or ((E <= 59 And E > 39) And (F <= 19 And F >
-1))

Where "E" and "F" have numeric values, and test if the condition is true.
There are 400 of these conditions and 10 variables "A" - "J".

What I thought was that I could build up an expression using the
stringbuilder in VB.Net. I have tried but I dont see how I can do it. Any
ideas. Here is some code i've been playing with.

Dim A, B, C, D, E, F, G, H, I, J As String
A = CStr(dt.Rows.Item(0).Item(0))
B = CStr(dt.Rows.Item(0).Item(1))
C = CStr(dt.Rows.Item(0).Item(2))
D = CStr(dt.Rows.Item(0).Item(3))
E = CStr(dt.Rows.Item(0).Item(4))
F = CStr(dt.Rows.Item(0).Item(5))
G = CStr(dt.Rows.Item(0).Item(6))
H = CStr(dt.Rows.Item(0).Item(7))
I = CStr(dt.Rows.Item(0).Item(8))
J = CStr(dt.Rows.Item(0).Item(9))

Dim xx As Boolean
Dim dc As DataColumn = ds.Tables(0).Columns.Add()
ds.Tables(0).Columns.Add("calcTrait")
For x = 0 To ds.Tables(0).Rows.Count - 1
Dim sb As New System.Text.StringBuilder
Dim condition As String =
CStr(ds.Tables(0).Rows(x).Item(0))
sb.Append(condition)
sb.Replace("And", "xxx")
sb.Replace("A", A)
sb.Replace("B", B)
sb.Replace("C", C)
sb.Replace("D", D)
sb.Replace("E", E)
sb.Replace("F", F)
sb.Replace("G", G)
sb.Replace("H", H)
sb.Replace("I", I)
sb.Replace("J", J)
sb.Replace("xxx", "And")
xx = CBool(sb.ToString)
If CBool(xx) Then
ds.Tables.Item(0).Rows(x).Item("calctrait") =
sb.ToString
End If
Next x
x = Nothing
 
Ummm... can you give a slightly broader picture of what the actual goal is?
It seems possible that there may be a better alternative but it's hard to
tell from the listing. Are you trying to isolate rows from a database that
match hundreds of criteria?

Also as a sidenote... consider expressing the conditions slightly
differently e.g. (E > 79 And E <= 100) It tends to make it slightly more
obvious that E is supposed to be between that range.

Tom
 
What you really need by the looks of it is the Eval statement which is no
longer supported by .NET ( To my knowledge ). I did see a very interesting
workaround to this using a JavaScript compiled into a DLL and declared in
VB.NET to use the eval.

If you do a search on google, you might find a way of doing this, its come
up a few times without a great solution. However, as tom leylan pointed out,
you may want to outline what it is you are actually trying to achieve we can
guess but it's a lot easier to be told explicitly.

Best Regards - OHM

O_H_M{at}BTInternet{dot}com




J said:
I need to plug values into a condition like:

((E <= 100 And E > 79) And (F <= 59 And F > 39)) Or ((E <= 79 And E >
59) And (F <= 39 And F > 19)) Or ((E <= 59 And E > 39) And (F <= 19
And F > -1))

Where "E" and "F" have numeric values, and test if the condition is
true. There are 400 of these conditions and 10 variables "A" - "J".

What I thought was that I could build up an expression using the
stringbuilder in VB.Net. I have tried but I dont see how I can do it.
Any ideas. Here is some code i've been playing with.

Dim A, B, C, D, E, F, G, H, I, J As String
A = CStr(dt.Rows.Item(0).Item(0))
B = CStr(dt.Rows.Item(0).Item(1))
C = CStr(dt.Rows.Item(0).Item(2))
D = CStr(dt.Rows.Item(0).Item(3))
E = CStr(dt.Rows.Item(0).Item(4))
F = CStr(dt.Rows.Item(0).Item(5))
G = CStr(dt.Rows.Item(0).Item(6))
H = CStr(dt.Rows.Item(0).Item(7))
I = CStr(dt.Rows.Item(0).Item(8))
J = CStr(dt.Rows.Item(0).Item(9))

Dim xx As Boolean
Dim dc As DataColumn = ds.Tables(0).Columns.Add()
ds.Tables(0).Columns.Add("calcTrait")
For x = 0 To ds.Tables(0).Rows.Count - 1
Dim sb As New System.Text.StringBuilder
Dim condition As String =
CStr(ds.Tables(0).Rows(x).Item(0))
sb.Append(condition)
sb.Replace("And", "xxx")
sb.Replace("A", A)
sb.Replace("B", B)
sb.Replace("C", C)
sb.Replace("D", D)
sb.Replace("E", E)
sb.Replace("F", F)
sb.Replace("G", G)
sb.Replace("H", H)
sb.Replace("I", I)
sb.Replace("J", J)
sb.Replace("xxx", "And")
xx = CBool(sb.ToString)
If CBool(xx) Then
ds.Tables.Item(0).Rows(x).Item("calctrait") =
sb.ToString
End If
Next x
x = Nothing
Click to expand...

--
 
* J Jones said:
I need to plug values into a condition like:

((E <= 100 And E > 79) And (F <= 59 And F > 39)) Or ((E <= 79 And E > 59)
And (F <= 39 And F > 19)) Or ((E <= 59 And E > 39) And (F <= 19 And F >
-1))

Where "E" and "F" have numeric values, and test if the condition is true.
There are 400 of these conditions and 10 variables "A" - "J".
Click to expand...

What's the exact problem?
 
So I have XML returned from SQL Server via a Stored Procedure. I then
transformed the XML string into a datatable. There Are 2 Columns in the
datatable: "condition" and "note". the "condition" column holds strings
like:

A < -10 And B > 0 And C > 0 And D > 0 And E > 10 And F > 10 And G > 0 And
H > 0 And I > 10 And J < -10

(I<= 59 And I > 14) And I > J And J < 20

((F <= 100 And F > 79) Or (F <= 79 And F > 59)) And G < -19 And H < -19

The "note" column holds strings like:

Although you are normally very busy and active there are areas that you
probably know you should handle or do things about that you don't handle
effectively.

His high standards and demands are not always combined with correct
estimation of people and situations. This can lead him to be critical; he
gets stuck in handling details to "perfection" while missing the overall
target.

She tends to be over-analytical and sometimes put too much effort into
trying to figure out things that are not necessarily the most important.

I have another datatable holding calculated values for integers A - J
A=10
B=20
C=30
etc..

I want to use the "conditions" column select the "note" where the
"condition" is evaluated as being true.
Thanks
 
Good points Tom. To follow up
tell from the listing. Are you trying to isolate rows from a database that
match hundreds of criteria?
Click to expand...

Not having a fully informed knowledge of the methodologies for database
access in .Net, I may showing my hand earlier than I would normally. But
risking being flamed, I am going to comment the SQL query (if of-course
that is what the intention of the string

((E <= 100 And E > 79) And (F <= 59 And F > 39)) Or ((E <= 79 And E > 59)
And (F <= 39 And F > 19)) Or ((E <= 59 And E > 39) And (F <= 19 And F > ....

is for - need some more information to help you J Jones). Having
considerable experience with Oracle databases, and programming in Java, C++,
and PL/SQL, I would not recommend repetitive execution or construction of
SQL statements in this manner. You will find the use of bind variables, for
repetitive queries with different values, far less expensive on the server.

Ie, something like this:

Dim stmt As System.Data.OleDb.OleDbCommand =
SupportClass.TransactionManager.manager.PrepareStatement(conn,"SELECT
whatever FROM whereever WHERE (?<=100 and ?>79) AND (?<59 AND ?>39 AND
.....")
stmt.setString(1, aValue)
stmt.setString(2, anotherValue)
stmt.setString(3, anotherValue)

rather than putting aValue and anotherValue in the SQL statement per-se
(your original post J Jones).

The first execution of the PrepareStatement, your server will take the
expensive hit parsing it. The second and subsequent executions of the same
PrepareStatement, Oracle will check to see whether is already exists, and if
so use it saving considerable overhead. For a query of this
length/complexity, you risk thrashing the server. It may run okay for one or
two isolated database queries but it will certainly lack scalability. Go for
an alternative approach such as PrepareStatement.

The same databse server parsing argument can be applied to other databases.
I'm not sure on SQL Server but it's certainly the case for DB2.

Regards
Hexathioorthooxalate
 
You might try a random number generator :-) Is this for telling fortunes?

Seriously though... step back one more level of description. You are making
it sound like the "A < -10 And B > 0" is some sort of natural occuring piece
of information that you simply have to work with. And it sounds like that
"string storage system" is the cause of the trouble you are having.

In English... do you have "paragraphs of text" and "criteria" which describe
various aspects of the paragraph? I assume the criteria correspond to your
letters and that the value indicates how much it corresponds? Any limit to
the range of values?

Instead of describing three "sort of like this" examples can you post a
single complete one?

"His high standards and demands are not always combined with correct
estimation of people and situations. This can lead him to be critical; he
gets stuck in handling details to "perfection" while missing the overall
target."

What would I enter in a search that would end up matching this paragraph?

I don't mean to dig but I think people are having a problem answering your
question simply because there is little understanding of what you are trying
to accomplish. You might have set up the situation which makes it so hard
to create the SQL query.

Tom
 
Thanks Tom I'll try to make it clearer....

Seriously though... step back one more level of description. You are making
it sound like the "A < -10 And B > 0" is some sort of natural occuring piece
of information that you simply have to work with.
Click to expand...

In English... do you have "paragraphs of text" and "criteria" which describe
various aspects of the paragraph? I assume the criteria correspond to your
letters and that the value indicates how much it corresponds? Any limit to
the range of values?
Click to expand...
I have paragraphs of text that i want to display only if the criteria is
true i.e. if A < -10 And B > 0 = TRUE

(A could be any value from -100 to +120
B could be any value from -102 to +111)
Instead of describing three "sort of like this" examples can you post a
single complete one?

"His high standards and demands are not always combined with correct
estimation of people and situations. This can lead him to be critical; he
gets stuck in handling details to "perfection" while missing the overall
target."

What would I enter in a search that would end up matching this paragraph?
Click to expand...

My problem is that what I need to search is, for intstace A < -10 And B > 0
Where A gets replaced with the value of A from another table and B gets
replaced with the value of B from another table e.g. -99<-10 And -44 >0,
Obviously in this example the expression is not true so I wouldnt want to
return the text of this row.

I don't mean to dig but I think people are having a problem answering your
question simply because there is little understanding of what you are trying
to accomplish. You might have set up the situation which makes it so hard
to create the SQL query.
Click to expand...
Is an SQL query what I need?

Thanks
Jim
 
J Jones,
I would use the DataTable.Select method to evaluate your condition column.

Something like:
Dim tableNotes As DataTable ' condition, note
Dim tableValues As DataTable ' A, B, C, D, E...

' tableResult contains the note rows that have a true condition
Dim tableResult As DataTable = tableNotes.Clone()

Dim exists() as DataRow

For Each row As DataRow in tableNotes.Rows
exists = tableValues.Select(DirectCast(row!condition, String))
If exists.Length > 0 Then
tableResult.ImportRow(row)
End If
Next

The tableResult table will have a row for each true condition. Instead of
"tableResult.ImportRow(row)" you could process the row at that time.

Note: David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS
Press, is a very good tutorial on ADO.NET, plus its a good desk reference
once you know ADO.NET.

Hope this helps
Jay
 
J Jones said:
"A < -10 And B > 0" would be the search creiteria. ie. if A < -10 And B > 0
= TRUE then - - pull the text from that row.
Click to expand...

That's good. And we can infer that the criteria can get fairly involved and
also that you created this criteria string right? And just to clarify if A
was only -9 then this paragraph doesn't qualify. And that would be because
it's A-ness (so to speak) is extremely low whereas it has positive B-ness?

Doesn't the description alone make you cringe? What could the quality of
"A" represent and couldn't this be represented in some other way? Not that
you have to, I'm not suggesting it is "wrong" simply fraught with the
potential for errors since it can barely be read and probably can't easily
be written.

Again I have no clue but "usually" (if I can use that term) the attributes
are assigned rather than a "matching criteria" and in that way some routine
can simply ask for the matching criteria.

In other words if you can assign this paragraphs "A-ness and B-ness" as
simple values A = -10 and B = 1 (for instance) then the query would decide
whether it wanted to see all the A's that were below zero or the B's that
were between 1 and 5. By adding simple attributes it would be possible to
"refine" the search. You could (for instance) ask to see a list of all that
met criteria "A <= 0" and your sample would appear along with 300 others...
then you could say heck, just show me the ones that also match "B > 3" and
if this had a B value of 1 it would be dropped from the list.
My problem is that what I need to search is, for intstace A < -10 And B > 0
Where A gets replaced with the value of A from another table and B gets
replaced with the value of B from another table e.g. -99<-10 And -44 >0,
Obviously in this example the expression is not true so I wouldnt want to
return the text of this row.
Click to expand...

Not knowing what A and B represent it's hard to follow but I've never seen
anything like it which suggests it's an odd solution. I can't come with a
great example but it looks like you would be doing the equivalent of setting
up a search for a book title (let's say) by defining the criteria (in the
book) as:

title.contains( "T") or title.contains( "Th") or title.contains("The") ...
etc.

rather than simply identifying the book title (the actual attribute) and
letting a query somewhere decide if it wanted just "The Red Pony" or every
book title that starts with "T" or any book that contains the word "the".
Is an SQL query what I need?
Click to expand...

If it can't be done through a SQL query there is a good chance that
something is up :-) There are problems where SQL isn't optimal but the
solution should probably be able to be expressed with SQL syntax. Again it
looks like an odd way to describe the matching criteria of a paragraph this
way. Who knows the problem could be such that there isn't any way but try
as I will I can't think of one.

How does a paragraph get selected on the basis of A being -10 or less? What
is that somebody looking for A = -9 would notice if this paragraph popped
up? Would they say "hey that's only -9 A-ness?" I didn't want a -13 A...
that's simply not enough A for me.

I'm afraid I still don't get it.
Tom
 
Ok Tom,
if I write:

Dim A,B,C As Integer
A = 10
B = 20
C = 30
If
A<B And B<100 And C<=30 = True
Then
Initalizedisplay()
Else
Giveup()

The above will Initalizedisplay(). Because the expression is true.
That is what is wanted.

My situation is:(example)
I have 3 varables declared as Integers
A = 10, B = 20, C = 30
And I have and expression in a datatable in a dataset
A<B And B<100 And C<=30

I import "A<B And B<100 And C<=30" as a string into a StringBuilder
and use StringBuilder.Replace to insert the values of A,B and C as string
values.
So what I have is 10<20 And 20<100 And 30<=30. but it is a string so I cant
evaluate it as true or false. That is the problem I have.
 
J Jones,
So what I have is 10<20 And 20<100 And 30<=30. but it is a string so I cant
evaluate it as true or false. That is the problem I have.
Click to expand...
Did you try the sample code I posted?

It will evaluates your condition string against your values table. Without
using a string builder or running an SQL statement against the server!

Of course we all may be missing what you are trying to accomplish.

Hope this helps
Jay
 
J Jones said:
So what I have is 10<20 And 20<100 And 30<=30. but it is a string so I cant
evaluate it as true or false. That is the problem I have.
Click to expand...

Okay I've got it... Jay presented one solution (I didn't try it) but I did
search around and you won't believe what I found :-) And it works!

The instructions are presented here. If you follow them you will get the
results demonstrated. Note that your copy of jsc (the jscript compiler) may
not be in the folder referenced in the article.
http://dbforums.com/arch/219/2002/12/578053

To get it to behave more like you want you have to modify the example to
take the parameters A, B and C (as integers) and pass them along. Then you
can reference the variables by name in your expression.

So for instance I made the following modification:

class JScriptEval {
function Evaluate(evalString : String, a : int, b : int, c: int)
{ return eval(evalString); }
}

Which lets me pass along three integer values. Remember you have to
reference the variable names in the string as they are named in the Evaluate
function.

Dim a As Integer = 3
Dim j As New JScriptEval
Dim s1 As String = "a + 2 * 3"
MsgBox(j.Evaluate(s1, a, 0, 0))

So you would write:

Dim test As Integer = 3
Dim me As Integer = 5
Dim j As New JScriptEval
Dim s1 As String = "a + 2 * c"
MsgBox(j.Evaluate(s1, test, 0, me))

so long as the string uses the letter names. Probably a good idea to use
the same names to keep things straight however.

Interesting huh?
Tom
 
Sorry about that... I must have scanned it... perhaps subconsciously that
was my motivation to do the search. So "as One Handed Man mentioned"...

It might be best if I leave the answers in 2004 to the experts!
 
Thanks for all your effort. I followede the instructions and I got as far
as adding the references before my app. fell apart:-( now I have removed
the two references but my app wont work because it says it doesnt have
references to the things that I'm sure it did have references to - like
system, system.xmland the function overrides are throwing up errors.. ANY
IDEAS?
 
Thanks Jay, I'm trying you code but am not having much luck.
I've had to play around with it a bit to fit it into my code and I get:
Syntax error: Missing operand after '0' operator.
I've posted the entire Sub, Maybe you can see what I've done wrong?

Private Sub Res()

Dim oBusinessLogic As New SurveyAdminLogic.BusinessLogic
Dim sXML1 As String
Dim sXML As String
Dim iReturn As Integer
Dim intResultIndex As Integer = CInt(txtSession.Text)

Try
sXML1 = oBusinessLogic.GetTraits(iReturn)
sXML = oBusinessLogic.GetScores(intResultIndex, iReturn)
Catch ex As Exception

End Try

oBusinessLogic = Nothing
Select Case iReturn

Case RETURN_SUCCESS
Dim dsValues As New DataSet
Dim dsNotes As New DataSet
Dim rValues As New System.IO.StringReader(sXML)
Dim rNotes As New System.IO.StringReader(sXML1)

Dim dtValues As New DataTable
Dim drValues As DataRow = dtValues.NewRow
Dim x As Integer = 0

dsValues.ReadXml(rValues)
dsNotes.ReadXml(rNotes)

dsValues.Tables.Add(dtValues)

While x < dsValues.Tables.Item(0).Rows.Count - 1
For Each row As DataRow In
dsValues.Tables.Item("Result").Rows

dtValues.Columns.Add(CStr(dsValues.Tables.Item(0).Rows.Item(x).Item(0)),
System.Type.GetType("System.Int32"))
x = x + 1
Next
End While
x = Nothing

dtValues.Rows.Add(drValues)

For x = 0 To dsValues.Tables.Item(0).Rows.Count - 1
dtValues.Rows.Item(0).Item(x) =
CInt(dsValues.Tables.Item(0).Rows.Item(x).Item(1))
Next

' tableResult contains the note rows that have a true
condition
Dim tableResult As DataTable = dsNotes.Tables(0).Clone()

Dim exists() As DataRow

For Each row As DataRow In dsNotes.Tables(0).Rows
exists = dtValues.Select(DirectCast(row!condition,
String))
If exists.Length > 0 Then
tableResult.ImportRow(row)
End If
Next
DataGrid1.DataSource = tableResult
DataGrid1.DataBind()
DataGrid1.Visible = True

End Select
End Sub
 
J Jones,
Thanks Jay, I'm trying you code but am not having much luck.
I've had to play around with it a bit to fit it into my code and I get:
Syntax error: Missing operand after '0' operator.
I've posted the entire Sub, Maybe you can see what I've done wrong?
Click to expand...

Which line do you get that error? Is it at run time or compile time?

If you are getting it on the following line at run time:
exists = dtValues.Select(DirectCast(row!condition, String))
Click to expand...

Then it sounds like you have a badly formed condition expression in your
table. The three you posted worked fine.

Remember that "row!condition" is short for "row.Item("condition")" where
"condition" is the name of the field in the DataTable.

For the proper syntax on the expression see:

http://msdn.microsoft.com/library/d...fsystemdatadatacolumnclassexpressiontopic.asp

You may want to put the dtValues.Select itself in its own Try Catch, and
display the value of the "condition" field in the catch block, just to make
sure the syntax is correct... Alternatively you could add a column to
dsNotes.Tables(0) that you update in the catch block around dtValues.Select
to indicate a syntax error for that row...

Something like:

Try
row!ValidSyntax = True
exists = dtValues.Select(DirectCast(row!condition, String))
If exists.Length > 0 Then
tableResult.ImportRow(row)
End If
Click to expand...
Catch ex As Exception
row!ValidSyntax = False
End Try

Then when you are done the dsNotes.Tables(0) will have a column indicating
if the condition was valid or not...

Hope this helps
Jay



J Jones said:
Thanks Jay, I'm trying you code but am not having much luck.
I've had to play around with it a bit to fit it into my code and I get:
Syntax error: Missing operand after '0' operator.
I've posted the entire Sub, Maybe you can see what I've done wrong?

Private Sub Res()

Dim oBusinessLogic As New SurveyAdminLogic.BusinessLogic
Dim sXML1 As String
Dim sXML As String
Dim iReturn As Integer
Dim intResultIndex As Integer = CInt(txtSession.Text)

Try
sXML1 = oBusinessLogic.GetTraits(iReturn)
sXML = oBusinessLogic.GetScores(intResultIndex, iReturn)
Catch ex As Exception

End Try

oBusinessLogic = Nothing
Select Case iReturn

Case RETURN_SUCCESS
Dim dsValues As New DataSet
Dim dsNotes As New DataSet
Dim rValues As New System.IO.StringReader(sXML)
Dim rNotes As New System.IO.StringReader(sXML1)

Dim dtValues As New DataTable
Dim drValues As DataRow = dtValues.NewRow
Dim x As Integer = 0

dsValues.ReadXml(rValues)
dsNotes.ReadXml(rNotes)

dsValues.Tables.Add(dtValues)

While x < dsValues.Tables.Item(0).Rows.Count - 1
For Each row As DataRow In
dsValues.Tables.Item("Result").Rows

dtValues.Columns.Add(CStr(dsValues.Tables.Item(0).Rows.Item(x).Item(0)),
System.Type.GetType("System.Int32"))
x = x + 1
Next
End While
x = Nothing

dtValues.Rows.Add(drValues)

For x = 0 To dsValues.Tables.Item(0).Rows.Count - 1
dtValues.Rows.Item(0).Item(x) =
CInt(dsValues.Tables.Item(0).Rows.Item(x).Item(1))
Next

' tableResult contains the note rows that have a true
condition
Dim tableResult As DataTable = dsNotes.Tables(0).Clone()

Dim exists() As DataRow

For Each row As DataRow In dsNotes.Tables(0).Rows
exists = dtValues.Select(DirectCast(row!condition,
String))
If exists.Length > 0 Then
tableResult.ImportRow(row)
End If
Next
DataGrid1.DataSource = tableResult
DataGrid1.DataBind()
DataGrid1.Visible = True

End Select
End Sub
Click to expand...
column.
 
Back
Top