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
