Solved

How to insert / delete /edit using vb.net and sql server

Posted on 2011-03-14
17
4,487 Views
Last Modified: 2012-05-11
Hi guys

i need codes for add/delete and edit records in sql server
vb.net windows application

i need code and sample files

thank you
0
Comment
Question by:l0o3
  • 4
  • 4
  • 4
  • +4
17 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35126711
0
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 35126713
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35126722
Inserting Records

The following code inserts a Record into the Jobs table in Pubs sample database. Drag a button onto the form and place the following code.

Imports System.Data.SqlClient
Public Class Form2 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
'integer holds the number of records inserted
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Insert into Jobs values 12,'IT Manager',100,300,_
myConnection)
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("New Row Inserted" & ra)
myConnection.Close()
End Sub
End Class  


Deleting a Record  

We will use Authors table in Pubs sample database to work with this code. Drag a button onto the form and place the following code.

Imports System.Data.SqlClient
Public Class Form3 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Delete from Authors where city='Oakland'",_
myConnection)
'since no value is returned we use ExecuteNonQuery
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records affected" & ra)
myConnection.Close()
End Sub
End Class  


Updating Records

We will update a row in Authors table. Drag a button onto the form and place the following code.

Imports System.Data.SqlClient
Public Class Form4 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim ra as Integer
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Update Authors Set city='Oakland' where city=_
'San Jose' ",myConnection)
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records affected" & ra)
myConnection.Close()
End Sub
End Class  

0
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35126734
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35126750
Here is a sample code:-
http://www.c-sharpcorner.com/UploadFile/suthish_nair/3869/

Open in new window

0
 

Author Comment

by:l0o3
ID: 35126774
i need sample code, with attachment

vb.net and sql server windows application

thank you
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35126777
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35126786
You need to create a login at http://www.c-sharpcorner.com/
to download the sample. Its free!
0
 

Author Comment

by:l0o3
ID: 35126810
this is csharp code i need vb.net
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35126812
I have provided code have you tried that
0
 

Author Comment

by:l0o3
ID: 35126841
pratima_mcs do u have yahoo id?
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127017

the following site can convert c# code into vb.net:-

http://www.developerfusion.com/tools/convert/vb-to-csharp/

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35127088
no , why you need that?
0
 

Author Comment

by:l0o3
ID: 35127097
yeah, if u have gtalk or yid please give me

tnx
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35127107
sorry I don't have it.

0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 35128826
First you have to learn SQL:
http://www.w3schools.com/sql/default.asp

Using SQL Server to Insert, Delete, Update records in VB.NET:
http://www.startvbdotnet.com/ado/sqlserver1.aspx
0
 
LVL 3

Accepted Solution

by:
1303gun earned 500 total points
ID: 35129362
Hello.

I have made I small sample project for you. It is attached.

In order for the project to run, you have to create the database and modify the connection strings in the application's resource section (My Project > Resources).

I don't know wich version of SQL Server you use and what is you knowledge about SQL Server or the SQL language, but inside the attached ZIP file there is a file named DBSample.sql. It is the SQL script you can use to create the database.

You can also create the database manually. Just create it and add a table named Users with the following columns:

UserId (uniqueidentifier) - Primary Key
Name (nvarchar(MAX))
Email (nvarchar(MAX))
Phone (nvarchar(MAX))

Then you have to modify the connection string in the application's resource section (My Project > Resources).

If you created a database and attached it to the server, the connection string must be like this:

Data Souce=SERVERNAME\INSTANCE; Initial Catalog=DATABASENAME; User ID=USERID; Password=PASSWORD

Replace SERVERNAME\INSTANCE by the server name (your computer's name) and instance name of your SQL Server installation (if you use the Express edition, the default instance name is SQLEXPRESS, so you can use .\SQLEXPRESS or [COMPUTERNAME]\SQLEXPRESS, where [COMPUTERNAME] is the name of your computer), DATABASENAME by the name of the database you created (if you used the SQL Script I've provided, replace by DBSample) and USERID and PASSWORD by your SQL Server login and password. If you don't know the login and password of your SQL Server, the following connection string will probably work (Replace the other info also):

Data Souce=SERVERNAME\INSTANCE; Initial Catalog=DATABASENAME; Integrated Security=True

If you created the database as an MDF file, you must use the following connection string:

Data Source=SERVERNAME\INSTANCE;AttachDbFilename=[DATABASEFILENAME];Integrated Security=True;Connect Timeout=30;User Instance=True

Replace the SERVERNAME\INSTANCE with the applicable information and DATABASEFILENAME with the full path of your database file (like C:\Users\ABC\Documents\DBExample\DBSample.mdf).

This should be enough for the application to work. The code is fully commented, so you should be able to understand it well. The project was made in Visual Studio 2010.

Note: If someone is doubting why I stored the connection string on the resource section rather than the settings section, its because ExpertsExchange does not allow me to attach files with .settings extension, wich is the main settings file of the project. So I had to do this workaroud.


DBExample.zip
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question