Hi,
I have boolean values in a table for ex. Federation. And I want to
select following
Select 'Insert into' + member + 'test' as test1from federation
Then I get error message
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals bit.
Someone can help me out of it.
Thanks an advance
- Loi -
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.
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :)
I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query.
(tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server">
2 <asp:ListItem Value="0">Open</asp:ListItem>
3 <asp:ListItem Value="1">Completed</asp:ListItem>
4 </asp:DropDownList>
5
6
7 <asp:DropDownList ID="drpdwnCompany" runat="server">
8 <asp:ListItem Value="">All</asp:ListItem>
9 <asp:ListItem Value="cur">Cur District</asp:ListItem>
10 <asp:ListItem Value="jho">Jho District</asp:ListItem>
11 <asp:ListItem Value="sea">Sea District</asp:ListItem>
12 <asp:ListItem Value="san">Net District</asp:ListItem>
13 <asp:ListItem Value="sr">Research District</asp:ListItem>
14 </asp:DropDownList>
15
16
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>"
18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')">
19 <SelectParameters>
20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" />
21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" />
22 </SelectParameters>
23 </asp:SqlDataSource>
24
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
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
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
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
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.
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag
--------------------------------
1 2012-12-12 rowA
1 2012-12-12 rowB
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
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!
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
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.
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
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...?
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
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?
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
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
I have trouble converting a text field into decimals in a select statement.
The select statement is as follows:
select cast(ltrim(rtrim(units)) as decimal) as units
and I get an error message:
Msg 8114, Level 16, State 5, Line 23
Error converting data type nvarchar to numeric.
and the 'units' column contains ' 324,32 '
Created a new scheduled job using Transact-SQL.
Running a basic script SELECT FROM WHERE
As of now, it places it into one column, for all the fields.
I need the fields to be placed in their respective columns. Tried saving it as .xls and .csv. Same issue. Running SQL Server 2008R2.
I wish I had a better idea about how to describe this but here goes. I'm trying to display a list of majors/minors/graduate programs for a particular user. Now, I've gotten it display perfectly and was able to append (Graduate), (Major), (Minor) to each of the 3 different sections. My question is is it possible to add like rows that don't actually exist in table to the beginning of the select? For instance I want to add All and General before the rest of the select. I was also wondering where I should do a group by cuz I'd like to keep it in blocks of like graduates then majors and finally minors but currently it is just doing it alphabetically...Anyways below are the tables and at the end the current stored procedure i'm using, which works great...just not to the degree I'm aiming for.
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[GraduateDiscipline] Script Date: 03/30/2008 14:45:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GraduateDiscipline](
[GraduateDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GraduateID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_GraduateDiscipline] PRIMARY KEY CLUSTERED
(
[GraduateDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_DegreeShortName] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_DegreeShortName]
GO
ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_GraduateName] FOREIGN KEY([GraduateID])
REFERENCES [dbo].[Graduate] ([GraduateID])
GO
ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_GraduateName]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Graduates] Script Date: 03/30/2008 14:46:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Graduates](
[GraduateDisciplineID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Student_Graduates] PRIMARY KEY CLUSTERED
(
[GraduateDisciplineID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID] FOREIGN KEY([GraduateDisciplineID])
REFERENCES [dbo].[GraduateDiscipline] ([GraduateDisciplineID])
GO
ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID]
GO
ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_StudentID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_StudentID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDiscipline] Script Date: 03/30/2008 14:46:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MajorDiscipline](
[MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MajorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Majors] Script Date: 03/30/2008 14:46:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Majors](
[MajorDisciplineID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Student_Majors] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDiscipline] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_MajorDisciplineID]
GO
ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_StudentID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_StudentID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Minors] Script Date: 03/30/2008 14:46:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Minors](
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Minors] PRIMARY KEY CLUSTERED
(
[MinorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Minors] Script Date: 03/30/2008 14:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Minors](
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Student_Minors] PRIMARY KEY CLUSTERED
(
[MinorID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_Minors] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_Minors]
GO
ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_StudentID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_StudentID]
ALTER PROCEDURE [dbo].[Student_Deciplines]
@studid int
AS
BEGIN
SELECT RTRIM(gradDis.DisciplineName) + ' (Graduate)' FROM GraduateDiscipline gradDis, Student_Graduates sGrads WHERE gradDis.GraduateDisciplineID = sGrads.GraduateDisciplineID AND sGrads.StudentID = @studid
UNION
SELECT RTRIM(majDis.DisciplineName) + ' (Major)' FROM MajorDiscipline majDis, Student_Majors sMajors WHERE majDis.MajorDisciplineID = sMajors.MajorDisciplineID AND sMajors.StudentID = @studid
UNION
SELECT RTRIM(mins.MinorID) + ' (Minor)' FROM Minors mins, Student_Minors sMinors WHERE mins.MinorID = sMinors.MinorID AND sMinors.StudentID = @studid
END
Some sample data:
GraduateDiscipline
GraduateDisciplineID DegreeID GraduateID DisciplineName ....
0 M.B.A. 1 Master of Business Administration
Student_Graduates
GraduateDisciplineID StudentID
0 0
MajorDiscipline
MajorDisciplineID DegreeID MajorID DisciplineName
1 B.S. Computer Science Computer Science
Student_Majors
MajorDisciplineID StudentID
1 0
Minors
MinorID Description Criteria
Computer Science blah blah blah blah
Student_Minors
MinorID StudentID
Computer Science 0
Output I'd like for StudentID 0
All
General
Master of Business Administration (Graduate)
Computer Science (Major)
Computer Science (Minor)
Hope someone can help!
I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
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!
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
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>
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername,
from CustomerTable
where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430
ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it
Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
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
I wrote a select statement, I only want to see orders with max lastUpdatedOn date of 14 days and older. Now my results show dates with all orders of 14 days and older (which is OK), but all others are displayed in the "Uitgifte" column as "NULL". But those orders should not be displayed at all.
selectdistinct ProductionHeader.ProdHeaderOrdNr,
ProductionHeader.PartCode,
ProductionHeader.Description,
ProductionHeader.Qty,
(select max (ProdStatusLog.ProdStatusCode)
[code]...
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
Hi... I have a problem getting a select statement to return a boolean value from sql server... the aim is to populate a checkbox list with permissions a group of users has. The statement is an outer join as follows: select SS.PermissionName, BB.GroupId,case when BB.GroupId Is Null then cast(0 as bit) else cast(1 as bit)end IsAddedfrom clPermissions SS left join ( select PermissionName, GroupId from clvGroupPermissions where GroupId = (Select groupId from clGroups where GroupName = 'Admin')) BB on SS.PermissionName = BB.PermissionName The returned values are 0 and 1... but .NET does not seem to recognize these values as true and false...Any one had an issue like this before?
View 3 Replies View RelatedI try to insert VB.NET boolean true values into a SQL2000 table thru a stored procedure. While varchar fields of the table are correctly filled, I'm surprised to find the bit fields all stay 0 after every single insert. This makes me wonder if VB.NET boolean variables can be passed to sp bit parameters and inserted into a SQL2000 table. Or did I miss something? Please advise. Thanks.
View 1 Replies View Related