1

I need to use code to set up an old-fashioned DSN, rather than a connection string, due to working with a legacy application. I can set it up manually using the DSN setup wizard. If I do that, it appears in the list of DSNs and appears in the registry. If I use the following code, it returns TRUE indicating success, but the DSN does not appear in the list of DSNs nor in the registry. Here is the code:

Imports System.Runtime.InteropServices

Public Class frmMain

    <DllImport("ODBCCP32.DLL")>
    Shared Function SQLConfigDataSource( _
        ByVal hwndParent As Integer, _
        ByVal fRequest As Integer, _
        ByVal lpszDriver As String, _
        ByVal lpszAttributes As String) As Boolean
    End Function

    Private Sub btnGo_Click(sender As Object, e As EventArgs) Handles btnGo.Click
        Const ODBC_ADD_SYS_DSN = 4
        Dim Driver As String = "SQL Server"
        Dim Attributes As String =
            "DSN=" & "WebCoolDSN" & Chr(0) &
            "Database=" & "WebCool" & Chr(0) &
            "Description=" & "WebCool DSN" & Chr(0) &
            "Server=" & "semsdb02\dev1" & Chr(0) & Chr(0)
        Dim Result As Boolean
        Result = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)
        MsgBox(Result.ToString())
    End Sub

End Class

Here is some of my research:

http://msdn.microsoft.com/en-us/library/ms716476(VS.85).aspx

How do I create an ODBC DSN entry using C#?

See comments for more. The research got me to the point where:

  1. I found out about the SQLConfigDataSource API call.
  2. I found out the format of the parameters that supposedly would work.
  3. I found out what to look for in the registry to see what gets put in when I create the DSN manually.
  4. I found out you can't put in a username and password with the DSN.
  5. I found out that it might be possible in InstallShield, but I would need to have my department purchase me a higher-level version of it.
  6. I found out another way to do this is to edit the registry directly, but that is not preferred because it could be dangerous.
Community
  • 1
  • 1
Gnurrd
  • 11
  • 6
  • Please don't tell us that you *looked elsewhere for an answer.* That doesn't satisfy the requirement for prior research. If you want to demonstrate prior research, then *show us the prior research,* and explain why it didn't solve your problem. – Robert Harvey Jan 16 '15 at 17:18
  • Here is some of my research: – Gnurrd Jan 16 '15 at 18:34
  • Put it in your question. – Robert Harvey Jan 16 '15 at 18:35
  • http://msdn.microsoft.com/en-us/library/ms709275(v=vs.85).aspx – Gnurrd Jan 16 '15 at 18:41
  • http://msdn.microsoft.com/en-us/library/ms715433(v=vs.85).aspx – Gnurrd Jan 16 '15 at 18:42
  • https://community.flexerasoftware.com/showthread.php?57106-How-create-a-DSN-with-installshield – Gnurrd Jan 16 '15 at 18:43
  • OK. Now include the results when you tried each technique. Be specific; it's very unlikely that they all do absolutely nothing. There should be some error messages or some other artifact. – Robert Harvey Jan 16 '15 at 18:43
  • https://community.flexerasoftware.com/showthread.php?196554-Create-a-DSN-using-Installshield-properties – Gnurrd Jan 16 '15 at 18:43
  • I do not get any error message. The code returns True, which indicates success, but the DSN is not created. – Gnurrd Jan 16 '15 at 18:45
  • Sorry for multiple comments. It won't let me put the links in my original post, and it won't let me chat. I have 8 more links. – Gnurrd Jan 16 '15 at 18:46
  • Do you have sufficient user rights on the machine to make these particular registry changes? The OS might be simply discarding the changes silently. Try using [ODBCCONF](http://msdn.microsoft.com/en-us/library/ee388579(v=vs.85).aspx) and see if that works. If that doesn't work either, then it's almost certainly not a problem with your code. – Robert Harvey Jan 16 '15 at 18:47
  • Your last edit made this a *much* better question. Editing the registry is only dangerous if you don't know what you are doing. – Robert Harvey Jan 16 '15 at 19:01
  • I tried running the executable as admin, and it didn't help. Then I wrote a program that uses Registry.SetValue to write directly to the registry. That also executes without an error, but does not alter the registry, whether I run as admin or not. I have not yet dug into ODBCCONF, but I noticed it is deprecated. – Gnurrd Jan 16 '15 at 20:57
  • Let's try ODBCCONF, and see what happens. If that checks out, see if you can use regedit.exe to make a DSN entry manually. – Robert Harvey Jan 16 '15 at 20:58
  • Using regedit works. – Gnurrd Jan 16 '15 at 22:55
  • This works:ODBCConf /A {CONFIGSYSDSN "SQL Server" "DSN=WebCoolDSN|Server=semsdb02\dev1|Database=WebCool"} – Gnurrd Jan 16 '15 at 23:50
  • OK, so now that you know it's not a permissions problem, you have some choices. You can troubleshoot one or more of those solutions you found, or you can write some registry code that makes the necessary modifications. My vote is for the latter. – Robert Harvey Jan 17 '15 at 01:10
  • I wrote the code to edit the registry. Same problem. Executes without error. But DSN not there and registry not changed. – Gnurrd Jan 19 '15 at 15:09

1 Answers1

0

It turns out that my trouble was that there are 2 different places in the registry where DSNs are stored, and two different programs for editing DSNs. Both places in the registry start out with HKEY_LOCAL_MACHINE -> SOFTWARE. Under SOFTWARE, the first place DSNs are kept is under ODBC. The second place where they are kept is under Wow6432Node -> ODBC. The DSN editing program under the control panel looks at the first place. To look at the second place, you have to use the program odbcad32.exe under C:\Windows\SysWOW64 My code was successfully putting the DSN into the second place, where I wasn't seeing it.

Gnurrd
  • 11
  • 6