Problem occurs while store varbinary type data from ASP.NET C#

Question

0.00/5 (No votes)

See more:

Hello,

Here i have comlaintmaster table in database along with varbinary and many more fields.
When I going to save data to this table then its not working.

Please help me,

Thanks

What I have tried:

This is my table structure

CREATE TABLE [dbo].[tblComplaintMaster](
	[complID] [int] IDENTITY(1,1) NOT NULL,
	[complNo] [varchar](20) NOT NULL,
	[FinYr] [varchar](30) NOT NULL,
	[complDate] [date] NOT NULL,
	[complNature] [varchar](50) NOT NULL,
	[product] [varchar](100) NOT NULL,
	[prodct_srno] [varchar](20) NOT NULL,
	[issue_id] [int] NOT NULL,
	[descr] [varchar](255) NOT NULL,
	[assignto] [varchar](50) NOT NULL,
	[reqby] [varchar](50) NOT NULL,
	[contact_no] [varchar](50) NOT NULL,
	[email] [varchar](50) NOT NULL,
	[reqLoc] [varchar](100) NOT NULL,
	[priority] [varchar](20) NOT NULL,
	[status] [varchar](20) NOT NULL,
	[fromtime] [time](7) NULL,
	[totime] [time](7) NULL,
	[ReassignTo] [varchar](50) NULL,
	[ReassignDT] [date] NULL,
	[reason] [varchar](100) NULL,
	[call_report] [varbinary](max) NULL,
	[content_type] [varchar](50) NULL
)

and this my code

 string complNo = txtComplNo.Text;
            string finyr = "01-04-2018-31-03-2018";
            DateTime complDt = Convert.ToDateTime(txtComplaintDt.Text);
            string complNature = ddlComplList.SelectedValue;
            string product = ProductList.SelectedValue;
            string srno = srnoList.Text;
            string descr = txtDesc.Text;
            string assignto = AssignToList.SelectedValue;
            string reqBy = UserList.SelectedValue;
            string loc = txtReqLoc.Text;
            string priority = ddlPriorityList.SelectedValue;
            string fromTime = DateTime.Now.ToString("HH:mm:ss");

            string toTime = "";

string filename = Path.GetFileName(callReportFileUpload.PostedFile.FileName);
                    Stream str = callReportFileUpload.PostedFile.InputStream;
                    BinaryReader br = new BinaryReader(str);
                    Byte[] bytes = br.ReadBytes((int)str.Length);
                    string ext = Path.GetExtension(filename);
                    string contentType = String.Empty;

                    //Set the contenttype based on File Extension
                    switch (ext)
                    {
                        case ".doc":
                            contentType = "application/vnd.ms-word";
                            break;
                        case ".docx":
                            contentType = "application/vnd.ms-word";
                            break;
                        case ".xls":
                            contentType = "application/vnd.ms-excel";
                            break;
                        case ".xlsx":
                            contentType = "application/vnd.ms-excel";
                            break;
                        case ".jpg":
                            contentType = "image/jpg";
                            break;
                        case ".png":
                            contentType = "image/png";
                            break;
                        case ".gif":
                            contentType = "image/gif";
                            break;
                        case ".pdf":
                            contentType = "application/pdf";
                            break;
                    }




                    SqlConnection con = null; ;
                    SqlCommand cmd;
                    SqlDataReader dr;
                    try
                    {

                        con = new SqlConnection(ConfigurationManager.ConnectionStrings["CRMConnection"].ConnectionString);
                        int issueid=Convert.ToInt32(issueList.SelectedValue);
                        string contact=txtContactNo.Text;
                        string email=txtEmail.Text;
                        //string qry = "insert into tblComplaintMaster values('" + complNo + "','" + finyr + "',Convert(Date,'" + complDt + "'),'" + complNature + "','" + product + "','" + srno + "',"+issueid+",'" + descr + "','" + assignto + "','" + reqBy + "','"+contact+"','"+email+"','" + loc + "','" + priority + "','" + txtStatus.Text + "','" + fromTime + "','" + toTime + "',null,null,null,'"+bytes.ToString()+"','"+contentType+"')";
                        //string qry = "insert into tblComplaintMaster(complNo,FinYr,complDate,complNature,product,prodct_srno,issue_id ,descr,assignto,reqby,contact_no,email,reqLoc,priority,status,fromtime,totime,ReassignTo,ReassignDT,reason,call_report,content_type) values (@complNo,@FinYr,@complDate,@complNature,@product,@prodct_srno,@issue_id ,@descr,@assignto,@reqby,@contact_no,@email,@reqLoc,@priority,@status,@fromtime,@totime,@ReassignTo,@ReassignDT,@reason,@call_report,@content_type)";                            
                        cmd = new SqlCommand("insert into tblComplaintMaster(complNo,FinYr,complDate,complNature,product,prodct_srno,issue_id ,descr,assignto,reqby,contact_no,email,reqLoc,priority,status,fromtime,totime,ReassignTo,ReassignDT,reason,call_report,content_type) values (@complNo,@FinYr,@complDate,@complNature,@product,@prodct_srno,@issue_id ,@descr,@assignto,@reqby,@contact_no,@email,@reqLoc,@priority,@status,@fromtime,@totime,@ReassignTo,@ReassignDT,@reason,@call_report,@content_type)");
                        cmd.Parameters.AddWithValue("@complNo", complNo);
                        cmd.Parameters.AddWithValue("@FinYr", finyr);
                        cmd.Parameters.AddWithValue("@complDate", SqlDbType.Date).Value = txtComplaintDt.Text;
                        cmd.Parameters.AddWithValue("@complNature", complNature);
                        cmd.Parameters.AddWithValue("@product", product);
                        cmd.Parameters.AddWithValue("@prodct_srno", srno);
                        cmd.Parameters.AddWithValue("@issue_id", issueid);
                        cmd.Parameters.AddWithValue("@descr", descr);
                        cmd.Parameters.AddWithValue("@assignto", assignto);
                        cmd.Parameters.AddWithValue("@reqby", reqBy);
                        cmd.Parameters.AddWithValue("@contact_no", contact);
                        cmd.Parameters.AddWithValue("@email", email);
                        cmd.Parameters.AddWithValue("@reqLoc", loc);
                        cmd.Parameters.AddWithValue("@priority", priority);
                        cmd.Parameters.AddWithValue("@status", txtStatus.Text);
                        cmd.Parameters.AddWithValue("@fromtime", fromTime);
                        cmd.Parameters.AddWithValue("@totime", toTime);
                        cmd.Parameters.AddWithValue("@ReassignTo", null);
                        cmd.Parameters.AddWithValue("@ReassignDT", null);
                        cmd.Parameters.AddWithValue("@reason", null);
                        cmd.Parameters.AddWithValue("@call_report", bytes);
                        cmd.Parameters.AddWithValue("@content_type", contentType);
                        

                        //cmd.Parameters.AddWithValue("@report", SqlDbType.VarBinary).Value = bytes;
                        //cmd.Parameters.AddWithValue("@call_report", bytes);
                       // cmd = new SqlCommand(qry);
                        Response.Write(cmd.CommandText);
                        con.Open();
                        cmd.Connection = con;

                        int result = cmd.ExecuteNonQuery();

                        if (result > 0)
                        {
                            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Message", "alert('Record Save')", true);
                            ResetAll();
                        }
                        else
                        {
                            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Message", "alert('did not Save')", true);
                        }

problem is that it doesn't fire any error or exception still entry also doesn't save.
Please help me.

Posted 29-Mar-18 19:06pm

SujataJK

Updated 4-Apr-18 12:58pm

v2

Add a Solution

Comments

Wendelius 30-Mar-18 1:18am

Is the row inserted without value in call_report or does the ExecuteNonQuery return 0?

SujataJK 30-Mar-18 1:24am

Response.Write("Result=" + result); it doesn't show any output.I am not understand where is the problem.

Wendelius 30-Mar-18 1:34am

I can't find such code in your post.

SujataJK 30-Mar-18 1:38am

yes,just i add this line in y application but it doesn't show any output.

Wendelius 30-Mar-18 1:45am

It's hard to tell where the problem is without seeing all relevant code. Please use the "Improve question" widget to modify your question to show the complete code excerpt.

2 solutions

Solution 1

Unless an exception is thrown, result will either be 1 or 0. I suspect there's something wrong with your insert query.

Are you sure there's even data in the bytes array?

Is the record being inserted at all?

Is an exception being throw?

Posted 29-Mar-18 23:09pm

#realJSOP

Comments

SujataJK 30-Mar-18 6:55am

@john Simmons

In my application when i am checking whether file is attached or not using this

if (callReportFileUpload.HasFile)
{
Response.Write("has file");
}
and to checking data in bytes array using Response.Write as follows

string filename = Path.GetFileName(callReportFileUpload.PostedFile.FileName);
Stream str = callReportFileUpload.PostedFile.InputStream;
BinaryReader br = new BinaryReader(str);
Byte[] bytes = br.ReadBytes((int)str.Length);
Response.Write(bytes.Length);

and your 3 rd point record being inserted or not ? I am checking this is using
int result = cmd.ExecuteNonQuery();
Response.Write("Result=" + result);
Then it doesn't gives any result either 0 or 1.
Ant your 4th point it doesn't gives any exception also

SujataJK 31-Mar-18 4:16am

one more thing when i insert null values for var binary field then its works fine
i.e
string qry = "insert into tblComplaintMaster values('" + complNo + "','" + finyr + "',Convert(Date,'" + complDt + "'),'" + complNature + "','" + product + "','" + srno + "'," + issueid + ",'" + descr + "','" + assignto + "','" + reqBy + "','" + contact + "','" + email + "','" + loc + "','" + priority + "','" + txtStatus.Text + "','" + fromTime + "','" + toTime + "',null,null,null,null,null)";
cmd = new SqlCommand(qry);
con.Open();
cmd.Connection = con;

int result = cmd.ExecuteNonQuery();
Response.Write("Result=" + result);
Then gives result=1 as output that means values inserted to database sucessfully

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

an0ther1 · Accepted Answer · 2018-04-04T13:00:00

First off, cmd.ExecuteNoQuery() returns an integer that identifies how many records were affected by the cmd, if the insert does not happen then the expected result would be 0.

ASP.Net limits the size of a file that can be uploaded via the maxRequestLength Configuration attribute - refer; HttpRuntimeSection.MaxRequestLength Property (System.Web.Configuration)[^]
The default is 4MB but can be overridden via your web.config

Additionally, there is no need to read the file into a stream if you are using a FileUpload control.

I use the below which works successfully - shortened for brevity

C#

if(callReportFileUpload.HasFile)
{
    // read the bytes from the file
    byte[] bytFile = callReportFileUpload.FileBytes;
    
    // when adding your parameter ensure you set the datatype!!
    SqlParameter paramFile = new SqlParameter("@call_report", SqlDbType.VarBinary);
    // set direction and value
    paramFile.Direction = ParameterDirection.Input;
    paramFile.Value = bytFile;
    cmd.Parameters.Add(paramFile);
}

Hope this helps