HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI have tried this:SELECT FName, LNameFROM Names_DBORDER BY LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY LName 'ASC' What is wrong with this syntax?ThanksLynn
I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
This is a Stored proc I am working on I dont know much about stored procs. This is what I am trying to achieve The proc takes two input parameter and returns one outparameter.
This is what I have
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30), @NextAction ntext output ) AS BEGIN
SET NOCOUNT ON; Declare @ID int; @ID = Select Sequence from [dbo].[Track] where Code=@Code; Declare @nextCode varchar; @nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1); @NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode; return @NextAction;
END GO
I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this. Thanks
If someone can show me the correct syntax for the right side of the code below, I'd appreciate it. srcMoreInfo.InsertParameters("Address").DefaultValue = DirectCast(cuwMoreInfo.FindControl("txtAddress"), TextBox).Text
WHATS WRONG IN THIS CODE ? --------------------------------- PS: I'm getting this error: -------------------------------
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 7 Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
Hi, I'm new to SQL and was wondering if there was an easier way to filter data.
I have two tables -
The first table called Names of Companies has a column named: NAMES
NAMES XYZ Company ABC Limited Liability Company ZZZ Corporation KKK Inc. ABC Inc.
The second table called Keywords has a column named: WORDS
WORDS Company Limited
I want to search for all NAMES that contain the WORDS in some form
The results should be:
NAMES XYZ Company ABC Limited Liability Company
Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.
SELECT * FROM [Names of Companies] WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));
But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.
I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive] @DB varchar(30), @Date DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives' , @step_id = 1 , @step_name = 'ArchiveAHD' , @subsystem = 'TSQL' , @command = 'spArchiveAHD ''@Date''' , @on_success_action = 3 , @on_fail_action = 2 , @database_name = '@DB' , @retry_attempts = 1 In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] ( @dtArchiveBefore DateTime)AS I'm unclear about these lines: @command = 'spArchiveAHD ''@Date''' @database_name = '@DB' Do they look correct to you or should I drop some/all of the apostrophes?Robert W.
I try to call the storeproc to perform task by allowing only input 6 of length. The syntax over here might not be relevant, im seeking for correct syntax. webform Dim connString2 As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString Using myConnection2 As New SqlConnection(connString2) Dim test01 As IntegerDim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection2) myPuzzleCmd2.CommandType = CommandType.StoredProcedure Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6) < -------- allow 6 letters in length to be input retLengthParam.Direction = ParameterDirection.InputmyPuzzleCmd2.Parameters.Add(retLengthParam) Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30) retRandomCode.Direction = ParameterDirection.Output myPuzzleCmd2.Parameters.Add(retRandomCode) TryDim reader2 As SqlDataReader = myPuzzleCmd2.ExecuteReader() myPuzzleCmd2.ExecuteNonQuery() Catch ex As Exception Response.Write("sp value : " & retRandomCode.Value) Dim iRandomCode(1) As StringReDim Preserve iRandomCode(1) iRandomCode(1) = Convert.ToString(retRandomCode.Value)myPuzzleCmd2 = Nothing Session.Remove("RandomCode") HttpContext.Current.Session("RandomCode") = iRandomCode(1) Response.Write(Session("RandomCode")) Finally myConnection2.Close() End Try End Using
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number). I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance. The database table is called "friday" and the one and only column is called "Tickets". Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e) {SqlConnection conn; conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***"); conn.Open();int count = -1; SqlCommand cmd = new SqlCommand("select Tickets from friday", conn); count = (int)cmd.ExecuteScalar();if (count > 0) { string updateString = @" update friday set Tickets = 500" ; <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString); cmd2.Connection = conn; cmd2.ExecuteNonQuery(); } else { } conn.Close(); }
I am having trouble finding the correct syntax to access a variable. I have a variable defined in the Variables window: The variable name is formatedDate. The DataType is String.
I am successfully setting the value of the variable in a Execute SQL Task. The SQL is as follows:
SELECT LEFT(CONVERT(VARCHAR, MAX(ReportDate), 120), 10) as formatedDate from DimReportDates
The Result Set is set to “Single Row” and properly set up.
No problem so far. I can see with a watch that the variable has the correct value, something like:
‘2015-09-30’
Now, in a subsequent step, a Data Flow Task, I want to access the variable. Actualy it is in the SQL statement of a OLE DB source in the Data Flow… I have the following:
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select * from VendTable1 a join ( select firstname, lastname from VendTable1 group by firstname, lastname having count(*) > 1 ) b on a.firstname = b.firstname and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
Could anyone help med with a select statement with a join between totables. It is to be used in a OLAP cube.I Havde table LedgerBudget and Table Admin. In table admin I can setupa from and to date and also a budgetmodel.The admin have ONE record per OLAP cube.The statement below works fine if I have stated a budgetmodel in tableAdmin.But if no budetmodel stated in table Admin, I want the statement toselect every ledgerbudget with active = 1 and allocatemethod = 0Could anyone help me with this.SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE, - dbo.LEDGERBUDGET.AMOUNT ASBUDGETFROM dbo.LEDGERBUDGET INNER JOINdbo.ADMIN ON dbo.LEDGERBUDGET.STARTDATE >=dbo.ADMIN.FROMDATE ANDdbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATEANDdbo.LEDGERBUDGET.MODELNUM =dbo.ADMIN.BUDGETMODELIDWHERE (dbo.LEDGERBUDGET.ACTIVE = 1) AND(dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0)BR/Thanks
I am importing data from a legacy system. The date column is a varchar and in the destination DB it is a proper DateTime.
That said I have invalid data and need to clean / accommodate for it as I go. What I need is if the date is valid then insert it, if not then ue a default of '01/01/1900' I am doing the following and it is not working.
CASE WHEN isDATE(RatifiedDate) = 1 THEN RatifiedDate WHEN isDate(RatifiedDate) = 0 THEN '01/01/1900' END as RatDate
INSERT INTO #TEMP VALUES(1,'James',NULL) INSERT INTO #TEMP VALUES(1,'James','George') INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1) INSERT INTO #TEMP2(ID) VALUES(1) INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,* FROM #TEMP ) SELECT CASE WHEN RowNum=1 THEN CREATEDBY WHEN RowNum > 1 THEN ModfiedBy END FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,* FROM #TEMP ) UPDATE #TEMP2 SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY WHEN RowNum > 1 THEN ModfiedBy END FROM CTE WHERE #TEMP2.ID=CTE.ID
In a select statement select_list, if one of the columns in the result set is NULL, I need to substitute a constant value else I want to return the value in the column. Would I use an IF_THEN_ELSE or CASE expression and how would that look? Thanks, Robert
I need to use IF-THEN in a SQL Select statement. Using Google I have found a couple of obscure references to this but nothing that I can use. Tried various combinations without luck. Can anyone point me to a good resource or supply a simple example of proper syntax? Thanks in advance for any help provided.
I have the following statement that I am trying to convert to a "like" statement in a SqlDataSource for a web application. I can't seem to get the syntax correct. Would someone be able to assist with this? Thanks! SELECT * FROM [Employees] WHERE ([LName] = @LName) ORDER BY [LName], [FName] Something like below. SELECT * FROM [Employees] WHERE ([LName] LIKE '@LName%') ORDER BY [LName], [FName]
What is the syntax for making a query using like, the below is my code <asp:SqlDataSource ID="Search" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString.ProviderName %>" SelectCommand="SELECT * FROM [Products]WHERE category LIKE %@category%">
<SelectParameters> <asp:QueryStringParameter Name="category" QueryStringField="category" Type="string" /> </SelectParameters> </asp:SqlDataSource> Its giving a syntax error but I dont know how to change it. Please advise, thanks!
Hi everyone, I'm looking for a way to select the default value of a particular column but can't seem to find the syntax for this anywhere. It's a rather peculiar situation where it's needed... Thanks
