SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE 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 in
the IN clause, but unfortunately SQL is trying to be too helpful and sorts
the numbers in the IN clause so that the resultset comes back with a TOKENID
order 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.
1) I have a report with many parameters that I want users to be able to pick from. Allow them to pick 1, many or all to build their report dynamically. I'm all set on the TSQL side, but on the Reporting Services side I have to allow each parameter to be null with a default of NULL. In by doing this, the report will auto run, which I do not want to happen. The only resolution I've found thus far was by adding a parameter that does nothing, with a NULL default value. Yet It sticks out like a sore thumb on the report and I want to get rid of it. If I check in "Hidden" in the parameter options, my report errors out stating that the parameter requires a value.
2) Is it possible to have a parameter that has available values from a dataset have a NULL checkbox like those of parameters that do not have available values?
3) Is it possible to add back/forward buttons inside of a report instead of just at the report header by default?
i have a table Animals with column names id and Name eg1 Tiger2 Lion3 Elephant4 Cheetah5 Zebra i want the order to be anything of my chossing when i select from it. eg how could i get it to display results in this order (when i do select * from Animals): CheetahElephantZebraTiger Lion Thanks
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on theĀ roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
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')
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?
Does anybody know of a way to rollback SQL Server 2005 databases back to SQL Server 2000? Is there a way of doing it without resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I want to rollback to SS 2000, which was a lot more stable. I am having several major issues that are affecting my whole company's day-to-day operations and the managers are not happy. Some of the issues include night time batch running very sluggish for no apparent reason. This is a biggest problem because it only occurs once or so a week and causes a disturbance with the daily activities when the night time processing isnt completed on time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I don't believe it is a matter of my application needing to be retuned because if that was the case, then why isn't it running sluggish every night? Also, it's never the same day that the sluggish behavior occurs. If it was occurring on the same night, then I would have something to investigate within our application, but it doesn't. Another issue that I am having involves a night time job that restores a copy of the production database to the Data Warehouse server to be used for updating the data warehouse. Again, most of the time it runs great (~2 1/2 hours), but once or twice a week, it goes stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended state. What's a suspended state and how can I get it out of suspended state? I can find no information about suspended state in BOL. I did a Google and nothing came up. If this suspended state was put in for security reasons, great, but then tell me how I can remove the suspended state. I am also not happy with the fact that I can't get accurate information about the queries that are actively running at that particular moment. In SS 2000, when I noticed high CPU usage on the server, I would run the sp_who2 active stored proc and it would show me all the active thread and how much CPU it was consuming. I would then find the running threads with the highest CPU numbers and investigate the query and see if we could improve it. Now in SS 2005, I get in the same situation and run the sp_who2 stored proc, and there is no smoking gun. All of the active threads are showing very little CPU usage, which I am very suspect of. What the heck happen to sp_who2? I looked at some of the other ways of looking at running processes (i.e... sys.sysprocesses) and they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of years before I upgrade to SS 2005.
The 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
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??
Is 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.
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.
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;
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
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.
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+'
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
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
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.
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.
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 ?
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
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.
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."
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?
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
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?
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
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?
