GridView Update, With SqlDataSource UpdateCommand Set From Code-behind. (C#)
Mar 9, 2006
Hi all
I have a GridView on an aspx page, that is enabled for editing, deletion and sorting.
In the Page_Load event of the aspx page, i add a SqlDataSource to the page, and bind the source to the GridView.
When i click the update, or delete button, it makes a PostBack, but nothing is affected. I'm sure this has got something to do with the parameters.
First, i tried having the GridView.AutoGenerateColumns set to True. I have also tried adding the columns manually, but no affect here either.
The code for setting the commands, and adding the SqlDataSource to the page are as follows:
string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";
SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);
ds.ID = "RammeKategoriDS";
ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = @Kategoribeskrivelse WHERE (Kategorinavn = @Kategorinavn)";
ds.DeleteCommand = "DELETE FROM rammekategori WHERE (Kategorinavn = @Kategorinavn)";
Parameter Kategorinavn = new Parameter("Kategorinavn", TypeCode.String);
Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse", TypeCode.String);
ds.UpdateParameters.Add(Kategorinavn);
ds.UpdateParameters.Add(Kategoribeskrivelse);
ds.DeleteParameters.Add(Kategorinavn);
Page.Controls.Add(ds);
SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource;
if (m_SqlDataSource != null)
{
this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID;
}
As mentioned - no affect at all!
Thanks in advance - MartinHN
View 4 Replies
ADVERTISEMENT
Mar 16, 2007
I have a Gridview (edit enabled), connected to a SQLDataSource.
SQLDataSource populates the gridview from 1 SelectCommand.
figure1:
ColumnA | ColumnB | ColumnC
I have 2 SQL Server 2005 tables: Table1 and Table2
Now on my SQLDataSource's UpdateCommand, I want the value of ColumnB to go to Table1's ColumnB, and ColumnC to go to Table2's ColumnC.
How do I do this?
I understand I can't do this with 1 UPDATE-SET sql query. Maybe I can with stored procs or something, but im kinda noobish when it comes to this. How? Thanks
View 1 Replies
View Related
Mar 7, 2007
Hi,
say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2....
Does anybody know how can I alter programmatically these two sqldatasources in a gridview?
Thanks!!!
View 3 Replies
View Related
Jun 9, 2007
There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?
View 3 Replies
View Related
Jun 4, 2008
I have a gridview that is tied to a SQLDataSource control which is inside an update panel.
What I'm trying to accomplish is when the page is loaded the gridview is filled from the SQLDataSource. I have some textboxes also on the page so that the user can add new items to the gridview. The update panel trigger is tied to my code behind page through a button click which added the new data to the DB with an INSERT statement. Since the gridview was inside the update panel I thought when I placed the new data into the DB and the update panel caused the portion of the page that holds the gridview to refresh that the gridview would show the newly added data but it is not.
I am missing something but I do not know what. I tried adding a gridview1.databind() and SQLDataSource1.DataBind() but niether caused the update of the gridview as I had hoped.
Thanks for any of your help.
Ty
View 7 Replies
View Related
Aug 1, 2007
<!-- Trying
to create a GridView Survey form:
Hi [User]
Do you have these assets?
[Gridview]
Tag Number Response
123
[Yes ] [No] [Comments textbox]
234
[Yes ] [No] [Comments textbox]
Goal: The desire
is for the user to click Yes or No and have the database updated with the
user's name, date, response and any comments.
So far, I have
created SqlDataSource with Select and Update commands, created the gridview and
response buttons, setup the RowCommand and On_Updating Functions.
Problems:1) If I call the update() function for SqlDataSource1 and misuse the Defaultvalue parameters to run the sql, no update is posted to the database.
2) If I use the On_Update function to set the parameter values, I get "Data type mismatch in criteria expression." (Additionally, the On_Update function runs twice which I don't understand)Can anyone tell
me what/why? (and how to fix it?)Sorry for the deluge, but here is the code:
-->
<%@ Page
Language="VB"
%>
<html>
<head id="Head1" runat="server">
<title>Asset Survey</title>
</head>
<SCRIPT runat="server">
Protected Sub SqlDataSource1_Selecting(ByVal
sender As Object,
ByVal e As
System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
e.Command.Parameters("Name1").Value = User.Identity.Name
End Sub
Protected Sub SqlDataSource1_Updating(ByVal
sender As Object,
ByVal e As
System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating 'For some
reason, this function executes twice '2nd execution gets error: "Data type
mismatch in criteria expression." 'e.Command.Parameters("PrimaryKey1").Value
= intPrimaryKey 'e.Command.Parameters("Responder1").Value
= strUser 'e.Command.Parameters("ResponseDate1").Value
= dtModDate 'e.Command.Parameters("Response1").Value
= strResponse 'e.Command.Parameters("ResponseComments1").Value
= strComments End Sub
Protected Sub
GridView1_RowCommand(ByVal sender As Object, ByVal e As
GridViewCommandEventArgs) _
Handles
GridView1.RowCommand
Dim
GridRow1 As Integer
= Convert.ToInt32(e.CommandArgument)
If
e.CommandName = "UpdateYes" Then
'code here to disable buttons and textbox
'...
SqlDataSource1.UpdateParameters("Response1").DefaultValue
= "Yes"
End If
If
e.CommandName = "UpdateNo" Then
'code here to disable buttons and textbox
'...
SqlDataSource1.UpdateParameters("Response1").DefaultValue
= "No"
End If
'if I use the SqlDataSource1_Updating
function, I get the mismatch error
'but if I (mis)use the DefaultValue
parameter, no update occurs.
SqlDataSource1.UpdateParameters("ResponseComments1").DefaultValue = _
GridView1.Rows(GridRow1).FindControl("txtComments").ToString
SqlDataSource1.UpdateParameters("Responder1").DefaultValue =
User.Identity.Name
SqlDataSource1.UpdateParameters("ResponseDate1").DefaultValue =
DateTime.Now
SqlDataSource1.UpdateParameters("PrimaryKey1").DefaultValue = _
GridView1.Rows(GridRow1).Cells(0).Text
Try
SqlDataSource1.Update() 'Error Here if I use on_updating: "Data
type mismatch in criteria expression."
Catch except As Exception
' Handle the Exception.
End Try
End Sub
</SCRIPT>
<body>
<form id="formInv"
runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:LocalTestMDB %>"
ProviderName="<%$
ConnectionStrings:LocalTestMDB.ProviderName %>"
SelectCommand="SELECT
[PrimaryKey], [Name], [AssetTag], [Response], [Responder],
[ResponseDate],[ResponseComments] FROM [Tablename] WHERE ([Name] = ?) ORDER BY
[Login Name], [AssetTag]"
UpdateCommand="Update
[Tablename] SET [Response]=@Response1, [Responder]=@Responder1,
[ResponseDate]=ResponseDate1, [ResponseComments]=ResponseComments1 WHERE
[PrimaryKey]=@PrimaryKey1"
OnSelecting="SqlDataSource1_Selecting"
OnUpdating="SqlDataSource1_Updating">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="Name1" Type=String />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="ResponseComments1"
Type=String DefaultValue=""
/>
<asp:Parameter Name="Response1"
Type=String DefaultValue=""
/>
<asp:Parameter Name="ResponseDate1"
Type=DateTime DefaultValue="#8/1/2007
12:00:00PM#" />
<asp:Parameter Name="Responder1"
Type=String DefaultValue=""
/>
<asp:Parameter Name="PrimaryKey1"
Type=Int32 DefaultValue=0 />
</UpdateParameters>
</asp:SqlDataSource>
<span>Hi, Please
respond by clicking "Yes" or "No" for each asset</span>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataKeyNames="PrimaryKey"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="PrimaryKey"
HeaderText="PrimaryKey"
AccessibleHeaderText="PrimaryKey" Visible=False/>
<asp:BoundField DataField="Name"
HeaderText="Name"
SortExpression="Name"
AccessibleHeaderText="Name" />
<asp:BoundField DataField="AssetTag"
HeaderText="AssetTag"
SortExpression="AssetTag"
AccessibleHeaderText="AssetTag"
/>
<asp:TemplateField
HeaderText="Please
Respond">
<ItemTemplate>
<asp:Button ID="btnYes" runat="server"
Text="Yes"
Visible="true"
CommandName='UpdateYes'
CommandArgument='<%#
CType(Container, GridViewRow).RowIndex %>' />
<asp:Button ID="btnNo" runat="server"
Text="No"
Visible="true"
CommandName='UpdateNo'
CommandArgument='<%#
CType(Container, GridViewRow).RowIndex %>' />
<br />
<asp:Label ID="lblComments"
runat="server"
Text="Comments:"
Visible=True></asp:Label>
<asp:TextBox ID="txtComments"
runat="server"
Text='<%#
Bind("ResponseComments") %>'
Visible=True MaxLength=512 Height=24 Width=320 Wrap=True Enabled=True
TextMode=MultiLine></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
View 1 Replies
View Related
Nov 21, 2006
Hi all,
I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:
<asp:GridView ID="GridEditSettlement" runat="server" AutoGenerateColumns="False" BackColor="Navy"
BorderColor="IndianRed" BorderStyle="Solid" Font-Names="Verdana" Font-Size="X-Small" DataSourceID="SqlDataSource_grid" AllowPaging="True" AllowSorting="True" ForeColor="White" DataKeyNames="legid">
<Columns>
<asp:CommandField ShowEditButton="True" CancelImageUrl="~/App_Graphics/quit.gif" CancelText="" EditImageUrl="~/App_Graphics/EditGrid.GIF" EditText="" UpdateImageUrl="~/App_Graphics/save.gif" UpdateText="" ButtonType="Image" />
<asp:BoundField DataField="StartDate" HeaderText="Start Date" ReadOnly="True" />
<asp:BoundField DataField="EndDate" HeaderText="End Date" ReadOnly="True" />
<asp:BoundField DataField="CounterpartDealRef" HeaderText="CP Deal Ref" ReadOnly="True" />
<asp:TemplateField HeaderText="Preliminary Settlement Price" ><ItemTemplate>
<asp:Label ID=lblPreliminary runat=server Text='<%# Bind("PrimarySettlementPrice") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtPrimaryPrice Text='<%# Bind("PrimarySettlementPrice") %>'></asp:TextBox>
</EditItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="Agreed Settlement Price"><ItemTemplate>
<asp:Label ID=lblAgreed runat=server Text='<%# Bind("AgreedSettlementPrice") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtAgreedPrice Text='<%# Bind("AgreedSettlementPrice") %>'></asp:TextBox>
</EditItemTemplate></asp:TemplateField>
<asp:BoundField DataField="Volume" HeaderText="Volume" ReadOnly="True" />
<asp:BoundField DataField="Price" HeaderText="Price" ReadOnly="True" />
<asp:BoundField DataField="TotalVolume" HeaderText="Total Volume" ReadOnly="True" />
<asp:BoundField DataField="InstrumentName" HeaderText="Instrument" ReadOnly="True" />
<asp:BoundField DataField="NominalValue" HeaderText="Nominal Value" ReadOnly="True" />
<asp:BoundField DataField="Strike" HeaderText="Strike" ReadOnly="True" />
<asp:BoundField DataField="DeliveryDate" HeaderText="Delivery Date" ReadOnly="True" />
<asp:TemplateField HeaderText="LegId" SortExpression="LegId">
<ItemTemplate>
<asp:Label ID="lblLegID" runat="server" Text='<%# Bind("LegId") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtLegID Text='<%# Bind("LegId") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle BackColor="#FFFF66" ForeColor="#333333" />
<EditRowStyle BackColor="#FFFF66" Font-Names="Verdana" Font-Size="X-Small" ForeColor="#333333" />
<PagerStyle ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#333333" />
</asp:GridView>
<br />
<asp:SqlDataSource ID="SqlDataSource_grid" runat="server" ConnectionString="<%$ ConnectionStrings:DealCaptureDev %>" SelectCommand="sp_get_single_deal" SelectCommandType="StoredProcedure" UpdateCommand="Update trDealLeg Set PrimarySettlementPrice=@primarysettlement, AgreedSettlementprice=@agreedsettlement, LastUpdate=GetDate(), LastUpdateBy=Session('userid') Where LegID=@legid" EnableCaching="True" ConflictDetection="CompareAllValues" ProviderName="System.Data.SqlClient">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="" Name="dealnum" QueryStringField="deal"
Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="txtLegId" PropertyName="Text" Name="legId" />
<asp:ControlParameter ControlID="txtPrimarySettlement" Name="primarysettlement" PropertyName="Text" />
<asp:ControlParameter ControlID="txtAgreedSettlement" Name="agreedsettlement" PropertyName="Text"/>
<asp:SessionParameter DefaultValue="" Name="userid" SessionField="userid" />
</UpdateParameters>
</asp:SqlDataSource>
As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work).
So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event.
What is the best approach? Anyone come up against this issue before?
Thanks,
KB
View 1 Replies
View Related
May 13, 2008
Hi all,I have a gridview that bound to a SqlDataSource called SqlDataSourceGridView. I have enabled Edit in my GridView, but I do all the updating in code behind with stored procedure (using onRowUpdating). Now each time when I click "Update", the update went through and all the data got updated, but I received this error: Updating is not supported by data source 'SqlDataSourceGridView' unless UpdateCommand is specified.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.NotSupportedException:
Updating is not supported by data source 'SqlDataSourceGridView' unless
UpdateCommand is specified. What do I need to do to fix this problem?Thanks a lot.
View 6 Replies
View Related
Apr 13, 2007
I have a custom setup so bear with me here in my explanation. The SelectCommand for my SQLDataSource gets data from a table using a QueryString for filtering by the record ID. A Repeater control is then used to populate the data into different Textboxes on the page (and a custom HTML-editor control). This works 100%. I'm trying to use the UpdateCommand for the SQLDataSource to then update the record with the data that the user changes. (Example: FieldA is "foo" and the user changes it to "foobar". When the form is submitted, it then updates FieldB to be "foobar"). The form is being submitted, but the data isn't being updated at all. Any ideas? Thanks for the help in advance.
View 11 Replies
View Related
May 11, 2006
I have a SQLDataSource that has an UpdateCommand assigned to it. At certain times I may need to change the UpdateCommand to save different info to the database. I thought I would be able to use sqldatasource.updatecommand.equals() to change the UpdateCommand and then call the sqldatasource.update method, but it didn't work...the default command was still used. Is there something else I need to change? I figured my next option would be to create another sqldatasource. Any help would be great!
View 4 Replies
View Related
Oct 15, 2007
Hello all,
I am trying to update the record which involed the modification of key in Datakeynames, but when i click the update button from gridview, it doesn't allow to change the value of modified column.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>" OldValuesParameterFormatString="old_{0}" SelectCommand="SELECT * FROM [ServiceAgents]" InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)" UpdateCommand="UPDATE ServiceAgents SET AGENT=@AGENT, PHONE=@PHONE WHERE (STATE=@STATE and CITY=@CITY and AGENT=@old_AGENT and EQUIPMENT=@EQUIPMENT)" DeleteCommand="DELETE FROM ServiceAgents WHERE (STATE=@STATE and CITY=@CITY and AGENT=@AGENT and EQUIPMENT=@EQUIPMENT)" > <UpdateParameters> <asp:Parameter Type="String" Name="AGENT" /> <asp:Parameter Type="String" Name="PHONE" /> <asp:Parameter Name="old_AGENT" /> <asp:Parameter Type="String" Name="STATE" /> <asp:Parameter Type="String" Name="CITY" /> <asp:Parameter Type="String" Name="EQUIPMENT" /> </UpdateParameters> <InsertParameters> <asp:ControlParameter Name="Ser_STATE" ControlID="TextBox_state"/> <asp:ControlParameter Name="Ser_CITY" ControlID="TextBox_city"/> <asp:ControlParameter Name="Ser_AGENT" ControlID="TextBox_agent"/> <asp:ControlParameter Name="Ser_PHONE" ControlID="TextBox_phone"/> <asp:ControlParameter Name="Ser_EQUIPMENT" ControlID="TextBox_equip" /> </InsertParameters> <DeleteParameters> <asp:Parameter Type="String" Name="STATE" /> <asp:Parameter Type="String" Name="CITY" /> <asp:Parameter Type="String" Name="AGENT" /> <asp:Parameter Type="String" Name="EQUIPMENT" /> </DeleteParameters> </asp:SqlDataSource>
Does anyone got any ideas about it? Where is wrong in the control?
View 3 Replies
View Related
Oct 22, 2007
SqlDataSource UpdateCommand plus Insert
I have a SqlDataSource with an UpdateCommand but besides that i need also an Insert command triggered on the same update command.
That´s because i need to update a record and at the same time include a log of that update in another database.
Thanks
View 3 Replies
View Related
Mar 6, 2006
Hi
I have a Page with a DetailsView and SqlDataSource. When Editing the DetailsView the Sql;DataSource UpdateCommand does not seem to pick up the @parameters of the form fields. I have used OnItemUpdating to view all Keys/OldValues/NewValues passed into the UpdateCommand but nothing seems to be picked up.
When I place a value into UpdateCommand it updates correctly but does not when I use the form. At this stage I have simplified the code down to typical Master-Details Pages (Separate) using a QueryString to filter the SelectCommand. But nothing seems to work.
Please help! I have wasted so much time trying to resolve this and I am on deadline and need this to work.
Thanks
Dave
View 3 Replies
View Related
Aug 1, 2007
Hi, All
I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed:
<% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind();
dataSource.UpdateCommand = "";%>
<asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView>
The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row.
Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
View 3 Replies
View Related
Apr 14, 2008
Is there any way I can use a variable from my code behind file in the UpdateCommand of a sqlDataSource? I have tried
<%$ strUserGuid %>and<% strUserGuid %>
any help appreciated.Thanks
Dave
View 2 Replies
View Related
Jul 20, 2007
We've got an employee database that I'm modifying to include two photos of each employee, a small thumbnail image and a full-size image. The HR department maintenance page contains a listbox of employee names, which, when clicked, populates a detailsview control.To get the images to display and be updatable, I've had to structure the following SqlDatasource and DetailsView:
1 <asp:DetailsView ID="dvEmp" runat="server"2 AutoGenerateRows="false"3 DataSourceID="dsEmpView"4 DataKeyNames="empID">5 <Fields>6 <asp:CommandField ShowEditButton="true" ShowCancelButton="true" ShowInsertButton="true" />7 <asp:BoundField HeaderText="Name (Last, First)" DataField="empname" />8 <asp:TemplateField HeaderText="Thumbnail photo">9 <ItemTemplate>10 <asp:Image ID="imgThumbnail" runat="server" ImageUrl='<%# formatThumbURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />11 </ItemTemplate>12 <EditItemTemplate>13 <asp:Image ID="imgThumbHidden" runat="server" ImageUrl='<%# Bind("thumbURL") %>' Visible="false" />14 <asp:FileUpload ID="upldThumbnail" runat="server" />15 </EditItemTemplate>16 </asp:TemplateField>17 <asp:TemplateField HeaderText="Full Photo">18 <ItemTemplate>19 <asp:Image ID="imgPhoto" runat="server" ImageUrl='<%# formatImageURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />20 </ItemTemplate>21 <EditItemTemplate>22 <asp:Image ID="imgPhotoHidden" runat="server" ImageUrl='<%# Bind("photoURL") %>' Visible="false" />23 <asp:FileUpload ID="upldPhoto" runat="server" />24 </EditItemTemplate>25 </asp:TemplateField>26 </Fields>27 </asp:DetailsView>28 29 <asp:SqlDataSource ID="dsEmpView"30 runat="server"31 ConnectionString="<%$ ConnectionStrings:eSignInConnectionString %>"32 OnInserting="dsEmpView_Inserting"33 OnUpdating="dsEmpView_Updating"34 SelectCommand="SELECT empID, empname, photoURL, thumbURL FROM employees where (empID = @empID)"35 InsertCommand="INSERT INTO employees (empname, photoURL, thumbURL) values(@empname, @photoURL, @thumbURL)"36 UpdateCommand="UPDATE employees SET empname=@empname, photoURL=@photoURL, thumbURL=@thumbURL WHERE (empID = @empID)">37 <SelectParameters>38 <asp:ControlParameter ControlID="lbxEmps" Name="empID" PropertyName="SelectedValue" Type="Int16" />39 </SelectParameters>40 </asp:SqlDataSource>41 42 -----43 44 Protected Sub dsEmpView_Updating(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)45 Dim bAbort As Boolean = False46 Dim bThumb(), bPhoto() As Byte47 If e.Command.Parameters("@ename").Value.trim = "" Then bAbort = True48 Dim imgT As FileUpload = CType(dvEmp.FindControl("upldThumbnail"), FileUpload)49 If imgT.HasFile Then50 Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)51 bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)52 e.Command.Parameters("@thumbURL").Value = bThumb53 End Using54 End If55 Dim imgP As FileUpload = CType(dvEmp.FindControl("upldPhoto"), FileUpload)56 If imgP.HasFile Then57 Using reader As BinaryReader = New BinaryReader(imgP.PostedFile.InputStream)58 bPhoto = reader.ReadBytes(imgP.PostedFile.ContentLength)59 e.Command.Parameters("@photoURL").Value = bPhoto60 End Using61 End If62 e.Cancel = bAbort63 End SubIf the user updates both images at the same time by populating their respective FileUpload boxes, everything works as advertized. But if the user only updates one image (or neither image), things break. If they upload, say, just the full-size photo during an update, then it gives the error "System.Data.SqlClient.SqlException: Operand type clash: nvarchar is incompatible with image".I think this error occurs because the update command is trying to set the parameter "thumbURL" without having any actual data to set. But since I really don't want this image updated with nothing, thereby erasingthe photo already in the database, I'd rather remove this parameter from the update string.So, let's remove the parameter that updates that image by adding the following code just after the "End Using" lines: Else
Dim p As SqlClient.SqlParameter = New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image)
e.Command.Parameters.Remove(p)
(Similar code goes into the code block that handles the photo upload)Running the same update without an image in the thumb fileupload box, I now get this error: "System.ArgumentException: Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection."Huh? It's not there? Okay, so lets work it from the other end: let's remove all references to the thumbURL and photoURL from the dsEmpView datasource. We'll make its UpdateCommand = "UPDATE employees SET empname=@empname WHERE (empID = @empID)", and put code in the dsEmpView_Updating sub that adds the correct parameter to the update command, but only if the fileupload box has something in it. Therefore: If imgT.HasFile Then
Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)
bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)
e.Command.Parameters.Add(New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image, imgT.PostedFile.ContentLength))
e.Command.Parameters("@thumbURL").Value = bThumb
End Using
End If
(Similar code goes into the code block that handles the photo upload)But reversing the angle of attack only reverses the error. Uploading only the photo and not the thumb image results in: "System.Data.SqlClient.SqlException: The variable name '@photoURL' has already been declared. Variable names must be unique within a query batch or stored procedure."So now it's telling me the parameter IS there, even though I just removed it.ARRRGH!What am I doing wrong, and more importantly, how can I fix it?Thanks in advance.
View 5 Replies
View Related
Jul 20, 2007
Hi, I am a newbie in using ASP.NET 2.0 and ADO.NET. I wrote a hangman game and want to record statistics at the end of each game. I will create and update records in the database for each authenticated user as well as a record for the Anonymous, unauthenticated user. After a win or loss has occurred, I want to programmatically use the SQLDataSource control to increment the statistics counters for the appropriate record in the database (note I don't want to show anything or get user input for this function).
I need a VB.NET codebehind example that will show me how I should set up the parameters and update the appropriate record in the database. Below is my code. What happens now is that the program chugs along happily (no errors), but the database record does not actually get updated. I have done many searches on this forum and on the general Internet for programmatic examples of an update sequence of code. If there is a tutorial for this online or a book, I'm happy to check it out.
Any help will be greatly appreciated.
Lambanlaa
CODE - Hangman.aspx.vb
1 Protected Sub UpdateStats()2 Dim playeridString As String3 Dim gamesplayedInteger, gameswonInteger, _4 easygamesplayedInteger, easygameswonInteger, _5 mediumgamesplayedInteger, mediumgameswonInteger, _6 hardgamesplayedInteger, hardgameswonInteger As Int327 8 ' determine whether player is named or anonymous9 If User.Identity.IsAuthenticated Then10 Profile.Item("hangmanplayeridString") = User.Identity.Name11 Else12 Profile.Item("hangmanplayeridString") = "Anonymous"13 End If14 15 playeridString = Profile.Item("hangmanplayeridString")16 17 ' look up record in stats database18 Dim hangmanstatsDataView As System.Data.DataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)19 20 gamesplayedInteger = 021 gameswonInteger = 022 easygamesplayedInteger = 023 easygameswonInteger = 024 mediumgamesplayedInteger = 025 mediumgameswonInteger = 026 hardgamesplayedInteger = 027 hardgameswonInteger = 028 29 If hangmanstatsDataView.Table.Rows.Count = 0 Then30 31 ' then create record with 0 values32 statsSqlDataSource.InsertParameters.Clear() ' don't really know what Clear does33 statsSqlDataSource.InsertParameters("playerid").DefaultValue = playeridString34 statsSqlDataSource.InsertParameters("GamesPlayed").DefaultValue = gamesplayedInteger35 statsSqlDataSource.InsertParameters("GamesWon").DefaultValue = gameswonInteger36 statsSqlDataSource.InsertParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger37 statsSqlDataSource.InsertParameters("EasyGamesWon").DefaultValue = easygameswonInteger38 statsSqlDataSource.InsertParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger39 statsSqlDataSource.InsertParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger40 statsSqlDataSource.InsertParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger41 statsSqlDataSource.InsertParameters("HardGamesWon").DefaultValue = hardgameswonInteger42 43 statsSqlDataSource.Insert()44 End If45 46 ' reread the record to get current values47 hangmanstatsDataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)48 Dim hangmanstatsDataRow As System.Data.DataRow = hangmanstatsDataView.Table.Rows.Item(0)49 50 ' set temp variables to database values51 gamesplayedInteger = hangmanstatsDataRow("GamesPlayed")52 gameswonInteger = hangmanstatsDataRow("GamesWon")53 easygamesplayedInteger = hangmanstatsDataRow("EasyGamesPlayed")54 easygameswonInteger = hangmanstatsDataRow("EasyGamesWon")55 mediumgamesplayedInteger = hangmanstatsDataRow("MediumGamesPlayed")56 mediumgameswonInteger = hangmanstatsDataRow("MediumGamesWon")57 hardgamesplayedInteger = hangmanstatsDataRow("HardGamesPlayed")58 hardgameswonInteger = hangmanstatsDataRow("HardGamesWon")59 60 ' update stats record61 'statsSqlDataSource.UpdateParameters.Clear()62 'statsSqlDataSource.UpdateParameters("playerid").DefaultValue = playeridString63 64 If Profile.Item("hangmanwinorloseString") = "win" Then65 66 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 167 statsSqlDataSource.UpdateParameters("GamesWon").DefaultValue = gameswonInteger + 168 Select Case Profile.Item("hangmandifficultyInteger")69 Case 170 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 171 statsSqlDataSource.UpdateParameters("EasyGamesWon").DefaultValue = easygameswonInteger + 172 Case 273 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 174 statsSqlDataSource.UpdateParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger + 175 Case 376 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 177 statsSqlDataSource.UpdateParameters("HardGamesWon").DefaultValue = hardgameswonInteger + 178 End Select79 80 81 ElseIf Profile.Item("hangmanwinorloseString") = "lose" Then82 83 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 184 Select Case Profile.Item("hangmandifficultyInteger")85 Case 186 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 187 Case 288 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 189 Case 390 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 191 End Select92 End If93 94 statsSqlDataSource.Update()95 96 End Sub97
CODE - Hangman.aspx 1 <asp:SqlDataSource ID="statsSqlDataSource" runat="server" ConflictDetection="overwritechanges"
2 ConnectionString="<%$ ConnectionStrings:lambanConnectionString %>" DeleteCommand="DELETE FROM [Hangman_Stats] WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon"
3 InsertCommand="INSERT INTO [Hangman_Stats] ([PlayerID], [GamesPlayed], [GamesWon], [EasyGamesPlayed], [EasyGamesWon], [MediumGamesPlayed], [MediumGamesWon], [HardGamesPlayed], [HardGamesWon]) VALUES (@PlayerID, @GamesPlayed, @GamesWon, @EasyGamesPlayed, @EasyGamesWon, @MediumGamesPlayed, @MediumGamesWon, @HardGamesPlayed, @HardGamesWon)"
4 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT PlayerID, GamesPlayed, GamesWon, EasyGamesPlayed, EasyGamesWon, MediumGamesPlayed, MediumGamesWon, HardGamesPlayed, HardGamesWon FROM Hangman_Stats WHERE (PlayerID = @playerid)"
5 UpdateCommand="UPDATE [Hangman_Stats] SET [GamesPlayed] = @GamesPlayed, [GamesWon] = @GamesWon, [EasyGamesPlayed] = @EasyGamesPlayed, [EasyGamesWon] = @EasyGamesWon, [MediumGamesPlayed] = @MediumGamesPlayed, [MediumGamesWon] = @MediumGamesWon, [HardGamesPlayed] = @HardGamesPlayed, [HardGamesWon] = @HardGamesWon WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon">
6 <DeleteParameters>
7 <asp:Parameter Name="original_PlayerID" Type="String" />
8 <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
9 <asp:Parameter Name="original_GamesWon" Type="Int32" />
10 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
11 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
12 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
13 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
14 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
15 <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
16 </DeleteParameters>
17 <UpdateParameters>
18 <asp:Parameter Name="GamesPlayed" Type="Int32" />
19 <asp:Parameter Name="GamesWon" Type="Int32" />
20 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
21 <asp:Parameter Name="EasyGamesWon" Type="Int32" />
22 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
23 <asp:Parameter Name="MediumGamesWon" Type="Int32" />
24 <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
25 <asp:Parameter Name="HardGamesWon" Type="Int32" />
26 <asp:Parameter Name="original_PlayerID" Type="String" />
27 <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
28 <asp:Parameter Name="original_GamesWon" Type="Int32" />
29 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
30 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
31 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
32 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
33 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
34 <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
35 </UpdateParameters>
36 <InsertParameters>
37 <asp:Parameter Name="PlayerID" Type="String" />
38 <asp:Parameter Name="GamesPlayed" Type="Int32" />
39 <asp:Parameter Name="GamesWon" Type="Int32" />
40 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
41 <asp:Parameter Name="EasyGamesWon" Type="Int32" />
42 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
43 <asp:Parameter Name="MediumGamesWon" Type="Int32" />
44 <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
45 <asp:Parameter Name="HardGamesWon" Type="Int32" />
46 </InsertParameters>
47 <SelectParameters>
48 <asp:ProfileParameter Name="playerid" PropertyName="hangmanplayeridString" />
49 </SelectParameters>
50 </asp:SqlDataSource>
View 2 Replies
View Related
Jun 5, 2007
hi all,i'm working on a formview which will update 2 tables which are linked together but..i'm getting an error of the control not being found.my code is as shown below <asp:SqlDataSource ID="invDetSrc" runat="server" ConnectionString="<%$ ConnectionStrings:rockwellConnectionString %>" ProviderName="<%$ ConnectionStrings:rockwellConnectionString.ProviderName %>" SelectCommand="SELECT rockwell.logix_asset_list.Inventory_ID, rockwell.vendor.Vendor_Name, rockwell.logix_asset_list_category.Category_Name, rockwell.logix_asset_list.Total_Quantity, rockwell.logix_asset_list.Quantity_In_Stock, rockwell.logix_asset_list.Part_Number, rockwell.logix_asset_list.On_Order, rockwell.logix_asset_list.ImageUrl, rockwell.logix_asset_list.DrawingUrl, rockwell.logix_asset_list.Description FROM rockwell.logix_asset_list, rockwell.logix_asset_list_category, rockwell.vendor WHERE rockwell.logix_asset_list.Logix_Asset_List_Category_ID = rockwell.logix_asset_list_category.Logix_Asset_List_Category_ID AND rockwell.logix_asset_list.Vendor_ID = rockwell.vendor.Vendor_ID AND rockwell.logix_asset_list.Part_Number = ?" UpdateCommand="UPDATE vendor, logix_asset_list set vendor.vendor_name= @Vendor_Name, logix_asset_list.Total_Quantity=@Total_Quantity, logix_asset_list.Quantity_In_Stock=@Quantity_In_Stock, logix_asset_list.Part_Number=@Part_Number, logix_asset_list.On_Order=@On_Order, logix_asset_list.Description=@Description WHERE logix_asset_list.Vendor_ID = vendor.Vendor_ID and logix_asset_list.Inventory_ID = @Inventory_ID" > <SelectParameters> <asp:ControlParameter ControlID="rstGrid" Name="?" PropertyName="SelectedValue" /> </SelectParameters> <UpdateParameters> <asp:ControlParameter Type="Int32" ControlID="Total_Quantity" ConvertEmptyStringToNull="true" Name="@Total_Quantity"/> <asp:ControlParameter Type="string" ControlID="Vendor_Name" ConvertEmptyStringToNull="true" Name="@Vendor_Name"/> <asp:ControlParameter Type="Int32" ControlID="Quantity_In_Stock" ConvertEmptyStringToNull="true" Name="@Quantity_In_Stock"/> <asp:ControlParameter Type="string" ControlID="Part_Number" ConvertEmptyStringToNull="true" Name="@Part_Number"/> <asp:ControlParameter Type="string" ControlID="On_Order" ConvertEmptyStringToNull="true" Name="@On_Order"/> <asp:ControlParameter Type="string" ControlID="Description" ConvertEmptyStringToNull="true" Name="@Description"/> <asp:ControlParameter Type="Int32" ControlID="Inventory_ID" ConvertEmptyStringToNull="true" Name="@Inventory_ID"/> </UpdateParameters> </asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataSourceID="invDetSrc" ForeColor="Black" GridLines="Vertical" DataKeyNames="Inventory_ID"> <EditItemTemplate> <table border="0" cellpadding="3" cellspacing="3" style="background-color: white; color: black;"> <tr> <td colspan="2"> <asp:Image ID="Image1" runat="server" Height="150px" ImageUrl='<%# "~/Image/photo/Logix%20Asset/"+Eval("Category_Name")+"/"+Eval("ImageUrl") %>' Width="150px" /> <br /> <asp:HyperLink ID="drawingLink" runat="server" NavigateUrl='<%# "~/Image/drawing/Logix%20Asset/"+Eval("Category_Name")+"/"+Eval("DrawingUrl") %>' Target="_blank" Text="View Drawing" ForeColor="Blue"></asp:HyperLink> </td> <asp:HiddenField ID="HiddenField1" runat="server" Value='<%# Bind("Inventory_ID") %>' /> </tr> <tr> <td style="color: black"> Part Number:</td> <td> <asp:TextBox ID="Part_Number" runat="server" Text='<%# Bind("Part_Number")%>'></asp:TextBox> </td> </tr> <tr> <td> <strong style="color: black">Vendor:</strong></td> <td> <asp:TextBox ID="Vendor_Name" runat="server" Text='<%# Bind("Vendor_Name")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black"> <strong>Total Quantity:</strong></td> <td> <asp:TextBox ID="Total_Quantity" runat="server" Text='<%# Bind("Total_Quantity")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black"> <strong>Quantity In Stock:</strong></td> <td> <asp:TextBox ID="Quantity_In_Stock" runat="server" Text='<%# Bind("Quantity_In_Stock")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black"> <strong>Quantity on Order:</strong></td> <td> <asp:TextBox ID="On_Order" runat="server" Text='<%# Bind("On_Order")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black; height: 44px;"> <strong>Description:</strong></td> <td style="height: 44px"> <asp:TextBox ID="Description" runat="server" Text='<%# Bind("Description")%>' Height="53px" TextMode="MultiLine" Width="150px"></asp:TextBox></td> </tr> <tr> <td> <asp:Button ID="EditBtn" runat="server" Text="Submit Changes" Width="106px" CommandName="Update" /></td> <td> <asp:Button ID="Cancel" runat="server" Text="Cancel" CommandName="cancel" /></td> </tr> </table> </EditItemTemplate> </asp:FormView>any help would be much appreciated!
View 1 Replies
View Related
May 3, 2008
i have a gridview which is bound to sqldatasource. i have a delete button in the gridview. I have written code for "deleting" a record in app_code directory.
now iam not using "deletecommand" of sqldatasource but on the click of "delete" link i want to call the procedure in the app_code.
Any ideas on how to do it.??
View 4 Replies
View Related
Apr 14, 2006
I have created a GridView that uses a SqlDataSource. When I run the page it does not pull back any data. However when I test the query in the SqlDataSource dialog box it pulls back data.
Here is my GridView and SqlDataSource:
<asp:GridView ID="Results" runat="server" AllowPaging="True" AllowSorting="True"
CellPadding="2" EmptyDataText="No records found." AutoGenerateColumns="False" Width="100%" CssClass="tableResults" PageSize="20" DataSourceID="SqlResults" >
<Columns>
<asp:BoundField DataField="DaCode" HeaderText="Sub-Station" SortExpression="DaCode" >
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="DpInfo" HeaderText="Delivery Point" SortExpression="DpInfo" >
<HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" />
<ItemStyle CssClass="tdResults" />
</asp:BoundField>
<asp:HyperLinkField DataNavigateUrlFields="CuCode,OrderID" DataNavigateUrlFormatString="TCCustDetail.asp?CuCode={0}&OrderID={1}"
DataTextField="OrderID" HeaderText="Order No" SortExpression="OrderID">
<ItemStyle CssClass="tdResults" HorizontalAlign="Center" />
<HeaderStyle CssClass="tdHeaderResults" HorizontalAlign="Center" />
</asp:HyperLinkField>
<asp:BoundField HeaderText="Order Date" SortExpression="OrderDate" DataField="OrderDate" >
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="ReqDeliveryDate" HeaderText="Req Delivery Date" SortExpression="ReqDeliveryDate" >
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="StatusDate" HeaderText="Status Date" SortExpression="StatusDate">
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="ManifestNo" HeaderText="Manifest No" SortExpression="ManifestNo">
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="CustomerPO" HeaderText="P.O. No" SortExpression="CustomerPO">
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="Class" HeaderText="Class" SortExpression="Class">
<ItemStyle HorizontalAlign="Left" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="OrderStatus" HeaderText="Order Status" SortExpression="StatusSort">
<ItemStyle HorizontalAlign="Left" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" />
</asp:BoundField>
</Columns>
<HeaderStyle ForeColor="White" HorizontalAlign="Left" />
<AlternatingRowStyle CssClass="tdResultsAltRowColor" />
</asp:GridView>
<asp:SqlDataSource ID="SqlResults" runat="server" ConnectionString="<%$ ConnectionStrings:TransportationConnectionString %>" SelectCommand="GetOrderSummaryResults" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="10681" Name="CuCode" Type="String" />
<asp:Parameter DefaultValue="" Name="DaCode" Type="String" />
<asp:Parameter DefaultValue="" Name="DpCode" Type="String" />
<asp:Parameter DefaultValue="" Name="OrderID" Type="String" />
<asp:Parameter DefaultValue="" Name="ManifestNo" Type="String" />
<asp:Parameter DefaultValue="" Name="PONo" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
I can get it to fill with data by manually filling the GridView without using a SqlDataSource but then I cannot get the sorting to work when I do it that way. Actually not sure if the sorting will work this way either as I cannot get it to fill with data. Any ideas would be much appreciated.
View 1 Replies
View Related
Oct 31, 2006
Hi guys.Im using a gridview to show some training data on my website which is populated by:<asp:SqlDataSource ID="ARENATraining" runat="server" ConnectionString="<%$ ConnectionStrings:ARConnection %>" SelectCommand="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @EventPrivate) ORDER BY [EventDate]"> <SelectParameters> <asp:Parameter DefaultValue="FALSE" Name="EventPrivate" Type="Boolean" /> </SelectParameters> So far so good..However I want to be able to filter the data.A) Show everythingB) Show individual EventType i.e. Seminar, Training etc etcIs it possible to generate the Select Command via a function? (im not using stored procedures for this part of the site, nor really want to at the moment). Id appreciate any help, otherwise ill be forced to curse and swear and use the good ol repeater and figureing out how to page it! Cheers guys
View 6 Replies
View Related
Jan 28, 2007
i am now writing a web page which can allow users to edit the field(display in the gridview->gridview source is from sqldatasource), there is a problem(it seems that i have followed all steps in the book):
when the i edit the field and click update, in the web site, i can see those changes. however, i cant see any change in database table.
i cant find solution on this, could anyone help me to solve this problem? thx!
View 2 Replies
View Related
May 11, 2007
Hello.When I create a user at the ASP.NET database, I need to insert more fields than the defaults, and I do it like this: Dim customProfile As ProfileCommon = ProfileCommon.Create(CreateUserWizard1.UserName, True)
customProfile.telephone =
(CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("TelephoneText"),
TextBox)).Text(telephone example)Those data are inserted at the DB at the aspnet_Profile table, in the fields PropertyNames & PropertyValuesString, but they are saved together (see image above)
I want to separate those properties in the GridView as long as each property appears in a column, is it possible? Thank you very much, i'm expecting your answers.
View 11 Replies
View Related
Jan 12, 2008
hi .. i have a SqlDataSource, GridView & a Button .. i want to increase the condition in the "SelectCommand" below by one everytime i click the button .. i mean to be like that ID < 8 instead of ID < 7
so, the GridView will show 7 ID's instead of 6 .. and it will increase everytime i hit the button<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT * FROM [table1] WHERE ID < 7">
</asp:SqlDataSource>
View 4 Replies
View Related
Aug 21, 2006
What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!
View 4 Replies
View Related
Apr 15, 2008
Hi
I have a gridview which is using a SQLdatasource, to update the table. One of my parameters on the SP is username.
At the moment on page load I am declaring a session variable
Session("UserName") = me.user.identy.name
I then amend the update parameter in the SQL Datasource to accept the username from the session variable. - works fine, I was however wondering if there is a way of returning the username directly into the sqldatasource without having to pass it through a session variable?
regards
Tom
View 3 Replies
View Related
May 2, 2008
Hi guys! I have a simple windows form where a gridview is being populated by a database. I have checked the ability to update, insert delete. And everything is fine but when i want to delete a row it throws an exeption. The exeptions says: "Must declare the scalar variable "@id"."I haven't changed the delete, update and insert commands. They are as fallowsDeleteCommand="DELETE FROM [recepti] WHERE [id] = @id"InsertCommand="INSERT INTO [recepti] ([ime], [recepta], [snimka], [snimka2], [tip]) VALUES (@ime, @recepta, @snimka, @snimka2, @tip)" SelectCommand="SELECT * FROM [recepti]"UpdateCommand="UPDATE [recepti] SET [ime] = @ime, [recepta] = @recepta, [snimka] = @snimka, [snimka2] = @snimka2, [tip] = @tip WHERE [id] = @id" The id column is auto generated, unique, also it's a primery key.So any idea where is the problem?
View 2 Replies
View Related
May 19, 2008
Hi,I have two SQLDataSources called "LeagueTableHome" an "LeagueTableAway" on my page.
I want to create another SQLDataSource called "LeagueTableTotal" on my page which adds up all the totals from each of the other two sources.
The datasource looks like this:
Team, Pld, W, D, L, F, A, Agg, Pts
my code for LeagueTableHome looks like this:
SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
I want then to show LeagueTableTotal in a GridView.
Can anybody help?
View 4 Replies
View Related
May 19, 2008
I cant seen to change the Select command for a SQL Datasourcetry #1 SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters("ProfileID").DefaultValue = pProfileID SqlDataSourceProfilesThatMatch.SelectParameters("LoggedInUsersZipcode").DefaultValue = pUsersZipCode SqlDataSourceProfilesThatMatch.SelectParameters("ZipDistance").DefaultValue = pDistance
NewProfilesThatMatchGridView.DataBind()
try #2 SqlDataSourceProfilesToBeMatched.SelectParameters.Clear() SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters.Add("ProfileID", pProfileID) SqlDataSourceProfilesThatMatch.SelectParameters.Add("LoggedInUsersZipcode", pUsersZipCode) SqlDataSourceProfilesThatMatch.SelectParameters.Add("ZipDistance", pDistance)
NewProfilesThatMatchGridView.DataBind()
No errors but no rows show in the gridview. If I debug and get the value strSQLForSearch and paste it into a new SQL query window I get results.
Any ideas????
Thanks
View 3 Replies
View Related
Mar 8, 2006
Hello
I'm experiencing some problems, binding a SqlDataSource to a GridView.
The following code creates the SqlDataSource: string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; SqlDataSource ds = new SqlDataSource(strProvider, strConn); ds.SelectCommand = "SELECT * FROM rammekategori";
Then i bind the SqlDataSource to a GridView:
GridView1.DataSource = ds; GridView1.DataBind();
ErrorMessage:
Format of the initialization string does not conform to specification starting at index 0.
Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.Line 24: GridView1.DataBind();Am i totally off target here? Can it be something about, that you have to set the datasource of the gridview, before the Page_Load event?
Thanks - MartinHN
View 9 Replies
View Related
Apr 17, 2006
Help!I am trying to fill my datagrid using the SQLDataSource, using a stored procedure.The stored procedure expects a parameter which I can collect via the querystring, or a string. How can I pass the parameter through the SQLDatasSource?My SQLDataSource is SQLData1. I already have:SQLData1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSQLData1.SelectCommand = "dbo.get_players"Thanks in advance,Karls
View 2 Replies
View Related
Mar 4, 2007
Hi, I'm new in ASP 2.0. I need to incorporate edit and delete capability in GridView. Using the wizard, i've generated this code. When I delete a row, it gets deleted but update does not work. I've tried several ways. I got no error or exception. But row is not updated. I've checked database, and I think the update query is not executing at all. Please let me know, what I'm doing wrong? Here is the source code for reference. I'm using Visual Studio 2005 with SQL server 2005 Express Edition. Regards
==========================================================
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="QuoteItemID" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" CausesValidation="False" /> <asp:BoundField DataField="QuoteItemID" HeaderText="QuoteItemID" InsertVisible="False" ReadOnly="True" SortExpression="QuoteItemID" /> <asp:BoundField DataField="QuoteID" HeaderText="QuoteID" SortExpression="QuoteID" /> <asp:BoundField DataField="ChargeCodeID" HeaderText="ChargeCodeID" SortExpression="ChargeCodeID" /> <asp:BoundField DataField="RateItemAmount" HeaderText="RateItemAmount" SortExpression="RateItemAmount" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ratesdb %>" DeleteCommand="DELETE FROM [Quote_item] WHERE [QuoteItemID] = @QuoteItemID" InsertCommand="INSERT INTO [Quote_item] ([QuoteID], [ChargeCodeID], [RateItemAmount]) VALUES (@QuoteID, @ChargeCodeID, @RateItemAmount)" SelectCommand="SELECT * FROM [Quote_item]" UpdateCommand="UPDATE [Quote_item] SET [QuoteID] = @QuoteID, [ChargeCodeID] = @ChargeCodeID, [RateItemAmount] = @RateItemAmount WHERE [QuoteItemID] = @QuoteItemID" ConflictDetection="CompareAllValues"> <DeleteParameters> <asp:Parameter Name="QuoteItemID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="QuoteID" Type="Int32" /> <asp:Parameter Name="ChargeCodeID" Type="Int32" /> <asp:Parameter Name="RateItemAmount" Type="Decimal" /> <asp:Parameter Name="QuoteItemID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="QuoteID" Type="Int32" /> <asp:Parameter Name="ChargeCodeID" Type="Int32" /> <asp:Parameter Name="RateItemAmount" Type="Decimal" /> </InsertParameters> </asp:SqlDataSource>
View 6 Replies
View Related
Nov 5, 2006
I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this?
(if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data)
Thanks.
View 9 Replies
View Related