[2005] (Migration) CNN.Execute(VB6) equivalent in VB2005

[RS_Arm]

Hi.
I'm very new to vb2005. I'm migrating from VB6 to VB2005.
In VB6, I've been using ADODB.connection, In VB2005 I'm using SqlClient.SqlConnection.
My question is: what is the equivalent expression to "CNN.Execute (strSQL)".
(My old code: )

Public CNN As New ADODB.Connection

Public Function ExecuteSQL(strSQL As String) 
CNN.Execute (strSQL)
End Function

Thank you

[RobDog888]

Use the ExecuteReader function of the OleDbCommand object

[RS_Arm]

I will have to do somehting like this?

    Dim x As New OleDb.OleDbCommand
    Dim xx As New OleDb.OleDbConnection

This is my actual code. Should I change something?

    Public rs As New SqlClient.SqlDataAdapter
    Public cnn As New SqlClient.SqlConnection
    Public Const ID_DB As String = "GesProj"
    Public Const server As String = "SERVER_000001"
    Public Const user As String = "SA"
    Public Const Password As String = "SA"

    Public Sub Conect()

        Try
            cnn.ConnectionString = "Data Source=" & server & ";Initial Catalog=" & ID_DB & ";Persist Security Info=True;User ID=" & user & ";Password=" & Password
            Exit Sub

        Catch err As Exception
            MessageBox.Show("Erro: " & err.Message, "No connection", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Public Sub Disconect()
        Try
            cnn.Close()
            cnn = Nothing
            Exit Sub

        Catch err As Exception
            MessageBox.Show("Erro: " & err.Message, "No connection", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

This is my code in need of upgrade:

  Public Sub ExecuteSQL(ByVal strSQL As String)
 
   'OLD VB6 CODE
   cnn.exeucte strSQL

    End Sub

[RobDog888]

No quite. Here is one example...

Dim cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\VB-Guru\RobDog888.mdb;Jet OLEDB:Database Password=Meow;"
Dim con As New OleDb.OleDbConnection(cs)
con.Open()
Dim cmd As New OleDb.OleDbCommand("SELECT Field2 FROM Table1 WHERE Field2 = 'Meow'", con)
Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader()
'etc

Are you trying to execute query code or action code?

[RS_Arm]

In VB6, what I was doing was, basically, 2 functions:
1st -execute SQL (like Insert, Update and Delete instructions)
This function has a parameter StrSQL.

2nd -return an ADODB.RecordSet to a function (Select instructions) named OpenRS
With OpenRS, I could bind combobox, DataGrid, etc

Now I'm a little lost upgrading may apps.

By what I've been seeing, people uses SqlClient.SqlDataAdapter and SqlClient.SqlConnection to connect to SQL Databases.

What is the most appropriate to use:
SQLClient, OLEDB or even reference ADODB ActiveX Component from VB6?

[RobDog888]

Forget VB 6 ADO. Its not at all the same in ADO.NET

There are no recordsets, only datasets and datatables.

SqlClient class is only for use with SQL Server. Its optimized especially for SQL Server. Access or other generic databases use OleDB class.

Bottom line: ADO.NET <> classic (VB 6) ADO


For more info you may want to go through a tutorial or faq link this one...
http://vbforums.com/showthread.php?t=466658

[RS_Arm]

I'm using SQL Server 2000 Database

Am I in a good way with this code?

    Public CNN As New SqlClient.SqlConnection
    Public rs As New SqlClient.SqlDataAdapter

    Public Const ID_DB As String = "GesProj"
    Public Const server As String = "Turion\PRIMAVERA"
    Public Const user As String = "SA"
    Public Const Password As String = "SA"

    Public Sub Conect()
        Try
            CNN.ConnectionString = "Data Source=" & server & ";Initial Catalog=" & ID_DB & ";Persist Security Info=True;User ID=" & user & ";Password=" & Password
            Exit Sub

        Catch err As Exception
            MessageBox.Show("Erro: " & err.Message, "Sem conexão", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Public Sub Disconect()
        Try
            CNN.Close()
            CNN = Nothing
            Exit Sub

        Catch err As Exception
            MessageBox.Show("Erro: " & err.Message, "Sem conexão", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

[RS_Arm]

By the way, I'm using VB2005 Express.

[RS_Arm]

Now I'm getting this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I've tested the connection in VB6 and everything works great.
Help me please

[RobDog888]

Thats the same code you posted earlier. Its not valid as it only sets a connectionstring. Have you even tried my code?