Selecting From Two Tables With Different Data - Presenting In One Datalist
Oct 6, 2006
Hello!
I have two tables:
Pressreleases:
| Pressrelease_ID | PressDate | FilePath | Title |
PressLinks:
| PressLink_ID | PressLinkDate | PressLinkUrl | PressText |
I would like to select the the TOP 3 with the most recent dates from either Pressreleases or PressLinks, and present them in a DataList. How do I do that? Selecting from one table is no problem:
SELECT TOP 3 Pressreleases.PressDate, Pressreleases.Filepath, PressReleases.Title FROM Pressreleases ORDER BY PressDate DESC
How do I select from both tables and rename the columns to Date, Path, Text so that I can use it in a Datalist?
Thank you in advance!
View 4 Replies
ADVERTISEMENT
Jan 19, 2007
Hi all
I am having problem in selecting the data from my datalist. Here is teh sample code
<asp:datalist id="dlAttrName" runat="server" OnItemCommand="dlAttrName_SelectData" DataKeyField="attrID">
<ItemTemplate>
<asp:LinkButton ID="lbSelect" Runat="server" CommandName="Select" style="display:none">Select</asp:LinkButton>
<%# Container.DataItem("attrDef1")%>
</ItemTemplate>
</asp:DataList>
Private Sub dlAttrName_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles dlAttrName.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
'Add eventhandlers for highlighting
'a DataListItem when the mouse hovers over it.
e.Item.Attributes.Add("onmouseover","????")
e.Item.Attributes.Add("onmouseout", "????")
'Add eventhandler for simulating
'a click on the 'SelectButton'
e.Item.Attributes.Add("onclick", ?????????????????????, String.Empty))
??????????????????? this should have something like this
Me.Page.ClientScript.GetPostBackEventReference(e.Item.Controls(1)
But I am not able to get the ClientScript option, I don't know which namespace should I import?
I don't know whether I am right or not.
Its urgent please reply back soon,any help will be useful.
Thanks!!!!!!!
View 1 Replies
View Related
Jun 18, 2015
I have below table:
IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints
--===== Create the test table with
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)
and i want to have a table in below format: (query for it)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
View 9 Replies
View Related
Feb 12, 2006
Plugging away at my little message board that I am working on, I have hit another SQL snag. I am trying to select a list of all posts in a thread. I have to get each post, the name of the poster (which comes from a different table - referenced by userid), as well as some profil information, from a 3rd table (also referenced by the userid).
View 2 Replies
View Related
Apr 22, 2006
Everything works great on my development box. I am using GoDaddy for production (ASP.Net v2, SQL 2000).
I am not receiving any errors, so I am stumped; no data from the database is displaying on the GoDaddy pages.
I updated the connection string in web.config to this:
< add name="snsb" connectionString="
Server=whsql-vXX.prod.mesaX.secureserver.net;
Database=DB_42706;
User ID=username;
Password=pw;
Trusted_Connection=False" providerName="System.Data.SqlClient" / >
But I am unsure if this is the issue?? Any insights? This is the page I am working on: www.sugarandspicebakery.com/demo/bakery/default.aspx. So, the page displays fine, but it should be showing data from the database. This particular page uses a DataList with ItemTemplate. There is definitely data in the database, and I have even ran the same exact query from the code using the Query Analayzer on GoDaddt and it returned results
I know there isn't much info to go by, but I am hoping someone has some insight since I have been trying to figure this out for days now!
Thank youJennifer
View 2 Replies
View Related
Sep 26, 2007
I am coding a DataList control for 2 column data from a stored procedure. The 1st column isprogramName and the 2nd is programLink. I used Edit Template -> Edit DataBonding to add<h ref="http.....>, but can not get it right. Please help. Also, what is the Parameter Sourcefor using Logon User ID as a stored procedure paramter when configuring a SqlDataSource? None, cookie, control,..? TIA,Jeffrey
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Style="z-index: 16;left: 29px; position: absolute; top: 82px"><ItemTemplate>programName:<asp:Label ID="programNameLabel" runat="server" Text='<%# Eval("programName") %>'></asp:Label><br />programlink:<asp:Label ID="programlinkLabel" runat="server" Text='<%# Eval("programlink") %>'></asp:Label><br /><br /></ItemTemplate></asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:iTemplatesConnectionString %>"SelectCommand="MyPrograms" SelectCommandType="StoredProcedure"><SelectParameters><asp:Parameter Name="meid" Type="Int32" DefaultValue="3532" /></SelectParameters></asp:SqlDataSource>
View 4 Replies
View Related
Apr 1, 2015
I have a basic load proc (loading a staging area.) that truncates a target table just before inserting fresh data in said table, all wrapped in a distributed transaction. What happens (only with certain tables and very inconsistently.) is the truncate will fail (no error) and duplicate data ends up in the target table since no wipe actually occurs. This proc only started having this issue when we went from SQL 2008 to SQL 2014.
View 9 Replies
View Related
Jul 23, 2005
Using SQL2000. How do I format my select statement to choose one outof 24 different tables? Each table is slightly different and I washoping I could use one select statement and format it on-the-flyinstead of using 24 different ones. I had in mind using a casestatement, something like this:select * fromcase when <input parameter> = 'something1' then tblSomething1case when <input parameter> = 'something2' then tblSomething2...and so on...Thanks for any help.
View 4 Replies
View Related
Nov 1, 2006
Hello I have two tables:
Package
PakageID Destination Source
1 xyz abc
And
PendingPackages
ClientID PackageID Status
1 1 0
How would I create a select statement to select all rows from Package (PackageID, Destination, Source) where the CleintID=1 and the PackageID exists in pending packages with a status of 0?
Can this be done using a select statement or would I have to create a view? I'm not too sure!
Thanks :)
View 3 Replies
View Related
Jul 30, 2001
Hi all,
I have a query similar to this:
select "bcp databasename.."+name+"
from sysobjects where type = 'U'
order by name
What I need to know is that, I need to unselect some of the tables that starts with name cj_. I don't want the tables that starts with a name cj_.
Can someone help me on this.
Thank you
View 3 Replies
View Related
Apr 2, 2007
Hi all.
I'm trying to select from multiple table in one select statement but i'm having problems. Here is the code i'm trying:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoRead]
GO
CREATE PROCEDURE dbo.snow_ors_additionalInfoRead
@Reference int
AS
SELECT
Account.CanTravel,
Account.SEEmployee,
Account.WorkHours,
Account.DrivingLicence,
Account.CriminalConvictions,
Account.CriminalConvictionsDetails1,
Account.CriminalConvictionsDate1,
Account.CriminalConvictionsDetails2,
Account.CriminalConvictionsDate2,
Account.CriminalConvictionsDetails3,
Account.CriminalConvictionsDate3,
Application.VacancyMonitoring,
Application.VacancyMonitoringDetails
FROM Account, Application
WHERE Account.Reference = @Reference AND
Application.reference = @Reference
GO
Any help at all would be great.
thanks people.
View 5 Replies
View Related
Aug 13, 2006
Hi there,I'm new to sql and thus I'm having problems with a specific query which Ihope you guys can help me with.Basicly I have a few tables which I'm trying to do a query on:Table groups contains information about specific groups e.g. "Windows" or"Unix".Table users contains information about specific users e.g. "a", "b" or "c".Table users_groups contain information about group relationship (a user canbe in multiple groups) e.g. (a, Windows), (b, Unix), (a, Unix).In this case user c is ungrouped.Now I'd like to find the users which does belong to group Windows and thosewho do not:select distinct username from users_groups where groupname = "Windows" orderby username asc;This works pretty well for finding users in the specific group. In this casethe result is a.However I'd like to get the opposite result (b and c) but I'm stuck.The problem is that I'd like a list of all users excluding those which arein "Windows"Here is a partial query:select distinct users.username from users left join users_groups onusers.username = users_groups.username where users_groups.username is nullorder by users.username asc;This only gives me those users who are not grouped at all. This mean thatuser b is not in those results.Please advise.Thanks in advance.-- Henrik
View 2 Replies
View Related
Aug 5, 2004
Hi there,
I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.
So I just want to use a cursor on a table list that meet the criteria:
1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '
Example
declare @strqry varchar(1000)
declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
open dailyYear
fetch next from dailyYear into @DelTable
while @@FETCH_STATUS = 0
begin
Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '
exec(@strqry)
fetch next from dailyYear into @DelTable
end
close dailyYear
deallocate dailyYear
Any help would be greatly appreciated!!
Thanks
S
View 3 Replies
View Related
Sep 2, 2004
Hi there,
Is there a quick way to select all the tables in the DB that don't have certain column in the table, so for example getting a list of all tables that don't have columns: A, B or C?
Thanks
S
View 4 Replies
View Related
Feb 19, 2004
Hello,
Is it possible to get a list of rows from Multiple tables which have the same Column Name. I have 3 tables which comtain similar info
and I want to get a list of Names
the structure is
ID;Name;Address;Phone No.
I was thinking something along the lines of SELECT Name FROM TABLE1,TABLE2, TABLE3
But this does not work.
Is there a nice way of doing this with SQL or should I do code outside the SQL DB
View 4 Replies
View Related
Apr 18, 2008
Hi
Is there a way to select out the contents of sys.comments for an object (in this case, a procedure) so that it is nicely formatted?
The reason being, is that I want to update source control with all objects from the live environment (as it has become out of date.... long story). Currently, we have a single script for each object, split in directories for each object type. Anyway, I also want to wrap the objects in our standards (check for object existence, print out success/fail statements, etc).
Any suggestions to make the process more automated and less painfull? I don't want to have to script each object out, manually wrap it in standards and save as that would be perhaps the most tedious task in the world!!!!
Thanks!
Hearty head pats
View 2 Replies
View Related
Aug 27, 2007
Hello Tsql experts,
Can you please explain to me what the differnce is between selecting into a table and creating a new tabel and inerting in to it like so:
First way:
select names
into #ancestors
from names
and
second way
create table #names(
name varchar(10)
)
insert into #names (name) values(name)
select names from names
is it just personal choice or performance in one method is better than the other.Can you please explain.
Thanks
View 3 Replies
View Related
Jan 20, 2006
Hi,
I'm having some troubles getting my UPDATE to work with my DataList control. I am trying to get Optimistic Concurrency working, but i'm getting an exception stating that the dictionary for oldValues is empty. I understand this is referring to the original values it compares with before performing the update, however, my question is how do I fill this dictionary in with the original values?
I have included a code sample of the sort of thing i am trying to achieve, it is just a simplified example of how I am trying to UPDATE the underlying data source. Am I on the right track? What do I need to do in order to get this to work?
Thankstrenyboy
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = e.Item.ItemIndex;
DataList1.DataBind();
}
protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
SqlDataSource1.UpdateParameters["UnitPrice"].DefaultValue = ((TextBox)e.Item.FindControl("UnitPriceTextBox")).Text;
SqlDataSource1.UpdateParameters["UnitsInStock"].DefaultValue = ((TextBox)e.Item.FindControl("UnitsInStockTextBox")).Text;
SqlDataSource1.UpdateParameters["UnitsOnOrder"].DefaultValue = ((TextBox)e.Item.FindControl("UnitsOnOrderTextBox")).Text;
SqlDataSource1.Update();
DataList1.EditItemIndex = -1;
DataList1.DataBind();
}
protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = -1;
DataList1.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [ProductName], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ProductID] FROM [Products]" UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [UnitsInStock] = @original_UnitsInStock AND [UnitsOnOrder] = @original_UnitsOnOrder" OldValuesParameterFormatString="original_{0}" ConflictDetection="CompareAllValues">
<UpdateParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="UnitsInStock" Type="Int16" />
<asp:Parameter Name="UnitsOnOrder" Type="Int16" />
<asp:Parameter Name="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_ProductName" Type="String" />
<asp:Parameter Name="original_UnitPrice" Type="Decimal" />
<asp:Parameter Name="original_UnitsInStock" Type="Int16" />
<asp:Parameter Name="original_UnitsOnOrder" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
</div>
<asp:DataList ID="DataList1" runat="server" DataKeyField="ProductID" DataSourceID="SqlDataSource1" RepeatDirection="Horizontal" RepeatLayout="Flow" OnEditCommand="DataList1_EditCommand" OnUpdateCommand="DataList1_UpdateCommand" OnCancelCommand="DataList1_CancelCommand">
<HeaderTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<th>Product Name</th>
<th>Unit Price</th>
<th>Units in Stock</th>
<th>Units on Order</th>
<th></th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label></td>
<td><asp:Label ID="UnitPriceLabel" runat="server" Text='<%# Eval("UnitPrice") %>'></asp:Label></td>
<td><asp:Label ID="UnitsInStockLabel" runat="server" Text='<%# Eval("UnitsInStock") %>'></asp:Label></td>
<td><asp:Label ID="UnitsOnOrderLabel" runat="server" Text='<%# Eval("UnitsOnOrder") %>'></asp:Label></td>
<td><asp:LinkButton ID="LinkButtonEdit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton></td>
</tr>
</ItemTemplate>
<EditItemTemplate>
<tr>
<td><asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label></td>
<td><asp:TextBox ID="UnitPriceTextBox" runat="server" Text='<%# Eval("UnitPrice") %>'></asp:TextBox></td>
<td><asp:TextBox ID="UnitsInStockTextBox" runat="server" Text='<%# Eval("UnitsInStock") %>'></asp:TextBox></td>
<td><asp:TextBox ID="UnitsOnOrderTextBox" runat="server" Text='<%# Eval("UnitsOnOrder") %>'></asp:TextBox></td>
<td><asp:LinkButton ID="LinkButtonUpdate" runat="server" CommandName="Update" Text="Update"></asp:LinkButton> <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Cancel" Text="Cancel"></asp:LinkButton></td>
</tr>
</EditItemTemplate>
</asp:DataList>
</form>
</body>
</html>
View 1 Replies
View Related
Jan 5, 2008
I want to select columns from different tables into a single table...Not sure if a temp table is suited for this and if so how I should implement it (NOTE: this query will be executed many times by different users at the same time, so I'd rather avoid temp tables!)I have:TABLE1idfirstnamedescriptioncreatedateTABLE2idcarnamespecificationsimportdateNow, I want a resultset that has the columns (columns from other tables from which the values should be retreived are behind the desired columns):id (TABLE1.id, TABLE2.id)title (TABLE1.firstname , TABLE2.carname)description (TABLE1.description , TABLE2.sepcifications)date (TABLE1.createdate , TABLE2.importdate)Thanks!
View 1 Replies
View Related
Feb 10, 2002
This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
PLEASE HELP ME!!!
Pending deadline doom :(
Thanks,
kaskenasy@publishingconcepts.com
View 1 Replies
View Related
Jun 4, 2014
I have 3 tables:
News:
bigint NewId
nvarchar NewTitle
datetime NewDate
nvarchar NewBrief
--------------------------
Category:
int CatId
nvarchar CatName
--------------------------
NewsRelCategory:
bigint Id
int CategoryIdFk
bigint NewsIdFk
--------------------------
I want to select NewId, NewDate and Distinct NewTitle
I tried this but NewTitle doesn't distinct:
SELECT
FROM dbo.Category INNER JOIN
NewsRelCategory ON dbo.Category.CatId = NewsRelCategory.NrcCategoryIdFk INNER JOIN
dbo.News ON NewsRelCategory.NrcNewsIdFk = dbo.News.NewId
View 9 Replies
View Related
Sep 6, 2006
Hi
I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.
I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?
Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!
View 7 Replies
View Related
Dec 15, 2006
I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
Thanks a bunch.
View 1 Replies
View Related
Mar 17, 2008
hello guys,
I have 10 tables, table1, table2, table3, table4.......table10. all these tables have different structure.
From each of these tables I want extract data and dump into flat file csv.
So i have OLEDB source and FlatFile Destination.
If i write seperate data flow task for each of the tables, then there is no issue.
But i want to use a single data flow task for all these tables. So for this, i use a variable @SQLStr . And i dynamically set the value of this variable to select * from table1, select * from table2.........selct * from table10.
So in the OLEDB source I select Data Access Mode as : SQL Command from variable. And I use @SQLStr for this varible.
For Destination, i dynamically generate the flat file for each of the table.
But this doesnt work, i get validation errors.
So, first can i use a single data flow task to dynamically change the source(different source tables) and destination. If so, what i am missing in the above process?
any help appriciated.
Thanks
View 8 Replies
View Related
Oct 6, 2006
I am pulling info out of MSDB to report on job schedules. As we have a mixture of 2005 and 2000 servers, I am varying my select statement according to the result of
[Code]
Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)
[/Code]
I have to vary the tables for each condition. Not a problem,
[Code]
if @Version = 9
begin
Select whatever from table1 join table2
end
Else
begin
Select whatever from table1 join table3
end
Problem is that SQL 2005 is trying to verify the existence of the fields in the tables on compile (it knows that the table, for example, sysjobschedules exists in 2005, but the field "Name" doesn't - although it does in 2000) , even after the if statement checking if the version is 8, and SQL 2000 uses 2 tables whilst 2005 needs 3, and the fields I need are on one table in 2000, but in a different table in 2005.
SQL 2000 is fine with it - it just does it.
Syntax checking is fine.
Just to clarify, here is my code:
[Code]
Declare @Version Char(1), @CurrentDate datetime
Set @CurrentDate = GetDate()
Create Table #TempSchedDetails
( Server VarChar(30)
,CurrentDate VarChar(19)
,JobName VarChar(80)
,ScheduleName VarChar(80)
,Freq_Type Char(1)
,Freq_Interval VarChar(2)
,JobEnabled Bit
,Freq_Subday_Type VarChar(25)
,ScheduleEnabled Bit
,StartTime VarChar(25)
,EndTime VarChar(25)
)
Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)
If @Version = '9'
Begin
Insert Into #TempSchedDetails
Select left(@@SERVERNAME,30)
,GetDate()--@CurrentDate
,J.Name
,SS.Name
,SS.Freq_Type
,SS.Freq_Interval
,J.Enabled
,SS.Freq_SubDay_Type
,SS.Enabled
,SS.Active_Start_Time
,SS.Active_End_Time
From msdb..SysJobs J
Left Outer Join msdb..SysJobSchedules JS on J.Job_Id = JS.Job_Id
Join msdb.dbo.SysSchedules SS ON JS.schedule_id = SS.Schedule_Id
Order By J.Name, Active_Start_Time
End
Else
Begin
Insert Into #TempSchedDetails
Select left(@@SERVERNAME,30)
,@CurrentDate
,J.Name
,S.Name
,S.Freq_Type
,S.Freq_Interval
,J.Enabled
,S.Freq_SubDay_Type
,S.Enabled
,S.Active_Start_Time
,S.Active_End_Time
From MSDB..SysJobs J
Left Outer Join MSDB..SysJobSchedules S On J.Job_Id = S.Job_Id
Order by J.name, Active_Start_Time
End
[/Code]
Any ideas as to how to turn the precompile checking off in 2005?
View 3 Replies
View Related
Nov 30, 2007
Hi,
I was wanting to know if it is possible to create a left join when selecting from 3 seperate tables.
Select p.Project_name, p.project_id, cp.email_display_name, te.Mon
FROM tblProject p, tblCorpPerson cp, tblTimeEntry te
WHERE p.Project_ID = te.Project_ID
AND p.Person_ID = @PersonID
AND cp.Person_ID = p.Person_ID
I need to return all rows from tblProject, and any matching project_id's from tblTimeEntry.
Any ideas or suggestions?
Thanks
View 24 Replies
View Related
Apr 13, 2007
Hello Guys,I am really not very happy with all the possibilities you have in ASP.Net other than in Classic ASP. Back then I made a loop in which you access to a Database with your SQL-Statement and fill the Recordset into HTML-Tables. Now I cannot really do that anymore (at least not in this existing project). My task is to display additional content.We are using the Timetracker-SDK and are displaying the hours spent on any projects.Now I shall add the number of bugs, suggestions and so on that are in the database for the specific project.An ObjectDatasource is accessing to a DotProcedure (written correctly?) and displaying it in a DataList.I really do not have any knowledge to modify itSo my question: Can I add a SqlDatasource and modify the "Select-" Statement in the runtime and add the Output to the Datalist?Thanks in advance on any reply here!Below the .ASPX-Part of the Site: <asp:ObjectDataSource ID="ProjectReportData" runat="server" TypeName="ASPNET.StarterKit.BusinessLogicLayer.Project"
SelectMethod="GetProjectByIds">
<SelectParameters>
<asp:SessionParameter Name="ProjectIds" SessionField="SelectedProjectIds" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:timetrackerConnectionString2 %>"
SelectCommand="select Count(*) as Anzahl from issuetracker_issues where projectid =9 and issuecategoryid=16 and issuestatusid =17">
</asp:SqlDataSource>
<table>
<tr>
<td colspan="2">
<asp:DataList ID="ProjectList" RepeatColumns="1" RepeatDirection="Vertical" runat="server"
DataSourceID="projectReportData" OnItemCreated="OnProjectListItemCreated">
<HeaderStyle CssClass="header-gray" />
<HeaderTemplate>
Project Report
</HeaderTemplate>
<ItemTemplate>
<table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%">
<tr>
<td valign="top">
</td>
</tr>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%">
<tr>
<td width="180" class="report-main-header">
Project Name</td>
<td width="70" align="right" class="report-main-header">
Est. Hours</td>
<td width="100" align="right" class="report-main-header">
Actual Hours</td>
<td width="100" align="right" class="report-main-header">
Est. Completion</td>
<td width="100" align="right" class="report-main-header">
Issues</td>
<td width="100" align="right" class="report-main-header">
Solved</td>
<td width="100" align="right" class="report-main-header">
Reported</td>
<td width="100" align="right" class="report-main-header">
Not Qualified</td>
</tr>
<tr>
<td class="report-text">
<%# Eval("Name") %>
</td>
<td class="report-text" align="right">
<%# Eval("EstimateDuration") %>
</td>
<td class="report-text" align="right">
<%# Eval("ActualDuration") %>
</td>
<td class="report-text" align="right">
<%# Eval("CompletionDate", "{0:d}") %>
</td>
</tr>
</table>
View 2 Replies
View Related
Jan 31, 2008
Hi all, I'm trig to create a simple cart in ASP.NET/C#. I've create a table named cart in which there are 2 field (user_id and id_product). In my page product I show all product and the cart icon (this is made by an asp.net image_button). When a person click on the cart there's and event in .cs which should insert the relative product in cart table. But how a can retrieve the product id?This is my .cs page: using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class provacarrello : System.Web.UI.Page{ private string connectionString = WebConfigurationManager.ConnectionStrings["MySQLconnection"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { } protected void ImageButton1_Click(object sender, ImageClickEventArgs e) { MembershipUser mu = Membership.GetUser(User.Identity.Name); Guid myGuid = (Guid)mu.ProviderUserKey; string insertSQL = "INSERT INTO cart ("; insertSQL += "userid, id_product)"; insertSQL += "VALUES("; insertSQL += "@userid, @id_product)"; SqlConnection myConnection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(insertSQL, myConnection); //Add the parameters cmd.Parameters.AddWithValue("@userid", myGuid); cmd.Parameters.AddWithValue("@id_product", DataList1.??? <--- ??? }} Thank u in advance!
View 5 Replies
View Related
Sep 19, 2000
I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up?
Sample Structure
table = Claim
Field 1 = ClaimTypeA_ID
Field 2 = ClaimTypeB_ID
Field 3 = ClaimTypeC_ID
Field 4 = ClaimTypeD_ID
Field 5 = ClaimTypeE_ID
Field 6 = ClaimTypeF_ID
The six field relate to the 6 different tables ID.
If I do this how do I store the data? put 0's in each of the claim types that are not used???
Any suggestions would be appreciated.
View 2 Replies
View Related
Jul 1, 2015
i have this query in a proc
declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,
[code]....
where @reasons and @departure_code can be multiple.
View 2 Replies
View Related
Dec 8, 2007
Hi,
First the environment: two tables A and B.
Table A: ID (unique-identifier)
Table B: ID_A (unique-identifier to A.ID, relation)
DTime (datetime)
Rows (id1 and id2 are Id examples):
A: id1
id2
B: id1 and 12:00:00 (date not important)
id1 and 13:00:00
id2 and 12:00:00
Example:
SELECT A.ID, B.DTIME
FROM A
LEFT JOIN B ON B.ID_A = A.ID
WHERE B.DTime < '14:00:00'
ORDER BY NEWID()
When I run this, I get the three rows of table B. But what I want is to get each table A row once, and get the nearest datetime of WHERE expression from the relation of table B.
So, the result must been two rows, id1 and id2, and id1 with '13:00:00' row because this is the nearest value of '14:00:00'.
How can I do this? DISTINCT trying by A.ID of SELECT, but doesn't work. Also ORDER BY B.DTime will work, but not random by NEWID() anymore.
Thank you.
View 3 Replies
View Related
Nov 29, 2007
Hey,
I have two questions:
1- In my page there should be a list of all system users, each of them may have an image or may not.
So what I need to do is how to make the next SQL query return a default ImageURL - e.g. "noAvator.jpg" - in case of null image value?
SELECT UserName , image, notesFROM Members
2- When I try to write an image url in the SQL Server 2005 Management Studio - as "images/noAvator.jpg" or any other name - it always through me this error messagebox:
The changed value is not recignized as valid..NET Framework Data Type: Byte[]Error Message: You can not use the Result pane to set this Field data to value other than NULL.
Why is that? is there a problem with the format of typing the image url?
I appreciate your help!
View 4 Replies
View Related
Dec 25, 2007
Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%> <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList> </body></asp:Content> Thanks a lot.
View 5 Replies
View Related
