query problem

S

smriti

Well-known member
Joined
Jan 7, 2005
Messages
54
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.
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
Code: 
	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
 
Last edited by a moderator:
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.
 
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[VB]If TypeOf ctl Is TextBox Then[/VB]It would also be neater to use[VB]If ctl.Text.Trim() <> String.Empty Then[/VB]than CompareTo if you dont intend to use the other possible results of CompareTo. CompareTo is really to establish relative ordering rather than possible equality.

Its possibly pedantic but I suggest losing the VB6-style Mid() calls and use String.Substring() instead. I dont 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.
 
Trying to optimise it slightly further, Id probably go with something as follows (unless I parameterised it, of course):

[VB]
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
[/VB]

Forumised, untested code.

B.
 
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!!!!
 

Attachments

Back
Top