[RESOLVED] [2005] Data - SQL class

[RS_Arm]

Hi,

I'm coding my 1st class in VB2005.
This class that I've coded is going to be used to connect, disconnect and to run SQL commands.

By now, I just can't execute succesfully any kind of query. This query is executed in the 2nd code quote.

Could anybody take a look to my code?

Public Class acessos
    Public CNN As New Data.SqlClient.SqlConnection
    'Public rs As New Data.SqlClient.SqlDataAdapter

    Public Const ID_DB As String = "TLERP"
    Public Const server As String = "TURION"
    Public Const user As String = "xx"
    Public Const Password As String = "xx"

    Public Function Conectar() As Boolean
        Try
            CNN.ConnectionString = "Server=" & server & ";Database=" & ID_DB & ";User ID=" & user & ";Password=" & Password & ";Trusted_Connection=False"
            CNN.Open()
            Conectar = True

            Exit Function

        Catch err As Exception
            Conectar = False
        End Try

    End Function

    Public Function Desconectar() As Boolean
        Try
            CNN.Close()
            CNN = Nothing
            Desconectar = True

            Exit Function

        Catch err As Exception

            Desconectar = False
        End Try

    End Function
    Public Function AbrirDataAdapter(ByVal SqlString As String) As Data.SqlClient.SqlDataAdapter

        Dim da As Data.SqlClient.SqlDataAdapter

        da = New Data.SqlClient.SqlDataAdapter(SqlString, CNN)
        AbrirDataAdapter = da

    End Function
    Public Function ExecutaSQL(ByVal SqlString As String) As Boolean
        Dim da As Data.SqlClient.SqlCommand

        Try
            da = New Data.SqlClient.SqlCommand(SqlString, CNN)
            ExecutaSQL = True

        Catch ex As Exception
            ExecutaSQL = False

        End Try

    End Function
End Class

and in the form...

 Dim estadoConexao As Boolean    

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        

      estadoConexao = db.Conectar()
        MsgBox(db.ExecutaSQL("Insert into IVA values (2, 'aa', 20)"))
    End Sub

I'm moving from VB6 so I used to do this kind of class using Recordsets...

Thanks

[GaryMazzone]

You need to assign the connection to the command object. It is never done in you code.

[RS_Arm]

 Public CNN As New Data.SqlClient.SqlConnection
'...
 Public Function ExecutaSQL(ByVal SqlString As String) As Boolean
        Dim da As Data.SqlClient.SqlCommand

        Try
            da = New Data.SqlClient.SqlCommand(SqlString, CNN)
            ExecutaSQL = True

        Catch ex As Exception
            ExecutaSQL = False

        End Try

    End Function

I think I'm doing that with "CNN", aint I?
Advice, please

sorry for any misunderstood

[techgnome]

assuming that this is supposed to run the SQL:

    Public Function ExecutaSQL(ByVal SqlString As String) As Boolean
        Dim da As Data.SqlClient.SqlCommand

        Try
            da = New Data.SqlClient.SqlCommand(SqlString, CNN)
            ExecutaSQL = True

        Catch ex As Exception
            ExecutaSQL = False

        End Try

    End Function

I can see why it doesn't "work".... actually, I should say, it does work. It does EXACTLY what you told it to do... which is to create the command object, and it does... BUT.... creating the command doesn't do anything UNTIL you call an execute method .... .ExecureNonQuery, .ExecuteReader, .ExecuteScalar or use it to fill a DataTable.

In short, you're missing the code to actually execute the SQL.

-tg

[RS_Arm]

    Public Function ExecutaSQL(ByVal SqlString As String) As Boolean
        Dim da As Data.SqlClient.SqlCommand

        Try
            da = New Data.SqlClient.SqlCommand(SqlString, CNN)
            da.ExecuteNonQuery()
            ExecutaSQL = True

        Catch ex As Exception
            ExecutaSQL = False

        End Try

    End Function

Thank you!
Solved