Solved

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

Posted on 2011-03-14
17
5,284 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 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
Technology Partners: 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 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

632 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