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

Jx_Man 1 Tallied Votes 21K Views Share

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
kimhanu -3 Newbie Poster

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

Jx_Man 987 Nearly a Senior Poster Featured Poster

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

dlayante 0 Newbie Poster

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

muhammad.atif 0 Newbie Poster

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

Piya27 4 Junior Poster

thanks.. it helped a lot...

dapsin999 0 Newbie Poster

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

Jx_Man 987 Nearly a Senior Poster Featured Poster

Yes..you can

smdhas 0 Newbie Poster

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

Nick Evan 4,005 Industrious Poster Team Colleague Featured Poster

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

smdhas 0 Newbie Poster

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....

smdhas 0 Newbie Poster

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.

jaifox 0 Newbie Poster

wazzup

slamthedeck 0 Newbie Poster

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

Rshekdar 0 Newbie Poster

@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)

Jx_Man 987 Nearly a Senior Poster Featured Poster

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

Rshekdar 0 Newbie Poster
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()

Rshekdar 0 Newbie Poster

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?

ronie_redhat 0 Newbie Poster

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

ronie_redhat 0 Newbie Poster

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

akuvidz 0 Newbie Poster

hi,, i wanna ask u,, why couldn't i put this code

Me.Bengkel1DataSet.Fill(Me.Bengkel1DataSet.Staff)

the Me.Bengkel1Dataset.Fill say that Fill is not a member of Bengkel1..

what do i supposed to do,,

abhishekroy2jan 0 Newbie Poster

awesome code ..it works

iqlas 0 Newbie Poster

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.

thank you ........ sir i was searching for this code for one week and atlast i got it from you....

well i need to retrieve the data from database ... pls post another code for this...

also if you could make your code compact... pls hlp me....
thanx in advance

swedha 0 Newbie Poster

sir i need connection of dataset step..can u give

Leodumbi 0 Light Poster

@jx man: you've been able to teach me more than the person Ive pay to teach me the same thing. thank you very much, and may GOD ccontinue to lighten your way through life.

)ne quick Question: It just allow me to enter one record. looks like the line:

If (dtStudent.Rows.Count > 0) Then

is making things harder. can you give some tips, with the same easy-to-understand way you always do?
Thank you

Mike15 0 Newbie Poster

This is a big help on my project. But i have a question..

How to treat if im retrieving in database is not in there or the value is null..

i can save,edit, and delte already

but if the given value to delte is not in the Databse im having error..
"Index out of range'

pls help me thanks

pls can u email me at
mike_lhester15@yahoo.com

karthick.M 0 Newbie Poster

very nice coding ..very helpfull to students

LearnVBnet 0 Light Poster

Very HelpFull
I Use to my Invoice Form, and can insert with a new form
How can if my Invoice form have e. 3 row with productName, quantity, price, total
And then I add new 2 record to my datagridview.
If I click btnAdd there found Id then not save, If no msgbox, then my record double insert


For i As Integer = 0 To DGV.Rows.Count - 2
cmdInv.CommandText = "SELECT * FROM InvoiceDetail WHERE InvNomer='" & Trim(txtInvNomer.Text) & " ' "
daInv.SelectCommand = cmdInv
daInv.Fill(dsInv, "InvoiceDetail")
dtInv = dsInv.Tables("InvoiceDetail")

If (dtInv.Rows.Count > 0) Then
' MsgBox("Invoice with InvoiceNomer" & Trim(txtInvNomer.Text) & " already in database", MsgBoxStyle.OkOnly, "Message :")
Else

cmdInv1 = mConn.CreateCommand
cmdInv1.CommandText = "Insert into InvoiceDetail (ProductKey,ProductCode,Quantity ,Description,UnitPrice,Extention,InvNomer) values " & _
"('" & Convert.ToInt32(DGV.Rows(i).Cells(6).Value) & "','" & DGV.Rows(i).Cells(0).Value & "','" & DGV.Rows(i).Cells(1).Value & "','" & DGV.Rows(i).Cells(0).Value & "','" & DGV.Rows(i).Cells(2).Value & "','" & DGV.Rows(i).Cells(3).Value & "','" & txtInvNomer.Text & "')"
check = cmdInv1.ExecuteReader.RecordsAffected()
Please help.
thanks

yanize 0 Newbie Poster

your code really helped me a lot.. but i have a problem.. the new data is added on the datagrid on run time but when i check my database it's still not there. what is the problem with it?? thanks..

miramiey 0 Newbie Poster

thanks for the code. its help me..but i have a problem with refresh form(). my update and delete is successful but when the refresh is call...error will popup

Error: System.Windows.Forms: Child list for field Student cannot be created.

May i know why?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.