query problem

[smriti]

Hi all,

 

I have one problem with queries.

I am doing a program in vb.net and backend is ms access.

My problem is...

I have many controls on my form(windows application)

 

In textboxes If I enter data(not mandatory) and click the search button.

So depending upon the search criteria data will be displayed.

 

I wrote the foolwing code.

      	Dim ctl As Control
       Dim sSQL As String
       Dim sWhereClause As String

       sWhereClause = " Where "
sSQL = "select * from Table1 "
	For Each ctl In Me.Controls
           If ctl.GetType.FullName = "System.Windows.Forms.TextBox" And & _	    		ctl.Text.Trim.CompareTo(String.Empty) <> 0 Then
               If ctl.Text.GetType.FullName = "System.String" Then
                   If sWhereClause = " Where " Then
                       sWhereClause = sWhereClause & Mid(ctl.Name, 4, ctl.Name.Length) & 				"='" & ctl.Text & "'"
                   Else
                       sWhereClause = sWhereClause & " and " & Mid(ctl.Name, 4, 				ctl.Name.Length) & "='" & ctl.Text & "'" '& ctl.Text
                   End If
               Else
                   If sWhereClause = " Where " Then
                       sWhereClause = sWhereClause & Mid(ctl.Name, 4, ctl.Name.Length) & 			"=" & ctl.Text
                   Else
                       sWhereClause = sWhereClause & " and " & Mid(ctl.Name, 4, 				ctl.Name.Length) & "=" & ctl.Text '& ctl.Text
                   End If
               End If
           End If
       Next ctl
       Dim strSQL As String
       strSQL = sSQL & sWhereClause

When I run this code, it is displaying the exception as "Data type mismatch exception".

Because some fields in the database are numeric fields, So for numeric fields it displays

the exception.

 

So I tried in another way as

For Each ctl In Me.Controls
           If ctl.GetType.FullName = "System.Windows.Forms.TextBox" And & _	    				ctl.Text.Trim.CompareTo(String.Empty) <> 0 Then
                  If sWhereClause = " Where " Then
                       sWhereClause = sWhereClause & Mid(ctl.Name, 4, ctl.Name.Length) & 			"=" & ctl.Text
                   Else
                       sWhereClause = sWhereClause & " and " & Mid(ctl.Name, 4, 				ctl.Name.Length) & "=" & ctl.Text '& ctl.Text
                   End If
               End If
        
       Next ctl

When I execute this, it is also displalying the exception "datatype mismatch exception"

for text type fields.

 

How can I solve this problem,

 

 

Thanks

Edited June 21, 2005 by PlausiblyDamp

[PlausiblyDamp]

Is there any chance you could post an example of the contents of sWhereClause in each example - without knowing the underlying data types etc. it is quite tricky to diagnose the problem.

Also rather than relying on string concatenation you may want to consider a parameterised query (search these forums for several threads on the topic) - this will result in cleaner code and also remove the posibility of security exploits like injections.

[jmcilhinney]

Several things to note.

 

The type of the Text property of a TextBox is ALWAYS going to be String.

 

In your SQL you need to single quotes around the values of text fields and not number fields. You need to specify which ones are numbers and which ones are text yourself and treat them accordingly. If you are determined to use the name of the TextBox, perhaps add something to the name to identify what type that field is. You could also use a NumericUpDown, although this is not suitable in all cases. It certainly would be for integers though.

 

If you want to find the TextBoxes, it would be neater to use

If TypeOf ctl Is TextBox Then

It would also be neater to use

If ctl.Text.Trim() <> String.Empty Then

than CompareTo if you don't intend to use the other possible results of CompareTo. CompareTo is really to establish relative ordering rather than possible equality.

 

It's possibly pedantic but I suggest losing the VB6-style Mid() calls and use String.Substring() instead. I don't frown on any use of the Microsoft.VisualBasic namespace as some do but if the .NET Framework provides an alternative then I consider that preferable.

[penfold69]

Trying to optimise it slightly further, I'd probably go with something as follows (unless I parameterised it, of course):

 

dim sWhereClause as String = ""
dim bHasCriteria as Boolean = False
For Each ctl In Me.Controls
   If TypeOf ctl is TextBox AndAlso ctl.Text.Trim().Length > 0 Then
       If Not bHasCriteria Then
           sWhereClause = " WHERE "
       Else
           sWhereClause &= " AND "
       End If
       If ctl.Tag = "N" Then 'Assign "N" to the Tag field of each numeric control when you create it.
           sWhereClause &= Ctl.Name.SubString(4, Ctl.Name.Length - 4) & "=" & ctl.Text
       Else
           sWhereClause &= Ctl.Name.SubString(4, Ctl.Name.Length - 4) & "='" & ctl.Text & "'"        
       End If
       bHasCriteria = True
   End If
Next ctl 

 

Forumised, untested code.

 

B.

[Joe Mamma]

a class to solve your problem

 

greetings. . . been getting my ***** kicked at work.

 

attached is a project that demos a class (SQLQuerySpecification) I created for executing queries with varying parameters for the where clause (the class is C# cause vb sucks) but I attached a vb sample that show the use. . .

(note, change the connection string to use your local version of northwind. also this uses SQL client not odbc. it can be easily changed to any other client library)

 

trace into the code and check it out. . . very nifty

 

you basically set the select clause and the from clause.

 

then depending on values in controls you optionally add parameter/values with select criteria (equal, notequal, lessthan, etc. . . )

 

you can also add a grouping clause and and order clause

 

gets rid of that ugly spaghetti 'if else' for building the where clause and since it uses parameters it is secure. . .

 

DONT BUILD SQL!!!!

QuerySpec.zip