Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Madrid, Spain
    Posts
    13

    Red face Unanswered: 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).
    Iīd like to know if thereīs a way to use these Server tools from Visual Basic 6 SP6, something like APIs ...
    If thereīs 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 ?????)

  2. #2
    Join Date
    Apr 2004
    Location
    Madrid, Spain
    Posts
    13

    Smile Itīs done

    Iīve solved it with transactions, commit and rollback.
    Thanks for your patience

  3. #3
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Smile

    Hi pucca,

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

    thnks in advance

    With regards
    Sudar

  4. #4
    Join Date
    Apr 2004
    Location
    Madrid, Spain
    Posts
    13

    Red face Yes, but I realized my question was stupid

    because I noticed that I hadnīt to check the sintax, I just had to wait VB would do the SQL Statement, if it couldnīt do it then the sentence the user wrote was wrong or there were constraints in the server side.
    Sorry for my english, Iīll 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 campaņa. 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 campaņa, 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
    Attached Thumbnails Attached Thumbnails Pantalla.bmp  

  5. #5
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    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
    Last edited by TimS; 10-29-04 at 13:41.

  6. #6
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137
    Thank you very much pica.. for ur ellobrate explanatio...and all for sparing ur time to explain.

    with regards
    Sudar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •