Inserting BLOB into a TEXT column

G

Guest

In VB6 I store PDF files as BLOBS in a TEXT column of a SQL table
The VB6 code works and I'm trying to do the same in VB.NET
without success. Any help?

Dim vText As Variant, vLinesFromFile As Variant
Dim nFile as Integer
Dim sSQL as String

sSQL = "SELECT * FROM CIP_WF_DOCUMENTS WHERE [FILENAME] = ''"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

nFile = FreeFile
Open "c:\TEST.PDF" For Binary As #nFile
vText = StrConv(InputB(LOF(nFile), nFile), vbUnicode)
rs.AddNew
rs("CIP_WF_Status_ID").Value = gAccount.nCIP_WF_Status_ID
If nCIP_Documents_ID <> 0 Then ' Rev 11/03/2004
rs("CIP_Documents_ID").Value = nCIP_Documents_ID
End If
rs("Status").Value = nSubstitutePages ' Rev 04/20/2005 was 0
rs("Date").Value = gdNowDate
rs("FILENAME").Value = sName
rs("BODY").Value = vText
rs("DocOrder").Value = nDocOrder' Rev 04/20/2005
rs.Update
=VB.NET=================================================================
Private Function sqlSave(ByVal fileName As String) As Integer
Dim IsConnecting As Boolean = True
Dim nReturn As Integer = 0
Dim con As New SqlConnection(gsConnectionString)
Dim sSql As String = "INSERT INTO CIP_WF_Scanned(Filename, [Body])
VALUES(@fileName, @Body)"
Dim pdfImage() As Byte = GetPDFImage(fileName)
Dim addCIP_WF_Scanned As New SqlClient.SqlCommand(sSql, con)
addCIP_WF_Scanned.Parameters.Add("@fileName", SqlDbType.NVarChar,
50).Value = fileName.ToString
addCIP_WF_Scanned.Parameters.Add("@Body", SqlDbType.Text,
pdfImage.Length).Value = Convert.ToString(pdfImage)
Try
con.Open()
addCIP_WF_Scanned.ExecuteNonQuery()
con.Close()
sqlSave = 1
Catch exc As Exception
sqlSave = 0
SendSQLErrorEmail("sqlSave", sSql, exc)
End Try
con = Nothing
End Function
Public Shared Function GetPDFImage(ByVal filePath As String) As Byte()
Dim fs As FileStream = New FileStream(filePath, FileMode.Open,
FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim pdfImage() As Byte = br.ReadBytes(fs.Length)
br.Close()
fs.Close()
Return pdfImage
End Function
=TABLE==============================================================
Column_name Type Computed Length Prec Scale Nullable
CIP_WF_Scanned_ID int no 4 10 0 no
Filename varchar no 50 no no
DateTime datetime no 8 no
Barcode varchar no 50 no no
CIP_ID int no 4 10 0 no (n/a)
PageNumber int no 4 10 0 no (n/a)
Status int no 4 10 0 no (n/a) (n/a)
Body text no 16 yes (n/a) (n/a)
 
C

Cor Ligthert

Indianabeck,

I think that the most is right (I did not check it all), however you cannot
store a Blob in a Text column it has to go to an Image column.

I hope this helps,

Cor
 
G

Guest

Elton:

This code yields "System.Byte[]" in the type TEXT column rather than the
actual contents.

Elton W said:
Hope following URL is hopeful to you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;326502

Elton Wang
(e-mail address removed)


indianabeck said:
In VB6 I store PDF files as BLOBS in a TEXT column of a SQL table
The VB6 code works and I'm trying to do the same in VB.NET
without success. Any help?

Dim vText As Variant, vLinesFromFile As Variant
Dim nFile as Integer
Dim sSQL as String

sSQL = "SELECT * FROM CIP_WF_DOCUMENTS WHERE [FILENAME] = ''"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

nFile = FreeFile
Open "c:\TEST.PDF" For Binary As #nFile
vText = StrConv(InputB(LOF(nFile), nFile), vbUnicode)
rs.AddNew
rs("CIP_WF_Status_ID").Value = gAccount.nCIP_WF_Status_ID
If nCIP_Documents_ID <> 0 Then ' Rev 11/03/2004
rs("CIP_Documents_ID").Value = nCIP_Documents_ID
End If
rs("Status").Value = nSubstitutePages ' Rev 04/20/2005 was 0
rs("Date").Value = gdNowDate
rs("FILENAME").Value = sName
rs("BODY").Value = vText
rs("DocOrder").Value = nDocOrder' Rev 04/20/2005
rs.Update
=VB.NET=================================================================
Private Function sqlSave(ByVal fileName As String) As Integer
Dim IsConnecting As Boolean = True
Dim nReturn As Integer = 0
Dim con As New SqlConnection(gsConnectionString)
Dim sSql As String = "INSERT INTO CIP_WF_Scanned(Filename, [Body])
VALUES(@fileName, @Body)"
Dim pdfImage() As Byte = GetPDFImage(fileName)
Dim addCIP_WF_Scanned As New SqlClient.SqlCommand(sSql, con)
addCIP_WF_Scanned.Parameters.Add("@fileName", SqlDbType.NVarChar,
50).Value = fileName.ToString
addCIP_WF_Scanned.Parameters.Add("@Body", SqlDbType.Text,
pdfImage.Length).Value = Convert.ToString(pdfImage)
Try
con.Open()
addCIP_WF_Scanned.ExecuteNonQuery()
con.Close()
sqlSave = 1
Catch exc As Exception
sqlSave = 0
SendSQLErrorEmail("sqlSave", sSql, exc)
End Try
con = Nothing
End Function
Public Shared Function GetPDFImage(ByVal filePath As String) As Byte()
Dim fs As FileStream = New FileStream(filePath, FileMode.Open,
FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim pdfImage() As Byte = br.ReadBytes(fs.Length)
br.Close()
fs.Close()
Return pdfImage
End Function
=TABLE==============================================================
Column_name Type Computed Length Prec Scale Nullable
CIP_WF_Scanned_ID int no 4 10 0 no
Filename varchar no 50 no no
DateTime datetime no 8 no
Barcode varchar no 50 no no
CIP_ID int no 4 10 0 no (n/a)
PageNumber int no 4 10 0 no (n/a)
Status int no 4 10 0 no (n/a) (n/a)
Body text no 16 yes (n/a) (n/a)
 
G

Guest

Cor:

As you can see, we have been storing blobs in TEXT columns in VB6 for a long
time (see orgional post).
 
G

Guest

Peter

I feel a conversion program coming on. I knew it would work with an image
column but now I have to go back and covert the table we have had since TEXT
was the only place you could put a BLOB. Oh, well. I guess I need to test the
VB6 program to make sure it still works.

Dab
 
G

Guest

Cor is right. You should use image type to save binary data.

In VB6 you use StrConv(InputB(LOF(nFile), nFile), vbUnicode) to create
string to save string content to text.

HTH

Elton Wang


indianabeck said:
Elton:

This code yields "System.Byte[]" in the type TEXT column rather than the
actual contents.

Elton W said:
Hope following URL is hopeful to you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;326502

Elton Wang
(e-mail address removed)


indianabeck said:
In VB6 I store PDF files as BLOBS in a TEXT column of a SQL table
The VB6 code works and I'm trying to do the same in VB.NET
without success. Any help?

Dim vText As Variant, vLinesFromFile As Variant
Dim nFile as Integer
Dim sSQL as String

sSQL = "SELECT * FROM CIP_WF_DOCUMENTS WHERE [FILENAME] = ''"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

nFile = FreeFile
Open "c:\TEST.PDF" For Binary As #nFile
vText = StrConv(InputB(LOF(nFile), nFile), vbUnicode)
rs.AddNew
rs("CIP_WF_Status_ID").Value = gAccount.nCIP_WF_Status_ID
If nCIP_Documents_ID <> 0 Then ' Rev 11/03/2004
rs("CIP_Documents_ID").Value = nCIP_Documents_ID
End If
rs("Status").Value = nSubstitutePages ' Rev 04/20/2005 was 0
rs("Date").Value = gdNowDate
rs("FILENAME").Value = sName
rs("BODY").Value = vText
rs("DocOrder").Value = nDocOrder' Rev 04/20/2005
rs.Update
=VB.NET=================================================================
Private Function sqlSave(ByVal fileName As String) As Integer
Dim IsConnecting As Boolean = True
Dim nReturn As Integer = 0
Dim con As New SqlConnection(gsConnectionString)
Dim sSql As String = "INSERT INTO CIP_WF_Scanned(Filename, [Body])
VALUES(@fileName, @Body)"
Dim pdfImage() As Byte = GetPDFImage(fileName)
Dim addCIP_WF_Scanned As New SqlClient.SqlCommand(sSql, con)
addCIP_WF_Scanned.Parameters.Add("@fileName", SqlDbType.NVarChar,
50).Value = fileName.ToString
addCIP_WF_Scanned.Parameters.Add("@Body", SqlDbType.Text,
pdfImage.Length).Value = Convert.ToString(pdfImage)
Try
con.Open()
addCIP_WF_Scanned.ExecuteNonQuery()
con.Close()
sqlSave = 1
Catch exc As Exception
sqlSave = 0
SendSQLErrorEmail("sqlSave", sSql, exc)
End Try
con = Nothing
End Function
Public Shared Function GetPDFImage(ByVal filePath As String) As Byte()
Dim fs As FileStream = New FileStream(filePath, FileMode.Open,
FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim pdfImage() As Byte = br.ReadBytes(fs.Length)
br.Close()
fs.Close()
Return pdfImage
End Function
=TABLE==============================================================
Column_name Type Computed Length Prec Scale Nullable
CIP_WF_Scanned_ID int no 4 10 0 no
Filename varchar no 50 no no
DateTime datetime no 8 no
Barcode varchar no 50 no no
CIP_ID int no 4 10 0 no (n/a)
PageNumber int no 4 10 0 no (n/a)
Status int no 4 10 0 no (n/a) (n/a)
Body text no 16 yes (n/a) (n/a)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top