How to remove white space in VB.NET

Question

2.33/5 (3 votes)

See more:

In the following code all the subject codes are read by SQLDataReader rd. How do I make the DataReader read the subject code of only the selected subject regardless of whether the subject name contain white space or not such as Biology, English Language, Principles of Cost Accounting, etc?

VB

Dim sql = "Select subjectCode From ProgramDetails.subjects where subjectname like @subname"
       Using con = New SqlConnection("Data Source=EBENEZERAKAGLO\SQLEXPRESS;Initial Catalog=Naass;Integrated Security=True")
           Using cmd = New SqlCommand(sql, con)
               cmd.Parameters.AddWithValue("@subname", String.Format("%{0}%", ""))
               con.Open()
               Using rd = cmd.ExecuteReader()
                   While rd.Read()
                       Dim subjectCode = rd.GetInt32(0)
                       subcode = subjectCode
                       ' ... '
                       MsgBox(subjectCode)
                   End While
               End Using
           End Using
       End Using

Posted 28-Jan-13 8:42am

Akaglo

Updated 29-Jan-13 5:00am

fjdiewornncalwe

v5

Add a Solution

2 solutions

Solution 1

RTRIM[^] And/Or LTRIM[^] in your SQL statement.

Alternatively you could use String.Trim[^]

Posted 28-Jan-13 8:49am

Adam R Harris

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-01-28T08:53:00

Solution 2

1. Do not remove spaces from the subname variable during the Form Load event.
subname = Module1.sname

2. String.Format("%{0}%", "") creates a string parameter that contains %%. Used with the LIKE operator, it will match everything in the database. Please change that line of code to this:
cmd.Parameters.AddWithValue("@subname", subname)

3. Change the SQL statement to this.
Dim Sql = "Select subjectCode From ProgramDetails.subjects where subjectname=@subname;"

Posted 28-Jan-13 8:53am

Mike Meinz

Updated 29-Jan-13 7:32am

v3

Comments

Akaglo 28-Jan-13 15:08pm

Thank you. The variable name that contains the search value is " & subname & " and when I replace "" with this variable name, subjectcodes of subject names that contain white spaces are not read by the SQLDataReader. Pls help me out because I'm so much worried about this problem.

Mike Meinz 28-Jan-13 15:09pm

Please show us the actual source code that is not working the way you expect. Also, provide examples of what actual values are in the variable when you do the test.

I tested this query on one of my databases and it found "Apple Valley" just fine.
select * from city where city like '%apple valley%'

Akaglo 28-Jan-13 15:20pm

Dim sql = "Select subjectCode From ProgramDetails.subjects where subjectname like @subname"
Using con = New SqlConnection("Data Source=EBENEZERAKAGLO\SQLEXPRESS;Initial Catalog=Naass;Integrated Security=True")
Using cmd = New SqlCommand(sql, con)
cmd.Parameters.AddWithValue("@subname", String.Format("%{0}%", "" & subname & ""))
con.Open()
Using rd = cmd.ExecuteReader()
While rd.Read()
Dim subjectCode = rd.GetInt32(0)
subcode = subjectCode
' ... '
MsgBox(subjectCode)
End While
End Using
End Using
End Using

Mike Meinz 28-Jan-13 15:22pm

The line should look like this:
cmd.Parameters.AddWithValue("@subname", String.Format("%{0}%", subname))
You are putting quotes in the parameter value so it is trying to find rows with quotes in the subjectname.
Quotes are only used in a SQL statement when a value is included in the SQL statement. Not when a parameterized valuename is included in the SQL statement.

Akaglo 28-Jan-13 15:32pm

I've copied your code and used it as such, but it's only the subjectcodes of subject names without white space that are being read. Is it because I have the following at Form_load: subj = Module1.sname
subj1 = subj.Replace(" ", "")
subname = subj1
And without the above code I wouldn't be able to log on with subject names with white space. Pls what shall I do again?

Mike Meinz 28-Jan-13 15:37pm

1. Remove the line with the Replace
and change the following line to this:
subname = Module1.sname

2. Explain, in detail, what you mean by "wouldn't be able to log on with".

Akaglo 28-Jan-13 15:42pm

I've done exactly that but I couldn't log on to the form with any subject.

Mike Meinz 28-Jan-13 15:44pm

Please explain what you mean by "log on to the form".
Please include source code for area that causes the problem with "log on to the form".

Akaglo 28-Jan-13 15:50pm

There is a log on form that is used to log on to the Windows form where the code in question, is expected to work, with some specified user credential such as Term, Academic Year, Subject Name, User Name and Password as in the following:

Private Sub btnLogOn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogOn.Click
Try

Dim frm As New MarksEntryFrm
Dim flag As Boolean
flag = False
f = cboForm.Text
ac = cboAcadYear.Text
t = cboTerm.Text
user = txtUserName.Text
password = txtPassword.Text
Module1.frm = cboForm.SelectedItem
Module1.uid = txtUserName.Text
Module1.pwd = txtPassword.Text
Module1.sname = cboSubjName.SelectedItem
Module1.term = cboTerm.SelectedItem
Module1.acadyear = cboAcadYear.SelectedItem
If String.IsNullOrEmpty(f) Or String.IsNullOrEmpty(ac) Or String.IsNullOrEmpty(t) Or String.IsNullOrEmpty(user) Or String.IsNullOrEmpty(password) Then
MessageBox.Show("Please all the fields must be entered", "", MessageBoxButtons.OK, MessageBoxIcon.Stop)
cboForm.Focus()
Else
Dim i As Integer = 0
cmd = New SqlCommand("Select c.Form,c.AcademicYear,b.SubjectName,b.UserID,b.Password,c.RegId,c.Form,c.Term,c.AcademicYear from ProgramDetails.Programs a, ProgramDetails.Subjects b,StudentDetails.Registration c where b.SubjectName='" & Module1.sname & "'and b.UserID='" & txtUserName.Text & "' and b.Password='" & Trim(txtPassword.Text) & "' and c.Term=" & cboTerm.SelectedItem & " and c.academicYear='" & Trim(cboAcadYear.SelectedItem) & "' and c.form=" & cboForm.SelectedItem & " and a.form=c.form and (c.core1='" & Module1.sname & "' or c.core2='" & Module1.sname & "' or c.core3='" & Module1.sname & "' or c.core4='" & Module1.sname & "' or c.core5='" & Module1.sname & "' or c.core6='" & Module1.sname & "' or c.elec1='" & Module1.sname & "' or c.elec2='" & Module1.sname & "' or c.elec3='" & Module1.sname & "' or c.elec4='" & Module1.sname & "')", cn)
dr1 = cmd.ExecuteReader
ctr = ctr + 1
If dr1.Read Then
frm.Show()
ctr = 0
Me.Close()
ElseIf ctr < 3 Then
MessageBox.Show("The selected Subject Name, User Name and Password combination you entered does not match. Please try again.", "Log in Failed", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
Else
MsgBox("Unathorized access. Aborting...")
Close()
End If
dr1.Close()
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Mike Meinz 28-Jan-13 15:57pm

Does cboSubjName.SelectedItem contain embedded spaces for those subjects that have embedded spaces?

Does Module1.sname contain embedded spaces for those subjects that have embedded spaces?

Do the values in StudentDetails.Registration columns (core1, core2, core3, core4, core5, core6, elec1, elec2, elec3, elec4) contain embedded spaces for those values that have embedded spaces?

Akaglo 28-Jan-13 15:59pm

Yes to all three questions, Sir

Mike Meinz 28-Jan-13 16:04pm

So you are saying that the data in the database contains embedded spaces and the variables in your program contain embedded spaces but the login fails. How does the login fail? I suggest taking that long login query into a SQL Server Management Studio query window and try to find out what is failing. Since you told me you used that Replace(" ","") statement after the login, I don't understand why the login fails if you remove it.

Mike Meinz 28-Jan-13 16:07pm

By the way, because you are not using a parameterized query for that long login query, these things could happen:
1. An input username, password, subjectname, etc that contains a quote character (') will cause the query to fail.
2. A user could perform an SQL Injection Attack by entering malicious SQL into one of the variables to alter or destroy your database.

Akaglo 28-Jan-13 16:09pm

One more issue, Sir, if I put for example English within the double quotes in line 4 of the code in question, as in cmd.Parameters.AddWithValue("@subname", String.Format("%{0}%", "English")), the DataReader is able to read the subject code of English Language; if replace English with Social, it is then able to read the subject code of Social Studies as well.

Mike Meinz 28-Jan-13 16:13pm

I would expect that and if you "hardcoded" the line as follows, it would find Social Studies if it looks like that in the database.
cmd.Parameters.AddWithValue("@subname", "%Social Studies%")

Are you sure that the subject names in the ProgramDetails.subject table contain embedded spaces?

Akaglo 28-Jan-13 16:24pm

Alright, the main issue is how do I replace "Social Studies" with something that will enable the reader read the subject code of any selected subject?" I've tried the following and when a subject with space is selected the code doesn't execute. cmd.Parameters.AddWithValue("@subname", subname)

Mike Meinz 28-Jan-13 16:27pm

Your sample Login code looks like VB but your question is in C#. You have mixed language in one program?

Akaglo 28-Jan-13 16:30pm

Alright Sir, I've also tried the following and it's only subject codes of subject names without space that is read by the DataReader.
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()

Mike Meinz 28-Jan-13 16:32pm

But you were removing the spaces in subname. You can't do that and expect to find rows in the table where the subjectnames contain embedded spaces. Embedded spaces in values work fine. That is not the problem.

You said that when you didn't remove the embedded spaces in subname that the login form failed. But you did the removal of the embedded spaces after the login form so how does removing the code to remove the embedded spaces cause the login to fail?

Mike Meinz 28-Jan-13 16:46pm

I wrote the following test program using SQLDataReader against one of my tables that has a city name of "Apple Valley". It has embedded space. It works fine. This shows that SQLDataReader properly handles finding data when there is an embedded space.


{
	string sql = "select city from city where city Like @city;";
	SqlConnection con = new SqlConnection("Database=StudioDatabase;Server=ILLIAC\\SQLEXPRESS;Connect Timeout=10;Integrated Security=True;Net=dbmssocn;Application Name=Calendar;");
	SqlCommand cmd = new SqlCommand(sql, con);
	cmd.Parameters.AddWithValue("@city", "%" + "Apple Valley" + "%");
	con.Open();
	SqlDataReader rd = default(SqlDataReader);
	rd = cmd.ExecuteReader;
	while (rd.Read) {
		string cityname = rd("City").ToString;
		Interaction.MsgBox(cityname);
	}
	rd.Close();
	rd.Dispose();
	cmd = null;
	con.Close();
	con.Dispose();
}

Akaglo 28-Jan-13 16:51pm

Sir, the point is the embedded space has to be removed with this code
subj1 = subj.Replace(" ", "")
subname = subj1
on the Windows form where the code in question has to read the subject code
when login succeeds. The code that removes the embedded spaces is not used on the login form as shown to you above.

Akaglo 28-Jan-13 16:54pm

Thanks so much for your attention.

Mike Meinz 28-Jan-13 16:54pm

My point is that removing the space using that code causes the second form to not find the subjectnames with embedded spaces.

Again, why can't you leave the embedded spaces in subname?

Mike Meinz 28-Jan-13 17:01pm

I would like to see what some of the values in ProgramDetails.Subjects subjectnam column actually look like. If they are not correct in the database, that could be the problem.

Akaglo 28-Jan-13 17:01pm

In your case it seems there's always only one city name. In my case the DataReader should always read the subject code of any selected subject, be it Social Studies, English language, Biology, etc. That's my problem. My code is always reading subject codes of subjects whose names do not contain spaces.

Mike Meinz 28-Jan-13 17:05pm

Akaglo,
My sample program would retrieve multiple rows if I used a different variable. I tested it with "%Park%". There are several cities that end in the name "Park" and have an embedded space.

I don't understand your last statement. Do you mean you are getting multiple rows returned? What do you mean when you say "is always reading subject codes"?

If you put "%Social Studies%" into the cmd.AddParameters.AddWithValue("@subname","%Social Studies%") what do you get and is that right or wrong and if so why?

I could imagine a ProgramDetails.Subjects table would have many rows and there might be some rows like
English I
English II
Advanced English
where your query would just find all of them but only get the subjectcode from the first one. Is that the issue?

Akaglo 28-Jan-13 17:21pm

Exactly so; that is, I may need the subject code of English II but my query would get me that of English I.

Mike Meinz 28-Jan-13 17:49pm

Then do not use a LIKE clause. Change your query to something like the following. I left out lines that do not change.


Dim sql = "Select subjectCode From ProgramDetails.subjects where subjectname = @subname"
...  
cmd.Parameters.AddWithValue("@subname", subname)
...

The purpose of the LIKE clause and the "%" in the value is to find all rows that match.

Akaglo 28-Jan-13 18:14pm

The same old problem.

Mike Meinz 28-Jan-13 18:36pm

Please explain further. If subname contains English I then the Select above should return row with subjectname = 'English I'.

Try these queries in a Query window within SQL Server Management Studio and tell me what you get:

Select * from ProgramDetails.subjects where subjectname='English I';
Go
Select * from ProgramDetails.subjects where subjectname='English II';
Go

Mike Meinz 28-Jan-13 19:22pm

I am going to be away from my computer. I will have my tablet with me so I will get email alerts when you add new comments and I will then come back and work to help you further.

In the meantime, I offer the following as an opportunity for you to learn a better approach.

Your task would have been a lot easier if the database was designed with good data design practices.

1. The ProgramDetails.Subjects table should contain an Identity column that is the Primary Key.
IDNumber Identity,
SubjCode Varchar(10) not null,
SubjectName Varchar(50) not null

This allows the school administration to change the SubjCode or SubjectName for a course without having to change anything but the one value in the ProgramDetails.Subjects table. They will tell you that they will never want to change the SubjCode or SubjectName but sooner than later someone will come to you and say they made a mistake and have to change one.

2. The StudentDetails.Registration table should contain an Identity column and be normalized (multiple rows per student). This would greatly simplify the login query.
IDNumber Identity;
Student_IDnumber not null,
RegistrationType int not null,
RegisteredSubject_IDnumber not null,

* Where Student_IDNumber is the IDNumber found in a StudentDetails.StudentInformation Table that contains demographic information about the student.
* Where Registration Type tells you if it is Core or Elective or whatever else?
* Where RegisteredSubject_IDnumber is the IDNumber found in the ProgramDetails.Subjects table.

3. The tables used in the login form that have SubjectName (StudentDetails.Registration, ProgramDetails.Programs?) values should not have
the SubjectName in them. They should have the IDNumber value for the appropriate row in the ProgramDetails.Subjects table.

4. During login allow the user to select from a combobox that shows the SubjCode and SubjectName. When the combobox gets populated, use the TAG property to hold the IDNumber. When the use selects an item, get the TAG property and use that in the login SELECT statement and pass that same IDNumber to the second form to find the row in the ProgramDetails.Subjects table.

5. Always use parameterized SQL (SQLParameter Class) to prevent SQL Injection Attacks and for better performance.

Akaglo 29-Jan-13 5:14am

Thank you so much for your good counsel. I have run the queries in SQL Server Management Studio and got the right results. What, however, beats me completely is that in the code in question, if a subject name without white space such as Biology, Mathematics, Physics, etc is selected the DataReader is able to read the corresponding subject code; but if a subject name with white space such as English Language, Social Studies,etc is selected the code doesn't even execute. Sir, I'm deeply worried about this problem because it's taking me too far.

Akaglo 29-Jan-13 7:36am

Hurray!!! The problem is now solved. I'm so much thankful to you and may the Almighty God richly bless you for your generous assistance. I added subname = Module1.sname to the following
subj1 = subj.Replace(" ", "")
subname = subj1 in the Form_Load instead a replacement.

Mike Meinz 29-Jan-13 7:37am

When you say that you got the right results, did the two SQL Server Management Server queries each return just one row of data?

Are you sure you removed the line of code that removes the spaces subj1 = subj.Replace(" ","")?

Mike Meinz 29-Jan-13 7:40am

Please show me what code you have in that area.

Akaglo 29-Jan-13 7:42am

Private Sub MarksEntryFrm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.MdiParent = MDI
subj = Module1.sname
subj1 = subj.Replace(" ", "")
subname = subj1
subname = Module1.sname

Mike Meinz 29-Jan-13 7:44am

Three lines can be removed since they are there to set subname but you override them on the last line. That is what I have been trying to tell you that the spaces should not be removed from subname.

Remove:
subj = Module.sname
subj1 = subj.Replace(" ","")
subname = subj1

Akaglo 29-Jan-13 7:47am

Alright Sir, Kudos you! You're a very kind and good teacher.

Mike Meinz 29-Jan-13 7:48am

Ok. I am off to eat breakfast now.
Please study my recommendations.

Akaglo 29-Jan-13 7:51am

I'd keep contacting you for your generous assistance. My email address is: akaglosincerity@yahoo.com

Akaglo 29-Jan-13 9:23am

Sir Mike, I wish to ask you a different question, and that's the ConnectionString in my program is cn = New SqlConnection("Data Source=EBENEZERAKAGLO\SQLEXPRESS;Initial Catalog=Naass;Integrated Security=True") where the Data Source is my computer and that implies that the program can only be run on my computer. Could please teach me how to go about the connection such that the program can automatically connect to a database on any computer that has SQL Server installed when the program is finally compiled and installed?

Mike Meinz 29-Jan-13 9:47am

Just change the "MyServerName" to the server name that has the database server. If it is a regular SQL Server and not SQLExpress, then it is generally just the server name. If it is another computer with SQLExpress installed, then put that computer's name followed by \SQLExpress just like you have it for your computer's instance of SQLExpress.

You can use this as a sample format:


 "Database=MyDatabaseName;Server=MyServerName;Connect Timeout=10;Integrated Security=True;Net=dbmssocn;Application Name=MyApplicationName;"

In your case "MyServerName" is "EBENEZERAKAGLO\SQLEXPRESS".

I hope you are not saying that every computer will have its own copy of the database. You don't want that, you want a central computer (database server) to hold the database so that everyone can read and update the same database.

Akaglo 29-Jan-13 10:12am

Yes Sir, I want a central database to hold the database.

Mike Meinz 29-Jan-13 10:14am

Then "MyServerName" in the connection string should point to the server where the central database resides.

Akaglo 29-Jan-13 10:25am

Thank you, Sir.

Mike Meinz 29-Jan-13 13:34pm

I am pleased that we were able to solve your problem. Please accept Solution 2 above so that this Question is marked as Answered.

Akaglo 29-Jan-13 15:58pm

Now, I wish to extend the project by making it cover students’ financial records (billing application), such that it can as well be used to handle payment of fees. In this regard, since I wouldn’t want to mess up with the creation of the tables in the database, I’d be happier if you could very kindly give me recommendations regarding how I should create the tables. The following are the requirements specification of the billing application I intend to include in my school management application I’m developing.

1.Fees are paid every term (semester) according to program of study by a student. For example fees paid by general science students are different from that of general arts students.
2.Bills are to be given to students before fees are paid.
3.Bills should state the items and their corresponding prices as well as the grand total.
4.Bills should indicate whether a student has a debit or credit from the previous term (semester)
5. Receipt is to be printed when payment is made.

Mike Meinz 29-Jan-13 16:14pm

I am sorry but I cannot devote that much time.

Advice I will give you is to use IDENTITY as primary key for all tables like I included in my recommendations above. When tables are to be related via a foreign key, the foreign key value should be the IDENTITY value of the foreign table. The end-users should never see these values.

Akaglo 29-Jan-13 16:33pm

Thank you, Sir. That's quite enough.

Akaglo 7-Feb-13 18:08pm

Pls Sir, kindly check out another the following problem which I've posted on this site: "How do I update data that has its its primary key set to IsIdentity? "