MS SQL Server :: In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?

Need To Set A Field In A Select Statement Equal To Yes Or No If Record Exists In Separate Table
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 Replies !

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys,
I have the query below (running okay):

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:

Field01           Field02

-------------          ----------------------

192473           8461760

192474           22810

Because other reasons. I need to modify that query to:

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810

Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View Replies !

LIKE Syntax In A SELECT Statement
What is the syntax for making a query using like, the below is my code
<asp:SqlDataSource ID="Search" runat="server"
ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
ProviderName="<%$ ConnectionStrings:DatabaseConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Products]WHERE category LIKE %@category%">

<SelectParameters>
<asp:QueryStringParameter Name="category"
QueryStringField="category" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
Its giving a syntax error but I dont know how to change it.
Please advise, thanks!

View Replies !

SQL Syntax With For Select Like Statement
Hello,

I have the following statement that I am trying to convert to a "like" statement in a SqlDataSource for a web application. I can't seem to get the syntax correct. Would someone be able to assist with this? Thanks!
SELECT * FROM [Employees] WHERE ([LName] = @LName) ORDER BY [LName], [FName]
Something like below.
SELECT * FROM [Employees] WHERE ([LName] LIKE '@LName%') ORDER BY [LName], [FName]

View Replies !

Syntax For IF-THEN In SQL Select Statement
I need to use IF-THEN in a SQL Select statement. Using Google
I have found a couple of obscure references to this but nothing that I
can use. Tried various combinations without luck. Can
anyone point me to a good resource or supply a simple example of proper
syntax? Thanks in advance for any help provided.

View Replies !

If STATEMENT Within Select Statement Syntax
Hi,

I am a newbie to this site and hope someone can help....

I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:

if(TL_flag= '1', "yes") as [Trial Leave]

it is coming up with an error.... I can use Select case but I should not need to as this should work?

Any ideas?

View Replies !

Retrieve A Recordset From One Table Based On The CSV Values In The Field Of Another
Hello!

I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:

Tab1

SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
S102 | Def of S102

Tab2

DepID | Purpcode |SubCodes
-----------------------------
1 | P100 | S100,S101,S102
1 | P101 | S100, S101
2 | P101 | S100,S102

I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:

SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39)
FROM Tab2
WHERE DepID = 1 AND PurposeCode = 'P101')

The subquery will return: 'S100','S101' and I expect the final recordset to be:

SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101

However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.

SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN ('S100','S101')

it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..

Cheers
Greg

Complete DDL, Sample Data, and Query below:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SubjectCodeDefinition]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DepartmentReturn]
GO

CREATE TABLE [dbo].[SubjectCodeDefinition] (
[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DepartmentReturn] (
[DeptID] [int] NULL ,
[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
SELECT 'S100', 'Definition of Code S100' UNION ALL
SELECT 'S101', 'Definition of Code S101' UNION ALL
SELECT 'S102', 'Definition of Code S102' UNION ALL
SELECT 'S103', 'Definition of Code S103' UNION ALL
SELECT 'S104', 'Definition of Code S104' UNION ALL
SELECT 'S105', 'Definition of Code S105'
GO

INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
SELECT 1,'P100','S100,S101,S104' UNION ALL
SELECT 1,'P101','S102,S103' UNION ALL
SELECT 1,'P102','S100,S101,S105' UNION ALL
SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
SELECT 2,'P103','S103,S104,S105' UNION ALL
SELECT 3,'P100','S100,S102,S104'
GO

SELECT SubjectCode ,Definition
FROM SubjectCodeDefinition
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39)
FROM DepartmentReturn
WHERE DeptID = 1 AND PurposeCode = 'P102')

View Replies !

Proper Syntax For Bulk Insert?
Hi,

I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.

How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?

Here is what I'm doing so far:

CREATE Procedure Insert_From_Personnel
@emp_num char(10),
@Frst_Name char(10),
@Last_Name char(10),
@DivisionID char (4)

as

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

VALUES (@emp_num,@Frst_Name,@Last_Name,@DivisionID)
GO

Thanks for any help,

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 !

Retrieve Data From Properties Field In Prifile Table In Aspnetdb.mdf For Multi Users ?
I want retrieve data from properties field in prifile table in aspnetdb.mdf for multi users,what do i ?

View Replies !

The Select Statement Is In A Field
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 Replies !

Proper Syntax For Adding Object Name Owned By Domain User
I am receiving an error based on my query that is giving me the following error:

Code:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Config'.

I'm assuming that this is because the table was not made by dbo, but my domain account.

The query is currently defined as:

Code:

SQL = "SELECT * FROM Config"

but I'm assuming it needs to be defined with the syntax of ownername.objectname. My problem is when I change the string to this:

Code:

SQL = "SELECT * FROM sanm.APHinzeE.Config"

I get this error:

Code:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.

I tried to escape the slash using / but then I get the error on that. How do I specify my account name with the slash?

View Replies !

Select Statement With A New Identity Field
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 Replies !

Query To Sum The Same Field Twice In The Select Statement
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 Replies !

Select Statement Eliminate Field Name
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 Replies !

How Do I Call A Select Statement Properly When The Field Is A Yes/no?
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 Replies !

SELECT Statement - How To Not Get Column Field Names?
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 Replies !

Select Statement With Run Time Field Selection

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 Replies !

Syntax Error With "alter Table" Statement
Folks!

What is wrong with my syntax with the following command?:

alter table EmployeeInfo alter column OriginDate smalldatetime not null default getdate()

I'm getting:

Incorrect syntax near the keyword 'default'

Currently, in my table OriginDate is nullable with no default.
Thanks in advance for your help!

APF

View Replies !

Convert A Time Field In The Select Statement Of The Query
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 Replies !

Proper Use Of IF Statement In Stored Procedure?
I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:Cannot use empty object or column names. Use a single space if necessary.So, what's the correct way of doing the following?IF @filename <> ""BEGIN UPDATE Experimental_Images SET contentType = @contentType, filename = @filename WHERE (id = @iconNo)END

View Replies !

Executing Sql Code From Text Field Or From Multiple Varchar(8000) Rows In A Table
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn

View Replies !

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,
I am having this error when using execute query for CTE

Help will be appriciated

View Replies !

Using Result Of A Select Statement From One Table To Add Data To A Different Table.
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?

View Replies !

Select The Insert Value Into Field In Another Table!!!!!!
i have tbl_location which includes userid, building, room. i combine the building and room into one feild called mailstop

SELECT Userid, Building +'/'+Room AS mailstop
FROM tbl_Location

i then want to take this recordset and insert it into a field called mailstop in my employee table. but they must based upon the userid of the location table and the userid of the employee table.

so the userid of the location table must match the userid of the employee table and insert that mailstop value into the mailstop feild in employee table. i want to get this right the first time. any help would be greatly appreciated.

View Replies !

SELECT Table Field Names
I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

View Replies !

Referencing A Field In Another Table Within A SELECT
I have a SELECT line in an UPDATE query that looks like this:

UPDATE Terms
SET Field1 = LEFT(Emp_SSN,9),
Field2 = Health,
Field3 = (SELECT Emp_PayCode FROM Rates WHERE Health = ID)
FROM Inserted,Rates
WHERE Terms.Field1 = Emp_SSN

On the Field3 update, how do I evaluate Emp_PayCode. Emp_PayCode is a field in another table (Employees). The value of that field is Instructional24 and if I hard code that value into the SELECT line, it works fine and returns the value ($0.00).

View Replies !

Looking For Field Name From Table On Select Criteria
Hari writes "Sub:- looking for Field name from table on select criteria

dear friends

I have an table [assosories] which have 100 fields. initial 10 fields have some date, varchar, int types rest 90 are bool type.

for a single row I need to know the field name which have true vaules in rest 90 fields

I need to insert this field name into another table as a row.

Thanks
HARI"

View Replies !

How To Select Field Names Of Table?
Can someone write a query that select all the fields of tables in database that have type 'image'?

Something like this:

Select TableName, FieldName FROM TableWhereTheyKeepThoseThings
WHERE TableWhereTheyKeepThoseThings.FieldType='Image'

... olny it should work :)

View Replies !

Help With Select Statement - From XML Table
I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.

param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'

CREATE PROCEDURE [GetDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int)

INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)

SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0

View Replies !

Best Way To Retrieve One Row For Use In Code
i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it.
assume i have a query like this.
Select A, B, C from Table1 where ID=xyz
whats the easiest way to get A, B and C in to string variables?
I know i can use the sqldatasource control but i just feel there is too much overhead there.
whats your suggestions?
Thanks
Justin

View Replies !

Help With A Query- Select A Top Field And Join It With Another Table
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!

View Replies !

Variable For The Table Name In A SELECT Statement.
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20))
AS
BEGIN TRANSACTION
SET NOCOUNT ON

DECLARE @ProjectNumber int
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SELECT MAX(@ProjectNumber)
FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','');
END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.

View Replies !

SELECT Statement With An Attendance Table
I have a table that contains the following structure and information:

Code:

UID Member_Number Time_Stamp Status Reason Event Reg F_Name L_Name
18772054062/3/2005 11:48:27 AMInNonenoneNoneWendyBoud
86930082522/3/2005 12:39:35 PMInNonenoneNoneJeremyAhlman
98772054062/3/2005 12:40:20 PMOutNonenoneNoneWendyBoud
106930082522/3/2005 12:40:45 PMOutNonenoneNoneJeremyAhlman
118772054062/3/2005 12:40:50 PMInNonenoneNoneWendyBoud
128772054062/3/2005 12:46:25 PMOutNonenoneNoneWendyBoud

I need to be able to take this information and display it in a data grid so that on each row I see the Member Number, First and Last Name and the In and Out Time.

I am not sure how to group the In and Out times together so that the query knows which time out corresponds to which time time for the member?

Any help is greatly appreciated!

View Replies !

Creating Table With A Select Statement
Dear folks,

create table temptable(eno, ename) as select eno, ename from emp.

here the problem is it is asking for the datatype for the temporary table.

is it not possible to create the temp table without providing the datatypes?

thank you very much.

Vinod

View Replies !

Need Help W/ SELECT From One Table, One Field, Multiple Unique Records
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:

Anna Smith
John Doe

etc. I did not see IDs listed for any of the names in the table when I looked.

There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.

Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:

strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"

This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.

Is there a way to modify this statement to pull only the records that I want?

Thanks for any help you can give.

AJ

View Replies !

Select Statement To Pull Columns From Same Table For Different Ids
I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.

The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.

Thanks

View Replies !

How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?
Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

Robert

View Replies !

Create Temporary Table Through Select Statement
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.

View Replies !

Dynamic Create Table Statement Or SELECT INTO
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?

Example:

I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.

I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.

BTW - this would be a stored procedure...

Any suggestions would be greatly appreciated.

View Replies !

Dynamic CREATE TABLE Or SELECT INTO Statement
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?

Example:

I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.

I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.

BTW - this would be a stored procedure...

Any suggestions would be greatly appreciated.

View Replies !

SQL Query (SELECT Statement) And Table Design
I am wondering if there is a direct query in this case:

I am developing a program to a company which simply sells services
One service may have different prices for different types of clients
The price of any service for any client can change at any time, and I should be able to trace these changes at any time

I made the following tables (simplified): (asterisk for primary key)

(Table)             (Fields)

CLIENT_TYPES :    ID*,        ClientTypeName
SERVICES :    ID*,        ServiceName
PRICES :    ServiceID*, ClientTypeID*, Price,       Date*
ORDERS :    ID*,        Date,           ClientTypeID
ORDER_SERVICES :    OrderID*,   ServiceID*

The field in bold is the area of the question

This is a sample data in the PRICES table:

ServiceID           ClientTypeID    Price    Date1                      1                100        1/1/20072                      1                150        1/1/20071                      2                90         1/1/20072                      2                135        1/1/2007

Now if I want to update a price of service 1 for clienttype 1, I add the following row:

1                      1                100        1/1/2008

So one product for one client can have any number of prices with different dates

The following query:

SELECT * FROM PRICES WHERE ClientTypeID = 1

will retrieve all prices with repeats for a specific client (#1 here)

What I want is a query to retrieve the most recent prices for specific client for all products, even if a query on query

If there is commemts on table design please tell me

thanks for any one who provides help

View Replies !

Retrieve Images From SQL Db: Code Problem.
Hi,
I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005.
All I've done is:

// and Page Display_image.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
try
{

SqlConnection Con = new SqlConnection(
"server=localhost;" +
"database=;" +
"uid=;" +
"password=;");

System.String SqlCmd = "SELECT img_data FROM Image WHERE business_id = 2";

System.Data.SqlClient.SqlCommand SqlCmdObj = new System.Data.SqlClient.SqlCommand(SqlCmd, Con);

Con.Open();

System.Data.SqlClient.SqlDataReader SqlReader = SqlCmdObj.ExecuteReader(CommandBehavior.CloseConnection);

SqlReader.Read();

System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";

// I write this:
System.Web.HttpContext.Current.Response.BinaryWrite((byte[])SqlReader["img_data"]);

// Or this:
//System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream((byte[])SqlReader["img_data"]));

//System.Drawing.Image _newimage = _image.GetThumbnailImage(100, 100, null, new System.IntPtr());

//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);

Con.Close();

}
catch (System.Exception Ex)
{

System.Web.HttpContext.Current.Trace.Write(Ex.Message.ToString());
}
}

Both work the same way. I mean the image is displayed well but when I view code of the web page I see this:

....
GIF89aåo÷óåÝâ€°97)HS¢Å’xL£0¬B´ç¬D(áâKâ€¹â€ ô²â€“Iâ‚¬8`È®û l1ãÂ?#KÅ¾L(Ë†*X±â€°Î"«Mè±â€ Å¸
....

TOO MUCH characters before the html tag. And any code of the master page used for this page does not work as well!

Can anyone help me with this? I've tried for 2 days but I still fail.

Thanks.

View Replies !

SQL Sp Or View To Retrieve Originial Sql Code
I am interested in retrieving the original SQL code of various stored procedures & views that I have created & pass them into a field within a table.

I have used one before but cannot remember whether I used a stored proc or system view to retrieve this information, can anyone be of assistance???

View Replies !

Insert Record Into Temporary Table From A Select Statement
Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chua

View Replies !