insert @TempAddressParsingTable (ad_str1)
select 'apple UNITY RD' union all
select 'watermelon UNITY RD unit#32' union all
select 'currency UNITY RD unit# 99' union all
select 'marrakesh UNITY RD unit #32'
select * from @TempAddressParsingTable where ad_str1 like '%unit%'
select * from @TempAddressParsingTable where ad_str1 like '%unit%'
and ad_str1 not in (select ad_str1 from @TempAddressParsingTable where ad_str1 like '% unity %')
I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?
Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...
Here is my sql procedure: ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?
i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it. assume i have a query like this. Select A, B, C from Table1 where ID=xyz whats the easiest way to get A, B and C in to string variables? I know i can use the sqldatasource control but i just feel there is too much overhead there. whats your suggestions? Thanks Justin
I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?Thanks in advance.
I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.
Can any one please tell me how to retrieve values from timestamp column. I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Hi, all. I tried to get result of sp. Dim rst As Recordset Set rst = New ADODB.Recordset rst.Open sp, cnn
doesn't get result. when i call rst.EOF, it thows error: Can do this since rst is closed...
I found it's problem of sp which is little complex. But, still I think it should work! My question is how can I get the returned value from following sp in VB?
--- return list of tables that needed to update --- list is one string separated by '&' delimeter --- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&... CREATE procedure spGetListOfTableToDownLoad @listOfUpdateTime varchar(500) as SET NOCOUNT ON Declare @listOfTable varchar(300), @item varchar(300) Declare @tbl varchar(50), @uptime datetime Declare @list varchar(500) Declare @sep varchar(1) SET @list = '' SET @sep = '&' DECLARE cur CURSOR FAST_FORWARD FOR SELECT * FROM fnSplit(@listOfUpdateTime, @sep)
OPEN cur
FETCH NEXT FROM cur INTO @item
Declare @re bit, @tp varchar(50) WHILE @@FETCH_STATUS = 0 BEGIN
-- get tablename, update time Declare cur2 CURSOR FAST_FORWARD FOR SELECT * FROM fnSplit(@item, '=') OPEN cur2 FETCH NEXT FROM cur2 INTO @tbl Print @tbl FETCH NEXT FROM cur2 INTO @tp print 'tp:' + @tp SET @uptime = CAST(@tp as datetime) print @uptime -- @re =1: true, 0: false EXEC spIsUpdate @tbl, @uptime, @re output IF @re = 1 SET @list = @list + @tbl + @sep CLOSE cur2 DEALLOCATE cur2
FETCH NEXT FROM cur INTO @item END if LEN(@list) > 0 SET @list = LEFT(@list, Len(@list)-Len(@sep)) CLOSE cur DEALLOCATE cur
SELECT @list as Result
SET NOCOUNT OFF
GO __________________ --- PARAM:: @tbl: table name, --- @uptime : update time (passed from local db) that will be compared on HQ table --- return 1 if Max(UpdateTime) of @table > @uptime --- otherwise return 0 CREATE Procedure spIsUpdate @tbl varchar(50), @uptime datetime, @result bit output as BEGIN Declare @bit bit
DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) declare @uptimeHQ datetime /* Build the SQL string once.*/ SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl SET @ParmDefinition = N'@tp datetime OUTPUT' EXECUTE sp_executesql @SQLString, @ParmDefinition ,@uptimeHQ OUTPUT
If @uptimeHQ > @uptime SET @result = 1 ELSE SET @result = 0 --RETURN @bit END
I am trying t get output for the following querry but I know am missing something. Can anyone help me out with it.
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
In the above query am getting the productnum and quanity and it looks like this
productnum quantity abc 6 ttt 3 sss 1
What am tring to do to this query is that . From another table 'product' i want all the data to be retrieved with this productnum(the table 'product' has a column called prductnum). I don't know how to write a query for this.
my query is select * from product where productnum in ( select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
I have retrieved the data using CTE . But still need the retrieve the latest row number record from my result.
;with cte as ( Select ROW_NUMBER() over ( PARTITION by [T7S1_PRODUCT_CDE_original],[T7S1_BR_NO_original] order by [sql_updated] ) rn , [sql_updated] ,[T7S1_TYP_CDE] ,[T7S1_PRODUCT_CDE_original] ,[T7S1_BR_NO_original] from [interface_i084].[dbo].[tb_i084_ds_CeresTLStageFileDocDetail](nolock) ) Select * from cte
I'm trying to compare the fields if they are equal, retrieve it. Because null <> null. i cant retrieve the record. Is there anyway to retrive the records by comparing all the fields?
select * from @table where ad_num = ad_num_test and ad_str1 = ad_str1_test ad_num_suffix_a = ad_num_suffix_a_test and ad_num_suffix_b = ad_num_suffix_b_test and ad_unit = ad_unit_test
i have a table that has 9 columns that belongs to other tables. i need to retrieve one column from this table. In order to do this, do i have to join all the other tables that has similar columns?
here is my table: MVPTABLE columnName: Accsno JanMail JanVisit JanPhone JanComments FebMail..upto DecMail DecVisit DecPhone DecComments.
eg: table with values: Accsno JanMail JanVisit JanPhone JanComments FebMAil FebVisit A234 1 2 3 yes jim 0 2 A234 0 2 0 No Comments 1 2 As34 0 0 0 No Comments 1 2 A235 1 2 3 yes jim 0 2 A235 0 2 0 No Comments 1 2 As35 0 0 0 No Comments 1 2
am sending 2 parameter as 1> @param1= A234 2> @param2= 'JanMail,JanVisit,JanPhone,FebMail,FebVisit,FebPhone,MarMail,MarVisit,MarPhone,AprMail,AprVisit,AprPhone,MayMail,MayVisit,MayPhone,JunMail,JunVisit,JunPhone,JulyMail,JulyVisit,JulyPhone,AugMail,AugVisit,AugPhone,SepMail,SepVisit,SepPhone,OctMail,OctVisit,OctPhone,NovMail,NovVisit,NovPhone,DecMail,DecVisit,DecPhone
based on these 2 parameter i wanted to retrieve respective Comments say, if @param1 Ac234 @param2= JanMail,JanVisit,JanPhone,MarMail if the value of JanMail or FebMAil....DecMail = 1,then retieve respective comments if the value of JanVisit,FebVisit..........DecVisit=2,then retrieve respective comments if the value of JanPhone,or FebPhone,.....DecPhone=3 then retive respective comments
Before I ask a question, I am visually impaired and cannot read printed words only on a screen. I am 51 years old and have been coding professionally since I was 23 (yes that long). I have four apps on the market and run a small consultancy company in the UK and my SQL knowledge is scant. I have read two books on the subject and thought I had an idea on how SQL works.
I used to belong to another forum but they gave me a hard time so please go easy on me.
I developed an ADO app ages ago and remembered some of it ...
I have four tables (the following is a simplified version of the real thing)
MainTable (Contact data like name address etc. 23 fields) Towns (Two fields TownUIN and TownName) Counties (Two fields CountyUIN and CountyName) Countries (Two fields CountryUIN and CountryName)
My database is normalised to 3NF and contains no duplicate or redundant data.
My problem ...
Sample data
Main: MainUIN MainName MainTown 1000 Fred Bloggs 1 1001 Fred Smith 2
Towns: TownUIN TownName 1 Bradford 2 Leeds
SELECT MainName,MainTown FROM Main WHERE MainUIN=1000 returns 'Fred Bloggs' 1 (the TownUIN)
I want ... 'Fred Bloggs' 'Bradford'
It was suggested I use SELECT MainName,MainTown FROM Main,Towns WHERE MainUIN=1000 AND TownUIN=MainTown This returned 'Fred Bloggs' 'Bradford' but was slow.
My question is what am I doing wrong?
I am using SQL Server 2005 Express with VB.Net 2005 on Vista Business and XP.
I am sorry my SQL knowledge is weak please dont get angry just tell me the basic thing that I do not know.
Hi,I am going to be difficult here... How do I retrieve one row at atime from a table without using a cursor?For example, I have a table with 100 rows. I want to retrieve thedata from row 1, do some stuff to it and send it on to anther table,then I want to grabrow 2, do some stuff to it and send it to another table.Here is how I am envisioning it:WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by totalrowcount)BEGINSELECT * FROM [TABLE] BUT ONLY ONE ROW.... MANIPULATE THE DATAINSERT into another tableENDOther notes, I am using SQL Sever 2000....Thanks and in advance and as always the help is greatly appreciated.Regards,CLR
Hi.I have a IIS server with php3 installed. I have SQL server databaseand data stored in unicode format (nvarchar fields). In header of myphp I have the meta: <meta HTTP-EQUIV="content-type"CONTENT="text/html; charset=UTF-8">.I have the following problem:- I trie to do a query using ODBC (version 3.525.1022.0) and SQLServer odbc driver (version 200.85.1022.00). The query doesn'tretrieve information in utf format and accents and extra charactersaren't showed correctly.- I trie to di the query usin native OLE DB. I use mssql funtions andthe problem is the same.If I install php4 and I use "newCOM("ADODB.Connection",NULL,CP_UTF8);", then it works correctly.Anybody knows if its posible to retrieve data from sql-server in utf-8format using php3?I see in other postings that odbc driver only accept utf-8 in version3.7 or higher? Is it true? If is true, where I can download odbcdriver 3.7?Thank's in advance.Miki.
I have a db with three tables - books, sections, and a joining table.The normal way of getting a many to many relationship (i.e. one bookmay belong to many sections, and one section may contain many books)I want to extract the data with a single row for each book so that Ionly retrieve the first section description for any book. (e.g. title,author, section, description)Structure as follows:tbl_bookbook_id, title, author, description etc...tbl_sectionsection_id, section_desctbl_book_sectionbook_id, section_idDBA is away and I can't figure this out at all...any help gratefullyreceived.
I am trying to retrieve pdf(s) from our server using a UNC path. I created a textbox with the value
=Fields!DisplayLink.Value. I coded my Action property with a constant to test to see how it works in "Jump to URL" \RALEIGHPurchase179104620071017910461001070724.pdf and the pdf is not showing up. Does anyone have any ideas?
Hi I have a table with a column 'value' as nvarchar I want to select some rows from this table that the value of 'value' can be converted to decimal ... it's possible that value of 'Value' contains some other characters other than digits .
On daily basis I need to generate excerpts by mean of Excel. Prior to sql25k I used to play with Enterprise Manager and pick up the name of all the columns for a table very easily doing this
select top 1 f1,f2,-.. from table
name age enric 80
How do I such thing from Sql Management Studio?? It's a silly thing, I know, but it's very useful for me because when I've got those columns then I can do paste them perfectly into .XLS. Otherwise I see forced to write one by one and sometimes tables have more than 60 columns
It's not useful generate a CREATE TABLE script or launch SP_HELP <MYTABLE> because I obtain the name of the columns in vertical no horizontal.
