SQLDataSource - How To Retreive The SQL Select Statement With The Values Of The Parameters
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Sqldatasource Using Session Data In Select Parameters
I'm having trouble using session data in my select parameters. If I manually plug a value into the selectcommand or create a hard coded value using a parameter it works, but if I try to use the session data the query pulls no results. I know the session data is set because I print the value at the top of the page, but for some reason it's not getting passed to the sessionparameter??? This is the datasource code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:flautoconnstr %>" SelectCommand="SELECT * FROM [tblVehicles] WHERE ([profileid] = @profileid)"> <SelectParameters> <asp:SessionParameter Name="profileid" SessionField="profileid" Type="String" /> </SelectParameters> </asp:SqlDataSource> This is the line I use to set the session data. Session["profileid"] = myQuote.Profileid;
View Replies !
Sqldatasource Select Statement
Hi all,I drag sqldatasource to my form, and then adding a button there. I want when clicking the button to be able to use the sqldatasource1.select statement . I found some parameters that this method used but still dont know how to figure it out, which was IEnumerable Select (DataSourceSelectArguments a)for example when the button it clicked I want to perform the select * from employee Thanks
View Replies !
Dynamically Assign Select Statement To SqlDataSource
How can i assign select statement to a sqldatasource I mean if the user clicks button 1 sqldatasource=-"select * from customers where status='ClOSED' " or else if the user clicks button2 sqldatasource="Select * from Customers Where Status='OPEN' " I want to bind the sqldatasource to a gridview finally . Is this possible ? I tried sqldatasource filters But am reallly confused Any suggestions/solutions are appreciated thankyou
View Replies !
Programatically Change Sqldatasource Select Statement
Hi Everyone, I am trying to change the select statement of an sqldatasource if a check box is checked. I am using the SqlDataSourceSelectingEventArgs but i can't get it to work, anyone got any pointers? Code BehindProtected Sub LocMan_Searching(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs) Handles LocManSearch.Selecting If cb_Today.Checked = True ThenLocManSearch.SelectCommand = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() & "+ '%') AND [available] LIKE '%' + " & Date.Today & "+ '%')" Else : LocManSearch.SelectCommand = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() & " + '%')" End If End Sub My SQLDATSOURCE <asp:SqlDataSource ID="LocManSearch" runat="server" ConnectionString="<%$ ConnectionStrings:MYLOCDEVConnectionString %>" > <SelectParameters> <asp:ControlParameter ControlID="dd_Area" Name="area" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> Thanks in advance Chris
View Replies !
Using SqlDataSource To Execute A Select Statement In The Code File???
Hi you all, In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource. In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!
View Replies !
Using Passed Parameters In A Select Statement
hello all, im trying to run a select statement using a parameter, but am having extreme difficulties. I have tried this about 50 different ways but i will only post the most recent cause i think that im the closest now than ever before ! i would love any help i can get !!! Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim pageID As StringpageID = Request.QueryString("ID") TextBox13.Text = pageID 'Test to make sure the value was stored SqlDataSource1.SelectParameters.Add("@pageID", pageID) End Sub .... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName=System.Data.SqlClient ConnectionString="Data Source=.SQLEXPRESS;Initial Catalog=software;Integrated Security=True;User=something;Password=something" 'SelectCommand="SELECT * FROM Table1 WHERE [ClientID]='@pageID' ></asp:SqlDataSource> The error that i am getting, regardless of what i put inside the ' ' is as follows: "Conversion failed when converting the varchar value '@pageID' to data type int." anyone have any suggestions ?
View Replies !
Insert From Parameters And Select Statement
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
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 !
Controlling Fields In A Select Statement By Use Of Parameters
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
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 !
Can You Have Multiple Output Parameters? Difference Between A Select Statement?
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible? Do I need just a select statement to do this? What is the difference between the output and select statements? Thanks in advance.
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 !
Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g. Table 1 Org aaa:aaa:aa aaa:aaa:ab aaa:aab:aa Table 2 delim Seg1Start Seg1Len Seg2Start Seg2Len Seg3Start Seg3Len : 1 3 5 3 9 2 My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work. Any ideas? Can this be done in SSIS? I'd really appreciate any insight that anyone might have. Regards, Bill
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 !
Default Values Does Not Include All The Values (Cascading Parameters)
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically. One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2). If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked. What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
View 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 !
How To Value Parameters For Sqldatasource
I set up a sqldatasource based on a stored procedure which takes one parameter. The sqldatasrouce wizard generates the following code for the parameter below. The question is how do I value the DeptID parameter on the load of the form. I tried the following code in the load of the page, but get a null reference error:Me.SqlDataSource1.InsertParameters("DeptID").DefaultValue = Session("DeptID") <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FDConn %>" SelectCommand="GetTruckStatus" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="DeptID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <radG:RadGrid ID="RadGrid1" runat="server"> </radG:RadGrid>
View Replies !
Parameters Of SqlDataSource
Hello I need help withsetting parameters for SqlDataSource I have a simple program. I want display date from database on MS SQLSERVER from the table USERS only for current sing on user select by his login. I save into this variable login current user: string @LOGIN = Context.User.Identity.Name; I have already done with this way without SqlDataSource: string login = Context.User.Identity.Name; SqlConnection conn1 = new SqlConnection("server=CR\SQLEXPRESS;database=myData;integrated security=SSPI"); conn1.Open(); SqlCommand cmd1 = new SqlCommand(" SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN", conn1); cmd1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50); cmd1.Parameters["@LOGIN"].Value = login; 1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50); cmd1.Parameters["@LOGIN"].Value = login; Now I don't know how to do with SqlDataSource, what I have to set in SqlDataSource1 yet <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" ProviderName="<%$ ConnectionStrings:myConnectionString.ProviderName %>" SelectCommand="SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN"> </asp:SqlDataSource>
View Replies !
SqlDataSource Parameters
Is it possible to specify different parameters for the select/update/delete stored procedures used by a sqldatasource? When I 'configure the datasource' it lists the different stored procedures for each command, but when it comes to specifying the parameters it only lets me do so for the select command. Is there another way to do this for the other commands? I have tried doing it manually in the aspx file (using the DeleteParemeters etc), but I don't know how to reference a specific cell in the selected row of the gridview for the controlparameter). Any thoughts?
View Replies !
SQLDataSource: How To Use Parameters
I need to do some select & update from VB code behind a web page. Using VS2005, ASP.NET. To me, the most logical approach would be to use a SqlDataSource. I can select the connection string, predefine select, insert and update queries and call the select(), and other commands from this control. I need to use parameters in the queries but I cannot connect parameters straight to controls, I need to do it from VB. But nowhere can I find how to set the parameter values from code. I can find all kind of examples from VS2003 or using SqlCommand, but it should be possible from this control as well, but the help documentation is very poor in this respect. Please provide me with some example. Kind regards
View Replies !
How To Send Parameters From SqlDataSource?
I'm try to use a SqlDataSource control for binding data (stored procedure) to a GridView control. But I only get the error message below. What am I doing wrong? ----------- Error message ----------------------Procedure or function 'StatisticCustomers' expects parameter '@From', which was not supplied. ------------- Customer.aspx -------------------- From <asp:TextBox id="txtDateFrom" CssClass="datebox" runat="server"></asp:TextBox> To <asp:TextBox id="txtDateTo" CssClass="datebox" runat="server"></asp:TextBox> Min: <asp:TextBox id="txtMinimumOrders" CssClass="" runat="server"></asp:TextBox> <asp:GridView ID="grdvwCustomers" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="Column1" HeaderText="Column1" ReadOnly="True" SortExpression="Column1" /> <asp:BoundField DataField="Items" HeaderText="Items" ReadOnly="True" SortExpression="Items" /> <asp:BoundField DataField="Column2" HeaderText="Column2" ReadOnly="True" SortExpression="Column2" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="StatisticCustomers" SelectCommandType="StoredProcedure" runat="server"> <SelectParameters> <asp:ControlParameter ControlID="txtDateFrom" Name="dtFrom" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="txtDateTo" Name="dtTo" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="txtMinimumOrders" Name="iMinimumOrders" PropertyName="Text" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> ALTER PROCEDURE [dbo].[StatisticCustomers] (@From datetime, @To datetime, @MinimumOrders int )AS SELECT Orders.UserId, 'name', COUNT(*) AS Items, SUM(OrderDetail.Subtotal) FROM Orders INNER JOIN OrderDetail ON Orders.OrderId = OrderDetail.OrderId WHERE DateCreated > @From AND DateCreated < @To GROUP BY Orders.UserId ORDER BY Items DESC Regards, Sigurd
View Replies !
Problems With SqlDataSource Parameters
Hello! I am pulling my hair out over this latest problem. I'm trying to do a simple SQL SELECT statement by taking values from textboxes and inserting them into the WHERE part of the statement. I'm using MySQL and have to use ? prefix for all inserts, that I have working by the way. Now for some reason I cannot get the values from the text boxes to replace the prefixes! Here is my code: private void submitLogin(object source, EventArgs e) { if (isPassword(password.Text.ToString()) && isEmail(username.Text.ToString())) { ValidateCustomer.SelectParameters["c_email"].DefaultValue = "fish"; ValidateCustomer.SelectParameters["c_password"].DefaultValue = "cakes"; test_label.Text = ValidateCustomer.SelectCommand.ToString(); } else { test_label.Text = "Username or Password Incorrect"; } } <asp:SqlDataSource runat="server" ID="ValidateCustomer" ProviderName="System.Data.Odbc" ConnectionString ="<%$ connectionStrings:MySQLDatabase %>" SelectCommand ="SELECT * FROM customer_details WHERE (Customer_Email = ? AND Customer_Password = ?)"> <selectparameters> <asp:parameter name="c_email" type="String" /> <asp:parameter name="c_password" type="String" /> </selectparameters> </asp:SqlDataSource> I have omitted obvious code. The test label, when updated, shows the SQL statement but with the ?. I also use controlparameters that took the values directly from the textboxes but again this did not work. I'm really stuck with this one. Your help is much needed! Cheers Dan
View Replies !
SqlDataSource Setting Of Parameters?
Hi,How do I set the parameters of an SqlDataSource programatically?I have tried the following...dsDraftBudgetPI.SelectParameters.Add(new Parameter("@person_id", TypeCode.Int32, pID.ToString()));But that didn't work. I already have the parameter defined at design time so I don't need to create one I just want to set its value and then bind it to a ListBox to display the result.Thanks,Scott
View Replies !
SQLDataSource Parameters Question
Can you access the values within sqldatasource parameters programmatically? I have tried to reference them in the following ways without any luck. -SqlDataSource_Contacts.UpdateParameters("original_user_id") -SqlDataSource_Contacts.UpdateParameters("original_user_id").ToString -SqlDataSource_Contacts.UpdateParameters("original_user_id").DefaultValue -SqlDataSource_Contacts.UpdateParameters("original_user_id").DefaultValue.ToString -SqlDataSource_Contacts.UpdateParameters.Item("original_user_id") -SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").ToString -SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").DefaultValue -SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").DefaultValue.ToString
View Replies !
Programmatically Specify SqlDataSource Parameters
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted. What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0]) Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user? Thank you very much for your help.
View Replies !
Need To Increment Numeric Parameters With SqlDataSource- Help!
hi, i am using a sqldatasource with a formview that has three stored procedures associated with it. when i update a record in my formview, i need to increase two parameters that are displayed as labels in the formview by their value plus one. i can't figure out how to increase the value in the stored procedure, or using the codebehind, or with some script in the html. any solution works for me. any suggestions? if i can figure out how to increment one of my fields, i can use the same logic for the other. below is an example of what i am trying to do. in my formview i have three fields: Count, Rate, Price. Count is a label, Rate and Price are editable textboxes. once i commit the update for the values of Rate and Price, i need the value of Count to increase by 1, and then have that value updated in the data base along with the new values for Rate and Price. is there a way to do this in the codebehind, or in my stored procedure? i am using Asp.Net 2.0, vs2005, and sql server. any help is greatly appreciated. thank you.
View Replies !
Using SqlDatASource Programmatically - Output Parameters
I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues. The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key: ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY() This works fine when run from Sql Server Management Studio and @NewID has the correct value. My data access code is roughly as follows: dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value. Has anyone tried to do this and can tell me what I am doing wrong? Jon
View Replies !
How Do I Pass VB Variables To The Asp Parameters Of An SqlDataSource?
I’ve got the following piece of ASP code, which updated a table through the standard edit/update command filed options. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CAT_SYSTEMConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="sp_diplomaViewQualifications" UpdateCommand="UPDATE PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and qualId=@original_qualId" DeleteCommand="UPDATE PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy WHERE rowstatus=1 and qualId=@original_qualId" SelectCommandType="StoredProcedure"> <UpdateParameters> <asp:Parameter Name="original_qualId" /> <asp:Parameter Name="quantity" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="original_qualId" /> <asp:Parameter Name="createdBy" DefaultValue="TEST"/> </DeleteParameters> <SelectParameters> <asp:Parameter Name="masterKey" /> </SelectParameters> </asp:SqlDataSource> My problem is that I need to pass the contents of the VB variable ‘createdBy’ into the DeleteParameters option. This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page) How do I pass this into the update and/or delete part of the command field on the ASP page?
View Replies !
Setting SqlDatasource Parameters From TextBox
I have a sqlDatasource with 3 parameters based on the input of 3 text boxes on the page. The datasource returns sales details for a company based on a from and to date. I am attempting to set the value of the 3 parameters in the Selecting event of the datasource control but I'm not getting any data back. If I set the values literally then I get data back. Also when I step through the code I can see the 3 parameters getting their values from the textboxes and the drop down list as they should. This is driving me insane as I'm new to .net and just can't see what is stopping me retrieving the data when using the form fields to set the datasources parameters. Below is the aspx and the code behind for the page. Thanks in advance for any help. aspx..... <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="LabelSales.aspx.cs" Inherits="LabelSales" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSourceSales" runat="server" EnableCaching="false" ConnectionString="<%$ ConnectionStrings:streetwisedigitalConnectionString %>" SelectCommand="DL_GET_SALES_BY_LABEL" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSourceSales_Selecting" > <SelectParameters> <asp:Parameter Name="fromDate" /> <asp:Parameter Name="toDate" /> <asp:Parameter Name="label" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSourceLabels" runat="server" SelectCommand="select label_id, label_name from dl_label order by label_name asc" ConnectionString="<%$ConnectionStrings:streetwisedigitalConnectionString%>"> </asp:SqlDataSource> <div> <table> <tr> <td>Start Date: </td> <td> <asp:TextBox ID="FromDate" Runat="server" Width="70"/> <asp:Button ID="btnFrom" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>End Date: </td> <td> <asp:TextBox id="ToDate" Runat="server" Width="70" /> <asp:Button ID="btnTo" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>Label: </td> <td> <asp:DropDownList ID="LabelList" Runat="server" DataSourceID="SqlDataSourceLabels" DataTextField="label_name" DataValueField="label_name"> </asp:DropDownList> </td> </tr> <tr> <td colspan="2" align="right"> <asp:Button ID="Button1" OnClick="SubmitButton_OnClick" Text="View Sales" runat="server" /> </td> </tr> </table> <p> <asp:GridView ID="GridViewSales" runat="server" DataSourceID="SqlDataSourceSales" ShowFooter="True" AllowSorting="True" AutoGenerateColumns="False" OnRowDataBound="GridViewSales_RowDataBound" EmptyDataText="No data to display."> <Columns> <asp:BoundField DataField="cat_no" HeaderText="Cat No" /> <asp:BoundField DataField="artist" HeaderText="Artist" /> <asp:BoundField DataField="title" HeaderText="Title" /> <asp:BoundField DataField="remix" HeaderText="Remix" /> <asp:BoundField DataField="qty" HeaderText="Sold" /> <asp:boundfield datafield="commission" HtmlEncode="False" dataformatstring="{0:F2}" headertext="Commission"> <ItemStyle HorizontalAlign="Right" /> </asp:boundfield> </Columns> </asp:GridView> </p> </div></asp:Content>code behind...using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class LabelSales : System.Web.UI.Page{ int totalSold; decimal totalCommssion; protected void GridViewSales_RowDataBound(object sender, GridViewRowEventArgs e) { // add column totals to gridview if (e.Row.RowType == DataControlRowType.DataRow) { totalSold += (int)DataBinder.Eval(e.Row.DataItem, "qty"); totalCommssion += (decimal)DataBinder.Eval(e.Row.DataItem, "commission"); } // display the totals else if (e.Row.RowType == DataControlRowType.Footer) { e.Row.Cells[0].Text = "<b>Total</b>"; e.Row.Cells[4].Text = totalSold.ToString(); e.Row.Cells[5].Text = totalCommssion.ToString("f2"); } } protected void Page_Load(object sender, EventArgs e) { } protected void SqlDataSourceSales_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { // *** This method does not work *** e.Command.Parameters[0].Value = FromDate.Text; e.Command.Parameters[1].Value = ToDate.Text; e.Command.Parameters[2].Value = LabelList.Text; // *** This method works! *** e.Command.Parameters[0].Value = "02/01/2007"; e.Command.Parameters[1].Value = "02/01/2008"; e.Command.Parameters[2].Value = "Fat!"; } protected void SubmitButton_OnClick(object sender, EventArgs e) { SqlDataSourceSales.Select(DataSourceSelectArguments.Empty); }} Many thanks Simon
View Replies !
|
TRACKER
