6 Replies - 2629 Views - Last Post: 28 February 2011 - 07:12 AM Rate Topic: -----

#1 Qnatz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 21-February 11

sql query multiple columns

Posted 25 February 2011 - 01:03 PM

hey guys
can any one help me,
i have six textboxes from which the user
can query the database. Now the user can
decide to fill all the fields and query this
i have done using AND OR in the sql syntax
but the problem is when he decides to leave
some blank my program is throwing an exception
how can i skip the blank fields and only execute
the WHERE clause for the filled boxes.
Any help will be highly appreciated.
Thanx Guys
Qnatz

Is This A Good Question/Topic? 0
  • +

Replies To: sql query multiple columns

#2 jjsaw5  Icon User is offline

  • I must break you
  • member icon

Reputation: 90
  • View blog
  • Posts: 3,060
  • Joined: 04-January 08

Re: sql query multiple columns

Posted 25 February 2011 - 01:08 PM

Can you please post back with your code? the more information you can give us the easier it will be to help you!

:code:
Was This Post Helpful? 0
  • +
  • -

#3 Shadar  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 46
  • View blog
  • Posts: 157
  • Joined: 26-May 09

Re: sql query multiple columns

Posted 25 February 2011 - 01:09 PM

If Not String.IsNullOrEmpty(TextBox1.Text.Trim) Then

        End If

This post has been edited by Shadar: 25 February 2011 - 01:10 PM

Was This Post Helpful? 0
  • +
  • -

#4 Qnatz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 21-February 11

Re: sql query multiple columns

Posted 26 February 2011 - 12:05 AM

Am Sorry guys should have done this first.
This is the code in my search button

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
       
        qSql = "SELECT * FROM tblStocks WHERE  Crop='" & ComboBoxCrop.Text & "'  ORDER BY IDStocks ASC"


            With ListView2
                .Clear()

                .View = View.Details
                .FullRowSelect = True
                .GridLines = False
                '.HeaderStyle = ColumnHeaderStyle.None
            .Columns.Add("IDStocks")
            .Columns.Add("System Date")
            .Columns.Add("Entered By")
            .Columns.Add("Document Date")
            .Columns.Add("Document No.")
            .Columns.Add("From/To")
            .Columns.Add("Crop")
            .Columns.Add("Variety")
            .Columns.Add("Stock In")
            .Columns.Add("Stock Out")
            .Columns.Add("Comment")

                FillListView(ListView2, GetData(qSql))
            End With
    End Sub


Am querying columns - 2(enteredBy) to 7(Variety).
Of-course the dates have two textboxes for start and Last dates.
The user can decide to query in any combination of the five choices.

Let me try shadar's code ll get back.
Thanks
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: sql query multiple columns

Posted 26 February 2011 - 06:22 AM

Basically you're going to have to build your sql statement by appending sections together.
One way to do this is to create variables to hold the various fields part of the select statement and then appending them together to build the final statement.

The example below is appending the controls into the string instead of using parameters. I chose this method as I think it will make it easier to understand what is happening.
        Dim sqlCrops, sqlField2, sqlField3, sqlWHERE, sqlAND As String
        Dim fieldCount As Integer = 0 'we'll use this to determine when we need WHERE and AND
        Dim sqlStart As String = "SELECT * FROM tblStocks "

        '--BEGIN BUILDING FIELD STATEMENTS--
        'The code in this section checks each box for an entry. If there is an entry
        'if builds that part of the statement and increments the fieldCount
        'otherwise, it sets that part of the statement to "" (empty string)
        If ComboBoxCrops.SelectedIndex > -1 Then
            sqlCrops = "crops = '" & ComboBoxCrops.Text & "' "
            fieldCount += 1
        Else
            sqlCrops = ""
        End If

        If Not String.IsNullOrEmpty(TextBox1.Text) Then
            sqlField2 = "field2 = '" & TextBox1.Text & "' "
            fieldCount += 1
        Else
            sqlField2 = ""
        End If

        If Not String.IsNullOrEmpty(TextBox2.Text) Then
            sqlField3 = "field3 = '" & TextBox2.Text & "' "
            fieldCount += 1
        Else
            sqlField3 = ""
        End If
        '--END BUILDING FIELD STATEMENTS

        '--BEGIN Checking for fields entered by looking at fieldCount
        'If not fields have been entered then we only need the main
        'SELECT * FROM tblStocks and nothing else but if the
        'fieldcount is one or more then we need to append WHERE 
        If fieldCount = 0 Then
            sqlWHERE = ""
        Else
            sqlWHERE = " WHERE "
        End If
        'Lastly, if the field count is 1 we don't need AND in our statement so 
        'we leave it blank. But if we enter more than just 1 field we have to 
        'have AND between those segments of the statment.
        If fieldCount > 1 Then
            sqlAND = " AND "
        Else
            sqlAND = ""
        End If
        'Lastly, we have the ORDER BY clause
        Dim sqlOrder As String = " ORDER BY IDStocks"
        Dim sqlStmt As String = sqlStart & sqlWHERE & sqlCrops & sqlAND & sqlField2 & sqlAND & sqlField3 & sqlOrder
        MsgBox(sqlStmt) '<--- sqlStmt is the newly built statment to be submitted to your database.

Was This Post Helpful? 1
  • +
  • -

#6 Qnatz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 21-February 11

Re: sql query multiple columns

Posted 28 February 2011 - 03:44 AM

Hey Guys
Woww.... This Monday is very BAD n GOOD
one for me.My boy calls in at 7:00AM, his
sister has moved on. When i slide in at
D.I.C, CharlieMay you just sorted my
**s.
Thanx al'ya

Qnatz

Guys how do i mark this as SOLVED
Was This Post Helpful? 0
  • +
  • -

#7 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: sql query multiple columns

Posted 28 February 2011 - 07:12 AM

No need to mark it. You kind of did when you clicked the + for Was this post helpful. It tells others that visit the site that it was a viable solution to the topic.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1