[Resolved] Use Correlation Names

Jun 18, 2008

I have a working sp:

SELECT dbo.Job.CompanyJobId, dbo.Item.UnitOfMeasure, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Region.CompanyRegionID, dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production

FROM dbo.job
inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid
inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid

WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.UnitOfMeasure = 'TN'
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

GROUP BY dbo.Job.CompanyJobId, fMeasure, dbo.Region.CompanyRegionID,
dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active

Now I need to inser another join like this:

dbo.Event ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid INNER JOIN

and I get error:

Server: Msg 1013, Level 15, State 1, Line 15
Tables or functions 'dbo.Event' and 'dbo.Event' have the same exposed names. Use correlation names to distinguish them.

Not sure what this means. Thank you.

View 7 Replies


ADVERTISEMENT

Msg 1013 Select Not Working -- Correlation Names?

Apr 7, 2006

I am trying to run a select query and getting the following error:
Server: Msg 1013, Level 15, State 1, Line 12
Tables or functions 'PatientVisit' and 'PatientVisit' have the same exposed names. Use correlation names to distinguish them.

Not sure what I am doing wrong! Here is the query. Can anyone point me int the right direction?

SELECT Batch.Entry AS [Date Of Entry], PatientProfile.[Last] + ', ' + PatientProfile.[First] AS Name,
MedLists.Description AS [Adjustment Type], PatientVisit.TicketNumber, PatientVisitProcs.Code AS [Procedure Code],
TransactionDistributions.Amount AS [Adjustment Amount], PatientVisitProcs.DateOfServiceFrom,
ISNULL(CONVERT(varchar(255), Transactions.Note), ' ') AS Notes,
DoctorFacility.DotID, DoctorFacility.ListName as DrName, PatientVisit.DoctorID as DrID
FROM PaymentMethod
INNER JOIN VisitTransactions ON PaymentMethod.PaymentMethodId = VisitTransactions.PaymentMethodId
INNER JOIN Transactions ON VisitTransactions.VisitTransactionsId = Transactions.VisitTransactionsId
INNER JOIN TransactionDistributions ON Transactions.TransactionsId = TransactionDistributions.TransactionsId
INNER JOIN Batch ON PaymentMethod.BatchId = Batch.BatchId
INNER JOIN PatientVisit ON VisitTransactions.PatientVisitid = PatientVisit.PatientVisitId
Inner Join PatientVisit ON DoctorFacility.DoctorFacilityID = PatientVisit.DoctorID
INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
INNER JOIN MedLists ON Transactions.ActionTypeMId = MedLists.MedListsId
INNER JOIN PatientVisitProcs ON TransactionDistributions.PatientVisitProcsId = PatientVisitProcs.PatientVisitProcsId

Any Idea on how to fix it up? Thanks

View 1 Replies View Related

Using Correlation Names - Same Table Referenced 4X In Select

May 18, 2015

I have a "case" table describing what type of social worker case and who performed work on the case. Four case workers are referenced within each case and all workers belong to the "worker" table. I am trying to present the case worker name on a detailsView rather then the much simpler task of showing the worker's index stored within the case. I can't seem to get the correlation names working.

SelectCommand="SELECT [ReportID], [RecCreated], tblVCaseType.Description AS cType, [IntakeDate], tblVCounty.Description AS County, [Abuse], [Neglect], [Dependency], [AfterHours],
[Screenout], tblVScreenOutcome.Description AS Outcome, [Closed], [ClosedDate], iWorker.[Worker Name], cWorker.[Worker Name] ,
tblVAssessmentType.Description AS aType, tblVTimeframe.Description AS tFrame, tblVDisposition.Description AS Dispo , tblVProgramXfer.Description AS Xfer,

[Code] ...

Getting only one worker name is easy. But, I need all four. I have tried many versions without success. The error from this version is repeated 4X:

The multi-part identifier "tblVWorker.ID" could not be bound.

View 5 Replies View Related

RESOLVED RESOLVED An Attempt To Attach An Auto-named Database For File &&<file String&&> Failed &&>&&>&&> But ONLY After SSE Reinstall

Sep 16, 2007

RESOLVED RESOLVED RESOLVED

DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings

Not great, but better than continuing to bang head against wall.

Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.

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





I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.


Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)


I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:

An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).


The Connection strings are all of the form

connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"


I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.


The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.


The only thing that changed is that there was this fresh install of SQL Server Express.


I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.


So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.

Any suggestions would be appreciated.

Thanks!

EDIT----TRIED SOME THINGS:




Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...

Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.

So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.

EDIT --- TRIED SOME MORE THINGS:

I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.

I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.

Any pointers on this last piece would be helpful.

EDIT - MORE INFO

The error details

[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181

View 1 Replies View Related

Correlation Error

Mar 24, 2008

I'm have the biggest issue here that I cannot resolve. I have a select command that I'm building myself in code behind. When it gets up to the third line of code as shown below I get an error that says "Tables or functions 'Owner' and 'Owner' have the same exposed names. Use correlation names to distinguish them." I have been searching for the past few hours on this and not comming up with anything. If someone could please help me on what I'm doing wrong I would appreciate it. Thank You
Dim strProjectOwnerSelectCommand As String = "SELECT ProjectOwner.ID, ProjectOwner.CreateDateTime, ProjectOwner.ProjectID, ProjectOwner.OwnerID, Owner.Name AS OwnerDescription FROM ProjectOwner WITH(NOLOCK) INNER JOIN Owner ON ProjectOwner.OwnerID = Owner.ID WHERE (ProjectOwner.ProjectID = " & hdfProjectID.Value & ") AND (ProjectOwner.OwnerID = " & intUserID & ")"
sdsProjectOwner.SelectCommand = strProjectOwnerSelectCommand
Dim lblTasksID As Label = CType(gvTasks.Rows(hdfTaskID.Value).FindControl("lblTasksID"), Label)

View 2 Replies View Related

Correlation Matrix?

Jan 29, 2008

Hello everybody;

One of my friend showed me a correlation matrix rendered from STATISTICA. This matrix represents correlations between the questions of an exam that has 24 questions. Teacher asked them to discover the relationships between each question that both answered true or false.. Can we make a graph or something like that in AS?

Here is the screenshot extracted from Word.http://www.imageturk.com/goster.php?res=1ee9826a924e7c51a44er.jpg

View 3 Replies View Related

Update Statement Using A Subquery With Correlation

Apr 18, 2008

Hello

I am new to this forum and pretty new to running queries in SQL Server. I have been doing it for years on an iSeries platform and the following update statement would definitely work in SQL/400....but it does not in SQL Server 2000. Any help would be appreciated.

UPDATE TESTDTA.F0101 X
SET (ABAC07, ABAC12, ABAC28) =
(SELECT AIAC07, AIAC12, AIAC28 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)
WHERE EXISTS(SELECT AIAC07 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)

...and here are the errors

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'X'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.

View 2 Replies View Related

Error 1012: The Correlation Name '%' Has The Same Exposed Name As Table '%'.

Jul 20, 2005

Im trying to find the error in this statement:CREATE PROCEDURE STP_selectmainASselect a.inventoryid, b.firstname, b.lastname, art.title, art.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions,a.reference, art.provenance, sum(c.restorationcost),sum(d.framingcost), sum(e.cost)from art as a left outer join artist as b on a.artistid =b.artistid,a left outer join restoration as c on a.inventoryid =c.inventoryid,a left outer join outframing as d on a.inventoryid =d.inventoryid,a left outer join basiccosts as e on a.inventoryid =e.inventoryidgroup by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,a.provenanceorder by a.inventoryid descGOeveytime I do a syntax check on it I get this error.error 1012: the correlation name 'a' has the same exposed name astable 'a'.Whats the syntax to fix this?thanks-Jim

View 1 Replies View Related

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

Jun 11, 2014

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

View 3 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Integration Services :: Chinese Names Export To CSV - Garbled Names

Aug 11, 2015

As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).

I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.

Here is the connection manager setup

There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?

View 4 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.

View 5 Replies View Related

DB Mirroring: Different Server Names With Same Instance Names

Jan 31, 2008

I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?

For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.

Thanks!

View 4 Replies View Related

Sp Won't Work, SQL Does ????? RESOLVED

Oct 4, 2005

I am using a function to fill a listbox in asp.net If I run it using the sql statement in the code, it works. if I use the sp I get an error saying the sp expects @strBookingNo which wasn't supplied. I have stepped through the function and there is a value in txNewBookingNo.text. Here's my function - the part commented out is the part that works,but I'd rather do it through an sp. I've included my sp too

Function GetFacilitiesBookings()

Dim MySQL As String = "spRB_GetFacilitiesBookings"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strBookingNo", CInt(Me.txtNewBookingNo.Text)))
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)
Dim ddlDataSet As New DataSet

myDataAdapter.Fill(ddlDataSet, "tblRB_FacilitiesBookings")

Return ddlDataSet

'Dim strSQL As String

'strSQL = "Select * from tblRB_FacilitiesBookings where FB_BookingNo = " & CInt(Me.txtNewBookingNo.Text) & " and FB_Cancelled =0"
'Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strConn)
'Dim ddlDataset As New DataSet
'ddlDataset.Clear()

'myDataAdapter.Fill(ddlDataset, "tblRB_FacilitiesBookings")
'Return ddlDataset

End Function

CREATE Procedure [spRB_GetFacilitiesBookings]

@intBookingNo integer

as

Select * from tblRB_FacilitiesBookings where
FB_BookingNo = @strBookingNo and
FB_Cancelled = 0
GO

View 14 Replies View Related

SQL Syntax RESOLVED

Dec 13, 2005

Can anyone point me to a site which explains SQL syntax. ie I want to put brackets around a variable which holds a string of values for an IN clause.

Dim mySQLstr As String = "SELECT * from vweAvailableRooms where BD_RoomRef IN '"(" & Me.Session("RoomRefLogin") & ")"'"

View 2 Replies View Related

Using MAX(Mydate) In Sp RESOLVED

Jan 5, 2006

I have an sp which I should return the latest date in my sql table. Howver I am getting this error 'Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query. ' Here's my sp and my code which calls the sp.

CREATE PROCEDURE spRB_MaxDate (
@MaxDate datetime OUTPUT
)
AS
SELECT @MaxDate =MAX(BD_DateRequired)
FROM tblRB_BookingDates

RETURN
GO

Here's how I call it

Dim MySQL1 As String = "spRB_MaxDate"
Dim myConn1 As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim Cmd1 As New SqlCommand(MySQL1, myConn1)
Cmd1.CommandType = CommandType.StoredProcedure

'Add the parameters


Dim outParm As SqlParameter = New SqlParameter("@MaxDate", 0)

outParm.Direction = ParameterDirection.Output
Cmd1.Parameters.Add(outParm)

myConn1.Open()
Cmd1.ExecuteNonQuery()
myConn1.Close()

'
' get value of new id
Dim MaxDate As DateTime = Cmd1.Parameters("@MaxDate").Value

View 2 Replies View Related

RsAccessDenied (RESOLVED)

Jul 12, 2007

Hi,

I have installed SSRS and created a .net application... now when I try to open the report viewer, I get the error " the permission granted to user 'domainuser' are insufficient for performing this operation. (rsAccessDenied)" I have searched the net hi and low and cant seem to find an adequate answer... something to do with roles or permissions? I have assigned permissions to this user on the ssrs database, yet I still cant seem to view the report. It works fine in my test environment (as it usually does ) any help on this would be greatly appreciated as to be honest, I dont really know what im doing here.

"Impossible is Nothing"

View 8 Replies View Related

[ RESOLVED ] WHERE IN (Count Only Once)

Dec 10, 2007

I am selecting from two tables, with a one-to-many relationship.

Select t2.*
From Table1 t1
inner join Table2 on t1.id = t2.id
where t2.Field2 in (1,3)

There are times when there are 2 or more entries in table2 that relate to table1. I want to only display 1 of these entries, (either will do), I just dont want to display all.

View 3 Replies View Related

The Remote Name Could Not Be Resolved:

Apr 10, 2007



Hi we recently depoyed SSRS 2005 on two servers along with our web application.



the extranet setup

IIS Web server (app server) in DMZ linked to database server sat behind our corporate firewall. SSL installed on IIS server. SSRS app is on the IIS, with its db on the main database server behind corproate firewall.



we have a simple link going from our app to the reporting services link



however when we enter a login (incl admin) it fails with the message 'The remote name could not be resolved: "ourdomain.com"



windows authentication at the moment.



any help much appreciated.



thanks



View 3 Replies View Related

[Almost Resolved] SqlDataReader Vs. DataSets

Feb 1, 2008

Hi,
I am pretty new to harcore ASP.NET and .NET in general but I know the basics of the language and stuff like that. Basically I used to be a hardcore object pascal Delphi developer doing Windows Applications but have now moved to .NET world. The reason I posted this question here is I couldn't find any other specific place to ask so here it goes.
To get me started with good programming source and practices I downloaded the ASP.NET TimeTracker Starter Kit and after some modificaion in connection string (using SQL 2005 Developer edition not the Express one) I have managed to make it work. Things done in there are pretty interesting way by using a delegate to retrieve data and present it using databound controls.
As much simple as it sounds I am more of a fan of not using too many databound controls except in dropdown box, list box and stuff like that. So basically I need to develop DataAccessLayer (DAL) in such a way that it's useful for both Windows and Web application. So with my research I found that I should be using DataSets in the DAL because they are serializable and thatz what Web Applications & Services love over using SqlDataReader. Other advantage of using DataSet is to use ForEach syntex over While Loop in SqlDataReaders. So below is some code I extracted from the TimeTracker Starter Kit to get me started.
I also noticed that the return result is a list array of certain object, in this case Category. So does it mean that it can be used in frontend using ForEach syntex? How can I convert the code below to use DataSets over SqlDataReader?  Private Delegate Sub TGenerateListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef tempList As List(Of T))

Public Overrides Function GetAllCategories() As List(Of Category)
Dim sqlCmd As SqlCommand = New SqlCommand()
SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES)

Dim categoryList As New List(Of Category)()
TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)

Return categoryList
End Function

Public Overrides Function GetCategoryByCategoryId(ByVal Id As Integer) As Category
If Id <= DefaultValues.GetCategoryIdMinValue() Then
Throw New ArgumentOutOfRangeException("Id")
End If

Dim sqlCmd As SqlCommand = New SqlCommand()
AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id)
SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID)

Dim categoryList As New List(Of Category)()
TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)

If categoryList.Count > 0 Then
Return categoryList(0)
Else
Return Nothing
End If
End Function

Private Sub TGenerateCategoryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef categoryList As List(Of Category))
Do While returnData.Read()

Dim actualDuration As Decimal = 0
If Not returnData("CategoryActualDuration") Is DBNull.Value Then
actualDuration = Convert.ToDecimal(returnData("CategoryActualDuration"))
End If

Dim category As Category = New Category(CStr(returnData("CategoryAbbreviation")), actualDuration, _
CInt(returnData("CategoryId")), CDec(returnData("CategoryEstimateDuration")), CStr(returnData("CategoryName")), _
CInt(returnData("ProjectId")))
categoryList.Add(category)
Loop
End Sub
Hope this makes sense. Sorry if I have posted this in a wrong section..!
Cheers,Nirav

View 6 Replies View Related

[RESOLVED] Trying To Filter Some Data On SQL

Apr 14, 2008

Here is a simple grid view page I made.

http://visualboxscore.com/boxscores/cfb_box_scores.aspx

Ultimately I want to be able to filter every field that is listed. However, for now I am just learning this stuff and would like to just filter the by the name in the "offense" field.

However, it is not binding the selection thus not filtering it out. Do you see any problems in my code?

<%@ Page Language="VB" %>
<html>
<head id="Head1" runat="server">
<title>GridView DetailsView Master-Details (2 Page)</title>
</head>
<body>
<form id="form1" runat="server">
<b>Choose a team:</b>
<asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource2" AutoPostBack="true"
DataTextField="offense" Runat="server" />
<asp:SqlDataSource ID="SqlDataSource2" Runat="server" SelectCommand="SELECT DISTINCT [offense] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" />
<br />
<br />
<asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="false" Runat="server"
DataSourceID="SqlDataSource1" DataKeyNames="offense"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField HeaderText="Date" DataField="date" DataFormatString="{0:MM/dd/yy}" ItemStyle-HorizontalAlign="Center" SortExpression="date" />
<asp:BoundField HeaderText="Offense" DataField="offense" ItemStyle-HorizontalAlign="Center" SortExpression="offense" />
<asp:BoundField HeaderText="Defense" DataField="defense" ItemStyle-HorizontalAlign="Center" SortExpression="defense" />
<asp:BoundField HeaderText="Rush Yards" DataField="rush_net" ItemStyle-HorizontalAlign="Center" SortExpression="rush_net" />
<asp:BoundField HeaderText="YPC" DataField="yards_per_carry" DataFormatString="{0:#.##}" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_carry" />
<asp:BoundField HeaderText="Pass Yards" DataField="pass_yards" ItemStyle-HorizontalAlign="Center" SortExpression="pass_yards" />
<asp:BoundField HeaderText="YPA" DataField="yards_per_att" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_att" />
<asp:BoundField HeaderText="Total Yards" DataField="total_yards" ItemStyle-HorizontalAlign="Center" SortExpression="total_yards" />
<asp:BoundField HeaderText="YPP" DataField="yards_per_play" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_play" />
<asp:BoundField HeaderText="Points" DataField="points" ItemStyle-HorizontalAlign="Center" SortExpression="points" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT [date], [offense], [defense], [rush_net], [yards_per_carry], [pass_yards], [yards_per_att], [total_yards], [yards_per_play], [points] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>">
<SelectParameters>
<asp:ControlParameter Name="Offense" ControlID="DropDownList1" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

overall I want to format the table look and and background but for now I just want to see how this stuff functions.

Thanks!!

View 3 Replies View Related

[Resolved] What Are Instance Parameters

Jun 19, 2008

I am reading over a job description and I am unsure of that the employer is refering to when they say "Instance Parameters".

Here is the context that it is used in.
* Tune and optimize SQL statements, Instance Parameters, and Data Storage including the identification of performance related issues.

View 3 Replies View Related

Default Contraint *** RESOLVED***

Aug 16, 2005

You can't have but one default contraint in a DB named
df_revisiondate Default getdate()

I thought that sense they were being created on different Tables that it would not matter. It was a way to maintain a type of consistency by giving them the same name. However, it SEEMS to have caused all my tables to crash.

thanks!

View 5 Replies View Related

[resolved]-joining 3 Tables

Oct 14, 2005

Hi all.. i have a problem.

Let say i have 3 tables. Let's name it Main, table2, table3. in table Main, there are 4 colums(M_id,M_name M_tbl2, M_tbl3). In table2 and table3, each has 3 colums. For table2 the colums are id, tbl2Name, tbl2Desc and for table3(id, tbl3Name, tbl3Desc).

M_tbl2 will refer to id in table2 and M_tbl3 will refer to id in table3. How am i going to get M_id, M_name, tbl2Name, tbl3Name in one query?

View 4 Replies View Related

Add Column To A View - RESOLVED

May 3, 2006

I am creating a view in SQL using the View tab in the database and adding the table and the fields I need. For criteria I need to have 28 'or' s . How do I insert more columns on the end of my view ?

View 2 Replies View Related

Reset Key Field To 1 RESOLVED

Nov 29, 2006

I have test tables with key fields that increment by one each time a record is added. If I delete all the records in a table (using a delete query) is there a way I can make the key start at 1 again.

The reason I ask is that by using test data in these tables (and there are a lot of records),when I clear them out and append the live data the key field starts at something like 764525

View 1 Replies View Related

Edit Scheduled Job RESOLVED

Jun 6, 2007

Sorry if this sounds dumb but I can't find the answer . I have created a scheduled job on my test database using the wizard. How do I edit it or delete it ? Step by step instructions please

I have never done this before so am just experimenting at the moment.

View 1 Replies View Related

[RESOLVED] COALESCE Script

Aug 13, 2007

I have a table NUMBERS in which I have Column1 and Column2. Column1 will always be a unique number, yet Column2 may have duplicates.

What I would like to do is return all the data in Columns 1 and 2, yet have Column1 return as a comma seperated value.

Column1 Column2
1 15
2 16
3 16
4 17

Return:
Column1 Column2
1 15
2,3 16
4 17

Any ideas will be great!

Thanks

View 14 Replies View Related

[RESOLVED] SQL String Array - How To

Jan 25, 2008

Hello All,

I have about 10 different insert into statements which I would like to include into a loop of some sort. Here is what I am trying to accomplish.

SET @aryTableNames = 'thisTable','thatTable','anotherTable'

DO
SELECT * INTO [Server].[dbo].[@aryTableNames]
FROM OPENQUERY(mysqlDB, 'SELECT * FROM `@aryTableNames`')
LOOP

View 5 Replies View Related

[Resolved] Using Substring In The Where Clause

Jan 25, 2008

I have a stored procedure where I would like to use substring in the where clause, if possible.


CREATE PROCEDURE GetJobList
@PlantId char(3)

Select ......
From ......
WHERE (dbo.Source.CompanySourceId = 'PROD' and dbo.Batch.ReportDate >= @DateFrom and dbo.Batch.ReportDate <= @DateTo and (if @PlantId <> ' ' then SUBSTRING(dbo.Job.CompanyJobId, 1,3) = @PlantId))


I get syntax error "Incorrect syntax near 'if, incorrect sysntax near 'then'....

View 4 Replies View Related

[Resolved] SQL Select Problem

Feb 1, 2008

I have a sp that selects from a table. The table has a MA record (received qty) type and a PR record (used qty) type.

Below is code that produces an extract and gives 2 records, one for MA (received qty) and one for PR (used qty).

SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, dbo.SourceType.CompanySourceTypeId,
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity)
ELSE SUM(dbo.ProductionEvent.AlternateQuantity)
END
AS
tot_qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE dbo.SourceType.CompanySourceTypeId = 'PR'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'

or dbo.SourceType.CompanySourceTypeId = 'MA'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'

GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId

Tot Qty
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA372.25
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR357.25


Now I want to extract the same data into a temp table (that I have created) and when reading the same records I would like to update either a received qty field or a used qty field depending upon record type MA or PR). The query works but both qty fields are empty. Can anyone see what I am doing wrong?


SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, dbo.SourceType.CompanySourceTypeId,
qty_received =
CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END,
qty_used =
CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END,
qty_wasted =
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity)
ELSE 0
END
-
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity)
ELSE 0
END
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE dbo.SourceType.CompanySourceTypeId = 'PR'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'


or dbo.SourceType.CompanySourceTypeId = 'MA'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'


GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId

QTY QTY QTY
REC USED WASTE
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA.00 .00 .00
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR.00 .00 .00

View 9 Replies View Related

What Looks Like A Basic SQL Query Still Not Resolved

Nov 23, 2005

ARRRRRRRGGGGGHHHHH!!Please can you help, I'm going round the bend with this.I have a simple and small table called STOCKCATS, which I need to query toget back a dataset in a particular order, but although it looks simple Ican't get it to work. My table schema plus sample data to see the problemis as follows:DROP TABLE IF EXISTS `STOCKCATS`;CREATE TABLE `STOCKCATS` (`CATID` varchar(30) NOT NULL default '',`LEVEL` varchar(30) default NULL,PRIMARY KEY (`CATID`),KEY `indxCATEGORYID` (`CATID`));INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('A001',''),('A002','A001'),('A003','A001'),('A004','A001'),('A005','A001'),('PCHW01',''),('MHW01',''),('FD01',''),('ELEC01',''),('MHW02','MHW01');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('MHW03','MHW01'),('MHW04','MHW01'),('MHW05','MHW01'),('PCHW02','PCHW01'),('PCHW03','PCHW01'),('PCHW04','PCHW01'),('PCHW05','PCHW01'),('PCSW01',''),('MSW01',''),('C001',''),('C002','C001'),('C003','C001'),('MV',''),('SUZ','MV'),('ALF','MV'),('PLASMA','ELEC01'),('T01','ELEC01'),('HEATING',''),('RAD','HEATING'),('P01',''),('B01','P01'),('BB','HEATING'),('FS','HEATING'),('WM','HEATING'),('AEROSOL',''),('SOLVENTS','AEROSOL'),('DGC','');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('DGXWINDOWS','DGC'),('DGXEXTRA','DGC'),('DGXCON','DGC');As you can see from the table structure, this table consists of 2 fieldvalues. The 1st is the category code and the 2nd is the level is at. If acatid has a level of nothing, eg '', then it means that it is a root levelcategory. If a catid has a another cat's catid in it's level, eg B01 hasP01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.All I want to do is query this table and bring back the data so thatalphabetically it goes root level cat A1, then all the sub-cats for thisroot level, then root level A2, then all sub-cats for this root level and soon. An example using the above would be as follows:^ ^ A to G of root level cats plus their sub-cats....HEATING << root levelBB << sub-cat of heatingFS << sub-cat of heatingWM << sub-cat of heating/ / I to Z of root level cats plus their sub-cats....A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),CATID and I thought this had done it, but I was looking at the ('A001',''),('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')entries as these naturally fell into place. If you use this order commandon the above you will see that ('P01','') and it's associated ('B01','P01')sub-cat just don't come together.Does any body have any ideas?ThanksLaphan

View 6 Replies View Related

Table Names And Field Names

Jan 21, 2004

I'm trying to do an update query that looks like this:

UPDATE

PAEMPLOYEE

SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL


FROM

PAEMPLOYEE A

JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE

It's erroring out on the Employee prefix B.EMPLOYEE saying:

..."does not match with a table name or alias name used in the query"


Is it wrong or will it cause problems to have a field name the same as the table name?

View 5 Replies View Related







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