Click here to Skip to main content
12,503,838 members (32,459 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB VB.NET
Dear Sir,

I have used ms access database and connect through ADODB and Jet Oledb, But
I want to change this to Sql Connection Below is the function to open database i used, please tell me were i change it to connect through sql server database :-

Public Function OpenDatabase() As Boolean
        Dim Find As Boolean = False
        Dim PenPath As String = ""
 
        For i As Byte = 70 To 82
 
            If System.IO.Directory.Exists(Microsoft.VisualBasic.Chr(i) & ":\JyothiIMS") = True Then
                Microsoft.VisualBasic.FileSystem.ChDrive(Microsoft.VisualBasic.Chr(i) & ":\")
                Microsoft.VisualBasic.FileSystem.ChDir(Microsoft.VisualBasic.Chr(i) & ":\JyothiIMS")
                PenPath = Microsoft.VisualBasic.Chr(i) & ":\JyothiIMS"
                Find = True
                Exit For
            End If
        Next
 

        If Find = True Then
            APPApplicationPath = Microsoft.VisualBasic.FileSystem.CurDir
            DefaultBackupPath = Microsoft.VisualBasic.FileSystem.CurDir + "\Backup"
            APPDataBasePath = Microsoft.VisualBasic.FileSystem.CurDir + "\inventory.mdb"
            APPPhotoPath = Microsoft.VisualBasic.FileSystem.CurDir + "\images\"
        Else
            APPApplicationPath = Microsoft.VisualBasic.FileSystem.CurDir
            DefaultBackupPath = Microsoft.VisualBasic.FileSystem.CurDir + "\Backup"
            APPDataBasePath = Microsoft.VisualBasic.FileSystem.CurDir + "\inventory.mdb"
            APPPhotoPath = Microsoft.VisualBasic.FileSystem.CurDir + "\images\"
        End If
 

        Try
 
            Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APPDataBasePath & ";Jet OLEDB:Database Password=prakash"
            DbfConnString = Conn.ConnectionString
            Conn.Open()
            OpenDatabase = True
            Dim dbf As New ADODB.Recordset
 
            dbf.Open("select * from company", Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
            If dbf.RecordCount > 0 Then
                dbf.Close()
            Else
                dbf.Close()
                Company.ShowDialog()
                dbf.Open("select * from company", Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
                If dbf.RecordCount > 0 Then
                    dbf.Close()
               Else
                    dbf.Close()
                    Conn = Nothing
                    ISDataBaseOpen = False
                End If
            End If
            dbf.Open("Select * from Company", Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
            dbf.MoveFirst()
            CompanyDetails.Name = dbf.Fields("CmpName").Value.ToString
            CompanyDetails.Title = dbf.Fields("CmpTitle").Value.ToString
            CompanyDetails.Address = dbf.Fields("CmpAddress").Value.ToString
            CompanyDetails.City = dbf.Fields("cmpCity").Value.ToString
            CompanyDetails.State = dbf.Fields("CmpState").Value.ToString
            CompanyDetails.Country = dbf.Fields("CmpCountry").Value.ToString
            CompanyDetails.phone1 = dbf.Fields("CmpContact1").Value.ToString
            CompanyDetails.phone2 = dbf.Fields("CmpContact2").Value.ToString
            CompanyDetails.Tin = dbf.Fields("CmpTinNo").Value.ToString
            CompanyDetails.SalesTax = dbf.Fields("CmpStNo").Value.ToString
            CompanyDetails.Version = dbf.Fields("CmpVersion").Value
            CompanyDetails.DbName = dbf.Fields("CmpDBName").Value.ToString
            CompanyDetails.TrailCount = dbf.Fields("TrailCount").Value
            CompanyDetails.Istrail = dbf.Fields("IsTrail").Value
            CompanyDetails.ImageFolder = dbf.Fields("ImageFolder").Value.ToString
            CompanyDetails.DbServer = dbf.Fields("DatabaseServer").Value.ToString
            CompanyDetails.DbPassword = dbf.Fields("Databasepwd").Value.ToString
            CompanyDetails.CurrentDate = dbf.Fields("CreateDate").Value
            CompanyDetails.PeriodFrom = dbf.Fields("PeriodFrom").Value
            CompanyDetails.PeriodTo = dbf.Fields("PeriodTo").Value
            CompanyDetails.IsFixedDate = dbf.Fields("IsFixedDate").Value
            CompanyDetails.CompanyType = dbf.Fields("CompanyType").Value
            CompanyDetails.IsAutobackup = dbf.Fields("AutoBackup").Value
            CompanyDetails.SoftwareKey = dbf.Fields("softwarekey").Value.ToString
            CompanyDetails.ProductKey = dbf.Fields("ProID").Value.ToString
            CompanyDetails.BackupPath = dbf.Fields("BackupPath").Value.ToString
            CompanyDetails.CPUID = dbf.Fields("cpucode").Value.ToString
            CompanyDetails.HDDID = dbf.Fields("hddcode").Value.ToString
            CompanyDetails.UpadateCode = dbf.Fields("tobeupdate").Value
            ApplicationStoredPath = dbf.Fields("ApplicationPath").Value.ToString
 
            'MsgBox(dbf.Fields("CompanyType").Value)softwarekey

            If CompanyDetails.CompanyType = 0 Then
                CompanyType = CompanyTypeStruct.Simple
            ElseIf CompanyDetails.CompanyType = 1 Then
                CompanyType = CompanyTypeStruct.Simplewithvat
            ElseIf CompanyDetails.CompanyType = 2 Then
                CompanyType = CompanyTypeStruct.advance
            ElseIf CompanyDetails.CompanyType = 3 Then
                CompanyType = CompanyTypeStruct.advancewithvat
            ElseIf CompanyDetails.CompanyType = 4 Then
                CompanyType = CompanyTypeStruct.AdvanceSimpleWithoutColor
            ElseIf CompanyDetails.CompanyType = 5 Then
                CompanyType = CompanyTypeStruct.AdvanceWithoutColor
 
            ElseIf CompanyDetails.CompanyType = 6 Then
                CompanyType = CompanyTypeStruct.AdvancedWithADDDiscountLVH
 
            End If
 
            DefaultBackupPath = CompanyDetails.BackupPath
            dbf.Close()
            If PasswordEntry.ShowDialog() = DialogResult.Cancel Then
                OpenDatabase = False
                ISDataBaseOpen = False
            Else
                ISDataBaseOpen = True
            End If
 
        Catch ex As Exception
            OpenDatabase = False
            Try
                Conn.Close()
            Catch ex3 As Exception
 
            End Try
            Conn = Nothing
            ISDataBaseOpen = False
            MsgBox(ex.Message)
        End Try
    End Function
Rgds,
Indranil
Posted 15-Jan-13 3:03am
hspl305
Updated 15-Jan-13 3:29am
v2
Comments
Abhishek Pant 15-Jan-13 10:11am
   
just change your connection string.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Take a look at http://www.connectionstrings.com/[^] and select which SQL version you want to connect.

After that you should change the line
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APPDataBasePath & ";Jet OLEDB:Database Password=prakash"

To what you need.

BR,
VG
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 15 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100