• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

ms sql table with column with string data which has to be parsed. (Winforms environment)

Table has a column named         comb_bill_key  varchar  (250).  Col contains user keys which can be from 1 char in lenght to 8 char in lenght.  Data is a number preceded by a  " * ".  

                              example of some data----   *1234*2345*3456*87*987

        My problem is to parse out each user key and insert it into another (work) table with the identifier from original table. An example would be using data above . if the data above were in record number 1005, then I would like to insert into a work table        
                                     REC #                user_Key
                                     1005                   1234
                                     1005                   2345
                                     1005                  3456
                                     1005                  87
                                     1005                  987

thanks


0
abobby2005
Asked:
abobby2005
  • 3
  • 2
1 Solution
 
Brian CroweCommented:
assuming you are using a datareader to pull the comb_bill_key value from your SQL DB and assuming the existence of a datatable (dtKeys) with "RecID" and "UserKey" fields...

dim cmd as new sqlcommand
dim dr as sqldatareader
dim row as datarow
dim keys() as string
dim i as integer

cmd.connection = mySQLConnection
cmd.commandtext = "SELECT RecID, comb_bill_key FROM myTable"
try
   cmd.connection.open
   dr = cmd.executequery
   while dr.read
      keys = dr("comb_bill_key").tostring.split("*"c)
      for i = 0 to keys.length - 1
         row = dtKeys.newrow
         row("RecID") = dr("RecID")
         row("UserKey") = keys(i)
         dtkeys.rows.add(row)
      next
   end while
catch ex as exception
   messagebox.show(ex.message)
finally
   if cmd.connection.state = connectionstate.open
      cmd.connection.close
   end if
end try
0
 
abobby2005Author Commented:
When I attempt to run the code below I get an error  :  

             NAME "wrk_bill_Key"  is not declared.
 
Help  !!!


    Dim cmd As New SqlCommand
        Dim cmSQL As SqlCommand
        Dim dr As SqlDataReader
        Dim row As DataRow
        Dim keys() As String
        Dim i As Integer

        Dim conaccess As New SqlClient.SqlConnection(SqlAccess)
        cmSQL = New SqlCommand(Command, conaccess)
        cmSQL.CommandText = "SELECT claims_key,Combined_Billing_Keys From claims"
        Try
            conaccess.Open()
            cmSQL.ExecuteNonQuery()
            conaccess.Close()
            conaccess.Open()
            dr = cmSQL.ExecuteReader
            While dr.Read
                keys = dr("claims_key").ToString.Split("*"c)
                For i = 0 To keys.Length - 1
                    row = Wrk_Bill_Key.newrow
                    row("RecID") = dr("RecID")
                    row("UserKey") = keys(i)
                    Wrk_Bill_Key.rows.add(row)
                Next
            End While
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If conaccess.State = ConnectionState.Open Then
                conaccess.Close()
            End If
        End Try
    End Sub
0
 
Brian CroweCommented:
Wrk_Bill_Key as you are using it should be the name of your datatable.
0
 
abobby2005Author Commented:
Yes, it is a table in the database
0
 
Brian CroweCommented:
you're confusing database table and datatable.  The DataTable is a visual  basic object used to hold data locally.  It is a disconnected container from your database.


private dtBillKey as new datatable
...
dtbillkey.columns.add("RecID", sqldbtype.int)
dtbillkey.columns.add("UserKey", sqldbtype.int)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now