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
make this minor change
tempStr = words(i)
if InStr(tempStr, ",") < 1 then tempStr = tempStr + ","
Bname = Split(tempStr, ",")