Click here to Skip to main content
13,512,223 members
Rate this:
Please Sign up or sign in to vote.
See more:
What to do to Solve this?

I have the following code which I am trying to use to enter data into sql 2005 database. When I run the application, it seem to run fine but after checking the database to see if data has been added, I found out that no data has been added. I wonder what am doing wrong that data is not populating the database.

Code for Database Class (DAL)

mports System.Data.SqlClient

Public Class StudentsDB
Dim students As Student
Dim ds As New DataSet()
Dim i As Integer

Public Shared Function AddStudent(ByVal student As Student) As Integer
Dim connection As SqlConnection = Schoolpayables.GetConnection()
Dim insertStatement As String _
= "INSERT Students" _
& "(StudentNo, Firstname, Surname, Middlename, DOB, Gender, " & _
" Address, Homephone, Cellphone, email, HomeTown, Region, Photo)) " _
& "VALUES (@StudentNo, @FirstName, @Surname, @Middlename, @DOB, " _
& "@Address, @HomePhone, @Cellphone, @Town, " _
& "@Region, @Photo)"
Dim InsertCommand As New SqlCommand("insertStatement", connection)

InsertCommand.Parameters.AddWithValue("@StudentNo", Student.StudentNo)
InsertCommand.Parameters.AddWithValue("@Firstname", Student.FirstName)
InsertCommand.Parameters.AddWithValue("@Surname", Student.SurName)
InsertCommand.Parameters.AddWithValue("@Middlename", Student.MiddleName)
InsertCommand.Parameters.AddWithValue("@DOB", Student.DOB)
InsertCommand.Parameters.AddWithValue("@Gender", Student.Gender)
InsertCommand.Parameters.AddWithValue("@Address", Student.Address)
InsertCommand.Parameters.AddWithValue("@Homephone", Student.HomePhone)
InsertCommand.Parameters.AddWithValue("@CellPhone", Student.Cellphone)
InsertCommand.Parameters.AddWithValue("@Town", Student.HomeTown)
InsertCommand.Parameters.AddWithValue("@Region", Student.Region)
InsertCommand.Parameters.AddWithValue("@Photo", Student.Photo)


Dim Selectstatement As String _
= "SELECT IDENT_CURRENT('Students') FROM Students"
Dim SelectCommand As New SqlCommand(Selectstatement, connection)
Dim studdentID As Integer = CInt(SelectCommand.ExecuteScalar)
' Return StudentID

Catch ex As Exception
Throw ex
End Try

End Function

Public Shared Function GetStudent(ByVal studentID As Integer) As Student

Dim student As New Student
Dim connection As SqlConnection = Schoolpayables.GetConnection()

Dim selectStatement As String _
= "SELECT StudentNo, Firstname, Surname, Middlename, DOB, Gender, " & _
" Address, Homephone, Cellphone, email, HomeTown, Region, Photo From Students WHere StudentID = @StudentID"

Dim selectCommand As New SqlCommand(selectStatement, connection)
selectCommand.Parameters.AddWithValue("@StudentID", StudentID)


Dim reader As SqlDataReader _
= selectCommand.ExecuteReader(CommandBehavior.SingleRow)

If reader.Read Then
student.StudentID = CInt(reader("StudentID"))
student.StudentNo = reader("StudentNo").ToString
student.FirstName = reader("Firstname").ToString
student.SurName = reader("Surname").ToString
student.MiddleName = reader("Middlename").ToString
student.DOB = CDate(reader("DOB"))
student.Gender = reader("Gender").ToString
student.Address = reader("Address").ToString
student.HomePhone = reader("HomePhone").ToString
student.Cellphone = reader("Cellphone").ToString = reader("email").ToString
student.HomeTown = reader("Town").ToString
student.Region = reader("Region").ToString
student.Photo = reader("Photo").ToString
student = Nothing
End If
Catch ex As SqlException
Throw ex

End Try

Return Student

End Function

Public Shared Function UpdateStudents(ByVal oldStudent As Student, _
ByVal newStudent As Student) As Boolean
Dim connection As SqlConnection = Schoolpayables.GetConnection()
Dim updateStatement As String _
= "UPDATE Students SET " _
& "StudentNo = @NewStudentNo, " _
& "Firstname = @NewFirstname, " _
& "Surname = @NewSurname, " _
& "Middlename = @NewMiddlename, " _
& "DOB = @NewDOB, " _
& "Homephone = @NewHomephone, " _
& "CellPhone = @NewCellPhone, " _
& "email = @Newemail, " _
& "HomeTown = @NewHomeTown, " _
& "Region = @NewRegion, " _
& "photo = @NewPhoto " _
& "WHERE StudentID = @OldStudentID " _
& "AND StudentNo = @OldStudentNo " _
& "AND Firstname = @OldFirstname " _
& "AND (Surname = @OldSurname " _
& "AND Middlename = @OldMiddlename " _
& "AND DOB = @OldDOB " _
& "AND Homephone = @OldHomephone " _
& "AND CellPhone = @OldCellPhone" _
& "AND email= @Oldemail " _
& "AND HomeTown = @OldHomeTown " _
& "AND Region = @OldRegion " _
& "AND photo = @Oldphoto"
Dim updateCommand As New SqlCommand(updateStatement, connection)
updateCommand.Parameters.AddWithValue("@NewStudentNo", newStudent.StudentNo)
updateCommand.Parameters.AddWithValue("@NewFirstname", newStudent.FirstName)
updateCommand.Parameters.AddWithValue("@NewSurname", newStudent.SurName)
updateCommand.Parameters.AddWithValue("@NewMiddlename", newStudent.MiddleName)
updateCommand.Parameters.AddWithValue("@NewDOB", newStudent.DOB)
updateCommand.Parameters.AddWithValue("@NewHomephone", newStudent.HomePhone)
If newStudent.Cellphone = "" Then
updateCommand.Parameters.AddWithValue("@NewCellPhone", DBNull.Value)
updateCommand.Parameters.AddWithValue("@NewCellPhone", newStudent.Cellphone)
End If

updateCommand.Parameters.AddWithValue("@NewHomeTown", newStudent.HomeTown)

updateCommand.Parameters.AddWithValue("@NewRegion", newStudent.Region)
updateCommand.Parameters.AddWithValue("@NewPhoto", newStudent.Photo)
updateCommand.Parameters.AddWithValue("@OldStudentID", oldStudent.StudentID)
updateCommand.Parameters.AddWithValue("@OldStudentNo", oldStudent.StudentNo)
updateCommand.Parameters.AddWithValue("@OldFirstname", oldStudent.FirstName)
updateCommand.Parameters.AddWithValue("@OldSurname", oldStudent.SurName)
updateCommand.Parameters.AddWithValue("@OldMiddlename", oldStudent.MiddleName)
updateCommand.Parameters.AddWithValue("@OldDOB", oldStudent.DOB)
updateCommand.Parameters.AddWithValue("@OldHomephone", oldStudent.HomePhone)
If oldStudent.Cellphone = "" Then
updateCommand.Parameters.AddWithValue("@OldPhone", DBNull.Value)
updateCommand.Parameters.AddWithValue("@OldCellPhone", oldStudent.Cellphone)
End If


updateCommand.Parameters.AddWithValue("@OldHomeTown", oldStudent.HomeTown)

updateCommand.Parameters.AddWithValue("@OldRegion", oldStudent.Region)
updateCommand.Parameters.AddWithValue("@OldPhoto", oldStudent.Photo)

Dim count As Integer = updateCommand.ExecuteNonQuery
If count > 0 Then
Return True
Return False
End If
Catch ex As SqlException
Throw ex
End Try
End Function

Public Sub LoadData(ByVal StudentID As Decimal)
Dim connection As SqlConnection = Schoolpayables.GetConnection()

Dim str As String = "SELECT * FROM Students WHERE StudentID = " & StudentID
Dim cmd As New SqlCommand(str, connection)
Dim b() As Byte
b = cmd.ExecuteScalar()

Catch ex As Exception
End Try
End Sub

Code For Presentation Layer:

Imports System.Threading
Imports GhanaSchools
Imports System.IO

Public Class frmStudents
Public addStudent As Boolean
Public student As Student
Dim b() As Byte
Private WithEvents mobjStudents As Student
Dim i As Integer
Dim strValue As String

Private Sub frmStudents_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
If addStudent Then
Me.Text = "Add Student"
cboGender.SelectedIndex = -1
End If

End Sub

Private Sub btnAccept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAccept.Click
Dim st As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
Dim mbr As BinaryReader = New BinaryReader(st)
Dim buffer(st.Length) As Byte
mbr.Read(buffer, 0, CInt(st.Length))

With mobjStudents
For i = 0 To cboGender.Items.Count - 1
strValue = CType(cboGender.Items(i), String)
'If strValue = .Gender Then
cboGender.SelectedIndex = i
Exit For
'End If

End With
If IsValidData() Then
If addStudent Then
student = New Student

Student.StudentID = StudentsDB.AddStudent(Student)
Me.DialogResult = Windows.Forms.DialogResult.OK

Catch ex As Exception
MessageBox.Show(ex.Message, ex.GetType.ToString)
End Try
Dim newStudent As New Student
newStudent.StudentID = Student.StudentID
If Not StudentsDB.UpdateStudent(Student, newStudent) Then
MessageBox.Show("Another user has updated or deletaed that student. ", "Database error")
Me.DialogResult = Windows.Forms.DialogResult.Retry
Student = newStudent
Me.DialogResult = Windows.Forms.DialogResult.OK
End If
Catch ex As Exception

MessageBox.Show(ex.Message, ex.GetType.ToString)
End Try
End If
End If
MessageBox.Show("The record has been added.", "Record Added", MessageBoxButtons.OK, MessageBoxIcon.Information)

GoTo a
txtStudentNo.Text = ""
txtFirstname.Text = ""
txtMiddlename.Text = ""
txtSurname.Text = ""
cboGender.SelectedIndex = -1
txtAddress.Text = ""
txtHPhone.Text = ""
txtcellphone.Text = ""
txtemail.Text = ""
txtHomeTown.Text = ""
txtRegion.Text = ""
txtcellphone.Text = ""
txtemail.Text = ""
PicPhoto.Image = Nothing

End Sub

Private Function IsValidData() As Boolean
If Validator.IsPresent(txtStudentNo) AndAlso _
Validator.IsPresent(txtFirstname) AndAlso _
Validator.IsPresent(txtMiddlename) AndAlso _
Validator.IsPresent(txtSurname) AndAlso _
Validator.IsPresent(cboGender) AndAlso _
Validator.IsPresent(dtpDOB) AndAlso _
Validator.IsPresent(txtAddress) AndAlso _
Validator.IsPresent(txtHPhone) AndAlso _
Validator.IsPresent(txtcellphone) AndAlso _
Validator.IsPresent(txtemail) AndAlso _
Validator.IsPresent(txtHomeTown) AndAlso _
Validator.IsPresent(txtRegion) AndAlso _
Validator.IsPresent(PicPhoto) Then

Return True
Return False
End If
End Function

Private Sub PutStudentData(ByVal Student As Student)
Student.StudentNo = txtStudentNo.Text
Student.FirstName = txtFirstname.Text
Student.MiddleName = txtMiddlename.Text
Student.SurName = txtSurname.Text
Student.DOB = dtpDOB.Value
Student.Gender = cboGender.Text
Student.Address = txtAddress.Text
Student.HomePhone = txtHPhone.Text
Student.Cellphone = txtcellphone.Text = txtemail.Text
Student.HomeTown = txtHomeTown.Text
Student.Region = txtRegion.Text
Student.Photo = PicPhoto.Text

End Sub

Private Sub DrawToScale(ByVal bmp As Image)
PicPhoto.Image = New Bitmap(bmp)
End Sub

Private Sub btnBrowse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
OpenFileDialog1.Filter = "Bmp Files(*.bmp)|*.bmp|Gif Files(*.gif)|*.gif|Jpg Files(*.jpg)|*.jpg"
PicPhoto.Image = Image.FromFile(OpenFileDialog1.FileName)

End Try
End Sub

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
'clears the image
If Not (PicPhoto.Image Is Nothing) Then
PicPhoto.Image = Nothing
End If
End Sub

Public Sub Loaddata()

Dim tmeNow As Date = Now

'Get ready to create a random number
Dim strStudentNo As String = "000000"

Dim mls As Integer = tmeNow.Millisecond
' Generate two random numbers between A100 and Z999
Dim rndNumber As New Random(mls)
Dim NewNumber1 As Integer = rndNumber.Next(100, 999)
Dim NewNumber2 As Integer = rndNumber.Next(100, 999)
' Create an Product Code from the random numbers
strStudentNo = NewNumber1.ToString() + "-" + NewNumber2.ToString()

' Display the created item number in the Item # text box
Me.txtStudentNo.Text = strStudentNo

End Try
If (b.Length > 0) Then
Dim stream As New MemoryStream(b, True)
stream.Write(b, 0, b.Length)
DrawToScale(New Bitmap(stream))
End If


End Try
End Sub

Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
End Sub
Private Sub BindCombobox()
With cboGender
End With
End Sub
Posted 25-Feb-13 2:15am
Updated 26-Feb-13 3:33am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

First, you might want to use code blocks to format your question better.

Second: your insert statement uses a table called Sudents. If this isn't a typo in your question you might want to change it to Students (like in the rest of your code)

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 | 2.8.180417.1 | Last Updated 26 Feb 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