Henrik Mikkelsen

Henrik Mikkelsen

  • 2.1k
  • 3
  • 5.3k

Gridview updating

Jan 25 2013 4:42 AM
Heey all

i have a gridview where there is a FileUpload, and i trying to get it op update my database

I have tryed almost every thing

my FrontEnd code:

<asp:GridView ID="gvEditProducts" runat="server" AutoGenerateColumns="False" AllowPaging="True" 
        DataSourceID="ObjEditProduct" onrowediting="gvEditProducts_RowEditing" 
        onrowupdating="gvEditProducts_RowUpdating">
        <Columns>
            <asp:TemplateField HeaderText="ProductID" InsertVisible="False" 
                SortExpression="ProductID">
                <EditItemTemplate>
                    <asp:Label ID="ProductID" runat="server" Text='<%# Eval("ProductID") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblProductID" runat="server" Text='<%# Bind("ProductID") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="ProductName" SortExpression="ProductName">
                <EditItemTemplate>
                    <asp:TextBox ID="txtProductName" runat="server" Text='<%# Bind("ProductName") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblProductName" runat="server" Text='<%# Bind("ProductName") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="CategoryID" SortExpression="CategoryID">
                <EditItemTemplate>
                    <%--<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("CategoryID") %>'></asp:TextBox>--%>
                    <asp:DropDownList ID="ddlCategory" runat="server" AutoPostBack="True" DataSourceID="ObjCategory"
                        DataTextField="CategoryName" DataValueField="CategoryID" SelectedValue='<%# Bind("CategoryID") %>'>
                    </asp:DropDownList>
                    <asp:ObjectDataSource ID="ObjCategory" runat="server" OldValuesParameterFormatString="original_{0}"
                        SelectMethod="GetCategories" TypeName="DataSet1TableAdapters.FL_CategoryTableAdapter">
                    </asp:ObjectDataSource>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="CategoryID" runat="server" Text='<%# Bind("CategoryID") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="ProductDescription" 
                SortExpression="ProductDescription">
                <EditItemTemplate>
                    <asp:TextBox ID="txtProductDescription" runat="server" 
                        Text='<%# Bind("ProductDescription") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblProductDescription" runat="server" Text='<%# Bind("ProductDescription") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="ProductPrice" SortExpression="ProductPrice">
                <EditItemTemplate>
                    <asp:TextBox ID="txtProductPrice" runat="server" Text='<%# Bind("ProductPrice") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblProductPrice" runat="server" Text='<%# Bind("ProductPrice") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="ProductAmount" SortExpression="ProductAmount">
                <EditItemTemplate>
                    <asp:TextBox ID="txtProductAmount" runat="server" Text='<%# Bind("ProductAmount") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblProductAmount" runat="server" Text='<%# Bind("ProductAmount") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="ProductCreatetDate" 
                SortExpression="ProductCreatetDate">
                <EditItemTemplate>
                    <asp:Label ID="ProductCreatetDate" runat="server" Text='<%# Eval("ProductCreatetDate") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblProductCreatetDate" runat="server" Text='<%# Bind("ProductCreatetDate") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="ProductImage" SortExpression="ProductImage">
                <EditItemTemplate>
                    <%--<asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("ProductImage") %>'></asp:TextBox>--%>
                    <asp:FileUpload ID="fuProductImage" runat="server" />
                </EditItemTemplate>
                <ItemTemplate>
                    <%--<asp:Label ID="Label6" runat="server" Text='<%# Bind("ProductImage") %>'></asp:Label>--%>
                    <asp:Image ID="Image1" runat="server" ImageUrl='<%# Bind("ProductImage") %>' Width="50px" Height="50px" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField ShowHeader="False">
                <EditItemTemplate>
                    <asp:LinkButton ID="btnUpdate" runat="server" CausesValidation="True" 
                        CommandName="Update" Text="Opdater"></asp:LinkButton>
                    &nbsp;<asp:LinkButton ID="btnCancel" runat="server" CausesValidation="False" 
                        CommandName="Cancel" Text="Fortryd"></asp:LinkButton>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:LinkButton ID="btnEdit" runat="server" CausesValidation="False" 
                        CommandName="Edit" Text="Redigere"></asp:LinkButton>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        
    </asp:GridView>
    <asp:ObjectDataSource ID="ObjEditProduct" runat="server" 
        OldValuesParameterFormatString="original_{0}" SelectMethod="GetProducts" 
        TypeName="DataSet1TableAdapters.FL_ProductsTableAdapter" 
        UpdateMethod="UpdateProducts">
        <UpdateParameters>
            <asp:Parameter Name="ProductName" Type="String" />
            <asp:Parameter Name="CategoryID" Type="Int32" />
            <asp:Parameter Name="ProductDescription" Type="String" />
            <asp:Parameter Name="ProductPrice" Type="Decimal" />
            <asp:Parameter Name="ProductAmount" Type="Int32" />
            <asp:Parameter Name="ProductCreatetDate" Type="String" />
            <asp:Parameter Name="ProductImage" Type="String" />
            <asp:Parameter Name="Original_ProductID" Type="Int32" />
        </UpdateParameters>
    </asp:ObjectDataSource>
    <asp:Label ID="lblOutput" runat="server" Text=""></asp:Label>
    <asp:ImageButton ID="imgPicture" runat="server" Visible="False" />

my BackEnd code:

protected void gvEditProducts_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox txtProductName = (TextBox)gvEditProducts.Rows[e.RowIndex].Cells[1].FindControl("txtProductName");
        DropDownList ddlCategory = (DropDownList)gvEditProducts.Rows[e.RowIndex].Cells[2].FindControl("ddlCategory");
        TextBox txtProductDescription = (TextBox)gvEditProducts.Rows[e.RowIndex].Cells[3].FindControl("txtProductDescription");
        TextBox txtProductPrice = (TextBox)gvEditProducts.Rows[e.RowIndex].Cells[4].FindControl("txtProductPrice");
        TextBox txtProductAmount = (TextBox)gvEditProducts.Rows[e.RowIndex].Cells[5].FindControl("txtProductAmount");
        FileUpload fuProductImage = (FileUpload)gvEditProducts.Rows[e.RowIndex].Cells[7].FindControl("fuProductImage");
        // Initialize variables
        string sSavePath;
        string sThumbExtension;
        int intThumbWidth;
        int intThumbHeight;

        // Set constant values
        sSavePath = "~/ProductImages/";
        sThumbExtension = "_thumb";
        intThumbWidth = 100;
        intThumbHeight = 80;

        // If file field isn't empty
        if (fuProductImage.PostedFile != null)
        {
            // Check file size (mustn't be 0)
            HttpPostedFile myFile = fuProductImage.PostedFile;
            int nFileLen = myFile.ContentLength;
            if (nFileLen == 0)
            {
                lblOutput.Text = "No file was uploaded.";
                return;
            }

            // Check file extension (must be JPG)
            if (System.IO.Path.GetExtension(myFile.FileName).ToLower() != ".jpg")
            {
                lblOutput.Text = "The file must have an extension of JPG";
                return;
            }

            // Read file into a data stream
            byte[] myData = new Byte[nFileLen];
            myFile.InputStream.Read(myData, 0, nFileLen);

            // Make sure a duplicate file doesn't exist.  If it does, keep on appending an 
            // incremental numeric until it is unique
            string sFilename = System.IO.Path.GetFileName(myFile.FileName);
            int file_append = 0;
            while (System.IO.File.Exists(Server.MapPath(sSavePath + sFilename)))
            {
                file_append++;
                sFilename = System.IO.Path.GetFileNameWithoutExtension(myFile.FileName)
                                 + file_append.ToString() + ".jpg";
            }

            // Save the stream to disk
            System.IO.FileStream newFile
                    = new System.IO.FileStream(Server.MapPath(sSavePath + sFilename),
                                               System.IO.FileMode.Create);
            newFile.Write(myData, 0, myData.Length);
            newFile.Close();

            // Check whether the file is really a JPEG by opening it
            System.Drawing.Image.GetThumbnailImageAbort myCallBack =
                           new System.Drawing.Image.GetThumbnailImageAbort(ThumbnailCallback);
            Bitmap myBitmap;
            try
            {
                myBitmap = new Bitmap(Server.MapPath(sSavePath + sFilename));

                // If jpg file is a jpeg, create a thumbnail filename that is unique.
                file_append = 0;
                string sThumbFile = System.IO.Path.GetFileNameWithoutExtension(myFile.FileName)
                                                         + sThumbExtension + ".jpg";
                while (System.IO.File.Exists(Server.MapPath(sSavePath + sThumbFile)))
                {
                    file_append++;
                    sThumbFile = System.IO.Path.GetFileNameWithoutExtension(myFile.FileName) +
                                   file_append.ToString() + sThumbExtension + ".jpg";
                }

                // Save thumbnail and output it onto the webpage
                System.Drawing.Image myThumbnail
                        = myBitmap.GetThumbnailImage(intThumbWidth,
                                                     intThumbHeight, myCallBack, IntPtr.Zero);
                myThumbnail.Save(Server.MapPath(sSavePath + sThumbFile));
                imgPicture.ImageUrl = sSavePath + sFilename;

                // Displaying success information
                lblOutput.Text = "File uploaded successfully!";

                // Destroy objects
                myThumbnail.Dispose();
                myBitmap.Dispose();
            }
            catch (ArgumentException errArgument)
            {
                // The file wasn't a valid jpg file
                lblOutput.Text = "The file wasn't a valid jpg file.";
                System.IO.File.Delete(Server.MapPath(sSavePath + sFilename));
            }

            string connection = WebConfigurationManager.ConnectionStrings["FarmorLopperConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connection);

            SqlCommand comm;
            comm = new SqlCommand("UPDATE FL_Products SET ProductName = @ProductName,CategoryID = @CategoryID, ProductDescription = @ProductDescription, ProductPrice = @ProductPrice, ProductAmount = @ProductAmount, ProductImage = @ProductImage)", conn);
//ProductID = @ProductID
            //comm.Parameters.Add("@ProductID", SqlDbType.Int).Value = Convert.ToInt32(gvEditProducts.DataKeys[e.RowIndex].Values[1].ToString());
            comm.Parameters.Add("@ProductName", System.Data.SqlDbType.NVarChar).Value = txtProductName.Text;
            comm.Parameters.Add("@ProductDescription", SqlDbType.NVarChar).Value = txtProductDescription.Text;
            comm.Parameters.Add("@ProductPrice", SqlDbType.Money).Value = Convert.ToDouble(txtProductPrice.Text);
            comm.Parameters.Add("@ProductAmount", SqlDbType.Int).Value = Convert.ToInt32(txtProductAmount.Text);
            comm.Parameters.Add("@CategoryID", SqlDbType.Int).Value = Convert.ToInt32(ddlCategory.SelectedValue);
            comm.Parameters.AddWithValue("@ProductImage", (imgPicture.ImageUrl == null ? (object)DBNull.Value : (object)imgPicture.ImageUrl));

            //comm.ExecuteNonQuery();
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            //catch (Exception err)
            //{
            //    lblOutput.Text = err.ToString();
            //}
            finally
            {
                conn.Close();
            }

and i get this error: (the error place is highlightet with Red)

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ')'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Admin_Products_TestEditProducts.gvEditProducts_RowUpdating(Object sender, GridViewUpdateEventArgs e) in c:\Online Websites\Hoved\FarmorsLopper\Admin\Products\TestEditProducts.aspx.cs:line 278 ClientConnectionId:8e646372-3ffb-449b-8555-e6122e279b0f

Answers (1)