INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to make a userfriendly sql query structure

How to make a userfriendly sql query structure

(OP)
Hello people,

I have got a very long Query.
my goal is to make a very user-friendly interface where the user can select from lots different query criteria.

I did try to create an arraylist where each item presents a SQL column/SELECT/WHERE from the db example:

CODE --> VB.net

Dim QueryList As New ArrayList()
            QueryList.Add("SQLStr =")
            QueryList.Add("SELECT")
            QueryList.Add("tt.ordernr 'Order number'")
            QueryList.Add("tt.offertnr 'Quotation number'")
            QueryList.Add("WHERE")
            QueryList.Add("(DATEPART(YEAR,tt.date)=@datum") 
I found the problem I need to add a comma "," between all the elements to except the first and the last.

which is the best way to create a such of program?
I believe it must be a better way to make this?
I thought about use a datagridview/datatable
also dataset and sql parameters.

Thank you in advance

RE: How to make a userfriendly sql query structure

The best way to do this is to make your query a stored procedure in the database, and pass the user selections in as parameters. Running dynamic queries against a database can cause security problems.

If you can't create a stored procedure, I find it easier to create a template for the query and fill in the values using string.format. The nice thing about doing it this way is that you can write the query on a database and test it, then just change your test values to placeholders, ({0}, {1}, etc), for your application.

I'd use brackets instead of single quotes for the field names. Single quotes are used to identify literal strings and can throw errors when used like this.

CODE

Dim strTmplt as string = "SELECT tt.ordernr [Order number], tt.offertnr [Quotation number], WHERE (DATEPART(YEAR,tt.date)={0}"
Dim strQuery as string = [String].Format(strTmplt, @datum) 

RE: How to make a userfriendly sql query structure

That's easily done using your code.


For each column AFTER THE FIRST column in your SELECT clause, prefix the column name with a comma as:


QueryList.Add("SELECT")
QueryList.Add("tt.ordernr 'Order number'")
QueryList.Add(",tt.offertnr 'Quotation number'")


By adding it before the second and subsequent columns you will not end up with a trailing comma.

RE: How to make a userfriendly sql query structure

Also don't forget when you are building your query to ensure that spaces are entered appropriately eg

" WHERE "

This save you having to handle that when you put all the parts together.

RE: How to make a userfriendly sql query structure

(OP)
could you help me with a code snippet for that?

as you know its never a comma in beginning before the first element either the last element.

Thank you in advance.

RE: How to make a userfriendly sql query structure

Unfortunately I'm doing a VS update at the moment and that is likely to take an hour or more it's a 12GB install

So the following has not been tested. Keeping to your method of storing these elements - which will involve the least changes to your code:


With QueryList
.Add("SELECT "
.Add("ordernr 'Order number' ")
.Add(",offertnr 'Quotation number' ")
.Add(",Column3 'Column 3' ")
.Add(",tt.Column4 'Colum 4' ")
.Add(" FROM ")
.Add(" tt ")
.Add(" WHERE ")
.Add(DATEPART(YEAR,tt.date)=@datum")
.Add(" AND ")
.Add(" ordernr = 12345 ")
End With


Using the With construct saves you from having to type QueryList on each line.

Then when you are ready to put it all together use a StringBuilder, which I think is in System.Text
So either add System.Text to your Imports section (which will save you having to reference System.Text as I've done below

Dim sb as System.Text.StringBuilder = New System.Text.Stringbuilder

'If you have added System.Text to your Imports then the above line will be
'Dim sb As StringBuilder = New StringBuilder

With QueryList
For a As Integer = 0 To .Count - 1 'it might be .Length - 1 (I can't remember, but Intellisense will show you)
sb.Add(.Items(a))
Next
End With


Then to use your query

Dim MyQuery As String = sb.ToString


Although you don't need a new string variable, in your CommandText???? parameter for the connection you could simply use sb.ToString directly.

Please remember the above snippets where written around your code structure and that I can't run Visual Studio for the moment, so you may need to make some changes, though hopefully not too many.



RE: How to make a userfriendly sql query structure

Just out of curiosity, why use the ArrayList at all if you're going to feed all the elements into a StringBuilder? It seems like extra work and processing to add all the array elements, then loop through them to add them to a StringBuilder. Why not just bypass the ArrayList with something like this?

CODE

Dim sb As New System.Text.StringBuilder
        With sb
            .AppendLine("SELECT")
            .AppendLine("ordernr [Order number]")
            .AppendLine(",offertnr [Quotation number]")
            .AppendLine(",Column3 [Column 3]")
            .AppendLine(",tt.Column4 [Colum 4]")
            .AppendLine("FROM")
            .AppendLine("tt")
            .AppendLine("WHERE")
            .AppendLine("DATEPART(YEAR,tt.date)= @datum")
            .AppendLine("AND")
            .AppendLine("ordernr=12345")
        End With

        Dim MyQuery As String = sb.ToString.Replace(VbCrLf, " ") 

Whenever I build SQL queries, I use AppendLine and skip the spaces, then use sb.ToString.Replace(VbCrLf, " ") to build the final query. It just helps avoid SQL syntax errors at runtime.

You may also have syntax issues with this when the query runs: " DATEPART(YEAR,tt.date) = @datum ", but it's unclear what @datum is. Passing a variable into a SQL query can't be done inside double quotes. If it's just a placeholder you added for demonstration purposes then it's not problem, but if you're going to use a variable from your VB code in the query, it won't work. You'll have to change that line to .Append(" DATEPART(YEAR,tt.date) = ").Append(myYearVariable.ToString()).Append(" ").

RE: How to make a userfriendly sql query structure

I tried to keep as close as possible to elmnas's current code layout - and without VS available to test any other format I felt that that was the safest approach to provide a working/workable solution. I agree I was unsure of @datum, but decided to stick with it at least for now.

Additionally an SP would still require the parameters to be collected in some way.

I generally use a template select/update/insert string into which I place the required elements. I have a function that I wrote many years ago to handle this. The template consists of SELECT/UPDATE/INSERT placeholder for column list FROM placeholder for table placeholder for optional JOIN ON criteria WHERE placeholder for where criteria placeholder for optional HAVING and ORDER BY clauses when several columns, joins and/or criteria are involved. But I include spaces at each point and always PREFIX list items with a comma for the second and subsequent items as opposed to POSFIXing which could leave a trailing comma to clean up. For smaller simpler situations I use a method not dissimilar to the one you have posted.

RE: How to make a userfriendly sql query structure

(OP)
Thank you so much for this!! I will test it very soon I hope this is a good solution!

RE: How to make a userfriendly sql query structure

(OP)
Hi @pmegan,

Thank you for very good answer.
about @datum it's something I need to keep. (I actually could replace with anything).

RE: How to make a userfriendly sql query structure

No problem, glad I was able to help.

RE: How to make a userfriendly sql query structure

(OP)
@pmegan,

I wonder how do I remove elements from the stringbuilder?

thank you in advance.

RE: How to make a userfriendly sql query structure

Parametized queries are great, but only if the structure of the query isn't going to change much. It sounds like your program is going to give the user a way of defining the result set and filters on the fly, which won't work with this kind of query. You would have to create a template for every combination of results and filters.

The StringBuilder doesn't really have elements like an array, it's really just a concatenation tool. I wouldn't try to remove parts of the string. Whenever I do stuff like this I create a function that builds the query, and call it when a button is clicked to run the query.

RE: How to make a userfriendly sql query structure

In fact using a List(Of String) or ArrayList as you have done gives the most flexibility.

RE: How to make a userfriendly sql query structure

(OP)
@pmegan
ok I understand then I don't want to use SQL parameters. my query have to be very flexible.
Thank you

RE: How to make a userfriendly sql query structure

It seems like this is getting more complicated than it needs to be. I'm not sure why you would need to remove sections of a query. I certainly wouldn't do that. If there's a procedure that creates the query dynamically, I'd just create a new query when the criteria changes rather than try to remove a piece of text.

Can you give a little more detail on how this is going to work? I'm assuming there will be a form where the user will pick desired result fields for the Select clause, and some text boxes or drop downs with the filter criteria for the Where clause. Is that correct?

I've done a couple of programs that are similar to that, so made a quick form with 4 checkboxes to allow users to add up to 4 columns
Name: chkField_1, tag: dbField1, text: Field 1
Name: chkField_2, tag: dbField2, text: Field 2
Name: chkField_3, tag: dbField3, text: Field 3
Name: chkField_4, tag: dbField4, text: Field 4

2 MaskedTextBox controls for the year and order number. These are hardcoded so I didn't use tags.
txtYear
txtOrderNum

And 4 textboxes for additional filters
Name: txtWhere_1, tag: dbfilter1
Name: txtWhere_2, tag: dbfilter2
Name: txtWhere_3, tag: dbfilter3
Name: txtWhere_4, tag: dbfilter4

In an actual program, the tags would be set to the associated column name from the database. This allows you to pass the controls into generic processing functions instead of using a giant list of if/then statements.

CODE

Private Function MakeQuery() As String

        ' function to create a SQL query from user supplied criteria
        ' checkboxes and textboxes on the from must have the associated 
        ' data table fieldname set as the tag
        ' result set column names will be taken from the checkbox text

        Dim sb As New System.Text.StringBuilder
        Dim strQuery As String = [String].Empty

        Try

            With sb

                .AppendLine("SELECT")
                .AppendLine("ordernr [Order number]")
                .AppendLine(",offertnr [Quotation number]")

                BuildSelectLine(sb, chkField_1)
                BuildSelectLine(sb, chkField_2)
                BuildSelectLine(sb, chkField_3)
                BuildSelectLine(sb, chkField_4)

                .AppendLine("FROM")
                .AppendLine("tt")
                .AppendLine("WHERE 0 = 0")

                ' if the year textbox has a value, add it to the query
                If Not txtYear.Text = "" Then
                    .AppendLine("AND DATEPART(YEAR,tt.date) = " & txtYear.Text)
                End If

                BuildWhereLine(sb, txtOrderNum)
                BuildWhereLine(sb, txtWhere_1)
                BuildWhereLine(sb, txtWhere_2)
                BuildWhereLine(sb, txtWhere_3)
                BuildWhereLine(sb, txtWhere_4)

            End With

            strQuery = sb.ToString '.Replace(vbCrLf, " ")

        Catch ex As Exception

        End Try

        Return strQuery

    End Function

    ''' <summary>
    ''' Adds and element to the SELECT query if checked
    ''' </summary>
    ''' <param name="sb">stringbuilder passed in by reference</param>
    ''' <param name="cb">the checkbox to process</param>
    ''' <remarks></remarks>
    Private Sub BuildSelectLine(ByRef sb As System.Text.StringBuilder, cb As CheckBox)
        If cb.Checked Then
            sb.AppendLine("," & cb.Tag & " [" & cb.Text & "]")
        End If
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause if the textbox is not empty
    ''' </summary>
    ''' <param name="sb">stringbuilder passed in by reference</param>
    ''' <param name="txt">user entered filter criteria</param>
    ''' <remarks></remarks>
    Private Sub BuildWhereLine(ByRef sb As System.Text.StringBuilder, txt As TextBox)
        If txt.Text <> "" Then
            sb.AppendLine("AND " & txt.Tag & " = '" & txt.Text & "'")
        End If
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause if the maskedtextbox is not empty
    ''' </summary>
    ''' <param name="sb">stringbuilder passed in by reference</param>
    ''' <param name="txt">user entered filter criteria</param>
    ''' <remarks></remarks>
    Private Sub BuildWhereLine(ByRef sb As System.Text.StringBuilder, txt As MaskedTextBox)
        If txt.Text <> "" Then
            sb.AppendLine("AND " & txt.Tag & " = '" & txt.Text & "'")
        End If
    End Sub 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close