none
Object must implement IConvertible....

    Question

  • Using:  VB.Net 2003, SQL Server 2000 and nText Column...

    Examples: "Writing BLOB Values to a Database  [Visual Basic]" and "Conserving Resources When Writing BLOB Values to SQL Server  [Visual Basic]"

    I modified the following code from the samples that were available in Help and MSDN.  When my application reaches the ExecuteNonQuery statement it informs me that the Byte Array oDocument must implement IConvertible.  I have even tried the extended version which uses a SQL Insert followed by UPDATETEXT with Pointers, and I get the same result with the Byte Array used in that example.  My goal is to have Users Append certain documents to Proposals within my database, as I understand that is what I would be doing anyway if I used SharePoint.

    Following the examples I have not trouble whatsoever reading an Image File from SQL Server 2000, and therefore I expect no trouble with nText Fields.  I just cannot seem to insert Documents or Images via VB Code.

    What is wrong with the examples that I cannot use the same code?

    ---- VB Code Follows ----

    Private Sub btnSelectFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectFile.Click

    Dim oSQLConnection As System.Data.SqlClient.SqlConnection

    Dim oSQLCommand As System.Data.SqlClient.SqlCommand

    Dim oSQLDataReader As System.Data.SqlClient.SqlDataReader

    Dim oDocument() As Byte

    Try

    'Define the Initial Directory to be the "My Documents" Directory.

    ofdAttachment.InitialDirectory = "My Documents"

    'Filter for File of Word, PDF, and Text. This will suffice for now.

    ofdAttachment.Filter = "Word Documents (*.doc)|*.doc|PDFs (*.pdf)|*.pdf|Text Files (*.txt)|*.txt"

    'Show the Dialog to Open a File.

    ofdAttachment.ShowDialog()

    'If the User Selected a File then attempt to Add the Attachment.

    If ofdAttachment.FileName <> String.Empty Then

    'Read the Document into a Byte Array.

    oDocument = GetDocumentInBytes(ofdAttachment.FileName)

    'Define the Connection to the SQL Database.

    oSQLConnection = New System.Data.SqlClient.SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=proposal_tracking;server=dbserver\dbserver;")

    'Attempt to Open the SQL Connection.

    oSQLConnection.Open()

    'Create a New SQL Command Object.

    oSQLCommand = New System.Data.SqlClient.SqlCommand

    'Assign the SQL Connection information to the SQL Command Object.

    oSQLCommand.Connection = oSQLConnection

    'Define the SQL Statement to Attach the Document.

    oSQLCommand.CommandText = "INSERT INTO Documents (DocumentName, Document) VALUES (@DocumentName, @Document)"

    oSQLCommand.CommandType = CommandType.Text

    'Define the Parameters of the SQL Statement.

    oSQLCommand.Parameters.Add("@DocumentName", SqlDbType.VarChar).Value = Mid(ofdAttachment.FileName, ofdAttachment.FileName.LastIndexOf("\") + 2)

    oSQLCommand.Parameters.Add("@Document", SqlDbType.NText, oDocument.Length).Value = oDocument

    'Execut the SQL Statement. Resulting in an Error that states:

    'Object must implement IConvertible.

    oSQLCommand.ExecuteNonQuery()

    If Not oSQLCommand Is Nothing Then

    oSQLCommand.Dispose()

    oSQLCommand = Nothing

    End If

    If Not oSQLConnection Is Nothing Then

    oSQLConnection.Close()

    oSQLConnection = Nothing

    End If

    End If

    Catch sqlex As System.Data.SqlClient.SqlException

    MsgBox(sqlex.Message)

    Catch ex As Exception

    MsgBox(ex.Message)

    End Try

    End Sub

    Public Shared Function GetDocumentInBytes(ByVal strFilePath As String) As Byte()

    Dim ofsDocumentReader As System.IO.FileStream = New System.IO.FileStream(strFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read)

    Dim obrDocumentReader As System.IO.BinaryReader = New System.IO.BinaryReader(ofsDocumentReader)

    Dim oDocument() As Byte

    Try

    oDocument = obrDocumentReader.ReadBytes(CInt(ofsDocumentReader.Length))

    obrDocumentReader.Close()

    ofsDocumentReader.Close()

    Return oDocument

    Catch ex As Exception

    MsgBox(ex.Message)

    End Try

    End Function

    Monday, October 16, 2006 9:02 PM

Answers

  • NText is the wrong Data Type for the Column.  It should be Image and everything works as written.  The problem arises in that the explaination of the Image Data Type is weak, and the examples all work with picture files, and that in combination with a poorly chosen word (Image) lead me to believe that pictures had their own data type.  Image data type is really a byte level representation of any File, regardless the type.
    Friday, October 20, 2006 2:45 PM