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

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)
CherryexAsked:
Who is Participating?
 
pauljk1619Connect With a Mentor Commented:
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
 
pauljk1619Commented:
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
 
pauljk1619Commented:
Sorry...  in the while statement you might need...

while x < myArray.Ubound + 1

To go through the last item in your array.
0
 
CherryexAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.