Resumen SQL Server y ADO

Mensaje enviado por "VbNick" <mjli@mundo-r.com> el 25/02/2002

Hola Ariel, te paso un código con varias formas de abrir conexión con SQLServer

Public Sub ConnectionString()

 Dim cnn1 As ADODB.Connection
 Dim cnn2 As ADODB.Connection
 Dim cnn3 As ADODB.Connection
 Dim cnn4 As ADODB.Connection

 ' Abre una conexión sin usar un Data Source Name (DSN).
 Set cnn1 = New ADODB.Connection
 cnn1.ConnectionString = "driver={SQL Server};" & _
  "server=bigsmile;uid=sa;pwd=pwd;database=pubs"
 cnn1.ConnectionTimeout = 30
 cnn1.Open

 ' Abre una conexión usando DSN y ODBC.
 Set cnn2 = New ADODB.Connection
 cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
 cnn2.Open

 ' Abre una conexión usando DSN y OLE DB.
 Set cnn3 = New ADODB.Connection
 cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
 cnn3.Open

 ' Abre una conexión que usa un DSN y argumentos individuales
        ' en lugar de una cadena de conexión.
 Set cnn4 = New ADODB.Connection
 cnn4.Open "Pubs", "sa", "pwd"

 ' Muestra el estado de las conexiones
 MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _
  "cnn2 state: " & GetState(cnn2.State) & vbCr & _
  "cnn3 state: " & GetState(cnn3.State) & vbCr & _
  "cnn4 state: " & GetState(cnn4.State)

 cnn4.Close
 cnn3.Close
 cnn2.Close
 cnn1.Close

End Sub

Public Function GetState(intState As Integer) As String

 Select Case intState
  Case adStateClosed
   GetState = "adStateClosed"
  Case adStateOpen
   GetState = "adStateOpen"
 End Select

End Function

    Y a continuación un código que abre un Stored Procedure sacado de SQLMax

Public Sub TestParameter

' This code sample demonstrates the following:
' 1. Creating a stored procedure that uses input/output parameters
' 2. Creating the parameters and firing the stored procedure
' 3. Displaying the result and delete the stored procedure

  Dim conn As ADODB.Connection
  Dim Param As ADODB.Parameter
  Dim Com As ADODB.Command

  Dim strConn As String

  ' Assign the connection string to a variable

  strConn = "PROVIDER=SQLOLEDB;SERVER=(local);User Id=" & _
  "sa;Password=;DATABASE=pubs"

  ' Create the Connection object

  Set conn = New ADODB.Connection

  'Assign the connection string and provider, then open the
  'connection

  conn.ConnectionString = strConn
  conn.Open strConn

  'Create a new command object

  Set Com = New ADODB.Command

  'Load it with code that will create a new stored
  'procedure in PUBS

  Com.CommandText = "Create Procedure sp_ReturnsOutput @authorid " _
  & "varchar(11),@result Varchar(20) OUTPUT As Select @result " _
  & "= (Select au_fname from authors Where Au_id = @authorId)"

  'Fire it to create new stored procedure
  Com.ActiveConnection = conn
  Com.Execute

  'Now get ready to create 2 parameters: One for input, one for output.
  'Create input Parameter
  Set Param = Com.CreateParameter("AuId", adVarChar, adParamInput, 11)

  Param.Value = "172-32-1176"
  Com.Parameters.Append Param

  'Create return parameter

  Set Param = Com.CreateParameter("Return", adVarChar, _
  adParamReturnValue, 20)

  Com.Parameters.Append Param

  'NOTE: When creating parameters you MUST include the SIZE of the
  'parameter as well as the TYPE of value that you're dealing with.
  'Load Command Object a Sql String

  Com.CommandText = "sp_ReturnsOutput"
  Com.CommandType = adCmdStoredProc

  'Fire it.
  Com.Execute

  'Show Results

  MsgBox "The first name of the author with an SS of " & _
  Com.Parameters(0).Value & " is " & Com.Parameters(1).Value

  ' Clean up by deleting stored procedure
  Com.CommandText = "Drop Procedure Sp_ReturnsOutput"
  Com.CommandType = adCmdText
  Com.Execute
  conn.Close

End Sub

    A ver si te sirve.

    Un saludo.




Resumen Resumen

Visual Basic Página de Visual Basic

Página principal Página principal

www.jrubi.com