UPDATE With A DataList
Jan 20, 2006
Hi,
I'm having some troubles getting my UPDATE to work with my DataList control. I am trying to get Optimistic Concurrency working, but i'm getting an exception stating that the dictionary for oldValues is empty. I understand this is referring to the original values it compares with before performing the update, however, my question is how do I fill this dictionary in with the original values?
I have included a code sample of the sort of thing i am trying to achieve, it is just a simplified example of how I am trying to UPDATE the underlying data source. Am I on the right track? What do I need to do in order to get this to work?
Thanks
trenyboy
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = e.Item.ItemIndex;
DataList1.DataBind();
}
protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
SqlDataSource1.UpdateParameters["UnitPrice"].DefaultValue = ((TextBox)e.Item.FindControl("UnitPriceTextBox")).Text;
SqlDataSource1.UpdateParameters["UnitsInStock"].DefaultValue = ((TextBox)e.Item.FindControl("UnitsInStockTextBox")).Text;
SqlDataSource1.UpdateParameters["UnitsOnOrder"].DefaultValue = ((TextBox)e.Item.FindControl("UnitsOnOrderTextBox")).Text;
SqlDataSource1.Update();
DataList1.EditItemIndex = -1;
DataList1.DataBind();
}
protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = -1;
DataList1.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [ProductName], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ProductID] FROM [Products]" UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [UnitsInStock] = @original_UnitsInStock AND [UnitsOnOrder] = @original_UnitsOnOrder" OldValuesParameterFormatString="original_{0}" ConflictDetection="CompareAllValues">
<UpdateParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="UnitsInStock" Type="Int16" />
<asp:Parameter Name="UnitsOnOrder" Type="Int16" />
<asp:Parameter Name="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_ProductName" Type="String" />
<asp:Parameter Name="original_UnitPrice" Type="Decimal" />
<asp:Parameter Name="original_UnitsInStock" Type="Int16" />
<asp:Parameter Name="original_UnitsOnOrder" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
</div>
<asp:DataList ID="DataList1" runat="server" DataKeyField="ProductID" DataSourceID="SqlDataSource1" RepeatDirection="Horizontal" RepeatLayout="Flow" OnEditCommand="DataList1_EditCommand" OnUpdateCommand="DataList1_UpdateCommand" OnCancelCommand="DataList1_CancelCommand">
<HeaderTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<th>Product Name</th>
<th>Unit Price</th>
<th>Units in Stock</th>
<th>Units on Order</th>
<th></th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label></td>
<td><asp:Label ID="UnitPriceLabel" runat="server" Text='<%# Eval("UnitPrice") %>'></asp:Label></td>
<td><asp:Label ID="UnitsInStockLabel" runat="server" Text='<%# Eval("UnitsInStock") %>'></asp:Label></td>
<td><asp:Label ID="UnitsOnOrderLabel" runat="server" Text='<%# Eval("UnitsOnOrder") %>'></asp:Label></td>
<td><asp:LinkButton ID="LinkButtonEdit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton></td>
</tr>
</ItemTemplate>
<EditItemTemplate>
<tr>
<td><asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label></td>
<td><asp:TextBox ID="UnitPriceTextBox" runat="server" Text='<%# Eval("UnitPrice") %>'></asp:TextBox></td>
<td><asp:TextBox ID="UnitsInStockTextBox" runat="server" Text='<%# Eval("UnitsInStock") %>'></asp:TextBox></td>
<td><asp:TextBox ID="UnitsOnOrderTextBox" runat="server" Text='<%# Eval("UnitsOnOrder") %>'></asp:TextBox></td>
<td><asp:LinkButton ID="LinkButtonUpdate" runat="server" CommandName="Update" Text="Update"></asp:LinkButton> <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Cancel" Text="Cancel"></asp:LinkButton></td>
</tr>
</EditItemTemplate>
</asp:DataList>
</form>
</body>
</html>
View 1 Replies
ADVERTISEMENT
Apr 21, 2008
I try to get the display a datalist with nickname. And make changes to the nickname and then press update button (UpdateClick) to update nickname changes
Problem:
The value of the datalist did change from the datalist but just does not update the SQL 2005 Express databaseSub UpdateClick(ByVal sender As Object, ByVal e As System.EventArgs)
Dim asdf As TextBox = CType(ProfileDataList.Items(0).FindControl("txtnickname"), TextBox)
Dim asdfnickname As Stringasdfnickname = CStr(asdf.Text)
Response.Write(asdfnickname)
Dim asdfSession As String = "1"Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("Local_LAConnectionString1"))Dim myCommand As SqlCommand = New SqlCommand("ProfileUpdate", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Connection = myConnectionmyCommand.Parameters.AddWithValue("@nickname", asdfnickname)
myCommand.Parameters("@nickname").Direction = ParameterDirection.InputDim parameterCusID As SqlParameter = _New SqlParameter("@CustomerID", SqlDbType.NVarChar, 2)
parameterCusID.Value = asdfSession
myCommand.Parameters.Add(parameterCusID)Try
myConnection.Open()
myCommand.ExecuteNonQuery()Catch ex As Exception
Finally
myConnection.Close()
End Try
End Sub
<asp:TextBox ID="txtnickname" Text='<%#DataBinder.Eval(Container.DataItem, "nickName")%>' runat="server" />
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="UpdateClick" />
ProfileUpdate (Storeprocedure)USE [Local_LA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[ProfileUpdate]
@CustomerID nvarchar(2),
@nickname nvarchar(75)
AS
UPDATE CustomersSET nickName = @nickname
WHERE
CustomerID = @CustomerID
View 7 Replies
View Related
Apr 13, 2007
Hello Guys,I am really not very happy with all the possibilities you have in ASP.Net other than in Classic ASP. Back then I made a loop in which you access to a Database with your SQL-Statement and fill the Recordset into HTML-Tables. Now I cannot really do that anymore (at least not in this existing project). My task is to display additional content.We are using the Timetracker-SDK and are displaying the hours spent on any projects.Now I shall add the number of bugs, suggestions and so on that are in the database for the specific project.An ObjectDatasource is accessing to a DotProcedure (written correctly?) and displaying it in a DataList.I really do not have any knowledge to modify itSo my question: Can I add a SqlDatasource and modify the "Select-" Statement in the runtime and add the Output to the Datalist?Thanks in advance on any reply here!Below the .ASPX-Part of the Site: <asp:ObjectDataSource ID="ProjectReportData" runat="server" TypeName="ASPNET.StarterKit.BusinessLogicLayer.Project"
SelectMethod="GetProjectByIds">
<SelectParameters>
<asp:SessionParameter Name="ProjectIds" SessionField="SelectedProjectIds" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:timetrackerConnectionString2 %>"
SelectCommand="select Count(*) as Anzahl from issuetracker_issues where projectid =9 and issuecategoryid=16 and issuestatusid =17">
</asp:SqlDataSource>
<table>
<tr>
<td colspan="2">
<asp:DataList ID="ProjectList" RepeatColumns="1" RepeatDirection="Vertical" runat="server"
DataSourceID="projectReportData" OnItemCreated="OnProjectListItemCreated">
<HeaderStyle CssClass="header-gray" />
<HeaderTemplate>
Project Report
</HeaderTemplate>
<ItemTemplate>
<table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%">
<tr>
<td valign="top">
</td>
</tr>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%">
<tr>
<td width="180" class="report-main-header">
Project Name</td>
<td width="70" align="right" class="report-main-header">
Est. Hours</td>
<td width="100" align="right" class="report-main-header">
Actual Hours</td>
<td width="100" align="right" class="report-main-header">
Est. Completion</td>
<td width="100" align="right" class="report-main-header">
Issues</td>
<td width="100" align="right" class="report-main-header">
Solved</td>
<td width="100" align="right" class="report-main-header">
Reported</td>
<td width="100" align="right" class="report-main-header">
Not Qualified</td>
</tr>
<tr>
<td class="report-text">
<%# Eval("Name") %>
</td>
<td class="report-text" align="right">
<%# Eval("EstimateDuration") %>
</td>
<td class="report-text" align="right">
<%# Eval("ActualDuration") %>
</td>
<td class="report-text" align="right">
<%# Eval("CompletionDate", "{0:d}") %>
</td>
</tr>
</table>
View 2 Replies
View Related
Jan 31, 2008
Hi all, I'm trig to create a simple cart in ASP.NET/C#. I've create a table named cart in which there are 2 field (user_id and id_product). In my page product I show all product and the cart icon (this is made by an asp.net image_button). When a person click on the cart there's and event in .cs which should insert the relative product in cart table. But how a can retrieve the product id?This is my .cs page: using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class provacarrello : System.Web.UI.Page{ private string connectionString = WebConfigurationManager.ConnectionStrings["MySQLconnection"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { } protected void ImageButton1_Click(object sender, ImageClickEventArgs e) { MembershipUser mu = Membership.GetUser(User.Identity.Name); Guid myGuid = (Guid)mu.ProviderUserKey; string insertSQL = "INSERT INTO cart ("; insertSQL += "userid, id_product)"; insertSQL += "VALUES("; insertSQL += "@userid, @id_product)"; SqlConnection myConnection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(insertSQL, myConnection); //Add the parameters cmd.Parameters.AddWithValue("@userid", myGuid); cmd.Parameters.AddWithValue("@id_product", DataList1.??? <--- ??? }} Thank u in advance!
View 5 Replies
View Related
Jan 19, 2007
Hi all
I am having problem in selecting the data from my datalist. Here is teh sample code
<asp:datalist id="dlAttrName" runat="server" OnItemCommand="dlAttrName_SelectData" DataKeyField="attrID">
<ItemTemplate>
<asp:LinkButton ID="lbSelect" Runat="server" CommandName="Select" style="display:none">Select</asp:LinkButton>
<%# Container.DataItem("attrDef1")%>
</ItemTemplate>
</asp:DataList>
Private Sub dlAttrName_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles dlAttrName.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
'Add eventhandlers for highlighting
'a DataListItem when the mouse hovers over it.
e.Item.Attributes.Add("onmouseover","????")
e.Item.Attributes.Add("onmouseout", "????")
'Add eventhandler for simulating
'a click on the 'SelectButton'
e.Item.Attributes.Add("onclick", ?????????????????????, String.Empty))
??????????????????? this should have something like this
Me.Page.ClientScript.GetPostBackEventReference(e.Item.Controls(1)
But I am not able to get the ClientScript option, I don't know which namespace should I import?
I don't know whether I am right or not.
Its urgent please reply back soon,any help will be useful.
Thanks!!!!!!!
View 1 Replies
View Related
Nov 29, 2007
Hey,
I have two questions:
1- In my page there should be a list of all system users, each of them may have an image or may not.
So what I need to do is how to make the next SQL query return a default ImageURL - e.g. "noAvator.jpg" - in case of null image value?
SELECT UserName , image, notesFROM Members
2- When I try to write an image url in the SQL Server 2005 Management Studio - as "images/noAvator.jpg" or any other name - it always through me this error messagebox:
The changed value is not recignized as valid..NET Framework Data Type: Byte[]Error Message: You can not use the Result pane to set this Field data to value other than NULL.
Why is that? is there a problem with the format of typing the image url?
I appreciate your help!
View 4 Replies
View Related
Dec 25, 2007
Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%> <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList> </body></asp:Content> Thanks a lot.
View 5 Replies
View Related
Apr 22, 2006
Everything works great on my development box. I am using GoDaddy for production (ASP.Net v2, SQL 2000).
I am not receiving any errors, so I am stumped; no data from the database is displaying on the GoDaddy pages.
I updated the connection string in web.config to this:
< add name="snsb" connectionString="
Server=whsql-vXX.prod.mesaX.secureserver.net;
Database=DB_42706;
User ID=username;
Password=pw;
Trusted_Connection=False" providerName="System.Data.SqlClient" / >
But I am unsure if this is the issue?? Any insights? This is the page I am working on: www.sugarandspicebakery.com/demo/bakery/default.aspx. So, the page displays fine, but it should be showing data from the database. This particular page uses a DataList with ItemTemplate. There is definitely data in the database, and I have even ran the same exact query from the code using the Query Analayzer on GoDaddt and it returned results
I know there isn't much info to go by, but I am hoping someone has some insight since I have been trying to figure this out for days now!
Thank youJennifer
View 2 Replies
View Related
Oct 6, 2006
Hello!I have two tables:Pressreleases:| Pressrelease_ID | PressDate | FilePath | Title |PressLinks:| PressLink_ID | PressLinkDate | PressLinkUrl | PressText |I would like to select the the TOP 3 with the most recent dates from either Pressreleases or PressLinks, and present them in a DataList. How do I do that? Selecting from one table is no problem:SELECT TOP 3 Pressreleases.PressDate, Pressreleases.Filepath, PressReleases.Title FROM Pressreleases ORDER BY PressDate DESCHow do I select from both tables and rename the columns to Date, Path, Text so that I can use it in a Datalist?Thank you in advance!
View 4 Replies
View Related
Aug 26, 2007
Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message.
FROM | SUBJECT | User Photo | || | Subject || User Name | |
Here is the list of the relevant 4 tables of my database and the relevant fields.....
aspnet_Users tableUserId (used to link to Member table)UserName
Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table)
Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive)
Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max))
So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far.....
SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember
Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.
And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol.
This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.
View 22 Replies
View Related
Sep 26, 2007
I am coding a DataList control for 2 column data from a stored procedure. The 1st column isprogramName and the 2nd is programLink. I used Edit Template -> Edit DataBonding to add<h ref="http.....>, but can not get it right. Please help. Also, what is the Parameter Sourcefor using Logon User ID as a stored procedure paramter when configuring a SqlDataSource? None, cookie, control,..? TIA,Jeffrey
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Style="z-index: 16;left: 29px; position: absolute; top: 82px"><ItemTemplate>programName:<asp:Label ID="programNameLabel" runat="server" Text='<%# Eval("programName") %>'></asp:Label><br />programlink:<asp:Label ID="programlinkLabel" runat="server" Text='<%# Eval("programlink") %>'></asp:Label><br /><br /></ItemTemplate></asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:iTemplatesConnectionString %>"SelectCommand="MyPrograms" SelectCommandType="StoredProcedure"><SelectParameters><asp:Parameter Name="meid" Type="Int32" DefaultValue="3532" /></SelectParameters></asp:SqlDataSource>
View 4 Replies
View Related
Jan 2, 2008
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
View 5 Replies
View Related
Nov 11, 2007
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
View 2 Replies
View Related
Feb 16, 2006
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies
View Related
Oct 20, 2006
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
View 1 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Jul 23, 2005
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies
View Related
Nov 9, 2007
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 Replies
View Related
Jul 23, 2006
I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
The Stored Procedure:
ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)
/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN
View 2 Replies
View Related
May 13, 2008
I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
View 23 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
Feb 18, 2006
I do realize that his could be posted in a few spots but I think the answer is in the SQL.
I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting.
ex:
<asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox>
I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together.
I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters.
Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update.
There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it.
wbochar
View 1 Replies
View Related
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
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
Jul 30, 2007
Hello,
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
View 6 Replies
View Related
Feb 8, 2008
Hello
I have interesting situation with one of my update statement.
Update takes 2 min to run and usually updates 20000 rows.
However lately update executes for longest time - 10 hours.
After I reindex table the updates run fine again
Please advice what can cause this
I do not think reindexing table before every update is good idea
We are running SQL 2000 SP4, Windows 2003 Server
Thank you in advance for all your help
Armine
View 17 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Oct 19, 2005
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies
View Related
Mar 7, 2005
OK,
This is an Update that I have working, But what do I do, if the customer does not exist already it doesn't add the customer? How should I remedy this? if the customer does exist works great.
UPDATE AC
SET CustId = Left (CustomerId,10),
CustName = Left (CustomerName,25),
Addr1 = Left (Address1,25),
Addr2 = Left (Address2,25),
City = Left (ca.City,15),
Region = Left (State,2),
PostalCode = Left (Zip,5)
FROM RIO.dbo.tblArCust AC INNER JOIN
(SELECT CustomerCode, MAX(LastUpdatedDate) MaxDate
FROM COFFEE.dbo.vueCustomerAddress
GROUP BY CustomerCode) V
ON V.CustomerCode = AC.CustId
INNER JOIN COFFEE.dbo.vueCustomerAddress CA
ON CA.CustomerCode=V.CustomerCode AND
MaxDate=LastUpdatedDate
WHERE CA.addresstypeid = 1
View 1 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
