Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Binding a textbox from a access query

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
BKennedy2008
Asked:
BKennedy2008
  • 6
  • 6
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Nick67Commented:
<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
 
BKennedy2008Author Commented:
Nick,

Sounds like a winner. I am installing the support for access with .net program ability support and will test out <Grin> </Grin>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BKennedy2008Author Commented:
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
 
Nick67Commented:
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
 
BKennedy2008Author Commented:
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
 
BKennedy2008Author Commented:
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
 
Nick67Commented:
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
 
Nick67Commented:
Wrong quote probably
Not
"[Jobs].[Job No]= @JobHolder "
but
"[Jobs].[Job No]=" & @JobHolder
0
 
BKennedy2008Author Commented:
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
 
Nick67Commented:
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
 
BKennedy2008Author Commented:
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
 
Nick67Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now