Retrieving Selected Records

May 29, 2001

Hi every body,
I am making a program which is currently dealing with thousands of records.
What I want is to have a button in the front END which make me able to fetch only first 100 record.If the desired record doesn't come with in first 100 record I press this button another time to fetch next 100 records i.e. from 101 to 199.
How can it be possible in SQL Server stored procedure.

Eagerly waiting from all of you GENIUS people.
Kailash

View 1 Replies


ADVERTISEMENT

Retrieving Selected Join Records

Nov 22, 2006

Hi,
I have the folowing 3 (SS2005) tables:


CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,


CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL,
[TransactionTypeID] [int] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Created] [datetime] NOT NULL,


CREATE TABLE [dbo].[tblMailing](
[MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL,
[SubscriptionID] [int] NOT NULL,
[MailTypeID] [int] NOT NULL,
[MailDate] [datetime] NOT NULL


So for each subscription there can be 1 or more transactions and 0 or
more mailings, and the mailings are not necassarily related to the
transactions. What I am having difficulty doing is this:


I wish to select tblMailing.MailingID, tblMailing.MailDate,
tblMailing.SubscriptionID (or tblSubscription.SubscriptionID),
tblSubscription.SubscriberID, tblSubscription.Status,
tblTransaction.TransactionID, tblTransaction.Created, but I only wish
to retrieve rows from the transaction table where
tblTransaction.Created is the latest dated transaction for that
subscription.
I.E. (maybe this makes more sense..:) I wish to select all rows from
tblMailing along with each mailing's relevent subscription details,
including details of the LATEST TRANSACTION for each of those
subscriptions.


I am currently working along the lines of MAX(tblTransaction.Created)
and possibly GROUP BY in a subquery, but cannot quite figure out the
logic.


Any help appreciated.


Thanks, KoG

View 4 Replies View Related

Retrieving Same Set Of Rows Selected Randomly

Nov 9, 2004

Hi All


by using this query

"select * from sample order by newid()" im getting a set of rows. On refreshing this query i need the same set of rows to validate.(provided sufficient data in the table).

Please provide me the query to use for this

Adv. Thanks
Hari...

View 2 Replies View Related

Retrieving Selected Gridview Column Values For SQLDatasource Asp:controlparameters

Jun 12, 2006

Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure.  The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples.  It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error".  Maybe the DataKeyNames is not right??  I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same.  It's got to be something very simple, but I'm at a loss.  All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters.  Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):
<asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False"
ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" />
<asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" />
<asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" />
<asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True"
SortExpression="loadid" />
<asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" />
<asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" />
<asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" />
</Columns>
</asp:GridView>
and the sqldatasource's info:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" >
<UpdateParameters>
<asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter>
<asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter>
<asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter>
<asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter>
<asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter>
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue"
Type="Int32" />
<asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
 
TIA,
John

View 1 Replies View Related

Update Records Just Selected (in One Query)

Mar 28, 2006

I have a stored procedure that I give some parameters and it joins a couple of tables and returns the results.  I would like to know how to update the records in one of the original tables before returning results.
CREATE Procedure dbo.DS_GetSomething(@someValue char(5)AS
Select table1.* ,table2.* FROM table1 left inner join table1.itemid on table2.itemid where table2.someValue = @someValue
The table has a field of IMPRESSIONS that I would like to do something like:
;UPDATE table1 set impressions=impressions + 1 where recordid=??
I don't know how to access the recordids I just got from the original select statement.
Thanks for any help
Greg

View 7 Replies View Related

Help Please Retrieving Unique Records.

Apr 15, 2008

I'm trying to read from a table and return only the unique records from a table.
The table has 3 columns,
ID - Autoinc
Amount - decimal 6,2
Name - varchar(10)

If there are records like

ID Amount Name
1 0.03 Name1
2 0.07 Name9
3 0.05 Name3
4 0.03 Name8
5 0.07 Name4
6 0.06 Name7


I am wanting to retreive only records 3 & 6 (values 0.05
0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.

I put the following sql statement together which achieves this but it it only returns the amount value
select Count(amount),amount from bids group by amount having count(amount)<2 order by amount

So I then expanded it to read
select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount
not sure where i'm going wrong but the results returned were
0.03
0.03
0.05
0.06
0.07
0.07

Appreciate any help here.

View 2 Replies View Related

How To Count Number Of Selected Records And Store Result To Another Column

Jul 4, 2012

Code:

SELECT ((ACOS(SIN(34.37769 * PI() / 180) * SIN(latitude * PI() / 180)
+ COS(34.37769 * PI() / 180) * COS(latitude * PI() / 180) * COS((132.404738 - longitude)
* PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS ranges FROM completeyuubinwithlatlon having ranges<=3

First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.

View 3 Replies View Related

Retrieving The First N Records From A SQL Query In VS 2005

Apr 6, 2006

Hi,
first off, I'm a TOTAL novice at this stuff, I'm just currently blundering my way through a complex site to learn stuff.
I'm trying to call the newest addition to a SQL database into a webpage, in this case, it'll be 'newest user', one result only. I've done several other data retrival sections using a datatable, but the guy who was helping me though it is unavailable at the moment and I get the feeling I've jumped into the deepend slightly.
Could anyone give me an example of how retrieving the First N Records from SQL should look in VS? Does it need to be in a data table or can it go in a label?
Sorry if this is somewhat vague, but as I said, I've really only been using VS for a week!

View 3 Replies View Related

How To Create Sql For Retrieving 10 Records Per Time In .NET

Aug 4, 2004

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.

Thanks

View 6 Replies View Related

Retrieving Records And Inserting Into Table

Mar 19, 2008

Folks:

I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?


SET NOCOUNT ON

DECLARE @ImportId INT
SET @ImportId = 5151

DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)


SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)


===================================================================================

Same Query when trying to insert the records in a temp table it takes hours.

===================================================================================

SET NOCOUNT ON

DECLARE @ImportId INT
SET @ImportId = 5151

DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)

create table #TResults
([ID] [INT] IDENTITY(1,1) NOT NULL,
DealName VARCHAR(200),
CUSIP VARCHAR(100),
Vintage INT,
PoolType VARCHAR(100),
PaidOff BIT)


INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)

SELECT * FROM #TResults


============================================


Thanks !

View 7 Replies View Related

Retrieving Records Within An Index Range, The Nth Record?

Mar 5, 2007

if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.

View 14 Replies View Related

DB Engine :: Table Partition - Retrieving Records

Jul 2, 2015

I need to partitioning the table on which most expensive query run.

Every day 500000 lac records inserted/update in that table

How to create what impact on performance while retrieving the records.

View 5 Replies View Related

Trouble Writing Stored Procedure To Retrieve Records By Selected Month And Year

May 15, 2007

hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev]  [Selected Month]  [Next>>]
 the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).  
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!

View 2 Replies View Related

SQL Question ( Retrieving Records Based On A Priority Order )

Feb 24, 2008

Hi all,I have a table with this structure:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/4/2007 | M1 | 1/2/2008 | R2 | 1//5/2007 | M2 | 1/6/2007 | RFor each customer ID, I need to retrive only the records with the mostrecent transaction date, and I did:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/2/2008 | R2 | 1/6/2007 | RThe problem is, each customer can make more than one transaction inthe same day (see above). In these cases, I need to retrieve only onerecord per customer, based on the importance of the transaction: F ismore "important" than R which is more "important" than M. So, in theexample above, I would only retrieve transaction F for customer 1 anddisregard transaction R: they were made on the same date, but I ammore interested in F than in R.Do you have any suggestions on how I could achieve this in SQL?I use Microsoft SQL server 2005.Thank you for your help!

View 1 Replies View Related

Reporting Services :: How To Display (All Selected) When Parameter (Select All) Is Selected In SSRS

May 6, 2015

Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.

Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.

View 3 Replies View Related

SQL 2012 :: Selected Subscriber Does Not Satisfy Minimum Version Compatibility Level Of Selected Publication

Feb 21, 2014

I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"

The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...

View 0 Replies View Related

How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.

Apr 30, 2007

Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.

View 4 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

If There Is No Picture Selected

Apr 2, 2008

Dear readers,
On my page people can opload foto's with a <asp:FileUpload ID="myfile" runat="server" BorderWidth="3px" BorderColor="Silver" BorderStyle="Inset" />
and stored the filePath in the database
Now i want, that when there is no picture selected, the path to Noimage.jpg picture who is in my image map will stored on my datadbase.
my script start like this If Not (myfile.HasFile) Then

////how can i select a standard image from
my imageMap and store path to database /////


Dim cnn As Data.SqlClient.SqlConnection

Dim cmd As Data.SqlClient.SqlCommand

Dim strSQL As String

Dim connString As String = (ConfigurationManager.ConnectionStrings("Personal").ConnectionString)
strSQL = "Insert Into tblMateriaal(ArtikelGroep,Artikelnaam,ArtikelType,ArtikelMaat,Aantal,Prijs,ContactPersoon,EmailAdress,Aanvul) Values(@ArtikelGroep,@ArtikelNaam,@ArtikelType,@ArtikelMaat,@Aantal,@Prijs,@ContactPersoon,@EmailAdress,@Aanvul)"
cnn = New Data.SqlClient.SqlConnection(connString)

cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)

Dim plaatje As New Data.SqlClient.SqlParameter("@plaatje", System.Data.SqlDbType.NVarChar)
plaatje.Value = " "
cmd.Parameters.Add(plaatje)

Dim ArtikelGroep As New Data.SqlClient.SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar)
ArtikelGroep.Value = ddl1.SelectedValue
cmd.Parameters.Add(ArtikelGroep)

Dim ArtikelNaam As New Data.SqlClient.SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar)
ArtikelNaam.Value = tb1.Text
cmd.Parameters.Add(ArtikelNaam)


cnn.Open()

cmd.ExecuteNonQuery()

cnn.Close()
End if  Lots of thanks

View 2 Replies View Related

Update From Selected Row

Apr 30, 2007

Hi,

I am having a problem updating one field in a table the update should be the product of two other fields from the same row.
There are atleast 3000+ records need to be updated here.


e.g.

update A
set A.b = A.c * A.D

here b c and d are from same row .. I was wondering if someone knows how to solve this problem.

Thanks in advance.

View 8 Replies View Related

WildCard If Not Value Selected

May 7, 2008

HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).

Current Statement:
And tblRetailer_Contact.faxcontact LIKE *

I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’

This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?

DECLARE @FaxContact as varchar (50)

SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)

SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'

Thanx so much,
Trudye

View 11 Replies View Related

Formatting A Selected Value

Jan 25, 2006

I am new to writing sprocs so forgive me if this is trivial. I am selecting fields from a table and placing them into a temp table in row format. (Row 1 in temp table is the first row in a file that will be created using DTS package). My question is: How can a format a field that I have selected that only has, say 3 chars, into a value of 5.
Ex: field in DB = aaa
I need to format it as: 2 spaces + aaa

But the length of the value will be varying from record to record.

View 7 Replies View Related

Selected Backup

Jan 23, 2008

I need to get the Bacup of my SQl 2000 database
but i need only last 100 records of all tables in my database.
or
i want to create new database from existing , schama is same but i need to import only 1000 record from all tables

View 2 Replies View Related

Selected Value Of Dropdown

Feb 20, 2007

How would I reference the selected value of a dropdown parameter in SSRS using VS2005?

For instance, the city Miami is selected I want to find out if both dropdowns match eachother...

iif(dropdown1.selected.value = dropdown2.selected.value,false,true)

View 9 Replies View Related

Selected Record's Id

Feb 28, 2008

How can I get an Id of selected record? something like




Code Snippet
DECLARE @Id uniqueidentifier

SELECT @Id = ID FROM DataTable




BUT! I need also retrieve a data in the same query. Is that possible or there is a different way? Thanks.

View 4 Replies View Related

Dts Only Selected Rows

Oct 13, 2006

I have to dts rows by timestamp. For example if my dts downloaded at 10 am then in the next run i want to grab rows updated in the AS400 after 10am. what is the best way to go in ssis?

thanks...

kushpaw

 

View 5 Replies View Related

No Cube Selected

Oct 8, 2007

Hello,
I've gotton both the sprocs in these tutorials to work in the C# app:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4503.aspx
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/2271.aspx


But when I try to call them in the Managment Studio I get the following error:
"No cube specified. A cube must be specified for this command to work."

What am I missing here?

Thanks in advance,
Adam

View 8 Replies View Related

Get Data From SqlDataSource For Selected Row

Feb 24, 2007

Hello all! How can I get data from SqlDataSource for row selected in GridView?

View 3 Replies View Related

Listbox Selected Values

Dec 1, 2005

Hi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.

View 2 Replies View Related

Bring A Data That Not Already Selected...

Feb 12, 2006

Hi I have a problem that seems to be stupid… but still I cant solve it so please if you can help ASAP I'll appreciated.
 
Its 2 tables "Courses" and "Facilitators"
The Course table contain: Course_Id and Course_Name
The Facilitators table contain: Facili_Id and Facili_Name
 
The two tables have (many to many) relation between them.
So I split them into a 3rd table called "Trans"  which contain the PK from each Table
That will be : "Trans_Course_Id" and "Trans_Facili_Id"
 
I put some data on each table… at least 3 records and related some of them in the "Trans" table
 
Now im looking for an SQL command that brings me All the "Facili_ID" and "Facili_Name" for a specific course. And only those who is not already selected by the same course?
 
Like if I have the data in the "Courses" tableid: 1 and Name: VB
Id:2 and Name: C#
 
And in "Facilitators" table:
Id:1 and Name: Adam
Id:2 And Name: George
Id:3 and Name: Sam
 
Now in the relation table "Trans"
 
Course_Id:1 and Facili_Id:1
Course_Id:2 and Facili_Id:1
Course_Id:2 and Facili_Id:3
 
Now I want the SQL Commands that brings me the he "Facili_ID" and "Facili_Name"
For Course_id "For example" and should not be selected by the same course…
That would be:
 
Id:2 And Name: George
Id:3 and Name: Sam
 
And the same for eash time I pass the course_id for the command
Thank you.
 

View 10 Replies View Related

Events For Selected Month

May 16, 2006

I know how to get the events that start say on May, and I know how to get the events that end on May, however, How would I get the events that start on January and end in July. The month of May should display that event too.
so far, as an example, I have: SELECT
Events.startDate,
Events.endDate

FROM Events
WHERE
Events.Active = 1
AND
startDate BETWEEN convert(smalldatetime, '5/1/2006') AND convert(smalldatetime, '5/31/2006')

ORDER BY Events.startDate ASC; thank in advance. 

View 3 Replies View Related

Is There Any Functions For Counting Each Row Selected

Nov 23, 1999

Hi !

Is there any function for counting each row selected.

For example if I do "select * from employers order by salary"
and get:

10 Matthew Norton 5000
15 Ben King 4000
13 Caroline Stewart 2500
97 Joe Langkow 1300

How can I rank this so that I can get a number like that Caroline has got the third best salary in the company ?

View 2 Replies View Related

Using A User Selected Parameter

Nov 20, 2006

Ok..............so I'm totally new to this whole SQL thing and I need some help..........please!
I have a table rf_log with the following fields:
PACKSLIP varchar(20)
BINLABEL varchar(8)
EXTENDED varchar(50)
TERMID smallint
USERID varchar(8)
ACTION varchar(8)
QUANTITY int
Q_SCALER smallint
REFERENCE2 varchar(30)
REFERENCE3 varchar(30)
DATE_TIME varchar(23)
DATE_CREAT timestamp(8)
LOCATION varchar(20)
TOTLABEL varchar(20)
CLIENTNAME varchar(15)
PO_NUM varchar(25)
SERIAL varchar(25)
LICENSE_PLATE varchar(22)

What I need to do is to get a list of packslips where the packslip number will BEGIN with the user selected parameter.
For example if the user input is 'ORD000888' then I could possibly get a return of:
Ord000888-0
Ord000888-1
Ord000888-2...............and so on.
I have tried this but unsuccesfully:
SELECT PACKSLIP,BINLABEL,EXTENDED,TERMID,USERID,QUANTITY,Q_SCALER,TOTLABEL,REFERENCE2,REFERENCE3,DATE_TIME, DATE_CREAT,CLIENTNAME,PO_NUM,SERIAL,LOCATION,LICENSE_PLATE
FROM rf_log
WHERE PACKSLIP LIKE ?C

I have also tried:
DECLARE @Pickslip varchar(30)
SET @Pickslip = ?C
SELECT PACKSLIP
FROM rf_log
WHERE PACKSLIP LIKE @Pickslip

None of these worked. Any help would be greatly appreciated.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved