Multiple insert using connection object

[Hamed]

I have used the following code to insert multiple records to a table but get the OLE DB error for the second insert. I can only
insert once. What's the problem ? Is there any better solution with lower overhead and better performance?
------------------------------------------------------------------------------
Set oConn = server.createobject("ADODB.connection")

strConn = "Provider=vfpoledb;Data Source=E:\WorkArea\Lottary\DATA\Lottary.dbc; Mode=ReadWrite|Share Deny None;Collating
Sequence=MACHINE;Password=''"

oConn.Open strConn

'friends_data is a text area input field that has mutiple lines
aFriendsData = Split(Request.Form("friends_data"),Chr(13))

For i=LBound(aFriendsData) To UBound(aFriendsData)-1
cInsert = "INSERT INTO friend(ForeignKey,email) VALUES(" & cKey _
& ",'" & aFriendsData(i) & "')"
oConn.Execute(cInsert)
oConn.Close
oConn.Open strConn
Next

------------------------------------------------------------------------------
P.S. I have even released the connection object and created again in the loop but no promotion occured. Any help is appriciated
Thanks in advance
Hamed...

[Bonnie Berent]

Hamed,

Try taking the Close and Open out of the loop ... put them after the end of
the loop.

~~Bonnie


"Hamed" <hamedarab@hotmail.com> wrote in message news:3d841585@10.1.10.29...
> I have used the following code to insert multiple records to a table but
get the OLE DB error for the second insert. I can only
> insert once. What's the problem ? Is there any better solution with lower
overhead and better performance?
> --------------------------------------------------------------------------
----
> Set oConn = server.createobject("ADODB.connection")
>
> strConn = "Provider=vfpoledb;Data
Source=E:\WorkArea\Lottary\DATA\Lottary.dbc; Mode=ReadWrite|Share Deny
None;Collating
> Sequence=MACHINE;Password=''"
>
> oConn.Open strConn
>
> 'friends_data is a text area input field that has mutiple lines
> aFriendsData = Split(Request.Form("friends_data"),Chr(13))
>
> For i=LBound(aFriendsData) To UBound(aFriendsData)-1
> cInsert = "INSERT INTO friend(ForeignKey,email) VALUES(" & cKey _
> & ",'" & aFriendsData(i) & "')"
> oConn.Execute(cInsert)
> oConn.Close
> oConn.Open strConn
> Next
>
> --------------------------------------------------------------------------
----
> P.S. I have even released the connection object and created again in the
loop but no promotion occured. Any help is appriciated
> Thanks in advance
> Hamed...
>
>
>
>
>
>

[Hamed]

I tryed it. The result doesn' change


"Bonnie Berent" <bonnieb@profitware-online.com> wrote in message news:3d84982a$1@10.1.10.29...
| Hamed,
|
| Try taking the Close and Open out of the loop ... put them after the end of
| the loop.
|
| ~~Bonnie
|
|
| "Hamed" <hamedarab@hotmail.com> wrote in message news:3d841585@10.1.10.29...
| > I have used the following code to insert multiple records to a table but
| get the OLE DB error for the second insert. I can only
| > insert once. What's the problem ? Is there any better solution with lower
| overhead and better performance?
| > --------------------------------------------------------------------------
| ----
| > Set oConn = server.createobject("ADODB.connection")
| >
| > strConn = "Provider=vfpoledb;Data
| Source=E:\WorkArea\Lottary\DATA\Lottary.dbc; Mode=ReadWrite|Share Deny
| None;Collating
| > Sequence=MACHINE;Password=''"
| >
| > oConn.Open strConn
| >
| > 'friends_data is a text area input field that has mutiple lines
| > aFriendsData = Split(Request.Form("friends_data"),Chr(13))
| >
| > For i=LBound(aFriendsData) To UBound(aFriendsData)-1
| > cInsert = "INSERT INTO friend(ForeignKey,email) VALUES(" & cKey _
| > & ",'" & aFriendsData(i) & "')"
| > oConn.Execute(cInsert)
| > oConn.Close
| > oConn.Open strConn
| > Next
| >
| > --------------------------------------------------------------------------
| ----
| > P.S. I have even released the connection object and created again in the
| loop but no promotion occured. Any help is appriciated
| > Thanks in advance
| > Hamed...
| >
| >
| >
| >
| >
| >
|
|

[Bonnie Berent]

>> I have used the following code to insert multiple records to a table but
>> get the OLE DB error for the second insert.


What is the error message that you're getting?

--


~~Bonnie


"Hamed" <hamedarab@hotmail.com> wrote in message news:3d8565f9@10.1.10.29...
> I tryed it. The result doesn' change
>
>
> "Bonnie Berent" <bonnieb@profitware-online.com> wrote in message
news:3d84982a$1@10.1.10.29...
> | Hamed,
> |
> | Try taking the Close and Open out of the loop ... put them after the end
of
> | the loop.
> |
> | ~~Bonnie
> |
> |
> | "Hamed" <hamedarab@hotmail.com> wrote in message
news:3d841585@10.1.10.29...
> | > I have used the following code to insert multiple records to a table
but
> | get the OLE DB error for the second insert. I can only
> | > insert once. What's the problem ? Is there any better solution with
lower
> | overhead and better performance?
> |
> --------------------------------------------------------------------------
> | ----
> | > Set oConn = server.createobject("ADODB.connection")
> | >
> | > strConn = "Provider=vfpoledb;Data
> | Source=E:\WorkArea\Lottary\DATA\Lottary.dbc; Mode=ReadWrite|Share Deny
> | None;Collating
> | > Sequence=MACHINE;Password=''"
> | >
> | > oConn.Open strConn
> | >
> | > 'friends_data is a text area input field that has mutiple lines
> | > aFriendsData = Split(Request.Form("friends_data"),Chr(13))
> | >
> | > For i=LBound(aFriendsData) To UBound(aFriendsData)-1
> | > cInsert = "INSERT INTO friend(ForeignKey,email) VALUES(" & cKey _
> | > & ",'" & aFriendsData(i) & "')"
> | > oConn.Execute(cInsert)
> | > oConn.Close
> | > oConn.Open strConn
> | > Next
> | >
> |
> --------------------------------------------------------------------------
> | ----
> | > P.S. I have even released the connection object and created again in
the
> | loop but no promotion occured. Any help is appriciated
> | > Thanks in advance
> | > Hamed...
> | >
> | >
> | >
> | >
> | >
> | >
> |
> |
>
>

[Hamed]

I sent the following two strings to ADO within a loop

INSERT INTO friend (ForeignKey,email) VALUES (40,'name1@domain.com')
INSERT INTO friend (ForeignKey,email) VALUES (40,' name2@domain.com')

I got the folowing error for the second run of the loop. The first row is inserted correctly.

Error in insert: Command contains unrecognized phrase/keyword.
Source: Microsoft OLE DB Provider for Visual FoxPro

[Bonnie Berent]

Hamed,

OK, in your first post you posted the following:

>For i=LBound(aFriendsData) To UBound(aFriendsData)-1
> cInsert = "INSERT INTO friend(ForeignKey,email) VALUES(" & cKey _
> & ",'" & aFriendsData(i) & "')"
> oConn.Execute(cInsert)

I don't know VB all that well (I'm assuming this is VB code), but the only
thing that I can think of is that something is not right with your
aFriendsData(i) ... maybe you've got the UBound() wrong or something (again,
I'm not familiar with VF).

In this last post you have:

> INSERT INTO friend (ForeignKey,email) VALUES (40,'name1@domain.com')
> INSERT INTO friend (ForeignKey,email) VALUES (40,' name2@domain.com')

Have you actually tried it with two INSERT statements like this (in a loop)?
And if so, does it work? I still think something is wrong with the variables
inside the loop in your original code, not in the INSERT itself (that should
work just fine)


~~Bonnie


"Hamed" <hamedarab@hotmail.com> wrote in message news:3d86c240@10.1.10.29...
> I sent the following two strings to ADO within a loop
>
> INSERT INTO friend (ForeignKey,email) VALUES (40,'name1@domain.com')
> INSERT INTO friend (ForeignKey,email) VALUES (40,' name2@domain.com')
>
> I got the folowing error for the second run of the loop. The first row is
inserted correctly.
>
> Error in insert: Command contains unrecognized phrase/keyword.
> Source: Microsoft OLE DB Provider for Visual FoxPro
>
>
>