• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

Search in a Datatable/Dataset/Datagrid

How do i search through a datatable or dataset or datagrid?  Right now I have the sql server running through all of the jobs to pull out the jobs from a certain client.  It then displays all the jobs from that particular client in a datagrid.  I now want to search through this datagrid/dataset/datatable (whatever) for a particular date that the user is searching for via a date time picker.  (client process (open a job--click on reports button--another form loads with all of that one client's jobs--client selects a date from the datetimepicker and clicks search--from here i want only the date that the user selected in the datetimepicker to show up from the jobs in the datagrid (not from the jobs in the entire database)).  I think I'm clear on this--let me know if there are any questions!
0
computerg33k
Asked:
computerg33k
1 Solution
 
iboutchkineCommented:
Is this what you are looking for?

This is from .NET help on DataRow.Find

 The following example uses the values of an array to find a specific row in a collection of DataRow objects.
The method presumes a DataTable exists with three primary key columns. After creating an array of
the values, the code uses the Find method with the array to get the particular object desired.
This example shows how to use one of the overloaded versions of Find.

Private Sub FindInMultiPKey(ByVal myTable As DataTable)
   Dim foundRow As DataRow
   ' Create an array for the key values to find.
   Dim findTheseVals(2) As Object
   ' Set the values of the keys to find.
   findTheseVals(0) = "John"
   findTheseVals(1) = "Smith"
   findTheseVals(2) = "5 Main St."
   foundRow = myTable.Rows.Find(findTheseVals)
   ' Display column 1 of the found row.
   If Not (foundRow Is Nothing) Then
     Console.WriteLine(foundRow(1).ToString())
   End If
End Sub


the example above presumes that the DataTable already has primary key columns set
0
 
Jeff CertainCommented:
Alternately, you can use create a dataview and set dataview.RowFilter ="columnA=value1"

This approach allows you to keep the original dataset untouched, and still retrieve all rows in a format that is easy to bind to .Net controls. In addition, you can store the underlying dataset in a session variable to keep from having to hit the db server again.

private sub SetDataGrid(dateSelected as date)
'retrieve data
dim dt as datatable = GetMyData()
dim dv as new dataview(dt)
if Isnothing(dateselected) then
dv.rowfilter=""
else
dv.Rowfilter ="dateColumn=datetime"
end if
datagrid1.datasource = dv
datagrid1.databind
end sub
0
 
Jeff CertainCommented:
soory... type
dv.Rowfilter ="dateColumn=#" & datetime.tostring & "#"
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
computerg33kAuthor Commented:
alright chaosian--what is "GetMyData()"
0
 
Jeff CertainCommented:
That's the part where you put your data from the SQL server into a datatable...
0
 
computerg33kAuthor Commented:
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        WaitCursor()
        SetDataGrid(DateTimePicker1.Value.ToString)
    End Sub

    Private Sub SetDataGrid(ByVal dateSelected As Date)
        'retrieve data
        Dim frmGrabowskiReports As New frmGrabowskiReports()
        If frmGrabowskiReports.propGenerate Then
            Dim dt As DataTable
            dt = frmGrabowskiReports.propDataSet.Tables("Grabowski")
            Dim dv As New DataView(dt)
            If IsNothing(dateSelected) Then
                dv.RowFilter = ""
            Else
                dv.RowFilter = "dateColumn=#" & DateTimePicker1.Value.ToString & "#"
            End If

            dgGrabowski.DataSource = dv
        End If






Didn't work--am I doing something wrong?
0
 
Jeff CertainCommented:
           dgGrabowski.DataSource = dv
            dgGrabowski.DataBind
        End If
0
 
computerg33kAuthor Commented:
   Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        WaitCursor()
        SetDataGrid(DateTimePicker1.Value.ToString)
    End Sub

    Private Sub SetDataGrid(ByVal dateSelected As Date)
        WaitCursor()
        'Instantiate Jobs Object
        Dim objGrabowski As New ExcelGrabowskiAttempt()
        objGrabowski.JobID = iJobId
        objGrabowski.ClientPK = 12548

        Dim ds As DataSet = objGrabowski.GetGrabowski
        Dim frmGrabowskiReports As New frmGrabowskiReports()
        frmGrabowskiReports.propDataSet = ds
        frmGrabowskiReports.ShowDialog()
       
        If frmGrabowskiReports.propGenerate Then
            Dim dt As DataTable
            dt = frmGrabowskiReports.propDataSet.Tables("grabowski")
            Dim dv As New DataView(dt)
            If IsNothing(dateSelected) Then
                dv.RowFilter = ""
            Else
                dv.RowFilter = "dateColumn=#" & DateTimePicker1.Value.ToString & "#"
            End If

            dgGrabowski.DataSource = dv
            dgGrabowski.SetDataBinding(dv, "Grabowski")
        End If
    End Sub




It just copied the first datagrid and reproduced the form
0
 
computerg33kAuthor Commented:
It gets hung up here:

    Public Function SearchGrabowski() As DataSet
        Dim oConnection As New SqlConnection(mConnectionString)
        Dim sSQL As String = "stp_SearchGrabowski"
        Dim oCommand As New SqlCommand(sSQL, oConnection)
        Dim mGrabowski As New DataSet()

        oCommand.Connection.Open()
        oCommand.CommandType = CommandType.StoredProcedure

        oCommand.Parameters.Clear()

        oCommand.Parameters.Add(New SqlParameter("@SPS_Dt", SqlDbType.SmallDateTime))
        oCommand.Parameters("@SPS_Dt").Value = FormatSQLString(mSPSDate)

        Dim daGrabowski As SqlDataAdapter
        daGrabowski = New SqlDataAdapter(oCommand)
        daGrabowski.Fill(mGrabowski, "Grabowski")

        oConnection.Dispose()
        oConnection = Nothing

        oCommand.Dispose()
        oCommand = Nothing

        SearchGrabowski = mGrabowski
        mGrabowski = Nothing
    End Function

the stored procedure is this
ALTER PROCEDURE stp_SearchGrabowski
      @SPS_Dt smalldatetime

AS

Declare @sSQL varchar(6000)

Select @sSQL = 'Select Distinct tbl_Job.*, tbl_JobGroup.* ' +
            'From tbl_Job, tbl_JobGroup ' +
            'Where tbl_Job.JobGroup_id = tbl_JobGroup.JobGroup_id ' +
            '  and tbl_jobgroup.client_pk = 12548 ' +
            '  and tbl_job.sps_dt LIKE ''%' + RTrim(LTrim(@sps_dt)) + '%'''

--Print(@sSQL)
Exec(@sSQL)
0
 
computerg33kAuthor Commented:
on this line:
daGrabowski.Fill(mGrabowski, "Grabowski")
it says:
string is not recongnized as a valid datetime
0
 
Jeff CertainCommented:
1. This line has to have dateColumn set to the name of the column that you're searching for dates in... dv.RowFilter = "dateColumn=#" & DateTimePicker1.Value.ToString & "#"

2. FormatSQLString(mSPSDate) -- not sure this will work when you're trying to work with a date. Also... don't see where mSPSDate is set -- I'm assuming it is a global in your code.

3.
0
 
Jeff CertainCommented:
Change
 oCommand.Parameters("@SPS_Dt").Value = FormatSQLString(mSPSDate)
to
 oCommand.Parameters("@SPS_Dt").Value =mSPSDate
0
 
computerg33kAuthor Commented:
when i changed:
oCommand.Parameters("@SPS_Dt").Value = FormatSQLString(mSPSDate)
to
oCommand.Parameters("@SPS_Dt").Value =mSPSDate

i got this:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.


and what you mean by this:
1. This line has to have dateColumn set to the name of the column that you're searching for dates in... dv.RowFilter = "dateColumn=#" & DateTimePicker1.Value.ToString & "#"

is it should be
dv.RowFilter = "@sps_dt=#" & DateTimePicker1.Value.ToString & "#"
?
0
 
computerg33kAuthor Commented:
would it be easier to change the stored procedure?  but in order to do that i need to figure out how to get the input from the client (ie: how to get the datetimepicker1.value into the stored procedure--guess i could save it into another table and do it that way--brb) hehe
0
 
Jeff CertainCommented:
1. You're trying to filter your data in some column based on the date, in order to prevent the whole dataset from being diusplayed. This is what the RowFilter does. The example I gave you assumes that the column you're filtering is called "dateColumn"... but I'm sure that's not the case. So you have to make sure that the correct column name is inserted in place of "dateColumn"

2. I don't see where mSPSDate is declared or set to a value. If it is not of type DateTime, you'll have issues. If the function FormatSQLString returns a data type other than DateTime you'll have problems.

0
 
computerg33kAuthor Commented:
Here's the setup--The user sees all the jobs from a user named Grabowski ( this is done through a stored procedure)--from there I want to be able to only see the jobs where the value of the date time picker on the form is equal to the sps_dt in the datagrid.

Another way that i could acheive this is to check the @to_print checkbox that shows on the datagrid where the job's sps_dt (which is on the datagrid) equals the date that I specify with the date time picker.

Which would be better--to pass the datetimepicker's value to sql--convert it to a date then rebind the datagrid or to use the dataview option?

Either way--I still don't really understand how to do it lol

[code]
Private Sub BindDataGrid()
       Dim objJobs As New ExcelJobs()

       objJobs.SPSDt = DateTimePicker1.Value.ToShortDateString

       Try
           Dim dt As DataTable
           dt = objJobs.GetGrabowskiJobs()
           If dt.Rows.Count = 0 Then
               lblWindowInfo.Text = "Your search returned no rows.  Reset the criteria and try again."
           Else
               dgGrabowski.DataSource = dt
               pointInCell00 = New Point(dgGrabowski.GetCellBounds(0, 0).X + 4, dgGrabowski.GetCellBounds(0, 0).Y + 4)
           End If
           dt = Nothing
       Catch Ex As Exception
           MessageBox.Show("Error retrieving Jobs.  " & sPleaseContact & vbLf & vbCr & Ex.Message)
       End Try
       objJobs = Nothing
       GC.Collect()
       GC.WaitForPendingFinalizers()
       If dgGrabowski.CurrentRowIndex = 1 Then
           Me.dgGrabowski.Select(1)
       End If
   End Sub

   Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
       WaitCursor()
       dgGrabowski.TableStyles.Add(tsSearchFind)
       BindDataGrid()
   End Sub
[/code]


[code]
ALTER PROCEDURE stp_GetGrabowskiJobs
@Sps_dt varchar(50)
AS

DECLARE @sSQL VARCHAR(6000)
SELECT @sSQL =  ' SELECT J.*, A.*, CaseStatusVV.*, JG.*, C.*, P.* ' +
       ' INTO #Temp ' +
       ' FROM ' +
       '    tbl_JobGroup JG, ' +
       '    tbl_ServiceType S, ' +
       '    tbl_ValidValue CaseStatusVV, ' +
       '    tbl_Address A, ' +
       '    tbl_Job J ' +
       '    tbl_Person P ' +
       '    tbl_Company C ' +
       ' LEFT OUTER JOIN tbl_ValidValue ServiceMannerVV ON J.ServiceManner_vv = ServiceMannerVV.ValidValue_id ' +
       ' WHERE ' +
       '    J.JobGroup_id = JG.JobGroup_id AND ' +
       '    J.ServiceType_id = S.ServiceType_id AND ' +
       '    J.CaseStatus_vv = CaseStatusVV.ValidValue_id AND ' +
       '    J.ServiceAddress_id = A.Address_id ' +
       '    J.ServiceAddress_id = A.Address_id AND ' +
       '    jg.CLIENT_PK=12548 and ' +
       '    p.person_id = j.recipient_pk and ' +
       '    c.company_id = p.company_id '
       
IF (@sps_dt <> '')
BEGIN
SELECT @sSQL = @sSQL + ' AND jg.sps_dt = ' + CAST(@sps_dt as smalldatetime) + ' ' +
Convert(varchar(50),CAST(@sps_dt as datetime),3)
END

BEGIN
SELECT @sSQL = @sSQL + ' ORDER BY Job_id '
END

SELECT @sSQL = @sSQL + ' DROP TABLE #Temp '

EXEC (@sSQL)
[/code]
0
 
computerg33kAuthor Commented:
Thats my code that attempts to convert a string to a datetime in sql--doesn't work
0
 
Jeff CertainCommented:
1. What is more efficient depends on what you're doing.... if you're letting the user select a datetime once, then use a SP to return the data you're interested in. If you want them to be able to pick a different date multiple times, then you should return a subset of your data from a SP and use the dataview to filter it every time they pick a different date. This lets you hit the database as few times as possible.

2. BEGIN;SELECT @sSQL = @sSQL + ' ORDER BY Job_id ';END  should use SET not SELECT
0
 
computerg33kAuthor Commented:
Got it..
Stored Procedure:
ALTER PROCEDURE stp_GetGrabowskiAttempts
      @Client_pk bigint
AS

DECLARE @To_Print BIT

SELECT @To_Print = 1

SELECT
JG.File_num, dbo.uf_getReversedPersonCompanyName(Recipient_vv, Recipient_pk) Recipient_nm, p.SSN, A.Address_1, A.Address_2,
A.City_nm, A.State_cd, A.Zip_cd,p.HomePhone_num, J.SpecialInstructions_desc,p.AKA_nm, ServiceMannerVV.ValidValue_nm as ServiceManner_nm,J.CaseStatus_dt,c.Company_nm, A1.Address_1, A1.Address_2,
A1.City_nm, A1.State_cd, A1.Zip_cd,c.MainPhone_num,J.ClosingComments_desc,J.Job_id,J.Fee_amt

FROM
tbl_Person P, tbl_Company C, tbl_JobGroup JG, tbl_ServiceType S, tbl_Address A, tbl_address A1, tbl_ValidValue CaseStatusVV,
tbl_ValidValue JobNavigationVV, tbl_StateCode SC, tbl_Job J
LEFT OUTER JOIN tbl_ValidValue AS ServiceMannerVV ON J.ServiceManner_vv = ServiceMannerVV.ValidValue_id
LEFT OUTER JOIN tbl_ValidValue AS RequestPriorityVV ON J.RequestPriority_vv = RequestPriorityVV.ValidValue_id
WHERE
A.State_cd = SC.State_cd AND
J.JobGroup_id = JG.JobGroup_id AND
J.ServiceType_id = S.ServiceType_id AND
J.ServiceAddress_id = A.Address_id and
J.CaseStatus_vv = CaseStatusVV.ValidValue_id AND
J.JobNavigation_vv = JobNavigationVV.ValidValue_id and
jg.CLIENT_PK=12548 and
p.person_id = recipient_pk and
c.company_id = p.company_id
ORDER BY
jg.sps_dt



function:
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        'Instantiate Jobs Object

        Dim objGrabowski As New ExcelGrabowskiAttempt()
        objGrabowski.JobID = iJobId
        objGrabowski.ClientPK = 12548
        objGrabowski.SearchDate = Trim(txtDate.Text.ToString)

        Dim ds As DataSet = objGrabowski.SearchGrabowski

        propDataSet = ds

        Try
            If propGenerate Then
                Dim dt As DataTable
                dt = propDataSet.Tables("grabowski")

                Dim iRows As Integer = dt.Rows.Count
                Dim i As Integer

                For i = 0 To iRows - 1
                    Dim mrow As DataRow
                    Dim colindex As Integer
                    Dim rowindex As Integer
                    Dim col As DataColumn

                    For Each col In dt.Columns
                        colindex += 1
                    Next
                    rowindex = 1

                    For Each mrow In dt.Rows
                        rowindex += 1
                        colindex = 0
                        For Each col In dt.Columns
                            colindex += 1
                            objGrabowski.SearchGrabowski()
                        Next
                    Next
                Next
            End If
        Catch
        End Try
        BindDataGrid(MdsGrabowski)
    End Sub

   Private Sub BindDataGrid(ByVal dsData As DataSet)
        Dim dcPK() As DataColumn
        Dim dca As DataColumn
        Dim dt As DataTable
        dt = MdsGrabowski.Tables("Grabowski")

        MdsGrabowski = dsData

        dca = MdsGrabowski.Tables("Grabowski").Columns("Job_ID")
        dcPK = dt.PrimaryKey
        MdsGrabowski.Tables("Grabowski").DefaultView.Sort = "Job_ID"
        dgGrabowski.DataSource = MdsGrabowski.Tables("Grabowski")

        dgGrabowski.SetDataBinding(MdsGrabowski, "Grabowski")
        Dim cm As CurrencyManager = CType(BindingContext(dgGrabowski.DataSource, dgGrabowski.DataMember), CurrencyManager)
        CType(cm.List, DataView).AllowNew = False
    End Sub

Thanks for all the input though!
0
 
Jeff CertainCommented:
You need to change your SP code from
J.CaseStatus_vv = CaseStatusVV.ValidValue_id AND
J.JobNavigation_vv = JobNavigationVV.ValidValue_id and
jg.CLIENT_PK=12548 and

to
J.CaseStatus_vv = CaseStatusVV.ValidValue_id AND
J.JobNavigation_vv = JobNavigationVV.ValidValue_id and
jg.CLIENT_PK=@Client_PK and
0
 
moduloCommented:
Closed, 150 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now