SUM In 2005 Wrong
Apr 20, 2006
I have same database on 2000 and on 2005
I have 10 rows each with value of 100 for BudgetAmount and BudgetType = 'T'
When running
SELECT M.Market_code, M.Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN B.BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN B.BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN B.BudgetAmount ELSE 0 END )
from Budget B, Market M
Where B.BudgetYear=2006
and B.Customer_Market_code = M.Market_code
GROUP BY M.Market_code, M.Description
ORDER BY M.Description
SQL2000 shows Tooling as 1200 (correct) but SQL2005 shows 2400
Any Idea what the difference is?
View 1 Replies
ADVERTISEMENT
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies
View Related
Mar 23, 2007
I am an old hand at RDBMS but have been using SQL Server for only 1 year. I have a local install of 2005 Developer Edition, and also access a number of 2000 and 2005 instances on remote and network servers. OK, so on 3/16 (after installing the 3/15 Windows Security Updates), I began getting this error when invoking any 2005 DB under Object Explorer in Management Studio "SQLWB - SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience." Very helpful. So I ran a debug and got some unhandled exception errors:
'SqlWb.exe': Loaded 'C:WINDOWSsystem32wbemwbemsvc.dll', No symbols loaded.
'SqlWb.exe': Loaded 'C:WINDOWSsystem32wbemfastprox.dll', No symbols loaded.
'SqlWb.exe': Loaded 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727diasymreader.dll', No symbols loaded.
'SqlWb.exe': Loaded 'C:WINDOWSsystem32apphelp.dll', No symbols loaded.
The thread 'Win32 Thread' (0x21c) has exited with code 0 (0x0).
The thread 'Win32 Thread' (0xa98) has exited with code 0 (0x0).
Unhandled exception at 0x79ea69f3 in SqlWb.exe: 0xC0000006: In page error.
First-chance exception at 0x79ea69f3 in SqlWb.exe: 0xC0000005: Access violation reading location 0x088b200c.
Unhandled exception at 0x79ea69f3 in SqlWb.exe: 0xC0000005: Access violation reading location 0x088b200c.
First-chance exception at 0x79ea69f3 in SqlWb.exe: 0xC0000005: Access violation reading location 0x088b200c.
After some poking around, my DBA told me that we had upgraded the servers for our team to SP1, and I should install that, because the file being read did not 'match up' with the commands. But when I did so I got the warning:
- Edition Change Check (Warning)
Messages
Edition Change Check
To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
I researched this information, but it didn't really apply to my situation. So I selected 'CONTINUE' and everything went downhill from there. The install downloaded the SP1 setup support files (which by the way "cannot be removed") and then aborted. I poked around some more and decided it would be easiest to Uninstall and Reinstall the Dev Edition from the DVD and then apply the SP. The uninstall went fine until it got to the tools (which apparently were my real problem to start with). When I attempt to uninstall the tools, I get the following message:
"Error reading from file C:WindowsassemblyGAC_MSILMicrosoft.SqlServer.SqlEnum9.0.242.0__89845dcd8080cc91Microsoft.SqlServer.SqlEnum.dll
Verify that this file exists and that you can access it"
This message is presented with Retry and Cancel options. The file does NOT exist, so Retry is not useful and cancel rolls back the entire uninstall.
Error signature:
EventType : sql90setup P1 : installfinalize P2 : 0x643 P3 : unknown
P4 : 0x519 P5 : unknown P6 : unknown
P7 : sqlrun_tools.msi@9.00.1399.06
In this part SP1/half uninstalled 2005 environment I am stuck like chuck. I have searched for this dll online and on other folders of my hard drive to no avail. I have also dug through these forums until my eyes are crossed.
Can anyone help?!?
View 3 Replies
View Related
Jan 7, 2007
Hi,
I cant get SQL Server Management Studio installed. The install completes but Management Studio is unavailable in my program files list. I've uninstalled and reinstalled and even been through "How to uninstall an instance of SQL Server 2005 manually" http://support.microsoft.com/kb/909967 . But no matter how many times I run ARPWrapper.exe 5 items are still present:
<img scr="http://EventAnalyser.Appointmentsbook.com/SQLWontUninstall.jpg">
I'm tempted to use MSIZAP but it doesn't apply to sqlserver. How can I get rid of SQL Server and start again without rebuilding my pc?
Cheers,
Jeremy
View 4 Replies
View Related
Aug 10, 2006
Hi,
I just downloaded the trial version of MS SQL 2005 from Micriosoft website.
However, when I run the setup.exe. it is to install Microsoft Office 2003.
Can anyone help me?
Thanks.
View 3 Replies
View Related
Jan 15, 2007
Hi
I am having a query
SELECT Dur1.rootId
FROM DurableEventTab Dur1
WHERE (Dur1.dev_ReferenceClusterRoot = 'iyrwd.52' )
AND Dur1.dev_Action = 'Order:Ordered')
AND (Dur1.dev_Active = 1) AND (Dur1.dev_PurgeState = 0)
AND (Dur1.dev_PartitionNumber = 0)
This table has a primary key : aribapk11
and the indexes on the dev_ReferenceClusterRoot,
dev_Action,dev_purgestate .
Now when I fire this query
the query execution plan is actaull doing a Clustered Index scan on the PK :aribaPK11 . What I was expecting was an index seek on the key defined on dev_referenceClusterRoot. Please not the index seek is the behaviour in sql server 2000.
Any idea what is going wrong ?
Clustered Index Scan(OBJECT:([typhoon1902].[dbo].[DurableEventTab].[AribaPK7] AS [Dur1]), WHERE:([typhoon1902].[dbo].[DurableEventTab].[dev_Active] as [Dur1].[dev_Active]=(1.) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PurgeState] as [Dur1].[dev_PurgeState]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PartitionNumber] as [Dur1].[dev_PartitionNumber]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_ReferenceClusterRoot] as [Dur1].[dev_ReferenceClusterRoot]='iyrwd.52' AND [typhoon1902].[dbo].[DurableEventTab].[dev_Action] as [Dur1].[dev_Action]=N'Order:Ordered')) 0 0 Clustered Index Scan Clustered Index Scan OBJECT:([typhoon1902].[dbo].[DurableEventTab].[AribaPK7] AS [Dur1]), WHERE:([typhoon1902].[dbo].[DurableEventTab].[dev_Active] as [Dur1].[dev_Active]=(1.) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PurgeState] as [Dur1].[dev_PurgeState]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PartitionNumber] as [Dur1].[dev_PartitionNumber]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_ReferenceClusterRoot] as [Dur1].[dev_ReferenceClusterRoot]='iyrwd.52' AND [typhoon1902].[dbo].[DurableEventTab].[dev_Action] as [Dur1].[dev_Action]=N'Order:Ordered') [Dur1].[rootId] 1 0.00386574 0.0002263 71 0.00409204 [Dur1].[rootId] PLAN_ROW 0 1
View 3 Replies
View Related
Jun 15, 2006
Hi
I installed RS 2005, SP1 and then the rollup fix 2153 however; if I run http://server/reportserver or view the reportserver log file, it shows version 2047 which is the SP1 version. I have added the tail end of the rollup hotfix log file which shows I have installed it.
Is this a known issue?
06/14/2006 16:03:57.153 Failed to read version information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesReportServerCatalog.sql
06/14/2006 16:03:57.169 Failed to read version information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesReportServerStylesHtmlViewer.css
06/14/2006 16:03:58.216 Attempting to install file: sqlrun_rs.msp
06/14/2006 16:03:58.263 Attempting to install file: \NLDN9577DWWd$89a527f93aa2bb044103hotfixrsFilessqlrun_rs.msp
06/14/2006 16:03:58.278 Creating MSP install log file at: C:WINDOWSHotfixRS9LogsRS9_Hotfix_KB918222_sqlrun_rs.msp.log
06/14/2006 16:03:58.294 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
06/14/2006 16:03:58.294 Failed to read registry key: Debug
06/14/2006 16:04:44.826 MSP returned 0: The action completed successfully.
06/14/2006 16:04:45.092 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
06/14/2006 16:04:45.107 Failed to read registry key: Debug
06/14/2006 16:04:45.123 Successfully installed file: \NLDN9577DWWd$89a527f93aa2bb044103hotfixrsFilessqlrun_rs.msp
06/14/2006 16:04:45.139 Restarting RS Service ReportServer since it was previously running
06/14/2006 16:04:45.154 Attempting to start service: ReportServer
06/14/2006 16:04:51.936 Successfully started service: ReportServer
06/14/2006 16:04:51.983 Successfully installed target: NLDN9577DWW
06/14/2006 16:04:51.998 Successfully installed instance: MSSQLSERVER
06/14/2006 16:04:52.014 Successfully opened registry key: SoftwareMicrosoftWindowsCurrentVersionUninstall
06/14/2006 16:04:52.608
06/14/2006 16:04:52.623 Product Status Summary:
06/14/2006 16:04:52.639 Product: SQL Server Reporting Services 2005
06/14/2006 16:04:52.701 MSSQLSERVER - Success
06/14/2006 16:04:52.717
06/14/2006 16:06:05.500 Hotfix package closed
Cheers
Steve
View 1 Replies
View Related
Mar 16, 2007
Hi am very new with VB.net (Visual Studio 2005) and the first time try to use the sql server 2005 express. I did create a trial.mdf (local server) and complete procedure to attach the trial1.mdf in Microsoft SQL Management Studio Express.
I try to use VB to connect /open/close it with the following codes: (but failed)
-----------------------------------------------
Imports System.Data.SqlClient
Public Class Form1
'Link database
Dim DB_Location = "c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTrial1.mdf"
Dim MyConnection = "Server=localhost; DataBase= " & DB_Location & "Integrated Security=SSPI"
Dim sqlConnection As New SqlConnection(MyConnection)
'Use database
Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' "
Dim cmd As New SqlCommand(strSQL, MyConnection)
sqlConnection.Open() ' ----> error
cmd.ExecuteNonQuery() ' ----> error
sqlConnection.Close() ' ----> error
End Class
-------------------------------------------
At the last 3 line of codes I got the errors ... can someone explain why & help me to correct it?
Thanks for any help
View 10 Replies
View Related
Apr 2, 2007
Visual Studio 2005 shows SQL Server 2005 installed product versions differently than the actual. As seen below VS2005 displays 9.00.3042.00 as the version of SQL Server 2005 however it is 9.00.3050.00 in SQL Server Management Studio. [Look at Installed Products section after selecting About MS Visual Studio from Help menu ]
SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00
I have VS2005 SP1 and SQL2005 SP2 installed. Why VS2005 reports versions wrong?
View 1 Replies
View Related
May 1, 2008
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image,
2 (SELECT Location
3 FROM Location_Table
4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID,
5 (SELECT TypeOfProperty
6 FROM Type_Table
7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8 FROM Property_Table
9 WHERE (TypeID = @TypeID OR
10 TypeID IS NULL) AND (LocationID = @LocationID OR
11 LocationID IS NULL) AND (Price >= @MinPrice OR
12 Price IS NULL) AND (PriceMax <= @MaxPrice OR
13 PriceMax IS NULL)
View 7 Replies
View Related
May 14, 2004
This is working:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
"MONTH(Some_Date) >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
This is NOT:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
Month >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
it says - Invalid column name 'Month'
Why ? Why ? Why ?
View 3 Replies
View Related
Oct 5, 2004
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting
Alter Table Employees
Add Constraint CK_SNN
Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Any help would be nice. Thanks in advance.
View 2 Replies
View Related
Jan 3, 2005
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _
"S (@UserID, @UserName, @UserPassword, @Name, @Email)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userID.ParameterName = "@UserID"
dbParam_userID.Value = userID
dbParam_userID.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_userID)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)
Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_name.ParameterName = "@Name"
dbParam_name.Value = name
dbParam_name.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_name)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Message.Text = "Register Successed, click on the link WebCam for login"
Else
Message.Text = "Failure"
End If
End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs)
Line 53:
Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Line 55: Message.Text = "Register Successed, click on the link WebCam for login"
Line 56:
Source File: c:inetpubwwwrootweb_sitemusic
egister.aspx Line: 54
In the DB the fields are added as :
UserID as int
UserName as varchar
UserPassword as varchar
Name as varchar
Email as varchar
and I have TRYED to change from "varchar" on to "text" but I receive same error message.
PLEASE HELP !!!!!!!! this is not first time I get the same errors on the all reg pages :( WHY ? WHAT LINE I HAVE TO EDIT ?
Thank You !!!
Regards
View 2 Replies
View Related
Mar 18, 2005
Hi please lok at this SP I have written and point where am I commiting mistake.
The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails]
(
@ProductCode Int = null
)
AS
DECLARE @sqlString AS nvarchar(2000)
SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode,
Master.PROD_DESC_TEXT AS ProductName,
Detail.PiecesPerBox, Detail.Price
FROM cp_nafta_dws.PRODUCT AS Master
INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail
ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM'
BEGIN
IF NOT (@ProductCode = NULL)
BEGIN
SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode
END
END
EXEC @sqlString
GO
Thanks
View 11 Replies
View Related
Jul 7, 2005
Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does it not bring anything when Where is there?
Thanks,
View 3 Replies
View Related
Oct 3, 2005
This code is not updating the database, please help me
SqlCommand UpdCmd; SqlCommand SelCmd; SqlDataAdapter da; DataSet ds = new DataSet();
da = new SqlDataAdapter(); if (!(Conn.State == ConnectionState.Open)) { Conn.Open(); }
SelCmd = null; SelCmd = new SqlCommand("sp_SelectUserInfo",Conn); SelCmd.CommandType = CommandType.StoredProcedure;
oSelCmd.Parameters.Add("@UserID",userid);
da.SelectCommand = oSelCmd;
da.Fill(ds,"UserTab"); oUpdCmd = null; UpdCmd = new SqlCommand("sp_UpdateUserInfo",Conn); UpdCmd.CommandType = CommandType.StoredProcedure;
UpdCmd.Parameters.Add("@UserID",userid); UpdCmd.Parameters.Add("@FirstName",firstName); UpdCmd.Parameters.Add("@LastName",lastName); UpdCmd.Parameters.Add("@Region",region); da.UpdateCommand = UpdCmd; da.Update(ds,"UserTab");
Conn.Close();
View 1 Replies
View Related
Mar 16, 2006
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.Source Error:
View 3 Replies
View Related
Mar 29, 2006
I have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables
Event EventCategoryTypeField Type Example Field Type ExampleEventID Int(4) 1 CategoryID(PK)int(4) 1CategoryID(FK)int(4) 1 Type varchar(50) ExerciseType varchar (200) Exercise Color varchar(50) Brown
The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color).
my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across.
Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private void Load_Events(){///<summary>///Loads the events added from the NewEvent from into a dataset///and then just as with the Holidyas, the events are wriiten to///the appropriate calendar cell by comparing the date. only the ///title and time will be displayed in the cell. other event details///such as, Objective, owner will be shown in a dialog box syle when ///the user hovers over the event.///</summary>
mycn = new SqlConnection(strConn);myda = new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);myda.Fill(ds2, "Events");}
private void Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e){///<summary>///This procedure will read all the data from the dataset - Events and then///write each event to the appropriate calendar cell by comparing the date of ///the EventStartDate. if an event is found, the title and time are written///to the cell, other details are shown by hovering over the cell to bring///up another function that will display the data in a dialogBox. once the///event is written, the appropriate color is applied to the text.///</summary>if (!e.Day.IsOtherMonth){foreach (DataRow dr in ds2.Tables[0].Rows){if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString())){DateTime evStDate = (DateTime)dr["EventStartDate"];string evTitle = (string)dr["Title"];string evStTime = (string)dr["EventStartTime"];string evEnTime = (string)dr["EventEndTime"];string evColor = (string)dr["CategoryColor"];
if(evStDate.Equals(e.Day.Date)){e.Cell.Controls.Add(new LiteralControl("<br>"));e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));}}}}else{e.Cell.Text = "";}}
View 2 Replies
View Related
Mar 29, 2006
Hi,
Just a quickie... Can anyone see anything wrong with this SQL. I'm using Microsoft SQL Server 2000.
Code:
SELECT * FROM PFP_UserProfiles, Users, PFP_UserSkills, PFP_UserIndustries WHERE PFP_UserSkills.SkillID IN ( '222', '221', '182') AND PFP_UserProfiles.IsPublic = 1;
Cheers
Chris
View 2 Replies
View Related
Mar 11, 2008
Hi everyone.
I'm working on an assignment and I am about to give up. I can't figure out what I'm doing wrong. It seems like I have everything worked out, but when I run the SQL query i've come up with, I get errors regarding INVALID tables.
As far as I can tell, all my tables are valid. Can anyone give me any pointers on what i'm doing wrong **read: I'm not asking for my assignment to be done for me, just asking for help because I am so close to getting the right answer....i think**
Here is what was provided:
http://i47.photobucket.com/albums/f152/hmarandi/problem.gif
Here is the Query I have come up with which gives me errors!
Code:
-- START --
CREATE TABLE dept
(deptnameCHAR(15),
empid CHAR(8),
CONSTRAINT PKdeptname PRIMARY KEY (deptname));
ALTER TABLE dept
ADD CONSTRAINT FKempid FOREIGN KEY (empid) REFERENCES Employee(empid);
-- START --
CREATE TABLE Employee
( empid CHAR(8),
deptname CHAR(15),
empfname VARCHAR(10) NOT NULL,
emplname VARCHAR(10) NOT NULL,
empphone CHAR(15),
empemail VARCHAR(20) NOT NULL,
bossid CHAR(8),
empsalary DECIMAL(9,2) CONSTRAINT CHKEmpsalay Check (empsalary > 5),
CONSTRAINT PKempid PRIMARY KEY (empid),
CONSTRAINT uniqueEmail UNIQUE(empemail) );
-- Add constraint
ALTER TABLE Employee
ADD CONSTRAINT FKdeptname FOREIGN KEY (deptname) REFERENCES dept(deptname);
-- START --
CREATE TABLE POrder
(OrderID CHAR(8),
OrderDatedatetime,
CustIDCHAR(8),
EmpIDCHAR(8)
CONSTRAINT PKOrderIDPRIMARY KEY (OrderID));
-- Add constraint
ALTER TABLE POrder
ADD CONSTRAINT FKCustID FOREIGN KEY (CustID) REFERENCES Customer (CustID);
ALTER TABLE POrder
ADD CONSTRAINT FKEmpID FOREIGN KEY (EmpID) REFERENCES Employee (EmpID);
-- START --
CREATE TABLE Customer
(CustID CHAR(8),
CustNameVARCHAR(15),
BalanceCHAR(8)
CONSTRAINT PKCustIDPRIMARY KEY (CustID));
-- START --
CREATE TABLE OrderItem
(OrderIDCHAR(8),
ProdIDCHAR(8),
QtyCHAR(8)
CONSTRAINT PKOrderIDPRIMARY KEY (OrderID));
ALTER TABLE OrderItem
ADD CONSTRAINT PKProdIDPRIMARY KEY (ProdID)
ALTER TABLE OrderItem
ADD CONSTRAINT FKOrderID FOREIGN KEY (OrderID) REFERENCES POrder (OrderID);
ALTER TABLE OrderItem
ADD CONSTRAINT FKProdID FOREIGN KEY (ProdID) REFERENCES Product(ProdID);
-- START --
CREATE TABLE Product
(ProdIDCHAR(8),
ProdNameVARCHAR(15),
MakerVARCHAR(15),
StockSizeCHAR (6),
PriceCHAR (10)
CONSTRAINT PKProdIDPRIMARY KEY (ProdID));
Any HELP would be greatly appreciated.
Thanks!
View 14 Replies
View Related
Sep 10, 2004
it gives an error saying
"incorrect syntax near update" . can anyone tell me why?
EXEC("update "+@sTable+"
set status='A'
where breakdate < datediff(day,'08/12/1960',getdate())
and clientid=12059
and status ='F'")
View 2 Replies
View Related
Jul 19, 2004
I am trying to created a view and have a need for conditional logic:
Here is what I presently have (not working):
----------------------------------------------------------------------------
IF (ISDATE(COMPLETIONDATE) = 1)
BEGIN
CASE
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
ELSE
IF (ISDATE(COMPLETIONDATE) = 0)
BEGIN
CASE
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
--------------------------------------------------------------------------
Can someone tell me what I am doing wrong?
Basically I am trying to test to see if "completiondate" is a date and if it is then perform a case operation using it, if it is not a date then I want to perform the case operation using "targetcompletiondate".
Thanks...
View 4 Replies
View Related
May 4, 2008
select * from dbo.Advertisement_Search '%'
Advertisement_Search is a store procedure and i am trying this in ms sql.
Thanks in advance.
View 8 Replies
View Related
Jun 5, 2008
Hello all.
Ive written the following code to check if a foreign key exists, and if it doesnt to add it to a table. The code i have is:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]'))
BEGIN
PRINT N'Adding foreign keys to [dbo].[tblProductStock]'
ALTER TABLE [dbo].[tblProductStock] ADD
CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType]) REFERENCES [dbo].[tblLookup] ([ID])
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
END
However i keep getting the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.
Thanks for reading.
View 14 Replies
View Related
Jan 14, 2006
INSERT INTO IS_REGISTERED VALUES
(54907,2715,'I-2001')
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__IS_REGISTERED__629A9179'. Cannot insert duplicate key in object 'IS_REGISTERED'.
The statement has been terminated.
How do I fix this? I am trying to insert this into the IS_REGISTERED Table.
Student_ID Section_ID Semester
38214 2714 I-2001
54907 2714 I-2001
54907 2715 I-2001
66324 2713 I-2001
IS_REGISTERED (Student_ID, Section_ID, Semester)
albanie
View 7 Replies
View Related
Nov 5, 2007
Hi everybody,
I posted this issue a couple of days ago, but got no solution yet. Anyhow, here is the thing:
"A limited user account is able to see items on the report manager with no permission?"
I have created a local user account on the domain machine where the reports are deployed. This user is not a member of administrator group. It is just a user under the User Group. This user doesn't even have a permission on the Report Manager, not even a browser role. However, this user is able to see the contents of the report manager and also can make changes role assignment under the properties tab in the report manager.
Accourding to my observation so far, any user account created on this domain machine is acting like an admin on the Report Manager, with out being given a permission on the Report Manager. I know something is wrong
Please somebody advise on this
View 5 Replies
View Related
Feb 29, 2008
here i am trying to get the count of both match ((substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11) + (AECPRDA_1.product_id = z.vendorcode)... probably AND won't do the trick.
i believe my below query (3) is wrong. what changes should i make to get both the match and continue further.
(3) should be atleast greater than (1) or (2)
1. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcode
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (only the first match)
2. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11) and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 2183 (only the second match)
3. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on ((substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11)) and (AECPRDA_1.product_id = z.vendorcode) )
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (1st & 2nd match.. i expect the count to be higher than 2)
what i am doing wrong here...
View 6 Replies
View Related
Jun 2, 2006
[Column 9] == "00000000" ? NULL(DT_WSTR,10) : RIGHT([Column 9],2) + "/" + SUBSTRING([Column 9],5,2) + "/" + SUBSTRING([Column 9],1,4)
Where destination sql table own as datatype smalldatetime and accept NULLS.
Where Column 9 is defined on the pipeline as [DT_WSTR] with lenght of 10.
Otherwise Column 0 is exactly the same than Column 9 and works fine with this expression:
RIGHT([Column 0],2) + "/" + SUBSTRING([Column 0],5,2) + "/" + SUBSTRING([Column 0],1,4)
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 9" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
View 1 Replies
View Related
Aug 28, 2007
Hi All!
Simple problem, I'm missing something.
I have this at the end of my statement
IF @@ROWCOUNT = 0
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'jrudolf@ikon.com',
@body= @body,
@body_format = 'HTML',
@Subject='NO RECORDS TO DELETE',
@profile_name = 'Ikon'
Else
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'jrudolf@ikon.com',
@body= @body,
@body_format = 'HTML',
@Subject='PLEASE REVIEW RECORDS',
@profile_name = 'Ikon'
END
I'm thinking when it returns 0 rows it should do the first email, else it should do the second email. But it's always doing the second. And I know it's 0 rows, because I'm not getting any results. And I made sure of it.
Thanks!
Rudy
View 9 Replies
View Related
Oct 31, 2007
My code is below. It gives me an error message that says:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
My code is:
SqlConnection TestConnection = new SqlConnection();
SqlDataAdapter TestAdapter = new SqlDataAdapter();
DataTable TestTable = new DataTable();
int RowPosition = 0;
private void MainForm_Load(object sender, EventArgs e)
{
this.MarketStatus();
TestConnection.ConnectionString = @"Data Source=I:DocumentsVisual Studio 2005ProjectsStockProjectStockProjectstocks.sdf;Integrated Security=SSPI";
TestConnection.Open();
TestAdapter = new SqlDataAdapter("Select * From StockInfo", TestConnection);
SqlCommandBuilder TestCommandBuilder = new SqlCommandBuilder(TestAdapter);
TestAdapter.Fill(TestTable);
this.DatabaseTest();
}
private void DatabaseTest()
{
if (TestTable.Rows.Count == 0)
{
txtStockSymbolTest.Text = "";
txtLastTrade.Text = "";
return;
}
else
{
txtStockSymbolTest.Text = TestTable.Rows[RowPosition]["StockSymbol"].ToString();
txtLastTrade.Text = TestTable.Rows[RowPosition]["LastTrade"].ToString();
}
}
private void MainForm_FormClosed(object sender, FormClosedEventArgs e)
{
TestConnection.Close();
this.Close();
}
View 3 Replies
View Related
Jun 5, 2007
So i started learning some sql compact edition, and i'm making a small applicaiton to store quotes in... So i started down that road, i setup a few table, had some auto increasing indexes, and i started working on it, after a while i wanted to delete my test data and so i deleted all the rows from each of my tables and then i tried to run a
Code Snippet
DBCC CHECKIDENT('AuthorsTable', RESEED, 0)
Then i get an error..
The error i get is
"There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = DBCC ]"
This works fine in sql express, but what am i doing wrong, I've looked all over the books online and all i can see is that there is a DBCC command in SQLCE, I've been looking for an answer for about 3 hours now and I'm not finding anything. So if you know what's going on, or if there is something else i can do to accomplish the same task.
View 4 Replies
View Related
Aug 30, 2006
I am using MVWD 2005 Express and Sql Server 2005 Express. I am using SqlDataSource control and GridView control to disply a ata table, and let user to input data into the database through dropdownlist and TextBox. When I run the application I always get exception:"Conversion failed when converting character string to smalldatetime data type." I could not find anything wrong. Could anybody out there help me find out what's wrong with my code. Much appreciate it.Here is the code:<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"DataSourceID="SqlDataSource2" DataKeyNames="DocumentID"><Columns><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /><asp:BoundField DataField="DocumentName" HeaderText="DocumentName" ReadOnly="True"SortExpression="DocumentName" /><asp:BoundField DataField="DateInput" HeaderText="Date" SortExpression="DateInput" /><asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" /><asp:BoundField DataField="DocumentCategory" HeaderText="Category" SortExpression="DocumentCategory" /><asp:CheckBoxField DataField="TopDoc" HeaderText="TopDoc" SortExpression="TopDoc" /></Columns><EmptyDataTemplate>There is no data to be displayed!</EmptyDataTemplate></asp:GridView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" DeleteCommand="DELETE FROM [Documents] WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"InsertCommand="INSERT INTO [Documents] ([DocumentName], [DateInput], [Comments], [DocumentCategory], [TopDoc]) VALUES (@DocumentName, @DateInput, @Comments, @DocumentCategory, @TopDoc)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Documents]"UpdateCommand="UPDATE [Documents] SET [DocumentName] = @DocumentName, [DateInput] = @DateInput, [Comments] = @Comments, [DocumentCategory] = @DocumentCategory, [TopDoc] = @TopDoc WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"><DeleteParameters><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></DeleteParameters><UpdateParameters><asp:Parameter Name="DocumentName" Type="String" /><asp:Parameter Name="DateInput" Type="DateTime" /><asp:Parameter Name="Comments" Type="String" /><asp:Parameter Name="DocumentCategory" Type="Int32" /><asp:Parameter Name="TopDoc" Type="Boolean" /><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></UpdateParameters><InsertParameters><asp:ControlParameter Name="Comments" Type="String" ControlID="TextBox2" /><asp:ControlParameter Name="DocumentCategory" Type="Int32" ControlID="DropDownList1" PropertyName="SelectedValue" /><asp:ControlParameter Name="TopDoc" Type="Boolean" ControlID="RadioButton1" PropertyName="Checked" /></InsertParameters></asp:SqlDataSource>Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.ClickDim savePath As String = "C: empuploads"Dim fileName As String = FileUpload1.FileName Dim dt As DateTime = DateTime.Now SqlDataSource2.InsertParameters.Add("DocumentName", fileName) SqlDataSource2.InsertParameters.Add("DateInput", dt)SqlDataSource2.Insert()If (FileUpload1.HasFile) ThensavePath += FileUpload1.FileName FileUpload1.SaveAs(savePath) Label1.Text = "Your file was uploaded successfully."DisplayFileContents(FileUpload1.PostedFile)ElseLabel1.Text = "You did not specify a file to upload."End IfEnd Sub
View 4 Replies
View Related
Mar 9, 2007
Hi im new and new to coding when i run the following code it gives me the following error can someone please help me and tell me where i am going wrong; "Invalid attempt to read when no data is present. " using System;
using System.Data;
using System.Data.SqlClient;
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 dclogin : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void BTNlogin_Click(object sender, EventArgs e)
{
String strSql;
SqlConnection conn = new SqlConnection("server=cssql; database=movie_stream; Trusted_Connection = true");
strSql = "SELECT MS_Customer.email_address,MS_Customer.Password FROM MS_Customer WHERE email_address = " + "'" + Txtemail.Text + "'";
SqlCommand dbCommand4 = new SqlCommand();
dbCommand4.CommandText = strSql;
dbCommand4.Connection = conn;
conn.Open();
SqlDataReader myDataReader = dbCommand4.ExecuteReader();
myDataReader.Read();
if (Txtpassword.Text == Convert.ToString(myDataReader["Password"]))
{
Response.Redirect("customer.aspx");
}
else
{
lblMessage.Text = "Please check your Email address and password and try again";
}
//newlabel.Text = Convert.ToString(myDataReader["Password"]);
conn.Close();
} // end chkpwd
} // end class
View 8 Replies
View Related
