Hello I am trying to insert a hyperlink to display on my web site, via the database, for example: http://www.tressleworks.ca/Modules/SQLGridSelectedView/LiveExamples/Example4Links/tabid/91/Default.aspx Name of my table is: Biz_Names Cols: ID_numberFNameLNameBusiness I know that this works: SELECT FName, LName, BusinessFROM BizNamesWHERE FName = 'John' ORDER BY LName ASC I want to add a hyperlink and I have since included extra columns: ID_numberFNameLNameBusinessurltitleLink
I want to add the hyperlink from the url column and use the title from the title column to fill the Link column, I don't want to include the url and the title column on the site. I only want to display Link data on the site, but so far my syntax is not working: SELECT FName, LName, Business, '<a href='" + url '"> + Title +'</a>' as LinkFROM BizNamesWHERE FName = 'John' And url like 'http:%' ORDER BY LName ASC What am I doing wrong? Thanks
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
Attempt to parse the expression ""*** " + @[System::MachineName] + " - SSIS Package Error - [ " + @[System:ackageName] + " ] @ " + (DT_STR, @[System::ContainerStartTime]) + " ***"" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
CASE field_val WHEN 'M5' THEN EXEC ( @query) ELSE THEN print '' END
I have a SP , and i state a query .. but i only want this query to be executed based on the value of field_val , otherwise i print a blank .. but when i check the syntax (ms sql server 2000) i get "invalid syntax near CASE ..
Hi I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1 The SQL I have written however returns all records SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2 WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1"> Please could someone help Many thanks Colin
We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:
CASE APTran.LineNbr WHEN < '0' THEN 'O' --Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry). WHEN > '0' THEN 'D' --Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry). ELSE 'E' END AS JED_DIST_OR_OFF,
The error is 'Server: Msg 170, Level 15, State 1, Procedure sp_append_SQLtables_to_NewDB_V2, Line 13 Line 13: Incorrect syntax near '.dbo.rt_action_level_type'.'
2γ?the PhotoManager's class is : public static List GetPhotos(int AlbumID) { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString)) { using (SqlCommand command = new SqlCommand("GetPhotos", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID)); command.Parameters.Add(new SqlParameter("@IsPublic", 1)); connection.Open(); List list = new List(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) {
3γ?photo.cs is using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Drawing.Drawing2D; using System.Drawing.Imaging; using System.IO; using System.Web; public class Photo {
private int _id; private int _albumid; private string _caption; private string _location;
private int _ViewCount;
private DateTime _LastModified; private int _AlbumCategoryID;
public int PhotoID { get { return _id; } } public int AlbumID { get { return _albumid; } } public string Caption { get { return _caption; } } public string Location { get { return _location; } }
public int ViewCount { get { return _ViewCount; } }
public DateTime LastModified { get { return _LastModified; } }
public int AlbumCategoryID { get { return _AlbumCategoryID; } }
4γ?the StoredProcedure "GetPhotos" is : ALTER PROCEDURE [dbo].[GetPhotos] @AlbumID int, @IsPublic bitAS SELECT [Photos].[AlbumID],[Photos].[PhotoID],[Photos].[Caption],[Photos]. [Location],[Photos].[ViewCount],[Photos].[LastModified], [Albums].[AlbumCategoryID] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [Photos].[AlbumID] = @AlbumID AND ([Albums].[IsPublic] =@IsPublic OR [Albums].[IsPublic] = 1) ORDER BY [Photos].[Caption] ASC
************* Edited by moderator Adec *************** Inserted missing < code></ code> tags. Always include such tags when including code in your postings. Many readers disregard postings without the code tags. **************************************************
Hello,
I have a big problem with some piece of code. I am new to C# and ado.net. My MSDE 2000 is set up and running. But the following code produces the error (translated from german) like "wrong syntax near the user keyword"
The connection to the database works fine. But trying to create a table or to drop a table alwas leads to the error. Whats wrong with it?
I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.
The details are as following:
There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = \test?, Folder = test? and JobNumber = 12345?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.
Then Im going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = \LiveServer?, Folder = Job? and JobNumber = 33091?.
After series of consequent executions I have got following log file:
¦ Execute Process Task 1¦ Executing the process \LiveServerJob_33091date.bat?
¦ Execute Process Task 2¦ Executing the process \Test est_12345 ime.bat?
¦ Execute Process Task 1¦ Executing the process \Test est_12345date.bat?
¦ Execute Process Task 2¦ Executing the process \LiveServerJob_33091 ime.bat?
¦ Execute Process Task 1¦ Executing the process \LiveServerJob_33091date.bat?
¦ Execute Process Task 2¦ Executing the process \Test est_12345 ime.bat?
¦ Execute Process Task 1¦ Executing the process \LiveServerJob_33091date.bat?
¦ Execute Process Task 2¦ Executing the process \LiveServerJob_33091 ime.bat?
¦
As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on default? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.
Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) youll get a good chance to catch error like this:
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.
Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task cant read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.
Hello i wrote this case statement to choose the password for a certain user but seems not to work. I have to get custom1 from another table. select cast(custom1 as varchar(5)) from dbo.newhiresraw where id =userid) when custom1 'C%' then password = abcd4321 when not 'C%' then password = [postalcode] end [postalcode].
Not sure if custom1 = C then password =abcd4321 If custom1 not = C then it will be a zipcode.
SELECT OH.OHONR, CASE WHEN LV.LVTYP='VIL' THEN LV.LVT30 ELSE NULL END AS LE, CASE WHEN LV.LVTYP='SΔL' THEN LV.LVT30 ELSE NULL END AS LS INNER JOIN LV ON OHONR=LVONR
The result gives one row for each hit on EITHER of the CASE statements. (Two rows if both case stantements are true) OHONR LE OHONR LV
I want to have one row with the two fields after each other. OHONR LE LV
Hi, I require some help with a SP. I have written the following CASE block but it is giving me an syntax error with Case statement.
Here @loc_id, @id_val and @inid are input parameters to the SP.
Basically what I am doing is I am selecting the column from table based on the input parameter @loc_id I want to use Case and avoid IF statements as Case will reduce the execution time.
If any of you have faced such problem and have a sloution then do let me know.
-- SP section starts
CASE @loc_id WHEN 1 THEN select @id_val = val1 from id_val where name = @inid WHEN 2 THEN select @id_val = val2 from id_val where name = @inid WHEN 3 THEN select @id_val = val3 from id_val where name = @inid WHEN 4 THEN select @id_val = val4 from id_val where name = @inid WHEN 5 THEN select @id_val = val5 from id_val where name = @inid WHEN 6 THEN select @id_val = val6 from id_val where name = @inid WHEN 7 THEN select @id_val = val7 from id_val where name = @inid WHEN 8 THEN select @id_val = val8 from id_val where name = @inid WHEN 9 THEN select @id_val = val9 from id_val where name = @inid END -- SP section ends
Can someone tell me what is wrong with my syntax? I am getting several errors:
Incorrect syntax near keyword 'CASE' Incorrect syntax near keyword 'WHEN'
Incorrect syntax near '@Tablename'
CREATE PROCEDURE al_readcampsignup
@User_ID int,
@Pagenumber smallint,
@Tablename varchar(10)
AS
CASE @Mypagenumber WHEN 1 THEN SELECT Blah FROM tempcampsignup WHERE Camp_ID = @User_ID WHEN 2 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID WHEN 3 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID END
I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..
I have my SPROC configured as below, can anyone help me out as to why its not working?
btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)
IF @genderID > 2 .. ELSE ........WHERE genderID = @genderID
I am attempting to mask a user's SS# based on Users.DisplaySSN. I have gone over the code time and time again and can't find where the error is.
Error I'm getting: Incorrect syntax near the keyword 'AS'.
Here's where the error lies: CASE (SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID) WHEN False THEN 'xxx-xx-xxxx' + right(SocialSecurityNumber,0) AS SocialSecurityNumber ELSE vwEmp.SocialSecurityNumber END
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE ses.BK_MS_SESSION <= '2015-03' AND vis.CAT_DRAW_STATUS = (CASE ses.BK_MS_SESSION WHEN '2015-03' THEN vis.CAT_DRAW_STATUS ELSE CASE stat.BK_MS_VISIT_STATUS WHEN 'T' THEN 'X' ELSE vis.CAT_DRAW_STATUS END END ) IN ('D','R')
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S1' THEN 'Other sale items' ELSE 'Not for sale' END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S2' THEN 'Other sale items' ELSE 'Not for sale' END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
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!
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?