Enviando informacion a Base de Datos SQL (tratando)
publicado
Estimados,
los molesto con el siguiente problema. Estoy tratando de construir un libro de Excel donde al accionar un boton de comando, la informacion visualizada sea transferida a una tabla en particular de una base de datos SQL. BIen, ahora al ejecutar el modulo que enviara la informacion a SQL, surge el siguiente error: Run-Time Error: the connection cannot be used to perform this operation. It Is either closed or invalid in this contex. el codigo que estoy utilizando es:
Option Explicit
Public CN As ADODB.Connection
Dim Cod_Prod, Nombre, Existencia
Dim Fila, Final As Integer
Function Connect(Server As String, User As String, Pass As String, Database As String) As Boolean
Set CN = New ADODB.Connection
On Error Resume Next
With CN
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Password=" & Pass & ";" & _
"Persist Security Info=True;" & _
"User ID=" & User & ";" & _
"Initial Catalog=" & Database & ";" & _
"Data Source=" & Server
.Open
End With
If CN.State = 0 Then
Connect = False
Else
Connect = True
End If
End Function
Function Query()
Dim SQL As String
Dim RS As ADODB.Recordset
Dim Field As ADODB.Field
Dim CodProd As String
Dim NroSerie As String
Dim FechaViaje As Date
Dim NroViaje As String
Dim Col As Long
Set RS = New ADODB.Recordset
Final = GetUltimoR(Hoja1)
For Fila = 2 To Final
CodProd = Hoja1.Cells(Fila, 1)
NroSerie = Hoja1.Cells(Fila, 2)
FechaViaje = Hoja1.Cells(Fila, 3)
NroViaje = Hoja1.Cells(Fila, 4)
SQL = "insert into Metalsa values('" & CodProd & "','" & NroSerie & "' , '" & FechaViaje & "','" & NroViaje & "');"
RS.Open SQL, CN
Next
RS.Open "SELECT * FROM Metalsa", CN
If RS.State Then
Col = 1
For Each Field In RS.Fields
Cells(1, Col) = Field.Name
Col = Col + 1
Next Field
Cells(2, 1).CopyFromRecordset RS
Set RS = Nothing
End If
End Function
Function Disconnect()
CN.Close
End Function
Public Sub run()
Dim SQL As String
Dim Connected As Boolean
Connected = Connect("10.204.76.70\baseprueba1", "sa", "S<h+2016", "SchenkerARIT")
If Connected Then
Call Query
Call Disconnect
Else
MsgBox "No podemos Conectarnos!"
End If
End Sub
Hay algo que este haciendo mal?
El debug me marca la linea RS.Open SQL, CN como error.
Les agradesco de ante mano la ayuda que puedan darme.
Saludos cordiales,
Featured Replies
Archivado
Este tema está ahora archivado y está cerrado a más respuestas.
Estimados,
los molesto con el siguiente problema. Estoy tratando de construir un libro de Excel donde al accionar un boton de comando, la informacion visualizada sea transferida a una tabla en particular de una base de datos SQL. BIen, ahora al ejecutar el modulo que enviara la informacion a SQL, surge el siguiente error: Run-Time Error: the connection cannot be used to perform this operation. It Is either closed or invalid in this contex. el codigo que estoy utilizando es:
Option Explicit Public CN As ADODB.Connection Dim Cod_Prod, Nombre, Existencia Dim Fila, Final As Integer Function Connect(Server As String, User As String, Pass As String, Database As String) As Boolean Set CN = New ADODB.Connection On Error Resume Next With CN .ConnectionString = "Provider=SQLOLEDB.1;" & _ "Password=" & Pass & ";" & _ "Persist Security Info=True;" & _ "User ID=" & User & ";" & _ "Initial Catalog=" & Database & ";" & _ "Data Source=" & Server .Open End With If CN.State = 0 Then Connect = False Else Connect = True End If End Function Function Query() Dim SQL As String Dim RS As ADODB.Recordset Dim Field As ADODB.Field Dim CodProd As String Dim NroSerie As String Dim FechaViaje As Date Dim NroViaje As String Dim Col As Long Set RS = New ADODB.Recordset Final = GetUltimoR(Hoja1) For Fila = 2 To Final CodProd = Hoja1.Cells(Fila, 1) NroSerie = Hoja1.Cells(Fila, 2) FechaViaje = Hoja1.Cells(Fila, 3) NroViaje = Hoja1.Cells(Fila, 4) SQL = "insert into Metalsa values('" & CodProd & "','" & NroSerie & "' , '" & FechaViaje & "','" & NroViaje & "');" RS.Open SQL, CN Next RS.Open "SELECT * FROM Metalsa", CN If RS.State Then Col = 1 For Each Field In RS.Fields Cells(1, Col) = Field.Name Col = Col + 1 Next Field Cells(2, 1).CopyFromRecordset RS Set RS = Nothing End If End Function Function Disconnect() CN.Close End Function Public Sub run() Dim SQL As String Dim Connected As Boolean Connected = Connect("10.204.76.70\baseprueba1", "sa", "S<h+2016", "SchenkerARIT") If Connected Then Call Query Call Disconnect Else MsgBox "No podemos Conectarnos!" End If End Sub
Hay algo que este haciendo mal?
El debug me marca la linea RS.Open SQL, CN como error.
Les agradesco de ante mano la ayuda que puedan darme.
Saludos cordiales,