Textfield - Multi Field insert

Posted on 2009-02-20
Last Modified: 2012-05-06
Hello All;

  Got something that i have been wondering how to do for several years now.
(When it comes to me, I think about how it could be done)

have it to where each line does a break. (String)
And then you look at each break in the textfield, to determin how many strings you have.
You then have an INSERT Statement that will insert each line (String) into indivisual fields.
(Multi Field insert statement)

To break it down.
Take a continues string from a Textfield
Break it down to indivisual strings (Per row)
And insert indivisual records into a Table.

Hope that it is clear.
Just something that I have been wondering about for a while.
What made me thing about it was that I have an Old Perl script
That uses a Textfield and for each row, it inserts into the MySQL database.
If it can be done in Perl/MySQL
It should be able to be done in ASP/Access (or) (SQL Server)
(I am using Access)

have a good one all.
Question by:Wayne Barron
    LVL 15

    Expert Comment

    Do you mean line by line? I would just split the string by vbCrLf.

    strArray = Split(txtBoxText, vbCrLf)

    That will put the values into an array, so
    strArray(0) = first line
    strArray(1) = second line

    Then just loop throw the array and insert each string.
    LVL 30

    Author Comment

    by:Wayne Barron
    I have read something about this before.
    Would you have some resources on this, or possibly could you make up a souce example?

    Thanks AI
    You are actually the first person that has said that it is possible.
    The last time that I posted this same topic years ago, I was told that
    You could not take a continues string and split it.
    LVL 15

    Accepted Solution

    Well it's not actually a continuous string, it would be split up by carriage returns.

    ASP would see it as
    first line#10#13second line#10#13third line#10#13fourth line

    To loop through an array and insert the values you could do something like this:

    set conn=Server.CreateObject("ADODB.Connection")
    conn.Open "c:/webdata/myaccessfile.mdb"
    Dim strArray = Split(Request.Form("MyTextField"), vbCrLf)
    Dim arrValue
    For Each arrValue In strArray
       Dim sql
       sql = "INSERT INTO myTable (Column1) VALUES ('" & arrValue & "')"

    Open in new window

    LVL 30

    Author Comment

    by:Wayne Barron
    I am getting this error

    Microsoft VBScript compilation (0x800A0401)
    Expected end of statement
    /Test/EE/Q_24163759/Insert.asp, line 16, column 13
    Dim strArray = Split(Request.Form("MyTextField"), vbCrLf)
    LVL 15

    Expert Comment

    Oh, I'm thinking in, just split it into two lines.

    Dim strArray
    strArray = Split(Request.Form("MyTextField"), vbCrLf)
    LVL 30

    Author Comment

    by:Wayne Barron

    I have thought about venturing into ASP.NET
    But I am just know getting to where I can do a lot of things in ASP Classic.
    So, I will wait until there is something that I want to do, that can only be done in .NET.
    LVL 30

    Author Comment

    by:Wayne Barron
    I'll be darn.

    Worked like a charm.
    You did what others said could not be done.
    That was so simple. You have just done something that I have wanted to do for years in ASP.
    Proven that ASP is just as powerful as PERL

    Thank you so very, very much.

    LVL 30

    Author Closing Comment

    by:Wayne Barron
    You Rock BIG TIME.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now