Comments by Akaglo (Top 40 by date)

I've removed the repeated line of code and what is happening is that the grid now appears but can't display data in columns

Please Sir, kindly solve the problem for me.

Please I'd be grateful if you could try to edit the code to solve the whole problem.

Please Sir, I have posted a question on Deleting a record from multiple tables. Kindly attend to it out for.

My main concern is the following line of code: ProgramDetails.EnglishLanguage. How I do represent all the subjects by a variable or something as though all the subjects table are one table.Since it would not be appropriate to write something like ProgramDetails.EnglishLanguage, ProgramDetails.Mathematics, ProgramDetails.French, etc

Sir, kindly review the following code for me. The update command executes but no update takes place.

If Not bError Then
sbQry.Append("Update StaffDetails.Staff set StaffID=@StaffID,EmployeeName=@EmployeeName,SSN=@SSN,Day=@Day,Month=@Month,Year=@Year,Sex=@Sex,Qualifications=@Qualifications,Rank=@Rank,Responsibility=@Responsibility,ApDay=@ApDay,ApMonth=@ApMonth,ApYear=@ApYear,RegNumber=@RegNumber,Phone=@Phone,Email=@Email,Status=@Status")
If img IsNot Nothing Then
sbQry.Append(",Photo=@Photo")
End If
sbQry.Append(" where Number='").Append(txtNo.Text).Append("'")

cmd = New SqlCommand(sbQry.ToString, cn)
cmd.Parameters.Add(New SqlParameter("@StaffID", txtNo.Text))
cmd.Parameters.Add(New SqlParameter("@StaffID", txtStaffID.Text))
cmd.Parameters.Add(New SqlParameter("@EmployeeName", txtName.Text))
cmd.Parameters.Add(New SqlParameter("@SSN", txtSsNumber.Text))
cmd.Parameters.Add(New SqlParameter("@Day", cboDay.Text))
cmd.Parameters.Add(New SqlParameter("@Month", cboMonth.Text))
cmd.Parameters.Add(New SqlParameter("@Year", txtYear.Text))
cmd.Parameters.Add(New SqlParameter("@Sex", Sex))
cmd.Parameters.Add(New SqlParameter("@Qualifications", txtQualifications.Text))
cmd.Parameters.Add(New SqlParameter("@Rank", txtRank.Text))
cmd.Parameters.Add(New SqlParameter("@Responsibility", txtRespons.Text))
cmd.Parameters.Add(New SqlParameter("@ApDay", cboApDay.Text))
cmd.Parameters.Add(New SqlParameter("@ApMonth", cboApMonth.Text))
cmd.Parameters.Add(New SqlParameter("@ApYear", txtApYear.Text))
cmd.Parameters.Add(New SqlParameter("@RegNumber", txtRegdNum.Text))
cmd.Parameters.Add(New SqlParameter("@Phone", txtPhone.Text))
cmd.Parameters.Add(New SqlParameter("@Email", txtEmail.Text))
cmd.Parameters.Add(New SqlParameter("@Status", cboStatus.Text))
If img IsNot Nothing Then
cmd.Parameters.Add(New SqlParameter("@Photo", img))
End If
cmd.ExecuteNonQuery()
lblSave_Update.Text = "Record successfully updated"
End If
Showgrid()
Catch ex As Exception
MessageBox.Show(ex.Message)

Please if I parametrize it to be like IDNumber=@IDNumber the following I get the message: Must declare the scalar variable "@IDNumber". How do therefore declare it, please.

Thank Sir, the problem has now solved, but I wish that you could show me how I can do away with & txtIDNumber.Text & by using a parameter. Even though the code now works fine what I've done is that I've set the Visible of txtIDNumber to False in the Property pane, to prevent the users from seeing the IsIdentity number. However, if I parametrize it to be like IDNumber=@IDNumber the following I get the message: Must declare the scalar variable "@IDNumber". How do therefore declare it, please?

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? "

"subject" is a variable name that holds subject type e.g. Elective or Core

Thank you, Sir. That's quite enough.

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.

Thank you, Sir.

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

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?

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

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

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

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.

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.

The same old problem.

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

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.

Thanks so much for your attention.

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.

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()

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)

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.

Yes to all three questions, Sir

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

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

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?

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

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.