Display Custom Values In Select Statement
Hi,
I have a table called emp, having 2 field name & sex
values are:
name sex
a 1
b 2
c 1
now i want to display the values in above table as like below...
name sex
a Male
b Female
c Male
How to do that...?
View Complete Forum Thread with Replies
Related Forum Messages:
SELECT Statement To Display Combined Fields
Dear All I am trying to populate an OledbDatareader for binding to a ASP datagrid. For this I use select statement to display combined fields in a datagrid cell. Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on. But the problem is if any of the three field is null the combined field 'Address' returns as Null. Please help me to overcome this problem. Regards kalanad ( beginner)
View Replies !
Cannot Build This Custom Select Statement...
I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this: SELECT filenameFROM pictableWHERE (@searchby LIKE @searchwords) It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works: SELECT filenameFROM pictableWHERE (keywords LIKE @searchwords) Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail. Thanks in advance.
View Replies !
Need Gridview To Display Different Columns Based On New SELECT Statement
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great. My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around. So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting. I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions? Thanks, Brad
View Replies !
Max Values In A Select Statement
I have a table which has 4 fields, patientid,testdate,testtype,results. I want to select the most recent testdate by a patient regardless of the results, or the testtype. I do however need those fields for my query. I tried the below, but I get more than 1 record if the person has had two different types of tests. For instance if patient 100 has the following 2 records I just want the most recent patientid testdate testype results 100 01/02/2002 TBI ASYMP 100 02/02/2001 PPD 00000 select max(testdate)as testdate,testtype, other_id_number from vw_cms_tb_lasttest What am missing? Thanks group by other_id_number,testtype order by other_id_number,testtype
View Replies !
Getting Different Values From Select Statement
I am running a select statement against a function that is giving me different values depending on how it is called. When I run it through sql server management studio, I get the proper results, the default value column has the parameters default value. When I call it through my web app, I get this in the default value column: *** ERROR OCCURRED level 2 (this is not a default value) *** Why would the same sql statement get different results? Here is the call: select * from dbo.f_GetSProcParameters('webservices_BENEFICIAL_USES_DM_SELECT') Here are the two functions: --------------------------------------------------- USE [si_training_db] GO /****** Object: UserDefinedFunction [dbo].[f_GetSProcParameters] Script Date: 06/13/2008 09:29:21 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[f_GetSProcParameters](@StoredProcedureName VARCHAR(128) ) Returns @sProcParameters Table(ParmName VarChar(128), DefaultValue VarChar(128), HasDefault Bit, IsInput Bit) /* * DESCRIPTION : This function returns a table listing all the parameters of a stored *procedure and the default values of those parameters. * * RETURNS : table * * * ORIG AUTHOR : Josh Kinder * DATE WRITTEN : 3/14/2006 * * REVISIONS LOG * *ID/Date PC# Description *------- --- ---------------------------------------------------------- */ As Begin Declare @Count SmallInt, @Index SmallInt, @CurParm VarChar(128), @DefaultVal VarChar(128), @IsInput BIT /*----------------------------------------------------------------------------------------------------------------------------------*/ --EDIT CHECK -sProc only /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Check that the proc name is valid If OBJECT_ID(@StoredProcedureName, 'P') Is Null Begin Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ Declare @ParmTable Table (Id SmallInt Identity Primary Key Clustered, ParmName VarChar(128)) Insert Into @ParmTable Select a.Name From SysColumns a Inner Join SysObjects b On b.Id = a.Id Where b.Name = @StoredProcedureName Select @Count = Count(Id), @Index = 1 From @ParmTable While (@Index<=@Count) Begin Select @CurParm = ParmName From @ParmTable Where Id = @Index Set @DefaultVal = dbo.f_GetsProcParamDefaultValue(@StoredProcedureName, @CurParm) SELECT@IsInput = CASE WHEN params.is_output = 1 THEN 0 ELSE 1 END FROMsys.procedures AS procs INNER JOINsys.all_parameters AS params ON params.object_id = procs.object_id LEFT JOINsys.types AS types ON types.system_type_id = params.system_type_id AND types.user_type_id = params.user_type_id WHEREprocs.is_ms_shipped = 0 AND params.name = @CurParm AND procs.name = @StoredProcedureName Insert Into @sProcParameters ( ParmName, DefaultValue, HasDefault, IsInput ) Values ( @CurParm, @DefaultVal, Case When @DefaultVal = 'NoDefaultExists' Then 0 Else 1 End, @IsInput ) Set @Index = @Index + 1 End ScriptErr: Return End --------------------------------------------------- USE [si_training_db] GO /****** Object: UserDefinedFunction [dbo].[f_GetsProcParamDefaultValue] Script Date: 06/13/2008 09:30:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[f_GetsProcParamDefaultValue] ( @StoredProcedureName VarChar(128), @ParamName VarChar(128) ) ReturnsVarChar(128) /* * DESCRIPTION : This function returns a table listing all the parameters of a stored *procedure and the default values of those parameters. * The following copyright info is for the parsing algorithm to get the default value. I obtained the code from SQL Farms Solutions and their only stipulation for use is that the copyright info remain with the code. Although I customized it for us, it is still their algorithm. ================================================================================== Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved. This code may be used at no charge as long as this copyright notice is not removed. ================================================================================== * RETURNS : table * * * ORIG AUTHOR : Josh Kinder * DATE WRITTEN : 3/14/2006 * * REVISIONS LOG * *ID/Date PC# Description *------- --- ---------------------------------------------------------- */ As Begin Declare @minRow Int, @maxRow Int, @tmpInt Int, @tmpText VarChar(4000), @tmpCharPos1 Int, @tmpCharPos2 Int, @tmpCharPos3 Int, @ParameterDefault VarChar(128), @sProcTextVarChar(4000), @DelimiterChar(1), @Count SmallInt, @Index SmallInt, @CurTextVarChar(4000) Select @minRow = Null, @maxRow = Null, @Delimiter = char(13) Declare @ProcText Table (Id Int Identity(1, 1) Primary Key Clustered, ProcText VarChar(4000)) Insert Into @ProcText Select a.Text From SysComments a Inner Join SysObjects b On b.Id = a.Id Where b.Name = @StoredProcedureName Select @Count = Count(Id), @Index = 1 From @ProcText While (@Index<=@Count) Begin Select @CurText = Coalesce(@CurText,'') + ProcText From @ProcText Where Id = @Index Set @Index = @Index + 1 End /*----------------------------------------------------------------------------------------------------------------------------------*/ --EDIT CHECKS /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Check that the parameter name is valid for the proc If Not Exists( Select 1 From INFORMATION_SCHEMA.PARAMETERS Where SPECIFIC_NAME = @StoredProcedureName And PARAMETER_NAME = @ParamName ) Begin Set @ParameterDefault = '*** ERROR OCCURRED level 1 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Get sProc into a workable temporary table /*----------------------------------------------------------------------------------------------------------------------------------*/ Declare @ProcContent Table (Idx Int Identity(1, 1) Primary Key Clustered, ProcText VarChar(4000)) Insert Into @ProcContent Select Value From dbo.f_Split(@CurText, @Delimiter, 1, 0) -- Make sure that some rows were returned successfully If @@ROWCOUNT = 0 Begin Set @ParameterDefault = '*** ERROR OCCURRED level 2 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Get location of parm and get ready to parse /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Get the first line where the parameter is referenced in the proc code. -- (the LIKE here is a little complex since it is possible that multiple parameters -- will start with the same string. Most cases of interest are covered by the -- conditions listed below). Select @minRow = Min(Idx) From @ProcContent Where ProcText Like '%' + @ParamName + ' %' Or ProcText Like '%' + @ParamName + Char(9) + '%' Or ProcText Like '%' + @ParamName + Char(10) + '%' Or ProcText Like '%' + @ParamName + Char(13) + '%' Or ProcText Like '%' + @ParamName + '=%' Or ProcText Like '%' + @ParamName + '%=%' Or ProcText Like '%' + @ParamName + ',%' -- Check that the parameter is referenced in the code If @minRow Is Null Begin Set @ParameterDefault = '*** ERROR OCCURRED level 3 (this is not a default value) ***' Goto ScriptErr End -- Get the proc line where the word 'AS' is declared. 'AS' is required -- upon proc creation to complete the variable declaration. -- Note: This cover most cases of interest. There could be scenarios where -- additional condition should be applied. Select @maxRow = Min(Idx) From @ProcContent Where ProcText Like '% AS' Or ProcText Like '% AS ' Or ProcText Like '% AS' + Char(9) Or ProcText Like '% AS' + Char(10) Or ProcText Like '% AS' + Char(13) Or ProcText Like 'AS %' Or Upper(RTrim(LTrim(ProcText))) = Char(10) + 'AS' Or Upper(RTrim(LTrim(ProcText))) = 'AS' Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) + Char(10) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) + Char(13) -- Check that the 'AS' string was found successfully If @maxRow Is Null Begin Set @ParameterDefault = '*** ERROR OCCURRED level 4 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Parse and get the default value /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Get the first proc line of code where the parameter is referenced, for string processing, -- and append to it all proc rows until the 'AS' string Select @tmpText = LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' ' From @ProcContent Where Idx = @minRow While @minRow < @maxRow Begin Set @minRow = @minRow + 1 Select @tmpText = @tmpText + ' ' + LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' ' From @ProcContent Where Idx = @minRow End -- Find the position of the parameter name. Delete all text before that position. Set @tmpInt = Null Set @tmpInt = PatIndex('%' + @ParamName + '%', @tmpText) - 1 Set @tmpText = Right(@tmpText, Len(@tmpText) - @tmpInt) -- At this point we are nearly done: -- We check whether the character '=' comes before the 'AS' or ',' string -- If not- the parameter has no default value. -- If so, we continue to find the value of the default parameter Set @tmpCharPos1 = PatIndex('%=%', @tmpText) Set @tmpCharPos2 = PatIndex('%,%', @tmpText) Set @tmpCharPos3 = PatIndex('% AS %', @tmpText) If @tmpCharPos1 <= 0 Or (@tmpCharPos1 > @tmpCharPos2 And @tmpCharPos2 > 0) Or (@tmpCharPos1 > @tmpCharPos3 AND @tmpCharPos3 > 0) Begin -- The column does not have a default Set @ParameterDefault = 'NoDefaultExists' End Else Begin -- Column has a default and it is left to find it. -- First chop the string until the '=' character Set @tmpInt = NULL Set @tmpInt = PatIndex('%=%', @tmpText) - 1 Set @tmpText = LTrim(Right(@tmpText, Len(@tmpText) - @tmpInt)) -- Now, we p*** the remaining string until we get a ',' or a ' ' character Set @tmpCharPos1 = NULL Set @tmpCharPos2 = NULL Set @tmpCharPos1 = PatIndex('%,%', @tmpText) Set @tmpCharPos2 = PatIndex('% %', @tmpText) Set @tmpInt = NULL If @tmpCharPos2 > @tmpCharPos1 AND @tmpCharPos1 > 0 Set @tmpInt = @tmpCharPos1 - 1 Else Set @tmpInt = @tmpCharPos2 - 1 If @tmpInt <= 0 Begin Set @ParameterDefault = '*** ERROR OCCURRED level 5 (this is not a default value) ***' Goto ScriptErr End Set @ParameterDefault = SubString(@tmpText, 1, @tmpInt) -- If the parameter default is a string, then we will have an '' at each side of it. -- These last lines of code will get rid of the ''. If Len(@ParameterDefault) >= 1 If Right(@ParameterDefault, 1) = '''' Set @ParameterDefault = Left(@ParameterDefault, Len(@ParameterDefault) - 1) If Len(@ParameterDefault) >= 1 If Left(@ParameterDefault, 1) = '''' Set @ParameterDefault = Right(@ParameterDefault, Len(@ParameterDefault) - 1) End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ Goto ScriptExit ScriptErr: ScriptExit: Return @ParameterDefault End
View Replies !
Select Statement When Values Are Not Null
Hi. I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format : "number | name", but if there is a null value in the number it will display only the name and vice versa. How can I do it ? Any help is appreciated.
View Replies !
Use Listbox Values To Do Select Statement
Hi, I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2. The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all. public string str;protected void Button3_Click(object sender, EventArgs e) {ArrayList itemsSelected = new ArrayList(); string sep = ","; //string str;for (int i = 0; i < ListBox2.Items.Count; i++) {if (ListBox2.Items[i].Selected) { itemsSelected.Add(ListBox2.Items[i].Value); } int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items. str = ListBox2.Items[i].Value + sep; Response.Write(str); } SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False"); String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon); SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable(); Adptr.Fill(Dt); //return Dt; GridView1.DataBind(); SqlCon.Close(); } I did some changes and the new error message is Incorrect syntax near the keyword 'from'. Thank you
View Replies !
Return Select Statement Or Values Using SqlDataSource?
Hello all, I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be? I have a SqlDataSource" <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)" UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure"> <SelectParameters> <asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="Action" Type="Byte" DefaultValue="2" /> </UpdateParameters> </asp:SqlDataSource> And my SP:/* 09-19-07 Used to update affiliate profile */ CREATE PROCEDURE spUpsertAffiliateProfile @Action tinyint, @AffiliateId int, @MemberId int = -1, @CategoryId int, @WebAddress varchar(50), @Email varchar(50), @Comments varchar(1500) AS SET NOCOUNT ON -- Find errors first, check is not needed if deleting IF @Action <> 3 IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%') BEGIN SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId) BEGIN SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 AND @AffiliateId = 0-- insert BEGIN INSERT INTO tAffiliateMaster (member_id, category_id, profile_web_address, profile_email_1, comments) VALUES (@MemberId, @CategoryId, @WebAddress, @Email, @Comments) SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id RETURN END ELSE IF @Action = 2 AND @AffiliateId > 0-- update BEGIN UPDATE tAffiliateMaster SET category_id= @CategoryId, profile_web_address=@WebAddress, profile_email_1=@Email, comments=@Comments WHERE affiliate_id = @AffiliateId AND member_id = @MemberId SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id RETURN END ELSE IF @Action = 3 AND @AffiliateId > 0-- delete BEGIN DELETE tAffiliateMaster WHERE affiliate_id = @AffiliateId SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id RETURN END GO My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event? Thanks!
View Replies !
Adding Column Values Together In SQL SELECT Statement
I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below: SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU, dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM, dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD, dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video, dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID
View Replies !
Putting Commas Between Select Statement Values
Hello, This may be a strange request, but I am going to ask about it anyways. Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this: NUMBERS 1 2 3 4 How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get: 1,2,3,4 Instead of: 1 2 3 4 Any ideas? Thanks
View Replies !
Select Statement For Comma Separated Values
hi, my sample SQL Server DB Tables are like, SID Skill --- ------- 1 JAVA 2 ORACLE 3 C 4 C++ PID Skillset --- --------- 1 1,2,3 2 2,4 3 1,2,3,4 4 3 I need the Query to display Person skills as follows... PID Skillset --- -------------- 1 Java,Oracle,C 2 Oracle,C++ 3 Java,Oracle,C,C++ 4 C and another query for Search.. if i give the search string as Java,C or i will pass the SID 1,3. i need to diplay the person records which contains the SID. output will be... PID Skillset --- -------------- 1 Java,Oracle,C 3 Java,Oracle,C,C++ 4 C or PID Skillset --- --------- 1 1,2,3 3 1,2,3,4 4 3 Plz help meee.. Thanking you in advance for your help.
View Replies !
Concatenating Column Values In SELECT Statement
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression: SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent FROM Agents; I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing. Any help greatly appreciated. Please cc me privately so I receive your assistance at once! TIA, Arthur
View Replies !
'combining' Values From A Select Statement Into A String
Hi, I got a really simple question here. Say I have a table with ID, Name 1, A1 2, A2 3, A3 .... 10, A10 Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10: A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 Is there a function that allows me to 'combine' the names from a select statement? Thanks in advance, Steven
View Replies !
Zero Count Values Not Appearing In SELECT Statement
Hi all I have the following tables: Code Snippet CREATE TABLE #Lkp_Circle ( ID INT , Abbreviation varchar(50) ) GO CREATE TABLE #Lkp_OtherCircles ( Circle varchar(50) ) GO CREATE TABLE #Tbl_User ( ID INT, Name VARCHAR(50), IsActive bit ) GO CREATE TABLE #Tbl_UserDetails ( AssociateID INT, CircleID INT ) GO INSERT INTO #Lkp_Circle VALUES (1,'C1') INSERT INTO #Lkp_Circle VALUES (2,'C2') INSERT INTO #Lkp_Circle VALUES (3,'C3') INSERT INTO #Lkp_Circle VALUES (4,'C4') INSERT INTO #Lkp_Circle VALUES (5,'C5') INSERT INTO #Lkp_Circle VALUES (6,'C6') INSERT INTO #Lkp_Circle VALUES (7,'C7') GO INSERT INTO #Lkp_OtherCircles VALUES ('C3') INSERT INTO #Lkp_OtherCircles VALUES ('C4') INSERT INTO #Lkp_OtherCircles VALUES ('C5') INSERT INTO #Lkp_OtherCircles VALUES ('C6') GO INSERT INTO #Tbl_User VALUES ( 101,'U 1','True') INSERT INTO #Tbl_User VALUES ( 102,'U 2','True') INSERT INTO #Tbl_User VALUES ( 103,'U 3','True') INSERT INTO #Tbl_User VALUES ( 104,'U 4','True') INSERT INTO #Tbl_User VALUES ( 105,'U 5','True') GO INSERT INTO #Tbl_UserDetails VALUES(101,3) INSERT INTO #Tbl_UserDetails VALUES(102,4) INSERT INTO #Tbl_UserDetails VALUES(103,5) INSERT INTO #Tbl_UserDetails VALUES(104,5) INSERT INTO #Tbl_UserDetails VALUES(105,3) GO SELECT ISNULL(Circle,'Total') Circle, ISNULL(COUNT([HeadCount]),SUM(1)) AS [Total] FROM ( SELECT DISTINCT 'Circle' = CASE WHEN #Lkp_Circle.Abbreviation IN (SELECT Circle FROM #Lkp_OtherCircles) THEN #Lkp_Circle.Abbreviation WHEN #Lkp_Circle.Abbreviation NOT IN (SELECT Circle FROM #Lkp_OtherCircles) THEN 'Others' ELSE 'Total' END,ISNULL(#Tbl_UserDetails.AssociateID,0) AS 'HeadCount' FROM #Tbl_User INNER JOIN #Tbl_UserDetails ON #Tbl_User.ID = #Tbl_UserDetails.AssociateID INNER JOIN #Lkp_Circle ON #Tbl_UserDetails.CircleID = #Lkp_Circle.ID WHERE #Tbl_User.IsActive='True' AND #Tbl_User.ID>0 AND #Tbl_UserDetails.AssociateID>0 ) AS PivotTable GROUP BY Circle WITH Cube DROP TABLE #Tbl_User,#Tbl_UserDetails,#Lkp_Circle,#Lkp_OtherCircles ----EXPECTED RESULT --Circle HeadCount --C3 2 --C4 1 --C5 2 --C6 0 --Others 0 --Total 5 -- ----ACTUAL RESULT --Circle HeadCount --C3 2 --C4 1 --C5 2 --Total 5 The criteria for Others is that those circles which are not part of #Lkp_OtherCircles i.e. C1,C2,C3 and C7 clubbed together. I have tried checking for the condition ISNULL when for that circle there is no user but the end result is same. Can someone tell me where I am going wrong and how to correct it?
View Replies !
Overloading Select Statement Return Values
Hey guys, This is what I think and hope is a fairly straight forward SQL question Essentially, I have a table which has the following columns that are relevent to my question: PROJID ACTIVITY_NAME COMPLETION_DATE Rows in this table are, for example: PROJID ACTIVITY_NAME COMPLETION_DATE 1 Prepro 10/12/2007 3:42:30 2 Prepro 10/13/2007 9:16:27 2 QA 10/13/2007 10:00:01 2 Delivery 10/14/2007 09:31:12 etc. So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that). (Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this: PROJID LAST_ACTIVITY ---------------------------------------------- 1 Prepro 2 Delivery where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro I really appreciate the help Thanks, Steve
View Replies !
Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result: Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
View Replies !
Return NULL Values In SELECT Statement With INNER JOIN ?
If I try to run the code below, and even one of the values in the INNER JOIN statements is NULL, the DataReader ends up with zero rows. What I need is to see the results even if one or more of INNER JOIN statements has a NULL value. For example, if I want info on asset# 2104, and there's no value in the DriverID field, I need the rest of the data to display and just have the lblDriverName by blank. Is that possible? <code> Sub BindSearchGrid() Dim searchUnitID As String Dim searchQuery As String searchUnitID = tbSearchUnitID.Text lblIDNum.Text = searchUnitID searchQuery = "SELECT * FROM Assets " & _ "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _ "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _ "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _ "INNER JOIN AssetCategories ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _ "INNER JOIN Store ON Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '" & searchUnitID & "'" Dim myReader As SqlDataReader myReader = Data.queryDB(searchQuery) While myReader.Read If Not IsDBNull(myReader("Store Name")) Then lblStrID.Text = myReader("Store Name") If Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text = myReader("AssetCategory") If Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text = myReader("Condition Description") If Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text = myReader("DepartmentName") If Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit ID") If Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year") If Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make") If Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model") If Not IsDBNull(myReader("Mileage")) Then lblMile.Text = myReader("Mileage") If Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text = myReader("Vin Number") If Not IsDBNull(myReader("License Number")) Then lblLicNum.Text = myReader("License Number") If Not IsDBNull(myReader("Name")) Then lblDriverName.Text = myReader("Name") If Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text = myReader("DateAcquired") If Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text = myReader("DateSold") If Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text = myReader("PurchasePrice") If Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text = myReader("NextSchedMaint") If Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR") If Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW") If Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text = myReader("Crane Capacity") If Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text = myReader("Crane Certification") If Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text = myReader("Repair Cost") If Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text = myReader("Estimate Replacement") If Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text = myReader("SalvageValue") If Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text = myReader("CurrentValue") If Not IsDBNull(myReader("Comments")) Then lblCom.Text = myReader("Comments") If Not IsDBNull(myReader("Description")) Then lblDesc.Text = myReader("Description") End While End Sub</code>
View Replies !
Select Boolean Values Merged In A Text Statement
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -
View Replies !
Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works
Hello to all, i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me. I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418: declare @IDM int; declare @IDO varchar(8000); set @IDM = 3418; set @IDO = '3430' select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 )) but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM)) I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks I use MS SQL 2005 Server Management Stadio Express Thanks a million and Best regards Sha
View Replies !
Add Column With Fixed Number Of Values (text) To The Select Statement
Hello, I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
View Replies !
How To Dynamically Generate Drop Down Values For Custom Property In Custom Component
Need help in custom component with custom property. I have seen examples on how to have an enumeration for custom property value selection; however, the enumeration is pre-set. In my case, I will need to generate the values for a drop down list at custom component design time. Specifically, I will need the input column names in the drop down list for the user to select as the custom component is configured. I have no idea how to pass the collection of input column names and the user selected value back and forth to my UITypeEditor subclass. How can I achieve that? Thanks in advance for any input, Yan Yi
View Replies !
Check If Database Is Down, Display Custom Message
Hi, I have been using SSRS for creating and getting data from the Oracle databases. I want to display customer error message "Database is down" if the oracle database is down. Is there anyway to check if database is down and display custom error message?
View Replies !
Get All The Matching Values In ColB For ColA And Display ColB Values As Comma Seperated
Hi all sql gurus, I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name . The tables below might give a better idea current scenario: Product Name SubmissionID columnC Column D AAA 123 AAA 456 BBB 111 ccc 121 AAA 789 Expected result: Product Name SubmissionID columnC Column D AAA 123,456,789 BBB 111 CCC 121 Any suggestions/pointers would be highly appreciated.
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Display Of Variable Values
Hello Experts: I want to access the values coming from the table but my code is just printing the varibale name. e.g. It is printing right now @lev0 @lev1 @lev2 @lev3 @lev4 but infact in variable @lev1, the actual value from table is [prodcut]. the value in varibale @lev1 is [category]. Can you pl. help me? --- My code Declare @lev0 varchar(50), @lev1 varchar(50), @lev2 varchar(50), @lev3 varchar(50), @lev4 varchar(50), @stmnt varchar(2000), @counter int, @st varchar(50) Select @lev0=Dimnsion, @Lev1= Lev1, @Lev2= Lev2, @Lev3= Lev3, @lev4= lev4 from dbo.Lookup where dimnsion = '[Product]' Set @counter = 0 while @counter < 5 begin set @st = '@lev'+cast(@counter as varchar(50)) print @st set @counter= @counter+1 end ------- Thanks
View Replies !
All Datetime Values Display As 12/30/99
Every date that I retrieve from my sql7 database displays as 12/30/99 in my .asp pages. This is currently how I'm retrieving the date. <%=FormatDateTime(cdate(MyDate), 2)%> When I tried to simply display the date value itself, that didn't work either, though I know all records have a valid date value in this field. Any suggestions? Thanks.
View Replies !
Display Null Values
Hello, I'm facing a problem in my reporting. I have a Customer table where is record various events like CustomerEventId, DateTime, StatusId, StatusTime, GroupId, ... I also have a status table (Id, Description) and a group table (Id, Description). I want to create a report where for a selected date range (From ... To ...) i can see (grouped by date) all status's the customer went in. The possible status are : Id Description ------------------------------------- 1 status 1 2 status 2 3 status 3 4 status 4 My query looks something like this : SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId, status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription From Customers inner join status on customers.StatusId = status.id inner join group on customers.GroupId = group.id Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description My reports has 3 parameters (From date, To date, Group) In my report i have a table with two groups : GroupByDate (grouped on DATEVAL) and GroupByStatus now my problem : let's say i have values for statusid 1,2 and 4 then my report will only display those 3 status. How can i display the status where there is no data for : now it shows : DATEVAL Occurrences Time 01/07/2007 Status 1 15 125 Status 2 25 366 Status 4 8 66 I would like it to show: DATEVAL Occurrences Time 01/07/2007 Status 1 15 125 Status 2 25 366 Status 3 0 0 Status 4 8 66 Anybody (i hope i have provide enough details ...) Vinnie
View Replies !
Display Null Values
Hi, I have the following problem. I have created different tables in my database with descriptions in from other tables (example : a table named errors with errorid and errordescription as fields in it) In a report i want to display the total nr of errors during a certain period for a certain department. i have created that report with a list (by department). in this list i have a table where i group my errors. until here all ok. when i display my report it shows all the errors. but what it doesn't show is all the errors who have value 0 (or errors that didn't occur during that period i defined) how can i display all my errors, even if they did not occur (0 times) Greetings vinnie
View Replies !
How To Use BETWEEN With Custom-ordered Values
We have a 10 digit primary key value in this format: M000123456. Theorder for this key is first determined by positions 3 and 4 in thisexample, then positions 1 and 2. So a brief sample of correct orderingwould look like this:M000001501M000011501M000021501M000001601M000011601M000021601Now my question: how can I use a BETWEEN (or > and <) in my WHEREclause to get a range of values for this column? I use the followingORDER BY clause to control how the results are sorted, but I can't getthe same logic to work with BETWEEN in a WHERE clause.ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)How do I return values between M000011501 and M000011601 for example?
View Replies !
To Display Two Values In Combobox But In Database Will Store Only One Value Out Of That
hi friends i am using visual studio 2005. i need one combo box which will display two values. one is "Designation" i.e.("clerk,accountant,manager,officer,shopkeeper) and another "Designation ID" i.e.(1,2,3,4,5 and respectively) i made table of "Designation Master" in sql server 2000 including field of "Designation ID, Designation". manually i am inserting id and designation in that table. what i want exactly i have one customer form which contain a "combobox" for "Designation" column i achieved "Designation ID" and "Designation" in that combo box also but when i will go for save i want that i will store only "Designation ID" i don't want to store "Designation" in my "Designation" column which is in "Customer table" i am attaching code which store "Designation ID" and "Designation" in customer table but i want only "Designation ID" only bold line which is difficult for me to inserting only "designationId" in customer table. Inventory_Master.cntData.Open() cmdstr = "Select DesigID,Designation from Designation_Master" cmd = New SqlCommand(cmdstr, Inventory_Master.cntData) cdrCustomer = cmd.ExecuteReader() If cdrCustomer.HasRows = True Then While cdrCustomer.Read() cmddesig = cdrCustomer.Item(0).ToString.PadLeft(1) + cdrCustomer.Item(1).ToString.PadLeft(25) cbodesignation.Items.Add(cmddesig) End While Else End If Inventory_Master.cntData.Close() Inventory_Master.cntData.Open() cmd = New SqlCommand("insert CUSTOMER(Membership_No,Member_Initial,Member_Name,Member_Birthdate,Gender,Res_Telno,Mobile_No,Res_Addr1,Res_Addr2,Res_City,Res_Pincode,Landmark,Company_Name,Comp_Addr1,Comp_Addr2,Comp_Telno,Comp_City,Comp_Pincode,Designation,Credit_Limit,Income_Mthly,Email_ID,Note) values (@Membership_No,@Member_Initial,@Member_Name,@Member_Birthdate,@Gender,@Res_Telno,@Mobile_No,@Res_Addr1,@Res_Addr2,@Res_City,@Res_Pincode,@Landmark,@Company_Name,@Comp_Addr1,@Comp_Addr2,@Comp_Telno,@Comp_City,@Comp_Pincode,@Designation,Convert(money,@creditlimit),@Income_Mthly,@Email_ID,@Note)", Inventory_Master.cntData) cmd.Parameters.AddWithValue("@Membership_No", txtmebno.Text) cmd.Parameters.AddWithValue("@Member_Initial", cbomebname.Text) cmd.Parameters.AddWithValue("@Member_Name", txtmebname.Text) cmd.Parameters.AddWithValue("@Member_Birthdate", dtime.Value) cmd.Parameters.AddWithValue("@Gender", cbogender.Text) cmd.Parameters.AddWithValue("@Res_Telno", txttelno.Text) cmd.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text) cmd.Parameters.AddWithValue("@Res_Addr1", txtresaddr1.Text) cmd.Parameters.AddWithValue("@Res_Addr2", txtresaddr2.Text) cmd.Parameters.AddWithValue("@Res_City", txtrescity.Text) cmd.Parameters.AddWithValue("@Res_Pincode", txtrespin.Text) cmd.Parameters.AddWithValue("@Landmark", txtlandmark.Text) cmd.Parameters.AddWithValue("@Company_Name", txtcompname.Text) cmd.Parameters.AddWithValue("@Comp_Addr1", txtcompaddr1.Text) cmd.Parameters.AddWithValue("@Comp_Addr2", txtcompaddr2.Text) cmd.Parameters.AddWithValue("@Comp_Telno", txtcomptelno.Text) cmd.Parameters.AddWithValue("@Comp_City", txtcompcity.Text) cmd.Parameters.AddWithValue("@Comp_Pincode", txtcomppin.Text) cmd.Parameters.AddWithValue("@Designation", cbodesignation.Text.PadLeft(1, CChar(CStr(1)))) [ help me for inserting designation in this line] cmd.Parameters.AddWithValue("@creditlimit", txtcreditlimit.Text) cmd.Parameters.AddWithValue("@Income_Mthly", txtincomemthly.Text) cmd.Parameters.AddWithValue("@Email_ID", txtemailid.Text) cmd.Parameters.AddWithValue("@Note", rtxtNote.Text) cmd.ExecuteNonQuery() Inventory_Master.cntData.Close() MsgBox("RECORD SAVED")
View Replies !
Display Null Values As 0 On Line Chart
I have a report I'm writing and have got a problem that has stumped me. The data the report is based in is in this format: Date Type 1/1/08 A 1/3/08 B 3/1/08 C 3/5/08 B 3/10/08 A 3/12/08 C 3/20/08 A Management wants a report showing a line chart that summarizes the data by Month and by Type. So, there are 2 series depending on the Type and data values are based on the count of each Type. So, in the example above the x-axis group would be month, the values would be count(Type) and there would be 2 series €“ Series 1. A, B and Series 2. C I€™ve defined the x-axis to be Month(Fields!DateOccured.Value). However, they want the x-axis to show every month for the entire year not just what is in the database. So, I defined the x-axis to have a scale of 1 to 12 and the major interval is set to 1. This displays 1 through 12 on the x-axis. The problem is when there is no data (null) for a particular month. Instead of showing 0, the line chart does not plot anything and the line is drawn to the next displayed point. So, for example on the data above the line chart would plot: January Series 1 €“ Qty. 2 Series 2 €“ Qty. 0 February Null March Series 1 €“ Qty. 3 Series 2 €“ Qty. 2 The line chart would draw a line from January to March skipping February. I need to display the null values as 0 and not null indicating no occurrences for the month rather than no data present. Thanks!
View Replies !
Display Negative Values In A Simple Horizontal Bar Chart
Hi there, I am using RS chart control. The chart is used in the simple horizontal variant. I just found out that the bars disappear when negative values are provided... Is it possible at all to display negative values in the bar char? Positive values are displayed to the right of the vertical x-axis. I thought negative values would be displayed to the left of the axis..... Thank you very much for any ideas! Best regards NB
View Replies !
Display Column Names And Its Values Programatically From The Dataset
Hi. In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this: @sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT' EXEC(@sql) So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code? Thanks
View Replies !
Column Chart Does Not Display Correct Data Values
I have a report that contains a table to display data in tabular format and a column chart. Both the report and chart are driven off the same dataset. There is one parameter called number of days that retrieves data for these many days. The data that gets displayed in the table is correct, but on graph is totally incorrect. The length of column bars is incorrect when compared to data values. I have followed the exact steps to create the chart. Can someone please help as this is a production problem? Thanks a lot.
View Replies !
Unable To Set Values In Array Custom Property
I'd like to be able to hold onto lineageIDs in a list in the custom property of an output object. I found that I can really only easily use an array which is fine but now that I have coded it I cannot set any of the array values programatically. I can't even initialize the array the way I want it. I also cannot change the value in the debugger, it just pops back to zero. Am I missing something? Here's how I'm trying to do it: IDTSCustomProperty90 linIDsProperty = output.CustomPropertyCollection.New(); linIDsProperty.Name = "KeyColumnLineageIDs"; linIDsProperty.Value = new int[MAX_KEY_COLS]; for (int i = 0; i < ((int[])linIDsProperty.Value).Length; ++i) ((int[])linIDsProperty.Value)[i] = -1; (please ignore the \ around the [i], the forum changes [i] into for some reason) Thanks Charlie.
View Replies !
Get All The Matching Values In ColB And Display It Against ColA As Comma Seperated In The Same Row
Hi all sql gurus, I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name . The tables below might give a better idea current scenario: Product Name SubmissionID columnC Column D AAA 123 AAA 456 BBB 111 ccc 121 AAA 789 Expected result: Product Name SubmissionID columnC Column D AAA 123,456,789 BBB 111 CCC 121 Any suggestions/pointers would be highly appreciated.
View Replies !
Correct SQL Statement For URL Variable Display?
I have a page that is displaying different movie covers. They each have their respective genres stored in several keys (genre0, genre1, genre2). Now I can search within the movies by changing the url variable movies.aspx?genre=action but if I simply go to movies.aspx, I'm wanting all genres to appear. But for me, the page is blank if I don't specify the genre. So I've had to start filling in the genre All for all movies so I can go to movies.aspx?genre=all but I know this can't be necessary. Is it my SQL statement for the datasource causing the problem? Code is in VB. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Movies] WHERE [genre0] = @strGenre OR [genre1] = @strGenre OR [genre2] = @strGenre"> <SelectParameters> <asp:QueryStringParameter Name="strGenre" QueryStringField="genre" /> </SelectParameters> </asp:SqlDataSource> <asp:DataList ID="DataList1" runat="server" DataKeyField="movieID" DataSourceID="SqlDataSource1" RepeatColumns="5" RepeatDirection="Horizontal" CellPadding="5" BorderStyle="None" BorderWidth="0px"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "movie.aspx?id=" & Eval("movieID") %>'> <asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("imageURL", "{0}") %>' BorderStyle="None" /><br /> </asp:HyperLink> </ItemTemplate> </asp:DataList>
View Replies !
Custom Task Not Updating Property Values Or Saving.
Hi Folks, I have a task I wrote which does not always update the property value (as seen in the properties pane) Basically, change something on the form, then update the task host property with: this.taskHostValue.Properties["Duration"].SetValue(this.taskHostValue, Convert.ToInt32(spnDuration.Value)); Stepping through this, it does exactly what it is supposed to. Having a look at the property value, it confirms it has changed. Reopening the UI and resetting all the controls returns the expected results. The package however does not realise it has changed. There is no * next to the package name in the top tabs. As long as the package thinks it is unchanged, SaveXML does not get called either so the tasks do not persist. Changing the value on the properties pane works fine though. The frustrating thing is this is slightly random. Slight in the sense that sometimes it works but most of the time it does not. The sample code I used was the MS download IncrementTask (Which works BTW) so I can't see it as being a VS / SSIS bug but rather something I am / am not doing. 3 tasks I have written all behave the same. I have to "nudge" them before savign the package. Any ideas what the problem might be? TIA Cheers, Crispin
View Replies !
How To Create A Custom SQL Statement.
im creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server"> protected void CheckBox1_CheckedChanged(object sender, EventArgs e) { strquery += " and PO between " + textbox1.text + " and " + textbox2.text; } protected void CheckBox2_CheckedChanged(object sender, EventArgs e) { strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text; }</script> im a serious newbie with C#
View Replies !
Custom Function In SQL Statement?
In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function. I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name". What do I need to do to make this work? All help is greatly appreciated! Lynnette
View Replies !
Extracting Parameter Values From A Custom Data Processing Extension
I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension. My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
SQL Statement: Group By And Where Clause Do Not Display Null Rows
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement: "SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]" Thanks for the help in advance!
View Replies !
|
TRACKER
