Plz Help, Search Date in Database

[digitalsindhx]

My Search Date sql Query is not working plz help any body.
i set my Date formate dd/mm/yyyy in control panel, and in Access i set coulumns field as shortDate,

I used Folowing Sql Quries but non of them works

PurchaseSql = "Select * from PUrchase where PurchaseDate => '" & DatePurchaseFrm.Value.ToString("dd-MM-yyyy") & "' order By PUrchaseID"

        PurchaseSql = "Select * from PUrchase where PurchaseDate =@05/04/2010  order By PUrchaseID"

        PurchaseSql = "SELECT * FROM purchase WHERE PUrchaseDate between '" & Format(Me.DatePurchaseFrm.Value, "dd/dd/yyyy") & "' AND '" & Format(Me.DatePurchaseTo.Value, "dd/MM/yyyy") & "'"

        PurchaseSql = "Select * from PUrchase where PurchaseDate ='" & DatePurchaseFrm.Value & "' order By PUrchaseID"

        PurchaseSql = "Select * from PUrchase where CONVERT(CHAR(10),PurchaseDate) =>" & DatePurchaseFrm.Value.ToString("dd-mm-yyyy") & " order By PUrchaseID"

        PurchaseSql = "SELECT * FROM Purchase WHERE PurchaseDate>= DateValue('" & DatePurchaseFrm.Value & "')" & " ORDER BY PurchaseID"

        PurchaseSql = "select * from Purchase"

'yes i also used rowfilter function as 

PurchaseDt.DefaultView.RowFilter = "PurchaseDate >= DateValue('" & DatePurchaseFrm.Value & "')"

for further refrence i m also attaching file, plz Help to solve

[inthewind]

Easiest way...

Given that you have two database tables with at least the following...

purchaseMaster (table)
--    purID (field - key)
--    purDate (field)
--    itemID (field - foreign key)

purchaseDetail (table)
--    itemID (field - key/foreign key)
--    itemName (field)
--    etc...

To keep your database normalized and quick to respond, usually, you would split the purchase items into 'header' and 'detail' sections by using master/detail tables.

These two tables will have a relationship between the itemID fields to link them.

- create a link to at least one text box on your form to the database, which will create a collection of data adapters (for SQL) calling it "PurchaseMaster"

- use "PurchaseMaster" as the datasource for the datagrid

- configure the data adapter to add a query called "ByDate" which has this as your SQL statment...

SELECT        PurID, PurDate, ItemID
FROM            PurchaseMaster
WHERE PurDate between @startdate and @enddate

- under your search button

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Me.PurchaseMasterTableAdapter.ByDate(Me.PurchaseMasterDataSet.PurchaseMaster, _
Me.DatePurchaseFrm.Value, _
Me.DatePurchaseTo.Value)

        Catch ex As Exception
            MsgBox(ex.ToString, , "select error")

        End Try
    End Sub

When you load the form, all items will display. When you select a date from & to, then press 'search' you will narrow the results to the selected dates.

To reselect all items in your purchase order items database again, under your cancel button...reload the entire table using the "fill" method of the data adapter. Me.PurchaseMasterTableAdapter.Fill(Me.PurchaseMasterDataSet.PurchaseMaster) The way I am linking your calendar items in is by putting the two "@date" items in the SQL Query, and then in your fill "by date" update under the search button, you will put the names of the physical date picker controls.

You may not be able to parse this query in the designer though.

Hope that helps.

[digitalsindhx]

Dear Thanks, but i tried that code and syntax idea, and not worked at my side, can u please send me project's soft copy as example,

and yes is there no any way to search between date as was in vb6, we used to search date as simple as numeric or string data.

Thanks in Advance

[inthewind]

The code works fine on my end, but I don't know what your database structure is.

I'm using SQL Express, so the connection name is my PC, etc...

Hope that helps, Kevin

[digitalsindhx]

Dear thanks for Help, but my Database structure is MSAccess (.mdb) Database, and ur program is not running at my end, because i dont have inistalled sql server, and also i m not fimilar with sql server based database structures. can u plz modify it using .mdb file system and send me again. plz

Thanks in advance

[pritesh2010]

hii if you want to search date from database then
formate the date value

dim dt as date
dt=FormatDatetime(dtp1.Value,ShortDate)

try this one

[TrueCoding]

Well if you're using MS Access use this connection code, not sure if this is what you're having problems with? But this is my connection code that works for my Access..

con.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source =C:\blah blah whatever your folders are called..and then exact name of database .mdb

[digitalsindhx]

I Tried ur provided code but not worked, i tried it as follow

PurchaseDa.Dispose()
        PurchaseDs.Clear()
        PurchaseDt.Clear()
        PurchaseConn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & Pth & "\DisplayCenterData.mdb; User Id=admin; Password=;"

        Dim dt As Date
        dt = FormatDateTime(DatePurchaseFrm.Value, DateFormat.ShortDate)
        MsgBox(dt)

       ‘ PurchaseSql = "Select * from PUrchase where PurchaseDate => '" & dt.ToString("MM-dd-yyyy") & "' order By PUrchaseID"

‘ I also tried following qurey but not worked
''PurchaseSql = "Select * from PUrchase where PurchaseDate ='" & dt & "' order By PUrchaseID"

        PurchaseConn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & Pth & "\DisplayCenterData.mdb; User Id=admin; Password=;"

        PurchaseDa = New OleDbDataAdapter(PurchaseSql, PurchaseConn)

        PurchaseDs = New DataSet
        PurchaseDa.Fill(PurchaseDs, "DisplayCenterData")
        PurchaseDt = PurchaseDs.Tables("DisplayCenterData")

        PurchaseCmb = New OleDbCommandBuilder(PurchaseDa)

        PurchaseDa.InsertCommand = PurchaseCmb.GetInsertCommand
        PurchaseDa.UpdateCommand = PurchaseCmb.GetUpdateCommand

        
txtPurchaseID.DataBindings.Clear()
        txtCompanyID.DataBindings.Clear()
        txtBookID.DataBindings.Clear()
        txtPurchaseDate.DataBindings.Clear()
        txtSalePerson.DataBindings.Clear()

txtPurchaseID.DataBindings.Add("text", PurchaseDt, "purchaseID")
        txtCompanyID.DataBindings.Add("text", PurchaseDt, "companyID")
        txtBookID.DataBindings.Add("text", PurchaseDt, "BookID")
        txtPurchaseDate.DataBindings.Add("text", PurchaseDt, "PUrchaseDate")
        txtSalePerson.DataBindings.Add("text", PurchaseDt, "SalePerson")

plz send me zip file of working code if possible

[inthewind]

Send me a copy of your database and I will work on it...what version of VB are you using? SQL Express is free, available on MS website for download.

[digitalsindhx]

yes dear inthewind,
you may find it here
http://www.daniweb.com/forums/attachment.php?attachmentid=14405&d=1271318512
or u also may find it on this page, at top, with first post by me.

Thanks in advance

[pritesh2010]

hi i think that is not work then ok. i'll tell how to do it create one view based on your table and specifies the order by clause on that view after that search condition based your query
for formate date as per your database you can use this function also

''this is my view1 
dim str as string

Dim myDate As Date = DateTime.Now
MsgBox(Format(myDate, "MMddyy"))'''first month
MsgBox(myDate.ToString("MMddyy"))
str="Select * from [view1] where [date] >='" & CDate(trim(mydate)) & " '; "

and then pass the string to your command where we are giving the connection and all stuff.
first try the date function then try to make view and see how to pass date.

[pritesh2010]

hi i think that is not work then ok. i'll tell how to do it create one view based on your table and specifies the order by clause on that view after that search condition based your query
for formate date as per your database you can use this function also

''this is my view1 
dim str as string

Dim myDate As Date = DateTime.Now
MsgBox(Format(myDate, "MMddyy"))'''first month
MsgBox(myDate.ToString("MMddyy"))
str="Select * from [view1] where [date] >='" & CDate(trim(mydate)) & " '; "

and then pass the string to your command where we are giving the connection and all stuff.
first try the date function then try to make view and see how to pass date.

[digitalsindhx]

thanks Dear thanks for all of u, i solved my problem with this coding

PurchaseDa.Dispose()
        PurchaseDs.Clear()
        PurchaseDt.Clear()
        PurchaseConn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & Pth & "\DisplayCenterData.mdb; User Id=admin; Password=;"

        Dim dt As Date
        dt = FormatDateTime(DatePurchaseFrm.Value, DateFormat.ShortDate)


        PurchaseSql = "Select * from PUrchase where PurchaseDate >=#" & dt & "#"
        MsgBox(PurchaseSql)

        PurchaseConn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & Pth & "\DisplayCenterData.mdb; User Id=admin; Password=;"

        PurchaseDa = New OleDbDataAdapter(PurchaseSql, PurchaseConn)

        PurchaseDs = New DataSet
        PurchaseDa.Fill(PurchaseDs, "DisplayCenterData")
        PurchaseDt = PurchaseDs.Tables("DisplayCenterData")

        PurchaseCmb = New OleDbCommandBuilder(PurchaseDa)

        PurchaseDa.InsertCommand = PurchaseCmb.GetInsertCommand
        PurchaseDa.UpdateCommand = PurchaseCmb.GetUpdateCommand
        DataGridPurchase.DataSource = PurchaseDt

[digitalsindhx]

Thanks dear thanks to all of u, my following code worked nicely

PurchaseDa.Dispose()
        PurchaseDs.Clear()
        PurchaseDt.Clear()
        PurchaseConn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & Pth & "\DisplayCenterData.mdb; User Id=admin; Password=;"

        Dim dt As Date
        dt = FormatDateTime(DatePurchaseFrm.Value, DateFormat.ShortDate)


        PurchaseSql = "Select * from PUrchase where PurchaseDate >=#" & dt & "#"
        MsgBox(PurchaseSql)

        PurchaseConn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & Pth & "\DisplayCenterData.mdb; User Id=admin; Password=;"

        PurchaseDa = New OleDbDataAdapter(PurchaseSql, PurchaseConn)

        PurchaseDs = New DataSet
        PurchaseDa.Fill(PurchaseDs, "DisplayCenterData")
        PurchaseDt = PurchaseDs.Tables("DisplayCenterData")

        PurchaseCmb = New OleDbCommandBuilder(PurchaseDa)

        PurchaseDa.InsertCommand = PurchaseCmb.GetInsertCommand
        PurchaseDa.UpdateCommand = PurchaseCmb.GetUpdateCommand
        DataGridPurchase.DataSource = PurchaseDt

[inthewind]

this works

[digitalsindhx]

Thanks Dear ur provided code also worked

Thanks again.
Regards
Fahad