A SQL Server 2005 stored procedure expects a parameter UserID depending upon which it retrieves the no. of records & OrderIDs corresponding to the UserID from a DB table (note that OrderID & UserID are two of the columns in the DB table). So for e.g. consider a user whose UserID=6 & the DB table has 3 records where UserID=6. In other words, there are 3 OrderID records of the user whose UserID=6, say, OrderID=8, OrderID=17 & OrderID=29. The stored procedure will finally return 2 columns - the OrderCount (which is 3 for UserID=6) & the OrderID (which will be 8, 17 & 29 for UserID=6). This is the stored procedure:
ALTER PROCEDURE dbo.OrderCount
@UserID int
AS
DECLARE
@OrderCount int
SET @OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID= @UserID)
SELECT @OrderCount AS OrderCount, OrderID
FROM
NETOrders
WHERE
UserID = @UserID
In a VB class file, I am invoking the stored procedure in a function named GetOrderCount which returns a SqlDataReader back to the calling ASPX page. I want the ASPX page to display the 3 OrderIDs of UserID=6 in a Label control. Unlike the DataBinding controls like DataList, DataGrid, Repeater controls, the Label control will not automatically loop through the recordset.
So I tried to accomplish this using a For....Next loop
Dim i As Integer
Dim sqlReader As SqlDataReader
iUserID = Request.Cookies("UserID").Value
'ShopCart is the name of the class in the VB class file
boShopCart = New ShopCart
sqlReader = boShopCart.GetOrderCount(iUserID)
While (sqlReader.Read)
If (sqlReader.GetValue(0) > 1) Then
pnlLinks.Visible = True
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text = sqlReader.GetValue(1)(i)
Next i
Else
pnlLinks.Visible = False
End If
End While
But this generates the following error:
No default member found for type 'Integer'.
pointing to the line
lblLinks.Text = sqlReader.GetValue(1)(i)
in the above shown ASPX code. Can someone please correct me & suggest how do I loop through the recordset so that I can display the records in a Label control?
Hello. I have a multi value parameter in my report. I want to know how can I display all of the selected values in this parameter in a text box. Whan I try to use this parameter in a textBox it automaticly take the value: Parameter.param.Value(0) Whice take only the first select value.
I want it to either display the label (All) or the name of the month. I get an error when All is the parameter selected but not when any month is selected. If I removed the MonthName() function, I don't get an error but I also don't get the month name.
Below is code for inserting data into the database that I know works, so I thought if I replace "Insert" with "Select" I could retrieve a specific row from my database and present it in a label or text box but nothing happened. How can I retrieve a row or rows from a database using vb code and display it in a textbox? I'm using visual web developer 2008 which is similar to 2005. Thank you.
Dim UserDatasource As New SqlDataSourceUserDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ToString()
UserDatasource.InsertParameters.Add("username", Context.User.Identity.Name) UserDatasource.InsertParameters.Add("comments", txtSearch.Text)UserDatasource.InsertParameters.Add("points", points) UserDatasource.InsertParameters.Add("ipaddress", Request.UserHostAddress)UserDatasource.InsertParameters.Add("datatimestamp", DateTime.Now()) Dim rowaffected As Integer = 0
Try rowaffected = UserDatasource.Insert()MsgBox("Thanks for the post", MsgBoxStyle.OkOnly, "Post Executed") txtSearch.Text = ""Catch ex As Exception MsgBox("Please sign in or sign up to post comment", MsgBoxStyle.OkOnly, "Login Error") End Try
Hi,What's the 'new' declarative way to fetch one field from a SQL table and display the result in a Label control?I have a users table with a fullname field. I want to query the table and retrieve the fullname where the usertable.username = Session("loggedinuser")I can make the SqlDataSource ok that has the correct Select query, but not sure how to bind the label to that.thanks,Bruce
declare @table table ( ParentID INT, ChildID INT, Value float ) INSERT INTO @table SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
I have the following sql table and would like to group the results by "StoryTitle" to display in a datalist. The Storytitle field in the datalist is a LabelID StoryTitle StoryAuthor Rating StoryID Comments 1 About Me goodyone 6 20 Great Story 2 About Me goodyone 5 20 Love your work 3 Hello World magicme 6 26 What a Story 4 Hello World magicme 7 26 This Reminds me of... I know i have to do something in the SQL Datasource statement. Not sure how to do it. here is my statement below <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BrillConnectionString1 %>" SelectCommand="SELECT * FROM [iaw.comments]"> </asp:SqlDataSource>
TableName: Order_Archive Fields: orderid load_date filename order_date dollar
I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.
m.material_subtotal, m.cec_job_id,m.location FROM material m left outer join Other_Project op on m.project_id=op.other_proj_id left outer join Daily_Time_Entry dl on dl.project_id =op.other_proj_id inner join Employee_Mgmt emp on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec ON dl.cec_job_id = cec.cec_job_id where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and (dl.project_type='Other Project' and m.project_type = 'Other Project') and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and (dl.project_id='18')
Fields are Dt_id int Daily_type varchar Dt_date datetime Emp_id int Project_type varchar Project_id int Time_st int Time_ot int Time_dt int Records in Daily_Time_Entry are as follows
Where dt_date is in dd/mm/yyyy format Also in project_type field -- AB Corporate is taken for Ab_Corporate_Project , AB Plant for Ab_Plant_Project, Other Project for Other_Project This means if Project_type = AB Corporate then search into Ab_Corporate_Project Else if Project_type = AB Plant then search into Ab_Plant_Project Else if Project_type = Other Project then search into Other_Project
Now I want to display records as per datewise suppose I want to view records by date 13/03/2008
I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.
I tried this code but didn't work..
[code] SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection); [/code]
How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique. State UserName First Last ID City CountTX Kkeaton Kathryn Keaton 1001 Dallas 2TX KakiKeaton Kathryn Keaton 1001 Dallas 2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks Craig
I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.
There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.
I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.
For example:
PromoID StartDate EndDate ------- --------- ------- PROMO1 1/1/2004 1/1/2006 (1st Active Promotion) PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion) PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)
Here's a query I am using to display all active promotions:
select pl.promoid, pr.startdate, pr.enddate, pl.locid, pl.skuid, pl.promoprice, pl.disclevel from promolocation pl inner join promotion pr on pl.promoid = pr.promoid where pr.enddate >= getdate()
I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.
SELECT nvl(field1, 0), field2, field3, nvl(field4, 0), nvl(field5, 0), nvl(field6, 0) FROM TBL1 ------------------------------------------- Result: Record count 3000.
Only field3 is populated everything else is null.
select field3 from tbl1 group by field2 - record count = 48
I would like to run a SELECT statement using the SQLCMD, however, I do not like the count of records be displayed after the execution. How do I do that?
And I have a fact table that has 2 rows that join back to the first 2 dimension rows like this:
BrokerDimId Amt 1 $100 2 $200
I want to create the mdx so that I get all of the dimension rows, even if there is no record in the fact table for it and just default the amount to 0, like this
I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below
ID StudentName BirthDate 1 ABC 2002-12-25 2 VWX 2002-01-01 3 STU 2002-02-03 4 PQR 2002-03-05
In our organization we have fixed two weeks menu. On our intranet i have database entries with two weeeks menu without dates. I want first six entries to appear in one week and next six entries to appear in another week. How can i achieve this with SQL query.
SELECT NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS, TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS FROM [USAGE]
where <criteria>
I want to show the topmost 10 records. For some criteria I get the results in the chart.
But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records.
In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart.
If I remove the TopCount clause then I get the above message, which is obvious.
So how do I acheive the same message but at the same time limiting the records to 10?
How can I acheive this in my scenario? Can something be done at the query end?
I have created a table and want to display a datset. One of the fields in the dataset is 'month' which I need displayed across the top of the report. The other information should make up the rows.
How do I get it to list the Month across the page rather than down?
I use an embedded ReportViewer control as a LocalReport in a web form. The report displays the results in four pages and I need to display them all in one page (without any pagination). Is there a way to disable pagination? Also, what do I need to do to show the print button in toolbar?
I have a new installation of SQL Server 2000 Dev Edition on a Win2K3 Standard Edition Server that I used for development. I just set this machine up in th last week and installed all Win2K3 patches and then installed SQL2K and SP3a. I have a single named instance. I just noticed today that I cannot view table data or use the Query part of EM. When I right click a table and select Open Table->Return All Rows it gives me an error dialog "An unexpected error happened during this operation". While the EM is diplaying this dialog the EM screen looks like Internet Explorer and says "Action Canceled - You might not have permission to view this directory or page using the credentials you supplied." I believe that this is a EM issue as I cannot view table content on other remote server. ANy ideas? Might this be an IE security patch disallowing some connectivity ?--Frank--Message posted via http://www.sqlmonster.com
I'm using the query desinger in ASP.NET , however the number of recordsin the resultset are not displaying, so I cut and paste it into Queryanalyzer which is silly.How do I set this in the output window, or result grid?Thanks Moe
Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output
select Standard, Total, MonthName from (SELECT Standard, COUNT(Standard) AS Total, datename(month, ReportDate) as [MonthName] FROM CPTable where ReportDate >= @ReportDate
