Click here to Skip to main content
11,633,978 members (76,107 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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
hspl249
Edited 15-Jan-13 3:29am
v2
Comments
Abhishek Pant at 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
0 OriginalGriff 8,658
1 Sergey Alexandrovich Kryukov 8,204
2 Mika Wendelius 6,645
3 F-ES Sitecore 2,321
4 Suvendu Shekhar Giri 2,095


Advertise | Privacy | Mobile
Web04 | 2.8.150728.1 | Last Updated 15 Jan 2013
Copyright © CodeProject, 1999-2015
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