www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: INSERT the results of an array into a table

  1. #1
    Join Date
    Apr 2010
    Posts
    57

    INSERT the results of an array into a table

    Hi all:

    I cannot figure out why this is won't work!!!!

    I have an array named CartArray, let's call it a list of items, quantity and prices such as

    2 Widgets 4.00
    4 Bongos 3.95

    I want to insert CartArray into my MSSQL DB. I am trying:

    Insert Into MyTable (Invoice) VALUE ('"&CartArray&"')

    Nothing appears in the table. No error message.

    Any help appreciated!

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    Code:
     
    Insert Into MyTable (Invoice) VALUE ('"&CartArray&"')
    In ASP, can you display on the screen what the above query evaluates to in your code before you actually run it?

    If so, post what the query evaluates to and hopefully things will become clearer.

  3. #3
    Join Date
    Apr 2010
    Posts
    57
    Yes the array displays. If I write <% CartArray %> in the code the item list appears.

    But as I mentioned, entering CartArray into the Insert statement has no effect.

    Am I properly coding CartArray in the Values side of the insert statement?

  4. #4
    Join Date
    Mar 2010
    Posts
    2,803
    Quote Originally Posted by Square1 View Post
    Yes the array displays. If I write <&#37; CartArray %> in the code the item list appears.

    But as I mentioned, entering CartArray into the Insert statement has no effect.

    Am I properly coding CartArray in the Values side of the insert statement?
    I suspect not, but without you posting what the query actually evaluates to in your code as I requested I cannot say for sure.

    But in general, if I understand you correctly, your query should evaluate to something like:

    Code:
     
    insert into myTable (Invoice) VALUES ('some_single_value');
    But you appear to have a multidimensional array and so I suspect your query needs to be edited so that the appropriate array element value from your cart array is inserted into your query. I imagine you will need some kind of loop to insert your cart array contents into your database.

    But I know next to nothing about ASP (I went to the PHP school), so I can help you hopefully get your query right but I can't help you with the ASP db commands to actually run the query.
    Last edited by tirna; 06-27-2010 at 09:25 PM.

  5. #5
    Join Date
    Apr 2010
    Posts
    57
    Thank you tirna. I misunderstood yourt request. Below is the source code for the how the array is posting:

    Code:
    	<tr><td style='width:80%;'>2&nbsp;Super Listing:</td><td style='width:20%; text-align:right;'>$80.00</td></tr>	
    	
     
    	<tr><td style='width:80%;'>5&nbsp;Email Giveaway:</td><td style='width:20%; text-align:right;'>$500.00</td></tr>

  6. #6
    Join Date
    Mar 2010
    Posts
    2,803
    I think one of us has our wires seriously crossed and it could very well be me, so let's go back to square 1 - no pun intended

    In your first post you said, if I understand you correctly, that you were having problems inserting the contents of your cart array into your db using the query

    Code:
    Insert Into MyTable (Invoice) VALUE ('"&CartArray&"')
    If this is so, then I was suggesting that maybe your query is not evaluating correctly with your cart values, since your cart appears to be a multidimensional array, and I would need to see what your query actually evaluates to in your code before it is actually run.

    For example: (and this is using php code, but I assume there is an equivalent way in ASP, buy I don't know ASP)

    PHP Code:
    //this is the query about to be run on the db
    $query 'insert into myTable (invoice) values ('invoice_0001');
     
    echo $query;  //this line prints the '
    evaluated' query to your screen just before it is about to be run
     
    die();   //this stops the php script from executing any further
     
    @mysql_query($query, $conn);  //this runs the query in the db 
    What imo we need to first check, is the output to the screen of the ASP command that is equivalent to echo $query; above. This will show us exactly what the query is that is about to be run on your db and you can then check if the syntax and structure is correct.
    Last edited by tirna; 06-27-2010 at 10:15 PM.

  7. #7
    Join Date
    Apr 2010
    Posts
    57
    LOL! OK now I understand! Let me explain a bit more then.

    The array is in a Function called fCartArray. When I posted the query as you recommended I get the following:

    sQry="INSERT INTO SHL_SHP (Items) VALUES (fCartArray)" returns the following error:


    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]The name "fCartArray" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted


    sQry="INSERT INTO SHL_SHP (Items) VALUES ("&fCartArray&")" returns the following error:


    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.


    I hope this make sense.

  8. #8
    Join Date
    Mar 2010
    Posts
    2,803
    ok, at least we are heading in the right direction now

    So it appears to me that your query is not evaluating correctly in your code prior to it being run as shown by the error mesage saying
    .......Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted
    which imo means you need a single value in the values side of the query for each column you are inserting into.

    So basically your query needs to evaluate to something like

    Code:
     
    insert into myTable (invoice) values ('a_value_for_invoice');
    Unfortunately I cannot help you transfer the data in your multidimensional cart array into your query because as I mentioned earlier, I do not know ASP. But I strongly suspect you will need some kind of loop that loops through your cart array and inserts the current value in the array element into your query and then runs the query to do the insertion.

    Hopefully an ASP guru will come by and help you with that side of things. But at least now, you can be reasonably sure the problem is with the way your insert query is being evaluated in your code.

  9. #9
    Join Date
    Apr 2010
    Posts
    57
    Thank you very much for your time and patience!!!!

  10. #10
    Join Date
    Mar 2010
    Posts
    2,803
    you're welcome

    hopefully an ASP guru will help you with the rest.

  11. #11
    Join Date
    Apr 2010
    Posts
    57
    The problem was you cannot run an array in an INSERT statement. So I manually (no array) set up a variable to make sure my code wasn't throwing things off.

    Items = Items & "<tr><td style=""width:80&#37;"">" & Request.Form("Product1") & "</td></td>"

    I then ran that statement through the array loop.

    As I have this running as function I also had to use

    Response.write Items

    NOW I am able to enter '"&Items&"' in my INSERT!

  12. #12
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    I will be glad to help.. know that

    Items = Items & "blah"

    Is NOT an array... you will need to split your array and insert the values into the proper table fields.

    Please normalize your tables before proceeding as you never want to write delimited values into a field.

    You want to first split your array, then add your inserts.

    This is how something like you are doing should look.

    Code:
    for i = 0 to UBound(MyArray)
     sql = "insert into Invoices(InvoiceID,Quantity,Product,Price) Values("&InvoiceID&","&MyArray(0,i)&","&MyArray(1,i)&","&MyArray(2,i)&")"
    
    next
    Always use
    Code:
    response.write "my query = " & sql
    so that you know what your statement is producing.

  13. #13
    Join Date
    Apr 2010
    Posts
    57
    Thank you YamahaRuss.

    What is the benefit of putting each separate variable into a separate DB column? I would have dozens of additional columns that way. I don't follow you on this.

  14. #14
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    Why dozens? I only see three.

  15. #15
    Join Date
    Apr 2010
    Posts
    57
    LOL! Just to keep things simple I listed 3.

    I have 18 products. So it is 3 fields for each product. 54 columns.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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



Recent Articles