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
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
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?)
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
@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?
LOL
una carga de cubo de los bancos? un cubo grande de los bancos!
una carga de cubo de los bancos? un cubo grande de los bancos!
I suspect something got lost in translation or we are confusing Spanish with Portuguese. :)
ASKER
[BANK ID] stores the unique ID number of a bank transaction so I don't want that transaction reflecting twice in my payments table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
[BANK ID] stores the unique ID number of a bank transaction so I don't want that transaction reflecting twice in my payments tableSo, 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.
ASKER
Thanks very much for stepping in here. I was looking for a simple answer. Apologies to everyone else if I confused the issue.
are you saying that you can only EVER have ONE PAYMENT per bank?