How Do I Call A Select Statement Properly When The Field Is A Yes/no?
Dec 4, 2006
Hello,
i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work.
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn)
DOES NOT WORK:
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn)
Thanks in advance.
View 3 Replies
ADVERTISEMENT
Nov 28, 2005
Is there a way to call a stored procedure within a SELECT statement?Example;-----------SELECT FirstName, LastName, (EXEC UniqueID_KEYGEN @keyval output) AS UniqueIDINTO #tNewEmployeeFROM EmployeeTable-----------SELECT *FROM #tNewEmployeeThe return from the temp table would have a unique ID ready to insert into another table. Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS. I was used to having a Identity field do this for me, I don't know why we have to do it his way. Except for the reason of sequence and easily get the next record. But we don't use URL variables, only FORM or SESSION.Thanks for your help in advance.
View 2 Replies
View Related
Apr 17, 2004
Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it.
USE NORTHWIND
GO
CREATE TABLE tbA (
Item int NOT NULL,
Value int NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tbA (Item, Value)
SELECT 1, 10 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2
GO
CREATE PROCEDURE usp_SquareIt
@iItem int
AS
declare @iValue int
SELECT @iValue = Value FROM tbA
SELECT @iValue * @iValue AS Result
GO
SELECT Item,
EXECUTE usp_SquareIt Item AS Squared ---- can this be done
FROM tbA
GO
DROP TABLE tbA
GO
DROP PROCEDURE usp_SquareIt
GO
Any thoughts?
Mike B
View 1 Replies
View Related
Feb 21, 2007
Is it possible to call a stored procedure in a select statement?
For example:
SELECT Buyer, Country, (exec the sp_test*) as ItemList.....
*sp_test is a stored procedure with buyer and country as the input parameters and output a concatenated item numbers (example: F12345,A1023,C40165).
View 20 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Aug 31, 2005
Ok, I inherited this database and there is a field that stopres a selectstatement. Is there anyway possible to execute the value of the fieldwithin a select statement?For example:the table:Name "george"lookupForName "Select orders from Ordertable"So maybe something like select name, execute(lookupforname) as ordersSorry, I didn't design this, just inherited :)george
View 1 Replies
View Related
Mar 17, 2008
Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output
Trans Date, total deposits, total withdrawls, closing balance
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara
View 5 Replies
View Related
Mar 25, 2008
Hello,
Is it possible to generate a identityfield dynamically upon select, like this:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC
I want the output to be:
1 - Name12 - Name23 - Name3
The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.
Like:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC
I want the output to be:
1 - Name32 - Name23 - Name1
Patrick
View 4 Replies
View Related
Oct 5, 2001
Hi
I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help.
Thanks
Regards
-Leong
View 2 Replies
View Related
Aug 13, 2014
I've got a encrypted column in sql which holds the password field, e.g. TPSK9RlOz0/2BhuQntVeaBda+9g=, is their a way in a select statement to get the password ?
View 3 Replies
View Related
Aug 4, 2015
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.
SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',
[code]...
View 5 Replies
View Related
Jan 24, 2007
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View 1 Replies
View Related
Jan 3, 2008
I have this SELECT statement.
SELECT [issueID], [name] FROM [MyIssue]
What I wanted to do is in addition to the above statement, I want to add two run time fields like this:
99 [issueID],'All Issues' [name]
So let's say the above select statements generates this list:
Summer 2007 Issue
Winter 2007 Issue
The two addition fields will make the result list like this:
01 Summer 2007 Issue
02 Winter 2007 Issue
99 All Issues
How do I accomplish this? Any help is much appreciated.
View 5 Replies
View Related
May 21, 2007
Hi,
I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.
Can anyone help me please?
Thx
View 6 Replies
View Related
Jan 8, 2008
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
View 2 Replies
View Related
Apr 8, 2006
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View 2 Replies
View Related
Jul 13, 2015
Consider the following data:
create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')
if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.
View 9 Replies
View Related
Apr 23, 2008
Hi all, could someone be kind enough to provide me some help into why the following IF statement is not working? Basically, I am trying to find out whether a field in my database has a CONSTRAINT attached to it with a particular name... IF it does have one then drop it and re-create it ELSE simply create one.
I have used SELECT * FROM SYSOBJECTS to find whether it exists which according to the results it does EXIST, however my IF statement is not detecting it and I get an error which states:
Msg 1781, Level 16, State 1, Line 30Column already has a DEFAULT bound to it.Msg 1750, Level 16, State 0, Line 30Could not create constraint. See previous errors.
However, surely my IF statement is suppose to be handling this problem and therefore if it does EXIST THEN it should ideally drop the CONSTRAINT and then re-create it but this is not the case... instead the ELSE is always being triggered. After using SYSOBJECTS I copied it directly from the results view into my SQL but still for some reason if my constraint does exist it jumps to the ELSE.
Code SnippetIF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '[DF_TBL_SyncTable_OB-DEVUK]')
BEGIN
ALTER TABLE TBL_SyncTable DROP CONSTRAINT [DF_TBL_SyncTable_OB-DEVUK]
ALTER TABLE TBL_SyncTable ADD CONSTRAINT [DF_TBL_SyncTable_OB-DEVUK] DEFAULT 2 FOR [OB-DEVUK]
ALTER TABLE TBL_SyncTable ADD [OB-DEV6] TINYINT DEFAULT 2
END
ELSE BEGIN ALTER TABLE TBL_SyncTable ADD CONSTRAINT [DF_TBL_SyncTable_OB-DEVUK] DEFAULT 2 FOR [OB-DEVUK]
ALTER TABLE TBL_SyncTable ADD [OB-DEV6] TINYINT DEFAULT 2
END
Thanks for replies + suggestions + comments
Regards, Onam.
View 6 Replies
View Related
Sep 3, 2007
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 3 Replies
View Related
Oct 2, 2006
Hi,
I need to call a stored procdure from a SQL statement that I am running from query analyzer.
My SQL statement will select from a master table of bills. I have a stored procedure that calculates the amount due on each bill. The sp does not use the master table in the calculation.
How do I form the SQL statement to call the sp, which needs one of the master table columns as a parm?
i.e.
SELECT *, EXEC sp_abc columns name AS whatever
FROM tblMaster WHERE......
TIA!
Dave
View 3 Replies
View Related
Jan 8, 2002
How many characters can be used in a query?
Say I have a query that inserts into a varchar(4000). I need to do ~100 of them. Can all this data be passed at once. Or would I need to break it up?
Thoughts?
Dano
View 1 Replies
View Related
Jan 30, 2008
Hi,
to fully support SQL server 2005, I'd like to use DDL statements instead of deprecated stored procdure calls, I'm having trouble "translating" this one :
sp_fulltext_catalog 'catalogName', 'start_incremental';
The only option available with ALTER FULLTEXT CATALOG are REORGANIZE and REBUILD.
Any idea ?
Thanks for your help
View 1 Replies
View Related
May 20, 2015
I have a function like below
CREATE FUNCTION [dbo].[UDF_GetCode]Â
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)
[code]...
This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.
View 3 Replies
View Related
Jun 16, 2006
I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?
TIA,
barkingdog
View 1 Replies
View Related
Mar 13, 2008
I'm expecting to revamp some stored procs so that their selects are executed on a dynamic string that always returns the same columns but varies the sources.
I'm concerned that the bread and butter of products like RS and SSIS is the ability to predict what columns, and what column types to expect from a query, but that introducing dynamic sql will complicate using them.
I'm motivated not to use temp tables or table vars if possible. I'm also somewhat motivated to learn of a solution that works equally well in 2000 and 2005.
I've tried wrapping dynamic calls in a select as shown below but to no avail...
After€¦
declare @sqlString nvarchar(4000)
set @sqlString = 'select * from [' + @dbName + '].[dbo].[activity]'
I€™ve already tried things like€¦.
select a.* from exec sp_executesql @sqlString a
and
select * from exec (@sqlString)
View 8 Replies
View Related
Mar 31, 2006
Hi everyone,
I have a storedproc. This proc send back a value. how can i call this storedproc in select from block. Or what is your advise for other ways....
Select *
, (Exec MyStoredProc MyParam) as Field1
From Table1
View 1 Replies
View Related
Jul 13, 2004
I want in my query to select a different field in case another one is null. in mysql i'd do it like this:
select
a
,if(b is null, c, b)
,d
from
alphabet
how can this be done in sql server?
thanks
View 2 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Nov 9, 2015
I have a SELECT query that also needs to call a sproc with a column name passed in as a parameter.
Unfortunately I cannot use a TVF, as the sproc code references a TVF on a linked server.
What is the best option?
SELECT
u.UserID,
u.Username,
u.Address,
EXEC dbo.[GetResult] u.UserID AS 'Result'
FROM dbo.UserTests u
View 3 Replies
View Related
Jan 9, 2015
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.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Aug 4, 2015
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
View 4 Replies
View Related