Upload files data and Info to the database

Posted on 2012-04-11
Medium Priority
Last Modified: 2012-04-29
I have a method in my repository that uses the command object to add files data and Info to the database. I have 2 pages that uses this method. One page works every time 100% of the time. The second location never works. I have being dealing with this for a c full 24 hours now. The script fails when I attempt the retrieval of the output ID (fileId). I get the “Object reference not set to an instance of an object.” What I don’t understand is why it works from one page and not the other. I could really use your insight

Thank you

Question by:ruffone
  • 2
LVL 20

Expert Comment

ID: 37836266
i would suggest two things here:-

1. i could see transaction code with only commit, there is no rollback option.
2. Why would you need to use sqluploadstream, you can write your binary/buffer of bytes directly to sql server using ExecuteNonQuery. It will be much easier and straight forward.

Also please ensure that you are closing the connection after using, and every DB Write is opening up new connection. This exception generally happens when you try to use disposed object.

Accepted Solution

ruffone earned 0 total points
ID: 37889297
Adding the line "file.fileData.Seek(0, SeekOrigin.Begin)" fixed the issue
        Using conn1 As SqlConnection = GetConnection()
            Using trn As SqlTransaction = conn1.BeginTransaction()
                Dim cmdInsert As New SqlCommand(SQL_INSERT, conn1, trn)
                cmdInsert.Parameters.Add("@fileData", SqlDbType.VarBinary, -1)
                cmdInsert.Parameters.Add("@fileGuid", SqlDbType.UniqueIdentifier)
                cmdInsert.Parameters("@fileGuid").Value = file.fileGuid
                cmdInsert.Parameters.Add("@fileId", SqlDbType.BigInt)
                cmdInsert.Parameters("@fileId").Direction = ParameterDirection.Output
                Dim cmdUpdate As New SqlCommand(SQL_UPDATE, conn1, trn)
                cmdUpdate.Parameters.Add("@fileData", SqlDbType.VarBinary, -1)
                cmdUpdate.Parameters.Add("@fileGuid", SqlDbType.UniqueIdentifier)
                cmdUpdate.Parameters("@fileGuid").Value = file.fileGuid
                Using uploadStream As Stream = _
                    New BufferedStream(
                    New SqlStreamUpload() _
                    With {.InsertCommand = cmdInsert,
                          .UpdateCommand = cmdUpdate,
                          .InsertDataParam = cmdInsert.Parameters("@fileData"),
                          .UpdateDataParam = cmdUpdate.Parameters("@fileData")
                         }, 8040)
                    file.fileData.Seek(0, SeekOrigin.Begin)
                End Using
                ret = Convert.ToInt64(cmdInsert.Parameters("@fileId").Value.ToString())
            End Using

Open in new window


Author Closing Comment

ID: 37907552
Thank you

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conte…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question