Hi,I have table (tbl_a) that get it values form three Dropdown List and the PK of this table is FK in different table(tbl_b).Since tbl_a get its values from three Dropdown List the number of rows in this table is limited to the number of combinations the three Dropdown List is offer (in any case the number of raws in that table will be final).Assuming tbl_a and tbl_b are both empty.
The problem is that if i want to insert row to tbl_b i have to check first if there is a row that holds the values (or combination of values) i am getting from the Dropdown List in tbl_a .Then, if such row is exist i have to get the PK of that raw and insert it to tbl_b (as FK),if such row does not exist, i have to insert it and then get the PK of this row and insert it to tbl_b (as FK).
In the end, tbl_a will have all the combination the three Dropdown List can offer so checking if raw exist in this table will not be necessary.
In my opinion this is not effective way to do that.Can someone offer me better way to do it?
Hi, I have table (tbl_a) that get it values form three Dropdown List and the PK of this table is FK in different table(tbl_b). Since tbl_a get its values from three Dropdown List the number of rows in this table is limited to the number of combinations the three Dropdown List is offer (in any case the number of raws in that table will be final). Assuming tbl_a and tbl_b are both empty.
The problem is that if i want to insert row to tbl_b i have to check first if there is a row that holds the values (or combination of values) i am getting from the Dropdown List in tbl_a . Then, if such row is exist i have to get the PK of that raw and insert it to tbl_b (as FK), if such row does not exist, i have to insert it and then get the PK of this row and insert it to tbl_b (as FK).
In the end, tbl_a will have all the combination the three Dropdown List can offer so checking if raw exist in this table will not be necessary.
In my opinion this is not effective way to do that. Can someone offer me better way to do it?
I am trying to populate a dropdownlist from a sql data source in my codebehind using c#. I thought I had the code correct but I keep getting the following error and I am stuck: "Only assignment, call, increment, decrement, and new object expressions can be used as a statement" Here is the code:protected void populateOppNameList(){ SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);SqlCommand cmd = new SqlCommand("SELECT empname FROM opportunity WHERE (username = " + userName + ")", conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); try { conn.Open();DataSet ds = new DataSet();DropDownList ddl_OppNames = (DropDownList)FormView1.FindControl("dropdownlist1"); ddl_OppNames.DataSource = ds; ddl_OppNames.DataBind;}catch (Exception e) { } finally {if (conn != null) { conn.Close(); } }
I am currently trying to develop an application which would help in retrieving data from cubes (Microsoft Analysis Services)! The user would not be accessing the Business Intelligence Studio, etc. but would be viewing the data from a custom made application developed in VB.Net2005.
While implementing this, I want to populate the drop-down-list in the VB(.Net) Form, by retrieving the various hierarchies in the dimensions of the cubes (along with dimensions if possible). This should be done dynamically and in real-time!
Can you please help in implementing this? Any code/method, etc would be highly appreciated!
Thanks in advance.
Best wishes!
(Software : SQL Server 2005 Enterprise Edition (with Analysis Services and BI Studio), VS.Net 2005 Enterprise Edition, ADOMD.Net)
I have been attempting to populate a dropdown (combo) box from a SQL query (SQLConnector) in Web Matrix and I have so far been unsuccessful. I have been able to populate a DataGrid just fine from the query, so then I limited it down to one colum (so itd be in the dropdown box) and set all the properties I could find (DataSource, DataMember, DataTextField, DataValueField) (and in all possible combinations) for that dropdown box to the SQL stuff (SQLConnector for the DataSource and then used the dropdowns for the others), and nothing shows up in my dropdown box. How can I make this populate off of my SQL query properly?
Additionally I will need to do the same thing for a Checkbox List so if it is the same and you know how to do that and could let me know for that too that'd be great.
How can I populate options in a dropdown (combo?) box based on data pulled from a SQL database compared against certain paramaters (if it is not equal to x, equal to y, etc)?
I have a dropdownlist that is populated with an sqldatasource as follows:
SELECT [Project_ID], [Title] FROM [Projects] WHERE [Username] = @Username AND Hide ='false'
The Datavalue vield of the DDL is populated with the [Title].
When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table.
For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?Would this work? <asp:controlParameter Name="InvoiceNumber" Type="String" ControlID="ddAdSize.Value" />
I am having the following issue with one of my reports using Reporting Services 2005. When you chose a value from dropdown list , the page reloads but the dropdown list does not keep the value selected. It always display the first item from the dropdown list.
I created a multivalue parameter in SSRS 2005 SP2, but the problem is that the width of the dropdown list is too small to fit my data (I'd like to avoid scrolling horizontally).
I have a dropdown list that is populated by two columns in a database.select (firstname+surname) AS Fullname from table where id = 'id'; It works fine but i want to know how i would get a space between the firstname and the surname because at the moment all the values come back as JoeBloggs....without any spaces
I am building a report with several search parameters; three of which are dropdown boxes each populated by a data set. I wanted something more user-friendly than the default "(Null)" in the dropdown, so for each data set, it returns a result set UNIONed with " N/A" to show up first as the default.
For some reason, when the report loads, the first dropdown populates just fine and does indeed have the default value " N/A" but the other two dropdowns are grayed-out. Until you change the first dropdown (to anything, apparently it doesn't matter what you change it to) the other two stay grayed-out. When the first is changed, the other two are populated and default to the desired " N/A" default value.
Why would it do this, when it didn't do it before; i.e. when the default for all of them was simply null. The dropdowns are not dependent on each other; they don't depend on other parameters for their values. Any help would be appreciated.
I have a web form that needs to be bilingual. It contains several dropdown lists. The "text" and "value" for each item in the dropdown list is retrieved from a database. The logic reads the database and then populates the various dropdowns with logic such as:
dim newItem as new ListItem(textString, valueString)
theDropDown.items.add(newItem)
The problem that I am having occurs when the textString needs to contains special characters such as accented vowels or an "n" with a tilde. I have tried populating the database with HTML-formatted strings such as "Avión", hoping that this would display "Avion" with an accute accent over the "o". Unfortunately it displays "Avión".
How do I get dynamically-created dropdowns to display special characters? If the answer is to simply store the special characters directly in the database, then the question becomes how do I get the special characters into the database? I tried to figure out how to write a T-SQL command such as
update tablex set SpanishName = 'Avion' where id=999
but I can't figure out the syntax to insert an accented 'o' into the string.
i have two columns in a "release" table that i concatenate on my aspx page into one value for my dropdown list. for example, my database columns might look likeMajor: 3 | 3 | 4 | 4 | 5Minor: 1 | 2 | 1 | 2 | 1and my drop down list text like3.1 | 3.2 | 4.1 | 4.2 | 5.1my question comes when trying to take the drop down list's selected value and splitting it back into two fields so that i can use it for another query. how can i split my "major" and "minor" version numbers back up so that i can run a query similar to:SELECT * FROM [Version] WHERE (([Major] = @Major) AND ([Minor] = @Minor))but only having the one parameter of: <asp:ControlParameter ControlID="ddlVersion" Name="Version" PropertyName="Text" Type="String" /> where the selected value has a string of "<major>.<minor>" (where the numbers are separated by a period)?
I have an issue with connecting Excel to an analysis Services 2005 database. The computer is running Windows XP Sp2 and Office XP. I installed the oledb driver 9.0 but when I tried to connect with the pivot table and I want to create the connection, the dropdown list of the data provider is empty. I tried on another computer with Excel XP and the system is working fine. Is there somebody that could help?
I believe somebody know the reason. Please help me on it!
I plan to pass a multivalued parameter from my web application to the server report, and let user select the value(s) from the value set. But when I run the report, there's no checkbox leftside of the values, that means user has no choice on this parameter.
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
Our clients working with the Firefox browser on a Mac are unable to use the Multi-Value parameter drop down lists that the Report Viewer control generates. Of course I realize that the multi-select dropdown lists are not really dropdown option lists using the standard HTML select tag, but are rather tables within div tags with cells that contain spans, inputs, and labels.
Originally the report viewer displayed these lists in the wrong position within Firefox on any platform (Mac or PC). Furthermore, there were other visibility problems with those lists that made it virtually impossible to select a checkbox within the list. Fortunately, Microsoft fixed this problem with the latest version of Report Viewer, which we downloaded from the following link: http://207.46.19.190/downloads/details.aspx?FamilyID=cc96c246-61e5-4d9e-bb5f-416d75a1b9ef&displaylang=en
So currently we have SQL Reporting Services Report Viewer 9.0.21022.8 installed on our web server. And the dropdown lists do appear as expected, and they work properly in Firefox on a PC.
But, when the control is rendered in Firefox on a Mac, the list is not scrollable. The scroll bar that should appear on the right-hand side of the dropdown list, which would enable users to select values toward the bottom of the list, does not appear. That scrollbar is missing in Firefox.
This is likely related to a Firefox rendering issue with the overflow:auto style. There are numerous entries on the web that indicate Firefox for Mac has a problem with overflow:auto. For example: http://www.webdeveloper.com/forum/archive/index.php/t-96958.html http://www.daniweb.com/forums/thread44144.html http://iamthewalr.us/blog/2007/04/20/firefox-on-the-mac/#comment-2321 http://www.errorforum.com/mozilla-firefox-error/3503-will-float-mac-firefox-scrollbars-floating-pop-up-windows.html https://bugzilla.mozilla.org/show_bug.cgi?query_format=specific&order=relevance+desc&bug_status=__open__&id=187435
That being the case, it seems that there should be some workaround to address this, either via a style or through some alternate control. Or perhaps there is a property that we can apply to the ReportViewer control that I'm unaware of which addresses this.
If you know of a workaround, or can suggest an alternate approach that we could implement quickly, please respond. Thanks.
I have been trying to populate my dropdown list with State names in the USA. The dropdown list populates fine, but when I hit the submit button I receive the following message:
Could not find stored procedure 'Maryland'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'Maryland'.
Source Error:
Line 81: Dim sqlCmd As SqlCommand = New SqlCommand(sqlText, sqlConn) Line 82: sqlCmd.Connection.Open() Line 83: dr = sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Line 84: Return dr Line 85: End Function
I am fairly new to SQL programming and would like to see if you can provide me with some direction on a small project I would like to accomplish. I have experience working with databases (mainly in with different versions of FoxPro) and reporting with Crystal reports, etc. but not much with SQL.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and delete the assigned codes from the storage DB or mark them as used possibly the book code.
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL) and date assigned. The original idea was to put a Y in the used field and insert the date the code was assigned to a book, but this is not entirely necessary, we have 90k unused codes and it really does not matter if they are simply deleted from the DB.
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine (something like the list below) but what I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB Go to the books DB and update it with this classcode Go back to the classcode DB and mark the used record with a Y in the Used field Loop though the books DB and perform the same operation until no more records exist with an empty classcode field.
I will greatly appreciate your feedback you can provide.
Mike.
UPDATE [dbo].[Books] SET dbo.Books.Classcode = [dbo].[classcode].[classcode] From dbo.classcode On [dbo].[Books].[classcode]= [NULL]
I have a units conversion table. I'd like to populate the parameter drop down with the name of the unit and have the value selected be the numrical value found in a different attribute.
Here is my current MDX:
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Units].[Units].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim Units].[Units].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim Units].[Units].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim Units].[Units].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])
Units Parameter Caption Parameter Value Parameter Level (null) All [Dim Units].[Units].[All] 0 Gross Ton Gross Ton [Dim Units].[Units].&[Gross Ton] 1 Hundred Weight Hundred Weight [Dim Units].[Units].&[Hundred Weight] 1
I can't seem to get different attribute to show up for ParameterValue.
Also, is there a way to NOT include the "all" member? For what I am trying to do it would have no meaning.
I have a report where a user selects a AD Global Group. Then I have another parameter that populates with the members of that group. My problem is the userlist parameter isn't populating. If I hard code a Global group name it works, but it isn't getting the Global Group parameter when I use @GlobalGroup.
SELECT sAMAccountName, displayName, distinguishedName FROM 'LDAP://DC=xxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx' WHERE objectCategory = 'Person' AND objectClass = 'user' AND memberOf = 'CN=@GlobalGroup,OU=Groups,OU=SCSM,OU=Applications,DC=xxxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=us'
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
Mike writes "I am trying to learn SQL programming and would like to see if you can provide me with some direction on a small project.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and tag the assigned codes from the storage DB as used (“Y�)
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL).
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB Go to the books DB and update the first record that does not contain a code with the above classcode
Go back to the classcode DB and mark the used record with a Y in the Used field Loop though the books DB and perform the same operation until no more records exist with an empty classcode field. The existing data would look something like:
dbo.Books
BookidClasscode
A123400001 B123400002 C123400003 D1234 E1234
dbo.classcode
classcodeUsedDate
00001Y5/17/2006 12:00:00 AM 00002Y5/17/2006 12:00:00 AM 00003Y5/17/2006 12:00:00 AM 00004 00005 00006 00007 00008
I need to pick the next classcode.classcode record that does not have a Y in the Used field and apply it to the first books.classcode record that has a NULL classcode record and then mark the used classcode.classcode record with a "Y"
Hopefully this will make some sense, I will greatly appreciate any feedback.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
INSERT INTO @CategoryTable (ColID, ColCategory, ColValue) SELECT 0, LEFT(RawCollectionData,CHARINDEX(':',RawCollection Data)), LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',Ra wCollectionData)+1,255)) FROM Collections_Staging
--Assign an ID to each block of data for each occurance of 'Reason:'
DECLARE @ID int SET @ID = 1 UPDATE @CategoryTable SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @ID - 1 ELSE @ID END, @ID = CASE WHEN ColCategory = 'Reason:' THEN @ID + 1 ELSE @ID END
--Then put the data together
SELECT --cast to Nvarchar for MSAccess a.ColID, CAST(a.ColValue as Nvarchar(30)) AS OrderID, COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID, COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName, COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID, COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID, COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f .ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount, COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType, CASE WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL ELSE CAST(h.ColValue AS SmallDateTime) END AS DisbursementDate, --COALESCE(h.ColValue,'') AS DisbursementDate, CASE WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime) WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL END AS ReturnDate, --COALESCE(i.ColValue,'') AS ReturnDate, COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code, COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @CategoryTable a LEFT JOIN @CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:' LEFT JOIN @CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:' LEFT JOIN @CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:' LEFT JOIN @CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:' LEFT JOIN @CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:' LEFT JOIN @CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:' LEFT JOIN @CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:' LEFT JOIN @CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:' LEFT JOIN @CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:' LEFT JOIN @CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'
WHERE a.ColCategory = 'Order ID:'
***************************************
This statement parses. I can preview the data. I've tried to set up both an OLE DB destination to a SQL Server table and an MS Access table destination (Jet). In either case, the data will not populate the tables. I set up a Data Viewer, and no data appears in the Viewer. With the Access destination, I have the package set up to run in 32 bit mode.
If data appears in the preview, then why doesn't the data appear in the data viewer, and why will the data not populate either of the destination tables?
Now I have to populate the fact & dimension tables by writing sql scripts. Now I have already populated the dimension tables by writing sql script, But I have to populate the fact table taking into account, here I am facing problem in wriring sql script
(i) unit_price is taken from the book base table with reference to the isbn (ii) sales_quantity is taken from the order_detail.quantity with reference to table cust_order(via orderid & orderdate) (iii) discount_price is determined dependent on the quantity. if the quantity > 20 then discount 20 %(i.e discount_price = 0.8 * unit_price). if quantity < 10, no discount i.e normal price. if quantity between 10 and 20, discount 10%. Note that the quantity is determined based on each order of each customer, thus if the same book appears at multiple positions in an order, those positions shall be grouped together. This could happen because the pk of the order_detail table is order_id + item no, not order_id + isbn (iv) sales_amount is sales_quantity * discount_price
Hi all,I have two tables tbl_A and tbl_B.Table Definitions:tbl_A (field_1, field_2, field_3)tbl_B (field_1, field_2)I would like to populate tbl_B when a record is inserted into tbl_A.Note that tbl_A.field_1 = tbl_B.field_1 and tbl_A.field_2 =tbl_B.field_2 .How do I write a trigger on tbl_A to get the corresponding recordinserted into tbl_B?RegardsJune.....
