VB.net SQL Not at allowing duplicates in a field

Hi
I use the following VB code to create a SQL table.
How do I change the code so that the column [BANK ID] does not allow duplicates


Sub Create_Table_Payments()

On Error GoTo EH

DeleteTableADO ("PAYMENTS")

Dim sSQL As String

sSQL = "CREATE TABLE [PAYMENTS] "
sSQL = sSQL & "([ID] bigint identity(1,1) not null primary key, [LINK ID] bigint,  "
sSQL = sSQL & "[DATE] datetime, [AMOUNT] decimal(12,2), "
sSQL = sSQL & "[DETAILS] nvarchar(MAX), [PMT TYPE] nvarchar(40), [TRANS TYPE] nvarchar(40),[BANK BAL] decimal(12,2), [BANK ID] bigint)"
'/// NEW
Call oCreate_Online_Table(sSQL)
Exit Sub
EH:
MsgBox Err.Description

End Sub
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
what is considered "a duplicate" in this table?

are you saying that you can only EVER have ONE PAYMENT per bank?
0
PortletPaulEE Topic AdvisorCommented:
just observations,
[DATE] isn't a great field name as the word date used by Tsql,
defining field names that contain spaces is possible but is a nuisance from day 1 (use an underscore perhaps?)
0
James ElliottManaging DirectorCommented:
I would add an index.

CREATE UNIQUE NONCLUSTERED INDEX [nondupe] ON dbo.[PAYMENTS] 
(
	[BANK ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
@PortletPaul said:
are you saying that you can only EVER have ONE PAYMENT per bank?
And if it's that you really want to achieve, why the data type is bigint? How many banks do you have there?
0
PortletPaulEE Topic AdvisorCommented:
LOL
una carga de cubo de los bancos? un cubo grande de los bancos!
0
Anthony PerkinsCommented:
I suspect something got lost in translation or we are confusing Spanish with Portuguese.  :)
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
[BANK ID] stores the unique ID number of a bank transaction so I don't want that transaction  reflecting twice in my payments table
0
CodeCruiserCommented:
Set it to Not Null and Unique

http://www.w3schools.com/sql/sql_unique.asp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
I shall leave this question now, there are many suggestions on how to make a field unique.
Use a message if you think I can contribute meaningfully here.

I just don't think you should make that field unique, it does not make sense.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
[BANK ID] stores the unique ID number of a bank transaction so I don't want that transaction  reflecting twice in my payments table
So, is not really the ID from a bank, right?
If so you should change the column name to TransactionID because like this it's making a lot of confusion to us.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much for stepping in here. I was looking for a simple answer. Apologies to everyone else if I confused the issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.