Create the table
In the SQL database, create the contact table
Go to your database and insert a new stored procedure.
Copy the code below in this procedure and click the
"Execute" button.
Code for creating table Contact
USE [yentel]
GO
/****** Object: Table [dbo].[Contact] Script Date: 08/10/2011 18:52:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contact](
[City] [varchar](100) NULL,
[Address] [varchar](100) NULL,
[Name] [varchar](100) NULL,
[IDContact] [varchar](38) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contact] ADD CONSTRAINT [UK_ContactIDContact]
DEFAULT (newid()) FOR [IDContact]
GO
The last instruction will automatically create a new
IDContact for you for new contacts.
Insert some data in this table
Pressing the exclamation symbol will refresh the table and
the new ID's will be shown..
Adding data to your table can also be done using Access.
Open Access
From the File menu, select new.
On the right side, select "Project using existing
data"
Give it a name
And click the create button.
In the next window, insert the database URL and your
credentials.
And click OK.
Click Save Password is the easiest way.
Another way for creating the table is using TSQL in a bat
file.
Open Notepad.
Insert the code below
Code for TSQL batch file
Print 'START(A)'
GO 1
If not EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Contact'
)
BEGIN
CREATE TABLE [dbo].[Contact] (
[City] [varchar] (100) NULL ,
[Address] [varchar] (100) NULL ,
[Name] [varchar] (100) NULL ,
[IDContact] [varchar] (38) NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Contact] WITH NOCHECK ADD
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED
(
[IDContact]
) ON [PRIMARY]
END
ALTER TABLE [dbo].[Contact] ADD
CONSTRAINT [UK_ContactIDContact] DEFAULT (newid()) FOR
[IDContact]
CREATE INDEX [IX_Contact] ON [dbo].[Contact]([IDContact]) ON
[PRIMARY]
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
PRINT ''
GO
Save this file on disk as "O_Contact.txt".
Create a new text file containing the code below
Code for TSQL batch file
OSQL.EXE -U UserName -P Password -S sql1.vaisulweb.it
-i C:\Frans\O_Contact.txt -o C:\Frans\O_Contact.out -d Yentel
And save it to disk as contact.bat.
It contains
OSQL.EXE -U USERNAME -P Password -S yourURL -i SOURCE -o DESTINATION -d DATABASE
Now when you double click the bat file, your txt file will
be executed. The "O_Contact.out" will contain the result. You can
open it in Notepad.
You can use this structure for all kinds of database
commands; create tables, create or delete stored procedures etc.
Now that the table is created, we need a stored procedure to
get the information from the contact table in our project.
Stored procedures
Create the Contact stored
procedure
CREATE PROCEDURE O_GetS_ContactMaster
AS
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON SELECT
Contact.IDContact AS IDContact,
Contact.City AS City,
Contact.Address AS Address,
Contact.Name AS Name
FROM Contact
ORDER BY Name
GO
(You can insert this text into the O_Contact.txt file and
click the same bat file)
This was the preparation. Now we need to add our datagrid
and our Servicereference.
