Search in a Datatable/Dataset/Datagrid

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

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

soory... type
dv.Rowfilter ="dateColumn=#" & datetime.tostring & "#"

alright chaosian--what is "GetMyData()"

That's the part where you put your data from the SQL server into a datatable...

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?

           dgGrabowski.DataSource = dv
            dgGrabowski.DataBind
        End If

   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

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)

on this line:
daGrabowski.Fill(mGrabowski, "Grabowski")
it says:
string is not recongnized as a valid datetime

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.

Change
 oCommand.Parameters("@SPS_Dt").Value = FormatSQLString(mSPSDate)
to
 oCommand.Parameters("@SPS_Dt").Value =mSPSDate

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 & "#"
?

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

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.

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]

Thats my code that attempts to convert a string to a datetime in sql--doesn't work

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

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!

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