Go Premium for a chance to win a PS4. Enter to Win


Binding a textbox from a access query

Posted on 2011-09-15
Medium Priority
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")
        Dim qry As String
        Dim cmd As OleDbCommand
        qry = "SELECT * FROM [Jobs] WHERE ([Job No] = @NewJob)"
        cmd = New OleDbCommand(qry, conn)
        TextBox1.Text = ????????
        Textbox2.text = ???????
Question by:BKennedy2008
  • 6
  • 6
LVL 85
ID: 36544945
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
LVL 26

Expert Comment

ID: 36544999
<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
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
           "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


Author Comment

ID: 36545109

Sounds like a winner. I am installing the support for access with .net program ability support and will test out <Grin> </Grin>
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 36549509
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?
LVL 26

Expert Comment

ID: 36551329
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.

Author Comment

ID: 36560881
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

Author Comment

ID: 36561065
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
LVL 26

Expert Comment

ID: 36561070
Can you do me an IMMENSE favor?
Can you have a look at
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.

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
LVL 26

Expert Comment

ID: 36561077
Wrong quote probably
"[Jobs].[Job No]= @JobHolder "
"[Jobs].[Job No]=" & @JobHolder

Author Comment

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

Accepted Solution

Nick67 earned 1000 total points
ID: 36561161
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?

Author Comment

ID: 36561445
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 & "'")



    End Sub
LVL 26

Expert Comment

ID: 36561616
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.


Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

972 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