Solved

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

Posted on 2011-03-14
17
4,159 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now