Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

passing string parameter via VB.ODBC.SQLServer

Status
Not open for further replies.

LTusing

Technical User
Oct 5, 2005
20
US
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top