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

Posted on 2006-05-31
Last Modified: 2010-04-23
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


Question by:abobby2005
    LVL 34

    Accepted Solution

    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"
       dr = cmd.executequery
          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)
       end while
    catch ex as exception
       if cmd.connection.state =
       end if
    end try

    Author Comment

    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"
                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)
                End While
            Catch ex As Exception
                If conaccess.State = ConnectionState.Open Then
                End If
            End Try
        End Sub
    LVL 34

    Expert Comment

    by:Brian Crowe
    Wrk_Bill_Key as you are using it should be the name of your datatable.

    Author Comment

    Yes, it is a table in the database
    LVL 34

    Expert Comment

    by:Brian Crowe
    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

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now