Hello VB experts,

Recently I am facing a problem for insertion data in MS access database using ADO from VB...Its like:

my table is like

Id name Dept
-- ----- -----

Now from VB i have 3 array holding value like

i[10] = { 10 20 30 40 } ' For ID
n[10] = { 'A' 'B 'C' 'D' } ' For Name
d[10] = { 'CSE' 'BBA' 'EEE' 'MBA'
}

now i want to insert data for output

Id name Dept
-- ----- -----
10 A CSE
20 B BBA
30 C EEE
40 D MBA

Now i tried it for ADO's recordset .ADDNEW and .Update properties but failed.
Can any body help me any sQL command or any other tricks..Plz i m facing a challenging Problem in it. Plz help me as quick as possible.

Thnaks a Lot.

mahfuz

please note this is but a guide. It assumes that all arrays are of equal length. While ID is not a computational field, you have specified the data to be numeric, and not a text number. "n" and "d" are character strings.


dim xConx as ADODB.connection
dim iCnt as integer
dim strSQL as string
dim maxArray as integer

<make ADODB connection>

on error goto INSERTERROR
for iCnt = 0 to maxArrary -1 step 1
strSQL = "INSERT INTO <tablename> (id, name, dept) VALUES " & _
"(" & i(cnt) & ",'" & n(icnt) & "','" & d(icnt) & "')"
xConx.beginTrans
xConx.execute strSQL
xConx.committrans
next
<drop connection>
on error goto 0
exit sub

INSERTERROR:
xConx.rollbacktrans
<drop connection>
on error goto 0
exit sub