Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO.NET in a VB Module...

Posted on 2004-11-04
2
Medium Priority
?
428 Views
Last Modified: 2010-04-23
Hello, How do i convert the following code from this to ADO.NET
=========================================
Imports ADODB.CursorLocationEnum
Imports ADODB.CursorTypeEnum
Imports ADODB.LockTypeEnum
Module modGeneralDBCalls

    Public conn_CMS As New ADODB.Connection
    Public conn_cas_acc_usernames As New ADODB.Connection

    Public ServerName As String = "localhost"
    Public UserName As String = "root
    Public PassWord As String = "password
    Public db_name As String
    Public tb_name As String

    Public Function GetUserDB(ByRef U_ID As String, Optional ByVal Key As String = "", Optional ByVal keytext As String = "") As String
        'This pulls up general info about the internal CMS user
        On Error Resume Next
        If conn_CMS.State = 1 Then conn_CMS.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}" & ";SERVER=" & ServerName & ";DATABASE=" & db_name & ";UID=" & UserName & ";PWD=" & PassWord & ";OPTION=3"
        conn_CMS.Open()
        Dim rs As ADODB.Recordset
        tb_name = "UserID"
        db_name = "CMS"
        rs = New ADODB.Recordset
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer
        Dim strData As String
        strData = "U_ID = '" & U_ID & "'"
        rs.Open("select * from " & tb_name, conn_CMS, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
        rs.Find(strData)
        GetUserDB = rs.Fields(Key).Value
        If keytext <> "" Then
            rs.Fields(Key).Value = keytext
            rs.Update()
        End If
        rs.Close()
        conn_CMS.Close()
        If Err.Number <> 0 Then GetUserDB = "<font color=""#FF0000"">Error: [" & Err.Number & "] " & Err.Description & "</font>"
    End Function
End Module
0
Comment
Question by:wd006451
1 Comment
 
LVL 27

Accepted Solution

by:
planocz earned 80 total points
ID: 12505220
Sample code...
Use a Access DB can change to SQL Server easy...

Public Class frmMainForm
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        '
        'frmMainForm
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.IsMdiContainer = True
        Me.Name = "frmMainForm"
        Me.Text = "frmMainForm"

    End Sub

#End Region
    'Run the application
    'The main entry point for the application
    <STAThread()> Shared Sub Main()
        Dim objForm As New frmLogin
        objForm.ShowDialog()
        If bLogin = True Then
            System.Windows.Forms.Application.Run(New frmMainForm)
        End If
    End Sub
End Class



Public Class frmLogin
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call
        InitializeData()
    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents lblName As System.Windows.Forms.Label
    Friend WithEvents lblPassword As System.Windows.Forms.Label
    Friend WithEvents txtName As System.Windows.Forms.TextBox
    Friend WithEvents txtPassword As System.Windows.Forms.TextBox
    Friend WithEvents btnOK As System.Windows.Forms.Button
    Friend WithEvents btnCancel As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.lblName = New System.Windows.Forms.Label
        Me.lblPassword = New System.Windows.Forms.Label
        Me.txtName = New System.Windows.Forms.TextBox
        Me.txtPassword = New System.Windows.Forms.TextBox
        Me.btnOK = New System.Windows.Forms.Button
        Me.btnCancel = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'lblName
        '
        Me.lblName.Location = New System.Drawing.Point(12, 16)
        Me.lblName.Name = "lblName"
        Me.lblName.Size = New System.Drawing.Size(84, 20)
        Me.lblName.TabIndex = 0
        Me.lblName.Text = "Login Name:"
        '
        'lblPassword
        '
        Me.lblPassword.Location = New System.Drawing.Point(8, 56)
        Me.lblPassword.Name = "lblPassword"
        Me.lblPassword.Size = New System.Drawing.Size(88, 20)
        Me.lblPassword.TabIndex = 1
        Me.lblPassword.Text = "Login Password:"
        '
        'txtName
        '
        Me.txtName.Location = New System.Drawing.Point(100, 16)
        Me.txtName.Name = "txtName"
        Me.txtName.Size = New System.Drawing.Size(160, 20)
        Me.txtName.TabIndex = 2
        Me.txtName.Text = ""
        '
        'txtPassword
        '
        Me.txtPassword.Location = New System.Drawing.Point(100, 56)
        Me.txtPassword.Name = "txtPassword"
        Me.txtPassword.PasswordChar = Microsoft.VisualBasic.ChrW(42)
        Me.txtPassword.Size = New System.Drawing.Size(160, 20)
        Me.txtPassword.TabIndex = 3
        Me.txtPassword.Text = ""
        '
        'btnOK
        '
        Me.btnOK.Location = New System.Drawing.Point(16, 92)
        Me.btnOK.Name = "btnOK"
        Me.btnOK.TabIndex = 4
        Me.btnOK.Text = "&Ok"
        '
        'btnCancel
        '
        Me.btnCancel.Location = New System.Drawing.Point(108, 92)
        Me.btnCancel.Name = "btnCancel"
        Me.btnCancel.TabIndex = 5
        Me.btnCancel.Text = "&Cancel"
        '
        'frmLoginADO
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 125)
        Me.Controls.Add(Me.btnCancel)
        Me.Controls.Add(Me.btnOK)
        Me.Controls.Add(Me.txtPassword)
        Me.Controls.Add(Me.txtName)
        Me.Controls.Add(Me.lblPassword)
        Me.Controls.Add(Me.lblName)
        Me.Name = "frmLogin"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "frmLogin"
        Me.ResumeLayout(False)

    End Sub

#End Region
    Private Sub InitializeData()

        Cursor.Current = Cursors.WaitCursor
        sDBName = "C:\Textbox.mdb"       '<---- A standard access database from Microsoft
        sTableName = "User"

        OpenDBADOConnection()

        sSql = ""
        sSql = "SELECT * "
        sSql += "FROM " & sTableName & " "
        sSql += "ORDER BY Name"
        Try
            'TblAdapter = New SqlDataAdapter(sSql, Connect)            'FOR SQL Server
            'TblAdapter = New Odbc.OdbcDataAdapter(sSQL, SyConnect)    'FOR SyBase Server
            ADOAdapter = New OleDb.OleDbDataAdapter(sSql, Con)         'FOR ADO Access database
            ADOAdapter.Fill(DS)

        Catch Exp As Data.OleDb.OleDbException
            MsgBox("FillDataSet Procedure Error", MsgBoxStyle.Critical, "Load Report Error")
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try
        Cursor.Current = Cursors.Default
    End Sub
    Private Function CheckNameFromDS(ByVal DS) As Boolean
        ' A table in a dataset is used to populate a list box with
        ' product name from the products table.
        ' With the dataset the name of the table and field must be known
        ' at design time. If they are misspelled or mistyped
        ' an error will be generated only at runtime.

        Dim s As String
        Dim i As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the name for each record.
                s = DS.Tables(0).Rows(i)("Name").ToString()  '<----Field name "Name"
                ' Check Name in the Textbox
                If UCase(txtName.Text) = UCase(s) Then
                    'Name was found
                    Return True              
                End If
            End If
        Next i
    End Function
    Private Function CheckPasswordFromDS(ByVal DS) As Boolean
        ' A table in a dataset is used to populate a list box with
        ' product name from the products table.
        ' With the dataset the name of the table and field must be known
        ' at design time. If they are misspelled or mistyped
        ' an error will be generated only at runtime.

        Dim s As String
        Dim i As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the name for each record.
                s = DS.Tables(0).Rows(i)("Password").ToString()   '<----Field name "Password"
                ' Check Name in the Textbox
                If UCase(txtPassword.Text) = UCase(s) Then
                    'Name was found
                    Return True
                End If
            End If
        Next i
    End Function
    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        If txtName.Text <> "" And txtPassword.Text <> "" Then
            If CheckNameFromDS(DS) = True Then
                If CheckPasswordFromDS(DS) = True Then
                    bLogin = True
                    MessageBox.Show("You are IN")
                    Me.Close()
                Else
                    MessageBox.Show("Password not in Database")
                End If
            End If
        Else
            MessageBox.Show("Name or Password is missing")
        End If
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        bLogin = False
        Me.Close()
    End Sub
 End Class


AND last in the Module you should only have this....

Imports ADODB
Imports System.Data.SqlClient
Module ModADO
#Region " MY Global Vars "
    '********* database Var's ************
    Public sSql As String
    Public sTableName As String
    Public myDataRow As DataRow
    Public DS As New DataSet
    Public ADOAdapter As OleDb.OleDbDataAdapter '(ADO)

       '**** Server Connection Vars *********
    Public sDBServer As String               '<--- Server Name
    Public sUSERid As String                 '<--- User Id
    Public sDBName As String                 '<--- Database Name
    Public sDBPass As String                 '<--- Password
    Public Con As OleDb.OleDbConnection      '<--- for Access
    Public Connect As SqlConnection          '<--- for msSQL

    '******* Standard Var's *************
    Public sAppPath As String = Application.StartupPath
    Public bLogin As Boolean
#End Region
#Region " MY Server Connections "
 
    Public Sub OpenDBADOConnection()
        Try
            Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
            Con.Open()
            Con.Close()
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
   Public Sub OpenDBSqlConnection()
        If Len(sDBPass) = 0 Then
            sDBPass = ""
        End If
        Try
            Connect = New SqlConnection("Initial Catalog=" & sDBName & ";Data Source=" & sDBServer & ";User ID=" & sUSERid & ";password=" & sDBPass & ";")
            Connect.Open()
            Connect.Close()
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
#End Region
End Module
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question