Click here to Skip to main content
13,552,932 members
Rate this:
Please Sign up or sign in to vote.
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?
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}%", ""))
               Using rd = cmd.ExecuteReader()
                   While rd.Read()
                       Dim subjectCode = rd.GetInt32(0)
                       subcode = subjectCode
                       ' ... '
                   End While
               End Using
           End Using
       End Using
Posted 28-Jan-13 8:42am
Updated 29-Jan-13 5:00am
Rate this: bad
Please Sign up or sign in to vote.

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;"
Ebenezer-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.
MikeMeinz 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%'
Ebenezer-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 & ""))
Using rd = cmd.ExecuteReader()
While rd.Read()
Dim subjectCode = rd.GetInt32(0)
subcode = subjectCode
' ... '
End While
End Using
End Using
End Using
MikeMeinz 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.
Ebenezer-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?
MikeMeinz 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".
Ebenezer-Akaglo 28-Jan-13 15:42pm
I've done exactly that but I couldn't log on to the form with any subject.
MikeMeinz 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".
Ebenezer-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

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)
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
ctr = 0
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)
MsgBox("Unathorized access. Aborting...")
End If
End If
Catch ex As Exception
End Try
End Sub
MikeMeinz 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?
Ebenezer-Akaglo 28-Jan-13 15:59pm
Yes to all three questions, Sir
MikeMeinz 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.
MikeMeinz 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.
Ebenezer-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.
MikeMeinz 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?
Ebenezer-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)
MikeMeinz 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?
Ebenezer-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
MikeMeinz 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?
MikeMeinz 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" + "%");
SqlDataReader rd = default(SqlDataReader);
rd = cmd.ExecuteReader;
while (rd.Read) {
string cityname = rd("City").ToString;
cmd = null;
Ebenezer-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.
Ebenezer-Akaglo 28-Jan-13 16:54pm
Thanks so much for your attention.
MikeMeinz 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?
MikeMeinz 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.
Ebenezer-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.
MikeMeinz 28-Jan-13 17:05pm
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?
Ebenezer-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.
MikeMeinz 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.
Ebenezer-Akaglo 28-Jan-13 18:14pm
The same old problem.
MikeMeinz 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';
Select * from ProgramDetails.subjects where subjectname='English II';
MikeMeinz 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.

Ebenezer-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.
Ebenezer-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.
MikeMeinz 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(" ","")?
MikeMeinz 29-Jan-13 7:40am
Please show me what code you have in that area.
Ebenezer-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
MikeMeinz 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.

subj = Module.sname
subj1 = subj.Replace(" ","")
subname = subj1
Ebenezer-Akaglo 29-Jan-13 7:47am
Alright Sir, Kudos you! You're a very kind and good teacher.
MikeMeinz 29-Jan-13 7:48am
Ok. I am off to eat breakfast now.
Please study my recommendations.
Ebenezer-Akaglo 29-Jan-13 7:51am
I'd keep contacting you for your generous assistance. My email address is:
Ebenezer-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?
MikeMeinz 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.
Ebenezer-Akaglo 29-Jan-13 10:12am
Yes Sir, I want a central database to hold the database.
MikeMeinz 29-Jan-13 10:14am
Then "MyServerName" in the connection string should point to the server where the central database resides.
Ebenezer-Akaglo 29-Jan-13 10:25am
Thank you, Sir.
MikeMeinz 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.
Ebenezer-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.
MikeMeinz 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.
Ebenezer-Akaglo 29-Jan-13 16:33pm
Thank you, Sir. That's quite enough.
Ebenezer-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? "
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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

Alternatively you could use String.Trim[^]

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01-2016 | 2.8.180515.1 | Last Updated 29 Jan 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100