Solved

Binding a textbox from a access query

Posted on 2011-09-15
13
405 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
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
0
 
LVL 26

Expert Comment

by:Nick67
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
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
ID: 36545109
Nick,

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

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:BKennedy2008
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?
0
 
LVL 26

Expert Comment

by:Nick67
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.
0
 

Author Comment

by:BKennedy2008
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
0
 

Author Comment

by:BKennedy2008
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36561070
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
ID: 36561077
Wrong quote probably
Not
"[Jobs].[Job No]= @JobHolder "
but
"[Jobs].[Job No]=" & @JobHolder
0
 

Author Comment

by:BKennedy2008
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
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 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?
0
 

Author Comment

by:BKennedy2008
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 & "'")

        MSA.Application.DoCmd.Close()

        MSA.CloseCurrentDatabase()


    End Sub
0
 
LVL 26

Expert Comment

by:Nick67
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.

Thanks,
Nick67
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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