Insert Array using Asp into Access DB

not quite sure what you're asking to do,are you trying to split up the string "Field1,Field2,Field3,Field4,Field5" into individual values or something else? please explain where your problem lies

Sorry, yes trying to split the fields into individual values so i can insert them into the DB as each separate line
Data.csv

simple enough, you can use the
Split() function:

flds = "Field1,Field2,Field3,Field4,Field5"
arrayOfFields = Split( flds, "," )

for each fld in arrayOfFields
     '-- the variable "fld" now contains each value of the array, process as you want
next

Hi Monty and thank you for that! :)

I have got them splitting out now, just have to figure how to put them into an INSERT statement. I know what I want to statement to be, just not sure how/where it lays in the grand scheme of things

			rcptID=Split(Request.Form("rcptID")&",",",")
			For Each x In rcptID
				Response.Write(x & "<br>")
			Next
			rcptdate=Split(Request.Form("rcptdate")&",",",")
			For Each x In rcptdate
				Response.Write(x & "<br>")
			Next
			accountID=Split(Request.Form("accountID")&",",",")
			For Each x In accountID
				Response.Write(x & "<br>")
			Next
			transID=Split(Request.Form("transID")&",",",")
			For Each x In transID
				Response.Write(x & "<br>")
			Next
			banknotes=Split(Request.Form("banknotes")&",",",")
			For Each x In banknotes
				Response.Write(x & "<br>")
			Next
			rcptnotes=Split(Request.Form("rcptnotes")&",",",")
			For Each x In rcptnotes
				Response.Write(x & "<br>")
			Next
			rcptamount=Split(Request.Form("rcptamount")&",",",")
			For Each x In rcptamount
				Response.Write(x & "<br>")
			Next
			If Request.Form("transID")>0 And Request.Form("accountID")="---" Then
				oConn.Execute("UPDATE accounts SET receipt_confirm WHERE receipt_ID="&Request.Form("transaction"))
			ElseIf Request.Form("transID")="---" And Len(Request.Form("accountID"))>0 Then
				oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_amount,receipt_notes,bank_notes) VALUES("&AccountID&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&Request.Form("rcptnotes")&"','"&Request.Form("banknotes")&"')")
			End If

Select allOpen in new window

can you provide an example of what you want the sql statement to look like?

Sorry it was at the bottom

			If Request.Form("transID")>0 And Request.Form("accountID")="---" Then
				oConn.Execute("UPDATE accounts SET receipt_confirm WHERE receipt_ID="&Request.Form("transaction"))
			ElseIf Request.Form("transID")="---" And Len(Request.Form("accountID"))>0 Then
				oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_amount,receipt_notes,bank_notes) VALUES("&AccountID&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&Request.Form("rcptnotes")&"','"&Request.Form("banknotes")&"')")
			End If

Select allOpen in new window

I saw that but was a bit confused on what you're trying to accomplish.

let me ask it this way - for each value in rcptID, is it all going into one insert statement or multiple ones? if one insert statement, what would the actual sql look like just for that one portion?

oh i can see where i have confused you, sorry.

rcptID & transID are the same, but will be cross referencing them against each other (the UPDATE statement)

everything below that is INSERTING

not sure if my IF statement is right at the moment (can be fixed later)

I suppose 1 thing at a time, let go the INSERT (with below values)

			rcptdate=Split(Request.Form("rcptdate")&",",",")
			For Each x In rcptdate
				Response.Write(x & "<br>")
			Next
			accountID=Split(Request.Form("accountID")&",",",")
			For Each x In accountID
				Response.Write(x & "<br>")
			Next
			banknotes=Split(Request.Form("banknotes")&",",",")
			For Each x In banknotes
				Response.Write(x & "<br>")
			Next
			rcptnotes=Split(Request.Form("rcptnotes")&",",",")
			For Each x In rcptnotes
				Response.Write(x & "<br>")
			Next
			rcptamount=Split(Request.Form("rcptamount")&",",",")
			For Each x In rcptamount
				Response.Write(x & "<br>")
			Next

			oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes) VALUES("&AccountID&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&Request.Form("rcptdate")&"',"&Request.Form("rcptamount")&",'"&Request.Form("rcptnotes")&"','"&Request.Form("banknotes")&"')")

Select allOpen in new window

you have the variable banknotes, which contains values posted from the form, and is in a comma delimited format, right? is each bank note value being inserted individually, or is it all one insert statement? if it's one statement, why do you want to do a split?

Each banknote value is its own insert (along with everything else on the same line, accountID, rcptnote, etc)

so you need to do multiple inserts then, correct?

that is correct

each insert will be the same fields but different value depending on the line we are inserting

will each form item have the same amount of values?

Hi Monty, yes each form will have the same amount of values, not same values

Hi Monty, working well now! thank you

Where do I put the IF statement? outside of For arrIndex or inside?

tried both but no luck, I might also assume the fields might be slightly different?

This is what I think is most logical

For arrIndex=0 to Ubound(rcptdate)-1
	If transID(arrIndex)="Dont Import" Then
' Do nothing
	ElseIf transID(arrIndex)="---" And Len(accountID(arrIndex))>0 Then
		Set AccountID=oConn.Execute("SELECT * FROM account_names WHERE account_name="&accountID(arrIndex))
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes) VALUES("&Account(arrIndex)&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"')")
	ElseIf transID(arrIndex)>0 And accountID(arrIndex)="Client" Then
		oConn.Execute("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnote(arrIndex)&"' WHERE receipt_ID="&rcptID(arrIndex))
	End If
Next

Select allOpen in new window

what does transID contain? is it another array of split values?

if it is, make sure you split it before the FOR statement

Hi Monty, yes I have split them prior (like the rest of the values)

Is the SQL statement below

Set Account=oConn.Execute("SELECT * FROM account_names WHERE account_name="&accountID(arrIndex))

Select allOpen in new window

correct within For arrIndex=0, etc and i guess the IF statement as well ?

it looks correct, assuming that array has the same number of values as the others.

when you say not working, can you be more specific. Remember, the more info you can give the better!

Yes of course! :)

ok, so I have updated the code (as there some errors, but now fixed), but still getting an error

transID=Split(Request.Form("transID")&",",",")
rcptdate=Split(Request.Form("rcptdate")&",",",")
accountID=Split(Request.Form("accountID")&",",",")
banknotes=Split(Request.Form("banknotes")&",",",")
rcptnotes=Split(Request.Form("rcptnotes")&",",",")
rcptamount=Split(Request.Form("rcptamount")&",",",")

For arrIndex=0 to Ubound(rcptdate)-1
	If accountID(arrIndex)="Dont Import" Then
' Do Nothing
		Response.Write("NOT imported transaction<br>")
	ElseIf accountID(arrIndex)="Client" And transID(arrIndex)>0 Then
		Response.Write("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"' WHERE receipt_ID="&transID(arrIndex))
		oConn.Execute("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"' WHERE receipt_ID="&transID(arrIndex))
		Response.Write("<br>updated client transaction<br>")
	ElseIf Len(accountID(arrIndex))>0 And transID(arrIndex)="---" Then
		Set Account=oConn.Execute("SELECT * FROM account_names WHERE account_name="&accountID(arrIndex))
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"')")
		Response.Write("<br>imported transaction<br>")
	End If
Next

Select allOpen in new window

The error comes here when it looks at the second slice of data:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'transID(...)'

/customer-new.asp, line 729

ElseIf accountID(arrIndex)="Client" And transID(arrIndex)>0 Then

Select allOpen in new window

The values are: accountID="General" and transID="---"

which from my understanding the IF statement shouldnt be picking it up?

oh! so that should be below the other ElseIf

let me try

hmm it bypassed the other ElseIf and went for the same one:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'transID(...)'

/customer-new.asp, line 734

If accountID(arrIndex)="Dont Import" Then
ElseIf Len(accountID(arrIndex))>0 And transID(arrIndex)="---" Then
ElseIf accountID(arrIndex)="Client" And transID(arrIndex)>0 Then <-- 734
End If

Select allOpen in new window

Fantastic that got rid of the error, however... the other ElseIf didnt pick up the values to INSERT them

Values - accountID="General" (which then go into a SELECT statement) & transID="---"

it picked up the value - accountID="Client" & transID="500"

please post the updated code

transID=Split(Request.Form("transID")&",",",")
rcptdate=Split(Request.Form("rcptdate")&",",",")
accountID=Split(Request.Form("accountID")&",",",")
banknotes=Split(Request.Form("banknotes")&",",",")
rcptnotes=Split(Request.Form("rcptnotes")&",",",")
rcptamount=Split(Request.Form("rcptamount")&",",",")

For arrIndex=0 to Ubound(rcptdate)-1
	If accountID(arrIndex)="Dont Import" Then
' Do Nothing
		Response.Write("NOT imported transaction<br>")
	ElseIf Len(accountID(arrIndex))>0 And transID(arrIndex)="---" Then
		Set Account=oConn.Execute("SELECT * FROM account_names WHERE account_name="&accountID(arrIndex))
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&Date()&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"')")
		Response.Write("<br>imported transaction<br>")
	ElseIf accountID(arrIndex)="Client" And IsNumeric(transID(arrIndex)) Then
		If transID(arrIndex)>0 Then
			Response.Write("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"' WHERE receipt_ID="&transID(arrIndex))
			oConn.Execute("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"' WHERE receipt_ID="&transID(arrIndex))
			Response.Write("<br>updated client transaction<br>")
		End If
	End If
Next

Select allOpen in new window

so you're never seeing this message written out?

Response.Write("<br>imported transaction<br>")

That is correct. Only seeing below (as im testing both)

Response.Write("<br>updated client transaction<br>")

try changing your FOR statement to:

For arrIndex=0 to Ubound(rcptdate)    <-- get rid of the -1

Sorry. sorry! that was wrong, I didnt change something :/

Did the same as before, just 'updated client transaction' no 'imported transacation'

so where are we at now?

back to update works and import get passed on

so everything is working?

No, the ElseIf where it supposed to import a transaction still is passed on.

we are back to this post ID: 41548429

I need to break from this as I have other work I need to focus on. I'll try to have a look later this afternoon or tonight.

In the mean time, double check your values, make sure "General" is def coming through. Do a Response.Write of each value in your IF block as your first line within the FOR loop, and make sure they match up

Not a problem at all Monty, always a great help

ill test it out and report back

Thanks again

Hi Monty have done a Response.Writes just underneath 'For arrIndex=0 to Ubound(rcptdate)'

I'd thought i'd try both Response.Writing accountID & transID below to see if ElseIf is picking up either

For arrIndex=0 to Ubound(rcptdate)
	Response.Write(accountID(arrIndex)&"<br>")
	Response.Write(transID(arrIndex)&"<br>")
	If accountID(arrIndex)="Dont Import" Then
' Do Nothing
		Response.Write("NOT imported transaction<br>")
	ElseIf Len(accountID(arrIndex))>0 And transID(arrIndex)="---" Then
		Set Account=oConn.Execute("SELECT * FROM account_names WHERE account_name="&accountID(arrIndex))
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		Response.Write("<br>imported transaction<br>")
' test					
	ElseIf accountID(arrIndex)="Client" And transID(arrIndex)="---" Then
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(2,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(2,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		Response.Write("<br>imported transaction<br>")
	ElseIf accountID(arrIndex)="General" And transID(arrIndex)="---" Then
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(1,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(1,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		Response.Write("<br>imported transaction<br>")
	ElseIf accountID(arrIndex)="Tax" And transID(arrIndex)="---" Then
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(3,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(3,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		Response.Write("<br>imported transaction<br>")
	ElseIf accountID(arrIndex)="Personal" And transID(arrIndex)="---" Then
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(4,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES(4,'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&Time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		Response.Write("<br>imported transaction<br>")
' end test					
	ElseIf accountID(arrIndex)="Client" And IsNumeric(transID(arrIndex)) Then
		Response.Write("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"',recon_date='"&date()&"' WHERE receipt_ID="&transID(arrIndex))
		oConn.Execute("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"',recon_date='"&date()&"' WHERE receipt_ID="&transID(arrIndex))
		Response.Write("<br>updated client transaction<br>")
	End If
Next

Select allOpen in new window

So Response.Writing accountID & transID

outcome:

Client
500
UPDATE accounts SET receipt_confirm='8/04/2016',receipt_notes='Maz',bank_notes='RAIL PLUS MELBOURNE VI MELBOURNE AU',recon_date='14/04/2016' WHERE receipt_ID=500
updated client transaction
General
---
Dont Import
---
Dont Import
---
Dont Import
---
Dont Import
---
Dont Import
---

So it seems accountID(arrIndex) & transID(arrIndex) is outputting correctly, im thinking if the original ElseIf accountID='Client' & numeric transID gets picked up, then the issue may lie with transID being '---', what do you think?

Hi Monty, if I remove from 'test to 'end test, that particular ElseIf is in the place where you have asked

the Trim, worked amazingly (never knew about it and going back to the Form code, the space isnt there...) and now is picking up the EsleIf statement, thank you

However a new dilema has risen...

error '80020009'
/customer-new.asp, line 736

		Set Account=oConn.Execute("SELECT * FROM account_names WHERE account_name='"&accountID(arrIndex)&"'")
736 -->	Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		Response.Write("<br>imported transaction<br>")

Select allOpen in new window

I have response.written all values, Account("account_ID") is the only one that has issues, ideas?

We are almost there! :D Thanks heaps!

Thank you, have updated as you have requested. reading through, I am assuming this has nothing to do with the error, correct? As it is still there

updated code:

transID=Split(Request.Form("transID"),",")
rcptdate=Split(Request.Form("rcptdate"),",")
accountID=Split(Request.Form("accountID"),",")
banknotes=Split(Request.Form("banknotes"),",")
rcptnotes=Split(Request.Form("rcptnotes"),",")
rcptamount=Split(Request.Form("rcptamount"),",")

For arrIndex=0 to Ubound(rcptdate)
	Response.Write(accountID(arrIndex)&"<br>")
	Response.Write(transID(arrIndex)&"<br>")
	If accountID(arrIndex)="Dont Import" Then
' Do Nothing
		Response.Write("NOT imported transaction<br>")
	ElseIf accountID(arrIndex)<>"" And Trim(transID(arrIndex))="---" Then
		Set Account=oConn.Execute("SELECT * FROM account_names WHERE account_name='"&accountID(arrIndex)&"'")
		Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
		Response.Write("<br>imported transaction<br>")
	ElseIf accountID(arrIndex)="Client" And IsNumeric(transID(arrIndex)) Then
		Response.Write("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"',recon_date='"&date()&"' WHERE receipt_ID="&transID(arrIndex))
		oConn.Execute("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"',recon_date='"&date()&"' WHERE receipt_ID="&transID(arrIndex))
		Response.Write("<br>updated client transaction<br>")
	End If
Next

Select allOpen in new window

Cheers

oh, sorry do you mean put the NEW If statement between 'For...' and 'If...' ? and at end between 'End If' and 'Next' ?

exactly

OK! all updated :)

error is still there...

error '80020009'
/customer-new.asp, line 736

Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")

Select allOpen in new window

"&Account("account_ID")&" - being the issue

Fixed and working!!

Needed to Trim(accountID(arrIndex)) as well, was strange that 'Client' didnt need trimming and the rest did..

But it works and I am very very happy! :)

You do amazing work Monty! Big ups!

final working Code:

transID=Split(Request.Form("transID"),",")
rcptdate=Split(Request.Form("rcptdate"),",")
accountID=Split(Request.Form("accountID"),",")
banknotes=Split(Request.Form("banknotes"),",")
rcptnotes=Split(Request.Form("rcptnotes"),",")
rcptamount=Split(Request.Form("rcptamount"),",")

For arrIndex=0 to Ubound(rcptdate)
	Response.Write(accountID(arrIndex)&"<br>")
	Response.Write(transID(arrIndex)&"<br>")
	If accountID(arrIndex)<>"" Then
		If Trim(accountID(arrIndex))="Dont Import" Then
' Do Nothing
			Response.Write("NOT imported transaction<br>")
		ElseIf Len(accountID(arrIndex))>0 And Trim(transID(arrIndex))="---" Then
			Response.Write "SELECT * FROM account_names WHERE account_name='"&Trim(accountID(arrIndex))&"'"
			Set Account=oConn.Execute("SELECT * FROM account_names WHERE account_name='"&Trim(accountID(arrIndex))&"'")
			Response.Write("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
			oConn.Execute("INSERT INTO accounts(account_ID,office_pseudo,cons_ID,receipt_date,receipt_time,receipt_confirm,receipt_amount,receipt_notes,bank_notes,recon_date) VALUES("&Account("account_ID")&",'GPT',"&Session("ID")&",'"&rcptdate(arrIndex)&"','"&time()&"','"&rcptdate(arrIndex)&"',"&rcptamount(arrIndex)&",'"&rcptnotes(arrIndex)&"','"&banknotes(arrIndex)&"','"&date()&"')")
			Response.Write("<br>imported transaction<br>")
		ElseIf Trim(accountID(arrIndex))="Client" And IsNumeric(Trim(transID(arrIndex))) Then
			Response.Write("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"',recon_date='"&date()&"' WHERE receipt_ID="&transID(arrIndex))
			oConn.Execute("UPDATE accounts SET receipt_confirm='"&rcptdate(arrIndex)&"',receipt_notes='"&rcptnotes(arrIndex)&"',bank_notes='"&banknotes(arrIndex)&"',recon_date='"&date()&"' WHERE receipt_ID="&transID(arrIndex))
			Response.Write("<br>updated client transaction<br>")
		End If
	End If
Next

Select allOpen in new window

glad I could help :)

Always a pleasure! :)