Inserting data from an array into an ms access 2000 table

Discussion in 'Ruby' started by Cooper Deford, Jul 20, 2008.

  1. Good Afternoon,

    I am trying to figure out how to get data from an array into an access
    table.

    On the rubyonwindows site I found the following code:

    db = AccessDb.new('c:\Baseball\lahman54.mdb')
    db.opendb.query("SELECT * FROM AllStar WHERE playerID =
    'conceda01';")field_names = db.fields
    rows = db.data
    db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")
    db.close

    This code works fine if I break the array up into seperate elements but
    I think there must be a simpler way.

    this is how the array is built:

    class <<Array
    def multi(n, *args, &block)
    if args.empty?
    Array.new(n, &block)
    else
    Array.new(n) do
    Array.multi(*args, &block)
    end
    end
    end
    end
    card=Array.multi(24,24)


    so the question is; How do i get the data loaded into this array into my
    access table called wps in a database wps.mdb?

    I have been trying to do something like this:

    db.execute("INSERT INTO wps_test VALUES('card [$h]');"

    Any help would be greatly appreciated.
    --
    Posted via http://www.ruby-forum.com/.
    Cooper Deford, Jul 20, 2008
    #1
    1. Advertising

  2. Cooper Deford

    David Mullet Guest

    Cooper Deford wrote:
    > Good Afternoon,
    >
    > I am trying to figure out how to get data from an array into an access
    > table.
    >
    > On the rubyonwindows site I found the following code:
    >
    > db = AccessDb.new('c:\Baseball\lahman54.mdb')
    > db.opendb.query("SELECT * FROM AllStar WHERE playerID =
    > 'conceda01';")field_names = db.fields
    > rows = db.data
    > db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")
    > db.close
    >
    > This code works fine if I break the array up into seperate elements but
    > I think there must be a simpler way.
    >
    > this is how the array is built:
    >
    > class <<Array
    > def multi(n, *args, &block)
    > if args.empty?
    > Array.new(n, &block)
    > else
    > Array.new(n) do
    > Array.multi(*args, &block)
    > end
    > end
    > end
    > end
    > card=Array.multi(24,24)
    >
    >
    > so the question is; How do i get the data loaded into this array into my
    > access table called wps in a database wps.mdb?
    >
    > I have been trying to do something like this:
    >
    > db.execute("INSERT INTO wps_test VALUES('card [$h]');"
    >
    > Any help would be greatly appreciated.


    Assuming you are working with something like the AccessDb class defined
    here...

    http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html

    ...you could iterate over your array and generate/execute an SQL insert
    statement, like this:

    my_array.each do |a|
    b = a.collect{|x| x = "'" + x + "'"}
    c = b.join(",")
    sql = "INSERT INTO MyTable VALUES (#{c});"
    db.execute(sql)
    end

    The above code can certainly be improved upon, but hopefully gives you
    an idea.

    David

    http://rubyonwindows.blogspot.com
    --
    Posted via http://www.ruby-forum.com/.
    David Mullet, Jul 22, 2008
    #2
    1. Advertising

  3. David,

    Thanks very much for your response. This works just fine as is. I fooled
    around quite a bit with join as part of the sql= statement but never
    thought to iterate through the array. This is great.

    Thanks again,

    Cooper

    --
    Posted via http://www.ruby-forum.com/.
    Cooper Deford, Jul 22, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UCBvZiBEaHVtcA==?=

    Problem (Inserting records into sql server 2000 using asp.net)

    =?Utf-8?B?UCBvZiBEaHVtcA==?=, Aug 18, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    630
    =?Utf-8?B?UCBvZiBEaHVtcA==?=
    Aug 18, 2005
  2. Bhavesh
    Replies:
    0
    Views:
    422
    Bhavesh
    Jul 16, 2007
  3. Bhavesh
    Replies:
    5
    Views:
    628
    Bhavesh
    Jul 18, 2007
  4. Bhavesh
    Replies:
    1
    Views:
    450
    Bhavesh
    Jul 17, 2007
  5. Renie83
    Replies:
    1
    Views:
    175
    Kiran Math
    Jul 10, 2003
Loading...

Share This Page