Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6142
  • Last Modified:

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

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
l0o3
Asked:
l0o3
  • 4
  • 4
  • 4
  • +4
1 Solution
 
santoshmotwaniCommented:
0
 
Pratima PharandeCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mayank_joshiCommented:
Here is a sample code:-
http://www.c-sharpcorner.com/UploadFile/suthish_nair/3869/

Open in new window

0
 
l0o3Author Commented:
i need sample code, with attachment

vb.net and sql server windows application

thank you
0
 
mayank_joshiCommented:
You need to create a login at http://www.c-sharpcorner.com/
to download the sample. Its free!
0
 
l0o3Author Commented:
this is csharp code i need vb.net
0
 
Pratima PharandeCommented:
I have provided code have you tried that
0
 
l0o3Author Commented:
pratima_mcs do u have yahoo id?
0
 
mayank_joshiCommented:

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

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

0
 
Pratima PharandeCommented:
no , why you need that?
0
 
l0o3Author Commented:
yeah, if u have gtalk or yid please give me

tnx
0
 
Pratima PharandeCommented:
sorry I don't have it.

0
 
Mohamed AbowardaSoftware EngineerCommented:
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
 
1303gunCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now