dcsimg
Page 1 of 2 12 LastLast
Results 1 to 40 of 45

Thread: RichTextBox To/From SQL Server

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    RichTextBox To/From SQL Server

    How can I save the data (text and format) from RichTextBox control into SQL Server table and retrieve it from?
    I found the sample for saving and retrieving image. Although it looks like that approach should work for RTF format, it doesn't work.

    Thank you

  2. #2
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    How can I save the data (text and format) from RichTextBox control into SQL Server table and retrieve it from?
    I found the sample for saving and retrieving image. Although it looks like that approach should work for RTF format, it doesn't work.

    Thank you
    Save and retrieve the TextRTF property of the RichTextBox.
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by Eduardo- View Post
    Save and retrieve the TextRTF property of the RichTextBox.
    Any sample code please.

  4. #4
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    Any sample code please.
    I assume that you know how to save a record in a database and later read it.

    Get the text and save it:

    Code:
    rst!RTFText = RichTextBox1.TextRTF
    Retrieve the text from the database and set it back to the RichTextBox:

    Code:
    RichTextBox1.TextRTF = rst!RTFText
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    That approach produces error "Type Missmatched"
    If I use the same pretty complex approach way to save /retrieve RTF data using Stream object I get garbage in the RichTexBox. I have no way to check if the data was saved properly.

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    I tried NVARCHAR(MAX) datatype in the table, I tried Image type. Nothing works.

  7. #7
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    I tried NVARCHAR(MAX) datatype in the table, I tried Image type. Nothing works.
    You need to save just plain text, nothing else.
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    My initial request was about saving text and formatting. I have no problem with saving just text and see no reason to use RichTextBox for simple text.

  9. #9
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    Look, first try saving and retieving some text to and from the database.
    For example "ABCD".
    Once youa re able to save "ABCD" to the database and get it back, you are ready to go to work with the RichTextBox.

    Then, instead of "ABCD", save RichTextBox1.TextRTF

    To set the text back to the RichTextBox, I'll add a new line to the code:

    Code:
    RichTextBox1.Text =""
    RichTextBox1.TextRTF = rst!RTFText
    rst is the RecordSet, and RTFText is the field
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  10. #10
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    My initial request was about saving text and formatting. I have no problem with saving just text and see no reason to use RichTextBox for simple text.
    It is "simple text" in the sense that it is just normal plain text, or a String, or whatever you want to call something that is nothing but text.

    But that text has the format coded inside it.

    For saving the simple text of the RichTextBox I had said you need to save the .Text property, but I said .TextRTF
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    I use this code first:


    Code:
        strTempSQL = "SELECT * FROM dbo.___RTF"
        
        Set rsRTF = New ADODB.Recordset
        rsRTF.Open strTempSQL, objConn, adOpenStatic, adLockOptimistic
        
        rtfText.TextRTF = ""
    Then I enter some formatted text like bold underlined single word Test and save it this way:
    Code:
        rsRTF.Fields("RTF_Data").value = Me.rtfText.TextRTF
        rsRTF.Update
    This value appears in the SQL table field:
    {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}
    \viewkind4\uc1\pard\ul\b\f0\fs17 Test\ulnone\b0
    \par }

    Then I try to repeat the first block of code with added assignment:
    Code:
        strTempSQL = "SELECT * FROM dbo.___RTF"
        
        Set rsRTF = New ADODB.Recordset
        rsRTF.Open strTempSQL, objConn, adOpenStatic, adLockOptimistic
        
        rtfText.TextRTF = ""
        rtfText.TextRTF = rsRTF.Fields("RTF_Data").value
    I get an error "Out of Memory"
    I tried different datatypes for RTF_Data field in the database. All the tyme an error.
    One interesting thing: I see the long string I showed above if I watch it via SQL Management Studio, but just long string with a single questtion mark close to the beginning of the string if I try to use immidiate window in VB6 when I run the code and check values in the recordset.

  12. #12
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    I don't know about SQL Server databases because I never worked with them, but in Access databases you would need a Memo field. It is a text field not limited in size.

    I've done that of saving the RTF text into databases in my programs without problems.

    Check saving and retrieving texts from the database as I suggested before, save "ABCD" and see if you are getting "ABCD" or what.

    The TextRTF is just text, ANSI text, nothing special, it is not even Unicode.
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by Eduardo- View Post
    I don't know about SQL Server databases because I never worked with them, but in Access databases you would need a Memo field. It is a text field not limited in size.

    I've done that of saving the RTF text into databases in my programs without problems.

    Check saving and retrieving texts from the database as I suggested before, save "ABCD" and see if you are getting "ABCD" or what.

    The TextRTF is just text, ANSI text, nothing special, it is not even Unicode.
    Thank you very much for your attempt to help. Last week I spent 2 days on trying to save and retrieve RTF from VB6 and SQL Server.
    I found several ways to do this with Access database and with SQL Server but with VB.Net. No success with VB6 and SQL Server.
    What I have now:
    I successfuly saved data with formatting. It appears as long string with sequence of characters. When I retrieve that data and try to assign to the TextRTF property I get an Out Of Memory error. But when I copy the value directly from SQL Table and assigne it to the TextRTF property by hardcoding it works.
    So, now I need to find out why the recordset contains the different data.

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Instead of :
    rtfText.TextRTF = rsRTF(3).Value I used
    rtfText.TextRTF = "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\ul\b\f0\fs17 Test\ulnone\b0\par }"
    and it brought formatted simple text I saved in the table.
    If I run the view in SQL server it returns the long string I showed above, Why the recordset contains different data I have no idea. And that data creates Out Of Memory situation. Maybe some kind of conversion is required.

  15. #15
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: RichTextBox To/From SQL Server

    SQL Server it would be an NVarchar(Max) field....
    This value appears in the SQL table field:
    {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}
    \viewkind4\uc1\pard\ul\b\f0\fs17 Test\ulnone\b0
    \par }
    Good... it's being saved correctly at least. That's what raw RTF looks like.

    Code:
        strTempSQL = "SELECT * FROM dbo.___RTF"
        
        Set rsRTF = New ADODB.Recordset
        rsRTF.Open strTempSQL, objConn, adOpenStatic, adLockOptimistic
        
        rtfText.TextRTF = ""
        rtfText.TextRTF = rsRTF.Fields("RTF_Data").value
    Let's try something ....
    Code:
        strTempSQL = "SELECT RTF_Data FROM dbo.___RTF"   ' <-- Put a breakpoint here, then step through the rest of the code.
        
        Set rsRTF = New ADODB.Recordset
        rsRTF.Open strTempSQL, objConn, adOpenStatic, adLockOptimistic
        
        Dim RTFText as string
    
        RTFText = rsRTF.Fields("RTF_Data").value ' <-- Make sure this line works!
        rtfText.TextRTF = RTFText '<-- Then make sure this line works!
    After setting the RTFText variable, compare it to what's in the database and make sure it what you expect it to be.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    I added strRTFText as string to avoid conflict with the existing control name.
    Assigned the value from the recordset. It is different from what I have in the database.
    Assignment to the varable was successful but the assignment of the value of variable creates Out Of Memory problem.

  17. #17
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    It is different from what I have in the database.
    The question is: what is the difference?

    A null character at the end? The caracters are interleaved with null characters? A completely different string?

    Can you paste here what you get?
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  18. #18
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: RichTextBox To/From SQL Server

    Do you have an NVARCHAR field? OR a NCHAR field? Check... there's a difference.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    I change the way I create the recordset:
    Code:
        Set cndTemp = New ADODB.Command
        Set cndTemp.ActiveConnection = objConn
        cndTemp.CommandType = adCmdStoredProc
        cndTemp.CommandText = "___GetRTF"    'I created the stored procedure in SQL Server
        
        Set rsTempRTF = cndTemp.Execute
    and still get the wrong value and then Out Of Memory error.
    I suspect that there is something in ADODB requirering some additional processing. But from another point of view why the text cannot go without any change from DB into Recordset.
    Strange.

  20. #20
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    I change the way I create the recordset:
    Code:
        Set cndTemp = New ADODB.Command
        Set cndTemp.ActiveConnection = objConn
        cndTemp.CommandType = adCmdStoredProc
        cndTemp.CommandText = "___GetRTF"    'I created the stored procedure in SQL Server
        
        Set rsTempRTF = cndTemp.Execute
    and still get the wrong value and then Out Of Memory error.
    I suspect that there is something in ADODB requirering some additional processing. But from another point of view why the text cannot go without any change from DB into Recordset.
    Strange.
    What techgnome is asking to you is not how you create the recordset, but what data type is the field where you store the text.
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    In my previous attempts I tried varbinary(MAX), image and they did not work even on saving, then I added nvarchar(MAX) and varchar(MAX).
    Both types allow to save RTF data and retrieve via MSSQL Server Management Studio.
    But VB ADODB.Recordset does return strings different from what I see via MS

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    I just tried manual update one of the fields with simple not formatted text, just the word Test. VB Recordset returns empty string with the lenght = 4, when I changed the value to Testing the length becomes = 7. So, the recordset contains the proper number of characters, at least from these 2 tests it looks like that, but unfortunately by some reason doesn't contain the real characters saved in the field.
    What is it, bug in ADODB, bug in my head (I trust this version more) or something else?

  23. #23
    Frenzied Member
    Join Date
    Feb 2017
    Posts
    1,049

    Re: RichTextBox To/From SQL Server

    I have no idea. I will leave this thread to techgnome or anyone with knowledge about SQL Server.
    vbExtra: Print preview for VB6, print FlexGrids and more.
    MSDN online for VB6, Language reference.

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Thank you very much for your help. With your help I at list moved to the second step - retrieving data.

  25. #25
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: RichTextBox To/From SQL Server

    Code:
        Set cndTemp = New ADODB.Command
        Set cndTemp.ActiveConnection = objConn
        cndTemp.CommandType = adCmdStoredProc
        cndTemp.CommandText = "___GetRTF"    'I created the stored procedure in SQL Server
        
        Set rsTempRTF = cndTemp.Execute
    So after setting the recordset, if you check the fields, is it correct or not in this case?
    I think I know what's going on... ADODB predates the use of (n)varchar(max) ... so I wonder if that's causing the issue... Try this...

    change your sproc to convert the field to a smaller nvarchar length... like 1024...

    Something like this:
    Code:
    select convert(nvarchar(1024), RTF_Data) as RTF_Data from ___RTF
    (what is it with all those underscores? really? Anyways...

    It might be that the ADO SQL Server drivers weren't updated to handle *char(max) fields... I know I didn't start using it until long after I moved to .NET...

    If after converting it, you find it does work... then there's the problem. What does that mean? If you're going to use strings longer than 8k, you'll want to use a BLOB to shuttle the value in/out of SQL Server. If it's going to be shorter than that, and always, then you can set the limit on the database in the table, and then enforce it with a parameterized sproc query. At least that's how I'd do it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  26. #26

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    I tried nchar(4000) datatype for the column. It is a maximum size allowed. I manually tried to copy the string from NVarChar(Max) field. Although the length of the string is much smaller than 4000 only the part of the string was inserted into the NChar(4000) column. And that value is appearing in the recordset. Is it a small step forward or step to nowhere?

  27. #27

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by techgnome View Post
    Code:
        Set cndTemp = New ADODB.Command
        Set cndTemp.ActiveConnection = objConn
        cndTemp.CommandType = adCmdStoredProc
        cndTemp.CommandText = "___GetRTF"    'I created the stored procedure in SQL Server
        
        Set rsTempRTF = cndTemp.Execute
    So after setting the recordset, if you check the fields, is it correct or not in this case?
    I think I know what's going on... ADODB predates the use of (n)varchar(max) ... so I wonder if that's causing the issue... Try this...

    change your sproc to convert the field to a smaller nvarchar length... like 1024...

    Something like this:
    Code:
    select convert(nvarchar(1024), RTF_Data) as RTF_Data from ___RTF
    (what is it with all those underscores? really? Anyways...

    It might be that the ADO SQL Server drivers weren't updated to handle *char(max) fields... I know I didn't start using it until long after I moved to .NET...

    If after converting it, you find it does work... then there's the problem. What does that mean? If you're going to use strings longer than 8k, you'll want to use a BLOB to shuttle the value in/out of SQL Server. If it's going to be shorter than that, and always, then you can set the limit on the database in the table, and then enforce it with a parameterized sproc query. At least that's how I'd do it.

    -tg
    Thank you very much. I will try your scenarion

  28. #28

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Yes, it worked when I rewrote the stored procedure as you suggested.
    For testing I had a pretty small formatted text with my first and last name (13 characters in total). These 2 words were formatted (different color, different font). The length of the string presenting those 2 words is 349.
    The RichTextBox has a MaxLength property, but I think I cannot use it because probably it counts real characters, not formatting stuff, but database will or will not accept the string analyzing its entire length.
    You mentioned BLOB in your explanation. I started to look for the way to store/retrive RTF data with the scenario I used to store images in the database.
    Unfortunately I got no success.
    Do you have any link to code showing how to use BLOB for RTF?
    Thank you very much.

  29. #29
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: RichTextBox To/From SQL Server

    IT's the same thing... you need to get the RTF, convert it to an array of bytes and send that as your BLOB (instead of an array of bytes from an image)... and on the reverse, reverse the process... get the BLOB into an array of bytes from the server, convert it back into the RTF and stuff it into the RTB.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  30. #30

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Thank you. I tried with no success. Now, at least I can save some other way, I will start with retrieving. As I remember I had the problem with converting back to RTF file.

  31. #31
    Fanatic Member
    Join Date
    Apr 2015
    Location
    Finland
    Posts
    614

    Re: RichTextBox To/From SQL Server

    Save RTF file to disk and use bulk insert.

    See this Christine Lawrie answer, except change fieldtype to VarBinary(Max)
    https://www.sqlservercentral.com/For...066-338-1.aspx

    and relevant part fex. to
    SELECT * FROM OPENROWSET(BULK 'C:\Testfile\TestDoc.rtf', SINGLE_BLOB) AS Test

    Otherwise (without diskfile nor stream) you may try to base64 encode/decode RTF control data.

  32. #32

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by Tech99 View Post
    Save RTF file to disk and use bulk insert.

    See this Christine Lawrie answer, except change fieldtype to VarBinary(Max)
    https://www.sqlservercentral.com/For...066-338-1.aspx

    and relevant part fex. to
    SELECT * FROM OPENROWSET(BULK 'C:\Testfile\TestDoc.rtf', SINGLE_BLOB) AS Test

    Otherwise (without diskfile nor stream) you may try to base64 encode/decode RTF control data.
    Thank you, I'll try

  33. #33

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    I tried Christine way with no success. I get an error "XML parsing: line 5, character 1, illegal xml character"
    I searched Google for base64 encode/decode RTF sample with no success. My attempts to adjust the code which saves and retrieves Image to process RTF were unsuccessful as well.

  34. #34

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    An attempt to use ADODB Stream with this code:
    Code:
        strTempSQL = "select ID, convert(nvarchar(max), RTF_NVARCHAR) as RTF_NVARCHAR from ___RTF"
        
        Set rsRTF = New ADODB.Recordset
        rsRTF.Open strTempSQL, objConn, adOpenKeyset, adLockOptimistic, adCmdText
        
        Set stmStream = New ADODB.Stream
        stmStream.Type = adTypeText
        stmStream.Open
        stmStream.WriteText rsRTF!RTF_NVARCHAR
        
        If DirExists(App.Path & "\Temp") = False Then
            MkDir App.Path & "\Temp"
        End If
        stmStream.SaveToFile App.Path & "\Temp\Temp.rtf", adSaveCreateOverWrite
    created file with sequence of small squares instead of real characters.

  35. #35
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,734

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    What is it, bug in ADODB, bug in my head (I trust this version more) or something else?
    Definitely no bug in ADO - I guess your Field-Definition is somehow wrong (or your treatment of the Field-Value at the VB-side):

    Here an example, which works against SQLServer 2012 (using three different FieldTypes in a dynamically created Table [TestRTF]):
    Code:
    Option Explicit
    
    Const RTFText$ = "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\ul\b\f0\fs17 Test\ulnone\b0\par }"
    
    Private Cnn As New Connection
    
    Private Sub Form_Load()
      Caption = "Click Me..."
      Cnn.Open "Provider=SQLOleDB.1;Data Source=(local);Initial Catalog=NorthWnd;", "sa", "YourDbPassword"
    End Sub
    
    Private Sub Form_Click()
      On Error GoTo 1
      Cnn.Execute "Create Table TestRTF(ID Integer Identity Primary Key, Rtf1 NVarChar(max), Rtf2 VarChar(max), Rtf3 VarBinary(max))"
     
      With GetRs("Select * From TestRTF Where 1=0") 'add a new record (providing all 3 Rtf-Field-Types with the same Rtf-Const-content)
        .AddNew
           !Rtf1.Value = RTFText
           !Rtf2.Value = RTFText
           !Rtf3.Value = ToByteArray(RTFText)
        .UpdateBatch
        .Close
      End With
     
      With GetRs("Select Top 1 * From TestRTF Order By ID Desc") 'read-direction - checking what we just inserted (in a new roundtrip)
        Debug.Print "NVarChar(max)  RTFText = !Rtf1.Value: "; RTFText = !Rtf1.Value
        Debug.Print "VarChar(max)   RTFText = !Rtf2.Value: "; RTFText = !Rtf2.Value
        Debug.Print "VarBinary(max) RTFText = FromByteArray(!Rtf3.Value): "; RTFText = FromByteArray(!Rtf3.Value)
        .Close
      End With
      
    1 If Err Then MsgBox Err.Description
      On Error Resume Next
        Cnn.Execute "Drop Table TestRTF"
      Err.Clear
    End Sub
    
    'a few helper-functions for the above "Form_Click-Test"
    Public Function GetRs(Query As String, ParamArray P()) As Recordset
      Dim Cmd As New Command, i As Long
      Set Cmd.ActiveConnection = Cnn
          Cmd.CommandText = Query
      For i = 0 To UBound(P): Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i)): Next
      Set GetRs = New ADODB.Recordset
          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
    End Function
    
    'ToUtf8 and FromUtf8 (instead of plain UTF16 as below), would be a bit more storage-efficient in the DB-Blob-Field (VarBinary(max))
    Public Function ToByteArray(S As String) As Byte()
      ToByteArray = S
    End Function
    Public Function FromByteArray(B() As Byte) As String
      FromByteArray = B
    End Function
    The above delivers (from all 3 Fields) exactly the same content as was priorily put in with the Insert - printing out:
    Code:
    NVarChar(max)  RTFText = !Rtf1.Value: True
    VarChar(max)   RTFText = !Rtf2.Value: True
    VarBinary(max) RTFText = FromByteArray(!Rtf3.Value): True
    Olaf

  36. #36

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    When I run your code I get an error on this line and highlight property value:
    Code:
        Debug.Print "VarBinary(max) RTFText = FromByteArray(!Rtf3.Value): "; rtfText = FromByteArray(!Rtf3.value)
    I commented that line.
    After that I checked the contents of fields in the new table via MS SQL Management Studio. NVarChar and VarChar fields contain absolutely the same string as it is in RTFText$.
    But the immidiate window gives:
    Code:
    NVarChar(max)  RTFText = !Rtf1.Value: False
    VarChar(max)   RTFText = !Rtf2.Value: False
    If I try to put the field value into RichTextBox control I get Out of Memory error.
    If I print the contents of fields in the immidiate window I get this:
    Code:
    ?!rtf1.value
      ?   E ??        ?                                                                                                                        
    
    ?!Rtf2.value
          „                !
    I have no SQL 2012. I have SQL Server 2008 R2. May it be the reason why I get different result than you?
    Or maybe there are some other things affecting the process?

    This problem drives me crazy.
    Thank you for your help.
    Last edited by chapran; Dec 6th, 2017 at 09:44 PM.

  37. #37

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    The output in immidiate window was different than this site shows. There were some characters like triangle vertical lines, double arrows which is not displayed here. I also forgot to tell that insert worked for all three fields, the third field says Bynary Data, which I beleive means that insertion was successful.
    Retrieve is a problem now.

    Thank you

  38. #38
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    17,448

    Re: RichTextBox To/From SQL Server

    Just to add a little more. I have SQL Server 2014 and used NVarChar(max) without any problems. No longer have SQL Server 2008 to play along, sorry.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  39. #39
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: RichTextBox To/From SQL Server

    Just for S'n'Gs... what does your connection string look like?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  40. #40

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by techgnome View Post
    Just for S'n'Gs... what does your connection string look like?

    -tg
    Wow!!!
    Within many days I was fighting with this problem on my local server with the connection string
    Driver={SQL Server Native Client 10.0};Server=(local);Database=FnsNew;Trusted_Connection=yes;
    And I always got wrong result.
    After you asked about connection string I decided to switch to production database just to test the code I've got from Schmidt. The connection string is:
    DRIVER=SQL Server;SERVER=SRSNJC;UID=coordin;DATABASE=FnsNew;pwd=coordec

    And the Schmidt's code WORKED!!!!

    What's wrong with my connection to local server? I use it for years with no problem for any operation. And with storing Rtf data I spent many days and only today you put me to the right direction.
    Thank you very much!

Page 1 of 2 12 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
  •  



Featured


Click Here to Expand Forum to Full Width