Question Array to database question

[Kasteel7]

Hi

I have an array which can hold up to 20 entries. The problem is that my array will never contain the same amount of information in it. Example: sometimes the array will be filled with 4 entries and sometimes with 20. I want to output whatever is in my array to my database.

How would I do it if I don't know how full my array is?

Any help appreciated.

Here is the code I use for writing to the database:

Dim Conn As New ADODB.Connection
        Dim Rec As New ADODB.Recordset
        Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\Testdb.mdb"
        Conn.Open()
        Rec.Open("SELECT * FROM Table2", Conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
        Rec.AddNew()
        Rec.Fields("Question").Value = myarray --------DONT KNOW WHAT TO DO HERE...
        
Rec.Fields("Date and Time").Value = Date.Now
        Rec.Update()
        Rec.Close()
        Conn.Close()

 

[jmcilhinney]

First up, I strongly suggest that you learn how to use ADO.NET and ditch ADO. If you're going to use VB.NET then you should use it and not cling to VB6 data access technology.

As for the question, you can't save an array to a database. It's up to you to either save each element in the array to a separate record or else combine all the elements into a single value and then save that.

 

[cjard]

Follow jmc's advice, dump the old style data access..

Then, basically.. Dump the idea of an array too.. Instead use a DataTable with 20 columns as your data container. If the elements are to be written into the db above one another rather than side by side, use 20 rows of 1 column

 

[Kasteel7]

Thanks. I will switch to ADO.NET.

FYI

It is possible to write an Array to a database. (From the help of a friend)

For i = 0 To UBound(myarray)
sInfo = ""
sInfo = myarray(i)
If Not (sInfo = "") Then
Rec.AddNew()
Rec.Fields("Question").Value = sInfo
Rec.Fields("Date and Time").Value = Date.Now
Rec.Update()
End If
Next

 

[jmcilhinney]

It is possible to write an Array to a database.

I've already answered that question:

As for the question, you can't save an array to a database. It's up to you to either save each element in the array to a separate record or else combine all the elements into a single value and then save that.

Just note that I should have more correctly said "field" rather than "record", although often those fields would be in separate records anyway.

 

[cjard]

It is possible to write an Array to a database.

Indeed, and in your example of 10 lines of code, in ADO.NET would be:

myTableAdapter.Update(myDataTable)