1.11M Members

Save, Edit, Delete Data using VB.Net and SQL Server 2000

 
0
 

This code to save, Edit and delete data in VB.Net using SQLServer as backend. this code is continuance from my previous post "Show Data in DataGrid with VB.Net 2003 and SQLServer 2000". so i didn't write code how to connect SQLServer 2000 with VB.Net 2003 cause this already in there.
Please see and read a comment on code carefully so this code can implement goods.

' Programmed By Jery M
 'this Following code shows how to Save, Edit, Delete Data using VB.Net and SQL Server 2000 as database.
 'this code needed some control :
 'a database with 4 column (Id[primary key],FirstName,LastName,Age)
 '3 button (cmdSave,cmdEdit,cmdDelete)
 '4 text box (txtId,txtFirstName,txtLastName,txtAge).
 '1 datagrid (named dgStudent)

 ' This Proceduere to refresh form and refresh data in datagrid (always show the newest data)
 Private Sub Refresh_Form()
        Dim conn As SqlConnection
        Dim cmdStudent As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet
        Dim dtStudent As New DataTable

	'clear all textbox
        txtId.Text = ""
        txtFirstName.Text = ""
        txtLastName.Text = ""
        txtAge.Text = ""
        
	'this part to call data from database and show in datagrid
        conn = GetConnect()
        Try
            cmdStudent = conn.CreateCommand
            cmdStudent.CommandText = "SELECT * FROM Student"
            daStudent.SelectCommand = cmdStudent
            daStudent.Fill(dsStudent, "Student")
            dgStudent.DataSource = dsStudent
            dgStudent.DataMember = "Student"
            dgStudent.ReadOnly = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
        End Try
End Sub

  Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim check As Integer
        Dim conn As SqlConnection
        Dim cmdStudent As New SqlCommand
        Dim cmdStudent1 As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet
        Dim dtStudent As New DataTable

        If txtId.text = "" Or txtFirstName.Text = "" txtLastName.Text = "" Or txtAge.Text = "" Then
            MsgBox("Student Data is not completed", MsgBoxStyle.OKOnly)
        Else
            If MsgBox("Are you sure to save Student data with Id : " & txtId.text & " ?", MsgBoxStyle.OKCancel, "Input confirm") = MsgBoxResult.Cancel Then
                ' do nothing
            Else
                Try
                    conn = GetConnect()
                    conn.Open()
                    cmdStudent = conn.CreateCommand
                    cmdStudent.CommandText = "SELECT * FROM Student WHERE Id='" & Trim(txtId.text) & " ' "
                    daStudent.SelectCommand = cmdStudent
                    daStudent.Fill(dsStudent, "Student")
                    dtStudent = dsStudent.Tables("Student")

                    If (dtStudent.Rows.Count > 0) Then
                        MsgBox("Student dengan Id " & Trim(cmbId.Text) & " already in database", MsgBoxStyle.OKOnly, "Message :")
                    Else
                        
                        cmdStudent1 = conn.CreateCommand
                        cmdStudent1.CommandText = "INSERT INTO Student(Id, FirstName, LastName,Age) VALUES('" & Trim(txtId.text) & "','" & Trim(txtFirstName.Text) & "','" & Trim(txtLastName.Text) & "','" & Trim(txtAge.Text) & "')"
                        check = cmdStudent1.ExecuteReader.RecordsAffected()
                        If check > 0 Then
                            MsgBox("Student With Id " & Trim(cmbId.Text) & " succesfully to added", MsgBoxStyle.OKOnly, "Message :")
                        Else
                            MsgBox("Student With Id " & Trim(cmbId.Text) & " Failure  to added", MsgBoxStyle.OKOnly, "Message :")
                        End If
                        Refresh_Form()
                        conn.Close()

                    End If

                Catch ex As Exception
                    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
                End Try
            End If
        End If
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Dim check As Integer
        Dim cmdStudent As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet

        If txtId.text = "" Then
            MessageBox.Show("Please fill all data!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            If txtId.text = "" Or txtFirstName.Text = "" txtLastName.Text = "" Or txtAge.Text = "" Then
			MsgBox("Student Data is not completed", MsgBoxStyle.OKOnly)
		Else
                If MsgBox("Are you sure to edit Student data with Id : " & txtId.text & " ?", MsgBoxStyle.OKCancel, "Edit confirm") = MsgBoxResult.Cancel Then
                    ' do nothing
                Else
                    Try
                        conn = GetConnect()
                        conn.Open()
                        cmdStudent = conn.CreateCommand
                        cmdStudent.CommandText = "UPDATE Student SET FirstName ='" & Trim(txtFirstName.Text) & "', LastName= '" & Trim(txtLastName.Text) & "' , Age='" & Trim(txtAge.Text) & "' WHERE Id ='" & Trim(txtId.text) & "'"
                        check = cmdStudent.ExecuteReader.RecordsAffected
                        If check > 0 Then
                            MsgBox("Student With Id " & Trim(txtId.text) & " Succesfully To Edit", MsgBoxStyle.OKOnly, "Info Update Data Student ")
                        Else
                            MsgBox("Student With Id " & Trim(txtId.text) & " Failure To Edit", MsgBoxStyle.OKOnly, "Info Update Data Student ")
                        End If
                        Refresh_Form()
                        conn.Close()

                    Catch ex As Exception
                        MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
                    End Try
                End If
            End If
        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim check As Integer
        Dim conn As SqlConnection
        Dim cmdStudent As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet

        If txtId.text <> "" Then
            If MsgBox("Are you sure to delete data with Id : " & txtId.text & " ?", MsgBoxStyle.OKCancel, "Delete confirm") = MsgBoxResult.Cancel Then
                ' do nothing
            Else
                conn = GetConnect()
                Try
                    conn.Open()
                    cmdStudent = conn.CreateCommand
                    cmdStudent.CommandText = "DELETE FROM Student WHERE Id ='" & Trim(txtId.text) & "'"
                    check = cmdStudent.ExecuteReader.RecordsAffected
                    If check > 0 Then
                        MsgBox("Student With Id " & Trim(txtId.text) & " Succesfully To Delete", MsgBoxStyle.OKOnly, "Info Delete Student")
                    Else
                        MsgBox("Student With Id " & Trim(txtId.text) & " Failure To Delete", MsgBoxStyle.OKOnly, "Info Delete Student")
                    End If
                    Refresh_Form()
                    conn.Close()

                Catch ex As Exception
                    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
                End Try
            End If
        Else
                MsgBox("fill Id Student on Id textbox which student to delete!!", MsgBoxStyle.OKOnly, "Info Data")
            End If
    End Sub
 
0
 

what is conn = GetConnect() ?
please give the code of GetConnect() .

 
0
 

This code is exactly what i need but can you give a code of this using C# windows application
THANX...

 
0
 

how i can achiveve the same goal if i first save all input into dataset then all the data of dataset into database.

 
0
 

thanks.. it helped a lot...

 
0
 

Pls i am new to vb.net. Can this code help me as a beginner to save data in a datagrid in sql server unsing vs2008 and vb.net language. Thanks
dapsin

 
0
 

Yes..you can

 
0
 

very useful code...however would appreciate if you cud post snapshots which wud b more helpful.
thanks...

 
0
 

@ smdhas: At the time that this snippet was created, (> a year ago) it wasn't possible yet to add screenshots to a snippet.

 
0
 

sorry for askin dumb questions...what is the refresh_form()...
and how do u call the form to load again when hitting the save button....

 
0
 

hi...manage to work ur code..thanks alot...
instead of add/edit/delete through textboxes in the form i want to add/edit/save/delete directly in the grid view and just have the buttons on the form.

 
0
 

wazzup

 
0
 

I know I'm reviving an old thread, but I can't for the life of me figure out where 'cmbId' comes from.

 
0
 

@jx man, thanks a lot for this wonderfull code. Its helped me a lot. Really appriciate it. Love this site.(sorry if am being off topic i just came to this site today)

 
0
 

@Rshekdar : you're welcome my friend...yes this site is wonderful..just find great time here..enjoy it :)

 
0
 
str_command = "insert into CCDetails (cckeY , storerkey , sku , lot , loc , sysid , ccid , sysqty , ccqty , cceditdate , cceditwho, adddate , addwho , editdate , editwho) " + _
"VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , getdate() , 'hp_dbo' , getdate() , 'hp_dbo' , getdate() , 'hp_dbo' )"	
    update_cmd = New SqlCommand(str_command, update_con)
                    update_cmd.Parameters.Add("cckey", Data.SqlDbType.NChar, 10)
                    update_cmd.Parameters.Add("storerkey", Data.SqlDbType.NChar, 10)
                    update_cmd.Parameters.Add("sku", Data.SqlDbType.NChar, 20)
                    update_cmd.Parameters.Add("lot", Data.SqlDbType.NChar, 10)
                    update_cmd.Parameters.Add("loc", Data.SqlDbType.NChar, 10)
                    update_cmd.Parameters.Add("sysid", Data.SqlDbType.NChar, 18)
                    update_cmd.Parameters.Add("ccid", Data.SqlDbType.NChar, 18)
                    update_cmd.Parameters.Add("sysqty", Data.SqlDbType.Int)
                    update_cmd.Parameters.Add("ccqty", Data.SqlDbType.Int)
                    'update_cmd.Parameters.Add("cceditdate", Data.SqlDbType.DateTime)
                    'update_cmd.Parameters.Add("cceditwho", Data.SqlDbType.NChar, 18)
                    'update_cmd.Parameters.Add("adddate", Data.SqlDbType.DateTime)
                    'update_cmd.Parameters.Add("addwho", Data.SqlDbType.NChar, 18)
                    'update_cmd.Parameters.Add("editdate", Data.SqlDbType.DateTime)
                    'update_cmd.Parameters.Add("editwho", Data.SqlDbType.NChar, 18)
                    'cmdIns.Parameters("MainLoc").Value = Me.cboHosp.Text

                    update_cmd.Parameters("cckey").Value = ccount_number
                    update_cmd.Parameters("storerkey").Value = TxtStorer.Text
                    update_cmd.Parameters("sku").Value = TxtPart.Text
                    update_cmd.Parameters("lot").Value = ""
                    update_cmd.Parameters("loc").Value = TxtLoc.Text
                    update_cmd.Parameters("sysid").Value = TxtPltid.Text
                    update_cmd.Parameters("ccid").Value = TxtPltid.Text
                    update_cmd.Parameters("sysqty").Value = CInt(TxtQty.Text)
                    update_cmd.Parameters("ccqty").Value = CInt(TxtQty.Text)
                    'update_cmd.Parameters("cceditdate").Value = Now()
                    'update_cmd.Parameters("cceditwho").Value = s_username
                    'update_cmd.Parameters("adddate").Value = Now()
                    'update_cmd.Parameters("addwho").Value = s_username
                    'update_cmd.Parameters("editdate").Value = Now()
                    'update_cmd.Parameters("editwho").Value = s_username

                    update_con.Open()

                    Dim iretvalue As Integer
                    iretvalue = update_cmd.ExecuteNonQuery()

                    MsgBox(iretvalue.ToString)

                    update_cmd.Connection.Close()
                    update_cmd.Dispose()
                    TxtLoc.Select(0, Len(TxtLoc.Text))
                    TxtPltid.Text = ""
                    TxtPart.Text = ""
                    TxtStorer.Text = ""
                    TxtQty.Text = ""
                    update_con.Close()
                    update_cmd.Dispose()

could someone point out whats the mistake in this code above? I keep getting this exception. Earlier i had all values passed in as paramters, but kept getting errors so i got rid of the datetime ones and the default values and this is what is left still same error. Can anyone help out?
at System.Data.SqlClient.SqlConnection.OnError()
at System.Data.SqlClient.SqlInternalConnection.OnError()
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

 
0
 

I know I'm reviving an old thread, but I can't for the life of me figure out where 'cmbId' comes from.

guess its just a simple textbox or combobox on the form. cmbid might stand for combo(dropdown) of id's (student id's) Does this ans ur query? or i got your question totally wrong?

 
0
 

Imports System.Data
Imports System.Data.SqlClient

Module Koneksi
Public conn As SqlConnection
Public Function GetConnect()
conn = New SqlConnection("server = MyServerName;database = MyDatabaseName;Trusted_Connection = yes")
Return conn
End Function
End Module


'getConnect is a function to make a connection with database server

 
0
 

what is conn = GetConnect() ?
please give the code of GetConnect() .

is a function that saved in module, it used for make a connection to database server

Imports System.Data
Imports System.Data.SqlClient

Module Koneksi
Public conn As SqlConnection
Public Function GetConnect()
conn = New SqlConnection("server = MyServerName;database = MyDatabaseName;Trusted_Connection = yes")
Return conn
End Function
End Module

Isn't it about time forums rewarded their contributors?

Earn rewards points for helping others. Gain kudos. Cash out. Get better answers yourself.

It's as simple as contributing editorial or replying to discussions labeled or OP Kudos

You
This is an OP Kudos discussion and contributors may be rewarded
Post:
Start New Discussion
Tags Related to this Article