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("@email", Student.email)
InsertCommand.Parameters.AddWithValue("@Town", Student.HomeTown)
InsertCommand.Parameters.AddWithValue("@Region", Student.Region)
InsertCommand.Parameters.AddWithValue("@Photo", Student.Photo)
Try
connection.Open()
InsertCommand.ExecuteNonQuery()
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
Finally
connection.Close()
'objDataAdapter.Dispose()
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)
Try
connection.Open()
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
student.email = reader("email").ToString
student.HomeTown = reader("Town").ToString
student.Region = reader("Region").ToString
student.Photo = reader("Photo").ToString
Else
student = Nothing
End If
reader.Close()
Catch ex As SqlException
Throw ex
Finally
connection.Close()
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)
Else
updateCommand.Parameters.AddWithValue("@NewCellPhone", newStudent.Cellphone)
End If
updateCommand.Parameters.AddWithValue("@Newemail", newStudent.email)
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)
Else
updateCommand.Parameters.AddWithValue("@OldCellPhone", oldStudent.Cellphone)
End If
updateCommand.Parameters.AddWithValue("@Oldemail", oldStudent.email)
updateCommand.Parameters.AddWithValue("@OldHomeTown", oldStudent.HomeTown)
updateCommand.Parameters.AddWithValue("@OldRegion", oldStudent.Region)
updateCommand.Parameters.AddWithValue("@OldPhoto", oldStudent.Photo)
Try
connection.Open()
Dim count As Integer = updateCommand.ExecuteNonQuery
If count > 0 Then
Return True
Else
Return False
End If
Catch ex As SqlException
Throw ex
Finally
connection.Close()
End Try
End Function
Public Sub LoadData(ByVal StudentID As Decimal)
Dim connection As SqlConnection = Schoolpayables.GetConnection()
Try
Dim str As String = "SELECT * FROM Students WHERE StudentID = " & StudentID
connection.Open()
Dim cmd As New SqlCommand(str, connection)
Dim b() As Byte
b = cmd.ExecuteScalar()
Catch ex As Exception
'MsgBox(ex.ToString)
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
Me.Loaddata()
Me.BindCombobox()
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))
st.Close()
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
Next
End With
If IsValidData() Then
If addStudent Then
student = New Student
Me.PutStudentData(Student)
Try
Student.StudentID = StudentsDB.AddStudent(Student)
Me.DialogResult = Windows.Forms.DialogResult.OK
Catch ex As Exception
MessageBox.Show(ex.Message, ex.GetType.ToString)
End Try
Else
Dim newStudent As New Student
newStudent.StudentID = Student.StudentID
Me.PutStudentData(newStudent)
Try
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
Else
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
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
Else
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
Student.email = 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
Try
OpenFileDialog1.Filter = "Bmp Files(*.bmp)|*.bmp|Gif Files(*.gif)|*.gif|Jpg Files(*.jpg)|*.jpg"
OpenFileDialog1.ShowDialog()
PicPhoto.Image = Image.FromFile(OpenFileDialog1.FileName)
Catch
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.Dispose()
PicPhoto.Image = Nothing
End If
End Sub
Public Sub Loaddata()
Try
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
Catch
End Try
Try
If (b.Length > 0) Then
Dim stream As New MemoryStream(b, True)
stream.Write(b, 0, b.Length)
DrawToScale(New Bitmap(stream))
stream.Close()
End If
Catch
End Try
End Sub
Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
Me.Close()
End Sub
Private Sub BindCombobox()
With cboGender
.Items.Add("Male")
.Items.Add("Female")
End With
End Sub
Submit an article or tip