noelroshan
asked on
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
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
ASKER
s = rs("Authors").Value is saying error Conversion from type 'DBNull' to type 'String' is not valid
s = Convert.ToString(rs("Autho rs").Value )
However, instead of NULL in the db field, you will end up with '' (empty string)
However, instead of NULL in the db field, you will end up with '' (empty string)
ASKER
rs.MoveNext()
Error Operation is not allowed when the object is closed.
change
rs = conn.Execute(InsrSql, , )
to
conn.Execute(InsrSql, , )
otherwise you're tramping through the previous rs= asignment
rs = conn.Execute(InsrSql, , )
to
conn.Execute(InsrSql, , )
otherwise you're tramping through the previous rs= asignment
ASKER
what is that mean "Line 1: Incorrect syntax near 'Brien'.""
InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0).Replace("'","''") & "', '" & Bname(1).Replace("'","''") & "', '" & pos & "'"
ASKER
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.
Error :Index was outside the bounds of the array.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
InsrSql = "INSERT INTO SpAuthors(USQ_RPID, F_Name, L_NameI, pos) VALUES ('" & RPID & "', '" & Bname(0) & "', '" & Bname(1) & "', '" & pos & "')"