[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
10/11/2008 at 04:33PM PDT, ID: 23807074
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.2

C# - ASP.Net: Uploading File Path to Database, Error Returned on Gridview Data bind

Asked by AJ0424 in Programming for ASP.NET, Microsoft Visual C#.Net

Tags: C#, asp.net, SQL 2005, InnerException {"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."} System.Exception {System.Data.ConstraintException}

Hello,
I am brand new to development of any kind so please forgive me if this is a bit arduous.  I will put in code samples at the end.

Thanks,
AJ

Technologies:
C#, ASP.Net 2.0, SQL Server 2005

What I am doing (or trying to do):
Create a web page where a client can see a list of files, click on one and download it.  I am using the gridview control to display this info.  Currently it is display only...I haven't yet figured out how to make the file name a link that, in turn downloads the file.

There is also a file upload page where they can browse their computer, select a file and upload it.

I am using the FileUploader control to upload the file to a folder and return the file path (with other info) to the database table.

Problems:

1. The file uploads correctly into my folder and the file path (and additional info) is successfully inserted into my database.  The problem, however, comes in when I try to view the files on the screen with my gridview.  When I just had text for my file name, it worked.  If I have the file path, it breaks and throws this exception:

InnerException      {"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."}      System.Exception {System.Data.ConstraintException}

I have checked my database tables several times and I don't think I am doing any such things.  My table essentially is:

ID     int  PK not null,
FileName    nvarchar(1000) not null,
Description nvarchar(255) null,
Upload Date datetime not null

My first thought was that my datatype was incorrect in the database but the table accepts the path in the FileName column just fine...the problem is in the gridview.  Since I am brand new, I don't have any templating or custom code on the gridview and I thought it would just display whatever I was returning from that column.

The above is my main problem but I will throw in a couple more questions while I am at it.

The otherhalf of this is to make the file name a link that, when clicked on, downloads the file to the users computer.  I want this to be a save as box, not open the file in the browser since a lot of these files will be non-traditional file extensions.  The two things I am currently stuck on are:

1. How to make the FileName column display only the name and extension of the file instead of the whole path (example: Test.doc instead of C:\importantinfo\Test.doc)

2. How to make the FileName column a link that downloads the file.

If anyone has any helpful pointers on these two issues as well, I would be really grateful.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:

***Page Source:***
 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ViewClientFilesbyDateRange.aspx.cs" Inherits="FileTransfer2.ViewClientFilesbyDateRange" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>ViewFiles</title>
</head>
<body style="padding-right: 10px; padding-left: 10px; padding-bottom: 10px; padding-top: 10px; background-color: #eff3fb">
    <form id="form1" runat="server">
    <div style="padding-right: 10px; padding-left: 10px; padding-bottom: 10px; padding-top: 10px; background-color: #eff3fb">
        <asp:label id="lblSearchHeader" runat="server" font-bold="True" font-names="Calibri"
            font-size="Medium" text="Search Records by Date Range"></asp:label><br />
        <asp:textbox id="txtStartDate" runat="server" font-names="Calibri" font-size="Small"
            forecolor="Black">
        </asp:textbox>&nbsp;
        <asp:textbox id="txtEndDate" runat="server" font-names="Calibri" font-size="Small"
            forecolor="Black">
        </asp:textbox>
        &nbsp;&nbsp;
        <asp:button id="btnSubmitDates" runat="server" font-bold="True" font-names="Calibri"
            onclick="btnSubmitDates_Click" text="Submit" /><br />
        <asp:label id="lblEnterDates" runat="server" font-bold="True" font-names="Calibri"
            font-size="Small" text="Enter Start and End Dates (mm/dd/yyyy):"></asp:label><br />
        <asp:regularexpressionvalidator id="valStartDate" runat="server" controltovalidate="txtStartDate"
            display="Dynamic" errormessage="Start Date Formatted Incorrectly (mm/dd/yyyy)"
            validationexpression="\d{2}/\d{2}/\d{4}" font-names="Calibri" font-size="Smaller">
        </asp:regularexpressionvalidator>
        <br />
        <asp:regularexpressionvalidator id="valEndDate" runat="server" controltovalidate="txtEndDate"
            errormessage="End Date Formatted Incorrectly (mm/dd/yyyy)" validationexpression="\d{2}/\d{2}/\d{4}" font-names="Calibri" font-size="Smaller">
        </asp:regularexpressionvalidator><br />
        <br />
        <asp:gridview id="GridView1" runat="server" allowpaging="True" allowsorting="True"
            autogeneratecolumns="False" cellpadding="4" datasourceid="ObjectDataSource1"
            forecolor="#333333" gridlines="None" height="241px" style="font-size: smaller;
            font-family: Calibri, Arial" width="713px">
            <footerstyle backcolor="#507CD1" font-bold="True" forecolor="White" />
            <rowstyle backcolor="#EFF3FB" horizontalalign="Center" />
            <columns>
<asp:BoundField DataField="File Name" HeaderText="File Name" SortExpression="File Name"></asp:BoundField>
<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description"></asp:BoundField>
<asp:BoundField DataField="ClientFile" HeaderText="ClientFile" SortExpression="ClientFile"></asp:BoundField>
<asp:BoundField DataField="Upload Date" HeaderText="Upload Date" SortExpression="Upload Date"></asp:BoundField>
<asp:CheckBoxField DataField="eSign" HeaderText="eSign" SortExpression="eSign"></asp:CheckBoxField>
</columns>
            <pagerstyle backcolor="#2461BF" forecolor="White" horizontalalign="Center" />
            <selectedrowstyle backcolor="#D1DDF1" font-bold="True" forecolor="#333333" />
            <headerstyle backcolor="#507CD1" font-bold="True" font-size="Medium" forecolor="White"
                horizontalalign="Center" />
            <editrowstyle backcolor="#2461BF" horizontalalign="Center" />
            <alternatingrowstyle backcolor="White" />
        </asp:gridview></div>
        <asp:objectdatasource id="ObjectDataSource1" runat="server" oldvaluesparameterformatstring="original_{0}"
            selectmethod="GetFilesbyClientDateRange" typename="FileTransfer2.DataSets.ClientFilesbyDateRangeTableAdapters.getFilesbyClientDateRangeTableAdapter">
            <selectparameters>
<asp:Parameter DefaultValue="" Name="startDate" Type="DateTime"></asp:Parameter>
<asp:Parameter DefaultValue="" Name="endDate" Type="DateTime"></asp:Parameter>
<asp:Parameter Name="UserName" Type="String"></asp:Parameter>
</selectparameters>
        </asp:objectdatasource><br />
        <asp:button id="btnUploadFile" runat="server" font-bold="True" font-names="Calibri"
            onclick="btnUploadFile_Click" text="Upload New File" /><br />
        <br />
        <br />
    </form>
</body>
</html>
 
 
***CODE BEHIND***
 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
namespace FileTransfer2
{
    public partial class ViewClientFilesbyDateRange : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            ObjectDataSource1.SelectParameters["UserName"].DefaultValue = this.User.Identity.Name;
            ObjectDataSource1.SelectParameters["startDate"].ConvertEmptyStringToNull = true;
            ObjectDataSource1.SelectParameters["endDate"].ConvertEmptyStringToNull = true;
 
            if (!Page.IsPostBack)
            {
                ObjectDataSource1.SelectParameters["startDate"].DefaultValue = string.Empty;
                ObjectDataSource1.SelectParameters["endDate"].DefaultValue = string.Empty;
                GridView1.DataBind(); //This is where my exception is generated
            }
 
        }
 
        protected void btnSubmitDates_Click(object sender, EventArgs e)
        {
            ObjectDataSource1.SelectParameters["startDate"].DefaultValue = this.txtStartDate.Text.Trim();
            ObjectDataSource1.SelectParameters["endDate"].DefaultValue = this.txtEndDate.Text.Trim();
            GridView1.DataBind();
        }
 
        protected void btnUploadFile_Click(object sender, EventArgs e)
        {
            Response.Redirect("FileUpload.aspx");
        }
    }
}
 
 
***File Upload code in case it is needed***
 
SOURCE CODE:
 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FileUpload.aspx.cs" Inherits="FileTransfer2.FileUpload" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="padding-right: 10px; padding-left: 10px; padding-bottom: 10px; padding-top: 10px; font-family: Calibri, Arial; background-color: #eff3fb">
        <asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:FileTransferConnectionStringFileUpload %>"
            selectcommand="SELECT [ID], [FileName], [Description], [ClientFile], [UploadDate], [eSign] FROM [ClientFiles]">
        </asp:sqldatasource><br />
        <asp:linkbutton id="lbtnReturntoHome" runat="server" font-size="Smaller" onclick="lbtnReturntoHome_Click" font-names="Calibri">Return to Home Page</asp:linkbutton><br />
        <br />
        <asp:label id="Label1" runat="server" font-bold="True" font-size="X-Large" text="Select and Upload File">
        </asp:label><br />
        <br />
        &nbsp;<asp:fileupload id="FileUpload1" runat="server" font-names="Calibri"></asp:fileupload><br />
        <asp:label id="lblError" runat="server" font-bold="True" font-names="Calibri" font-size="Smaller"
            forecolor="Red" height="21px" width="291px"></asp:label><br />
        <asp:label id="lblDescription" runat="server" text="Description:" font-bold="True" font-size="Small"></asp:label><br />
        &nbsp;<asp:textbox id="txtDescription" runat="server" font-names="Calibri" height="97px" maxlength="255" rows="5" textmode="MultiLine" width="280px"></asp:textbox>
        <br />
        <asp:label id="Label2" runat="server" font-names="Calibri" font-size="Small" text="(Description max 255 characters.)">
        </asp:label><br />
        <br />
        <asp:button id="btnSubmit" runat="server" onclick="btnSubmit_Click" text="Submit" font-names="Calibri" font-overline="False" font-size="Medium" />
        <br />
        <br />
        <asp:label id="lblResults" runat="server" height="21px" width="291px" font-names="Calibri" font-size="Small" forecolor="DarkGreen"></asp:label><br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
    </div>
    </form>
</body>
</html>
 
 
CODE BEHIND:
 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;  
 
 
namespace FileTransfer2
{
    
    public partial class FileUpload : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }
 
        protected void lbtnReturntoHome_Click(object sender, EventArgs e)
        {
            Response.Redirect("default.aspx");
        }
 
        
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            
            
 
            if (FileUpload1.HasFile)
                try
                {
 
                    string fn = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName); 
                    string SaveLocation = Server.MapPath("UploadedFiles") + "\\" + fn; 
                    lblResults.Text = "File Successfully Uploaded: <br>" + "File name: " +
                        FileUpload1.PostedFile.FileName + "<br>" +
                        FileUpload1.PostedFile.ContentLength + " kb<br>" +
                        "Content type: " +
                        FileUpload1.PostedFile.ContentType;
 
 
                    ClientFileRepository repository = new ClientFileRepository();
                    repository.InsertNewFile(SaveLocation, txtDescription.Text);
                    
                    lblError.Text = "";
 
                    
                }
                
                catch (Exception ex) //this should catch if jibberish is entered but it doesn't
                {
                    //lblError.Text = "Error: File was not found.";
                    Response.Write("Error: " + ex.Message);
                }
            else
            {
                txtDescription.Text = "";
                lblResults.Text = "";
                lblError.Text = "You have not specified a file.";
            }
 
 
                        
        }
    }
}
 
 
Stored Procedure class:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
namespace FileTransfer2
{
    public class ClientFileRepository
    {
        public void InsertNewFile(string fileName, string description)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["FileTransferConnectionString"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand("InsertNewFile", conn)) //stored proc name
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FileName", fileName); // replace w/ FileUpload1.PostedFile.FileName (FileUpload.aspx) don't know how //did it in code behind
                    cmd.Parameters.AddWithValue("@Description", description);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
[+][-]10/11/08 08:20 PM, ID: 22696136

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Programming for ASP.NET, Microsoft Visual C#.Net
Tags: C#, asp.net, SQL 2005, InnerException {&quot;Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.&quot;} System.Exception {System.Data.ConstraintException}
Sign Up Now!
Solution Provided By: ragi0017
Participating Experts: 1
Solution Grade: A
 
 
[+][-]10/12/08 09:35 AM, ID: 22697965

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-91 / EE_QW_2_20070628