Need help with @@IDENTITY in sqlDataSource

So I need some help.

I have made a site for my wifes grandpa so he can sell his watercolor paintings. He wanted a way to log in and upload the paintings himself. So I created a form where he will enter in the name and price and size along with a picture. I am using SqlDataSource to do this, but I am not sure how to do this image. I want to save the image in a folder on the server, then have a path to that image in SQL, I know about the @@IDENTITY and thought that I could possibly use it for this. But I cannot get it to work. I don't know how to save the path into sql. I have looked around at other forums and just cannot seem to get it to work as I need it. I am so lost. I need the image to be linked with the other data as it is entered.

Please can someone help me?

Here is my aspx page:

</asp:Content>

<asp:Content ContentPlaceHolderID="ContentPlaceHolder1" runat="server">

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="id" DataSourceID="_watercolorDataSource"
EmptyDataText="There are no data records to display." CssClass="paintingTable">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="id" HeaderText="id" ReadOnly="True"
SortExpression="id" Visible="False"/>
<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" HeaderStyle-CssClass="headerStyle"/>
<asp:BoundField DataField="size" HeaderText="size" SortExpression="size" HeaderStyle-CssClass="headerStyle" />
<asp:BoundField DataField="size2" HeaderText="size2" SortExpression="size2"
Visible="False" />
<asp:BoundField DataField="size3" HeaderText="size3" SortExpression="size3"
Visible="False" />
<asp:BoundField DataField="price" HeaderText="price" SortExpression="price" HeaderStyle-CssClass="headerStyle" />
<asp:BoundField DataField="price2" HeaderText="price2"
SortExpression="price2" Visible="False" />
<asp:BoundField DataField="price3" HeaderText="price3"
SortExpression="price3" Visible="False" />
<asp:BoundField DataField="description" HeaderText="description"
SortExpression="description" HeaderStyle-CssClass="headerStyle" HeaderStyle-Width="250px"/>
<asp:BoundField DataField="type" HeaderText="type" SortExpression="type" HeaderStyle-CssClass="headerStyle" />
<asp:BoundField DataField="pic" HeaderText="pic" SortExpression="pic" HeaderStyle-CssClass="headerStyle" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="_watercolorDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"
DeleteCommand="DELETE FROM [watercolors] WHERE [id] = @id"
InsertCommand="INSERT INTO [watercolors] ([name], [size], [size2], [size3], [price], [price2], [price3], [description], [type]) VALUES (@name, @size, @size2, @size3, @price, @price2, @price3, @description, @type); SET @Identity = @@Identity;"
ProviderName="<%$ ConnectionStrings:LocalSqlServer.ProviderName %>"
SelectCommand="SELECT [id], [name], [size], [size2], [size3], [price], [price2], [price3], [description], [type], [pic] FROM [watercolors]"
UpdateCommand="UPDATE [watercolors] SET [name] = @name, [size] = @size, [size2] = @size2, [size3] = @size3, [price] = @price, [price2] = @price2, [price3] = @price3, [description] = @description, [type] = @type, [pic] = @pic WHERE [id] = @id">
<DeleteParameters>
<asparameter Name="id" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asparameter Name="name" Type="String" />
<asparameter Name="size" Type="String" />
<asparameter Name="size2" Type="String" />
<asparameter Name="size3" Type="String" />
<asparameter Name="price" Type="String" />
<asparameter Name="price2" Type="String" />
<asparameter Name="price3" Type="String" />
<asparameter Name="description" Type="String" />
<asparameter Name="type" Type="String" />
<asparameter Name="pic" Type="String" />
<asparameter Direction="Output" Name="Identity" Size="4" Type="Int16" />
</InsertParameters>
<UpdateParameters>
<asparameter Name="name" Type="String" />
<asparameter Name="size" Type="String" />
<asparameter Name="size2" Type="String" />
<asparameter Name="size3" Type="String" />
<asparameter Name="price" Type="String" />
<asparameter Name="price2" Type="String" />
<asparameter Name="price3" Type="String" />
<asparameter Name="description" Type="String" />
<asparameter Name="type" Type="String" />
<asparameter Name="pic" Type="String" />
<asparameter Name="id" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

</div>
<br />
<table class="admin">
<tr>
<td>painting name:</td>
<td>
<asp:TextBox ID="name" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td>size:</td>
<td>
<asp:TextBox ID="size" runat="server" Width="200px"></asp:TextBox></td>
</tr>
<tr>
<td>price:</td>
<td>
<asp:TextBox ID="price" runat="server" Width="200px" CssClass="currency"></asp:TextBox></td>
</tr>
<tr>
<td>original or print:</td>
<td>
<aspropDownList ID="DropDownList1" runat="server" Width="200px">
<asp:ListItem Text="select" Value="select" />
<asp:ListItem Text="original" Value="original" />
<asp:ListItem Text="print" Value="print" />
</aspropDownList>
</td>
<td></td>
<td align="left">

</td>
</tr>
<tr>
<td valign="top">description:</td>
<td>
<asp:TextBox ID="description" runat="server" Width="200px" Height="200px"
Wrap="true"></asp:TextBox></td>
<td></td>
<td align="left" valign="top">

</td>
</tr>
<tr>
<td>pic:</td>
<td colspan="2" align="left">
<asp:FileUpload ID="fileUpload" runat="server"/>
</td>
<td align="right">
<asp:Label ID="uploadLabel" runat="server"/>

</td>
</tr>
<tr>
<td></td>
<td align="left">
<asp:Button ID="submit" runat="server" Text="submit" CssClass="btnDark"
Width="55px" onclick="submit_Click"/>
</td>
</tr>
</table>
</asp:Content>

---------------------------------- code behind ------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.HtmlControls;
using System.IO;

public partial class admin : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void submit_Click(object sender, EventArgs e)
{
if (fileUpload.HasFile)
{
try
{
fileUpload.SaveAs(Server.MapPath("../watercolorImages/" + fileUpload.FileName));
}
catch (Exception ex)
{
uploadLabel.Text = "ERROR: " + ex.Message.ToString();
}
}
else
{
uploadLabel.Text = "You have not specified a file.";
}
string paintingName = name.Text;
string paintingSize = size.Text;
string paintingPrice = price.Text;
string paintingType = DropDownList1.Text;
string paintingDescription = description.Text;
_watercolorDataSource.InsertParameters["name"].DefaultValue = paintingName;
_watercolorDataSource.InsertParameters["size"].DefaultValue = paintingSize;
_watercolorDataSource.InsertParameters["size2"].DefaultValue = "";
_watercolorDataSource.InsertParameters["size3"].DefaultValue = "";
_watercolorDataSource.InsertParameters["price"].DefaultValue = paintingPrice;
_watercolorDataSource.InsertParameters["price2"].DefaultValue = "";
_watercolorDataSource.InsertParameters["price3"].DefaultValue = "";
_watercolorDataSource.InsertParameters["description"].DefaultValue = paintingDescription;
_watercolorDataSource.InsertParameters["type"].DefaultValue = paintingType;
_watercolorDataSource.Insert();
}
protected void _watercolorDataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
string sID = e.Command.Parameters["@Identity"].Value.ToString();

uploadLabel.Text = sID;
}
private void WriteToFile(string strPath, ref byte[] Buffer)
{
FileStream newFile = new FileStream(strPath, FileMode.Create);
newFile.Write(Buffer, 0, Buffer.Length);
newFile.Close();
}

}

 

Deja vu..somehow I feel like we've been here before

If you need to get the last value of an Identity column for a newly inserted row in your [watercolors] table, how about writing a stored procedure to perform the insert and return the value of the Identity column as an output parameter on the stored proc? Just return it with SCOPE_IDENTITY() in the stored proc.

For that matter if you were to write separate stored procs for all of the CRUD operations for this table (Create, Read, Update, Delete), to my mind that's much cleaner maintainable code with better tier separation than all of that embedded declarative SQL in the markup.

 

...If you want an easy way out of this go to: http://www.codeplex.com/
There are several, free, code packages there to handle image uploads and tracking.
All the best,
Mark