Solved

Binding a textbox from a access query

Posted on 2011-09-15
13
399 Views
Last Modified: 2012-06-27
As much as I hate access, I am stuck creating a web page into the database. I popluate the DB just fine, and now want to show the results page but not using the standard asp.net controls, as I need to move the data around to a printer friendly form that they are used to. My thoughts were creating another form in the results page with textbox's and binding the textbox's with the recordset that they just entered. I am half way there, with using a protected sub on load, that pulls the recordset, however, does access utilize a datareader like SQL? this is the code I started but having no luck on the finish side:

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=d:\LeadSheets\App_Data\DB.mdb")
        conn.Open()
        Dim qry As String
        Dim cmd As OleDbCommand
        qry = "SELECT * FROM [Jobs] WHERE ([Job No] = @NewJob)"
        cmd = New OleDbCommand(qry, conn)
        cmd.ExecuteNonQuery()
        TextBox1.Text = ????????
        Textbox2.text = ???????
        etc.
0
Comment
Question by:BKennedy2008
  • 6
  • 6
13 Comments
 
LVL 84
Comment Utility
So you're working in ASP.NET? And you're connecting to an Access database?

Access databases can be used with a standard OleDataAdapter if needed.

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM SomeTable", conn)
Dim ds As New Dataset

da.Fill(ds, "MyTable")

If ds.Tables("MyTable").Rows.Count > 0 Then
  For each dtr As Datarow in ds.Tables("MyTable").Rows
    Me.textbox1.text = dtr.Items("SomeField").ToString.Trim
    etc etc
  Next dtr
End If
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<grin>  When posting in the MS Access zone, it might not be such a great idea to start your question with <As much as I hate access> </grin>
Now, just for fun, for another question I just finished working out how to automate Access from C#
I realize you are using VB, but I suspect you'll be better at working the code back to VB than I would--since I do VBA and not .NET
The most important bits of it are here
http://rnarayana.blogspot.com/2008/02/access-2007-office-automation-using-c.html
You could create an Access report <grin><As much as I hate access> </grin> and have your web page print it.
And an Access report can print MUCH nice than can an HTML document--but you'd have to create a report.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Access=Microsoft.Office.Interop.Access;
using System.Reflection;

namespace testAccess
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
        // Start a new instance of Access for Automation:
        Access.Application myAccessInstance = new Access.Application();


        // Open a database in exclusive mode:
        // Replace with your file name, not m:\\yaddayadda
        myAccessInstance.OpenCurrentDatabase(
           "M:\\test\\TI_Prog.mdb", //filepath
           true //Exclusive
           );
        myAccessInstance.Visible = true;
        myAccessInstance.UserControl = true;
        //myAccessInstance.DoCmd.OpenForm ("frmCertStatus", Access.AcFormView.acNormal);
        //replace "MyReport" with what you need
        myAccessInstance.DoCmd.OpenReport ("myreport", Access.AcView.acViewNormal);
        



        }
    }
}

Open in new window

0
 

Author Comment

by:BKennedy2008
Comment Utility
Nick,

Sounds like a winner. I am installing the support for access with .net program ability support and will test out <Grin> </Grin>
0
 

Author Comment

by:BKennedy2008
Comment Utility
Hmmm. The issue I have is getting the Com to register so that it recognizes the import, Imports Access = Microsoft.Office.Interop.Access

I installed the .net for Office and access using the CD, added a reference for office 12 project library to the COM, and still is doesn't recognize it, did you find another trick to making this work?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Please understand that I am not an ASP.NET guy at all.
But I had another question that made me reseacrh the issue of Office automation from .Net and I shared the results of that.

Now, I had to set the references to  Microsoft Access 11.0 Object Library
Full blown Office is installed on my machine.
I had to add

using Access=Microsoft.Office.Interop.Access;
using System.Reflection;


manually to the top of the code module

I have pretty much the entirety of VS 2010 Express installed at the moment.
This includes the VS 2101 Tools for Office Runtime (x64)

I suspect Imports is a keyword.
You'll note that my code sample does not have the word Imports in it.
Is that relevant?

I can email you the project directory that the code came from, so you can peruse the source file
Will that be helpful?
I cannot post it as the upload won't permit most of those file types.
0
 

Author Comment

by:BKennedy2008
Comment Utility
I got everything working, it pulls the report utilizing the code below, the trick is the imports, However, does anyone know how to send a variable to the access report as it needs a job number to run the report.

Imports Microsoft.Office.Interop.Access
Imports Microsoft.Office.Interop.Access.Dao
Imports Microsoft.Office.Core

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim MSA As New Microsoft.Office.Interop.Access.Application()
        MSA.Application.Visible = True
        MSA.OpenCurrentDatabase("Path to access.mdb", False)
        MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal)

    End Sub
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:BKennedy2008
Comment Utility
I know I am close but the session variable is not populating @JobHolder, I am missing something

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim MSA As New Microsoft.Office.Interop.Access.Application()
        Dim JobHolder As String
        Session("NewJob") = "2007050104"  '(For Testing purposes)
        JobHolder = Session("NewJob")
        MSA.Application.Visible = True

        MSA.OpenCurrentDatabase("D:\LeadSheets\App_Data\URIDB.mdb", False)

        MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal, WhereCondition:="[Jobs].[Job No]= @JobHolder ")



    End Sub
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Can you do me an IMMENSE favor?
Can you have a look at
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_27217263.html#a36551688
And chime in to help this dude out.
It's been a PITA, but was for his question that I created the code that I posted and you found useful.

Now,
Probably the easiest way, given that this is Office automation, is to send your variable into the report via OpenArgs
OpenArgs is a string value that you can send into the report.
In the reports OnOpen() event, you can then coerce the string to whatever you need.
In the report you can use the OpenArgs value to set a global variable, build a Filter string, or base your report off a QueryDef object as your need dictates.

In the intellisense for MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal)
Its's the very last argument that it can take

Let me know if you need a hand to work that out
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Wrong quote probably
Not
"[Jobs].[Job No]= @JobHolder "
but
"[Jobs].[Job No]=" & @JobHolder
0
 

Author Comment

by:BKennedy2008
Comment Utility
No, I tried that, doesn't like it, its expecting expression. When I set the application to true, its asking for the variable @JobHolder
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 total points
Comment Utility
By the time it gets here

MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal, WhereCondition:="[Jobs].[Job No]= " & @JobHolder)

@JobHolder has to exist and come from someplace.
It's not coming from anything in Access, is it?
So from where in the Vb.Net stuff is it coming from?
0
 

Author Comment

by:BKennedy2008
Comment Utility
Works like a champ, thanks

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim MSA As New Microsoft.Office.Interop.Access.Application()
        Dim cboJobNums As String

        cboJobNums = Session("NewJob")
        MSA.Application.Visible = False

        MSA.OpenCurrentDatabase("Path to MDB", False)

        MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal, WhereCondition:="[Jobs].[Job No]= '" & cboJobNums & "'")

        MSA.Application.DoCmd.Close()

        MSA.CloseCurrentDatabase()


    End Sub
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Glad that for a guy who<grin><As much as I hate access> </grin>
you can kick it around pretty good and got it to work for you!

If you could have a look at that other question I posted a link to, I'd appreciate it.
The OP there doesn't seem to appreciate too much, though, so don't feel compelled to pour a lot of effort into it.

Thanks,
Nick67
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now