Checking for duplicate row before inserting

I need to know how to check/compareall fields in a row based on Cust Id before inserting a new record. As it is right now, if the Add/Update button is pressed continually, it increments the record number and adds a duplicate record. And you know you'll never stop people from double clicking a button that only needs to be clicked once.

I thought about just disabling the Update button after the insert occurs, then re-enable it if they go back and edit a field, but that's not a solid solution.I'd do it in your stored procedure.

You've got something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
GO

I'd change it to something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
IF EXISTS
 (SELECT CustID FROM CustTable
 WHERE SomeField = @.OneVal AND OtherField = @.OtherVal)
 BEGIN
 RETURN
 END
ELSE
 BEGIN
 INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
 END
GO

That's pretty simple, obviously...You'll likely want to return something if a match is found, or even if the new record gets inserted...But it shows you the basics, I hope.

Regards,

Xander|||That looks like what I'm looking for. Since the add buttons click event runs 5 functions to insert data into other tables; if I call the SP in the first function, that should intercept the insertion process all together and break right?

InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

I suppose that I could code a boolean to catch if the SP returns a duplicate using True/False, then use it to display a message in my errmsg label; notifying the user that they have attempted to store a duplicate record.

Thanks for the help, any other info would be greatly appreciated.
PD|||InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

No, the fact that a record wasn't inserted will not, in and of itself prevent the other functions from firing.

You've got the idea though...If you pass a boolean value back, you can use it to decide whether to fire the other functions or not.

You could pass an output parameter from the stored procedure, for instance, or return a scalar value that lets you know what the stored procedure did, and wrap your four subsequent functions in an IF block, (perhaps even inside the first function on successful insertion?) and that should give you what you want.|||I got it to work, but it does prevent the updating of the fields that are not part of the SP in one of my functions (Strange).

I am going to check it out. I thought about appending the table to store all the field data and re-code the SP to check all the fields, fire it first, and then let it yield to the other functions if no duplicate exists.

Thanks.|||Well, it has turned into a proverbial nightmare. If I yield to the other insert functions from my SP, then duplicate records can be created in those tables. So I guess I am going to have to write SP's for each of those functions to prevent duplicates there as well.

I inherited this app a couple of months ago from an outside firm, and what a mess it has been.

Checkbox in reports

I have a report on which i want to add a checkbox to the extreme left of
every row and allow the user the ability to select it, hit a button named
"Export" and move the contents of only that row to MS Excel.
Does Reporting services 2000 support this? Any suggestions on how this can
be done?
Thanks...uh; i'd start with 'not trying to use excel for reporting'
-Aaron
Gaurav wrote:
> I have a report on which i want to add a checkbox to the extreme left of
> every row and allow the user the ability to select it, hit a button named
> "Export" and move the contents of only that row to MS Excel.
> Does Reporting services 2000 support this? Any suggestions on how this can
> be done?
> Thanks...

Check SQL syntax

First of all, hello and good morning, my question is, you can check SQL syntax in SQL Server with secondary button mouse or "Check SQL" button in toolbar (Microsoft Management Console 1.2).
Id like to know if theres a way to use these Server tools from Visual Basic 6 SP6, something like APIs ...
If theres no solution, can anybody give me an idea of how to check SQL syntax in VB.
The application wants the users to make their own SQL sentences, (they just can write whatever they want ???)Ive solved it with transactions, commit and rollback.
Thanks for your patience|||Hi pucca,

Could u kindly explain how u done it. Atleast briefly when u r free...

thnks in advance

With regards
Sudar|||because I noticed that I hadnt to check the sintax, I just had to wait VB would do the SQL Statement, if it couldnt do it then the sentence the user wrote was wrong or there were constraints in the server side.
Sorry for my english, Ill try to explain the situation.
The application has a form called frmConsultasCampanya where the users can see the stored consults and manage them (add new, delete, modify ...), the users just write the consult as they need, so we can find some consults that return the same results, and as these data are stored in other table the application can stop execution. My idea was to check the consult before its execution, and if everything was right execute it, but I realized that I could check the syntax, not the logica.
I had heard of transactions, and I discovered you could do it in vb 6.
My solution is the following:

Public Function ejecutar_consulta() As Long
Dim cmd As Command
Dim rs As Recordset
Dim rs_2 As Recordset
Dim cont As Long
Dim ident_pers As Long
Dim encontrado As Boolean
Dim habilitado As Boolean
Dim sql As String

Set cmd = New Command
Set cmd.ActiveConnection = cn

'En primer lugar, antes de ejecutar nada ver si esa consulta ya se ha procesado para
'esa campaa. Si se ha procesado ya, se sale de la funcion, devolviendo el valor -1
'para que se pueda mostrar un mensaje adecuado indicando que la consulta ya se ha
'ejecutado.
cmd.CommandText = "select * from Consultas_campanya where Id_Campanya = '" & Campanya & "' and Id_Consulta = '" & Ident_Consulta & "' and habilitado=1"
Set rs = cmd.Execute
If Not rs.EOF Then
'Ya se ha procesado esta consulta para esta campaa, salir de la funcion
ejecutar_consulta = -1
Exit Function
End If

cmd.CommandText = Consulta_SQL
'Set rs = cmd.Execute '(1)

'------INICIO BARRA DE PROGRESO
'Si se quita la barra de progreso, quitar el comentario de la linea anterior
Set rs = New Recordset
rs.CursorType = adOpenStatic
rs.Open Consulta_SQL, cn

'Iniciar una transaccion para que en caso de que haya errores se pueda recuperar el
'ultimo estado correcto de los datos. En caso de que se produzca algun error durante
'la manipulacion de los mismos se va a la rutina ControlDeErrores, donde se deshacen los
'cambios que se hayan podido producir.
cn.BeginTrans
On Error GoTo ControlDeErrores

ProgressBar1.Min = 0
If (rs.RecordCount > 0) Then
ProgressBar1.Max = rs.RecordCount
Else
ProgressBar1.Max = 1
End If
ProgressBar1.Value = 0
Frame1.Visible = True
Frame1.Refresh
'------FIN BARRA DE PROGRESO

Dim numeroregistros As Long
Set rs_2 = New Recordset
rs_2.CursorType = adOpenStatic

'sql = "select * from Lista_Procesados_Campanya where Id_Campanya = '" & Campanya & "'"
sql = "select * from Lista_Procesados_Campanya_PRUEBAS where Id_Campanya = '" & Campanya & "' and Id_Consulta = '" & Ident_Consulta & "'"

rs_2.Open sql, cn
numeroregistros = rs_2.RecordCount

cont = 0
Do While Not rs.EOF
ident_pers = rs("Id_Persona")
If (persona_disponible(ident_pers)) Then
If numeroregistros <> 0 Then
rs_2.MoveFirst
End If
encontrado = False
Do While Not rs_2.EOF And Not encontrado
If rs_2("Id_Persona") = rs("Id_Persona") Then
encontrado = True
habilitado = rs_2("Habilitado")
End If
rs_2.MoveNext
Loop
If Not encontrado Then
'cmd.CommandText = "insert into Lista_Procesados_Campanya (Id_Campanya, Id_Persona, Habilitado, Usuario) values ('" & Campanya & "', '" & rs("Id_Persona") & "', '1', '" & Usuario & "')"
cmd.CommandText = "insert into Lista_Procesados_Campanya_PRUEBAS (Id_Campanya, Id_Persona, Id_Consulta, Habilitado, Usuario) values ('" & Campanya & "', '" & rs("Id_Persona") & "', '" & Ident_Consulta & "', '1', '" & Usuario & "')"
cmd.Execute
cont = cont + 1
Else
If Not habilitado Then
'cmd.CommandText = "update Lista_Procesados_Campanya set Habilitado = '1' where Id_Campanya = '" & Campanya & "' and Id_Persona = '" & rs("Id_Persona") & "'"
cmd.CommandText = "update Lista_Procesados_Campanya_PRUEBAS set Habilitado = '1' where Id_Campanya = '" & Campanya & "' and Id_Persona = '" & rs("Id_Persona") & "' and Id_Consulta = '" & Ident_Consulta & "'"
cmd.Execute
cont = cont + 1
End If
End If
End If
rs.MoveNext
'------INICIO BARRA DE PROGRESO
If (ProgressBar1.Value < ProgressBar1.Max - 1) Then
ProgressBar1.Value = ProgressBar1.Value + 1
End If
'------FIN BARRA DE PROGRESO
Loop

If cont <> 0 Then
cmd.CommandText = "select * from Consultas_campanya where Id_Campanya = '" & Campanya & "' and Id_Consulta = '" & Ident_Consulta & "'"
Set rs = cmd.Execute
If rs.EOF Then
cmd.CommandText = "insert into Consultas_Campanya (Id_Consulta, Id_Campanya, Habilitado, Usuario) values ('" & Ident_Consulta & "', '" & Campanya & "', '1', '" & Usuario & "')"
Else
If Not rs("Habilitado") Then
cmd.CommandText = "update Consultas_Campanya set Habilitado = '1' where Id_Campanya = '" & Campanya & "' and Id_Consulta = '" & Ident_Consulta & "'"
End If
End If
cmd.Execute
End If

'Si no ha habido ningun problema con los datos grabar los cambios en la base de datos
cn.CommitTrans

If rs.State = adStateOpen Then rs.Close
Set rs = Nothing

If rs_2.State = adStateOpen Then rs_2.Close
Set rs_2 = Nothing
Set cmd = Nothing

'------INICIO BARRA DE PROGRESO
Frame1.Visible = False
'------FIN BARRA DE PROGRESO

ejecutar_consulta = cont
'End Function
Exit Function 'Suprimir esta linea si se quita el control de errores y quitar el comentario de la anterior

ControlDeErrores:
'En caso de que haya habido cualquier problema durante la manipulacion de los datos se
'deshace la transaccion y se deja la base de datos como estaba.
'Se devuelve el valor -2 para que se pueda mostrar un mensaje avisando de que la consulta
'no se ejecuto.
cn.RollbackTrans
Frame1.Visible = False
ejecutar_consulta = -2
End Function|||SEE the Command

SET NOEXEC ON { ON | OFF }

AS in

SET NOEXEC ON

SELECT * FROM table

SET NOEXEC OFF

This might do what you want in VB not sure works in QA I think.

Edit: Not as good a QA check Syntax because it does not valid objects

Tim S|||Thank you very much pica.. for ur ellobrate explanatio...and all for sparing ur time to explain.

with regards
Sudar