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.
Save, Edit, Delete Data using VB.Net and SQL Server 2000
' 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...
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) Thenis 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.