My Report Not Returning Data For Only One Field

Jan 18, 2008

Hi all,


I have a simple query like this:


SELECT crop, ItemNo, LotNo, Site, Process, IntID, ObsDetail, ObsValue,

(CASE WHEN (rtrim(crop) = '010' OR
rtrim(crop) = '010W' OR
rtrim(crop) = '019' OR
rtrim(crop) = '019W' OR
rtrim(crop) = '018') THEN 'CORN' WHEN (rtrim(crop) = '0150' OR
rtrim(crop) = '0159' OR
rtrim(crop) = '0150W' OR
rtrim(crop) = '159W' OR
rtrim(crop) = '158') THEN 'RICE' ELSE '' END) AS Product, TestStartDate, TestEndDate, ObsString
FROM V_LAB
WHERE (LotNo >= @lotStart) AND (LotNo <= @lotEnd)

If I run this query and filled the lotStart and lotEnd parameter with: G0424MK
Then, below's the data returned. (I run the query in management studio)






010W

30A97-CLN-UNT-BULK

G0424MK

IDPMLG01

AFTER CLEANER

4982

PRCT MOIST

10.35

CORN

NULL


010W

30A97-CLN-UNT-BULK

G0424MK

IDPMLG01

AFTER CLEANER

4982

PRCT GOOD

97.5

CORN

NULL


010W

30A97-CLN-UNT-BULK

G0424MK

IDPMLG01

AFTER CLEANER

4982

PRCENTMEAN

97.75

CORN

NULL

I'd only going to retrive 3 data that i'd like to show on the report:
1. Moisture Content, with formula:
=IIf(RTRIM(Fields!ObsDetail.Value) = "PRCT MOIST", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK is: 10.35 (this is correct)

<!--[if !supportLists]-->2. Physical Damage, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCT GOOD", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 97.50 (this is correct)

<!--[if !supportLists]-->3. Warm Test, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCENTMEAN", Fields!ObsValue.Value, cdec(0.00))

the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 0.00 (this is wrong) because it should be shown 97.75



anybody knows which part i made my mistake?



Thanks!
Addin

View 6 Replies


ADVERTISEMENT

SSRS 2005 Report Stopped Returning AS 2000 Data

May 2, 2008

Hello:

Here is the scenario: I have a report in SSRS 2005 that uses an OLE DB connection to send a MDX query to an AS 2000 cube. The report has been working fine for weeks. When the report ran today, it returned the row and column metadata, but not the cell values.

I ran the MDX query separately in the SSRS query editor pane, and it returns all values properly. I was also able to perform the query using ProClarity, so it appears that the issue is between the result set and SSRS.

What would cause this to happen?

Thanks for your help!

Tim


View 4 Replies View Related

Returning Identity Field

Jun 12, 2006

Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot
my sproc:
CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao   int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO
 
class file
public class Cotacoes
{
public int codigoCotacao;
}
public class CotacaoAtualiza {  public Cotacoes cotacoes = new Cotacoes();
  public CotacaoAtualiza()  {  }
  public void AdicionarCotacao(   ...   )  {   SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]);   SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection);
   myCommand.CommandType = CommandType.StoredProcedure;
   ...
   SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4);   paramCodigo_cotacao.Direction = ParameterDirection.Output;   myCommand.Parameters.Add(paramCodigo_cotacao);
   ...
   myConnection.Open();   SqlDataReader result = myCommand.ExecuteReader();
   while(result.Read())    {    this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"];   }
   myConnection.Close();  }
 
calling into code-behind file:
CotacaoAtualiza ca = new CotacaoAtualiza();
Cotacoes cotacoes = ca.cotacoes;
Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);

View 1 Replies View Related

Returning A Count Of A Certain Field

Nov 18, 2014

I want to get a count of how many PosAnswer are associated with the QuesToAsk 'List your top 5 favorite places'

This is the syntax I am using to achieve this, and it works, but wasn't sure if there was a better way (changing the data structure is unfortunately not an option in this situation). Here is my table structure

Code:
Create Table Test
(
QuesToAsk varchar(1000),
PosAnswer varchar(1000)

[Code] ....

And this is the syntax I use to get the count I am after

Code:
DECLARE @VariableName varchar(25)
DECLARE @FormattedVariableName varchar(25)
DECLARE @FieldCount int
SET @VariableName =

[Code] ....

View 5 Replies View Related

SQL Query Not Returning Identity Field - Sometimes

Feb 2, 2006

Hello, I have a C# application that adds records to a SQL Server database using a query something like this one:

INSERT INTO table_name
(first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ;
SELECT SCOPE_IDENTITY() AS [Scope_Identity]

This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records.

(The table I'm adding to does have a autonumbered key field)

Thanks in advance!



View 1 Replies View Related

Sql SELECT Returning NULL On DateTime Field

Aug 8, 2006

Hi there ;
This Problem is goin to make me crazy!
I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 .
I used asp.net 2.0 to insert datetime values, directly,  using sth like DateTime.Now.ToString() .
i use 2 selects as follows :
1)select * from X where  Date1='8/9/2006 11:17:41 AM'
2)select * from X where  Date2='8/9/2006 11:17:41 AM'
#1 is OK, but the second one returns a row with all columns set to NULL .
(X is the name of the table)
Thanks in advance

View 6 Replies View Related

ASP Returning Null Sized Field From MS SQL Database

Jul 20, 2005

We currently have an SQL db running on a web server.One of these fields is a large(ish) amount of text data – up to 400characters – and has been cast variously as varchar, nchar and texttype to overcome a problem. The problem appears to be in retrievingthe data via ASP. I understand that ASP can handle string data of thissize so I am okay there.When the records are retrieved from the db, the data string length =0.I know the data is there because I have written a Delphi data managerwhich interrogates the db and shows all records and their contents.So if ASP can handle strings this size and the data is there, why do Iget a data length of zero bytes returned when I interrogate the recordset?Whichever way I cast this field I get the same result.I know the code is sound as it works locally through a MS SQL serveron my PS.Anyone have this problem or know what's causing it? I have logged asupport call with my hosting company, but they haven't replied as yetand I am stuck on an urgent project.Any suggestions?CheersGrant

View 1 Replies View Related

Query Returning Duplicate Field Names

Jul 20, 2005

I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don

View 1 Replies View Related

Returning Recordset That Includes A DateDiff Field In Hh:mm:ss Format?

Jul 20, 2005

Hi, not too swift with anything other than simple SQL statements, soI'm looking for some help.Using SQL Server 2000 with this stored proc:(@varCust varchar(50))ASSET NOCOUNT ONSELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,d.WIdoc,d.Start, d.StartUser, d.Finish, d.FinishUserFROM tblWorkOrder w, tblDocs dWHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder ANDd.Customer = @varCustGOI'm trying to get a complete dataset so I can simply apply it as thedatasource to a datagrid in asp.net. I need to include a 'TimeSpan'column that is the difference between d.Start and d.Finish. I alsoneed it to present in hh:mm:ss format in the datagrid column. (A) isit possible to do this within the stored proc, and (B) how would "I"do that?Thanks!Kathy

View 6 Replies View Related

SQL Server 2008 :: Returning Unique Rows - Read Date Field?

Jul 26, 2015

I have the following query:

Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn

[Code] ....

The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?

So instead of showing this:

2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370

I'm trying to get it to look like this:

2Person112015-07-26 17:07:24.9370

Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.

2Person102015-07-26 17:07:24.9370

View 1 Replies View Related

Bug? Report Builder Returning Only Unique Records!

Feb 8, 2007

Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.

Example:

TransID Type TransQuantity

1 CS 4
2 CS 4
3 CP 2
4 CN 3

Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:

Type TransQuantity


CS 4
CP 2
CN 3

Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.

Thanks,
Taurkon

View 3 Replies View Related

Returning A Value From A Subreport To The Main Report For Calculation

Feb 27, 2008



I have a need to complete the following:

Main Report (Group level) Sum(FinalPayment) ** Completed
SubReport Sum(PaymentAdvances) ** Completed (passing parameters)

This is the part that I need help. I need to use an original value from the main report and a "return" value from the subreport.
Main Report (Footer) Sum(FinalPayment) + SubReport.Sum(PaymentAdances)

Is it possible to pass a return value back to the main report from a subreport?

Any help or direction on this?

View 5 Replies View Related

Reporting Services :: SSRS Linked Report Based On Calculated Field From Main Report

Oct 6, 2015

How do I get data on my linked report based on my grouped subtotal and grand total from the main report.  The subtotal and grand total are calculated columns.

I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.  

The following is code for my summary report.  The results are shown below.

SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts,
CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST'
WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST'
ELSE 'OTHER'
END AS Actn_COAST

FROM tbl1
where ACTNDATE between @STARTDT and @EndDT

Code for my detail report contains the following SQL

SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY

I have linked my report based on the NumbOfAccts column.  I am able to get data if I click any of the NumbOfAccts values related to the state I want.  However when I am not sure how to make the subtotal and grand total work.  I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast.  If I click on the Grand Total, I only want data related to those 2 coasts.

View 2 Replies View Related

Report Model: Drill On Calculated Field In Report Builder

Feb 11, 2007

Hi,

I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.

Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.

Questions:

1) Can I remove the link from the calculated fields?

2) Can I prevent from users drill to fields that not in the smdl?

Thanks,

Assaf

View 1 Replies View Related

SSRS Reporting From Custom List Returning 1;# In SQL Report

Apr 28, 2008

Hi,

I have a set up SSRS which is reporting from a custom list in sharepoint to SQL 2005. I have created a parameter on the SQL report to return data from a field in the sharepoint list, however when it retrieves the data i get 1;# in front of the returned data?

Why would this happen?

Any help would be much appreciated, thanks.

View 5 Replies View Related

SQL Server 2012 :: Data Grouping On 2 Levels But Only Returning Conditional Data

May 7, 2014

I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.

So here is the DDL, sample data, and the ideal return rows

CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,

[Code] ....

I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.

SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1

One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.

View 9 Replies View Related

Transact SQL :: Server Text Field Not Returning Full Text

Apr 21, 2015

I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.

View 3 Replies View Related

This Feature Remote Access To Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Report

Jun 16, 2006

SQL server 2005 express reporting problem.

error message:

This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service

I got this error message when I try to connect to database hosted in another PC running SQL server 2000.

Is it true that SQlL server Express can only use Local Database Engine to host the database?



View 5 Replies View Related

Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.

Jul 11, 2005

I'm using DTS to import data from an Access memo field into a SQL Server ntext field.  DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View 4 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

How To Matrix: Conditional Data-field Bg-color When Field Is Subtotal

Mar 25, 2008

How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???

View 3 Replies View Related

SQL Server 2008 :: Selecting Field That Contains Data From Another Field

May 28, 2015

We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.

ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY

what I need to do is identify where the Brand is in the Description field ...

I have tried ;

select * from Table
where Description Like Brand

not very successful.

View 3 Replies View Related

Importing Of Varchar Field Data In Number Field

Dec 5, 2007

i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?


jto it

View 5 Replies View Related

Grouping By One Field And Combine Data Of Another Field In SSRS

Apr 2, 2008

Hi all experters,

Please suggest how to build the report in below case:

Raw data:






ID
Member
Functions

1
Alan
A

1
Alan
B

2
Tom
A

2
Tom
B

2
Tom
C

3
Mary
D

3
Mary
E


Report Shows:






ID
Member
Functions

1
Alan
A,B

2
Tom
A,B,C

3
Mary
D,E


I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.

Any good suggestion?

Thanks in advance,
Steve Wang
2008/4/2

View 6 Replies View Related

Sql Not Returning Any Data

Feb 20, 2007

Hello,
Im currently working on a asp.net file hosting wesite, and im being faced with some problems
I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User.
I already have an sql statement that performs this exact thing. It is as follow
select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName)
But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something.
For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero.
Is there  way around this?
Im not sure if ive been cleared enough, please feel free to request more info as needed.
Thank you very much, and thx in advance.

View 5 Replies View Related

Most Efficient Way Of Returning Data

Feb 22, 2007

I have a number of tables, and I need to create a summary of the data.Now I have two choices. I could create a stored procedure that creates a temp table with the filters applied. This would require 12 selects for each year (we have 3 years of data so far). This means 36 selects so far that fill a temp table.The second choice is to create a table with the required columns and rows. As I am esspecially cross-joining 4 tables, the table would have about 10 x 10 x 12 x A rows, where A is a variable that will grow quickly. I can then keep this table up-to-date using triggers for each insert. Then all I need to do is use an aggregate funtion on the relevant filter.My question is, which is more efficient. Creating a stored procedure that creates the table dynamically, or have a table with thousands of rows and using an aggregate function on these.PS I am using SQL Server 2000Jag 

View 1 Replies View Related

Returning Column Name(s) That Contain Data

Jun 25, 2007

say i have some columns and a person can vote either once, twice or 3 times ( checkbox style control ) and in the database it looks something like this: eg:    ID.... vote1.....   vote2.... vote3           1         0             0            1          2         1             0            1for ID = 2, what type of query would i have to write to return the column names vote1 & vote3 ( vote2 is not null ) Cheers!!!  

View 1 Replies View Related

Query Not Returning Data!

Oct 21, 2007

Hi! I have a sql query in stored procedure: SELECT     Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.

View 4 Replies View Related

READTEXT Not Returning All Data

Apr 12, 2004

SELECT @@TEXTSIZE
SET TEXTSIZE 4096
SELECT @@TEXTSIZE
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(emailTemplates.Data)
FROM emailTemplates WHERE [Name] = 'AccountInfoReceipt1'
READTEXT emailTemplates.Data @ptrval 0 2000
GO


Thats my code. and it never returns the entire email template. why?

thx in adv

View 2 Replies View Related

Problem Returning Data

Apr 2, 2006

I have a stored procedure (in SQL Server 2005 Express) that returns a string.  The problem is when I call it from my web page I get only the first character of the string.  This is my SP:
ALTER PROCEDURE dbo.usp_CalcDeliveryCharge
@mintDistance int,
@mintCustomer_ID int = 0,
@mintRate int = 0 OUTPUT,
@mstrZone nchar(10) = null OUTPUT
AS

/* SET NOCOUNT ON */

SELECT @mintRate=RATE,
@mstrZone=ZONE
FROM tblRates
WHERE Customer_ID=@mintCustomer_ID
AND Mile_Range_Min <= @mintDistance
AND Mile_Range_Max >= @mintDistance
 
And this is my code:
sql_Command.CommandText = "usp_CalcDeliveryCharge"
sql_Command.CommandType = CommandType.StoredProcedure
sql_Command.Parameters.Clear()
sql_Command.Parameters.AddWithValue("@mintDistance", intApproxMiles)
sql_Command.Parameters.AddWithValue("@mintCustomer_ID", Profile.CompanyID)
sql_Conn.Open()
sql_Reader = sql_Command.ExecuteReader()
While (sql_Reader.Read())
Me.lblZone.Text = sql_Reader.Item(0).ToString
End While
sql_Conn.Close()
sql_Reader.Close()
sql_Command.Dispose()

View 1 Replies View Related

2 Querys Not Returning The Same Data

Apr 17, 2008

I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:

SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue,
pf.pFigure
FROM dbo.PerfFactorWeights AS pfw INNER JOIN
dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN
dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN
dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN
dbo.Division AS d ON pfw.division = d.uniqueID
ORDER BY incentive, d.uniqueID, b.basis

View 4 Replies View Related

Query Not Returning Data

May 29, 2008

Hello everyone,

I set up a View in SQL Server 2005. The syntax checks ok, however, when I execute it, it doesn't return any data.
This is my Query:

SELECT DATEPART(hh, Time) AS Time, COUNT([Recipient-Address]) AS [CountOfRecipient-Address], ROUND(SUM([Total-bytes]) / 1048576, 2)
AS [SumOfTotal-Bytes]
FROM dbo.TrackingLog
WHERE (RIGHT([Recipient-Address], LEN([Recipient-Address]) - PATINDEX([Recipient-Address], '@')) IN
(SELECT Domains
FROM dbo.Domains))
GROUP BY DATEPART(hh, Time)


The part that I am most concerned about is the WHERE section. If I remove it, I get some data returned. If I don't, obviously I don't get anything back.

Any suggestion would be great.

Thanks

View 4 Replies View Related

Problem Returning SQL Data

Apr 9, 2007

I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.

Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:

(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...

Related Links:
John Doe interviewed by Susan Smith, 123 News

What it should say in the link is this: Mark Crisp's blog (as text link)

When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)




Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:webserviceCompanyCompany.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.

IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number

strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"

rs.Open strSQL,con 'open a connection to the database

%>

<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>


<!-- END OF THE GUESTS AND LINKS SECTION -->

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>


Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).

What am I missing?

View 1 Replies View Related







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