Ms access to Vb.net

[anishkgt]

Hi all,

I've managed to create an ms access application. i wish to do this in Vb.net with MS Visual Studio. Would that be possible. I've tried a bit but the codes are different.

 

[anishkgt]

I've added a date range query and when executing it works perfectly, since VBA is so different from VB.net i've no clue how to code it in a form. My form has two fields namely, txtFromDate and txtToDate. and my query is like this

SELECT        FIleID, MIRType, MIRSeq, ProcessedOn, PNRCrDate, MIRCrDate, BkgTkgPCC, BkgSignOnTkgSignOn, FIleName, PNR, TicketNumber, [TCN#], PaxType, Passengers, Sector, Airline, FOP, ActualAmt, TotalFare, 
                         BFare, EquivalentFare, NetProfit, Tax1, Tax2, Tax3, Tax4, Tax5, Credit, OrgTicket, SecondEx, ExType, ExFare, ExT1, ExT2, ExT3, ExT4, ExT5, PaxID
FROM            qryMIR
WHERE        (MIRCrDate BETWEEN #13-Jan-2015# AND #14-Jan-2015#)

 

[jmcilhinney]

I've added a date range query and when executing it works perfectly, since VBA is so different from VB.net i've no clue how to code it in a form. My form has two fields namely, txtFromDate and txtToDate. and my query is like this

SELECT        FIleID, MIRType, MIRSeq, ProcessedOn, PNRCrDate, MIRCrDate, BkgTkgPCC, BkgSignOnTkgSignOn, FIleName, PNR, TicketNumber, [TCN#], PaxType, Passengers, Sector, Airline, FOP, ActualAmt, TotalFare, 
                         BFare, EquivalentFare, NetProfit, Tax1, Tax2, Tax3, Tax4, Tax5, Credit, OrgTicket, SecondEx, ExType, ExFare, ExT1, ExT2, ExT3, ExT4, ExT5, PaxID
FROM            qryMIR
WHERE        (MIRCrDate BETWEEN #13-Jan-2015# AND #14-Jan-2015#)

You already have a typed DataSet so add any additional queries to that:

https://msdn.microsoft.com/en-us/library/7zt3ycf2.aspx

 

[anishkgt]

well thats how i've done. The query is available but how do i change the dates to a text box and show it up on the form. I've don't it for a single text field and it works fine on the form but the date range seems to be a little different.

 

[jmcilhinney]

You don't "change the dates to a text box". When you add the query to the table adapter, two new methods are added that you get to name. Where the default query is executed by methods named Fill and GetData, your new query is executed by method that you should name FillByX and GetDataByX, where X is a description of the filters. You then call one of those methods and pass the appropriate filter values, which in turn get passed on to the query. Where you might normally call:

myTableAdapter.Fill(myDataTable)

you now might call:

myTableAdapter.FillByDateRange(myDataTable, startDate, endDate)

If it's not working for you then you did it wrong. As you haven't shown or explained what you did, we can't really tell you what's wrong with it.

 

[anishkgt]

Hi jmcilhinney,
Total sorry if i were not clear with my posts, here is my query search which didn't work out when i executed it

SELECT        FIleID, MIRType, MIRSeq, ProcessedOn, PNRCrDate,  MIRCrDate, BkgTkgPCC, BkgSignOnTkgSignOn, FIleName, PNR, TicketNumber,  [TCN#], PaxType, Passengers, Sector, Airline, FOP, ActualAmt, TotalFare,  
                         BFare, EquivalentFare, NetProfit, Tax1, Tax2,  Tax3, Tax4, Tax5, Credit, OrgTicket, SecondEx, ExType, ExFare, ExT1,  ExT2, ExT3, ExT4, ExT5, PaxID
FROM            qryMIR
WHERE        (MIRCrDate = ?) AND (MIRCrDate = ?)

and the click event as

Private Sub btnFetch_Click(sender As Object, e As EventArgs) Handles btnFetch.Click
        QryMIRTableAdapter.FillByDateRange(Me.Backup_GAPDataSet.qryMIR, txtFrom.Text, txtTo.Text)
    End Sub

when its excuted the date 13-jan-15 to 13-jan-15 works out but 13-jan-15 to 14-jan-15 doesn't seem to be working for obvious reasons being the query statement is wrong.

 

[jmcilhinney]

How can any record ever match this unless both date parameters are the same?

WHERE        (MIRCrDate = ?) AND (MIRCrDate = ?)

If it's a range then you need to write your WHERE clause that way. How do you usually specify a range, with minimum and maximum values.

 

[anishkgt]

Not sure about vb.net but in vba its like "between forms!frmsearch!Fromdate AND forms!frmsearch!todate thenfrom forms we just call that query.

 

[jmcilhinney]

This has got nothing to do with VB.NET or VBA. It's SQL code.

 

[anishkgt]

Was wondering if you could help me out with SQL code

 

[JuggaloBrotha]

Not sure about vb.net but in vba its like "between forms!frmsearch!Fromdate AND forms!frmsearch!todate thenfrom forms we just call that query.

Are you trying to make a query like this?

SELECT        FIleID, MIRType, MIRSeq, ProcessedOn, PNRCrDate,  MIRCrDate, BkgTkgPCC, BkgSignOnTkgSignOn, FIleName, PNR, TicketNumber,  [TCN#], PaxType, Passengers, Sector, Airline, FOP, ActualAmt, TotalFare,  
                         BFare, EquivalentFare, NetProfit, Tax1, Tax2,  Tax3, Tax4, Tax5, Credit, OrgTicket, SecondEx, ExType, ExFare, ExT1,  ExT2, ExT3, ExT4, ExT5, PaxID
FROM            qryMIR
WHERE        MIRCrDate BETWEEN ? AND ?

 

[anishkgt]

Thanks it worked like a charm, it would take some time before i get things rite. Next, how do i open a record in report viewer with a double click. Do i need to start new thread as this tread is answered.

 

[anishkgt]

managed to make it this far

Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim row As DataGridViewRow
        row = Me.DataGridView1.Rows(e.RowIndex)
        [COLOR=#ff0000]frmReport.ReportViewer1 = ?[/COLOR]
    End Sub

marked in red is something that need, how do i set it display it in the reportviewer1