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