dcsimg
Page 3 of 3 FirstFirst 123
Results 81 to 86 of 86

Thread: SQL server connection string

  1. #81
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    837

    Re: SQL server connection string

    Code:
    For i = 0 To 7
        If i = 0 Then B0 = S(i)
        If i = 1 Then B1 = S(i)
        If i = 2 Then B2 = S(i)
        If i = 3 Then B3 = S(i)
        If i = 4 Then B4 = S(i)
        If i = 5 Then B5 = S(i)
        If i = 6 Then B6 = S(i)
        If i = 7 Then B7 = S(i)
    Next i
    Classic.

  2. #82
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,405

    Re: SQL server connection string

    Ok, code-handout #2 ...

    It's the only two routines you need to add to the serverside Code in cHandler.cls, to be able to cover any CRUD-scenario
    (for almost any DBEngine which is addressable over ADO - be it serverside "Jet-MDBs" or serverside instances of MS-SQLServer, etc.)

    Code:
    Public Sub GetRs()
      On Error Resume Next
      Dim SQL As String: SQL = RsIn(1).Value
     
      Select Case RsIn.Fields.Count - 1
        Case 1: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL)
        Case 2: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value)
        Case 3: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value)
        Case 4: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value, RsIn(4).Value)
        Case 5: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value, RsIn(4).Value, RsIn(5).Value)
      End Select
    End Sub
    
    Public Function UpdateBatch()
      On Error Resume Next
      Dim Rs As ADODB.Recordset: Set Rs = GetRsFromByteContent(RsIn(1).Value)
      
      Dim Success As Boolean: Success = modADODB.UpdateBatch(OpenCnn(strConn), Rs)
      Set RsOut = CreateResultRs("UpdateBatchSuccess", Success) 'pass the boolean result back to the caller
    End Function
    Note, that both routines expect a predefined Connection-String in the declaration-section of cHandler.cls -
    e.g. via a Const like I did for the NorthWnd Test-DB which exists in my local MS-SQLServer instance:
    Code:
    Const strConn = "Provider=SQLOleDB.1;Data Source=(local);Initial Catalog=NorthWnd;Integrated Security=SSPI;"
    For those who want to test this with a "NorthWind.mdb" - just change the above Const to the appropriate ConnStr for ADO-Jet-4.0.

    Ok, with those few additions in cHandler.cls, the serverside topic can be closed (as far as DB-access via CRUD-ops is concerned).

    The only thing remaining is, how to use the above two Handler-Functions properly from Client-Code:

    1. CRUD: Clientside RemoteCalls for Creating a new record in a given Table (using GetRs in conjunction with UpdateBatch):
    Code:
    Private Sub cmdAddNewCustomerCust1_Click()
      Dim RsForAdd As ADODB.Recordset
      Set RsForAdd = RPC.DoRPC("GetRs", "Select * From Customers Where 1=0") 'get an empty Rs, with only the FieldNames in it 
      
      If NoErrorIn(RsForAdd) Then
         RsForAdd.AddNew 'add a new Record to the (recordwise) yet empty Rs
          RsForAdd("CustomerID").Value = "Cust1" 'this CustomerID is hardwired in  this Click-Handler (write a proper AddNewCustomer-Function for  production-use)
         RsForAdd("CompanyName").Value = "New CompanyName 1"
         ' etc. for more FieldNames on this new to insert Record...
    
         Dim RsBoolResult As ADODB.Recordset
          Set RsBoolResult = RPC.DoRPC("UpdateBatch",  GetByteContentFromRs(RsForAdd)) 'pass the serialized bytes of RsForAdd  to UpdateBatch
         If NoErrorIn(RsBoolResult) Then
            MsgBox "AddNew was " & IIf(RsBoolResult(0).Value, "successful", "failing")
         End If
      End If
    End Sub
    2. CRUD: Clientside RemoteCall for Reading of arbitrary Rs (via the SQL-injection-save GetRs-Handler):
    Code:
    Private Sub cmdGetSingleCustomerCust1_Click()
      Dim RsResult As ADODB.Recordset
      Set RsResult = RPC.DoRPC("GetRs", "Select * From Customers Where CustomerID=?", "Cust1")
      
      If NoErrorIn(RsResult) Then VisualizeRs RsResult
    End Sub
    3. CRUD: Clientside RemoteCalls for Updating a given Record in a given Table (using GetRs in conjunction with UpdateBatch):
    Code:
    Private Sub cmdUpdateSingleCustomerCust1_Click()
      Dim RsForUpd As ADODB.Recordset
       Set RsForUpd = RPC.DoRPC("GetRs", "Select * From Customers Where CustomerID=?", "Cust1") 'get a specific Rs - here for CustomerID = "Cust1"
      
      If NoErrorIn(RsForUpd) Then
        If RsForUpd.RecordCount = 0 Then MsgBox "A Record with CustomerID '" & "CustID" & "' does not exist": Exit Sub
        RsForUpd("CompanyName").Value = "Updated CompanyName 1"
         ' etc. for more FieldNames on this updatable Record...
    
         Dim RsBoolResult As ADODB.Recordset
          Set RsBoolResult = RPC.DoRPC("UpdateBatch",  GetByteContentFromRs(RsForUpd)) 'pass the serialized bytes of RsForUpd  to UpdateBatch
         If NoErrorIn(RsBoolResult) Then
            MsgBox "Update for 'Cust1' was " & IIf(RsBoolResult(0).Value, "successful", "failing")
         End If
      End If
    End Sub
    4. CRUD: Clientside RemoteCalls for Deleting a given Record in a given Table (using GetRs in conjunction with UpdateBatch):
    Code:
    Private Sub cmdDeleteSingleCustomerCust1_Click()
      Dim RsForDel As ADODB.Recordset
      Set RsForDel = RPC.DoRPC("GetRs", "Select CustomerID From Customers Where CustomerID=?", "Cust1") 'get a specific Rs - here for CustomerID = "Cust1"
    
      If NoErrorIn(RsForDel) Then
         If RsForDel.RecordCount = 0 Then MsgBox "A Record with CustomerID '" & "CustID" & "' does not exist": Exit Sub
         RsForDel.Delete
    
         Dim RsBoolResult As ADODB.Recordset
         Set RsBoolResult = RPC.DoRPC("UpdateBatch", GetByteContentFromRs(RsForDel)) 'pass the serialized bytes of RsForDel to UpdateBatch
         If NoErrorIn(RsBoolResult) Then
            MsgBox "Delete for 'Cust1' was " & IIf(RsBoolResult(0).Value, "successful", "failing")
         End If
      End If
    End Sub
    So that's it with regards to CRUD-Operations, demonstrated on the NorthWnd-DB-Table [Customers]...
    Currently all within CommandButton-ClickEvent-Handlers - which is not really nice as it is ...
    Pleae write your own clientside cDBLayer.cls, which wraps these operations in dedicated Functions, to lower the code-amount in your Button- and other Click-Handlers)

    HTH

    Olaf

  3. #83

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: SQL server connection string

    Thanks Olaf,

    It is a lot for me to absorb and try and understand. I will try it out and try to figure it out - and thanks so much for the help you have given me.
    What I know now is that that I know a lot more today than I knew some months ago, thanks for your persistence to try and teach it to me.
    With respect I must say that I never pretended or professed to know or understand it well.
    I tried to understand your code and figure it out as we went along, much to your chagrin, but it was all new to me and still is, even to my own chagrin.
    I am sorry that I could not reach that level of understanding that you wanted from me.

    PK

  4. #84
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,405

    Re: SQL server connection string

    Before you start with real-world implementations -
    please add the following Helper *.bas-Module into your clientside Form-Project (and not the Server-Dll-Project within the Debug-Group).

    Name it modRemoteDB and save it to modRemoteDB.bas.

    Code:
    Option Explicit
    
    Public RPC As New cRPC '<- this is now publically defined - and available throughout your clientside Form-Project
    
    '*** generic helpers ***
    Public Function GetRs(SQL As String, ParamArray P()) As ADODB.Recordset
      Dim Rs As ADODB.Recordset
      Select Case UBound(P)
        Case -1: Set Rs = RPC.DoRPC("GetRs", SQL)
        Case 0:  Set Rs = RPC.DoRPC("GetRs", SQL, P(0))
        Case 1:  Set Rs = RPC.DoRPC("GetRs", SQL, P(0), P(1))
        Case 2:  Set Rs = RPC.DoRPC("GetRs", SQL, P(0), P(1), P(2))
        Case 3:  Set Rs = RPC.DoRPC("GetRs", SQL, P(0), P(1), P(2), P(3))
      End Select
      If NoErrorIn(Rs) Then Set GetRs = Rs
    End Function
    
    Public Function UpdateBatch(Rs As ADODB.Recordset) As Boolean
      Dim RsBoolResult As ADODB.Recordset
      Set RsBoolResult = RPC.DoRPC("UpdateBatch", GetByteContentFromRs(Rs)) 'pass the serialized Rs-Bytes to UpdateBatch
      UpdateBatch = NoErrorIn(RsBoolResult)
    End Function
    
    Public Function NoErrorIn(RsResult As ADODB.Recordset) As Boolean
      If RsResult(0).Name <> "ErrNumber" Then NoErrorIn = True: Exit Function
      MsgBox "Error " & RsResult(0).Value & ": " & RsResult(1).Value
    End Function
    
    Public Function GetEmptyRsFromTable(TableName As String) As ADODB.Recordset
      Set GetEmptyRsFromTable = GetRs("Select * From [" & TableName & "] Where 1=0")
    End Function
    
    Public Function GetSingleRecordFromTable(TableName As String, IDFieldName As String, IDFieldValue) As ADODB.Recordset
      Dim Rs As ADODB.Recordset
      Set Rs = GetRs("Select * From [" & TableName & "] Where [" & IDFieldName & "]=?", IDFieldValue)
      If Rs Is Nothing Then Exit Function
      If Rs.RecordCount <> 1 Then Exit Function
      Set GetSingleRecordFromTable = Rs
    End Function
    
    Public Function AddNewRecordToTable(TableName As String, F_V As Scripting.Dictionary) As Boolean
      Dim Rs As ADODB.Recordset, FieldName
      Set Rs = GetEmptyRsFromTable(TableName)
      If Rs Is Nothing Then Exit Function 'return False
      
      Rs.AddNew
      For Each FieldName In F_V
        Rs(FieldName).Value = F_V(FieldName)
      Next
    
      AddNewRecordToTable = UpdateBatch(Rs)
    End Function
    
    Public Function UpdateRecordInTable(TableName As String, IDFieldName As String, IDFieldValue, F_V As Scripting.Dictionary) As Boolean
      Dim Rs As ADODB.Recordset, FieldName
      Set Rs = GetSingleRecordFromTable(TableName, IDFieldName, IDFieldValue)
      If Rs Is Nothing Then Exit Function 'return False
     
      For Each FieldName In F_V
        Rs(FieldName).Value = F_V(FieldName)
      Next
    
      UpdateRecordInTable = UpdateBatch(Rs)
    End Function
    
    Public Function DeleteRecordFromTable(TableName As String, IDFieldName As String, IDFieldValue) As Boolean
      Dim Rs As ADODB.Recordset
      Set Rs = GetRs("Select [" & IDFieldName & "] From [" & TableName & "] Where [" & IDFieldName & "]=?", IDFieldValue)
      If Rs.RecordCount <> 1 Then Exit Function
      
      Rs.Delete
      DeleteRecordFromTable = UpdateBatch(Rs)
    End Function
    The purpose of the above module is, to keep your Form- and UserControl-EventHandler-routines as small (DB-Code-wise) as possible.
    The Helper-Functions in this module are generic (kind of "universally usable for all DB-Tables").

    After you've added it to the clientside Form-Project - please ensure also a reference to the 'Microsoft Scripting Runtime' in that Form-Project via the References-Dialogue
    (so that the usage of the MS-Dictionary becomes possible).
    One last thing to adjust: Please remove (in the fTest.frm of the clientside Project) the line:
    Private RPC As New cRPC
    since that line is now contained in the above new *.bas Module-Code.

    So, with the new Helper-Bas (and the just mentioned other two adjustments in place),
    the "still quite wordy" Demo-Code for the 4 Crud-Operations from my last post, can be written this way now:

    Code:
    Private Sub cmdCreate_Click()
      Dim F_V As New Scripting.Dictionary
          F_V.Add "CustomerID", "Cust1"
          F_V.Add "CompanyName", "New Company 1"
          F_V.Add "ContactName", "New Contact 1"
          
      If modRemoteDB.AddNewRecordToTable("Customers", F_V) Then
         VisualizeRs modRemoteDB.GetRs("Select * From Customers Where CustomerID=?", "Cust1")
      Else
         Me.Caption = "Insert-Operation failed"
      End If
    End Sub
    
    Private Sub cmdRead_Click()
      Dim Rs As ADODB.Recordset
      Set Rs = modRemoteDB.GetRs("Select * From Customers Where CustomerID=?", "Cust1")
      If Rs Is Nothing Then Me.Caption = "Read-Operation failed" Else VisualizeRs Rs
    End Sub
    
    Private Sub cmdUpdate_Click()
      Dim F_V As New Scripting.Dictionary
          F_V.Add "CompanyName", "Updated Company 1"
          F_V.Add "ContactName", "Updated Contact 1"
          
      If modRemoteDB.UpdateRecordInTable("Customers", "CustomerID", "Cust1", F_V) Then
         VisualizeRs modRemoteDB.GetRs("Select * From Customers Where CustomerID=?", "Cust1")
      Else
         Me.Caption = "Update-Operation failed"
      End If
    End Sub
    
    Private Sub cmdDelete_Click()
      If modRemoteDB.DeleteRecordFromTable("Customers", "CustomerID", "Cust1") Then
         VisualizeRs modRemoteDB.GetRs("Select * From Customers Where CustomerID=?", "Cust1")
      Else
         Me.Caption = "Delete-Operation failed"
      End If
    End Sub
    So the 4 Buttons are now named more generically as: cmdCreate, cmdRead, cmdUpdate, cmdDelete -
    because their Handlers contain "more generic code" as well.
    Meaning, that with the new helper-functions - you should be able to write sparse Remote-Commands,
    to apply CRUD-Ops to any Table within your DB.

    As for an explanation of the F_V As New Dictionary (which are used in cmdCreate and cmdUpdate Handlers):
    This is the new introduced MS-Dictionary, which allows to "gather" the concrete Field-Values you want to Insert or Update,
    before passing them into the generic AddNew or Update-Helpers (a Dictionary instead of a normal VB-Collection,
    because it offers a ReadOut-capability for both - the added Keys (in our case FieldNames) - and the added Variant-Values.

    HTH

    Olaf

  5. #85

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: SQL server connection string

    Thanks Olaf,

    I appreciate your continued help and support.
    I hope I can ask (intelligent?) questions as I proceed to implement these and for that I intend to start a new thread on the Implementation of RPC.
    I you are interested I can also report on my progress.
    PK

  6. #86

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: SQL server connection string

    I am implementing this now:

    Code:
    If NoErrorIn(RsResult) Then
        Dim B() As Byte: B() = RsResult(0).Value
        WriteByteArrayContentToFile B, App.Path & "\MyClientsideImage.jpg"
     End If
    What I need to find out is the following:

    1. I do not know what or where I can find these routines to write the byte-array to file or how to write it (I might have missed it somewhere): WriteByteArrayContentToFile and ReadByteArrayContentFromFile
    2. I would think that to protect the data in the file from being copied, I should save it to an in memory file.
    3. The nearest I could find out to write bytes to files is with: File.​Write​All​Bytes(String, Byte[]) Method - but I have never worked with it.

    Thanks
    PK

Page 3 of 3 FirstFirst 123

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width