I realize that I need to learn how to write expressions for conditional splits, etc. Frankly, I don't even know what "language" expressions are written in. They look a lot like excel functions to me.
I tried searching the internet for some documentation, but it is sparse.
Can anyone point me to some links, etc that can teach me how to write my own expressions?
Im trying to write an expression in a table cell which counts the number off records in a dataset given it is a certain value..
first column cell =count(Fields!idproduct.Value) = 'broadband' row heading = New Sales second column cell =count(Fields!idproduct.Value) = 'adsl'
I can create a matrix report which will achieve this however I need to create a few other calculated columns which a matrix table doesnt allow hence im trying to do this via expressions in a normal table, can this be done with IIF statements?
In my continuing saga with writing my little real estate application, I've got the need to upload images to a folder within the website and then capture some image information in a db. I know there are many ways to do this, and have read lots of them. However most answers don't fit what I'd like to do, or they're too complicated for me. Anyway, thought I'd write it from scratch. So, I have 2 pieces of code, 1 that uploads the image to a folder, and another piece that captures information to a db. The first part works great. The image file successfully gets uploaded. Problem lies in the db insert code. It's not capturing and writing to the db. Here's my code, and thanks in advance for your help! Partial Class Default2 Inherits System.Web.UI.PageProtected Sub page_load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If IsPostBack Then Dim path As String = Server.MapPath("~/images/") Dim fileOK As Boolean = False If FileUpload1.HasFile Then Dim fileExtension As String fileExtension = System.IO.Path. _ GetExtension(FileUpload1.FileName).ToLower() Dim allowedExtensions As String() = _ {".jpg", ".jpeg", ".png", ".gif"} For i As Integer = 0 To allowedExtensions.Length - 1 If fileExtension = allowedExtensions(i) Then fileOK = True End If Next If fileOK Then Try FileUpload1.PostedFile.SaveAs(path & _ FileUpload1.FileName) Label1.Text = "File uploaded!" Catch ex As Exception Label1.Text = "File could not be uploaded." End Try Else Label1.Text = "Cannot accept files of this type." End If End IfEnd IfEnd Sub ========================================================================= Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click 'Connect to the database and insert a new record into Products Dim picDataSource As New SqlDataSource()picDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()picDataSource.InsertCommandType = SqlDataSourceCommandType.TextpicDataSource.InsertCommand = "INSERT INTO Images (url, title, imageTag, fileName, PictureID) VALUES (@url, @title, @imageTag, @fileName, @PictureID)" picDataSource.InsertParameters.Add("url", "~/images/")picDataSource.InsertParameters.Add("title", imgTitleTxtbox.Text)picDataSource.InsertParameters.Add("imgTag", imgTagTxtBox.Text)picDataSource.InsertParameters.Add("fileName", imgFileNameTxtBox.Text)picDataSource.InsertParameters.Add("PictureID", 3) Dim rowsAffected As Integer = 0 Try rowsAffected = picDataSource.Insert()Catch ex As Exception Label2.Text = "Label 2 File could not be uploaded." Finally picDataSource = Nothing End Try If rowsAffected <> 1 Then Label3.Text = "Label 3 File could not be uploaded." Else Label3.Text = "File uploaded!"End If End Sub End Class ==================================================================== Here's the HTML for the upload page: <h1>Upload Property Image(s)</h1> <table border="0" cellpadding="10" cellspacing="0" style="width: 757px"> <tr><td style="width: 100px" align="right"><strong>Image Title</strong></td><td style="width: 100px"><asp:TextBox ID="imgTitleTxtbox" runat="server"></asp:TextBox></td></tr> <tr><td style="width: 100px" align="right"><strong>Image File Name</strong></td><td style="width: 100px"><asp:TextBox ID="imgFileNameTxtBox" runat="server"></asp:TextBox></td></tr> <tr><td style="width: 100px" align="right"><strong>Image Tag ID</strong></td><td style="width: 100px"><asp:TextBox ID="imgTagTxtBox" runat="server"></asp:TextBox></td></tr> <tr><td style="width: 100px; height: 38px" align="right"><strong style="text-align: right">Add Image</strong></td><td style="width: 100px; height: 38px"><asp:FileUpload ID="FileUpload1" runat="server" /></td></tr> <tr><td style="width: 100px; height: 38px"></td><td style="width: 100px; height: 38px"><asp:Button ID="Button1" runat="server" Text="Upload Image" /></td></tr> </table> <br /> <asp:Label ID="Label1" runat="server"></asp:Label><asp:Label ID="Label2" runat="server"></asp:Label><asp:Label ID="Label3" runat="server"></asp:Label>
I have a program where I have to do a weekly data upload of approx 1,500 records. I've written the sql, but need to know how I can add static header text to the SQL statement.the text I need in the header is Reportname(Static string) + #rows (Rowcount of the sql, int) + department (String static) I'm planning on writing it and saving it as a package, then schedule it to run every Friday at 5:00. But I have been reading a little on Reporting Services and wonder if people think it' might be the way to go? Is it a whole new area of security issues? Or is it worth installing and learning? I will have more and more reports I need to write that will increase in complexity, and want to know if I want to keep writing querys by hand, or use reporting services. Thanks in advance! Dan
How do I determine which version of SQL Server I have? In other words, I don't mean 6.5 or 7.0, rather, am I using the standard version, the desktop version, the enterprise, etc.?
i would like to know where can i find information on the concept of SQL like Base Table , View Table and Search Table as now i am using VS.Net 2005 and SQL Server 2005 to write a addressbook program and i am just a newbie to Sql and c#... i wish to write my sql commands in c# side before passing it into sql and may i know how to do it?
I have a table that contains a column called hardness. It is a varchar 50 and actually contains numbers. I did not create the database so not sure why they choose varchar vs. number. I need to write a query that will return all results with a space prior to the number in this column. I am having difficulty and would greatly appreciate some help. Thanks!
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.
Am I correct? If so... why not?
Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())
I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:
Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)
I have a table CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL) We will use these 3 queries select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage I want to make a method which handles these queries. But at the back end on Stored Procedures We have to write 3 queries Which I don't want to do. I want to minimize the queries and conditions and want to just write one for these 3 Can any one do it?
Hello all, I have come across a road block once again and it is probably something fairly easy to do. I have 3 queries that give counts with different criteria. They are: SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_scores = 'N') GROUP BY building ORDER BY building SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) GROUP BY building ORDER BY building SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_asmt = 'N') GROUP BY building ORDER BY building My question is how can I combine these three queries into one so that I only get one record set? Thanks for all the help!
I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.
For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:
"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"
I know that you can make an expression that will make it one color if a certain condition is met and a different one if it is not but is there anyway to make it so that if a number is less than another it's one color, if it's greater it's a different color and if they're equal it's a third color? Thanks for the help.
I am attempting to implement the following case statement BEFORE getting the data in to my destination table but I don't know how to create an expression for it.
In the mapping section of my OLE DB destination component I can only do mapping but I can't actually manipulate the data before it gets to the destination table.
What do I have to do to implement :
case when SOPD.PRICE_TOP_NUMBER is NULL then -8 else SOPD.PRICE_TOP_NUMBER end AS price_top_number,
I have a unusual problem, well it is unusual becaue i cannot understand it.
I am retrieving the following data, Revenue, Sales, Commission, and calculating Commission Rate in the report itself.
I got warnings that a textbox that was attempting to calculate the Commission Rate was trying to divide by zero.
When I analysed the data, I realised that indeed there were instances when Revenue was 0.00 and therefore when calculating the commission rate, commission/revenue it returned an error.
I then thought I'd do the following in the expression field
it does work. In fact if I use any other identifier or use two field thats add, subtract or multiplied with each other it will work. The problem only arises is when I decide to divide two fields together.
Strangely if the condition is satisfied - [Fields!RevenueGbp.Value = 0.00] - it will still try to work out the division even though it should just return Zero!
[CollectTrnsRxaFecPagLog [4712]] Information: The processing of file "\testteamserverMigracionLog\PCKG_BH_COLLECT estteamservermigraciondatamcli010001rxaCollectTrnsRxaFecPagLog.txt" has started.
I need to create a While Loop with the FOR LOOP task.
According to my SSIS book, "to set up a While Loop in the For Loop, simply create an expression that tests a variable that can be modified by some workflow either inside or outside the For Loop"
What I want to do is, put an FTP task (whose job it is to receive a file) inside the For Loop.
I want the control flow to go like:
FOR LOOP --> FTP task --> file received? --> NO --> set variable 0 --> FOR LOOP --> check for variable --> is variable 1? --> NO --> FOR LOOP --> FTP task
and so on, until the variable is set to 1, indicating the successful transfer of the file
According to my book, this can be done simply with the use of expressions.
My question is, how do I write the expressions, both for the FTP task (which will set the variable to 0 or 1), and then the expression for the For Loop, which will evaluate the variable in its expression?
So, seems like I need to write 2 expressions, but not sure how.
I have a table with about 20,000 records that have a date field, stored as a datetime in the database like '8/28/2006 8:42:14 AM'. The dates range from March 2004 to current. What I would like to do is retrieve the dates in that format (month year) and put them in a dropdown. I have this so far:SELECT DISTINCT DATEPART(month, dte_date) AS Expr1, DATEPART(yyyy, dte_date) AS Expr2 FROM myTable ORDER BY DATEPART(yyyy, dte_date), DATEPART(month, dte_date)And the query returns the information that I want, but I can only bind one field to the dropdown. I was thinking that if I return the results a single expression (concantenate?) then I could bind that to the dropdown. I'm not sure as how to go about this. Also, the month returned is numeric and I would rather have the name of the month returned (like "July" instead of "7"). Thanks in advance to anyone who helps me.
I'm in the process of building a site and converting views/tables/queries from an Access database to SQL. I've done this quite a few times, and never had any significant issues I couldn't figure out on my own.
In Enterprise Manager, I've created a view and in the query, I need to create an alias that is similar to below:
SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue
which is how the formula reads in the Access view.
However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order:
(monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue
Which gives me an incorrect value.
Does anyone know why this is happening, or am I just unaware of the right way of doing it?
I am creating a check constraint on a field (GRID_NBR) for values between 1 & 99. I am a little confused on creating the expression for it (Books online is vague).
Can I use the following expression: GRID_NBR BETWEEN 1 AND 99
Or do I have to use: GRID_NBR > 0 AND GRID_NBR < 100