Updating/getting Values From Datagrid In C# With Variable Parameters
Oct 24, 2006
Hi
I am new to the world of aspx, .net and C#.
In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine.
However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.
However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place.
Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.
Many Thanks
T
View 1 Replies
ADVERTISEMENT
Jun 29, 2007
Hello, I have a datagrid which is populated with data from an MS SQL server database. When I run an update query it always throws an exception - what is the most likely cause for this given that I am using the code below: 1 public void DataGrid_Update(Object sender, DataGridCommandEventArgs e)
2 {
3 String update = "UPDATE Fruit SET Product = @ID, Quantity = @Q, Price = @P, Total = @T where Product = @Id";
4
5 SqlCommand command = new SqlCommand(update, conn);
6
7 command.Parameters.Add(new SqlParameter("@ID", SqlDbType.NVarChar, 50));
8 command.Parameters.Add(new SqlParameter("@Q", SqlDbType.NVarChar, 50));
9 command.Parameters.Add(new SqlParameter("@P", SqlDbType.NVarChar, 50));
10 command.Parameters.Add(new SqlParameter("@T", SqlDbType.NVarChar, 50));
11 command.Parameters["@ID"].Value = DataGrid.DataKeys[(int)e.Item.ItemIndex];
12 command.Connection.Open();
13
14 try
15 {
16 command.ExecuteNonQuery();
17 Message.InnerHtml = "Update complete!" + update;
18 DataGrid.EditItemIndex = -1;
19 }
20 catch (SqlException exc)
21 {
22 Message.InnerHtml = "Update error.";
23 }
24
25 command.Connection.Close();
26
27 BindGrid();
28 }
All of the row types in MS SQL server are set to nvarchar(50) - as I thought this would eliminate any inconsistencies in types. Thanks anyone
View 5 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
Nov 4, 2015
CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
View 6 Replies
View Related
Mar 18, 2007
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.
One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).
If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.
What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
View 8 Replies
View Related
Sep 5, 2007
Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select €œselect all€? option from the parameter list all 20 parameters value should be false.
I will be very thankful.
Regards,
Faisal Saleem
View 5 Replies
View Related
Jul 20, 2005
Hi;I have a sqlserver database with a field that is of TEXT datatype (not my decision) that is used to store comments from users on one ofour websites.For various reasons I need to make code that will clean the text inthis field( for example purposes mytable.comment ) so that there are no singlequotes in it.I am experiementing with making mytable.comment a mix of 'B' and 'Q'such that all 'Q's are replaced with 'B's.The code below works.......once.If I run it more then once no further 'Q's will get replaced.The problem is with the @index variable I am using that tellsUPDATETEXT where to update.It isn't changing.Any ideas would be greatly appreciatedSteve------------------------------------------------------------------------DECLARE @ptrBlurb varbinary(16), @index intselect @ptrBlurb=TEXTPTR(comment), @index=PATINDEX('%Q%',comment)frommytablewhere PATINDEX('%Q%', comment) <> 0 andprojid = '00013'UPDATETEXT mytable.comment @ptrBlurb @index 1 'B'select projid, comment from mytable-------------------------------------------------------------------------
View 5 Replies
View Related
Jul 29, 2007
Hello People,
I'm using SSIS and I want to send a report to the admin about how many rows are new, updated or unchanged in a mail. Everything is working fine except that the values that are sent are always zeros. I'm using a Row Count Transformation and configuered it to update the approperiate User Variable which I priviously created. However, the initial values in these variables are always Zeros. What can I do?
Thanks,
SHIKO
View 12 Replies
View Related
May 7, 2015
I have a column being added with VB.net, but I can't figure out the syntax. I get the error No Value given for one or more required parameters. But no clue which one. Below is the code I am attempting.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim conp As String = "SELECT * INTO [Input] FROM [Text;DATABASE="
Dim aCon As String = "W:Glenn-123456VDDDataTest.mdb"
Dim scon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
Dim Conn_1 As New OleDbConnection(scon & aCon)
[Code] .....
View 3 Replies
View Related
Jul 17, 2007
Hi,
I updated a report adding parameter expressions. A linked report based on the report I updated didn't get the parameter expressions when I deployed the base report. Anybody else having that issue? It's not much of a problem. I just have to recreate the linked report which is a nusance.
Thanks,
DD
View 5 Replies
View Related
May 29, 2007
Not receiving any errors. The update runs perfectly on all fields, except for the added parameter during the sub. I need to change the hidden field's value after parsing out several fields in the form and generating a logfile entry of sorts. This needs to happen after all the form fields are updated, but before the update is executed.
aspx: (relevant parts only...)<asp:SqlDataSource ID="TicketDetails" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"UpdateCommand="UPDATE Tickets SET TicketSuspense = @TicketSuspense, TicketPriority = @TicketPriority, TicketLastUpdated = CURRENT_TIMESTAMP, TicketStatus = 'Assigned', TicketTechnicianNotes = @TicketTechnicianNotes WHERE (TicketID = @TicketID)"><UpdateParameters><asp:Parameter Name="TicketPriority" Type="String" /><asp:Parameter Name="TicketSuspense" Type="DateTime" /><asp:Parameter Name="TicketID" Type="Int32" /></UpdateParameters></asp:SqlDataSource>
codebehind: (once again, the relevant parts only)Protected Sub TicketDetails_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles TicketDetails.UpdatingTicketTechnicianNotesHiddenField.Value = "some text..." & TicketTechnicianNotesHiddenField.ValueTicketDetails.UpdateParameters.Add(New Parameter("TicketTechnicianNotes", TypeCode.String, TicketTechnicianNotesHiddenField.Value))End Sub
Thanks,
- Brad
View 3 Replies
View Related
Feb 14, 2005
I have a table variable into which I insert the results of a select statement. Based on the records held in the table variable I then want to update a field in one table and insert the records in the table variable into another table.
This works fine in a self contained test:
declare @table table(electionchangeid int)
declare @anothertable table(ID int)
insert into @table(electionchangeid) values(1036276)
update electionchange
set exportdate = GETDATE()
from electionchange ec
join @table t on t.electionchangeid = ec.electionchangeid
insert into @anothertable
select * from @table
But does not work within my sp .... (see next post).
It doesn't generate an error. It just doesn't update or insert any records. I would think that it was a scope issue, except that I can do a select on the table variable and see that it does contain records.
I would be very interested to hear people's thoughts on this.
Regards
Emma
View 1 Replies
View Related
Mar 14, 2007
Hi,
I'm having some trouble with some variables in my package.
A brief overview:
My package grabs all the data from an Excel sheet and based on several factors, divides the data up into rows and inserts them into a database.
The Excel sheet has certain ranges of cells that determine how I split the data (and therefore which table the data will go into).
My package is structured as follows:
I. Control Flow:
1. Data Flow Task:
a. OLE DB Source - grabs all data from Excel sheet
b. Script Component - adds a rowcount column to each row, determines the ranges of the various sets of data and sets some variables to the row numbers of the ranges.
c. Conditional Split - splits the data by comparing each row number to the variables with the range row numbers in them
d. Various Flat File destinations (to be replaced by something saving to a table).
The problem I am having is that the Conditional Split doesn't seem to get the correct values of the variables once they are changed in the Script Component.
I know that the Conditional Split is setup correctly as if I put the values for the variables in as defaults the Conditional Split works correctly .
Inside of "
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
" in my script component, as I'm adding the rownumber, I search for specific values to determine if the row represents the beginning or end of a range. If it meets the criteria, I take the row number and and save it to a public integer (of the Script Component)
After I've added my row numbers, inside of "Public Overrides Sub PostExecute()" I have the following:
Public Overrides Sub PostExecute()
Me.ComponentMetaData.FireInformation(0, "Changed Variable!!!", "FirstSalesRow: " + CStr(MyBase.Variables.StartRange1), "", 0, False)
'set our variables
MyBase.Variables.StartRange1= iStartRange1
MyBase.Variables.EndRange1= iEndRange1
Me.ComponentMetaData.FireInformation(0, "Changed Variable!!!", "FirstSalesRow: " + CStr(MyBase.Variables.StartRange1), "", 0, False)
End Sub
Now, when the Script component finishes and I go to the Execution results, I can see that the first "Me.ComponentMetaData.FireInformation()"
returns the default value of the variable, 0.
But the line below it is the second "Me.ComponentMetaData.FireInformation()" and it clearly shows the correct variable value.
I have checked that all of my variables in the Script Component are in the "ReadWriteVariables" property, all variables are "ReadOnly = False" as well and are scoped at the package level.
This has lead me to believe that the Conditional Split task is grabbing the value of the parameter prior to the begin of the Data Flow itself.
Is that correct?
If so, should I be able to work around this by having one Data Flow with a script component to set the variables and output an in memory dataset, then have a second Data Flow with the Conditional Split in it?
Please let me know if you need any more info to help.
Thanks!
View 2 Replies
View Related
Nov 2, 2007
Is it possible to update the value of a user defined variable within the DataFLow in SSIS. I am aware you can update a variable using a script task in the Control Flow, but how about the DataFlow?
Thanks for any help in advance.
View 1 Replies
View Related
May 14, 2008
Hi!
I am trying to retrieve and update data. I am retrieving the data into the respective controls such as textboxes. The codes are follow:
Retrieve data - working
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=" & "@ID", sqlConn)
Dim paramID As New SqlParameter("@ID", SqlDbType.Int)
paramID.Direction = ParameterDirection.Input
com.Parameters.Add(paramID)
paramID.Value = e.Value
sqlConn.Open()
Dim reader As SqlDataReader = com.ExecuteReader()
While reader.Read()
StartTimeTextBox.Text = reader("StartTime")
EndTimeTextBox.Text = reader("EndTime")
CompanyNameTextBox.Text = reader("CompanyName")
PurposeTextBox.Text = reader("Purpose")
AccountManagerTextBox.Text = reader("AccountManager")
PresenterTextBox.Text = reader("Presenter")
ColorDDL.SelectedValue = reader("ColorCode")
StatusRadioButtonList.SelectedValue = reader("Status")
CommentTextBox.Text = reader("Comments")
End While
update the data - NOT WORKING
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim cmd As SqlCommand = New SqlCommand("UPDATE Demo_Theatre_DB SET StartTime = @StartTime, EndTime = @EndTime, CompanyName = @CompanyName, Purpose = @Purpose, AccountManager = @AccountManager, Presenter = @Presenter, ColorCode = @ColorCode, Status = @Status, Comments = @Comments WHERE ID=@ID")
cmd.Parameters.AddWithValue("StartTime", StartTimeTextBox.Text)
cmd.Parameters.AddWithValue("EndTime", EndTimeTextBox.Text)
cmd.Parameters.AddWithValue("CompanyName", CompanyNameTextBox.Text)
cmd.Parameters.AddWithValue("Purpose", PurposeTextBox.Text)
cmd.Parameters.AddWithValue("AccountManager", AccountManagerTextBox.Text)
cmd.Parameters.AddWithValue("Presenter", PresenterTextBox.Text)
cmd.Parameters.AddWithValue("ColorCode", ColorDDL.SelectedValue)
cmd.Parameters.AddWithValue("Status", StatusRadioButtonList.SelectedValue)
cmd.Parameters.AddWithValue("Comments", CommentTextBox.Text)
Try
sqlConn.Open()
cmd.ExecuteScalar()
Catch ex As Exception
InsertMsgLabel.Text = ex.ToString
End Try
End Sub
I am getting the following error whenever I click update button.
"ExecuteScalar: Connection property has not been initialized"
Your help will be appriciated!
Thanks!
View 24 Replies
View Related
May 3, 2006
I am trying to update a package variable. The package consists only of a script task and a package user variable. I have included the variable, myVar (scope: package; type: string), in the ReadWriteVariables property of the script task.
The only code I have used, in Public Sub Main, is:
Dts.Variables("myVar").Value = "2"
The package runs successfully but the variable does not change. I thought that maybe the underlying value really does change even though the value as seen in the package variables window does not (I tested this in another package/solution but it does not seem to - not even during runtime).
I also tried running the variabledispenser method but this resulted in the package running continuously until I stop debugging.
Any suggestions greatly appreciated.
Regards,
Puzzled Again
View 3 Replies
View Related
Apr 29, 2007
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ 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 runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
View 1 Replies
View Related
Jul 8, 2015
updating a recordset contained in an System.Object variable during runtime.
I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.
Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.
My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?
View 5 Replies
View Related
Mar 9, 2004
I have table A which has and accountid,df_date1,df_date2. The table is a demographic one which has 1 record for each account
I have a table B which I need to populate from the first df_date1 fields in table A. Table B which is normalized and has an accountid and a df_date1 field but may have several records per accountid. I need the max(date) from this table. I wanted to do an update statement like below
update A
set df_date1
= max(df_date1) from b
where a.account_id = b.account_id
I get the error message
Server: Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement.
Is there another way to do this with a subselect and update?
View 2 Replies
View Related
Jun 23, 2008
how to use update statement to update fields in a table that has Null values
update sheet1 set [Middle Initial]='' where isnull([Middle Initial],'')=''
this statement some times works sometimes not.
View 7 Replies
View Related
Nov 7, 2007
I have a table named 'Personal' that has a field that contains values that represent colors (ex: BLACK, BROWN, BLUE, etc.). How do I run an update statement to update the values to defined set. For example (BLACK = BLK, BLUE = BLU, etc.)?
Thanks
View 5 Replies
View Related
Nov 16, 2007
Hi
I'm new to SQL and have a problem with the following script:-
INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)
VALUES 2311, 19219, 'BRAN', '01/12/2007', 'Jon')
The above script works OK on a one-off basis but....
Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).
Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?
I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.
Thanks for your help.
Jon
View 6 Replies
View Related
Feb 14, 2008
I have a table like that:
ColumnA ColumnB ColumnC
-------------------------------
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown
John Brown John.Brown
George Gotham George.Gotham
I want to update duplicated values at ColumnC like:
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford1
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown1
John Brown John.Brown2
George Gotham George.Gotham
How can i do it?
Thanks in advance!
Note: Table is for creating email aliases from names...
View 2 Replies
View Related
May 2, 2008
SELECT
#followups.suspectid,
#followups.planid,
cond4,
cond5,
cond6,
cond7,
cond8,
tbpdmmembers.firstname tbpdmmembers_firstname,
dbo.GetMemID_PartC_D(#followups.hic,#followups.IsPart_C) tbpdmmembers_MemberID,
--tbpdmmembers.Plan1 tbpdmmembers_MemberID,
tbaddress.address1 tbaddress_address1,
tbaddress.address2 tbaddress_address2,
tbaddress.city tbaddress_city,
tbaddress.state tbaddress_state,
tbaddress.zip tbaddress_zip
from #followups
I want to check if the tbaddress.address1 tbaddress_address1 if it is =null or empty then make it = tbaddress.address2 tbaddress_address2, at the same time I make the tbaddress.address2 tbaddress_address2, null. Otherwise leave it as it is.
Actually, I am working with the report and the tbaddress.address1 carrying the apt number. if there is no aprt # then the line print blanks. so that brings the empty line between the street address and the citystate line.
That is what i am tryig to cover. There could be a way to do this from the crystal report but I am thinking of doing this way cause I can't get the solution to do it from the crystal report.
View 5 Replies
View Related
May 16, 2008
Hello, I have been SSIS for a bit now, but really only it its most straight forward simple tasks I have the following issue which for the life of me I can't seem to sort out
1)Excel Source - - -> all data pushed into a initial SQL table
2) Data in initial SQL table gets sorted, certain fields get made UPPER , right trim that sort of thing
3) now I select a set of distinct values of a certain column in the SQL table and get a list of maybe 60 results
these results I want to enter into a table in a different database on same server, the table in the destination db has a identity column and a value column
adding / appending all the distinct value from the import data works fine,
but I want to add only the values which are not already in the destination table what is the best way to update the destination table, should I be using stored proc or is there a easier way in SSIS to do this
Thanks
View 1 Replies
View Related
Sep 19, 2007
Hi,
I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement.
I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'."
The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?
CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int)
AS
BEGIN
WITH SearchResults AS(
UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
)
SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END
Thanks in advance for any help.
View 3 Replies
View Related
Apr 5, 2007
In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
E.G.
AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0))
FROM facttable FT
LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID
LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID
(In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
View 2 Replies
View Related
Oct 13, 2007
Hi
I have a table containing:
ProductCat Product Qty NewQty
TypeA ProductA -10
TypeA ProductB 5
TypeB ProductX 8
TypeB ProductY -5
I need to get rid of negative values in the same ProductCat by adding surplus values to the negative values in the same ProductCat ie. This is what I ant the table to look like.
ProductCat Product Qty NewQty
TypeA ProductA -10 -5 (Add ProductB to ProductA in the same ProductCat to reduce the deficit)
TypeA ProductB 5 0 (Due to the fact that I added 5 to ProductA NewQty must be reduced by 5)
TypeB ProductX 8 3
TypeB ProductY -5 0 (Deficit only has to be reduced to 0 where possible therefor the balance
of 3 for ProductX)
Note the values in Qty can stay as the same. I am interested in the New values.
Could someone please help me with the correct method of doing this?
Thank you.
View 2 Replies
View Related
Feb 18, 2008
I get a Must Declare Scalar Variable for CompanyName error. Please help. Thank you. datasource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnString").ToString()
datasource.UpdateCommand = ("UPDATE Company SET [CompanyName] = @CompanyName), = @Email, [PhoneNumber] = @PhoneNumber, [WebsiteName] = @WebsiteName WHERE ([CompanyID] = " & Request.QueryString("CID"))datasource.UpdateParameters.Add("@CompanyName", txtName.Text)
datasource.UpdateParameters.Add("@Email", txtEmail.Text)datasource.UpdateParameters.Add("@PhoneNumber", txtPhoneNumber.Text)
datasource.UpdateParameters.Add("@WebsiteName", txtWebsite.Text)
datasource.Update()
View 4 Replies
View Related
Oct 6, 2006
Im trying to update the values int the database and the query is executed correctly but database is not updated and the returned message is one, I really dont know whats causing this here is my code.1 private void UpdatetheDatabase(string connection)
2 {
3 SqlConnection connect = new SqlConnection(connection);
4 SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " +
5 " SET [Name]=@Name,[City]=@City, " +
6 " WHERE [ID]=@ID", connect);
7 update_customer_details.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = txtCustomerName.Text;
8 update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = txtCity.Text;
9 update_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ID;
10 connect.Open();
11 int x = update_customer_details.ExecuteNonQuery();
12 connect.Close();
13 StatusLabel.Text = x.ToString();
14 }
your help will be highly appreciated.
View 7 Replies
View Related
May 26, 2000
I am looking for suggestions on how to accomplish the following task with the least amount of hand keying that I can get away with.
I have a main table named Office and 4 other tables that have the Office table ID field value in them.
I have been given a new set of ID values that need to replace the values that are currently in the Office table and then update the ID field in the other 4 tables.
I have only thought of 2 solution and I don’t like either one.
1 Add a new column to the Office table and key in the new ID’s then go through the pages that reference the old ID field and change the SQL queries to use the new field.
2 Change the value of the current Office ID field to the new value. Search the other 4 tables for the old value and then update them to the new value.
Anyone got a better Idea?
View 1 Replies
View Related
Aug 25, 2004
Hello!
I have a "current" table which users update on a daily basis, it holds forecast data. This table is designed to only hold 7 days of data and is therfore always rolling over by date.
What I want to do is have a procedure that copies this "Current" table to a "History" table every day so the historical information is stored. The copy is simple, but how do I check for and over ride values that are already in the "history" table with the newest values from the "current" table because the "current" values have the possibility of changing a few times while the forecast is updated. I can define a primary key but am not sure how to check for and update the necessary values?
Any help??
Many thanks!
View 1 Replies
View Related
