Inserting BLOB into a TEXT column

  • Thread starter Thread starter Guest
  • Start date Start date
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)
 
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
 
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)
Click to expand...
Click to expand...
 
Cor:

As you can see, we have been storing blobs in TEXT columns in VB6 for a long
time (see orgional post).
 
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
 
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)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top