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

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
            Next
            rs.MoveNext()
        End While
        conn.Close()
    End Sub

   
   
End Class
noelroshanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
imitchieConnect With a Mentor Commented:
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, ",")
0
 
imitchieCommented:
missing end )

                    InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0) & "', '" & Bname(1) & "', '" & pos & "')"
0
 
noelroshanAuthor Commented:
s = rs("Authors").Value is saying error Conversion from type 'DBNull' to type 'String' is not valid
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

However, instead of NULL in the db field, you will end up with '' (empty string)
0
 
noelroshanAuthor Commented:

rs.MoveNext()
Error Operation is not allowed when the object is closed.
0
 
imitchieCommented:
change

rs = conn.Execute(InsrSql, , )

to

conn.Execute(InsrSql, , )

otherwise you're tramping through the previous rs= asignment
0
 
noelroshanAuthor Commented:
what is that mean "Line 1: Incorrect syntax near 'Brien'.""
0
 
imitchieCommented:
InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0).Replace("'","''") & "', '" & Bname(1).Replace("'","''") & "', '" & pos & "'"
0
 
noelroshanAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.