Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET T-SQL
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}%", ""))
                    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
Akaglo419
Edited 29-Jan-13 5:00am
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

RTRIM[^] And/Or LTRIM[^] in your SQL statement.
 
Alternatively you could use String.Trim[^]
  Permalink  
Rate this: bad
good
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;"
  Permalink  
v3
Comments
Akaglo at 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 at 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 at 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 at 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 at 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 at 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 at 28-Jan-13 15:42pm
   
I've done exactly that but I couldn't log on to the form with any subject.
Mike Meinz at 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 at 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 at 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 at 28-Jan-13 15:59pm
   
Yes to all three questions, Sir
Mike Meinz at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 28-Jan-13 16:54pm
   
Thanks so much for your attention.
Mike Meinz at 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 at 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 at 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 at 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 at 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 at 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 at 28-Jan-13 18:14pm
   
The same old problem.
Mike Meinz at 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 at 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 at 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 at 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 at 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 at 29-Jan-13 7:40am
   
Please show me what code you have in that area.
Akaglo at 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 at 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 at 29-Jan-13 7:47am
   
Alright Sir, Kudos you! You're a very kind and good teacher.
Mike Meinz at 29-Jan-13 7:48am
   
Ok. I am off to eat breakfast now.
Please study my recommendations.
Akaglo at 29-Jan-13 7:51am
   
I'd keep contacting you for your generous assistance. My email address is: akaglosincerity@yahoo.com
Akaglo at 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 at 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 at 29-Jan-13 10:12am
   
Yes Sir, I want a central database to hold the database.
Mike Meinz at 29-Jan-13 10:14am
   
Then "MyServerName" in the connection string should point to the server where the central database resides.
Akaglo at 29-Jan-13 10:25am
   
Thank you, Sir.
Mike Meinz at 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 at 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 at 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 at 29-Jan-13 16:33pm
   
Thank you, Sir. That's quite enough.
Akaglo at 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? "

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

  Print Answers RSS
0 CHill60 320
1 Sarvesh Kumar Gupta 238
2 Sergey Alexandrovich Kryukov 163
3 OriginalGriff 163
4 DamithSL 153


Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 29 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid