Dsn Less Connection

[Girish Gopi]

Set con = New ADODB.Connection
Set cmd = New ADODB.Command
With con
.ConnectionString = "Provider=MSDataShape.1;DRIVER={SQL Server};Uid=sa;Pwd=;SERVER=Server02;DATABASE=ComDemo;DSN=;"
.Open
End With
With cmd

.ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "InsertCmstScheme"
For parmcnt = 1 To 10
.Parameters(parmcnt).Direction = adParamInput
Next
.Parameters("@SName") = m_str_SchemeName
.Parameters("@MinDeposit") = m_sgl_MinimumDeposit
.Parameters("@DepositDuration") = m_int_DepositDurationInMonths
.Parameters("@InterestFrequency") = m_str_InterestFrequency
.Parameters("@InterestType") = m_str_InterestType
.Parameters("@LockInPeriod") = m_int_LockInPeriod
.Parameters("@InterestRate") = m_sgl_InterestRate
.Parameters("@Brokerage") = m_sgl_Brokerage
.Parameters("@IntrestRateThroughBroker") = m_sgl_InterestRateThroughBroker
End With

End Sub


This Code Does Not Work

But Instead of ConnectionString If I use DSN IT Works Code Given Below

Set con = New ADODB.Connection
Set cmd = New ADODB.Command
With con
.Open("DSNNAME")
End With
With cmd

.ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "InsertCmstScheme"
For parmcnt = 1 To 10
.Parameters(parmcnt).Direction = adParamInput
Next
.Parameters("@SName") = m_str_SchemeName
.Parameters("@MinDeposit") = m_sgl_MinimumDeposit
.Parameters("@DepositDuration") = m_int_DepositDurationInMonths
.Parameters("@InterestFrequency") = m_str_InterestFrequency
.Parameters("@InterestType") = m_str_InterestType
.Parameters("@LockInPeriod") = m_int_LockInPeriod
.Parameters("@InterestRate") = m_sgl_InterestRate
.Parameters("@Brokerage") = m_sgl_Brokerage
.Parameters("@IntrestRateThroughBroker") = m_sgl_InterestRateThroughBroker
End With

End Sub



Can you Tell Me why And How Do I Use The Above Methode With A Connection String





Rate this post :
3 : Excellent!
2 : Useful / Answers the question
1 : Good suggestion or hint
--- Select an option ---
-1 : Not useful / wrong / off-topic

[muthiahr]

Hi,
I haven't used ADODB with SQL Server. With, Oracle, though, the following works. I hope it helps.

oraConnection.Open "Provider=MSDAORA;Data Source=formulate; User Id=dba;Password=dba;"

If you have a database alias, like Oracle does, you can replace formulate with yours and appropriate provider. Also I have seen in some postings people have used {Microsoft ODBC for Oracle}; for SERVER. But, your code sample says just SQL SERVER. I would check that too, unless you have used that and it worked before.
goodluck!
Rajan

[Allen Noakes]

Girish,

I think you have to much in you Connection String.
Here are 2 different ways that I have used with success:

"Provider=SQLOLEDB; Data Source=server_name; Initial Catalog=database_name; User Id=user_name; Password=user_password"

"Driver={SQL Server}; Server=server_name; Database=database_name; UID=user_name; PWD=password"

Just fill in the appropriate information where it is needed, for example "server_name = AURORA1".

Let me know how it works for you.

Allen Noakes
VB Programmer/Analyst
Dames & Moore Group
[email protected]