SQL Server ODBC DSN in VB6

[nidgep]

hi

I have been trying to programmatically create a System DSN entry using VB6.
I have managed to do this but without being able to supply the password for the SQLserver user.

Can anyone suggest why this might be?
I am using the:

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwndParent As Long, _
ByVal fRequest As Long, ByVal lpszDriver As String, _
ByVal lpszAttributes As String) As Long

API call and have also tried using the API calls to create the registry entries under the

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\
&
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

The registry entries do get created successfully but where and how would I supply the password using this method?

I have already checked out the previous posting on this topic they haven't worked for me, yet I know that this is possible!

Any replies using one of the above methods would be great!

Cheers

 

[strongm]

> have already checked out the previous posting on this topic they haven't worked for me

You mean you've looked at my (non-API) code for creating and editing more-or-less any ODBC connection string direct from VB and it hasn't worked for you?

 

[nidgep]

hi

yes amongst many others as well!

Why cant the dsn accept the password and uid without failing?

 

[strongm]

I don't know. We'd have to see your code.

Certainly the connection strings generated by my code in thread222-595040 work OK for me with usernames and passwords

 

[nidgep]

hi

this is a code snippet

' ODBC API
' -- ODBC Commands
Public Const ODBC_ADD_DSN = 1&
Public Const ODBC_CONFIG_DSN = 2&
Public Const ODBC_REMOVE_DSN = 3&
Public Const ODBC_ADD_SYS_DSN = 4&
Public Const ODBC_CONFIG_SYS_DSN = 5&
Public Const ODBC_REMOVE_SYS_DSN = 6&
Public Const ODBC_REMOVE_DEFAULT_DSN = 7&

' -- ODBC Error Codes
Public Const ODBC_ERROR_GENERAL_ERR = 1
Public Const ODBC_ERROR_INVALID_BUFF_LEN = 2
Public Const ODBC_ERROR_INVALID_HWND = 3
Public Const ODBC_ERROR_INVALID_STR = 4
Public Const ODBC_ERROR_INVALID_REQUEST_TYPE = 5
Public Const ODBC_ERROR_COMPONENT_NOT_FOUND = 6
Public Const ODBC_ERROR_INVALID_NAME = 7
Public Const ODBC_ERROR_INVALID_KEYWORD_VALUE = 8
Public Const ODBC_ERROR_INVALID_DSN = 9
Public Const ODBC_ERROR_INVALID_INF = 10
Public Const ODBC_ERROR_REQUEST_FAILED = 11
Public Const ODBC_ERROR_INVALID_PATH = 12
Public Const ODBC_ERROR_LOAD_LIB_FAILED = 13
Public Const ODBC_ERROR_INVALID_PARAM_SEQUENCE = 14
Public Const ODBC_ERROR_INVALID_LOG_FILE = 15
Public Const ODBC_ERROR_USER_CANCELED = 16
Public Const ODBC_ERROR_USAGE_UPDATE_FAILED = 17
Public Const ODBC_ERROR_CREATE_DSN_FAILED = 18
Public Const ODBC_ERROR_WRITING_SYSINFO_FAILED = 19
Public Const ODBC_ERROR_REMOVE_DSN_FAILED = 20
Public Const ODBC_ERROR_OUT_OF_MEM = 21
Public Const ODBC_ERROR_OUTPUT_STRING_TRUNCATED = 22

'API Command to create a Data Source Name, not used in this example
Private Declare Function SQLCreateDataSource Lib "odbccp32.dll" (ByVal hwnd&, ByVal lpszDS$) As Boolean
'API to modify/Edit/Create a Data Source Name
Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwnd As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Boolean



Public Function CreateSQLODBC() As Long
' This function setups a DSN common for remote Database servers
' Such as SQL or Oracle, keep in mind, this isnt a complete listing of parameters
   CreateSQLODBC = 0 'will get set to one if the dsn creation succeeds
   Dim DSN           As String
   Dim Server        As String
   Dim Address       As String
   Dim Database      As String
   Dim Description   As String
   Dim Security      As String
   Dim SQLDriver     As String
   Dim SQLParameter  As String
   Dim UID           As String
   Dim PWD           As String
   
   'Basically the DSN Name you want to have
   DSN = "DSN=AniteIntegration"
   'The IP Addy of the server you want , if this is a remote connection
   Server = "SERVER=EDMS"
   'Same as above
   Address = "ADDRESS=EDMS"
   'The name of the database as known by the DB Server, such as SQL Server
   Database = "DATABASE=AniteIntegration"
   'An Optional Description Feild
   Description = "DESCRIPTION=AniteIntegration DSN"
   
   UID = "UID=CalloutUser"
   PWD = "PWD=letmein"
   
   'This is optional, if you require a Security mode check the help files
'   Security = "NETWORK=dbmssocn"

    SQLDriver = "SQL Server"
   
   'the next couple lines setup the Driver Text , that defines the type of DB Drivers
   ' you are using, if its anything other than the ones I've listed, check your DB
   ' documentation, or check the ODBC settings to see it's names
      
   'Also you will notice as each string peice is put together, they are seperated by
   'VbNullChar, this gives it a Null seperated array in a sense so that the API Command
   'can use the Parameters
   
   SQLParameter = DSN & vbNullChar & Server & vbNullChar & Address & vbNullChar &  _
         Database & vbNullChar & UID & vbNullChar & PWD & vbNullChar & Description & vbNullChar & vbNullChar
'    SQLParameter = DSN & vbNullChar & Server & vbNullChar & Address & vbNullChar & _
'                                Database & vbNullChar & Description & vbNullChar & vbNullChar
   'calls SQLConfigDataSource , giving it the forms handle, the command to Add a System DSN
   'giving it the Driver name, and then the Null Seperated Parameter listing
   
   CreateSQLODBC = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, SQLDriver, SQLParameter)
   
   
End Function

Hope this helps

 

[strongm]

Excellent. Now we can diagnose ...

UID and PWD attributes are not supported via SQLConfigData source for SQL Server System DSNs, and (I believe) PWD is not supported for File DSNs ...

I can't get the references for you at the moment, because Microsoft's MSDN2 website seems to be broken ...

 

[strongm]

OK, MSDN" is back, so:

http://msdn2.microsoft.com/en-us/library/aa177860(SQL.80).aspx

 

[nidgep]

thanks for the link.

As you said earlier the SQLConfigDataSource does not support the UID and PWD attributes.

I have seen applications that prompt for info to create an ODBC DSN and maintain both the SQL username and password as I would like to do.

Looks like I'll have to try another approach.

Thanks again.

 

[strongm]

Are you sure thread222-595040 doesn't help?

 

[kermit01de]

Sorry to post in this thread - but I already tried the Link you provided in one of my apps. Finally we ended up in creating the ODBC links manually at the clients.

...

Why is Vb not able to use System DSN instead of User DSN?
...
Or better question: Ho to use Designer/Connections to use System DSN - maybe another thread.


--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[strongm]

>I already tried the Link you provided in one of my apps

To create a DSN? That's not what it is for. It's for DSN-less connections ... but you can quite happily save the connection strings for re-use and reconfiguration, so in effect it works much like a DSN

>Why is Vb not able to use System DSN

It can

When using the Designer make your provider the "Microsoft OLE DB Provider for ODBC Drivers" After that, under the Connection tab you should be able to select from bith User and System DSNs