Need to split column full of data  and insert to new table?

Posted on 2007-12-06
Last Modified: 2013-11-26
I try this code but not working pls help me out.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class Form1
    Dim InsrSql As String
    Dim RPID As String
    Dim tempStr As String
    Dim Bname() As String
    Dim words() As String
    Dim i As Integer
    Dim pos As String
    Dim s As String

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Sample data in Authors
        's = "Xiao, J & Zhang, Y & Fuller, A & Xiaohua, J"

        Dim conn As New ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim SqlStr As String
        conn.Open(" Provider=SQLOLEDB; server=PQDEV1; Initial Catalog=ndev; User ID=*****;Password=****;")
        SqlStr = "SELECT  USQ_RPID ,Authors FROM Author "
        rs = conn.Execute(SqlStr, , )

        While Not rs.EOF

            RPID = rs("USQ_RPID")
            s = rs("Authors")
            words = Split(s, "&")

            For i = 0 To UBound(words)
                pos = CStr(i + 1)
                tempStr = words(i)
                Bname = Split(tempStr, ",")

                If Len(tempStr) > 0 Then

                    'MsgBox("Fname -" + Bname(0) + " L int -" + Bname(1) + " Pos -" + CStr(1 + i))

                    InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0) & "', '" & Bname(1) & "', '" & pos & "'"
                    ' rs = conn.Execute(InsrSql, , )

                End If
        End While
    End Sub

End Class
Question by:noelroshan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 25

Expert Comment

ID: 20424769
missing end )

                    InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0) & "', '" & Bname(1) & "', '" & pos & "')"

Author Comment

ID: 20424897
s = rs("Authors").Value is saying error Conversion from type 'DBNull' to type 'String' is not valid
LVL 25

Expert Comment

ID: 20424906
s = Convert.ToString(rs("Authors").Value)

However, instead of NULL in the db field, you will end up with '' (empty string)
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Author Comment

ID: 20425027

Error Operation is not allowed when the object is closed.
LVL 25

Expert Comment

ID: 20425042

rs = conn.Execute(InsrSql, , )


conn.Execute(InsrSql, , )

otherwise you're tramping through the previous rs= asignment

Author Comment

ID: 20425090
what is that mean "Line 1: Incorrect syntax near 'Brien'.""
LVL 25

Expert Comment

ID: 20425127
InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0).Replace("'","''") & "', '" & Bname(1).Replace("'","''") & "', '" & pos & "'"

Author Comment

ID: 20425273
InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0).Replace("'","''") & "', '" & Bname(1).Replace("'","''") & "', '" & pos & "'"

Error :Index was outside the bounds of the array.
LVL 25

Accepted Solution

imitchie earned 500 total points
ID: 20425357
That happens when you have a name with no comma, which causes Split to return only 1 value for Bname...
make this minor change

                tempStr = words(i)
                if InStr(tempStr, ",") < 1 then tempStr = tempStr + ","
                Bname = Split(tempStr, ",")

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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