Making an ODBC Application

[androidz]

Hi

Does anyone here can show me how to make an application that is like an odbc wizard that could register a sql server type connection? I have found one on the net through searching but it does not accept the UID and password of the  sql server.
I also read somewhere in the msdn forum that no one in ms has finished it,and said it is found in the japanese website, i don't if it is true. I have this problem because my report runs through odbc. I am already pulling my hair. please share some of your expertise and knowledge. hoping for the positive replies.

best regards

[androidz]

anyone?

[androidz]

never mind answering this one i know no one can answer this thanks anyways

[androidz]

please close this thread thanks

[smi.im]

1.) Add New Form
2.) Add CrystalReportViewer
3.) Import the following.

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
Imports System.Data.Odbc

Dim rpt1 As New StockReport(Here your report name)
Dim Usr as String
Dim Pwd as String

Usr="sa"
Pwd="xyz"

Rpt1.DataSourceConnections.Item(0).SetLogon(Usr, Pwd)
CrystalReportViewer1.ReportSource = Rpt1
CrystalReportViewer1.Refresh()

This works for me already installed at client and tested.

for more details feel free to contact 

Ibrahim sm
HaPpY CoDiNg

[androidz]

@smi.im sorry for the very late reply however thanks for the help..btw are you using vs20120?because been wondering how did you able to import crystal decisions. but what i did instead i keep pulling my hair on vs2010 trying to figure out why i can't import vs2010, I switch to 2008 now it helps me a lot..

[androidz]

@smi.im btw the report i made is already is an odbc connection so whatever i will configure on the odbcad32 the report will automically get the connection from there..now what i want is to make like odbcad32 instead of going to odbcad32 and configure i know users will hate it that..just a simple registration application that act like odbcad..is that possible?

[androidz]

anyone have idea about what i meant?

[john.knapp]

Try this discussion, see if any of the suggestions help you.

[androidz]

thanks for the info but it seems it wasn't there that i was looking for :D. What i am looking for is to build a registration form and create a DSN,server and database rather than configuring manually it to the odbcad32.exe. I have found one and try to use the method by using odbc api which i am almost near to it but unfortunately it won't acccept the user id and the password using the SQL connection.I've been searching for this answers over the net but i could not found one. :(

[androidz]

here is the code

   Attr = "SERVER=" & machName & "" & Chr(0)
    Attr = Attr & "DSN=" & catelog & Chr(0)
    Attr = Attr & "DESCRIPTION=DSN For HOSPITAL" & Chr(0)
    Attr = Attr & "DATABASE=TEST_DB" & Chr(0)
    Attr = Attr & "uid=sa" & Chr(1) 
    Attr = Attr & "pwd=" & Chr(1)
    Attr = Attr & "TRUSTED_CONNECTION=YES" & Chr(0)

    iReturn = SQLConfigDataSource(0, 1, "SQL Server", Attr) '-- i got a zero value whenever there is a uid and pwd. and it always falls to failed.
    If iReturn Then
        MsgBox("DSN setup complete successfully.", MsgBoxStyle.OKOnly + MsgBoxStyle.Information)
    Else
        MsgBox("DSN setup can not complete successfully.")
    End If

is there something i missed?

[john.knapp]

Trusted Connection uses Windows Authentication, i.e., your Windows UserID & password.
You're then passing a SQL Authentication UserID & password... (Source: SQLConfigDataSource attributes here on MSDN)

BTW, you should never use "sa", that user has full control of the SQL Server instance.

[androidz]

but that was the user id of the database used by the admin.

[androidz]

thanks i will try this one. i will change the trusted_connection to NO so that the sql authentication will work. i will give this a shot thanks for your reply.

[john.knapp]

but that was the user id of the database used by the admin.

See good explanation of why to not use sa here

[androidz]

btw i tried to change the trusted_connection into No but still i can't get it successfully. the ireturn a zero value which is i need to be a 1 to be successfull. any idea?

[john.knapp]

I'll try to replicate the isssue here, but not until later today

[androidz]

ok thanks for your great help..I am also searching for ideas here..if you found one please let me know. i know someday someone will also get stumble into this issue and we solve this,it could give a big benefits to them..thanks ^_^

[john.knapp]

You're using a DLLImport statement to reference the WinAPI call for SQLConfigDataSource(), right? Can you post your Platform Invoke statement please? It should be something like:

Note: untested code, copied from PInvoke.net

Imports System.Runtime.InteropServices

Class NativeMethods
    <DllImport("ODBCCP32.dll", CallingConvention:=CallingConvention.Winapi, _
                            CharSet:=CharSet.Unicode, SetLastError:=True)> _
    Public Shared Function SQLConfigDataSourceW(ByVal hwndParent As Integer, _
                                                ByVal fRequest As Integer, _
                                                ByVal lpszDriver As String, _
                                                ByVal lpszAttributes As String) _
                                                As Integer
    End Function
End Class

[john.knapp]

If you're using SQLExpress you have to specify that as part of the SQL instance name.
For example: "Server = WH306UT\SQLExpress"

I just ran a test using your code, but specifying my machine and SQL instance successfully.

See code below, change server and instance name to match your system (of course!):

Private Sub CreateDSN()

    ' NOTE: This procedure creates a User DSN, **NOT** a System DSN
    '
    Dim strDriver As String
    Dim strAttributes As String
    strDriver = "SQL Server"
    Dim catalog As String = "Hospital_DSN"
    '
    ' NOTE THE INSTANCE NAME
    strAttributes = "SERVER=WH306UT\SQLExpress" & Chr(0)
    'strAttributes = "SERVER=" & machName & "" & Chr(0)
    '
    ' THESE ARE YOUR CONNECTION ATTRIBUTES BELOW
    strAttributes = strAttributes & "DSN=" & catalog & Chr(0)
    strAttributes = strAttributes & "DESCRIPTION=DSN For HOSPITAL" & Chr(0)
    strAttributes = strAttributes & "DATABASE=TEST_DB" & Chr(0)
    strAttributes = strAttributes & "TRUSTED_CONNECTION=YES" & Chr(0)

    ' IF YOU WANT TO SEE THE ODBCAD32.exe DIALOG 
    ' PASS THE WINDOW HANDLE OF THIS FORM AS THE FIRST ARGUMENT
    ' EX: SQLConfigDataSourceW(Me.Handle, 1, "SQL Server", strAttributes)
    '
    Dim iReturn As Integer = PInvokeImports.SQLConfigDataSourceW(CType(0, IntPtr), _
                                                    1, "SQL Server", strAttributes)
    If CBool(iReturn) Then
        MsgBox("DSN setup complete successfully.", _
                CType(MsgBoxStyle.OkOnly + MsgBoxStyle.Information, MsgBoxStyle))
    Else
        MsgBox("DSN setup can not complete successfully.")
    End If
End Sub

PInvokeImports Class (in a separate code file):

Imports System.Runtime.InteropServices

Public Class PInvokeImports
    'Const ODBC_ADD_DSN = 1           ' Add data source
    'Const ODBC_CONFIG_DSN = 2        ' Configure (edit) data source
    'Const ODBC_REMOVE_DSN = 3        ' Remove data source

    <DllImport("ODBCCP32.dll", CallingConvention:=CallingConvention.Winapi, _
                CharSet:=CharSet.Unicode, SetLastError:=True)> _
    Public Shared Function SQLConfigDataSourceW(ByVal hwndParent As IntPtr, _
                                                ByVal fRequest As Integer, _
                                                ByVal lpszDriver As String, _
                                                ByVal lpszAttributes As String) _
                                                As Integer
    End Function
End Class

[john.knapp]

Correction! Code above is for a Trusted Connection - using Windows Security, having difficulty myself with SQL security

[TnTinMN]

Good job John!

I never thought to to search for an api function to create a user DSN (Doh!) and have done it by directly modifying the the registry.

For those interested in the registry route, I have an example for creating/deleting a Dbase DSN at this link:

Click Here

[john.knapp]

Thanks - but... I can't take that prop, the OP found it, I'm just trying to get the SQL Auth to work - Windows Security works like a champ, but not so much with SQL Security

[john.knapp]

I am unable to find any documentation stating UID or PWD are valid Key/Value pairs (attributes) in SQLConfigDataSource().

I can change my attributes to use TRUSTED_CONNECTION=NO and as long as I do not pass the UID & PWD pairs - I still get the ODBCAD32 dialog. As soon as I try to pass those pairs, the connection returns an error.

My best suggestion is to use Windows Authentication, or use the ODBCAD32 dialog to input the UserID and Password for the connection (That does work!)

[john.knapp]

FWIW, the key/value pairs are shown as a byte[] array in a SQL Trace... :\

[androidz]

sorry for the late reply been busy this following days. Anyways I tried the code it only works for the windows configuration. Maybe there is a substitute prop for UID and PWD hope there is :)

[john.knapp]

Use Trusted_Connection = NO, leave off UID and PWD, invoke the dialog and go from there.

I would use Windows Security and make SQL User Groups instead, good luck.

[androidz]

what do you mean by use windows security and make sql user groups?yes i tried already trusted connection = NO but still it wont work.

[john.knapp]

what do you mean by use windows security and make sql user groups

I mean, use TRUSTED_CONNECTION=YES, that will force the DSN to use Windows Security (your users' windows login identity), then you do not have to pass the UID & PWD pair - which is apparently not supported by SQLConfigDataSource().

Alternatively, if you have to use SQL Security (users are setup individually in SQL Server with UID & PWD) - you use the Connection Attribute setup as detailed here, but change the first parameter from NULL to Me.Handle (as detailed in the code comments) to invoke the ODBCAD32 dialog to input username (UID) and password (PWD).

I've also found some sample code here at MSDN that uses SQLDriverConnect() to connect to a SQL Server - that API does prompt for a Username and Password if not supplied. I just ran that sample (C++ code) and am evaluating it to determine whether I can trim it down to just the UID & PWD prompt, then import it as a VB project reference (compiled as C++ DLL).

[john.knapp]

Bump... SQLDriverConnect looks to be the way to go.

SQLConfigDataSource() will not accept the UID/PWD key value pair, and in fact will not even show the ODBCAD32 dialog if you try to pass in even just the UID.

The example code at MSDN, mentioned in the previous post here, does prompt for UID & PWD, which is what we need. Be aware however, you will not be able to save those values in the DSN.

The ODBC API documents some other methods as well, including at least one that will write to ODBC.ini