?
Solved

VB.net SQL Not at allowing duplicates in a field

Posted on 2014-10-02
11
Medium Priority
?
118 Views
Last Modified: 2014-10-07
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
0
Comment
Question by:Murray Brown
[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
  • 2
  • 2
  • +3
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40358784
what is considered "a duplicate" in this table?

are you saying that you can only EVER have ONE PAYMENT per bank?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40358798
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
 
LVL 12

Expert Comment

by:James Elliott
ID: 40358818
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40358837
@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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40358846
LOL
una carga de cubo de los bancos? un cubo grande de los bancos!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40362754
I suspect something got lost in translation or we are confusing Spanish with Portuguese.  :)
0
 

Author Comment

by:Murray Brown
ID: 40364520
[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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 40364550
Set it to Not Null and Unique

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

Expert Comment

by:PortletPaul
ID: 40365170
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
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40365322
[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
 

Author Closing Comment

by:Murray Brown
ID: 40365388
Thanks very much for stepping in here. I was looking for a simple answer. Apologies to everyone else if I confused the issue.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 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