Solved

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

Posted on 2011-03-14
17
4,817 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

761 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