[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

Access 2007 updating a SQL 2005 image datatype field

I'm not much of a SQL guy here, so this may be really easy to do but I cant figure it out or find help online.

I'm programming in Visual Basic within a Microsoft Access 2007 database.  I have a SQL 2005 server.

My database needs to update the contents of an "image" datatype field on a database on the SQL server.  I dont think that it matters but the content Im trying to save is RTF text.

I do not need to READ anything from this field  simply WRITE my update to it.

I already have an ODBC link setup and I can already set the image field to null or to a string value (although that, of course, comes out garbled).  Ive attached code that I have that will successfully set the field to null.

I need to know how to take a visual basic string (which contains my RTF text & RTF codes) and convert it so that it can be stored in this field.

The creators of the database gave me the following query that will allow me to pull the data OUT of the field correctly if I use SQL Management Studio:

select convert(varchar(max),convert(varbinary(max),COMMENTS)) from EA7STUDENTGRADES

I thought I could easily reverse engineer this query and apply it to Visual Basic, but it appears to be beyond me!

Dim vCurrentTable
Set vCurrentTable = CurrentDb().OpenRecordset("SELECT dbo_EA7STUDENTGRADES.* FROM dbo_EA7STUDENTGRADES, dbOpenDynaset, dbSeeChanges)
vCurrentTable.Edit
vCurrentTable!COMMENTS = null
vCurrentTable.Update
vCurrentTable.close

Open in new window

0
RentANerds
Asked:
RentANerds
  • 7
  • 5
  • 3
1 Solution
 
Jeffrey CoachmanCommented:
RentANerds,

The RTF file format is a Word Processing format.
But you want to store a Word Processing File as an image?

Are you just looking to convert the RTF file to an Image file?
There are many utilities tha will do this:
http://www.google.com/search?hl=en&q=convert+rtf+to+image&aq=f&oq=

Or do you somehow want to take the RTF raw data and somehow manipulate it to be stored as an image?

Or are you just trying to store the rtf file as a Binary file (Not necessarily an image).


... In other words what do you actually need the file to be in the SQL database.
And what would these "Files" actually be when extracted from the SQL database, an rtf file again, or an images that look like the rtf file?

JeffCoachman
0
 
RentANerdsAuthor Commented:
Thanks for your reply Jeff.

My Microsoft Access database is modifying the content of an existing SQL 2005 database.  The original creators of the SQL 2005 database are the ones who chose to take RTF formatted text and store it has a binary image fieldtype.  I have no idea why but I can only guess that, with some previous version of SQL, it was an easy way to store the data.  But I don't know for sure.

So long story short, I'm looking to take a string variable (that happens to contain RTF data) in Access' Visual Basic and put it into a binary image field of a SQL database.
0
 
Jeffrey CoachmanCommented:
There is no documentation on how this was done previously?

Do a Google search of "Convert RTF to Binary"

Many of the utilities that clain to "Convert RTF to an Image" will make an image out of the rtf file, not really read in the rtf tags as you are requesting.

JeffCoachman
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
RentANerdsAuthor Commented:
Hi Jeff.

I've done numerous Google searches, including that one.  So far, none have brought me up anything overly helpful for this question.
0
 
Jeffrey CoachmanCommented:
Questions:

1. Again, no istructions were ever provided for this system?
2. Are there existing "Converted" files currently in the SQL table?
3. What happens to these files after they are stored in the SQL table? What is their purpose? Are they ever retrieved and/or displayed?
4. Are you bound to this mysterious system, or do you have the freedom to persue another storage option?
0
 
RentANerdsAuthor Commented:
Hi Jeff:

1 - Correct.  The only instructions I have were the SQL query as I posted previously.  The only problem/question I have is with the datatype conversion.  The format of the RTF information as well as getting it into the table have both been accomplished.  All I need to know is how to typecast or convert my data to the correct SQL format prior to saving it into the SQL Table.

2 - These aren't really files.  They are raw data.  Having said that, yes there are data in the SQL table.  Using the SQL query that was provided to me, I can succesfully see the data.  All I need to know is how to reverse the process with Visual Basic in Access.

3 - The data (again, not files) are used by an entirely different program for multiple tasks.  None are too relevant here to this I don't think.  I very specifically need the data I have converted to put into this field.

4 - 100% bound and stuck.  I need to figure out this exact problem.  :-)


Thanks for your reply,

-Mike
0
 
Anthony PerkinsCommented:
It should not be too difficult to accomplish using a ADO and an Update statement, if you are open to that, that is.
0
 
RentANerdsAuthor Commented:
Hi Acperkins:

Whatever gets the job done reliabily works for me!
0
 
Anthony PerkinsCommented:
1. Add a reference to MS ADO
2. Add this code (untested)

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Your connection string goes here"

cn.Execute "UPDATE YourTableName Set Comments = 'Your text goes here' Where  YourConditionGoesHere"

cn.Close
Set cn = Nothing
0
 
RentANerdsAuthor Commented:
Hi acperkins:

I'll try that code tomorrow at the office.  I'll be quite happy if it works!

Does the ADODB automatically convert the text to binary & then to the image format?

Amazingly simple!  I'll let you know the result.
0
 
Anthony PerkinsCommented:
>>Does the ADODB automatically convert the text to binary & then to the image format?<<
No.  ADO just passes the query to the SQL Server engine.  But SQL Server should implicitly convert it.  Otherwise try converting it explicitly, as in:
cn.Execute "UPDATE YourTableName Set Comments = CAST('Your text goes here' as Image) Where  YourConditionGoesHere"

And I also concur with the past comments.  Image is not only a lousy choice for what is essentially text, but is also a deprecated data type and will be removed in future versions.  It is a shame that there are still "professionals" who obviously know nothing about RTF, let alone SQL Server data types.
0
 
Anthony PerkinsCommented:
This is how I tested it:

1. With implict conversion.
Declare @dbo_EA7STUDENTGRADES table (COMMENTS image)

Insert @dbo_EA7STUDENTGRADES(COMMENTS)
Values ('First Image goes here')

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES

UPDATE @dbo_EA7STUDENTGRADES
SET COMMENTS = 'Updating First Image'

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES

2. With explicit conversion
Declare @dbo_EA7STUDENTGRADES table (COMMENTS image)

Insert @dbo_EA7STUDENTGRADES(COMMENTS)
Values (CAST('First Image goes here' as image))

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES

UPDATE @dbo_EA7STUDENTGRADES
SET COMMENTS = CAST('Updating First Image' as image)

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES
0
 
RentANerdsAuthor Commented:
Hi acperkins:

I think we may have it.  Based on what you sent, I've attached the function I've created to update the table.

vEA7StudentGradesID is the table's primary key so it allows me to find the correct row.
vNewActualComment is the new RTF data to save in the table.

I found out I have to replace a single quote (') with two single quotes ('') in my data, which is fine.

From what I can see, this all appears to work.  Do you happen to see anything wrong with what I did?

If my users don't report any problems with the data & database today, I'll count this issue as solved!

Thanks for all your help.
Function SaveUpdatedComment(vEA7StudentGradesID, vNewActualComment)
  Dim cn As ADODB.Connection
 
  Set cn = New ADODB.Connection
  
  cn.Open "BB-LIVE"
  
  cn.Execute "UPDATE EA7STUDENTGRADES SET EA7STUDENTGRADES.COMMENTS = CAST('" & Replace(vNewActualComment, "'", "''", 1, -1, vbBinaryCompare) & "' as image) WHERE EA7STUDENTGRADES.EA7STUDENTGRADESID=" & vEA7StudentGradesID
  
  cn.Close
  Set cn = Nothing
End Function

Open in new window

0
 
Anthony PerkinsCommented:
>>I found out I have to replace a single quote (') with two single quotes ('') in my data, which is fine.<<
That is correct.  Or you can parametize the query or even better still convert it to a stored procedure and you do not need to escape the single quotes.

>>Do you happen to see anything wrong with what I did?<<
The only thing I would change is this:
SET EA7STUDENTGRADES.COMMENTS =
For:
SET COMMENTS =

And
WHERE EA7STUDENTGRADES.EA7STUDENTGRADESID =
For:
WHERE EA7STUDENTGRADESID =


And I would keep the Replace simple as in:
Replace(vNewActualComment, "'", "''")
Instead of:
Replace(vNewActualComment, "'", "''", 1, -1, vbBinaryCompare)
0
 
RentANerdsAuthor Commented:
Thank-you very much!  We're rocking now!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now