Setting up DSN programatically in VB gives error

[crystalReporterNew]

Hi,

I have been getting a System.NullReferenceException with the SQLConfigDataSource function. Can somebody please help?
The following is the code:


Public Declare Function SQLCreateDataSource Lib "odbccp32.dll" (ByVal hwnd&, ByVal lpszDS$) As Boolean

Public Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwnd As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Boolean

Private Const ODBC_ADD_DSN = 1&
Private Const ODBC_CONFIG_DSN = 2
Private Const ODBC_REMOVE_DSN = 3

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim settings As String
Dim intret As Long

intret = SQLConfigDataSource(Me.Handle.ToInt64, ODBC_ADD_DSN, "MySQL ODBC 3.51 Driver", "DSN=AppliedEDSN\0""DESCRIPTION=Program\0""SERVER=192.168.1.105\0""ADDRESS=192.168.1.105\0""DATABASE=test\0""UID=root\0""PASSWORD=\0&quot

If intret Then
MessageBox.Show("DSN Added&quot
Else
MessageBox.Show("DSN Entry Failed&quot
End If


End Sub

 

[bboffin]

Visual Basic does not support escape characters in strings so the fourth argument to the SQLConfigDataSource should be:

"DSN=AppliedEDSN" & chr(0) & "DESCRIPTION=Program" & chr(0) & "SERVER=192.168.1.105" & chr(0) & "ADDRESS=192.168.1.105" & chr(0) & "DATABASE=test" & chr(0) & "UID=root" & chr(0) & "PASSWORD=" & chr(0)

The additional null byte required by the API spec will be provided by VB automatically. I must admit I would have thought that semi-colons instead of nulls should work since they do everywhere else in ODBC connection strings.

 

[crystalReporterNew]

Hi,

I tried doing the chr(0) option as well as the semi colons, but none of them work and I still get the same error. Please help :-(

 

[bboffin]

The only other thing that occurs to me is that VB uses Unicode and the API call may be expecting the ASCII single byte character set.

Try using the StrConv function with either the vbNarrow or vbFromUnicode argument.

 

[crystalReporterNew]

Found the answer! Had to import the DLL as follows in VB.NET

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