I would like to test following DMX, but it seems like we cannot use @param in DMX. If i indeed need what other tricks can avoid this constraint?
Declare @HCVS_MemberId nvarchar(15);
INSERT INTO test
(HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse)
OPENQUERY(Healthcare,
'SELECT TimeIndex, Quantity
FROM v_VitalSignForecast
WHERE HCVS_MemberId=@HCVS_MemberId AND HCVS_MeasureDate>=@From AND HCVS_MeasureDate<=@To')
Thanks,
Ricky.
is there a way in t-sql to pass a db name as a parameter, so that select * from [@passedDB].[dbo].[tableName] would work? Without dynamically building and executing the sql statement?
View 1 Replies View RelatedThe following stored procedure sets a value for the @@RowCount global variable.
How do I make use of it in the Data Access Layer?
When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @@RowCount to a Label.Text
What should I do?ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
@selectDate datetime
,@selectCountry Int
AS
SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_cmc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry
ORDER BY configname
SELECT @@RowCount As numberRows
GO
I'm working with a dataset something like this:
TypeID Sales($)
------ -------
1 123.45
1 47.98
2 9.21
3 87.23
3 99.88
4 123.43
And a multivalued parameter that lets the user select which TypeIDs specifically he wants to see:
ParamID ParamValue
1 Q1
2 Q2
3 Q3
4 Q4
And in my Report, I have data showing up something like this:
CountofAllSales: 6
SumOfAllSales: 491.18
CountofCustomSales: (count of sales with type specified in parameter)
SumOfCustomSales: (sum of sales with type specified in parameter)
The count and sum of custom sales should show -ONLY- the numbers from the TypeIDs selected in the multi-value parameter. But the CountAll and SumAll show everything, regardless. This is where I run into problems. I can't seem to find an "in" clause in the SSRS expressions. If the TypeID parameter was single value, I could write something like this
Expression for CountOfCustomSales:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value, 1, 0))
However, since its multi-valued, that won't work. You'd have to write something like:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(0), 1, 0)) +
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(1), 1, 0)) +
....
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(length), 1, 0))
And obviously this doesn't work when you don't know exactly how many elements are going to be selected.
What would be ideal would be something like an "in" clause, but I can't find any such functionality or think how to write my own function:
=SUM(iif(Fields!TypeID.Value in
Parameters!TypeID.Values, 1, 0))
Short of modifying the StoredProc itself (and for me, that means red tape. :( :( ) can anyone think of a way to count/sum only the values specified in an MVP??
Hi AllMy query is as follows:SELECT STRINGTEXT, TOKENIDFROM WEBSTRINGSWHERE TOKENID IN (6,20,234,19,32,4,800,177)All I want is my resultset to come back in the order that I have defined inthe IN clause, but unfortunately SQL is trying to be too helpful and sortsthe numbers in the IN clause so that the resultset comes back with a TOKENIDorder of 4,6,19,20,32,177,234,800.I don't want this bloody order I want 6,20,234,19,32,4,800,177!!Sorry for my rant, but its got my hot under the collar.Is there anyway round this?ThanksYobbo
View 3 Replies View RelatedIs there a limit to how many items you can have in a multi value param list. I have 20 items in an activity type param and when I chose "Select All" and run the report, it doesn't return. I opened profiler and picked up the following statement sent to SQL:
exec sp_executesql N'SELECT de.employeenumber as Employee_Id,de.employeelastname + '', '' + de.employeefirstname + '', '' + case when de.employeemiddlename=''N/A'' then '''' else de.employeemiddlename end as Employee_Name,da.activitytype as Activity_Type,da.activitycode as Activity_Code,da.activityname as Activity_Name,dd.fulldate as Completion_Date,das.currentattemptstatus as Current_Attempt,das.successstatus as Success_Status,das.completionstatus as Completion_Status,das.registrationstatus as Registration_Status,fa.score as Score,fa.dimgradeid as GradeId
FROM dimemployee de inner join factattempt fa on (de.dimemployeeid = fa.dimemployeeid) inner join dimattemptstatus das on (fa.dimattemptstatusid = das.dimattemptstatusid) inner join dimactivity da on (fa.dimactivityid = da.dimactivityid) inner join dimdate dd on (fa.attemptenddateid = dd.dimdateid)
WHERE
de.employeenumber = (@EmployeeId) and da.activitytype in (N''CBT'',N''Course'',N''Dart'',N''Discuss'',N''Document'',N''Evaluator'',N''JPM'',N''Lesson Plan'',N''Module'',N''Observation'',N''Procedure'',N''PSG'',N''Qual'',N''Read'',N''Reference'',N''Session'',N''Sign-off'',N''Simulator'',N''Task'',N''Trainer'')
and das.isvalidattempt = ''Yes'' and ((@LastAttempt=1 and das.currentattemptstatus = ''Yes'') or (@LastAttempt=0 and das.currentattemptstatus in (''Yes'',''No''))) and das.lmsmartcompletionstatus in (@CompletionStatus) and (dd.fulldate >= @StartDt or @StartDt is NULL) and (dd.fulldate <= @EndDt or @EndDt is NULL)
ORDER BY de.employeefirstname + '' '' + de.employeelastname,da.activitytype,da.activityname,dd.fulldate',N'@EmployeeId int,@LastAttempt nvarchar(1),@CompletionStatus nvarchar(10),@StartDt nvarchar(4000),@EndDt nvarchar(4000)',@EmployeeId=108001,@LastAttempt=N'1',@CompletionStatus=N'Successful',@StartDt=NULL,@EndDt=NULL
If I take any one of the items in "da.activitytype in" out (for example....N''Trainer'')...the query is fine. But if I run it as is, it never returns. I have also reduced the number of items in the MVP to 19 and "Select All" and it runs fine...it just bombs when I have 20 and Select All. Any ideas?
Hello....
I am trying for several weeks to figure out how can I create a stored procedure with parameters that returns all rows from a column (even then Nulls) when the parameter value is not set (or is '%', or anything tha might be. In a few words, in case the user hasn't input any data).
I 've created a WHERE clause that goes like this: WHERE fieldName LIKE @param + N'%' OR IS NULL
Well this query returns all rows in case the user hasn't input data but if the user inputs data it returns the correct rows but it is also returns null fields.
Thanks a lot in advance!
Manolo....
Is it possible to take a report input parameter and display it at the top of the report? StartDate/EndDate are two input params for my report that would be nice to display at the top.
thanks
(Reporting Services 2005)
I've got a stored procedure and one of the parameters is a DateTime. But no matter what I do to the string that's passed into the form for that field, it doesn't like the format. Here's my code: SqlConnection conn = new SqlConnection(KPFData.getConnectionString());
SqlCommand cmd = new SqlCommand("KPFSearchName", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@DOB", SqlDbType.SmallDateTime);
param.Direction = dir;
param.Value = txtDOB.Text;
// also have tried this:
param.Value = Convert.ToDateTime(txtDOB.Text);
// and
param.Value = Convert.ToDateTime(txtDOB.Text).ToShortDateString;
No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines. I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??Thanks for your help!!eddie
Hello,
I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.
How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.
Any tips highly appreciated! If I'm not being clear I'll gladly post more details.
Thanks,
Noc
PS SQL 2000, ASP.NET 1.1, VS 2003.
I have a uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!! i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'
order by '+ @SortExpression +''
exec sp_executesql @sql
Hello everyone,
I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.
I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.
create procedure ShowPopStyle
@style varchar(30),
@artistname varchar(30) output
as
select @artistname = artistname
from artists
where style = @style
go
Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?
declare @returnname varchar(30) -- variable for the output from the procedure
exec showpopstyle 'Pop', @artistname = @returnname output
print @returnname
Thanks
Kevin
Hi all,
I also have the same error, I am trying to do two things in my Stored Proc.
1) - Insert a parent record.
2) - Insert 1 or many records in the child table using the parent ID
The child records are being passed as a XML param.
I am also getting the
'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
error when I try to create the procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name String ,
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end
SELECT SCOPE_IDENTITY = @NewID
INSERT INTO Child
(ParentID,
name)
Values
((
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
))
SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end
commit transaction
return @@error
GO
I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result.
Here is my code
create procedure sp_Select_ProfileNames
@NameSearch varchar(50)
as
select ProfileFirstName +' '+ ProfileLastName as ProfileName
from Profiles
where ProfileLastName like '%@NameSearch%';
When i change this line:
where ProfileLastName like '%@NameSearch%';
to
where ProfileLastName like '%Bil%';
I see names starting with Bil, but when i enter Bil as param, I don't get anything.
What's wrong here ?
I have a rs report with a parameter(named Site) that is defined as multi-value. What I am trying to do is call the report from an html form with the parameter populated with a comma seperated list of values. The html code is a minimally modified version of the code found in ch. 11 of the hitchhikers guide(code included below after the solid line). If I don't select any items, then execution is fine.
View 6 Replies View RelatedHello,
I have a VB.NET stored procedure as below:
Code Snippet
Partial Public Class StoredProcedures
Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer)
'here I perform update statement using "paramInOut" passed form calling code
.......
'then I return value to the calling code
paramInOut = 555
End Sub
End Class
Calling code specifies a parameter like this:
Code Snippet
Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int)
param.Direction = ParameterDirection.InputOutput
param.Value = 999
cmd.Parameters.Add(param)
When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ?
Any help would be appreciated.
Thanks a lot,Fly.
Dear friends,
How can insert into a temp table 2 parameters. If it's only on, I dont have problem, but if I have 2 there is a error... The problem is the sintax of the execute SP ... thanks
ALTER PROCEDURE [dbo].[GD_SP_FACTURAS_TOTAL2]
AS
CREATE TABLE #TotaisDir
(
DirTotal bigint,
DirNome nvarchar(10)
)
DECLARE @DIR nvarchar(10)
DECLARE @Return_Status bigint
DECLARE LINHAS_CURSOR CURSOR FOR SELECT DIR_NOME FROM Direccao
OPEN LINHAS_CURSOR
FETCH NEXT FROM LINHAS_CURSOR INTO @DIR
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #TotaisDir (DirNome,DirTotal) VALUES
('ww', EXECUTE dbo.GD_SP_FACTURA_ValorTotal @DIR)
FETCH NEXT FROM LINHAS_CURSOR INTO @DIR
END
CLOSE LINHAS_CURSOR
DEALLOCATE LINHAS_CURSOR
SELECT DirTotal FROM #TotaisDir
SELECT SUM(DirTotal) As SOMATOTAL FROM #TotaisDir
ERROR:
Msg 156, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21
Incorrect syntax near the keyword 'EXECUTE'.
Msg 102, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21
Incorrect syntax near ')'.
Reading about SQL Injection attacks I came across this example:
SSN="172-32-9999';DROP DATABASE pubs --"
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"
One remedy given was a parameterized query as follows:
Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)
Why does this parameter which is defined as a varchar solve the problem? It's defined as a varchar, basically a string. Why is the result different in the solution? How is the query string in the second sample different from the one in the first? A simple question I know, but I've been wondering.
Thanks
Mike Thomas
Hi,
My package takes 1 global variable and I have set up jobs to run this SSIS with different variable values.
I select the package using the UI provided via the job set up screen. The job step type is Operating system (CmdExec).
When package are run by job, it seems that the job can not find the package. But, how can it not find the package when the package was selected from a list that the UI displayed? The error is below.
"Executed as user: <<LOGGEDIN USER(with admin right)>>. The process could not be created for step 1 of job 0xFECED6C09CC650489084E91C2FCF52FB (reason: The system cannot find the file specified). The step failed."
Thanks a lot in advance.
I want to pass a single char to a query and use that to set two flags.
(
@ID int,
@AssessedID int,
@CompetencyID int,
@Status char,
@Creator int
)
AS
UPDATE P4_Assessment
SET P4_Cancelled_f = (@Status = ('C')),
P4_Competent_f = (@Status = ('P')),
P4_Date = getdate(),
P4_Creator = @Creator
WHERE P4_ID = @ID
I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't.
This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))
Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?
TIA
Hi,
I'm exporting some data from a table to fixed-width file using SSIS.
The query inside the package is:
SELECT col1, col3, col6 FROM Table1
WHERE code = 5 AND status = 'S'
The 'code' value will be passed from the front-end.
How can I make the query dynamically take the 'code' value and export to a file?
If a user selects 'code' as 7 , then the query should change to
SELECT col1, col3, col6 FROM Table1
WHERE code = 7 AND status = 'S'
and with this it has to export to a file.
Is this possible? If so, any suggestions on how to do it? !!!
Thanks,
Siva.
Please help me on this one.
I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc
CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG
When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null
I try to return it with the following code (got the code from a friend)
Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function
PLEASE HELP.
Regards,
Sander
The proc below does two queries that are functionally identical. The only difference is that one LIKE 'foo%', and the other uses LIKE @searchText, where @searchText = 'foo%'.
But the first does an index seek, and the second does an index scan -- and it makes a big difference in performance. (Timing stats are below.)
How can I make the second query seek instead of scanning?
CREATE PROCEDURE test_like
@searchText nvarchar(64)
AS
-- Straight literal string search
SELECT companyId
FROM companies
WHERE searchbrand LIKE 'foo%'
-- With param
SELECT companyId
FROM companies
WHERE searchbrand LIKE @searchText
OPTION(OPTIMIZE FOR(@searchText = 'foo%'))
GO
EXEC test_like 'foo%'
-- Query 1:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
-- Query 2:
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 40 ms.
This is my SProc:
CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
/* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT
AS
SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount
GO
And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue
Dim con As New SqlClient.SqlConnection
con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int)
myParam.Direction = ParameterDirection.Output
myCommand.Parameters.Add(myParam)
myCommand.Connection = con
con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected
numberParts.Text = rowCount.ToString
con.Close()
End Sub
What should I fix?
I'm developing a web app using VS2005. I have a webpage with panel containing a gridview populated by a SQLdatasource. The SQLdatasource in turn is populated by a stored procedure that can take up to 5 parameters. The user types in up to 5 separate words (searchterms) in a text box which are then parsed and passed to the stored proc in the datasource which performs a fulltext search. The gridview then becomes visible. My problem is that unless the user types in 5 searchterms (no less), the gridview returns zero rows. 5 searchterms returns the proper results. Somehow, I need to be able to pass in null or empty values for unneeded parameters.
I've tested the stored procedure in Query Analyzer and from within the SQLdatasource configuration (using Test Query) using 0 up to 5 parameters and it works fine, so that's not my problem. Here's the code that runs after the user types in their search term(s) and presses a button:Public Sub FTSearch_Command(ByVal sender As Object, ByVal e As CommandEventArgs) Handles btnFullText.Command
Dim x As Integer
pnlFullText.Visible = Falsefiltertext = Replace(txtSearchTxt.Text, "'", "''")
If Not filtertext Is Nothing Then
filtertext = filtertext.Trim
Else
Return
End IfDim arrayString() As String = filtertext.Split(" ")
Dim length As Integer = arrayString.LengthFor x = 0 To (length - 1)
If Not arrayString(x) Is Nothing ThenSelect Case x
Case 0 : lblFTParm1.Text = arrayString(0)Case 1 : lblFTParm2.Text = arrayString(1)
Case 2 : lblFTParm3.Text = arrayString(2)Case 3 : lblFTParm4.Text = arrayString(3)
Case 4 : lblFTParm5.Text = arrayString(4)
End Select
End If
Next
pnlFullText.Visible = "True"
End Sub
Any ideas?
Thanks in advance.
I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently:
ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT
AS
SET NOCOUNT ON ;
select @vcOutputMsg = 'asdf'
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
//handle error on return
string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value;
}
On the page source side, the params are defined declaratively:
<UpdateParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" />
</UpdateParameters>
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"
PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?
hello,
i have a table in witch i store the favorites pictures for each user, like so:
FavID PictureID UserId (uniqueidentifer)
users can add a picture at favorites by clicking a button from the picture table ("Add to favorites")
i need a stored procedure witch uses two input params (userId and PictureID), witch should return 1 if the relations between the PictureID and UserId already exists in the table(avoid having same relation again) or 0 if not (and i'll use that result to make the button enabled or disabled)
how can i do that?
if you didn't understood, please tell me
thanks
While debugging a stored proc in Sql Query Analyser i have to pass value to stored proc param which is of datatype datetime
i passed it as '03/31/2003'
it's giving error "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification"
if i make it null it's working fine
pls suggest which date format value supposed to supply for datetime param to run debugging successfully
I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)
AS
INSERT INTO tbLabel
(LabelType
, Logo
, Field01)
VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)
How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...
Zack
I'm running a query on Oracle using OPENQUERY.
I need to pass a parameter.
Which means I'm having to use dynamic sql.
In turn this requires a ##table due to the connection issue of using EXEC.
There is surely a better way of doing this, but what?
is it true that I will not be able to use the returned value from an sp that is called on every row from an oledb command transformation? I see all kinds of complaints on the web but cant determine if this would be a waste of time. I'd like to append the returned value (which is calculated and cannot be joined in the buffer) to the data on its way out of the transformation.
View 3 Replies View RelatedI am getting this error when upgraded to SQL Server 2012. Earlier the same report(.rdl) just working fine on SQL Server 2008 R2.
Message = System.Web.Services.Protocols.SoapException: The value of parameter 'param' is not valid. ---> Microsoft.ReportingServices. Diagnostics. Utilities.InvalidParameterException: The value of parameter 'param' is not valid. ---> System.UriFormatException: Invalid URI: The Uri string is too long.
Is there anything changed in SQL Server 2012 regarding this.
Hi !!!
I have to use 2 web services !
One (WebServices A) which contains methods I need to call, and the other (WebServices B) which contains a method who returns a autenticationCookie, that I have to use in parameter for call method from WebServices A ?
It is clear ? I hope...
So my question is how I can do ?
I know I can create few dataSet, but how can I pass a data from on dataSet (from my WebServices B) to the query on an other dataSet (from my WebServices A) ?
Thank your help !