Page 2 of 3 FirstFirst 123 LastLast
Results 41 to 80 of 86

Thread: SQL server connection string

  1. #41

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

    Re: SQL server connection string

    Double post sorry
    Last edited by Peekay; Apr 18th, 2019 at 05:27 AM.

  2. #42

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

    Re: SQL server connection string

    Hi Olaf,

    Before we move ahead, there are a few things I do not understand.

    1. I understand the routine "ReflectString" quite well, because you use a string to reflect.
    2. In "TransferByteArrays" you also manipulate alpha-numeric characters. Why do you use Bytes for this? I have not worked with byte arrays before, but somehow followed your explanation, but I do not follow it in a methodical or understandable way.
    3. Sometimes you use RsIn(1) and RsIn(2) and other times you use Rsin.Fields(1).value. Should I not be consistent in using the same input type of ADO and abstracting data from it in a consistent manner?
    4. I understand partly that we will mostly work with multiple strings as parameters, so I understand that we need to work in arrays, but I am not sure what is the purpose of working in arrays in one place and with fields in another.

    PK

  3. #43
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    1. I understand the routine "ReflectString" quite well, because you use a string to reflect.
    That's good, because a String is basically the same as a ByteArray (an allocation of consecutive memory - containing a sequence of bytes aka "chars").

    Quote Originally Posted by Peekay View Post
    2. In "TransferByteArrays" you also manipulate alpha-numeric characters. Why do you use Bytes for this?
    I have not worked with byte arrays before, but somehow followed your explanation, but I do not follow it in a methodical or understandable way.
    The emphasis is on "Transfer" in this case -
    the "manipulation of content" (no matter if the byte-sequence is in a string or a bytearray) is not that important -
    it's only there as a kind of "proof", that the passed String or ByteArray was "indeed received and touched" at the serverside...

    Quote Originally Posted by Peekay View Post
    3. Sometimes you use RsIn(1) and RsIn(2) and other times you use Rsin.Fields(1).value.
    Should I not be consistent in using the same input type of ADO and abstracting data from it in a consistent manner?
    When you read out a Value like that:
    SomeValue = RsIn(1)
    then this is absolutely the same as:
    SomeValue = RsIn.Fields(1).Value
    because .Fields is a default-property of the ADO-Recordset-Class - and .Value is a Default-Property of the ADO-Field-Class (which you've addressed via the parentheses).
    (the VB6-compiler kind of "auto-expands" the short-form under the covers).

    If you are into "writing explicit, nice code", then use the longer (expanded form).

    Quote Originally Posted by Peekay View Post
    4. I understand partly that we will mostly work with multiple strings as parameters,
    so I understand that we need to work in arrays,
    but I am not sure what is the purpose of working in arrays in one place and with fields in another.
    The necessity to work with "Rs-Fields" comes from choosing ADO-Rs as the transport-container-format for Input- and Output of the RPC-http-Requests
    (due to the reason, that an ADO-Rs can take up multiple Field-Types in its Variant-Fields - but especially because an ADO-Rs can be serialized to a "sequence of Bytes",
    which is a necessity, when you transfer "stuff over sockets/network-connections".

    As for the similarities between ByteArrays and Strings - you might take a good long look at the following:
    Code:
    Private Sub Form_Load()
      Dim S As String, B() As Byte
      
      'Unicode-WChar-Mode (16Bit=2Bytes per Char)
          S = "ABC"
          B = S 'implicit conversion from String to ByteArray-Type (making a content-copy)
          Debug.Print B, S, UBound(B) + 1, LenB(S)
          
          'explicit filling of B in unicode-mode
          ReDim B(0 To 5) 'Bytes = 6, WChars = 3
                B(0) = 65
                B(2) = 66
                B(4) = 67
          Debug.Print B, S, UBound(B) + 1, LenB(S)
     
     
      'ANSI-Char-Mode (8Bit=1Byte per Char)
          S = StrConv(S, vbFromUnicode) 'make an ANSI-String from the Unicode-String
          B = S 'implicit conversion from String to ByteArray-Type (making a content-copy)
          Debug.Print StrConv(B, vbUnicode), StrConv(S, vbUnicode), UBound(B) + 1, LenB(S)
          
          'explicit filling of B in ANSI-mode
          ReDim B(0 To 2) 'Bytes = 3, Chars = 3
                B(0) = 65
                B(1) = 66
                B(2) = 67
          Debug.Print StrConv(B, vbUnicode), StrConv(S, vbUnicode), UBound(B) + 1, LenB(S)
    End Sub
    HTH

    Olaf

  4. #44

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

    Re: SQL server connection string

    Name:  Resultset.jpg
Views: 937
Size:  8.6 KB

    Here is my code for returning the same string and I can see the Resultset is correct, but it does not print the result in the grid, but prints and error number in stead:

    Public Sub TransferString()
    Dim S As String
    S = RsIn.Fields(1).Value
    Set RsOut = CreateResultRs("TransferString", S)
    End Sub

  5. #45
    Fanatic Member
    Join Date
    Aug 2016
    Posts
    597

    Re: SQL server connection string

    It is painful to follow up the steps for such old man...

  6. #46
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    Here is my code for returning the same string ...

    Public Sub TransferString()
    Dim S As String
    S = RsIn.Fields(1).Value
    Set RsOut = CreateResultRs("TransferString", S)
    End Sub
    Ok, the above looks right now (for transferring or "roundtripping" a simple String).
    Now show me your implementation for an appropriately named serverside HandlerRoutine, which does the same for the ByteArray-Type.

    As for the Errors you might see at the clientside...
    These might occur, when you spend too many seconds in the Serverside routines in "Break-Point-Mode" -
    because the clientside (async) httprequest might have run into a timeout in the meantime.

    If you remove all breakpoints, and start the whole thing from a correctly written clientside DoRPC-call,
    you should see correct results coming in at the clientside (in your Form-Code).

    Olaf

  7. #47

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

    Re: SQL server connection string

    Quote Originally Posted by DaveDavis View Post
    It is painful to follow up the steps for such old man...
    Thanks for the sympathy David, But once I get going nothing will stop me.
    I started with Fortran IV on mainframes in 1968 and still going strong!

    Kind regards

  8. #48

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

    Re: SQL server connection string

    I wanted to try this one:

    Public Sub TransferByteString()
    Dim S As String, B() As Byte
    B = StrConv(RsIn.Fields(1).Value, vbUnicode)
    S = B
    Set RsOut = CreateResultRs("TransferByteString", S)
    End Sub

    However, for some reason my http Send code line gives a 404 error, and I cannot fathom why all of a sudden.

    pk

  9. #49
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    I wanted to try this one:

    Public Sub TransferByteString()
    Dim S As String, B() As Byte
    B = StrConv(RsIn.Fields(1).Value, vbUnicode)
    S = B
    Set RsOut = CreateResultRs("TransferByteString", S)
    End Sub

    However, for some reason my http Send code line gives a 404 error, and I cannot fathom why all of a sudden.
    In that case, you will have to ensure proper http-request-serving again...

    http-error 404 means "resource not found"...
    ...and that in turn means, that the RPC-Request-URL you've speicified in your Client (fTest.frm) Form_Load Event:
    Code:
    Private Sub Form_Load()
      RPC.RPCUrl = "http://localhost/asp/ServerLib.asp"
    ...
    ...cannot be resolved (was not found) by your local IIS-instance on your Test-machine.

    So you will have to make sure, that the ServerLib.asp File exists in your: "C:\InetPub\wwwroot\asp" folder.

    Olaf

  10. #50

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

    Re: SQL server connection string

    Thanks Olaf, that is the problem.
    IIS suddenly disappeared from my Folders and Admin tools. I wonder if it has to do with installing the Chrome virus.
    You had an excellent tutorial about installing IIS for Asp, before you published this one:

    http://www.vbforums.com/showthread.p...g-of-http-RPCs

    I cannot find the thread. Can you please help with a link so that I can do the installation properly? One of the problems I run into is that I do not have authority to make folders under wwwroot.

    Thanks
    PK

  11. #51

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

    Re: SQL server connection string

    Olaf,
    I see I have lost the file serverlib.asp file. Can you please give me the code lines again?
    Thanks
    PK

  12. #52
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    I see I have lost the file serverlib.asp file. Can you please give me the code lines again?
    They are in the "IIS-config-thread" you linked to in post #50 of this thread.

    Olaf

  13. #53

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

    Re: SQL server connection string

    Hi Olaf,
    I did it.

    Here is my click event:
    Code:
    Private Sub Command1_Click()
      Dim B() As Byte, RsResult As ADODB.Recordset, S As String
    ReDim B(0 To 5)
    S = "QWERTY"
    B() = S
      Set RsResult = RPC.DoRPC("TransferByteString", B())
      B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      Debug.Print B
    End Sub
    
    Here is my Handler:
    
    Public Sub TransferByteString()
        Dim S As String, B() As Byte
        B = StrConv(RsIn.Fields(1).Value, vbUnicode)
        S = B
        Set RsOut = CreateResultRs("TransferByteString", S)
    End Sub
    And here is my debug.print from the click event:
    Attached Images Attached Images  
    Last edited by Shaggy Hiker; May 12th, 2019 at 08:36 AM. Reason: Added CODE tags.

  14. #54
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    I did it.
    Yep - now remember, that all the String-to-ByteArray stuff is not really what we need
    (it was just for the bettering of understanding "how to work with bytes", and "why a ByteArray is not that much different from a String").

    What we now need, is (without using any String-Variables):
    - Upload of a ByteArray (from the clientside)
    - Download of a ByteArray (from the serverside)

    So we are back at the task where we did start (in post #23).

    Show me, that you can transfer binary data (in ByteArrays) in both directions
    (from client to server - and from the server to the client) ...
    because that's a prerequisite, to finally transfer ADO-Recordsets in both directions.

    Olaf

  15. #55

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

    Re: SQL server connection string

    Name:  Result03.jpg
Views: 858
Size:  24.0 KB

    Hi Olaf,

    Here is my Client routine:
    Code:
    Private Sub Command1_Click()
    Dim B() As Byte, RsResult As ADODB.Recordset
    ReDim B(0 To 5)
    B(0) = Asc("Q"): B(1) = Asc("W"): B(2) = Asc("E"): B(3) = Asc("R"): B(4) = Asc("T"): B(5) = Asc("Y")
    Debug.Print "Sending from Client in Bytes: " & B(0), B(1), B(2), B(3), B(4), B(5)
    Set RsResult = RPC.DoRPC("TransferByteSentence", B())
    Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
      B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      Debug.Print B
    End Sub

    Here is my Handler routine:
    Code:
    Public Sub TransferByteSentence()
        Dim B() As Byte, i As Integer
        B = StrConv(RsIn.Fields(1).Value, vbUnicode)
        Debug.Print "Received in handler: ";: For i = 0 To 11: Debug.Print B(i);: Next i: Debug.Print
        Dim C() As Byte
        ReDim C(0 To 5)
        C(0) = Asc("Y"): C(1) = Asc("T"): C(2) = Asc("R"): C(3) = Asc("E"): C(4) = Asc("W"): C(5) = Asc("Q")
      Dim FldNames: FldNames = Array("1", "2", "3", "4", "5")
      Dim FldValues: FldValues = C()
    Debug.Print "Sent from Handler: " & C(0), C(1), C(2), C(3), C(4), C(5)
    
        Set RsOut = CreateResultRs("TransferByteSentence", C)
    End Sub

    See my results above.
    Last edited by Shaggy Hiker; May 12th, 2019 at 08:37 AM. Reason: Added CODE tags.

  16. #56

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

    Re: SQL server connection string

    Hi Olaf,
    I do not mind you shouting at me if this is wrong, but I need some comment please as I wish to move on to the next steps.
    Thanks
    PK

  17. #57

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

    Re: SQL server connection string

    I have tried a routine to add a new row - here is it:

    In ftestfrm:

    Code:
      
    Set RsResult = RPC.DoRPC("AddNewRow", "DailyJournal", "Date", Date, "UserNo", 1, "Entry", "May 11 entry")
    In the Handler:

    Code:
    Public Sub AddNewRow()
        Dim Params As Integer, i As Integer, Query As String, Cmd As ADODB.Command
        OpenMainConnection
        Query = "INSERT INTO " & RsIn.Fields(1).Value & " (" & RsIn.Fields(2).Value & "," & RsIn.Fields(4).Value & "," & RsIn.Fields(6).Value & ") VALUES (" & RsIn.Fields(3).Value & "," & RsIn.Fields(5).Value & "," & RsIn.Fields(6).Value & ")"
        Set Cmd = New ADODB.Command
        Cmd.CommandText = Query
        Cmd.ActiveConnection = Conn
        Conn.Command = Cmd.CommandText
        Conn.Execute (Cmd.CommandText)
        Conn.Close
    
        Set RsOut = CreateResultRs("AddNewRow", "Successful")
    End Sub
    
    
    Private Sub OpenMainConnection()
    
    On Error GoTo ConnectError
    Set Conn = New ADODB.Connection
    Conn.CursorLocation = adUseServer
    Conn.ConnectionString = strConn
    Conn.Open
    On Error GoTo 0
    Exit Sub
    
    ConnectError:
    MsgBox "Database connection failed."
    On Error GoTo 0
    Exit Sub
    
    End Sub
    Is this the method?

    PK

  18. #58
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    I have tried a routine to add a new row ...

    ...Is this the method?
    Well "in parts" - but not really...

    For example:
    - the usage of the Cmd-Object is not correct (will not prevent SQL-injection)
    - the incoming Params are not mapped to procedure-local Variables (from RsIn)
    - the OpenConn-Routine should not contain a MsgBox-call
    ..(it's code, which is run "headless" at the serverside - so unless you know about the "unattended execution"-setting in AX-Dlls, you should avoid showing any "blocking GUI-PopUps")

    What I'd prefer is, when you could concentrate on the ByteArray-stuff again
    (last shown in your post #55).

    The Handler-Routine does still contain unnecessary code
    (e.g. FldNames and FldValues Variables are defined and filled, but then not used - they are not needed at all).

    Remember, that our goal is, to transfer ByteArrays efficiently
    (to be able to later use these ByteArrays, to transport serialized ADO-Rs).

    Whats needed in the end, to completely cover any CRUD-scenario is only two Handler-Functions:
    - GetRs(SQL, BoundParams,...) ... returning a ByteArray with the serialized Rs, which was selected according to the SQL-const-string and the following Params
    - UpdateBatch(ByteArray_Of_A_SerializedRs) ... returning a Boolean

    That's it.

    Below is a module (e.g. name it: modADODB.bas), which you might want to study, ... it includes these functions.
    Code:
    Option Explicit 'convenience-functions for Cnn-Opening + Selecting ADO-Rs + Updating ADO-Rs
    
    Public TrueDisconnectedMode As Boolean 'should be set to True, when used at the serverside
    
    Public Function OpenJetCnn(DBName As String, Optional UserName$, Optional PassWord$) As ADODB.Connection
      Const JetOleDB$ = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
      Set OpenJetCnn = OpenCnn(JetOleDB & DBName, UserName, PassWord)
    End Function
    
    Public Function OpenCnn(ConnString As String, Optional UserName$, Optional PassWord$) As ADODB.Connection
      Set OpenCnn = New ADODB.Connection
          OpenCnn.Open ConnString, UserName, PassWord
          OpenCnn.CursorLocation = adUseClient
    End Function
    
    Public Function GetRs(Cnn As ADODB.Connection, ByVal SQL As String, ParamArray P()) As ADODB.Recordset
      Set GetRs = New ADODB.Recordset
      
      Dim Cmd As New ADODB.Command, i As Long
      Set Cmd.ActiveConnection = Cnn
          Cmd.CommandText = SQL
      For i = 0 To UBound(P)
        Select Case VarType(P(i))
          Case vbString: Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
          Case Else:     Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
        End Select
      Next
    
      GetRs.CursorLocation = adUseClient
      GetRs.Open Cmd, , adOpenStatic, adLockBatchOptimistic
      GetRs.Properties("Update Criteria") = adCriteriaKey 'allow only PKs, to locate Rows for Updates in the Where-Clause
      If TrueDisconnectedMode Then Set GetRs.ActiveConnection = Nothing
      Set Cmd.ActiveConnection = Nothing
    End Function
    
    Public Function UpdateBatch(Cnn As ADODB.Connection, Rs As ADODB.Recordset) As Boolean
      Set Rs.ActiveConnection = Cnn
          Rs.UpdateBatch
      If TrueDisconnectedMode Then Set Rs.ActiveConnection = Nothing
      UpdateBatch = True
    End Function
    First step should be, that you try to concentrate on the GetRs-Handler-Function first.
    (selecting a given Ado-Rs - by delegating the incoming RPC-Paramters from RsIn to the modADODB.GetRs-function)

    Please show me your first version of the clientside DoRPC-triggering - and the appropriate Handler-Method which wraps the GetRs-function.

    Olaf

  19. #59

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

    Re: SQL server connection string

    Olaf,

    I have tried and I think succeeded in sending a byte array in post 55 to DoRPC, but I do not know how to transform the following into a byte array if you wish me to send the parameters to DoRPC only in byte arrays?

    Code:
    Set RsResult = RPC.DoRPC("AddNewRow", "DailyJournal", "Date", Date, "UserNo", 1, "Entry", "May 11 entry")
    If I have to do that, I presume that I will have to have seven byte arrays, each with the code for each letter of the words I am sending or one byte array with a row and a column like in B(1,1).

    I presume that I should do this first as these are the parameters I will send from my program, or do I send it as a string array to be serialized, as the DoRPC function also accepts parameters of other variable types?

    PK

  20. #60
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: SQL server connection string

    A couple notes:

    1) I edited a few posts to add [CODE][/CODE] tags. You can do this by pressing the # button and pasting code between the resulting tags. The formatting is much nicer that way.

    2) This really should have been a second thread. It has wandered far from the original question, and now has nothing at all to do with connection strings. Since it was the thread starter that did the wandering, they can go where they will, but as a general rule, you'll get more eyes on the question if you start a new thread with a new question, and getting eyes on the question is the best way to get the best answers.
    My usual boring signature: Nothing

  21. #61

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

    Re: SQL server connection string

    Shaggy Hiker,

    Thanks for that. I only learned this week how to use the #.

    I understand your concern in your second comment, and it bothers me somewhat to keep coming back here for some un related subjects.
    This should have been a Code Project and really belongs in the Code Bank, but that started out as such on another thread and ended unresolved, but ... I am asking a lot of newbie questions here which I do not want to form part of the Code Bank.
    If Olaf is OK with this, I think we can pick out some useful parts from both threads to form a unified Coding thread for the Code bank, which can be helpful to a lot of members.

    PK

  22. #62

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

    Re: SQL server connection string

    Here is my first strike at the handler routine:

    Code:
    Public Sub CrudOperations()
        
        Dim B() As Byte, i As Integer, strConn As String, Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String
        B = StrConv(RsIn.Fields(1).Value, vbUnicode)
        strConn = "Provider=SQLOLEDB;Data Source=.\MSSQLEXP2014;Initial Catalog=*******;Integrated Security=SSPI"                                                        'for localhost
        SQL = "SELECT * FROM dailyjournal order by date"
    
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            Set rsADO = GetRs(Cnn, SQL, B())
            If rsADO.State = adStateOpen Then
                Set RsOut = CreateResultRs("CrudOperations", rsADO)
            Else
                Set RsOut = CreateResultRs("CrudOperations", "Recordset not opened")
            End If
        Else
            Set RsOut = CreateResultRs("CrudOperations", "Connection not opened")
        End If
    
    End Function

  23. #63

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

    Re: SQL server connection string

    Strike 2:
    In the form event:

    Code:
    Private Sub cmdAddRow_Click()
    
    Dim S(0 To 6) As String, B() As Byte, RsResult As ADODB.Recordset
    S(0) = "DailyJournal": S(1) = "Date": S(2) = Date: S(3) = "UserNo": S(4) = 1: S(5) = "Entry": S(6) = "May 11 entry"
    
    Set RsResult = RPC.DoRPC("AddNewRow", S(1), S(2), S(3), S(4), S(5), S(6))
    Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
      B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      'Debug.Print B
      VisualizeRs RsResult
    
    End Sub
    In the Handler:

    Code:
    Public Sub AddNewRow()
        
        Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, RsSend As New ADODB.Recordset, Updated As Boolean
        SQL = Query(1)
        Debug.Print RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6)
        
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            Updated = UpdateBatch(Cnn, RsIn)
            If Updated = True Then
                Set RsOut = CreateResultRs("AddNewRow", "Row added")
            Else
                Set RsOut = CreateResultRs("AddNewRow", "Row not added")
            End If
        Else
            Set RsOut = CreateResultRs("AddNewRow", "Connection not opened")
        End If
    
    End Sub
    In a new server module:

    Code:
    Option Explicit
    Public Const strConn = "Provider=SQLOLEDB;Data Source=.\MSSQLEXP2014;Initial Catalog=*******;Integrated Security=SSPI"                                                          'for localhost
    
    Public Function Query(QueryNo As Integer) As String
    
    Select Case QueryNo
        Case 1
            Query = "SELECT id, date, users.username, entry, private FROM dailyjournal LEFT JOIN users on users.id=dailyjournal.userno"
    
    
    End Select
    
    End Function

  24. #64

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

    Re: SQL server connection string

    Olaf,

    My understanding of the project we are busy working with, it is to prevent SQL injection.
    Where this injection can originate and how it is done, I do not know, because by serializing the data sent to the server, no hacker can understand or fiddle with that while it is on its way to the server, where it creates an object.
    How will a hacker know which objects to create once some injection gets there?
    Once arriving at the server, why can I not just use the values in RsIn as it comes in, or just convert it to local strings?
    That way I can write server side routines like the ones you have done in post #58.
    What I can do presently is send strings (or with some superhuman effort even bytes maybe) to DoRPC where it is serialized and sent to the server.
    On arriving at the server it is de-serialized and I can read the strings again and do what is needed to do the CRUD operations, for sure not as efficiently as you have done it in post #58.
    I need some direction please if you do not mind. I realize you are very busy, and to work with newbies, like me, is sometimes a waste of time.

    Thanks
    PK

  25. #65

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

    Re: SQL server connection string

    Let me try strike 3.

    In the form event I have this:

    Code:
    Private Sub cmdAddRow_Click()
    Dim S(0 To 9) As String, B() As Byte, C() As Byte, D() As Byte, E() As Byte, F() As Byte, G() As Byte, H() As Byte, K() As Byte, RsResult As ADODB.Recordset, i As Integer
    
    S(0) = "TableName": S(1) = "DailyJournal": S(2) = "Date": S(3) = Date: S(4) = "UserNo": S(5) = 1: S(6) = "Entry": S(7) = "May 11 entry"
    B = S(0): C = S(1): D = S(2): E = S(3): F = S(4): G = S(5): H = S(6): K = S(7)
    
    Set RsResult = RPC.DoRPC("AddNewRow", B, C, D, E, F, G, H, K)
    Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
      B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      'Debug.Print B
      VisualizeRs RsResult
    
    End Sub

    In the Handler I have this:

    Code:
    Public Sub AddNewRow()
        
        Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, k As Integer, RsQuery As New ADODB.Recordset, Updated As Boolean
        SQL = Query(1)
        
        k = 1
        Do While k <= RsIn.Fields.Count - 1
          If VarType(RsIn(k)) = 8209 Then 'ByteArray
            RsQuery.Fields.Append RsIn.Fields(k).Value, adBinary, -1
          Else
            RsQuery.Fields.Append RsIn.Fields(k).Value, VarType(RsIn(k))
          End If
          k = k + 2
        Loop
    
        RsQuery.Open
      
        RsQuery.AddNew
        k = 2
        Do While k <= RsIn.Fields.Count - 1
          RsQuery.Fields(k / 2 - 1).Value = RsIn(k)
          k = k + 2
        Loop
        
        Debug.Print "RsIn field values: ", RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6), RsIn(7), RsIn(8)
        Debug.Print "RsQuery field names: ", RsQuery.Fields(0).Name, RsQuery.Fields(1).Name, RsQuery.Fields(2).Name, RsQuery.Fields(3).Name
        Debug.Print "RsQuery field values: ", RsQuery.Fields(0).Value, RsQuery.Fields(1).Value, RsQuery.Fields(2).Value, RsQuery.Fields(3).Value
       
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            Updated = UpdateBatch(Cnn, RsQuery)
            If Updated = True Then
                Set RsOut = CreateResultRs("AddNewRow", "Row added")
            Else
                Set RsOut = CreateResultRs("AddNewRow", "Row not added")
            End If
        Else
            Set RsOut = CreateResultRs("AddNewRow", "Connection not opened")
        End If
    
    End Sub
    In the Hanlder I get these values:

    Code:
    RsIn field values:         TableName       DailyJournal     Date          21-May-19       UserNo        1    Entry        May 11 entry
    RsQuery field names:      TableName     Date          UserNo          Entry
    RsQuery field values:      DailyJournal     21-May-19     1  May 11 entry
    PK

  26. #66
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    I need some direction please if you do not mind.
    Believe me, I'm already trying my best - but it seems like you don't even read what I write...

    E.g. in thread-entry #58:
    - I've posted a code-module (modADODB.bas, to be included in the Serverside-Dll-Project-part of the VBGroup-Project we use for Debugging).
    - This module is capable to handle all the needed serverside DB-interaction
    - The only thing missing, is to "use this module properly" within the serverside cHandler.cls
    - by implementing exactly two (very small) Handler-Routines (in the usual manner within cHandler.cls - using a Sub - and in this Sub, RsIn and RsOut)

    Here an exact citation, what I wrote in post #58:
    ------------------------------
    Whats needed in the end, to completely cover any CRUD-scenario is only two Handler-Functions:
    - GetRs(SQL, BoundParams,...) ... returning a ByteArray with the serialized Rs, which was selected according to the SQL-const-string and the following Params
    - UpdateBatch(ByteArray_Of_A_SerializedRs) ... returning a Boolean

    I also wrote (at the end of post #58):
    First step should be, that you try to concentrate on the GetRs-Handler-Function first.
    (selecting a given Ado-Rs - by delegating the incoming RPC-Paramters from RsIn to the modADODB.GetRs-function)
    Please show me your first version of the clientside DoRPC-triggering - and the appropriate Handler-Method which wraps the GetRs-function.
    -------------------------------

    If there is any problem with the text-citations above (words or terms you don't understand), you'll have to let me know about it.
    I seriously don't know anymore, how to respond or reply - because you seem to misunderstand a whole lot (due to knowledge-gaps, I guess).
    But other than you telling me - where those gaps are (where I lost you), we will not make any further progress with this...

    So, what I'd like you to do is, to concentrate on the part I've marked blue above:
    - a functionality, which is able to select an Rs at the serverside - and transfer it back to the clientside
    - since this singular function(ality) is a Remote-Call, we will have to write two parts (to properly transport In-Params - and the Out-Result)
    1) the clientside Triggering (In-Parameter-Passing into DoRPC), where our RemoteCall starts
    2) the serverside receiving of those In-Parameters (in a Handler-Function, which will receive these In-Params the clientside sent to us, as usual in RsIn)
    3) the serverside execution within the Handler-Routine (acting on these In-Params -preparing to get an Out-Result)
    4) the serverside OutResult-Handling, so that we "get it savely and easily" back to the clientside

    What are the In-Params for GetRs?
    I wrote that above: 1. SQL, 2. an optional "BoundParameters"-List (bound to the SQL, to avoid SQL-injection)
    If you want, you can leave out Part 2. in your first try - and send only a single Param to the GetRs-Handler (1. SQL)

    HTH

    Olaf

  27. #67

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

    Re: SQL server connection string

    Believe me, I'm already trying my best - but it seems like you don't even read what I write...
    Thank you so much - I appreciate. I do read every sentence many times over, but some times I do not understand it well.

    - I've posted a code-module (modADODB.bas, to be included in the Serverside-Dll-Project-part of the VBGroup-Project we use for Debugging).
    I have included this new routine in my server side code previously already . I did not show it here, because I copied that correctly from your code. In entry#65 I am calling that module, but I see that I called the incorrect routine, as I tried adding a new row in stead of getting a new recordset.

    - This module is capable to handle all the needed serverside DB-interaction
    I can see that.

    - The only thing missing, is to "use this module properly" within the serverside cHandler.cls
    Here is how I think it should be called from the Handler:

    I need three parameters:
    1. I must tell the function which connection to use. I would make a connection string in the declaration section of your module modADODB.bas
    2. I must tell the function which SQL to use. I have (in my version of your modADODB.bas) added a function where the SQL queries can be read from, thereby obviating the necessity of sending them in the http.send code. I would call my SQL function in your module modADODB with a parameter indicating which SQL I want to use, numbering them 1 to 100 or more - I have 150 different functions, and therefore SQL queries, in my program. I would send the number of this SQL query via a parameter from the Form (as part of the other parameters) and the Handler will submit this parameters then to this function which will return the applicable SQL query without parameters.
    3. I must send the function the SQL parameters. In my Handler in entry #65, I have the parameters for field names and field values in the recordset RsQuery. I do not know which of these I must send to the function GetRs and in which order.

    If I presume that my recordset (RsQuery) contains these parameters correctly, which I think it is not, then I would call it this way:

    I would open the connection with your function OpenCnn with the correct connection string, username, password etc.
    I would then test whether its status is adstateopen.
    Then I would pass the parameters in my points 1 to 3 above to your function GetRs
    That function would return a recordset (RsOut) from the function GetRs which will be sent to the handler like in:

    Code:
    RsOut=GetRs(Cnn,SQL,RsQuery)
    This is the best I can do at this stage. Sorry.
    PK

  28. #68

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

    Re: SQL server connection string

    I have gone so far with the code as CreatingParameter in:

    Code:
      For i = 0 To UBound(P)
        Select Case VarType(P(i))
          Case vbString
            Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
          Case Else
            Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
        End Select
      Next
    I have never worked with CreateParameters and do not know what to send to the routine parameter P(). I would think it needs a field name and a field value.

    PK

  29. #69
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    I have gone so far with the code as CreatingParameter in:

    Code:
      For i = 0 To UBound(P)
        Select Case VarType(P(i))
          Case vbString
            Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
          Case Else
            Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
        End Select
      Next
    I have never worked with CreateParameters and do not know what to send to the routine parameter P(). I would think it needs a field name and a field value.
    If you want to get more familiar with the routines in modADODB.bas,
    you should use and test them in a separate (non RPC) simple Std-Exe-Project
    (which has only a Form1, the modADODB.bas + a Project-Reference to ADO).

    As for ParamArray P():
    This is kinda like an "entire List of Optional Variant-Params"
    (allowing "an unlimited amount" of them - but also passing "none of them").

    Within the Function it behaves like a Variant-Array (with zerobased indexing).
    The amount of passed Params can be determined with:
    Dim ParamCount: ParamCount = Ubound(P) +1
    And the above works also in case no optional Param was passed into P (by the caller),
    because the Ubound(P) will be -1 ... signalizing an "initialized, but yet unfilled VB-Array with no Elements".

    If you have such a little StdExe-TestProject in place:

    Then all you need in Form_Load is the following (please adapt the connection-string for your own MS-SQL-instance):
    Code:
    Private Sub Form_Load()
      Dim Cnn As ADODB.Connection
      Set Cnn = OpenCnn("Provider=SQLOleDB.1;Data Source=(local);Initial Catalog=NorthWnd;Integrated Security=SSPI;")
     
      Const SQL0 = "Select 3 - 1" 'zero Parameters (no ?-Parameter-Slot was given in the SQL)
      Const SQL1 = "Select ? - 1" 'SQL contains one Parameter-Slot
      Const SQL2 = "Select ? - ?" 'SQL contains two Parameter-Slots
      Const SQL3 = "Select ? + IIf(GetDate() Between ? And ?, 'Yes', 'No')" 'and here 3 Slots are defined
     
      Debug.Print GetRs(Cnn, SQL0).Fields(0).Value '<- no bound Param is used here (ParamArray P() remains empty)
      Debug.Print GetRs(Cnn, SQL1, 3).Fields(0).Value
      Debug.Print GetRs(Cnn, SQL2, 3, 1).Fields(0).Value
      Debug.Print GetRs(Cnn, SQL3, "In range? -> ", Now - 1, Now + 1).Fields(0).Value
    End Sub
    That shows already, how the SQL-Parameter-Binding works via GetRs(...):
    - by passing the Bound-Params in the ParamArray, matching the amount of ? in the SQL-constant
    - with the exception of SQL0, which is a "naked" (normal) SQL-string without ? Parameter-Slots (and an empty ParamArray P)
    (please count - or read - the ?-Slots from left to right - and then pass the matching "Bound-Paramarray-Value" in the same order in the GetRs-call)

    Note, that the SQL-Defs are done in Consts deliberately, to demonstrate that no change on them was taking place
    (no concatenation or replacment happened on the executed SQL, which is what prevents SQL-injection).
    Another side-effect of using bound Parameters via ADO-commands is, that you can input normal Values!
    That's quite a relief when you consider "classic" SQL-concat-fiddling with String-Values or Date-Values
    (the last query in SQL3 shows that nicely, since it gets passed 3 Params:
    - a normal VB-String-Value (no requirement to enclose this in single-qutoes)
    - followed by two normal VB-DateValues (which could be provided and passed from a DatePickerCtl.Value directly)

    Now, when you've played around enough with the above Demo-Code (in a separate project),
    we should try to get back to the topic at hand:
    Your implementation of only the GetRs-Handler-Routines for the client- and the serverside
    (and as suggested, please leave out the passing of Bound-Parameters for the moment, just pass a plain SQL-String to the Server -
    and call modADODB.GetRs(SQL) <- without any extra-params for the moment... GetRs allows that (as the SQL0-call above shows).

    HTH

    Olaf

  30. #70

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

    Re: SQL server connection string

    Olaf,

    Thanks, this is very lucid now, as I did not understand the '?' previously.
    In this exercise I have used the SQL without parameters.

    Here is my Form event:

    Code:
    Private Sub cmdGetRecordset_Click()
    Dim S(0 To 9) As String, B() As Byte, C() As Byte, RsResult As ADODB.Recordset, i As Integer
    
    S(0) = 1
    B = S(0)
    
    Set RsResult = RPC.DoRPC("GetRecordset", B)
    Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
      B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      'Debug.Print B
      VisualizeRs RsResult
    
    End Sub
    Here is my Handler:

    Code:
    Public Sub GetRecordset()
    Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, RsGetRecordset As New ADODB.Recordset, S(0 To 2) As String, B() As Byte, C() As Byte
        
        Debug.Print "GetRecordset() Handler RsIn field values: ", RsIn(0), RsIn(1)
        S(0) = RsIn(1)
        SQL = Query(S(0))
        
       
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            Set RsOut = GetRs(Cnn, SQL)
            If RsOut.State = adStateOpen Then
                Debug.Print "GetRecordset() Handler RsOut field values: ", RsOut.Fields(0).Value, RsOut.Fields(1).Value, RsOut.Fields(2).Value, RsOut.Fields(3).Value
            Else
                Set RsOut = CreateResultRs("GetRecordset", "Recordset not retrieved")
            End If
        Else
            Set RsOut = CreateResultRs("GetRecordset", "Connection not opened")
        End If
    
    Exit Sub
    
    End Sub
    Here is a function in my server side module named modQueries:

    Code:
    Public Function Query(QueryNo As String) As String
    Dim n As Integer
    
        n = CInt(QueryNo)
        Select Case QueryNo
            Case 1
                Query = "SELECT dailyjournal.id, date, userno, entry, private FROM dailyjournal"
    
    
    End Select
    
    End Function
    Here is my Debug.Print results:

    Code:
    GetRecordset() Handler RsIn field values:   GetRecordset  1
    GetRecordset() Handler RsOut field values:  2          2019-05-11       1  May 11 entry
    Received at client in ASCII: 2
    Is this OK?

  31. #71

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

    Re: SQL server connection string

    Here is the visualized result:

    Name:  VisualiseRPCcall.jpg
Views: 649
Size:  46.6 KB

  32. #72

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

    Re: SQL server connection string

    I have also done one with a parameter:

    In the form event:

    Code:
    Private Sub cmdGetRecordsetWithParameters_Click()
    
    Dim S(0 To 9) As String, B() As Byte, C() As Byte, RsResult As ADODB.Recordset, i As Integer
    
    S(0) = 2
    S(1) = "May 11"
    B = S(0)
    C = S(1)
    
    Set RsResult = RPC.DoRPC("GetRecordsetWithParams", B, C)
      B = StrConv(RsResult(0).Value, vbUnicode) 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      VisualizeRs RsResult
    
    End Sub
    In my modeQuery for index 2:

    Code:
    Public Function Query(QueryNo As String) As String
    Dim n As Integer
    
        n = CInt(QueryNo)
        Select Case QueryNo
            Case 1
                Query = "SELECT dailyjournal.id, date, userno, entry, private FROM dailyjournal"
    
            Case 2
                Query = "SELECT dailyjournal.id, date, userno, entry, private FROM dailyjournal WHERE entry LIKE '%'+?+'%'"
    
    End Select
    
    End Function
    Visualization:

    Name:  VisualiseRPCcall param.jpg
Views: 632
Size:  43.2 KB

  33. #73

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

    Re: SQL server connection string

    Olaf,
    I wish to raise some points if I may:

    1. I have used your GetRs routine in modADODB with and without parameters with some success in posts #70 to #72. However, your silence is deafening, so I presume I made a lot of mistakes there. If you can point me to the errors I will appreciate.
    2. When doing the exercise in 1. above, your code in the form event: B = RsResult(0).Value gives me an variant type error. I presume it is because I sent it data in the wrong format or type, because when I change that to B = StrConv(RsResult(0).Value, vbUnicode) it works perfectly. I do not know how to solve this error.
    3. In this whole process I never know when I must use bytes and when I may use strings. If you can please revisit my post #64 to point these out to me, I will appreciate. I do not know quite where SQL injection can and can not take place in this process.
    4. In the same modADODB you have a batchupdate function which takes a recordset Rs as a parameter. Which parameters or fields should I pass to this function in the recordset?

    Thanks
    PK

  34. #74

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

    Re: SQL server connection string

    I am publishing this variation on the song High Noon with the greatest respect:

    Do not forsake me, oh, my mentor
    On this, my ignoramus day
    Do not forsake me, oh, my mentor
    help, help along

    I do not know what fate awaits me
    I only know I must be brave
    And I must face a man who knows all
    Or lie a coward, a craven coward
    Or lie a coward in my grave

    Oh, to be torn 'tweenst ignorance and duty
    Supposin' I lose my programs's beauty
    Look at that data not moving along
    Nearin' high noon

    He made a promise whilst in AdStateOpen
    Vowed he would help me overcome my problem
    I'm not afraid of failure, but, oh
    What shall I do if you forsake me?

    PK
    PS - Moderator, please do not ban me!!! I love this forum.

  35. #75
    Fanatic Member
    Join Date
    Feb 2019
    Posts
    706

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    He made a promise whilst in AdStateOpen.
    As far as I know Olaf didn't make a promise, including in a previous thread where he tried to help you. This forum is made of a community volunteering their time, to help others. One doesn't have the right to demand they put everything away, and work on their issue. Others could step in and out depending what time they have or willing to spend. There should be no expectations that the same person comes back and volunteer their time over and over. Would you stop everything that you do and help me with my problem?

    I sometimes just post API functions or keywords to search and run. If a thread subject doesn't reflect the contents, then someone searching for a solution is unlikely to view such a thread, so any answers given to such a thread just benefit one specific person. The same goes for threads that say "I need help" in the subject, etc., I don't respond to these. Only one benefits rather than the community. I know derailing a thread happens from time to time, but usually briefly.

    Hope this helps.

  36. #76

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

    Re: SQL server connection string

    qvb6,
    I hold you and Olaf and many others here in veneration.
    However, I think that whilst we are mostly nerds we can enjoy some diversion.
    Rest assured that I am here to support the forum.
    PK

  37. #77

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

    Re: SQL server connection string

    I used this code for inserting a new row:

    In the form event:

    Code:
    Private Sub cmdAddRow_Click()
    Dim B() As Byte, B0() As Byte, B1() As Byte, B2() As Byte, B3() As Byte, B4() As Byte, B5() As Byte, B6() As Byte, B7() As Byte
    Dim S(0 To 6) As String, RsResult As ADODB.Recordset, i As Integer, ByteLength As Integer
    
    S(0) = "DailyJournal": S(1) = "MyDate": S(2) = Date: S(3) = "UserNo": S(4) = 1: S(5) = "Entry": S(6) = "May 11 entry"
    
    For i = 0 To 6
        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)
    Next i
    
    Set RsResult = RPC.DoRPC("AddNewRow", B0, B1, B2, B3, B4, B5, B6)
    B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
    End Sub
    Thjs works fine.

    I have this in the Handler:

    Code:
    Public Sub AddNewRow()
    Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, Updated As Boolean, TableName As String, FieldNames(0 To 2) As String, Values(0 To 2) As Variant
        
        Debug.Print "RsIn field values: ", RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6), RsIn(7)
        
        TableName = RsIn(1)
        
        FieldNames(0) = RsIn(2)
        FieldNames(1) = RsIn(4)
        FieldNames(2) = RsIn(6)
        
        Values(0) = RsIn(3)
        Values(1) = RsIn(5)
        Values(2) = RsIn(7)
        
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            SQL = "SELECT * FROM " & TableName & " WHERE id=0"
            Set rsADO = GetRs(Cnn, SQL)
            If rsADO.State = adStateOpen Then
                rsADO.AddNew
                For i = 0 To rsADO.Fields.Count - 1
                    If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = Values(0)
                    If rsADO.Fields(i).Name = CStr(FieldNames(1)) Then rsADO.Fields(i).Value = Values(1)
                    If rsADO.Fields(i).Name = CStr(FieldNames(2)) Then rsADO.Fields(i).Value = Values(2)
                Next i
                
                Updated = UpdateBatch(Cnn, rsADO)
                Set RsOut = CreateResultRs("AddNewRow", "New row added")
                
            Else
                Set RsOut = CreateResultRs("AddNewRow", "New row not added")
            End If
        Else
            Set RsOut = CreateResultRs("AddNewRow", "Connection not opened")
        End If
    
    Exit Sub
    This works fine if I enter real values in the code lines like this"

    Code:
                    If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = "01-06-2019"
    But it gives me an error when I do the code line like this:

    Code:
    If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = Values(0)
    And I know I have the correct values in FieldNames() and in Values().

    PK
    Last edited by Peekay; Jun 2nd, 2019 at 10:48 AM.

  38. #78

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

    Re: SQL server connection string

    Here is my DeleteRow form event:

    Code:
    Private Sub cmdDeleteRow_Click()
    Dim B() As Byte, C() As Byte, S(0 To 1) As String, RsResult As ADODB.Recordset
    
    S(0) = "DailyJournal": S(1) = "5"
    B = S(0): C = S(1)
    
    Set RsResult = RPC.DoRPC("DeleteRow", B, C)
    
    B = StrConv(RsResult(0).Value, vbUnicode) 'get the resulting ByteArray-Blob from the ResultRs-Field 0
    
    End Sub
    Here is my Handler:

    Code:
    Public Sub DeleteRow()
    Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, Updated As Boolean, TableName As String, IDvalue As Variant
        
        Debug.Print "RsIn field values: ", RsIn(1), RsIn(2)
        
        TableName = RsIn(1)
        IDvalue = RsIn(2)
        
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            SQL = "SELECT * FROM " & TableName & " WHERE id=" & IDvalue
            Set rsADO = GetRs(Cnn, SQL)
            If rsADO.State = adStateOpen Then
                rsADO.Delete
                Updated = UpdateBatch(Cnn, rsADO)
                rsADO.Close
                Set RsOut = CreateResultRs("DeleteRow", "Row deleted")
                
            Else
                Set RsOut = CreateResultRs("DeleteRow", "Row not deleted")
            End If
        Else
            Set RsOut = CreateResultRs("DeleteRow", "Connection not opened")
        End If
    
    Exit Sub
    And it works.

    PK

  39. #79

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

    Re: SQL server connection string

    Here is my UpdateRow from event:

    Code:
    Private Sub cmdUpdateRow_Click()
    Dim B() As Byte, B0() As Byte, B1() As Byte, B2() As Byte, B3() As Byte, B4() As Byte, B5() As Byte, B6() As Byte, B7() As Byte
    Dim S(0 To 7) As String, RsResult As ADODB.Recordset, i As Integer, ByteLength As Integer
    
    S(0) = "DailyJournal": S(1) = 6: S(2) = "MyDate": S(3) = Date: S(4) = "UserNo": S(5) = 2: S(6) = "Entry": S(7) = "June 1 entry"
    
    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
    
    Set RsResult = RPC.DoRPC("UpdateRow", B0, B1, B2, B3, B4, B5, B6, B7)
    B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
    
    End Sub
    Here is my Handler:

    Code:
    Public Sub UpdateRow()
    Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, Updated As Boolean, TableName As String, MyID As Variant, FieldNames(0 To 2) As String, Values(0 To 2) As Variant
        
        Debug.Print "RsIn field values: ", RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6), RsIn(7)
        
        TableName = RsIn(1)
        MyID = RsIn(2)
        
        FieldNames(0) = RsIn(3)
        FieldNames(1) = RsIn(5)
        FieldNames(2) = RsIn(7)
        
        Values(0) = RsIn(4)
        Values(1) = RsIn(6)
        Values(2) = RsIn(8)
        
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            SQL = "SELECT * FROM " & TableName & " WHERE id=" & MyID
            Set rsADO = GetRs(Cnn, SQL)
            If rsADO.State = adStateOpen Then
                For i = 0 To rsADO.Fields.Count - 1
                    If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = Values(0)
                    If rsADO.Fields(i).Name = CStr(FieldNames(1)) Then rsADO.Fields(i).Value = Values(1)
                    If rsADO.Fields(i).Name = CStr(FieldNames(2)) Then rsADO.Fields(i).Value = Values(2)
                Next i
                
                Updated = UpdateBatch(Cnn, rsADO)
                Set RsOut = CreateResultRs("UpdateRow", "Row updated")
                
            Else
                Set RsOut = CreateResultRs("UpdateRow", "Row not added")
            End If
        Else
            Set RsOut = CreateResultRs("UpdateRow", "Connection not opened")
        End If
    
    Exit Sub
       
    End Sub
    As for InsertRow, the fields update gives me an error.

  40. #80
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Peekay, the "problem I have" in this discussion, is that you don't read properly - you do not listen -
    or if you do - then you apparently don't understand even the simplest things with regards to programming.

    Seriously - you've not even mastered the "transfer of a Byte-Array-Param" via RPC yet...
    (which was a necessity, that should have been mastered about 40 postings earlier).

    I've now decided, to give up on my attempt "to make you really understand" (what's going on) -
    and instead stick to simple "code-handouts"...
    (To me, it feels like handing an airplane over to somebody who so far has not demonstrated
    any decent piloting-skills - and what's even worse is, that "he's convinced he does").

    So, here is - what I've expected you to come up with, regarding the "transfer bytearray task":

    In the clientside Form-Code:
    Code:
    Private Sub Command1_Click()
      ReDim B(0 To 2) As Byte: B(0) = 0: B(1) = 1: B(2) = 2
       
      Dim RsResult As ADODB.Recordset
      Set RsResult = RPC.DoRPC("TransferByteArrays", B)
      
      If NoErrorIn(RsResult) Then B = RsResult(0).Value: Debug.Print B(0), B(1), B(2)
    End Sub
    
    'just add this additional helper into the Form, to be able to test for returned RsResult-Errors in a single line in clientside Caller-Code
    Private 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
    In the serverside cHandler.cls:
    Code:
    Public Sub TransferByteArrays()
      'read the ByteArray (as a Blob-Value) from the incoming Rs' first Field-Slot
      Dim B() As Byte: B = RsIn(1).Value
    
      Dim i As Long 'now increment all the values in B() - just to show that we've touched it at the serverside
      For i = 0 To UBound(B)
        B(i) = B(i) + 1
      Next
    
      'return the (changed) B-Array in RsOut
      Set RsOut = CreateResultRs("OutBytesBlob", B)
    End Sub
    So the above is, what I tried to explain to you regarding Byte-Array passing (in about 30 postings).

    Perhaps I should write it up one last time, regarding the "flow and the passing" of a single ByteArray-Param (according to the code above):
    - It starts at the clientside in Command1_Click, where a single Param B (in Blue) was passed (behind the serverside ProcedureNameString)
    - next stop is the serverside Handler-Code, where the clientside InParam B is read from the RsIn(1)-transport-slot
    - next is the processing on the incoming B-bytes in the little For-Loop
    - last ilne in the serverside Handler-Routine is "passing B back to the remote-caller" (as a single param again, behind the "ParamDescriptionString")
    - finally the handling of the result in Command1_Click again, where after return from DoRPC the single ByteArray-Value sits in RsResult(0).Value

    That's it again, how a complete roundtrip works with the given Helper-Modules and -Functions (here for the case of ByteArray-Param passing).

    And of course you can mix String-Param-passing and ByteArray-Param passing - e.g. when you want to write functionality,
    which is able to transfer e.g. an ImageFiles contents to the clientside (as a ByteArray - but using a String-Param - the FilePath as an Input):

    Clientside Form-Code for ServerFile-Content-Downloads:
    Code:
    Private Sub Command3_Click()
      Dim FileName As String: FileName = "MyServersideImage.jpg"
      Dim RsResult As ADODB.Recordset
      Set RsResult = RPC.DoRPC("DownloadFileContent", FileName)
      
      If NoErrorIn(RsResult) Then
        Dim B() As Byte: B() = RsResult(0).Value
        WriteByteArrayContentToFile B, App.Path & "\MyClientsideImage.jpg"
      End If
    End Sub
    Serverside Handler-Code for the above:
    Code:
    Public Sub DownloadFileContent()
      'load the single InParam (which was passed as a String) into a local Variable
      Dim FileName As String: FileName = RsIn(1).Value
      
      'do internal processing with the passed param (here, loading a Files content as a ByteArray)
      Dim B() As Byte: B = ReadByteArrayContentFromFile(App.Path & "\" & FileName)
      
      'pass the read serverside ByteContent of the File back to the caller
      Set RsOut = CreateResultRs("OutBytesBlob", B)
    End Sub
    Please at least try to really understand what I've just posted (perhaps implementing the FileTransfer-Function as well, because it's something useful to have),
    before I'm posting my code-handouts which can cover CRUD-scenarios (which as said, can be covered completely with only two serverside routines: GetRs and UpdateBatch).

    Olaf

Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

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



Click Here to Expand Forum to Full Width