[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get data from an array into an Access table using a sql statement

Posted on 2006-05-02
4
Medium Priority
?
217 Views
Last Modified: 2010-04-23
Hi there,

I am creating a temp table in Access through my code, I then want to add values from an array into the temp table.  Can I somehow include the array in the INSERT statement so that the values from the array are added to my new table?

myArray = A fixed lenght single dimension array containing 13 date items.
tempWkArray = A table with one column called 'WeekDate'

Here is the code I have at the moment, I was experimenting with the code to add values manually:

        Dim str As String = Nothing
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand

        str = "CREATE TABLE tempWkArray (WeekDate Date)"
        cmd.Connection = conTRS
        cmd.CommandText = str
        conTRS.Open()
        cmd.ExecuteNonQuery()


        str = "INSERT INTO tempWkArray (WeekDate) VALUES (01/01/2006)"   'I would like to use my array here
        cmd.CommandText = str
        cmd.ExecuteNonQuery()
       
        conTRS.Close()  

Any ideas?
(I am using VB.NET Professional 2005 with Microsoft Access database as my backend)
0
Comment
Question by:Cherryex
  • 3
4 Comments
 
LVL 7

Expert Comment

by:pauljk1619
ID: 16587759
Use this for your array loop.

dim x as int
x=0
while x < myArray.Ubound
         str = "INSERT INTO tempWkArray (WeekDate) VALUES (" & myArray(x) & ")"   'I would like to use my array here
        cmd.CommandText = str
        cmd.ExecuteNonQuery()
       x = x + 1
wend
   
     
0
 
LVL 7

Expert Comment

by:pauljk1619
ID: 16587787
Sorry...  in the while statement you might need...

while x < myArray.Ubound + 1

To go through the last item in your array.
0
 
LVL 7

Accepted Solution

by:
pauljk1619 earned 2000 total points
ID: 16587802
Let me just start over...    I'm always mixing VB with VB.Net with C#!  

dim x as int
x=0
while x < myArray.GetUpperBound(0) + 1
        str = "INSERT INTO tempWkArray (WeekDate) VALUES (" & myArray(x) & ")"   'I would like to use my array here
        cmd.CommandText = str
        cmd.ExecuteNonQuery()
       x = x + 1
wend
0
 

Author Comment

by:Cherryex
ID: 16594255
Paul,

It worked great, the only thing I removed was the +1 on the GetUpperBound for the array.  It seems to loop through all the records without it.

Thanks for the help :-)

P.S.
I wish I knew three languages to get mixed up with!

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Loops Section Overview
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month19 days, 10 hours left to enroll

873 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