Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
Posted 25-Feb-13 3:15am
kingsoh365
Edited 26-Feb-13 4:33am
v2

1 solution

Rate this: bad
good
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)
  Permalink  

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

  Print Answers RSS
0 DamithSL 440
1 OriginalGriff 194
2 BillWoodruff 170
3 Zoltán Zörgő 165
4 Maciej Los 163
0 OriginalGriff 7,934
1 DamithSL 6,084
2 Sergey Alexandrovich Kryukov 5,449
3 Maciej Los 5,174
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 26 Feb 2013
Copyright © CodeProject, 1999-2014
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