SQL Tables?

When you set the CalendarControl.SetDataProvider "Provider=custom;"
what happens is that the CalendarControl will RaiseEvents like DoCreatexxx, DoReadxxx etc.
You need to intercept these events and read or write to your SQLServer connection.

Here is a link to: http://www.connectionstrings.com where you can find many more sample connection strings

Look at the OpenProvider function in frmMain and change to the following...

Public Sub OpenProvider(ByVal eDataProviderType As CodeJockCalendarDataType, ByVal strConnectionString As String)
dim strConnect As String

   'Here is a connection string to a sql server database.
   strConnect=""
   strConnect= strConnect & ""Provider=SQLOLEDB;"
   strConnect= strConnect & "Driver={SQL Server};"
   strConnect= strConnect & "Integrated_Security=False;"
   strConnect= strConnect & "Persist Security Info=false;"
   strConnect= strConnect & "Connect Timeout=30;"
   strConnect= strConnect & "DataTypeCompatibility=80;"
   strConnect= strConnect & "Trusted Connection=false;"
   strConnect= strConnect & "Application Name= My_ApplicationName;"
   strConnect= strConnect & "Network=DBMSSOCN;(Local)\SQLEXPRESS,1455;"
   strConnect= strConnect & "User ID=MyUser_ID;"
   strConnect= strConnect & "Password=MyUser_Password;"
   strConnect= strConnect & "Initial Catalog=MySQLServer_Database;"

   Set m_pCustomDataHandler = Nothing

    ' SQL Server provider
    Set m_pCustomDataHandler = New providerSQLServer
    m_pCustomDataHandler.OpenDB strConnect

    m_pCustomDataHandler.SetCalendar CalendarControl

    CalendarControl.SetDataProvider "Provider=custom;"

    If eDataProviderType = cjCalendarData_SQLServer Or eDataProviderType = cjCalendarData_MySQL Then
        CalendarControl.DataProvider.CacheMode = xtpCalendarDPCacheModeOnRepeat
    End If

    If Not CalendarControl.DataProvider.Open Then
        CalendarControl.DataProvider.Create
    End If

    m_eActiveDataProvider = eDataProviderType

    CalendarControl.Populate
    wndDatePicker.RedrawControl

End Sub

In the providerSQLServer class change the OpenDB function to...

Public Sub OpenDB(ByVal strConnection As String)
On Error GoTo err1
m_Connection.Open strConnection
m_bOpened = True

err1:
    If Err.Number <> 0 Then
        m_bOpened = False
        MsgBox "Cannot open SQL Server DB: " & Err.Description
    End If
End Sub

You will need to create the tables and fields in in your sql server database yourslef. I have not tested if the following in the OpenProvider function will create the tables if you are connected to a sql server database.

   If Not CalendarControl.DataProvider.Open Then
        CalendarControl.DataProvider.Create
    End If

You might want to try and see for yourself.

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
JasonG Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 07 July 2008 Status: Offline Points: 76	Post Options Post Reply Quote JasonG Report Post Thanks(0) Quote Reply Topic: SQL Tables? Posted: 21 July 2008 at 2:07pm
	I am trying to get the calendar control to work with my SQL server as the backend, but I can not find any documentation as to the Tables (and their fields) that need to exist in order for this to work properly... It seems I am able to connect to the database OK and set the Calendar.Dataprovider... What next??? Any guidance would be greatly appreciated.
	Product: Xtreme SuitePro (ActiveX) 12.0.1 Platform: Windows Vista/XP Language: Visual Basic 6.0 SP6

Stilki Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 27 May 2005 Status: Offline Points: 70	Post Options Post Reply Quote Stilki Report Post Thanks(0) Quote Reply Posted: 24 July 2008 at 12:40am
	There is a sample in the Calendar samples on how to do this. Check out the VB\Calendar sample and look in the code for 'Provider=Custom' in frmMain. Also there are two classes that do the work: providerSQLServer.cls SQLDataHelper.cls

JasonG Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 07 July 2008 Status: Offline Points: 76	Post Options Post Reply Quote JasonG Report Post Thanks(0) Quote Reply Posted: 24 July 2008 at 8:34am
	I see that and I saw that demo... but doesnt this demo use XML and not a SQL Server? My confusion comes in here.... 1) If I am only specifying connectionString="provider=custom" then where (and how) do I specify the actual SQL server address, port, etc? Do I need to setup an actual ODBC Provider? That would be less than ideal - I have constants with all that stuff in it anyway... can someone provide a sample connection string for a sql server? 2) Do the classes actually create the tables/fields required to hold the data?
	Product: Xtreme SuitePro (ActiveX) 12.0.1 Platform: Windows Vista/XP Language: Visual Basic 6.0 SP6

Stilki Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 27 May 2005 Status: Offline Points: 70	Post Options Post Reply Quote Stilki Report Post Thanks(0) Quote Reply Posted: 24 July 2008 at 9:27pm
	1) When you set the CalendarControl.SetDataProvider "Provider=custom;" what happens is that the CalendarControl will RaiseEvents like DoCreatexxx, DoReadxxx etc. You need to intercept these events and read or write to your SQLServer connection. Here is a link to: http://www.connectionstrings.com where you can find many more sample connection strings Look at the OpenProvider function in frmMain and change to the following... Public Sub OpenProvider(ByVal eDataProviderType As CodeJockCalendarDataType, ByVal strConnectionString As String) dim strConnect As String 'Here is a connection string to a sql server database. strConnect="" strConnect= strConnect & ""Provider=SQLOLEDB;" strConnect= strConnect & "Driver={SQL Server};" strConnect= strConnect & "Integrated_Security=False;" strConnect= strConnect & "Persist Security Info=false;" strConnect= strConnect & "Connect Timeout=30;" strConnect= strConnect & "DataTypeCompatibility=80;" strConnect= strConnect & "Trusted Connection=false;" strConnect= strConnect & "Application Name= My_ApplicationName;" strConnect= strConnect & "Network=DBMSSOCN;(Local)\SQLEXPRESS,1455;" strConnect= strConnect & "User ID=MyUser_ID;" strConnect= strConnect & "Password=MyUser_Password;" strConnect= strConnect & "Initial Catalog=MySQLServer_Database;" Set m_pCustomDataHandler = Nothing ' SQL Server provider Set m_pCustomDataHandler = New providerSQLServer m_pCustomDataHandler.OpenDB strConnect m_pCustomDataHandler.SetCalendar CalendarControl CalendarControl.SetDataProvider "Provider=custom;" If eDataProviderType = cjCalendarData_SQLServer Or eDataProviderType = cjCalendarData_MySQL Then CalendarControl.DataProvider.CacheMode = xtpCalendarDPCacheModeOnRepeat End If If Not CalendarControl.DataProvider.Open Then CalendarControl.DataProvider.Create End If m_eActiveDataProvider = eDataProviderType CalendarControl.Populate wndDatePicker.RedrawControl End Sub In the providerSQLServer class change the OpenDB function to... Public Sub OpenDB(ByVal strConnection As String) On Error GoTo err1 m_Connection.Open strConnection m_bOpened = True err1: If Err.Number <> 0 Then m_bOpened = False MsgBox "Cannot open SQL Server DB: " & Err.Description End If End Sub 2) You will need to create the tables and fields in in your sql server database yourslef. I have not tested if the following in the OpenProvider function will create the tables if you are connected to a sql server database. If Not CalendarControl.DataProvider.Open Then CalendarControl.DataProvider.Create End If You might want to try and see for yourself.