5 Replies - 2482 Views - Last Post: 05 April 2012 - 11:53 AM Rate Topic: ***-- 2 Votes

#1 Young_Dweezy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 01-April 12

Trying to pull data from access to word document

Posted 01 April 2012 - 12:39 PM

Ok so this is a small part of my senior project and I am heading in the right direction I just don't know where to go next. I already have code written so that "the potential" data can go to appropriate boomkarks on this custom word document.

Dim oWord As Word.Application
    Dim oDoc As Word.Document



    'Start Word and open the document template.
    oWord = CreateObject("Word.Application")
    oWord.Visible = True
    oDoc = oWord.Documents.Add("C:\Users\GuestUser\Desktop\DS Project\ME-Project\MEI\MEI\BlankOrder.dotx")
    oDoc.Bookmarks.Item("OrderNo").Range.Text = "12345"
    oDoc.Bookmarks.Item("Date").Range.Text = "10/30/1988"
    oDoc.Bookmarks.Item("Customer").Range.Text = "Dewar, You Be Trippin!"
    oDoc.Bookmarks.Item("CustomerPO").Range.Text = "Pwybt"
    oDoc.Bookmarks.Item("CustomerJobNo").Range.Text = "JN12345"
    oDoc.Bookmarks.Item("Project").Range.Text = "Gangsta Stuff"
    oDoc.Bookmarks.Item("ItemNo").Range.Text = "1"
    oDoc.Bookmarks.Item("ItemNo2").Range.Text = "1"
    oDoc.Bookmarks.Item("ItemNo3").Range.Text = "1"
    oDoc.Bookmarks.Item("JobNo").Range.Text = "2134"
    oDoc.Bookmarks.Item("JobNo2").Range.Text = "2134"
    oDoc.Bookmarks.Item("JobNo3").Range.Text = "2134"
    oDoc.Bookmarks.Item("Qty").Range.Text = "2"
    oDoc.Bookmarks.Item("Qty2").Range.Text = "2"
    oDoc.Bookmarks.Item("Qty3").Range.Text = "2"
    oDoc.Bookmarks.Item("Description").Range.Text = "This is just a test!!!!"
    oDoc.Bookmarks.Item("Description2").Range.Text = "This is just a test!!!!"
    oDoc.Bookmarks.Item("Description3").Range.Text = "This is the real deal, Big Mac Meal"
    oDoc.Bookmarks.Item("PriceEach").Range.Text = "$465.72"
    oDoc.Bookmarks.Item("PriceEach2").Range.Text = "$465.72"
    oDoc.Bookmarks.Item("PriceEach3").Range.Text = "$465.72"
    oDoc.Bookmarks.Item("Delivery").Range.Text = "Next Week"
    oDoc.Bookmarks.Item("Delivery2").Range.Text = "Next Week"
    oDoc.Bookmarks.Item("Delivery3").Range.Text = "Next Week (If you're lucky...)"
    oDoc.Bookmarks.Item("SpecialInstructions").Range.Text = "Dont blow it up"
    oDoc.Bookmarks.Item("RLM").Range.Text = "Yes"
    oDoc.Bookmarks.Item("SubmittalDRW").Range.Text = "Picasso Painting"
    oDoc.Bookmarks.Item("ASMECode").Range.Text = "9876"
    oDoc.Bookmarks.Item("FabDRW").Range.Text = "stick figure"
    oDoc.Bookmarks.Item("OMData").Range.Text = "Yo Mama!"



This code opens up a word document and puts the text in certain bookmarks labeled on the attached document.

MY PROBLEM, is that I dont know "fully" how to incorporate a check to make sure if an order number exists and then to then forward/bring the data to these appropriate bookmarks. My MIS classes have not fully prepared me for this in depth code I'm surprised i got this far but any help would be very helpful.

I have this code so far but me and my team have no idea where to go next.

        Dim strName As String
        Dim SerNum As String
        Dim NumRec As String


        strName = InputBox("Please enter the corresponding ORDER NUMBER", "Order Number")
        TextBox1.Text = strName

        SerNum = TextBox1.Text
        If IsNumeric(SerNum) And SerNum.Length = 6 Then

            Dim conn As New OleDb.OleDbConnection
            conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;DataSource=C:\users\GuestUser\Desktop\mei-db.accdb"
            Dim SelSta As String = "Select Count(ID) from Order"
            Dim cmd As New OleDb.OleDbCommand

            cmd.CommandText = SelSta
            cmd.CommandType = CommandType.Text
            cmd.Connection = conn

            cmd.Connection.Open()
            NumRec = cmd.ExecuteScalar
            cmd.Connection.Close()

            'ERROR! in above line
            'This is where I get "Keyword not supported: 'provider'" when I press the lookup button
            If NumRec = 0 Then
                MsgBox("No items Returned")
            Else
                'This is where I want to fill in form
                'so for example, if I enter order number 300252
                'it will look for ID = 300252 and if it exists, it will
                'pull OrderNumber from database and put it in the given bookmarks
                
                MsgBox("There's stuff here")

                'THE CODE WRITTEN IN BEGINNING (with opening up word) WILL GO IN HERE ONCE THE PROGRAM CAN SEE DATA

            End If
        End If
    End Sub



Once again any help will be greatly appreciated, thank you. :bigsmile:

Attached File(s)



Is This A Good Question/Topic? 0
  • +

Replies To: Trying to pull data from access to word document

#2 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 181
  • View blog
  • Posts: 659
  • Joined: 03-February 10

Re: Trying to pull data from access to word document

Posted 01 April 2012 - 07:43 PM

So you have 2 issues:
1) How to check if an order exists
2) If the order exists then forward the data to Word, otherwise ???

1) The approach that I would use is to select the data from the database based on the inputed order number.

2) And load that into a dataset/datatable then you can check the number of rows returned.

     Dim SelSta As String = "SELECT *  FROM Order WHERE OrderNumber = @OrderNumber"

     Dim cmd As New OleDb.OleDbCommand

     Dim odbParameter As New OleDbParameter()
     odbParameter.ParameterName = "@OrderNumber"
     odbParameter.OleDbType = OleDbType.BigInt
     odbParameter.Value = SerNum

     cmd.Parameters.Add(odbParameter)

     cmd.CommandText = SelSta
     cmd.CommandType = CommandType.Text
     cmd.Connection = conn

     cmd.Connection.Open()

     Dim oledbReader As New OleDbDataReader = cmd.ExecuteReader()
     Dim dtOrder As New DataTable()

     If oledbReader IsNot Nothing AndAlso oledbReader.HasRows Then
          dtOrder.Load(oledbReader)
     End If

     
     cmd.Connection.Close()

     If dtOrder.Rows.Count > 0 Then
          'the order exists and you can fill the word document
     Else
          'the order does not exist and you can do what you need to
     End If




This is a simple addition to the code that you supplied. What would be better is to create a class for the order data. To create some separate methods to handle the querying of the database.
Was This Post Helpful? 0
  • +
  • -

#3 Young_Dweezy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 01-April 12

Re: Trying to pull data from access to word document

Posted 02 April 2012 - 12:50 PM

Now I had this code that would make the customer enter a order number and that order number was turned into the string

        strName = InputBox("Please enter the corresponding ORDER NUMBER", "Order Number")
        TextBox1.Text = strName

        SerNum = TextBox1.Text
        If IsNumeric(SerNum) And SerNum.Length = 6 Then



But I tried your code, i didnt know if you wanted me to try to put it in the middle of the code or have that be the main code, (btw im using vb 2010) It showing me errors with the


 Dim odbParameter As New [u]OleDbParameter()[/u]   (underlined is where im getting error)



and

        Dim oledbReader As New OleDbDataReader = [u]cmd.ExecuteReader()[/u]
        Dim dtOrder As New DataTable()

        If [u]oledbReader[/u] IsNot Nothing AndAlso [u]oledbReader[/u].HasRows Then

            dtOrder.Load[u](oledbReader)[/u]



Do i have to initiate a dbreader and oledbparameter somewhere else ? Thanks
Was This Post Helpful? 0
  • +
  • -

#4 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 181
  • View blog
  • Posts: 659
  • Joined: 03-February 10

Re: Trying to pull data from access to word document

Posted 02 April 2012 - 06:46 PM

The errors are because I didn't see that you had to qualify the OleDbCommand, so to use the OleDbParameter, OleDbType, OleDbDataReader objects you would also need the OleDb. in front.

As for where the code goes you should be able to put it as I have it in my example with your code. The one thing I assumed was that your database is using an OrderId that is a bigint or long value type. If it is not then you will need to change the OleDb.OleDbType to the proper datatype for your database.
Was This Post Helpful? 0
  • +
  • -

#5 Young_Dweezy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 01-April 12

Re: Trying to pull data from access to word document

Posted 03 April 2012 - 08:08 PM

Ok Right now i have everything it seems like in place tell me if I'm wrong

Dim strName As String
        Dim SerNum As String
        'Dim NumRec As String

        strName = InputBox("Please enter the corresponding ORDER NUMBER", "Order Number")
        TextBox1.Text = strName

        SerNum = TextBox1.Text

        If IsNumeric(SerNum) And SerNum.Length = 6 Then

            Dim conn As New OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;DataSource=C:\users\GuestUser\Desktop\mei-db.accdb")
            Dim SelSta As String = "SELECT *  FROM Order WHERE OrderNumber = @OrderNumber"
            Dim cmd As New OleDb.OleDbCommand
            Dim odbParameter As New OleDb.OleDbParameter()

            odbParameter.ParameterName = "@OrderNumber"
            odbParameter.OleDbType = Data.OleDb.OleDbType.BigInt
            odbParameter.Value = SerNum
            cmd.Parameters.Add(odbParameter)

            cmd.CommandText = SelSta
            cmd.CommandType = CommandType.Text
            cmd.Connection = conn
            cmd.Connection.Open()    '''''THIS IS WHERE IT DOES NOT WORK


            'NumRec = cmd.ExecuteScalar
            'cmd.Connection.Close()



            Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
            Dim dtOrder As New DataTable()
            If Reader IsNot Nothing AndAlso Reader.HasRows Then

                dtOrder.Load(Reader)

            End If
            cmd.Connection.Close()


            If dtOrder.Rows.Count > 0 Then

                MessageBox.Show("There is stuff here!")

                'the order exists and you can fill the word document

                'Dim oWord As Word.Application
                'Dim oDoc As Word.Document
                ''Start Word and open the document template.
                'oWord = CreateObject("Word.Application")
                'oWord.Visible = True
                'oDoc = oWord.Documents.Add("C:\Users\GuestUser\Desktop\DS Project\ME-Project\MEI\MEI\BlankOrder.dotx")
                'oDoc.Bookmarks.Item("OrderNo").Range.Text = "12345"
                'oDoc.Bookmarks.Item("Date").Range.Text = "10/30/1988"
                'oDoc.Bookmarks.Item("Customer").Range.Text = "Dewar, You Be Trippin!"
                'oDoc.Bookmarks.Item("CustomerPO").Range.Text = "Pwybt"
                'oDoc.Bookmarks.Item("CustomerJobNo").Range.Text = "JN12345"
                'oDoc.Bookmarks.Item("Project").Range.Text = "Gangsta Stuff"
                'oDoc.Bookmarks.Item("ItemNo").Range.Text = "1"
                'oDoc.Bookmarks.Item("ItemNo2").Range.Text = "1"
                'oDoc.Bookmarks.Item("ItemNo3").Range.Text = "1"
                'oDoc.Bookmarks.Item("JobNo").Range.Text = "2134"
                'oDoc.Bookmarks.Item("JobNo2").Range.Text = "2134"
                'oDoc.Bookmarks.Item("JobNo3").Range.Text = "2134"
                'oDoc.Bookmarks.Item("Qty").Range.Text = "2"
                'oDoc.Bookmarks.Item("Qty2").Range.Text = "2"
                'oDoc.Bookmarks.Item("Qty3").Range.Text = "2"
                'oDoc.Bookmarks.Item("Description").Range.Text = "This is just a test!!!!"
                'oDoc.Bookmarks.Item("Description2").Range.Text = "This is just a test!!!!"
                'oDoc.Bookmarks.Item("Description3").Range.Text = "This is the real deal, Big Mac Meal"
                'oDoc.Bookmarks.Item("PriceEach").Range.Text = "$465.72"
                'oDoc.Bookmarks.Item("PriceEach2").Range.Text = "$465.72"
                'oDoc.Bookmarks.Item("PriceEach3").Range.Text = "$465.72"
                'oDoc.Bookmarks.Item("Delivery").Range.Text = "Next Week"
                'oDoc.Bookmarks.Item("Delivery2").Range.Text = "Next Week"
                'oDoc.Bookmarks.Item("Delivery3").Range.Text = "Next Week (If you're lucky...)"
                'oDoc.Bookmarks.Item("SpecialInstructions").Range.Text = "Dont blow it up"
                'oDoc.Bookmarks.Item("RLM").Range.Text = "Yes"
                'oDoc.Bookmarks.Item("SubmittalDRW").Range.Text = "Picasso Painting"
                'oDoc.Bookmarks.Item("ASMECode").Range.Text = "9876"
                'oDoc.Bookmarks.Item("FabDRW").Range.Text = "stick figure"
                'oDoc.Bookmarks.Item("OMData").Range.Text = "Yo Mama!"
            Else

                'the order does not exist and you can do what you need to
                MsgBox("No items Returned")
            End If

        End If



I get an error saying that OLEDBEXCEPTION was not handled, "Could not find installable ISAM."

Do you know what I would need to do with that error?
Was This Post Helpful? 0
  • +
  • -

#6 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 181
  • View blog
  • Posts: 659
  • Joined: 03-February 10

Re: Trying to pull data from access to word document

Posted 05 April 2012 - 11:53 AM

Your connection string is the issue. I am not sure if the case is important, never really tried (why temp fate :) ), but spaces are definitely important.

Dim conn As New OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;DataSource=C:\users\GuestUser\Desktop\mei-db.accdb")


should be
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\users\GuestUser\Desktop\mei-db.accdb;Persist Security Info=False;")



Connection Strings
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1