Hi
Readers,
Thank you for you emails.I appreciate it.
Here are some tips if you want to use LINK To SQL to select, insert,
delete and update known by CRUD.
I used ASP.NET AJAX web Template for this demo and VS 2008
Here are steps:
1-
Add a new table called Users to Northwind database :
CREATE TABLE [dbo].[Users](
[UserID] [int]
IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Users]
PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2-Create ASP.NET AJAX web app and make sure ScriptMaster is in place(without it
ajax functionality=null).Make also sure that Ajax ToolKit controls are in place.
Drop Tab Container on the design surface with 4 tabs:
-Query Task tab: to query and show result on GridView control
-Insert Task tab : to insert new records .
-Update Task tab: to update records
-Delete Task tab: to delete record.
Copy the source code
Demo.Zip
Here is how it works:
A-
For Query Task:
The goal of this task is to load data into GridView control via a button control:
' This code will query all users where UserName is not null
Dim users=From usr In db.Users _
Where usr.UserName IsNot Nothing _
Select usr
'This code bind users data to GridView control
Me.GridView1.DataSource=users
Me.GridView1.DataBind()
B-
For Insert Task:
'I defined a new User class and i used the column names
'you need With which allows you select column names
'after you type .(dot) inside {} column names will appear automatically
Dim u as New User With {.UserName=me.txtUserName,.PassWord=me.txtPassWord,.Email=me.txtEmail}
'Now submit result on Insert and Update table
db.Users.InsertOnSubmit(u)
db.SubmitChanges()
C-
For Update Task:
'This section is little loosy
'I select users where UserID=upUserID.txt
'You need these parentheses to make it work
'You need to specifiy single meaning you are
'updating only a single row
'match columns again fields
'finally update user table
Dim u=(From usr In db.Users _
Where usr.UserID=me.upUserID.txt _
Select usr).Single
u.UserName=me.upUserName.text
u.PassWord=me.upPassWord.text
u.Email=me.upEmail.text
db.SubmitOnChanges()
D-
Delete Task:
'Tricky too
Dim p1=db.Users.First(Function(p) p.UserID=me.delUserID.text
db.Users.DeleteOnSubmit(p1)
db.SubmitChanges()
Run app and if everything is ok you should see something like this:
