Multiple insert using connection object


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Multiple insert using connection object

  1. #1
    Hamed Guest

    Multiple insert using connection object

    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...







  2. #2
    Bonnie Berent Guest

    Re: Multiple insert using connection object

    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...
    >
    >
    >
    >
    >
    >




  3. #3
    Hamed Guest

    Re: Multiple insert using connection object

    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...
    | >
    | >
    | >
    | >
    | >
    | >
    |
    |



  4. #4
    Bonnie Berent Guest

    Re: Multiple insert using connection object

    >> 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...
    > | >
    > | >
    > | >
    > | >
    > | >
    > | >
    > |
    > |
    >
    >




  5. #5
    Hamed Guest

    Re: Multiple insert using connection object

    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




  6. #6
    Bonnie Berent Guest

    Re: Multiple insert using connection object

    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
    >
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center