CODEWITHSUNDEEP
sql-servervb.netdatagridview
Louis Royster
Louis Royster

Reputation: 121

VB.net data binding

Hey this is an homework assignment and I just cannot figure it out. Any help would be great!

Create a SQL Server database named "people" that has the following table. Please use Visual Studio 2012 and create a local service based database

people_id int PK identity firstname varchar(30) lastname varchar(40)

Create a Windows forms program that lists the id, firstname and lastname in a datagridview

Provide functionality (in code using command object and parameters, no wizards) to add, update and delete from the database table and reflect the changes in the datagridview.

This is the code I already have. I can run my program and the data will show up in the datagridview, but whenever I go to add update or delete data nothing happens. I am not throwing errors or anything. I have no idea how to fix this, please help!!

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
    Me.PeopleTableAdapter.Fill(Me.PeopleDataSet.people)
End Sub

Private Function getConnString() As String
    Return My.Settings.PeopleConnectionString.ToString()
End Function

Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
    Me.Close()
End Sub

Private Function getConnection() As String
    Return My.Settings.PeopleConnectionString.ToString()
End Function

Private Function getPeople(lName As String) As DataTable
    Dim sql As String
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim da As SqlDataAdapter
    Dim dt As New DataTable
    Dim plName As New SqlParameter
    plName.ParameterName = "@lName"
    plName.Value = lName
    sql = "select * from people where lName = @lName"
    conn = New SqlConnection(getConnection())
    conn.Open()
    cmd = New SqlCommand(sql, conn)
    cmd.Parameters.Add(plName)
    da = New SqlDataAdapter(cmd)
    da.Fill(dt)
    conn.Close()
    Return dt
End Function

Private Sub AddPerson(fname As String, lname As String, person_ID As Integer)
    Dim sql As String
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    sql = "insert into people(fName, lName, person_ID) values("
    sql += "@fName, @lName, @person_ID)"
    conn = New SqlConnection(getConnection())
    conn.Open()
    cmd = New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@fName", fname)
    cmd.Parameters.AddWithValue("@lName", lname)
    cmd.Parameters.AddWithValue("@person_ID", person_ID)
    cmd.ExecuteNonQuery()
    conn.Close()
End Sub

Private Sub btnADD_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
    Try
        AddPerson(txtfName.Text, txtlName.Text, txtpID.Text)
        MessageBox.Show("Added")
    Catch ex As Exception
        MessageBox.Show("Error" & ex.Message)
    End Try
End Sub

Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
    If DataGridView1.SelectedRows.Count > 0 Then
        Dim dr As DataGridViewRow = DataGridView1.SelectedRows(0)
        txtfName.Text = dr.Cells(1).Value.ToString()
        txtlName.Text = dr.Cells(2).Value.ToString()
        txtpID.Text = dr.Cells(0).Value.ToString()
    End If
End Sub

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
    Dim sql As String
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    sql = "update people set fName=@fName, lName=@lName"
    sql += " where person_ID=@person_ID"
    conn = New SqlConnection(getConnection())
    conn.Open()
    cmd = New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@fName", txtfName.Text)
    cmd.Parameters.AddWithValue("@lName", txtlName.Text)
    cmd.Parameters.AddWithValue("@person_ID", txtpID.Text)
    cmd.ExecuteNonQuery()
    conn.Close()
End Sub

Private Sub btnView_Click(sender As Object, e As EventArgs) Handles btnView.Click
    Dim sql As String
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    sql = "delete from people where person_ID=@person_ID"
    conn = New SqlConnection(getConnection())
    conn.Open()
    cmd = New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@person_ID", txtpID.Text)
    cmd.ExecuteNonQuery()
    conn.Close()

End Sub
End Class

Upvotes: 0

Views: 1440

Answers (1)

Koi Tsang
Koi Tsang

Reputation: 44

Sorry for I didn't have 50 reputation.

For any connection, u can not forecast the database is running or not, so you must type a try catch and return a message to user/ support to let them know what happen in this issue, for Exception type in vb, please check as below: Introduction to Exception Handling in Visual Basic .NET

Please using 

try{
    ' your action, connection or sql exe
    ' example using your update event
    Dim sql As String
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    sql = "update people set fName=@fName, lName=@lName"
    sql += " where person_ID=@person_ID"
    conn = New SqlConnection(getConnection())
    conn.Open()
    cmd = New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@fName", txtfName.Text)
    cmd.Parameters.AddWithValue("@lName", txtlName.Text)
    cmd.Parameters.AddWithValue("@person_ID", txtpID.Text)
    cmd.ExecuteNonQuery()
    conn.Close()
}catch(Exception e){
    ' print the error message from here to let u know the function/ event error or not
    ' check the exception message
    ' MsgBox(e.ToString())
    conn.Close()
} finally {
    conn.Close()
}

to check is that the button event was error or not.

Also I always using 

MsgBox("Alert")

to help me to check the function or event has been run or not.

Hope help.

one more thing... Please type the connection as class so u don't have to type it in so many times.

Upvotes: 1

Related Questions