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

Question

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?

Answer 1

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 & "')"

Answer 2

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

Answer 3

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