Page 1 of 3 123 LastLast
Results 1 to 40 of 86

Thread: SQL server connection string

  1. #1

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

    SQL server connection string

    After studying many of these strings I am more confused than ever.
    dilettante has an excellent contribution on this thread, which of course is way above my head:

    http://www.vbforums.com/showthread.p...ing+sql+server

    I wish to connect with ADO and VB6 to a remote server on the web where I have set up a SQL server database and I have the names of the server, database, user and password.

    Can someone please assist me to compile the correct connection string.
    Thanks
    PK

  2. #2
    Fanatic Member
    Join Date
    Feb 2019
    Posts
    706

    Re: SQL server connection string

    See "Connect via an IP address" at the following link, but I am not sure if it's secure:

    https://www.connectionstrings.com/mi...rver-sqloledb/

  3. #3
    Fanatic Member
    Join Date
    Feb 2019
    Posts
    706

    Re: SQL server connection string

    Official docs online:

    Microsoft OLE DB Driver for SQL Server:
    https://docs.microsoft.com/en-us/sql...ts-allversions

    Connection string parameters:

    https://docs.microsoft.com/en-us/sql...ts-allversions

    It looks like you need "Trusted_Connection" to establish a secure connection.

  4. #4

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

    Re: SQL server connection string

    qvb6,
    Thank you so much, I will check.
    PK

  5. #5

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

    Re: SQL server connection string

    This has been resolved

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: SQL server connection string

    What did you end up using? It might help others who stumble in here later.

    Just XXX out anything you need to conceal like DNS names/IP addresses, users/pws. DNS names should almost always be preferred over IP addresses. Names are meant to be more permanent.

  7. #7

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

    Re: SQL server connection string

    This connection string works on the webserver I use:

    "Provider=SQLOLEDB;Server=Servername;Initial Catalog=Databasename;Uid=Username;Pwd=Password"

  8. #8
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    460

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    This connection string works on the webserver I use:

    "Provider=SQLOLEDB;Server=Servername;Initial Catalog=Databasename;Uid=Username;Pwd=Password"
    sql server is a prime target for hackers. They use automatic scans to try and find them and then try and brute force the 'sa' password.

    Your server is likely already under attack. Consider the following:

    1. Don't use the default port 1433/1434. You can set the port number to some other number like 56645 in the sql configuration and then modify the connection string, eg, Server=Servername,56645

    2. If you know the IP address(es) of the clients that will be connecting, use a firewall to limit connections from specific "Source IP" addresses.

  9. #9

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

    Re: SQL server connection string

    Thanks DllHell, for a valuable contribution. I will implement your first recommendation.
    For the second, shall I then use the local network IP address and make them fixed?
    PK

  10. #10

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

    Re: SQL server connection string

    Thanks DllHell, for a valuable contribution. I will implement your first recommendation.
    For the second, shall I then use the local network IP address and make them fixed?
    PK

  11. #11
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    460

    Re: SQL server connection string

    you will want to use you public ip address. google "find my ip address" and google will show it to you or point you to a link to see it. Note that this may change from time to time if you are on a residential account.

  12. #12
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: SQL server connection string

    Hiding the port off 1433 is not a way to secure the system... Most hackers will port scan address and still find the sql server even on another port. I would do a couple of things to secure.... 1 put a server in the dmz zone to accept access. Send to the SQL Server from that address only for any connection that is coming from outside your local network. Disable the sa user and create your own with a secure password. And yes I know that some applications require the sa user (I know some that do and always vote against them in my environments). If you need sa user then once again ensure that it is using a complicated password at the least
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  13. #13

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

    Re: SQL server connection string

    Hi Gary,
    I am a newbie on SQL server. Can you please elaborate or send me some links to study?
    Thanks
    PK

  14. #14
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: SQL server connection string

    For what part... Changing the port? Removing sa? password complexity? The part about DMZ servers is networking issue. The port change can bring up other issues (needing the SQL Server Browser agent running which opens another security hole in SQL Server). These are all things I learned reading the SQL Server Docs... true I have been doing this for many years (over 20 years as a SQL Server DBA/professional) so I would point you to the documentation and send a lot of time reading.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  15. #15
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQL server connection string

    Putting a db server directly connected to the internet is generally a very bad idea. Whatever issue you are facing with conn-strings now will pale when (not if) the machine gets compromised and start spreading malware/spam. Securing a box behind a firewall (in so called DMZ) partially solves the problem but still the actual db server has to be exposed on a publicly accessible IP+port to be scanned and probed by potential intruders.

    What we usually do is to setup a connection server to establish some sort of tunneling to the db server -- any kind of VPN or as simple an ssh server. Using ssh port forwarding (to local machine only for further load-balancing) and self-signed client certificates (called private keys in ssh land where a single user can have *several* key files) is almost unbreakable security-wise and cheap to implement, configure and maintain. Latest Win 10 includes ssh.exe client too but even shelling plink.exe from VB6 apps has been a great success for setting up and securing various mobile clients w/ access to their db servers.

    cheers,
    </wqw>

  16. #16

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

    Re: SQL server connection string

    You two gentlemen obviously know the ropes, but you are hanging the fruit too high for me.
    I wish to implement a SQL server instance on my local network server.
    Why do you not write a short tutorial about the steps one must take to implement that with security?
    PK

  17. #17
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: SQL server connection string

    Not only do those two 'gentlemen' (we ASSUME that! :-)) know their business, but I'd wager they didn't learn all that from 'short tutorials'. There are computer security courses offered world-wide...if you really want to make sure (as much as humanly possible) that your server is safe from predators, "I" would suggest taking some of those courses FIRST, before you DO have your server(s) taken over by Ivan or Cho Min, or whoever.

  18. #18
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: SQL server connection string

    Is the local network a Domain? If the answer to that is yes then I would do the following:
    • Create a domain user name SQLEngine
    • Create a domain user named SQLAgent
    • Create a domain user name SQLReportEngine
    • Create a domain user SQLIntergration

    When you install the SQL Server install only the parts you will use that means do not just click next on each screen. Will you be using SSRS reporting services? If not do not install them. Will you be using Analysis serveries (I would be not) if not do not install that.
    When you get to the part that ask who will be running the services if on a domain select the user you created above for each part that is installed. If not in a domain select the local service runner. DO NOT!!!! select any user that has local or domain admin rights on the server!!!!!! Do not set the SQL Browser service to run.
    When asked about adding admins ensure you select your self. If you are not in a domain then you must select mix auth mode and when asked make sure that the sa user has a complex password.

    These are the first steps
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  19. #19

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

    Re: SQL server connection string

    Gary,

    This was of enormous help.
    Thank you so much for sharing your expertise. I appreciate.

    PK

  20. #20
    Fanatic Member
    Join Date
    Feb 2019
    Posts
    706

    Re: SQL server connection string

    The approach I would take is using ASP with VBScript(Which is similar to VB6, but not exactly), which talks to the DB, and sends me the result. You can use the WebBrowser Control or WinInet to talk to the ASP page. Another approach is to make an ActiveX DLL in VB6, then use CreateObject("Project1.Class1") to talk to it from VBScript, but I don't recommend this approach as it requires installing a DLL on the remote web server, and you have to learn about binary compatibility issues with ActiveX/COM projects, but it's the option I take if I have a lot of VB6 code that otherwise needs translating to VBScript. VBScript only supports Variant data types, so "Dim i As Long" would create an error, you would have to use "Dim i" alone. Also, VBScript can't call API functions as it doesn't support Declare statement, but you can use CreateObject to use COM DLL's.

    If you have MSDN Library - October 2001, look for VBScript in the Index, then "Language Reference". Here is a link to the online version. Also check out "Visual Basic for Applications Features Not In VBScript" for differences between VBScript and VB6.

    For a quick start with ASP, check out the second example at this link.

    Here is a link to ASP FAQ, but it's not easy to navigate:

    http://www.4guysfromrolla.com/aspfaqs/

    Look under the Database links.

  21. #21

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

    Re: SQL server connection string

    qvb6,

    Thanks, this is a very enlightening contribution which I appreciate.
    I know the route you suggest is the best route with much better security as the one I am embarking on now, and I wish to follow it, although I cannot get it online as quickly as my intended way. However, I will develop that as a priority to implement.
    From the start I was sure of doing it that way and I was advised by Olaf to use RPC with which he helped me half way. He then promised to make a demo program to do CRUD with RPC using Create Object and publish it here, but he reneged on that promise and left me high and dry with a half developed program, and that is the reason I had to go for my present way of doing it.
    I will follow your advice and see how far I get and maybe you can assist me again later.

    Thank you so much
    PK

  22. #22

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

    Re: SQL server connection string

    qvb6 late note.
    I know ASP and ASP.NET and ADO.NET quite well
    OK

  23. #23
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    From the start I was sure of doing it that way and I was advised by Olaf to use RPC with which he helped me half way.
    As for complex RPC-calls (supporting any VB-SimpleTypes + VB-ByteArrayys in the Input- and Result-Params) -
    the posted and described approach here: http://www.vbforums.com/showthread.p...g-of-http-RPCs
    was "the full way" (including support for "break-point-debugging in serverside-code" in the VB6-IDE).


    Quote Originally Posted by Peekay View Post
    He then promised to make a demo program to do CRUD with RPC using Create Object and publish it here,
    but he reneged on that promise and left me high and dry...
    Sorry for not coming up with such a demo so far - but with "just a little bit of effort on your own",
    you could have expanded (building) on what I've posted in #7 quite easily yourself.

    As a hint:
    CRUD-like DB-Communication using the IIS-based RPC-approach in the above link should ideally happen via:
    - disconnected ADO-Rs.

    And those disconnected ADO-Rs will have to be serialized (into ByteArrays), to be able to transport them in the outline approach.

    So, the first step for you should be, to expand on the given Demo in post #7 of the RPC-Thread in the CodeBank,
    which does only the following: "transfer Byte-Arrays" (still no CRUD-Ops yet).

    Homework for you:
    1) Please add a Command1-Button to fTest.frm with the following Click-Event-Code:
    Code:
    Private Sub Command1_Click()
      Dim B() As Byte, RsResult As ADODB.Recordset
      ReDim B(0 To 2): B(0) = 0: B(1) = 1: B(2) = 2 'allocate memory - and fill 3 ByteValues [0,1,2] into the Array
      
      Set RsResult = RPC.DoRPC("TransferByteArrays", B)
      B = RsResult(0) 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      Debug.Print B(0), B(1), B(2)
    End Sub
    2) Now write your own Handler-Sub (adding it to the serverside cHandler.cls), to ensure the following output from the blue-colorized line above:
    Code:
     1      2     3
    If you can do that entirely on your own (hint - the routine in cHandler.cls will contain only 5-8 lines of code),
    I'd be willing to explain things further from there.

    Olaf

  24. #24

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

    Re: SQL server connection string

    OK many thanks Olaf.

  25. #25

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

    Re: SQL server connection string

    Hi Olaf, here is my first try:

    Public Sub "TransferByteArrays"()
    Dim FldNames:FldNames=Array("One","Two","Three")
    Dim FldValues(0 to 2):FldValues(0)=Array(Rsin(0)+1:FldValues(1)=Array(Rsin(1)+1:FldValues(2)=Array(Rsin(2)+1
    Set RsOut=CreateResultRs("TransferByteArrays",RsIn(0)
    End Sub

  26. #26

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

    Re: SQL server connection string

    Hi Olaf, here is my first try:

    Public Sub "TransferByteArrays"()
    Dim FldNames:FldNames=Array("One","Two","Three")
    Dim FldValues(0 to 2):FldValues(0)=Array(Rsin(0)+1:FldValues(1)=Array(Rsin(1)+1:FldValues(2)=Array(Rsin(2)+1
    Set RsOut=CreateResultRs("TransferByteArrays",RsIn(0)
    End Sub

  27. #27

    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 could not test it on the program because there is an error in this line:

    B = RsResult(0)

    The error is type mismatch.

    PK

  28. #28
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    Hi Olaf, here is my first try:

    Public Sub "TransferByteArrays"()
    Dim FldNames:FldNames=Array("One","Two","Three")
    Dim FldValues(0 to 2):FldValues(0)=Array(Rsin(0)+1:FldValues(1)=Array(Rsin(1)+1:FldValues(2)=Array(Rsin(2)+1
    Set RsOut=CreateResultRs("TransferByteArrays",RsIn(0)
    End Sub
    Well - here is, what my VB6-IDE-Editor-Window looks like, when I put your above code into the cHandler.cls Module:



    The colorizing in this case was done by the VB6-IDE (not by me) -
    so I guess you should try to reduce the "amount of red" first
    (so that the VB6-compiler will accept the routine as "not obviously faulty" - letting it pass).

    Also note, that inside the routine there's "quite a lot of things which are misunderstood".

    Perhaps I should explain the mechanism of the choosen RPC-transport again.

    All the Handler-Sub-Routines have no Parameters... and - "being Subs" - also no Return-Value -> instead these are handled via:
    - RsIn (a transport Container-Object which contains multiple "In-Parameters" in its Rs.Fields)
    - RsOut (filled by you - but also just a Container-Object, which takes up one or multiple Results in its Rs-Fields)

    Both Rs will contain only a single Record (with multiple Field-Values though, as described above).
    Please think about those two "transport-Rs" not in a "DB-sense" - but as "Collection-like buffers".

    RsIn contains the Parameters in the order you have passed them at the Clientside (in your Form) into the RPC-call (the first at index-pos 1).

    Hint for you next attempts.
    - the "In"-ByteArray was passed as only a single Parameter into the transport-Rs
    - the RsOut-transport-container should also be filled with only a single "Out_Result" (a ByteArray)
    Please look at (and treat) a ByteArray as "just another kind of String"

    HTH

    Olaf

  29. #29

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

    Re: SQL server connection string

    Error by myself
    Last edited by Peekay; Apr 14th, 2019 at 10:28 AM. Reason: Error by myself

  30. #30

    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 have a much better understanding now.
    I follow the recordset field values as it goes through its paces.
    However, I do not get the three fields as 0, 1, and 2 in the Handler because the fTest form only sends an "A" in the place of B entered in this line of the fTest click event. Do I miss something?

    Set RsResult = RPC.DoRPC("TransferByteArrays", B)

    Thanks
    PK

  31. #31
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    Hi Olaf, I have a much better understanding now.
    I follow the recordset field values as it goes through its paces.
    However, I do not get the three fields as 0, 1, and 2 in the Handler because the fTest form only sends an "A" in the place of B entered in this line of the fTest click event. Do I miss something?

    Set RsResult = RPC.DoRPC("TransferByteArrays", B)
    Please leave the Command1_Click Event-Routine in fTest exactly as I've posted it previously:

    Code:
    Private Sub Command1_Click()
      Dim B() As Byte, RsResult As ADODB.Recordset
      ReDim B(0 To 2): B(0) = 0: B(1) = 1: B(2) = 2 'allocate memory - and fill 3 ByteValues [0,1,2] into the Array
      
      Set RsResult = RPC.DoRPC("TransferByteArrays", B)
      B = RsResult(0) 'get the resulting ByteArray-Blob from the ResultRs-Field 0
      Debug.Print B(0), B(1), B(2)
    End Sub
    The B in the code-snippet above, when you pass it as the second Parameter into DoRPC - is a Variable (of type "ByteArray") -
    with the "length of 3 Bytes" and the content-bytes [0, 1, 2].

    This single ByteArray-Param should arrive at the serverside (in cHandler.cls) in a single Field of the RsIn-Transport-Container (at Index-Pos 1).

    Don't confuse the Content of a Variable with a Variables Name...

    Olaf

  32. #32

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

    Re: SQL server connection string

    Name:  Olaf 01.jpg
Views: 1430
Size:  13.0 KB

    If that was the case then I would have been able to get 123 in field 1, because the data is sent to the Handelr like this:

    CallByName Handler, Handler.RsIn!ProcName.Value, VbMethod

    If I check the Value of this recordset I get "TransferByteQArrays" in field 0 and A in field(1).
    See attachment.

    PK

  33. #33
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    Name:  Olaf 01.jpg
Views: 1430
Size:  13.0 KB

    If I check the Value of this recordset I get "TransferByteQArrays" in field 0 and A in field(1).
    Please try:
    Code:
      Debug.Print TypeName(Handler.RsIn.Fields(0).Value), TypeName(Handler.RsIn.Fields(1).Value)
    instead... (you'll see that the RsIn.Field(1).Value will definitely transport and contain a ByteArray)

    Though - I see what you mean now with your A - Printout ... (it's an effect, related to auto-conversion from a VB-ByteArray-Vector to BStr-WChars)

    You can reproduce that easily (in an independent, virginal Form-Project) this way:
    Code:
    Private Sub Form_Load()
      Dim B() As Byte
      ReDim B(0 To 2): B(0) = 0: B(1) = 1: B(2) = 2
      Debug.Print B, AscW(B), ChrW$(256) '<- prints out:    A     256     A
    End Sub
    So, the 'A' you see, is not "the real ASCII-A" (which would have an AscW-Value of 65).

    When I talked about "looking at ByteArrays as Strings", I didn't mean "looking at the PrintOut" -
    I meant: "treat them argument-wise like a VB-String-Variable"...
    With regards to the transport-Rs, where you'd have no problem in understanding, why a String-Variables Content can be placed in "just a single Rs-Field-Slot".

    Please treat ByteArray-Variables (in such RPC-calls) the same way as you'd treat a String-Variable...
    You will put their content into a single Field-Slot in your clientside DoRPC-call (which results in a single occupied slot in the RsIn-transport-Rs).
    Readout in the Handler-Sub then:
    Code:
    Dim B() As Byte
        B = RsIn.Fields(1).Value
    Well -and in the opposite direction (when sending results from the serverside Handler-Sub), you can obviously do the same
    (when you fill RsOut with another - or a changed - ByteArray-Variables content, but I'll leave that to you).

    Olaf

  34. #34

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

    Re: SQL server connection string

    Double entry sorry
    Last edited by Peekay; Apr 16th, 2019 at 12:20 PM.

  35. #35

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

    Re: SQL server connection string

    Please disregard. How do I delete posts?
    Last edited by Peekay; Apr 16th, 2019 at 09:44 PM.

  36. #36
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    I can see the types of Handler.RsIn.Fields(0).Value and RsIn.Fields(1).Value and I can see the value of B as an array is 0, 1, 2
    but I cannot extract those values of 0, 1 and 2 from RsIn as B does not exist in the server side.
    Perhaps we should look at the serverside CodeModules (the two Classes cASP and cHandler):
    - cASP.cls contains generic Delegation-Code - and is created and used by the IIS-instance (you normally don't add any code into it)
    - cHandler.cls is, where you can place all your own - serverside, userdefined Routines.

    Putting your own Sub:
    Code:
    Public Sub "TransferByteArrays"()
    into the cHandler.cls Module, was already the right idea (the only thing to fix, would be the Double-Quotes around the Name of that routine).

    So, an empty Routine (placed at the end of cHandler.cls) like this:
    Code:
    Public Sub TransferByteArrays()
    ' 1) first read-out RsIn-Fields into procedure-locale Variables (for more convenience later)
    
    '... 2) "do stuff" with these procedure-locales Variables
    
    ' 3) put the result of the above "stuff done" into RsOut
    End Sub

    Quote Originally Posted by Peekay View Post
    In the Handler I still get B = RsIn.Fields(1).Value="A"
    The two lines of code I've posted at the end of my previous message (the ByteArray-ReadOut from RsIn),
    will have to go into the "Comment 1)-Section" of the above routine obviously...

    Olaf

  37. #37

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

    Re: SQL server connection string

    Hi Olaf,

    Here is strike 2:

    Public Sub TransferByteArrays() Dim i As Integer
    Dim B() As Byte
    B = RsIn.Fields(1).Value

    Dim FldNames(0 To 2)
    FldNames(0) = Array("One")
    FldNames(1) = Array("Two")
    FldNames(2) = Array("Three")

    Dim FldValues(0 To 2)
    For i = 0 To 2
    FldValues(i) = B(i) + 1
    Next i

    Set RsOut = CreateResultRs(FldNames, FldValues)

    End Sub

  38. #38
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    Hi Olaf,

    Here is strike 2:

    Public Sub TransferByteArrays() Dim i As Integer
    Dim B() As Byte
    B = RsIn.Fields(1).Value

    Dim FldNames(0 To 2)
    FldNames(0) = Array("One")
    FldNames(1) = Array("Two")
    FldNames(2) = Array("Three")

    Dim FldValues(0 To 2)
    For i = 0 To 2
    FldValues(i) = B(i) + 1
    Next i

    Set RsOut = CreateResultRs(FldNames, FldValues)

    End Sub
    Now you're trying to return multiple results from the routine (in multiple RsOut-Fields).

    As said, when you come to "Stage 3" in the routine (filling RsOut) - please treat B like a String-Variable -
    and pass it back in a single RsOut-Field, as a single Result.

    From the other Demo-Routines (in cHandler.cls) - the ReflectString()-Sub routine comes near
    (if you want to copy from something existing - with regards to "how to fill RsOut with a single result").

    In ReflectString():
    - you have a single incoming Param in RsIn (the String you passed in at the clientside) as well
    - then you change that incoming Strings Content (via a Helper-Function -> StrReverse)
    - then you pass the changed String along as a single result in RsOut

    Please do the same with your incoming ByteArray.
    The only difference is, that "changing the ByteArrays content" (adding 1 to each Byte, instead of reversing the Bytes) has to be done explicitely

    Olaf
    Last edited by Schmidt; Apr 17th, 2019 at 03:13 AM.

  39. #39

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

    Re: SQL server connection string

    Strike 3 - are the bases loaded?

    Public Sub TransferByteArrays()
    Dim i As Integer
    Dim B() As Byte
    B = RsIn.Fields(1).Value

    Dim FldNames: FldNames = Array("One", "Two", "Three")
    Dim FldValues: FldValues = Array(B(0) + 1, B(1) + 1, B(2) + 1)

    Set RsOut = CreateResultRs(FldNames, FldValues(0) & FldValues(1) & FldValues(2))

    End Sub

    PK

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

    Re: SQL server connection string

    Quote Originally Posted by Peekay View Post
    Strike 3 - are the bases loaded?

    Public Sub TransferByteArrays()
    Dim i As Integer
    Dim B() As Byte
    B = RsIn.Fields(1).Value

    Dim FldNames: FldNames = Array("One", "Two", "Three")
    Dim FldValues: FldValues = Array(B(0) + 1, B(1) + 1, B(2) + 1)

    Set RsOut = CreateResultRs(FldNames, FldValues(0) & FldValues(1) & FldValues(2))

    End Sub
    That's still far from a solution.

    Let's try something really simple now...

    In cHandler.cls there already exists a serverside routine, which does a String-Reflection (on an incoming single String-Parameter).
    Code:
    Public Sub ReflectString()
      Set RsOut = CreateResultRs("ReflectString", StrReverse(RsIn(1)))
    End Sub
    If I break up this routine into the "3 Stages" I recommended to you:
    - 1) Readout of the incoming, uploaded Input-Param(s) from RsIn into local variables
    - 2) Normal processing on the local variables
    - 3) Preparing the RsOut-container with a single result (multiple result-fields are possible as well, but that is a rarer case)
    it would look more like this:

    Code:
    Public Sub ReflectString()
      Dim S As String: S = RsIn.Fields(1).Value 'Stage 1 ... read Input
      
      S = StrReverse(S) 'Stage 2 ... process 
    
      Set RsOut = CreateResultRs("ReflectString", S) 'Stage 3 ... fill the Result into RsOut
    End Sub
    If I'd now challenge you, to write a serverside TransferString() Sub-Routine,
    which does no reversing or any other manipulation on the incoming String,
    but just passes it back as the routine-result - as it came in - could you do that - and post it here?


    Olaf
    Last edited by Schmidt; Apr 17th, 2019 at 05:54 PM.

Page 1 of 3 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