Passing MS SQL2005 Query Result Into Javascript Function
Mar 20, 2007
I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function.
This is how I retrieve the longitude:
<asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>"
SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)">
</asp:SqlDataSource>
I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this:
var map = new GMap2(document.getElementById("map"));
var lat = <%=lat%>;
var lon = <%=lon%>;
var center = new GLatLng(lat,lon);
map.setCenter(center, 13);
However, lat & long do not contain the retrieved result but rather a useless System.something string.
How do I assign the retrieved results to these variables and port them over to Javascript as required?
Many thanks!
View 14 Replies
ADVERTISEMENT
Sep 6, 2015
I am passing a field value from main report to a parameter in a drill through sub report. But some field values contain 'special characters'. Those field values that contain special characters are not working for drill through. Javascript code to include special characters to pass to sub report parameter?
View 3 Replies
View Related
Aug 24, 2004
I created a function that will return
from OpenDataSource('.....') tablename
where ... is fully populated.
However, I can't figure out how to use it?
For example
select functiona (parameter) as data_src
this returns the "from" statement above
I then try to run
select * data_src
So how do I reference the contents of data_src in the select?
Thanks for any help
View 1 Replies
View Related
Jan 21, 2008
Hello all:
How can I return the result of a SELECT statement from a stored procedure (function)?
CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN
// Is this right??
RETURN SELECT * FROM authors
END
Thanks!
View 12 Replies
View Related
May 20, 2006
I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report. I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report. In the Function Criteria, I enter @SelectDate. In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error. Any idea how I can pass a parameter from a SQL Function to an ADP report?
THANKS!
p.s. I tried searching for other postings on this without any luck.
View 1 Replies
View Related
Mar 17, 2015
I have a Javascript Function provided by our .NET Developer Team.
This code has to be used in a "Text Box" and in Text Box "Action Property" this will be added in the "Go To URL" section.
But Since i have no understanding of Javascript i am not been able to add it.I tried a method of adding this code in the expression section, but it gives error of "End of statement" in Preview section.
View 0 Replies
View Related
Oct 8, 2015
how to call javascript function in SSRS Reports?
View 3 Replies
View Related
Dec 3, 2015
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
View 3 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Aug 2, 2007
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View 3 Replies
View Related
Dec 9, 2007
Hi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
View 8 Replies
View Related
May 2, 2008
What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!
View 5 Replies
View Related
Apr 3, 2006
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View 11 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
Oct 25, 2005
Hi All:I've read a whole slew of posts about creating temp tables using storedproceedures to get the crosstab ability, but I'm wondering if, for thisspecific case, there might be a more efficient way.What makes this question different from the others that I've read isthat I'm using user defined functions, not tables. I actually thinkthat I've got the crosstab thing down, it's just passing the parameterto the 2nd udf that's messing me up.I've got a people table and an address table. Each person can havemultiple addresses. I need to create a dataset that has in each rowthe name of the person, the first address, any second address, and anythird address. I only need to show the first 3, so if there's 100, Ican just ignore the rest.I created a user defined function to return the 1st, 2nd, or 3rdaddress for a given person.udf_ReturnAddress(PersonID,MatchNumber)Another user defined function returns the people that I'm looking for(potential duplicates for a person in this case).udf_ReturnPossibleDupsForAPerson(PersonID)SELECTMain.FoundPersonID, Main.LastName, A1.Street, A2.Street,A3.StreetFROMudf(ReturnPossibleDupsForAPerson(@PersonID) MainTableCROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,1) Adr1) A1CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,2) Adr2) A2CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,3) Add3) A3If, for the first parameter for the return address function, I replaceMain.FoundPersonID with the ID of a person, it works just fine. Iobviously don't want a static id as a parameter - I want to use the IDof the person that the first udf found. Leaving the variableMainTable.PersonID there causes an error in the query designer though.I get "Error in list of function arguments: '.' not recognized.So maybe my problem is that I just don't know how to pass the id of theperson that's found by the first UDF as the parameter of the functionto find the found person's 3 addresses.Any guidance would be greatly appreciated!ThanksKen
View 6 Replies
View Related
Apr 13, 2007
Below is an example of a pivot table from the help in SQL2005.
My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?
I would like to use this for a daily report where the columns would be the dates?
Thanks.
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
View 3 Replies
View Related
Aug 10, 2007
Hello All,
I have a situation where after creating roles and addding users to the roles then when i call Is_Member('MyRole') on my development box it tells me the user is a member of the Role,
But when i detach the database and deploy on a production server and call Is_Member('MyRole') it tells me that the user is not a member of the Role....
I went on to call the system stored procedures, sp_helprolemember to actualy determine if the user is in the 'MyRole' role and the procedure returned a list which i confirmed that the user actually exists in that role..
So am pretty confused when i call Is_Member('MyRole') and it gives me 0 meaning the user is not in the role....
Plss i need help on this thanks a million
KNOW THY SELF
View 8 Replies
View Related
Jul 22, 2007
Hi,I want to pass parameter in to LIKE functionIs there anyway to do thatThanks,Janaka
View 6 Replies
View Related
Mar 10, 2008
I have to pass 3parameters in function,
@begindate,@enddate and @group_type..
but in @group_type should be - state,zipcode and country from salestable
inview :vwstzipcont
create view vwstzipcont
as
select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3
left outer join (select distinct stype,docdate from salesdisttable) s2
on s2.stype = s3.stype
where s2.soptype = 2
go
create function mystzipcont
( @begindate datetime, @enddate datetime, @group_type char(70))
RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from
(
select distinct
vs.docdate,vs.itemnmbr,
p.index From Pubs P
inner join vwstzipcont vs
on vs.index = p.index
Where (vs.docdate between @begindate and @enddate)
and @group_type ) as t
order by t.itemnmbr,t.docdate
end
how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country
can anyone tell me? what condition should be in where clause for this variable?
thanks
View 14 Replies
View Related
Jul 31, 2007
Hello,
I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:
Code Snippet
public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z)
{
//Do Something...
x = ....
y=...
z=...
}
I want to call this code from SQL code:
Code Snippet
create table #Temp (float a,float b)
declare @x float
declare @y float
declare @z float
exec dbo.DoSomething(a,b,@x,@y,@z) ---????
Do someone have an idea?
View 1 Replies
View Related
Jul 23, 2005
Hello,In the project I'm working on, I need to add up all rows data for onecolumn. So, I have this code:$getprodcount = mysql_query("SELECT SUM(qty) FROM purchase");$numproducts=$getprodcount;Later on, I have this code: <?php print $numproducts; ?>What is being printed is Resource id #5...not the numeric value of what issupposed to be a sum. What is wrong? I am assuming taht resource id #5 is apointer of some sorts to the number I am looking for, but how do you getthe actual sum number?Thanks in advance!--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Apr 15, 2008
I have an function executed like that:
select top 1 * from f_Function1('XXX',2099,99) ORDER BY ef DESC
I have about 400 XXX values. I execute function individually for them but normally i get results in individual result sets.
select top 1 * from f_Function1('XXX1',2099,99) ORDER BY ef DESC
select top 1 * from f_Function1('XXX2',2099,99) ORDER BY ef DESC
select top 1 * from f_Function1('XXX3',2099,99) ORDER BY ef DESC
I want all 400 results in one result set. What should i do?
Thanks in advance.
View 5 Replies
View Related
May 24, 2008
Hi all.
I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer.
Now to my problem:
I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables.
Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this.
All ideas are welcome. Thanks.
View 1 Replies
View Related
Aug 29, 2013
I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared @columnname. when i run the following it works fine
select @columnname,0,0,0,0
from temp_prt
it gives me my expected output
mtr_5120,0,0,0,0
mtr_3247,0,0,0,0
mtr_5160,0,0,0,0
etc........
now i want to get the min of each column name like so
select @columnname,min(mtr_5120),0,0,0
from temp_prt ------> this works for min(mtr_5120)
mtr_5120,34.5,0,0,0
now I want to generalize so I try to pass in the variable name and I do the following
select @columnname,min(@columnname),0,0,0
from temp_prt
(the columname (@columnname) exists in the table temp_prt)
but now i get an error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.how can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:
select @columnname,'min(' + @columnname + ')',0,0,0
from temp_prt
but i get the same error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.
View 2 Replies
View Related
Feb 28, 2008
How would one pass a subquery to a function as a parameter?
I have a function, f_Split that returns a table and has two parameters @List varchar(max) and @Delim char(1). I'd like to use it to normalize rows in a table and return the results for use in a report. I tried the following:
SELECT * FROM dbo.f_Split((SELECT Code FROM Codes WHERE ID = 10), '|')
I'm getting a syntax error for the subquery. Could anyone show me the proper way to pass a subquery to a function, even if the function is different from what I have defined here.
Thanks,
DD
View 3 Replies
View Related
Sep 23, 2015
I have an SP that is working, it creates a Union between 3 tables then creates a single table - I am using Visual studio Grid Control to view this by a browser, its working. at the bottom of the SP is a line of code that calls a function, the function queries a View. When I run the SP, SQL returns the data from the Union plus the data from the select statement on the Function. This all works but I can't get the data returned by the function query onto a browser.
I am doing it this was as the parameters in my SP are also used when calling the function, works really well. Is GTotal a return value?
Here is my function:
USE [LOGONs]
GO
/****** Object: UserDefinedFunction [dbo].[FX_AnnaulBudget1] Script Date: 23/09/2015 12:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
...and here is my SP:
USE [LOGONs]
GO
/****** Object: StoredProcedure [dbo].[USP_AnnualBudget] Script Date: 23/09/2015 12:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AnnualBudget]
[Code] .....
View 2 Replies
View Related
Dec 18, 2013
Need to INSERT into a different table the function value results in SELECT from a table for PurchorderNum and QtyOrder and not sure how
ALTER proc [dbo].[spCreateContainerFill]
(@containerID as nvarchar(64),
@lotNum as bigint,
@customerID as int = 1164,
[code]....
View 1 Replies
View Related
Aug 8, 2007
Hello Sir
I am working on a Teacher Evaluation Project. In my database I store results from 1-5 as evaluation indicators. I apply AVG() function on the result column. The result of the query is in integer values (i.e) 4, 3 2 or 5. I want the resutl up to two decimal places. How can i write the query to get the result in decimal form?
Shahbaz Hassan Wasti
View 3 Replies
View Related
Apr 27, 2007
I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)
The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?
View 5 Replies
View Related
Dec 8, 2007
I have created a function with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
WHERE item = @item AND week = @week
to work correctly please?
WHERE item = '@item' AND week = @week
won't work and
WHERE item = @item AND week = @week
won't work.
View 2 Replies
View Related
Dec 26, 2007
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.
There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint
There is a WETPAINT table:
ItemNo int,
Color varchar(20)
There is a DRYPAINT table:
ItemNo int,
Color varchar(20)
Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType
So I created a table function called getTable:
CREATE FUNCTION [dbo].[gettable]
(
@ItemType int = 1
)
RETURNS
@thistable TABLE
(
Color varchar(20)
)
AS
BEGIN
if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT
RETURN
END
This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:
SELECT MAIN.ItemNo, a.Color
FROM MAIN
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo
Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.
Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.
PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.
View 2 Replies
View Related
Jul 20, 2005
Hello,Is it possible? Can I select a specific record of the cursor to besent to a seperate function to do all the computations etc.?Regards,VS
View 5 Replies
View Related