Save BLOB to SQL2K TEXT column in VB.NET

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
CIP_WF_Scanned_ID int
Filename varchar
Body textHi
Have you seen
http://support.microsoft.com/defaul...b;en-us;317034?
John
"David A. Beck" <davidb@.beckb.com> wrote in message
news:%23UgkZnDaFHA.2984@.TK2MSFTNGP15.phx.gbl...
> 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
> CIP_WF_Scanned_ID int
> Filename varchar
> Body text
>|||The URL is invalid
*** Sent via Developersdex http://www.examnotes.net ***|||Hi David,
Please try this URL and let me know whether you could open the link
HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking
in ADO.NET and Visual Basic .NET
http://support.microsoft.com/kb/317034
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.