Problem In Concatinating Two Paramters To Update One Column
Sep 20, 2007
Using gridview to display the data and sql server 2000
I have
a column in the database say departtime of datetime datatype that
cntains the date and time resp(09/19/2007 9:00 PM). I am separating the
date and time parts to display in two different textboxes say
txt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by using
the convert in sqldatasource. Now i need to update the column in the
database and i am using Updatecommand with parameters in aspx lke
updatecommand = "Update table set departtime = @departtime" . How can
i update my column as datetime by getting the data from 2 texboxes as
now i have 2 textboxes displaying data for single column means if user
edit the data in txt1 as(10/19/2007) then on click of update i need to
populate the column daparttime as (10/19/2007 9:00 PM).
Please let me know if you have any questions.
In SQL 2000, working with Maintenance plan wizard, what would be best settings and values should i choose in "Update Data Optimization information" window,
I am taking details from a linked server file (.XLS file). Two of the fields in this file are STARTTIME and STARTDATE.
The problem I have is that these have to be imported into a SQL table, but only into one column of type DATETIME. The column name is STARTDATE. I really have no idea on how to go about this. Any ideas would be greatly appreciated.
Hello,How do I concatinate a variable. Here's the scenarios:declare @var1 varchar(20)declare @var2 varchar(20)declare @var3 varchar(20)declare @var4 varchar(20)....declare @var32 varchar(20)set @var1 = 'Something 1'set @var2 = 'Something 2'....set @var32 = 'Something 3'/* I have to store the values of these individual variables. I wish tohave a "While" routine which iterates through the above variables. Iwish to have the variable name concatinated as that I do not have towrite numerous lines of code setting up individual 32 variables. Howcould I use the '+' operator to join 'var' + @count . Where count isfrom 1 through 32. I am having some trouble with the syntax.*/Regards,VS
I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.
The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.
Here is what I am wanting to accomplish:
UPDATE tblWorkOrdersSET Comments = (SELECT Comments FROM tblComments WHERE tblWorkOrders.ReqNum = tblComments.ReqNum)
I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.
Newbie question regarding a db I have inherited. A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.DocNo has numbers that represent persons. SequenceNo has numbers that represent specific documents associated with each person (DocNo).So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.My SELECT statement looks like this:SELECT ReadingNo, SequenceNoThis returns data like this: 5 3I would like to concatenate the SELECT statement to return like this: 5-3So I made Sql like this:SELECT ReadingNo + '-" + SequenceNo Which returns a alias ('No Column Named') result value of 8 which is an arithmetic result instead of a string concatination that I want.So my questions are:1. Should the original database designer have used string data types for these columns since they will never be used for math purposes?2. Do I need to cast them to string data type (like nchar(4) - since neither column will ever exceed 4 digits) to get the result I desire?3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?
Hii,I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?use northwind select city, region,([city]+ +[region]) as uniqefrom customerswhere region is not nullThe resulting records in Quary Anchorage AK AnchorageAKTsawassen BC TsawassenBCVancouver BC VancouverBCSan Francisco CA San FranciscoCA
I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:
SET CONCAT_NULL_YIELDS_NULL ON
Here's the code snippet:
select DISTINCT
(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +
(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +
(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +
(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +
(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'
The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.
Any thoughts or ideas would be greatly appreciated.
I am in a SQL Class and I am starting to learn about SQL Parameters in conjunction with using them from a C# program. I have a couple of questions.
1. When I include an Output parameter in the list or paramters that I send, SQL always is returning a message indicating that the parameter was missing, when I know in fact that I am passing the paramter (@RtnMsg)
2. When I remove any reference to "@RtnMsg", which it can not find, and execute the stored procedure it always returns a -1 and never inserts the record.
From previous postings I have read it seems that I cannot create afunction that accepts a variable list of parameters. Seems that SQLServer does not support this.A few questions:Is this true?What is a workaround?Then how does the function COALESCE do it?Cut and pasted from online books:SyntaxCOALESCE ( expression [ ,...n ] )ArgumentsexpressionIs an expression of any type.nIs a placeholder indicating that multiple expressions can bespecified. All expressions must be of the same type or must beimplicitly convertible to the same type.
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
Code Block RS2000
="with " & "member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " & "set [Rolling Quarters] as '" + Parameters!DateQuarter.value +".lag(4):" + Parameters!DateQuarter.value + "'" & "SELECT " & "{[Measures].[Ex Price]} ON COLUMNS , " & "filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows " & "FROM Sales " & "WHERE (" & Parameters!CustomersLevel.Value & ")"
RS2005
with member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' set [Rolling Quarters] as '@DateQuarter' +'.lag(4):' + '@DateQuarter' SELECT {[Measures].[Ex Price]} ON COLUMNS , filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows FROM Sales WHERE STRTOSET(@CustomersLevel)
We are using MS Reporting Services to generate Xmls for corporate billing statements. Our DBAs found that MS ReportingServices are querying Oracle Catalogue before executing stored procedures in RDLs to get Oracle parameter information. Is there a way to eliminate the trip to database before executing the stored procedures?
I have one table with categoriestblCategoriescat_id | cat_name-----------------1 | cat 12 | cat 23 | cat 34 | cat 45 | cat 56 | cat 6and one table with projects which relates to tblCategoriestblProjectsproj_id | proj_name | cat_id----------------------------1 | proj 1 | 22 | proj 2 | 23 | proj 3 | 34 | proj 4 | 2How would you create stored procedure for searching some string infiled proj_name but within multiple categoriesfor exampleCREATE PROCEDURE [spSearch](@SEARCH_STRING nvarchar(200),@CAT_ID int)ASBEGINSELECT proj_idFROM tblProjectsWHERE (proj_name LIKE '%' + @SEARCH_STRING + '%') AND (cat_id =@CAT_ID)ENDBut that one works only with one categorie and i need to search for oneor more categories at once, does anyone have a solution? Is theresomething like ellipsis (...) in C++ for MSSQL?
Hi Im writing a program that stores data to SQL server 2005. Program writen on VC++ 2003. Code is very simple and it works ok if server runs and parameters are ok.
But! if i give wrong paramterer for Data Source , it may happen if user configures it and misprints it, or if server goes offline for some reason, program just hangs on m_Connection->Open and never gives exception as it should. Any ideas what wrong? Thanks.
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate) When I use the above on a GridView I get nothing. Using SQL manager it works great. I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do. I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date. Thanks for any help!
I have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st using (select * from InputStudent ins) on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid , st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid else st.previouschoolid end , st.grade_level=ins.grade_level ;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?
I'm using SQL Server 7. I have an invoice table. The invoice table has a datetime column called InvoiceDate. The InvoiceDate column contains the following date format: 5/3/00 I would like to use the InvoiceDate column to update the char (6) column called zInvoiceDate as a formatted date field like yymmdd.
The following syntax did not work: SET zInvoiceDate = Convert([ARInvoiceID],GetDate()12)
When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:
UPDATE [statistics] SET percentage = follow_count / view_count * 100 WHERE (stat_id = 15)
This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)
I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help. What I want to do is give a column in a table the same value as another column from the same table. For example: Table:Requests A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
i need to update the value in the column by adding to the value already in that column.
For eg, the value is in the column is already 5, i need to update it with addition of 8. i.e 5+8 = 13. The final value should be 13 in the column after that. How do I go about doing it?
UPDATE Table 1 SET ColumnName +=8 ?
Thanks in advance. really at a loss and very urgent.
I'm quite new to SQL and wondered if someone could help. I'm pretty good at writing reports but I now need to do an update.
Basically i need to update a column in table1 where coulmn 1 in table4 = Y, however to get to table4 I think I need to link to all 4 tables, table4 is the last table. The tables link based on ID's
Tried a few things such as
UPDATE table1 SET column1 = '' WHERE column1 IN ( SELECT column1 FROM table4 WHERE column1 = Y)
Now I need to get to table 4 I need some other wHERE clauses to link them together, just like I would if I were writing a SELECT statement.
I have a table with a column called data. In that column is a value like: <settings><myVarOne>valueOne</myVarOne><myVarTwo>valueTwo</myVarTwo></settings>
All I'd like to do, is update all the myVarOne values. So the new value for data would be: <settings><myVarOne>newValueHere</myVarOne><myVarTwo>valueTwo</myVarTwo></settings>
This will likely be SQL2000 not SQL2005 but it would be useful to know for both.
I've looked at OPENXML but all the examples seem keen on using sp_xml_preparedocument and then OPENXML needs the @idoc so I'm thinking there is something else.
If someone can point me in the right direction that would be extremely helpful as I haven't found anything that makes sense to me. UpdateGrams seems very overblown for manipulation like this when OPENXML is sooo very close to being correct.
Is it possible to update from one table to another?Pls examine my code here: UPDATE tStaffDir SET tStaffDir.ft_prevemp = ISNULL(tStaffDir_PrevEmp.PrevEmp01, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp02, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp03, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp04, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp05, ' ') Where tStaffDir_PrevEmp.ID=tStaffDir.ID I am trying to concatenate the columns from tStaffDir_PrevEmp to tStaffDir but I have this error where tStaffDir_PrevEmp is recognised as a column and not a table. Pls advise how this can be done. Many Thanks.
The problem is with @NUMERY parameterin code behind i setDim dr As GridViewRowDim numery As Stringnumery = "" For Each dr In GridView1.RowsDim numeros As Label = dr.Cells(0).Controls(1)numery += numeros.Text & ", "Next numery = numery.TrimEnd(", ") SqlDataSource1.UpdateParameters("NUMERY").DefaultValue = numery'so numery will look like this 123, 65465, 54616, 56465Update command looks like this :UpdateCommand="UPDATE slon SET mrowka = @MROWKA WHERE (NUMER IN (@NUMERY))" <UpdateParameters><asp:Parameter Name="MROWKA" /><asp:Parameter Name="NUMERY" /></UpdateParameters> And because of @numery i have err: Error converting data type nvarchar to numeric. how should i post "123, 65465, 54616, 56465" as parameter for this query ?
