1,105,380 Community Members

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

Member Avatar
Reputation Points: 987 [?]
Q&As Helped to Solve: 542 [?]
Skill Endorsements: 74 [?]
 
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
Member Avatar
kimhanu
Newbie Poster
15 posts since Mar 2008
Reputation Points: -3 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Jx_Man
Senior Poster
3,543 posts since Nov 2007
Reputation Points: 987 [?]
Q&As Helped to Solve: 542 [?]
Skill Endorsements: 74 [?]
Featured
 
0
 

as i tell before post 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.
Click this lick to go to my previous snippet :
Show Data in DataGrid with VB.Net 2003 and SQLServer 2000

Member Avatar
dlayante
Newbie Poster
11 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
muhammad.atif
Newbie Poster
3 posts since Nov 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Piya27
Junior Poster
112 posts since Jun 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 9 [?]
Skill Endorsements: 0 [?]
 
0
 

thanks.. it helped a lot...

Member Avatar
dapsin999
Newbie Poster
6 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
Jx_Man
Senior Poster
3,543 posts since Nov 2007
Reputation Points: 987 [?]
Q&As Helped to Solve: 542 [?]
Skill Endorsements: 74 [?]
Featured
 
0
 

Yes..you can

Member Avatar
smdhas
Newbie Poster
6 posts since Nov 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Nick Evan
Industrious Poster
4,827 posts since Oct 2006
Reputation Points: 4,005 [?]
Q&As Helped to Solve: 560 [?]
Skill Endorsements: 30 [?]
Team Colleague
Featured
 
0
 

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

Member Avatar
smdhas
Newbie Poster
6 posts since Nov 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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....

Member Avatar
smdhas
Newbie Poster
6 posts since Nov 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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.

Member Avatar
jaifox
Newbie Poster
1 post since Apr 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

wazzup

Member Avatar
slamthedeck
Newbie Poster
1 post since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Rshekdar
Newbie Poster
3 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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)

Member Avatar
Jx_Man
Senior Poster
3,543 posts since Nov 2007
Reputation Points: 987 [?]
Q&As Helped to Solve: 542 [?]
Skill Endorsements: 74 [?]
Featured
 
0
 

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

Member Avatar
Rshekdar
Newbie Poster
3 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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()

Member Avatar
Rshekdar
Newbie Poster
3 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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?

Member Avatar
ronie_redhat
Newbie Poster
2 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
ronie_redhat
Newbie Poster
2 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

You
Post:
Start New Discussion
Tags Related to this Article