How To Update SqlDataSource Query
Nov 7, 2007
I am trying to reload an AJAX control without reloading the page. This question may belong under AJAX, but I'm not sure if I'm managing the SqlDataSource correctly. I want to set the SelectCommand to a new SQL statement and refresh the form on the screen. My code is below. It doesn't seem to be working. Do I need to call something else or is this correct and the problem is with that nasty AJAX?SqlDataSource oSqlDataSource1 = (SqlDataSource) oContentPlaceHolder1.FindControl("SqlDataSource1");
oSqlDataSource1.SelectCommand = "SELECT * FROM WorkHistory WHERE WorkHistoryID = " + sWorkHistoryID;
oSqlDataSource1.Select(DataSourceSelectArguments.Empty);
View 2 Replies
ADVERTISEMENT
May 10, 2006
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e){
sqlDataSource1.Update();
sqlDateSource2.Update();}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
Zhang
View 2 Replies
View Related
Sep 6, 2006
I'm new to ASP and ASP.NET so I used the Wizards in Visual Web Deverlopment Express 2005 to build the following code:<asp:DetailsView ID="DetailsView" runat="server" DataSourceID="TracksDataSource"
Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False">
<Fields>
<asp:BoundField DataField="pk_trackID" HeaderText="pk_trackID" ReadOnly="True" SortExpression="pk_trackID" />
<asp:BoundField DataField="trackName" HeaderText="trackName" SortExpression="trackName" />
<asp:BoundField DataField="trackPath" HeaderText="trackPath" SortExpression="trackPath" />
<asp:BoundField DataField="lyrics" HeaderText="lyrics" SortExpression="lyrics" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="TracksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>"
SelectCommand="SELECT * FROM [Tracks] WHERE ([pk_trackID] = @pk_trackID)" UpdateCommand="UPDATE [Tracks] SET [trackName] = @trackName, [trackPath] = @trackPath, [lyrics] = @lyrics WHERE [pk_trackID] = @pk_trackID" >
<UpdateParameters>
<asp:Parameter Name="trackName" Type="String" />
<asp:Parameter Name="trackPath" Type="String" />
<asp:Parameter Name="lyrics" Type="String" />
<asp:Parameter Name="pk_trackID" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TracksListBox" Name="pk_trackID" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource> However, when I click Edit, change something, and then Update it doesn't update the database. However, if I remove the DataField bindings and use the AutoGenerateRows feature it works fine.
View 7 Replies
View Related
Nov 6, 2006
One of the requirements of the UpdateParameters for a GridView I'm building is that the fields that are being edited via EditItemTemplates are passed back to the UpdateParameter as XML. How would I go about combining the fields from the GridView/EditItems into an XML string that I can set as an asp:Parameter?
Thanks.
View 1 Replies
View Related
Mar 15, 2007
OK guys, I'm sure I'm doing something stupid here but after 2 days I find myself extremely frusted... Any help would be appreciated
I'm tring to write an update procedure for an SqlDataSource control that will allow me to chage the primary key values of a record in the table. Everything seems to work fine with the exception of obtaining the old values (the primary key before its changed by the end user) during the update. I've read several articles on the web an in the forums on this topic. Much of it has to do with the .NET beta version alteration from "original_{0}" to "{0}". I believe those conversations do not describe my problem.
Anyhow, onto some source code. Here's the ASP source code for a grid view and sql data source. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="productName,productURL" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="productName" HeaderText="productName" ReadOnly="True"
SortExpression="productName" />
<asp:BoundField DataField="productURL" HeaderText="productURL" ReadOnly="True" SortExpression="productURL" />
<asp:BoundField DataField="productSupportURL" HeaderText="productSupportURL" SortExpression="productSupportURL" />
<asp:BoundField DataField="description" HeaderText="description" SortExpression="description" />
<asp:BoundField DataField="modified" HeaderText="modified" SortExpression="modified" />
<asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" />
<asp:BoundField DataField="useridDomain" HeaderText="useridDomain" SortExpression="useridDomain" />
<asp:BoundField DataField="sortOrder" HeaderText="sortOrder" SortExpression="sortOrder" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SBAAdminConnectionString1 %>"
DeleteCommand="DELETE FROM [homePageList] WHERE [productName] = @old_productName AND [productURL] = @old_productURL"
InsertCommand="INSERT INTO [homePageList] ([productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder]) VALUES (@productName, @productURL, @productSupportURL, @description, @modified, @userid, @useridDomain, @sortOrder)"
ProviderName="<%$ ConnectionStrings:SBAAdminConnectionString1.ProviderName %>"
SelectCommand="SELECT [productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder] FROM [homePageList]"
UpdateCommand="UPDATE homePageList SET productSupportURL = @productSupportURL, description = @description, modified = @modified, userid = @userid, useridDomain = @useridDomain, sortOrder = @sortOrder, productName = @productName, productURL = @productURL WHERE (productName = @original_productName) AND (productURL = @original_productURL)" OldValuesParameterFormatString="original_{0}">
<DeleteParameters>
<asp:Parameter Name="old_productName" Type="String" />
<asp:Parameter Name="old_productURL" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="description" Type="String" />
<asp:Parameter Name="modified" Type="DateTime" />
<asp:Parameter Name="userid" Type="String" />
<asp:Parameter Name="useridDomain" Type="String" />
<asp:Parameter Name="sortOrder" Type="Single" />
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="productURL" Type="String" />
<asp:Parameter Name="productSupportURL" Type="String" />
<asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productName"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productURL"
PropertyName="SelectedValue" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="productURL" Type="String" />
<asp:Parameter Name="productSupportURL" Type="String" />
<asp:Parameter Name="description" Type="String" />
<asp:Parameter Name="modified" Type="DateTime" />
<asp:Parameter Name="userid" Type="String" />
<asp:Parameter Name="useridDomain" Type="String" />
<asp:Parameter Name="sortOrder" Type="Single" />
</InsertParameters>
</asp:SqlDataSource>I added a text box for every field in the grid to the form and update the fields when the user selects a record in the GridView. I then added an Update button to the form and tied the event to this procedure: Protected Sub bUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bUpdate.Click
'if the user has not entered key fields!
If missingKeyFields() Then
Exit Sub
End If
Dim count, so As Integer
Dim uDomain, uid As String
'parse out the user id and domain.
Try
If HttpContext.Current.User.Identity.Name = "" Then
uid = "unknow"
uDomain = "unknown"
Else
uid = HttpContext.Current.User.Identity.Name
uid = uid.Substring(uid.IndexOf("") + 1)
uDomain = uid.Substring(0, uid.IndexOf("") - 1)
End If
Catch ex As Exception
uid = "unknow"
uDomain = "unknown"
End Try
'fill in the parameters
'@productName, @productURL, @productSupportURL, @description, @modified, @userid, @useridDomain, @sortOrder
Me.SqlDataSource1.UpdateParameters.Item("productName").DefaultValue = Me.tbProductName.Text
Me.SqlDataSource1.UpdateParameters.Item("productURL").DefaultValue = Me.tbProductURL.Text
Me.SqlDataSource1.UpdateParameters.Item("productSupportURL").DefaultValue = Me.tbProductSupportURL.Text
Me.SqlDataSource1.UpdateParameters.Item("description").DefaultValue = Me.tbProductDescription.Text
Me.SqlDataSource1.UpdateParameters.Item("modified").DefaultValue = Now
Me.SqlDataSource1.UpdateParameters.Item("userid").DefaultValue = uid
Me.SqlDataSource1.UpdateParameters.Item("useridDomain").DefaultValue = uDomain
Me.SqlDataSource1.UpdateParameters.Item("sortOrder").ConvertEmptyStringToNull = True
If Integer.TryParse(Me.tbSortOrder.Text, so) Then
Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue = so
Else
Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue = ""
End If
'insert the record.
count = Me.SqlDataSource1.Update()
Me.lCommandInfo.Text = count.ToString + " record was updated."
End Sub
The net result of the procedure is the message "0 record was updated." when I select a record in the gridView and then edit the "productName" field and click Update.
In an attempt to trace down the problem I added this code: Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
Message.Text = e.Command.CommandText
Message.Text += "<br>"
For lp As Integer = 0 To e.Command.Parameters.Count - 1
Message.Text += e.Command.Parameters(lp).ParameterName + "=" + e.Command.Parameters(lp).Value
Message.Text += "<br>"
Next
End Sub
The message label displays the following text:UPDATE homePageList SET productSupportURL = @productSupportURL, description = @description, modified = @modified, userid = @userid, useridDomain = @useridDomain, sortOrder = @sortOrder, productName = @productName, productURL = @productURL WHERE (productName = @original_productName) AND (productURL = @original_productURL)
@description=interesting!
@modified=3/15/2007 4:07:31 PM
@userid=unknow
@useridDomain=unknown
@sortOrder=
@productName=ok22
@productURL=http
@productSupportURL=http
@original_productName=ok
@original_productURL=ok
Notice how the "@original_productURL" has the same value as the "@original_productName"? This should not be the case. The @original_productURL should equal "http" since I didn't update the field's value. It seems like the second field of the primary key is not getting updated or passed to the @original_productURL parameter. Since .NET handles the @original_{0} parameters I don't know why the field isn't being properly updated.
Any thoughts on how to track down the root cause of my problem... Or better yet any thoughts on a work around to my problem?
Thanks,Johnny
View 5 Replies
View Related
Jun 14, 2007
I need to know how to setup the ControlParameter for a template control in my Gridview? I have a datepicker in my template and I need to know how to refer to it in the ControlParameter of the SQLDataSource control.
<UpdateParameters>
<asp:ControlParameter Name="dp_start" ControlID="bdpPlanStart" PropertyName="SelectedValue" Type="Datetime" /> Here is the template:<asp:TemplateField HeaderText="Start" SortExpression="dp_start">
<ItemTemplate>
<%#DataBinder.Eval(Container, "DataItem.dp_start", "{0:d}")%>
</ItemTemplate>
<EditItemTemplate>
<BDP:BasicDatePicker id="bdpPlanStart" SelectedValue='<%# DataBinder.Eval(Container.DataItem,"dp_start") %>' runat="server" DateFormat="d">
</BDP:BasicDatePicker>
</EditItemTemplate>
</asp:TemplateField>
View 1 Replies
View Related
Jan 16, 2008
Is there a way to have two sql update commands so I can use both during an update depending on which row it is?
View 1 Replies
View Related
Feb 8, 2008
I have a number of textboxes in a WizardStep of my CreateUserWizard and I can't update the contents to the database (see below for an example). Any suggestions would be appreciated. Dim txt As TextBox = cuwMoreInfo.FindControl("txtAddress")
srcMoreInfo.UpdateParameters("Address").DefaultValue = DirectCast(txt, TextBox).Text
View 12 Replies
View Related
Apr 5, 2008
Hi All,
i want to use sqldatasource with controlparameters.there are some textboxes and a dropdownlist on my page.i can easily insert and delete records on database table by using these controls as controlparameters by sqldatasource insert() and delete() method but updating fails.i use try catch block to see the error but no errors found.And also update() method returns 1 that indicates that it worked fine but when i look into the database i see that the record is not updated.i am stucked and surprised.any ideas please?
Thanks
View 5 Replies
View Related
Apr 18, 2008
I just want to update a single file on the table with the content of a text box
the sql statement is update GAMME set newvaleur=@newvaleur where product_ID=old_product_ID
on sqldatasource1 updating I wrote
E.COMMAND.PARAMETERS("@newvaleur").value=....
e.command.parameters("old_product_id").value=....
the thing is it doesn't work, I don't think the line with the old_product_id is ok but I don't know how to do it...
View 4 Replies
View Related
Jan 15, 2006
Hi,
I have a repeater which i need to build editing capabilities into in a similar way as the GridView, just a little unsure how to tie it together. Ideally i would like to use a GridView as this would do all of the work for me however it looks like I need to go with the repeater due to the nested nature of the data I am working with.
What is the best way to go about updating the data source from within a repeater? I have already built the edit interface with some MultiViews in the Repeater control and handling the ItemCommand event, just not sure how to actually update the data source.
Can I still use the SqlDataSource wizard to generate all of the SQL for me? If so, within the ItemCommand event handler I have the data i want to update back to the database by using the FindControl method and retrieving the Text, but how do i get this data into the SqlDataSource object and update the database?
Are there any samples available which show how to update a datasource using a repeater?
Your help is much appreciated
trenyboy
View 6 Replies
View Related
Jul 10, 2006
I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast. And then firing off the update command that contains that parameter from a button. But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes. All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list. But it doesn't even change anything. It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc. It just won't change the data from the page when I try to.
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!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>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is much appreciated.
View 4 Replies
View Related
Aug 3, 2006
I'm referring to this bug, which I've seen a lot of people (including me) run into:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937
Has anyone heard if MS plans to fix this soon?
Thanks
View 3 Replies
View Related
Feb 13, 2007
Hi,I'm trying to create a registration page that I've divided into multiple pages (first page for basic details, next page for address, etc.). I insert the record in the first page, and update it in the other pages. I pass the newly created ID to the other pages using the Page.PreviousPage property. In the second page, I have the SqlDataSource configured as "SELECT * FROM [Table] WHERE ID = ?", and the UpdateCommand is "UPDATE .... WHERE ID = ?". In Page_Load, I am updating the SelectCommand to "SELECT ... WHERE ID = " & intID, and the UpdateCommand similarly. The I do a dtlsvw.Databind()But when I go to the next page (the newly created ID is being passed properly), the update doesn't do anything. The new record doesn't contain the values in the detailsview. Can somebody help me out? Thanks,Wild Thing
View 2 Replies
View Related
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
May 29, 2007
Hi, I have such a problem:I try to update (or insert) a row in my table and i fail althought i,ve read many posts here. I.ve created a button and "on_click" event to this button and want that event to update or insert a record in my table. I wrote: protected void selectButton_Click(object sender, EventArgs e) { String taskID = projectsGridView.SelectedRow.Cells[0].Text; usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@task where [UserID]=1"; usersSqlDataSource.UpdateParameters.Add("task", taskID); usersSqlDataSource.Update(); } The application creates error in the last line of code (usersSqlDataSource.Update();) and i receive such an error:You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is empty. Pass in a valid dictionary for update or change your mode to OverwriteChanges. For me it looks like there is a problem while setting parameters. Shall i change some properties of the sqlDataSource or GridView? Please help..
View 2 Replies
View Related
Jul 3, 2007
ello all
Would someone be so kind as to save me from getting balder through pulling my hair out.
My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource.
I have achieve the problem of retrieving the data from the sqlDataSource:DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty); //Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView;
foreach (DataRowView rowProduct in openReminding) { //Output the name and price lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString();
}
Within the sqlDataSource the sql code is as follows:SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @Number)
So at the moment my problems is being able to edit and update data to the same SELECTed data.Thank you for any help that you might have...
SynDrome
View 8 Replies
View Related
Jun 9, 2008
Does anyone know why would SQLDataSource.Update method return a negative value even though its updating records correctly?Its happening with .NET 2.0.
View 2 Replies
View Related
Jan 3, 2006
Hello Guys,
I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?
View 4 Replies
View Related
Jan 26, 2006
Here is the problem.
I have table such as this
PK selection VARCHARPK selection_choice VARCHAR show BIT
When I use the sqlDataSource to create an update it creates this.UPDATE lu_selection_choices SET show = @show WHERE (selection = @selection) AND (selection_choice = @selection_choice)
Now this is OK, but when you perform an update and in say a details control you change the selection_choice the value of the parameter @selection_choice is going to be equal to the old data. So it performs a successful update on show which isn't being changed selection_choice is.
So the questions is. Is there anyway to tell the update function of the datasource to insert the a new parameter with the SET selection_choice = @SOME NEW PARAM slection_choice?
Thanks,
Darren King
View 4 Replies
View Related
Jun 13, 2006
Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select).
Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command.
How do I get the link button to trigger the update command?
Thanks, Mark
View 1 Replies
View Related
Jul 4, 2006
i have default.aspx file and i put SqlDataSource into my designer form.when i want to configure SqlDataSource it not allowed my to insert, update and delete only insert is allowed.what is the problem and the solution for this case ...thx
View 1 Replies
View Related
Feb 3, 2007
Hi all:
I have a list of items (actually a relation in which a user has selected an item, along with a rating for the item) in an Access database table, connected to my app with a SqlDataSource and bound to a repeater. The repeater displays the items to the user along with a dropdown box to show the rating, and allow the user to update it. The page connects and displays correctly.
My problem is that when the user submits the page and I iterate through the repeater items to update each rating, the updates are not being completed in the database. The update works if I hard-code a value for the rating into the query itself, but not when using an updateparameter (pTaskRating below). In other words if I replace pTaskRating with '5', all the correct records will be found and have their ratings updated to 5. That means that the mySurveyId and pTaskId(DefaultValue) parameters have to be working, because the right records are found, but I can't seem to update records based on the DefaultValue of the pTaskRating parameter, even though I can verify that the DefaultValue is correct by placing a watch on it. It seems that my problem must be in my use of that particular parameter in the query, either in properties of the parameter or in the value assigned to it. I am extremely frustrated - any ideas would be greatly, greatly appreciated. Thanks!
Bruck
The table I'm pulling from and updating looks like this:
SURVEY_ID (Text 50), TASK_ID (Long Int), RATING_ID (Long Int)
Here's my ASPX for the main data source:
<asp:SqlDataSource ID="sqlTaskSelections" runat="server" ConnectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source="abc.mdb";Persist Security Info=True;Jet OLEDB:Database Password=xyz' ProviderName="System.Data.OleDb" SelectCommand="SELECT [SURVEY_ID], [TASK_ID], [RATING_ID] FROM [TBL_TASK_SELECTION] WHERE [SURVEY_ID] = mySurveyId" UpdateCommand="UPDATE [TBL_TASK_SELECTION] SET [RATING_ID] = pTaskRating WHERE ([SURVEY_ID] = mySurveyId) AND ([TASK_ID] = pTaskId)">
<UpdateParameters>
<asp:SessionParameter Name="mySurveyId" SessionField="SurveyId" DefaultValue="" /><asp:Parameter Name="pTaskId" DefaultValue="" /><asp:Parameter Name="pTaskRating" DefaultValue="" />
</UpdateParameters>
And here's the repeater (the Task ID and Rating are stored in hidden fields for easy access later):
<asp:Repeater ID="rptTaskSelections" runat="server">
<HeaderTemplate><table border="0"></HeaderTemplate>
<ItemTemplate>
<tr class="abctr"><td class="normal"><asp:DropDownList ID="cbRatings" runat="server"></asp:DropDownList><asp:HiddenField ID="hTaskId" Runat="server" Visible="false" Value='<%# Eval("TASK_ID") %>' /><asp:HiddenField ID="hRating" Runat="server" Visible="false" Value='<%# Eval("RATING_ID") %>' /> <%# Eval("TASK_ID") %></td></tr>
</ItemTemplate>
<FooterTemplate></td></tr></table></FooterTemplate>
</asp:Repeater>
And here's the page load and submit VB:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'BIND / LOAD RATINGS TO DROPDOWN BOXES HEREDim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentRating As HiddenFieldrptTaskSelections.DataSource = sqlTaskSelectionsrptTaskSelections.DataBind()
For i = 0 To rptTaskSelections.Items.Count - 1
cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentRating = rptTaskSelections.Items(i).FindControl("hRating")
cbCurrentRating.DataSource = sqlRatingscbCurrentRating.DataTextField = "RATING"cbCurrentRating.DataValueField = "ID"cbCurrentRating.DataBind()cbCurrentRating.SelectedValue = hCurrentRating.Value
Next
End If
End Sub
Protected Sub btnSubmitRateTasks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmitRateTasks.Click
'UPDATE RATINGS HERE
Dim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentTaskId As HiddenField
For i = 0 To rptTaskSelections.Items.Count - 1
cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentTaskId = rptTaskSelections.Items(i).FindControl("hTaskId")
sqlTaskSelections.UpdateParameters.Item("pTaskId").DefaultValue = hCurrentTaskId.ValuesqlTaskSelections.UpdateParameters.Item("pTaskRating").DefaultValue = cbCurrentRating.SelectedValue
sqlTaskSelections.Update()
Next
Response.Redirect("nextpage.aspx")
End Sub
View 3 Replies
View Related
Jan 18, 2008
hi
i have form view that retrieves a single row
i dont want to use SQLDataSource default Select/Update/Insert/Delete buttons
i am using Stored Procedures
I want to have my own buttons, the select has parameters, how to retrieve data for that parameter?
View 6 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
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
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
View Related
May 5, 2006
I have a gridview with a sqlDataSource with the SelectCommand as
"SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"
what kinda 'UpdateCommand' do I set so that ItemForSale is also updated from the grid? I tried two update statements seperated with a semicolon but that wouldn't work, any suggestions...
View 3 Replies
View Related
Oct 16, 2006
Hi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.
View 3 Replies
View Related
May 3, 2007
Hi,
I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code:
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
MembershipUser _membershipUser = Membership.GetUser();
Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog
SqlDataSource customerDataSource = new SqlDataSource();
customerDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here
customerDataSource.UpdateCommandType = SqlDataSourceCommandType.Text;
what next
I've not got a clue as to what to write next. There is a try / catch statement after this using :
rowsAffected = customerDataSource.Update();
which remains at 0 no matter what I try.
Any help greatly appreciated.
View 15 Replies
View Related
May 25, 2007
I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID". Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you.
Web Page 1:
Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
Web Page2:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @snCoDeptRowID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue" />
<asp:QueryStringParameter Name="User_ID" Type="Int32" QueryStringField ="@snCoDeptRowID" DefaultValue ="@snCoDeptRowID" />
</updateparameters>
</asp:SqlDataSource>
View 1 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
Aug 31, 2007
Hello all,
Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CAT %>"ProviderName="<%$ ConnectionStrings:CAT.ProviderName %>" SelectCommand='SELECT * FROM "COMPUTER"' UpdateCommand="UPDATE COMPUTER SET DESKTOP = :DESKTOP, TECH = :TECH, SERVICE_TAG = :SERVICE_TAG WHERE USERNAME=:USERNAME">
<UpdateParameters>
<asp:ControlParameter Name="USERNAME" ControlId="txtUserName" PropertyName="Text"/>
<asp:ControlParameter Name="SERVICE_TAG" ControlId="txtServiceTag" PropertyName="Text"/>
<asp:ControlParameter Name="TECH" ControlId="txtTech" PropertyName="Text"/>
<asp:ControlParameter Name="DESKTOP" ControlId="txtComputerName" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
