I am trying to call a SQLServer 2000 proc to update .adp record columns. We are able to complete task for data and int columns but have run into difficulty in passing nvarchar and char columns. We are receiving error "Cannot open database"mydatabase" requested in login. Login Failed. The code is as follows for table_Randomization (pt_id(int), rand_no(char), rand_dt(datetime). The VB procedure is as follows:
Private Sub UpdateRandomization_Record_Click()
Dim strMsg As String
Dim strOK As String
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim SQLServer As ADODB.Connection
Dim rstRandomization As New ADODB.Recordset
Dim strRandomization As String
Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter
Dim param3 As ADODB.Parameter
Dim param4 As ADODB.Parameter
Set SQLServer = New ADODB.Connection
SQLServer.Provider = "SQLOLEDB"
SQLServer.ConnectionString = "Data Source=PEDSBOT;Initial Catalog=fas.adhd.blind;Trusted_Connection=Yes"
SQLServer.CommandTimeout = 0
SQLServer.ConnectionTimeout = 300
SQLServer.Open
With cmd
.ActiveConnection = cnn
.CommandText = "dbo.procupdate_Randomization_1"
.CommandType = adCmdStoredProc
End With
Set param1 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = pt_id
Set param2 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append param2
param2.Value = Me.pt_id
Set param3 = cmd.CreateParameter(, adChar, adParamInput, 3)
cmd.Parameters.Append param3
param3.Value = Me.rand_no
Set param4 = cmd.CreateParameter(, adDBDate, adParamInput)
cmd.Parameters.Append param4
param4.Value = Me.rand_dt
cmd.Execute
Debug.Print rstRandomization(0)
rstRandomization.Close
End Sub
Thank You!
Private Sub UpdateRandomization_Record_Click()
Dim strMsg As String
Dim strOK As String
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim SQLServer As ADODB.Connection
Dim rstRandomization As New ADODB.Recordset
Dim strRandomization As String
Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter
Dim param3 As ADODB.Parameter
Dim param4 As ADODB.Parameter
Set SQLServer = New ADODB.Connection
SQLServer.Provider = "SQLOLEDB"
SQLServer.ConnectionString = "Data Source=PEDSBOT;Initial Catalog=fas.adhd.blind;Trusted_Connection=Yes"
SQLServer.CommandTimeout = 0
SQLServer.ConnectionTimeout = 300
SQLServer.Open
With cmd
.ActiveConnection = cnn
.CommandText = "dbo.procupdate_Randomization_1"
.CommandType = adCmdStoredProc
End With
Set param1 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = pt_id
Set param2 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append param2
param2.Value = Me.pt_id
Set param3 = cmd.CreateParameter(, adChar, adParamInput, 3)
cmd.Parameters.Append param3
param3.Value = Me.rand_no
Set param4 = cmd.CreateParameter(, adDBDate, adParamInput)
cmd.Parameters.Append param4
param4.Value = Me.rand_dt
cmd.Execute
Debug.Print rstRandomization(0)
rstRandomization.Close
End Sub
Thank You!