How to split a joined column into its seperate columns and use the data? (VB.Net + SQL Management Studio)

This is gonna be somewhat tricky to explain but I'll try to break it down. Note that this is created using VB.Net 2003.

I have a Web page which requires user to input data to save into SQL. They are required to fill in:

Course: {Select via drop-down table}  \\ Variable name = Crse

Emp No: {Manually type the number}    \\ Variable name = Emp

For the drop down list for Course, the data is obtained from an SQL table 'Course', with the columns:

| Course Code | Course Title |

Once input complete, I can then save the entry into my Emp_Course table in SQL using the query:

Dim updateState As String = "insert into EMP_COURSE" _
                          & "(Employee_No, CourseCode)" _
                          & "values('" & Emp.Text & "', " _
                          & "'"Crse.SelectedItem.ToString & "')"

Previously the drop-down list only needed the show Course Code, but now I'm required to add in the Course Title as well. Another thing to point out is that the Course Code has no fixed length.

Drop-down list sample:

  Before:
          A001
          BX003

  After:
          A001 - Course A
          BX003 - Course BX

Meaning I have to change the logic in populating the drop-down list:

Dim list As New SqlCommand("select CourseCode + ' - ' " _
                         & "+ CourseTitle as Course from [SQL].[dbo].[Course] " _
                         & "order by CourseCode", SQLDB)

Now comes my main issue, when I want to save my entry, the program obviously gives an error because the SQL still refers to the Course Code only, while my drop-down list is a Code + Description hybrid.

So since now I've made my course selection, how am I supposed to add to my SQL to update Emp_Course table to tell it to select the Course Code part of my hybrid selection?

I would just go to the Course table and just add a new Code + Title column and refer to that, but I have no authority to modify it and need to work around it.

Any other alternatives I can use?

Dim arr As String() = Crse.SelectedItem.ToString().Split(New Char() {"-"c})
Dim courseCode AS String = Trim(arr(0))
Dim updateState As String = "insert into EMP_COURSE" _
                      & "(Employee_No, CourseCode)" _
                      & "values('" & Emp.Text & "', " _
                      & "'"courseCode & "')"

Visual Basic .NET: How to use Split() and Join(), How to use the Split and Join functions in your VB NET code. Each word would then be separated, ready for you to place into an array. Here's an example for� Let’s see how to split a text column into two columns in Pandas DataFrame. Method #1 : Using Series.str.split () functions. Split Name column into two different columns. By default splitting is done on the basis of single space by str.split () function.

Comments and explanations in line.

Imports System.Data.SqlClient
Public Class WebForm1
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Crse.DataSource = CreateDataSource()
            'Course is the concatenated string returned from
            'the database containing the CourseCode and the CourseTitle
            Crse.DataTextField = "Course"
            Crse.DataValueField = "CourseCode"
            Crse.DataBind()
            Crse.SelectedIndex = 0
        End If
    End Sub
    Protected Function CreateDataSource() As DataTable
        Dim dt As New DataTable
        Using SQLDB As New SqlConnection("Your connection string")
            'This selects the CourseCode as a separate column and the string called Course
            'containing the CourseCode and the CourseTitle
            Using cmd As New SqlCommand("select CourseCode, CourseCode + ' - ' + CourseTitle as Course from [SQL].[dbo].[Course] order by CourseCode", SQLDB)
                SQLDB.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
        Return dt
    End Function

    Protected Sub UpdateDatabase()
        Using SQLDB As New SqlConnection("Your connection string")
            Using cmd As New SqlCommand("insert into EMP_COURSE (Employee_No, CourseCode) values(@EmpNo, @CourseCode);", SQLDB)
                'I guessed at the SqlDbType. Check the database for the real data types.
                cmd.Parameters.Add("EmpNo", SqlDbType.Int).Value = CInt(Emp.Text)
                'Always use parameters to prevent SQL injection
                'The SelectedValue will return the CourseCode
                cmd.Parameters.Add("@CourseCode", SqlDbType.VarChar).Value = Crse.SelectedValue
                SQLDB.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        UpdateDatabase()
    End Sub
End Class

DataTable.Merge Method (System.Data), Columns.Add("newColumn", typeof(System.String)); // Add three rows. Note that the id column can't be the // same as existing rows in the original table. row� Split Data into Multiple Worksheets Based on Column value. The best and simple way is to write an Excel VBA macro to split a worksheet into multiple worksheets based on a specified column. And we can use an open source VBA macro from GitHub. 1# click on “Visual Basic” command under DEVELOPER Tab.

Split the value and get the course code as in the following code. Hope it helps.

    Dim Str = Crse.SelectedItem.ToString
    Dim strArr() As String
    strArr = Str.Split(CType("-", Char()))
    Dim CourseCode As String = strArr(0).Trim

How to get single column value in split mulitple column in a row , Please, read my comment to the question first. As i mentioned, you have to keep your focus on data, not gridview object. On the first look you� Earlier, I have written a blog post about how to split a single row data into multiple rows using XQuery. Today, I came across a situation where I had to split a single column data into multiple columns using delimiter. Lets me create a sample to demonstrate the solution. Sample : Solution : Given below…

Split text across columns, The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, across columns. Go to tab "Data" on the ribbon; Click "Text to columns" button Don�t give up, it is easy to build a basic udf that splits values across columns. [vb 1="vbnet" language=","] In situations when you have a column of names of the same pattern, for example only first and last name, or first, middle and last name, the easiest way to split them into separate columns is this: Select the column of full names that you'd like to separate. Head to the Data tab > Data Tools group and click Text to Columns.

How I split a row by columns in MS SQL – PR Code, So here is the road to how to separate the columns data into each row that I… This is where the fun now happens, as we split each row by its column and piece it back together. In the loop below I use the split function found on the great Stack Overflow to separate INNER JOIN NEt website windows. Split date into three columns: day, month and year with Text to Column. In Excel, you also can use Text to Column function to split a column date to three columns with day, month and year. 1. Select the date column excluding header unless it has no header, and click Data > Text to Columns. See screenshot: 2.

Select distinct on two column with multiple columns returned, At first, please insert one new blank column at the left of your data, in this example, I will To simulate the select unique col_1, col_2 of SQL you can use DataFrame. To join these two tables I can add a day column to the budget table , and then join them How to split a column with delimited string into multiple columns. By default, it’s set up to split the selected cell (s) into two columns, which is exactly what we want. You can just go ahead and click the “OK” button to make the split. Input the number of rows and columns you would like to split your cell into. And that cell we selected is now two cells.

Comments
  • I do not understand what SSMS has to do with this.
  • Must have added that tag by accident. I'll remove it.
  • Please, comment on your code, even if it's self-explanatory
  • Thank for the code. Although it seems to have problems when the Course Code has - incorporated into it. Example: ERT-1234. Now it just reads ERT and it cannot find the corresponding Course Title. Sorry I didn't raised this earlier. Didn't notice it until today.