CODEWITHSUNDEEP
sqlvb.net
Akaglo
Akaglo

Reputation: 127

How to remove white space in VB.Net

In the following code SqlDataReader 'dr' does not read the "SubjectCode" of subjects whose names contain white space; example English Language, Social Studies, Principles of Cost Accounting, etc. from the subjects table.

I'd therefore be more than grateful if someone could very kindly help me out immediately.

cmd = New SqlCommand( _
    "select subjectCode from ProgramDetails.subjects where subjectname='" & _
    Trim(subname) & " ' ", cn)
dr = cmd.ExecuteReader
If dr.Read Then
    subcode = dr(0)
End If
dr.Close()

Upvotes: 0

Views: 1611

Answers (2)

spajce
spajce

Reputation: 7092

Just use the RTRIM

cmd = New SqlCommand("select RTRIM(subjectCode) from ProgramDetails.subjects " + _
                     "where subjectname Like '" & Trim(subname) & "%'", cn)
dr = cmd.ExecuteReader
If dr.Read Then
    subcode = dr(0)
End If
dr.Close()

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

  1. You're open for SQL-Injection, use SQL-Parameters
  2. You have a white-space at the end, i assume that this is not desired
  3. You might want to use LIKE with the wildcard % instead:

For example:

Dim sql = "Select subjectCode From ProgramDetails.subjects where subjectname Like @subname"
Using con = New SqlConnection(connectionString)
    Using cmd = New SqlCommand(sql, con)
        cmd.Parameters.AddWithValue("@subname", String.Format("%{0}%", "english"))
        Con.Open()
        Using rd = cmd.ExecuteReader()
            While rd.Read()  
                Dim subjectCode = rd.GetString(0)
                ' ... '
            End While
        End Using
    End Using
End Using

Upvotes: 3

Related Questions