Storing array values into an Access database table from a program
Hi, I’m trying to write a program that stores the values in an array into a database table. The name of the database is Company.mdb and the table that I want to store values is called Employee.
Below is the procedure that is supposed to perform the task. The problem is it doesn’t store the array(strEmployeeRecord) values into the table. Nothing happens. Can someone please help me with this problem? I’m using Visual Studio 2005. Thanks in advance.
Software/Hardware used:
MA Visual Studio 2005
Private Sub StoreInTable()
Dim strSQL As String = "INSERT INTO Employee FROM ARRAY strEmployeeRecord "
'strPath provides the database type and path of the Company database
Dim strPath As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = D:Company.mdb"
Dim odaRoute As New OleDb.OleDbDataAdapter(strSQL, strPath)
odaRoute.Dispose()
End Sub
Software/Hardware used:
MA Visual Studio 2005
ASKED:
April 23, 2010 9:50 AM
UPDATED:
April 26, 2010 2:52 PM
Answer Wiki:
First, this syntax:
<pre>INSERT INTO <table name> FROM ARRAY <array name></pre>
Is not standard SQL, and it would only work (I think) on Visual Fox, and I guess this is Visual Basic (you didn't mention the language being used).
Second, to use this constructor of the OleDbDataAdapter class you have to use a SELECT statement. An insert statement can be specified for the InsertCommand property, but it won't be executed when instantiating the object.
-CarlosDL
---------------------------
First thing I noticed was that the target database pointer had no slash after the D: in it. Indicating a full path, not an offset of the currently active directory on the D; drive.
This could be because the slash gets lost when you enter it into this forum text box.
Reason I mention it is because, you could end up with a database copied to the subdirectory of the active directory on the D drive, or the routine will not work because it can not find the table pointed to and will throw up an error. Do you catch any errors produced? (You never mentioned error reports)
Is nothing happening because the table has not been opened?
I guess you open the table at the beginning of the whole application. So, does it remain open, or does another sub close it before this sub can write to it?
as a starting point, I'd suggest opening the table, do the write and then close the table all in the same sub.
-----------
To see all answers submitted to the Answer Wiki: View Answer History.
Chippy, tables don’t need to be ‘opened’. You open connections to the database, not tables.