Parameters.AddWithValue Vs. Parameters.Add
Jan 2, 2008
Hello all,
Given:
string commandText = "Categories_Delete";
SqlCommand myCommand = new SqlCommand(commandText, connection);
myCommand.CommandType = CommandType.StoredProcedure;
Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder]
It seems that ADO.NET makes an implicit conversion to the valid type. If this is correct then I can move on fat dumb and happy. Anyone have any good insight?
Thanks,
View 11 Replies
ADVERTISEMENT
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Mar 7, 2007
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
View 3 Replies
View Related
Apr 18, 2008
I’m getting the error messages: Must declare the scalar variable "@CustomerName".Must declare the scalar variable "@CustomerEmail". When using the code:sqlcommand = "UPDATE Transactions SET CustomerName=@CustomerName WHERE transid='" + thetransid.ToString().Trim() + "' ";
sqlcommand += "UPDATE Transactions SET CustomerEmail=@CustomerEmail WHERE transid='" + thetransid.ToString().Trim() + "' ";
cmd.Parameters.Add("@CustomerName", CustomerNameTextBox.Text);
cmd.Parameters.Add("@CustomerEmail", CustomerEmailTextBox.Text);
cmd = new SqlCommand(sqlcommand, conn);
cmd.ExecuteNonQuery();
conn.Close();
View 8 Replies
View Related
Sep 2, 2007
HI
Can someone explain me in detail when we use <asp:parameter> and when we use <asp:controlparameter>
View 1 Replies
View Related
Sep 6, 2007
Hello Dears
I Have an stored procedure like this :ALTER proc [dbo].[gl_voucher_type_insert]
@company_code varchar(3),@source_code varchar(4)
,@voucher_code varchar(4),@desc_a nvarchar(50)
,@desc_l nvarchar(50)
,@voucher_form numeric(2) ,@voucher_prefix varchar(4)
,@voucher_start numeric(8)=null
asDeclare @PrefixCount as int
set @PrefixCount=isnull((select count(*) from gl_voucher_type
where company_code=@company_code and
voucher_code=@voucher_code),0)
if @PrefixCount=0
begin
insert into gl_voucher_type(company_code,source_code,voucher_code,voucher_desc_a,voucher_desc_l,voucher_form,voucher_prefix,voucher_start)
values(@company_code,@source_code,@voucher_code,@desc_a,@desc_l,@voucher_form,@voucher_prefix,@voucher_start)
end
return @PrefixCount
ok i need in my asp page get the @ prefixCount value to make some checking on it how can i do that
please help me as soon as possible
with my best regard
khalil T.Hamad
View 3 Replies
View Related
Sep 27, 2007
CREATE PROC xxx
@user VARCHAR(15),
@rank varCHAR(10) AS
DECLARE @sql VARCHAR(100)
SET @sql = 'SELECT ' + @user + ' FROM usertable where grade = ' + @rank
EXEC (@sql)
GO
when i execute this proc without where condintion its working, but when i use where condition its dispalyin invalid column name with the name im passing
eg.
xxx admin,aB
WHEN I TRY TO EXECUTE PROC WITH ABOVE STAT, ITS DIAPLAYIN ERROR AS "INVALID COLUMN NAME ab
but
xxx admin," ' aB ' "
when i try like this its giving result.
how can i avoid second method of executin the proc and use first method for the sake of passing value from frontend
View 3 Replies
View Related
Jun 21, 2008
Hi,
The following code doesnt work. I am trying to get data from a
table according to a querystring. Id like the data in the columns
'hello' and 'hello2' to be meta name and content. But it says
The name 'hello' does not exist in the current context
command.CommandText =
"SELECT hello, hello2 FROM table WHERE ID=@ID";
command.Parameters.AddWithValue("@ID",
Request.QueryString["ID"]);
command.ExecuteNonQuery();
HtmlMeta meta = new HtmlMeta();
meta.Name = "Description";
meta.Content = "first" + hello;
Page.Title = "first" + hello2;
Header.Controls.Add(meta);
Thanks
View 5 Replies
View Related
Jan 14, 2004
I need to add parameters to my SQL string, like Where [EndDate] >= @HStart AND [EndDate] <= @HEnd, I tried to Dim variables but it caused an error. Can anyone help me with this?
Thank You,
Sub BindDataCurrent()
Where [EndDate] >= @HStart AND [EndDate] <= @HEnd"
'MyCommand.Parameters.Add("@HStart", SqlDbType.VarChar, 80).Value = HistoryStartText.Text
'MyCommand.Parameters.Add("@HEnd", SqlDbType.VarChar, 80).Value = HistoryEndText.Text
ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyConnection = New SqlConnection(ConnectStr)
Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= @HStart AND [EndDate] <= @HEnd"
Dim DA As SqlDataAdapter = New SqlDataAdapter(SQL, MyConnection)
Dim DS As New DataSet
DA.Fill(DS, "tblCampaigns")
MyEditDataGridCurrent.DataSource = DS.Tables("tblCampaigns").DefaultView
MyEditDataGridCurrent.DataBind()
End Sub
View 1 Replies
View Related
Nov 1, 2004
Hi,
I'm having a bit of trouble with SQL Parameters. I can't seem to define the type when creating the parameters.
Here's what I've done:
I have a function that processes my request and returns a datatable:
Public Function Grab_Data(querystring asn string, params() as SqlParameter) as DataTable
... dims all the necessary variables
Try
...creates the connection and command
Dim p as SqlParameter
For each p in params
p = command.parameters.add(p)
p.direction = parameterdirection.input
Next
...opens connection, creates dataset and fills it
Finall
....disposes connection and command
End Try
Return datatable
End Function
I call this function by:
...dim the necessary variables
querystring = "SELECT * FROM Tbl_Users WHERE Joined>=@date"
dataTable = Grab_Data(querystring, New SQLParameter("@date", DateTime.Today))
My code works fine and I get the results that I want, but no where in my code is the type of the parameter defined.
I tried calling the function like:
Grab_Data(querystring, New SQLParameter("@date", SQLDBType.DateTime, DateTime.Today))
But when I do this, I get an error saying that no value is assigned to @date.
Can anyone tell me what I need to modify so that I can pass the type of the parameter to the function?
The reason why I am not explicity defining the parameters in the function is because I can reusing the function numerous times throughout my code. So some calls have three parameters passed to it and some and none.
Thanks.
View 4 Replies
View Related
Dec 18, 2001
Hi,
I'm new in sql. How can i set the current value of parameter "MAX DEGREE OF PARALLELISM" to new value?
Thank you
Rey
View 1 Replies
View Related
Oct 27, 2001
Hi,
I'm trying to create a DTS transform (sql2k) with a paramterized query like:
SELECT columns
FROM table
WHERE column1 IN ?
When I try to preview this I get an error "no value given for one or more required parameters". The global variable is set and I can see the value.
Any ideas? I've been struggling with this for days with no success.
Thanks,
View 1 Replies
View Related
Dec 3, 2005
Hi all, I know I could do something like this in SQL:
select customers.name
from customers
where customers.name = request.form("txtname")
but my question is, can I have a user pick the operator (such as =,>,<,>=,<=) from a dropdown box and pass it the the sql statement as a parameter such as:
select customers.name
from customers
where customers.name request.form("txtoperator") request.form("txtname")
Please help!
Thanks!
View 2 Replies
View Related
Apr 11, 2008
Hi all, I am using classic ASP and SQL Server 2005. Can I use parameters like @whatever in my asp code or are they for stored procedures exclusively? I am trying to change the output of a request.form into a paramater so I can prevent SQL injection I am aware of using trim to counter this but I wasn't sure of the best practice.
I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.
View 4 Replies
View Related
Apr 24, 2008
I have a set of date parameters in reporting services which are defaulted to 3/01/08. How can I make them to the current month so that six months from now they are not still reading 3/01/08
View 5 Replies
View Related
Apr 17, 2006
I'm trying to do this:
ALTER PROCEDURE dbo.SkillSearch
(
@skillname char(255)
)
AS
SET NOCOUNT ON
SELECT * FROM Skill WHERE SkillName LIKE @skillname
However, when I run dbo.skillsearch 'ph%'
I get an empty set, while dbo.skillsearch 'php' returns the results expected.
I am therefore assuming that I can't use a parameter for a LIKE clause with any wildcards?
Is there any way around this other than manually building the SQL statement in the SP and then executing it? I'd obviously prefer to not have to do it that way for all the SQL Injections and related reasons.
Wouldn't this also kill my query optimization benefits, manually building the statement each time?
Thanks,
- Brian
View 13 Replies
View Related
Jan 24, 2007
I am trying to use the URL to pass a parameter to a report. I have tried several ways to make this happen with no effect. My report is using a sproc that has a int parameter. I have tried make a list of partial parameters and passing the parameter I want through the URL. I hide the prompt. I put NULLs and Blanks allowed. I have created a linked report then hid the prompt. I used parameters=false instruction. Here is the basic URL http://reportsrv/Reports/Pages/Report.aspx?ItemPath=%2fActiveTasks%2fProject+Details&rs:Command=Render&ProjectID=56.
Please Help it is driving me crazy.
View 1 Replies
View Related
Nov 5, 2007
hi i need to know what's the syntax in reporting services query in a where statement to get values where comment card name like '%'&@RestaurantName&'%'. This syntax is giving me an error. i need to get the rows where the comment card name contains the restaurant name got from the parameter. can anyone give me the right syntax please?
View 1 Replies
View Related
Jan 18, 2007
I have a table of 25-30 million properties, from which are retrieved~150 centered on a point, based on the parameters -- coordinates,property type and date of transaction. There's an SP (also implementedas a function returning a table) to return the desired records.This look-up takes the most time in the C# program that calls it, andshould be optimized. It was suggested that instead of having an SP onthe server, each time the program should create an SP that is the same,however without any parameters -- with the values hard-coded. Thenexecute it, and drop it. This way, the execution plan will becustomized for the specific parameters. I tried it and it turns outthe suggested method is noticeably faster, even compared to recompilingan SP every time. I was wondering if there is a way to get equivalentperformance out of an SP or UDF that has parameters, or is thisapproach necessarily going to be less optimized than hard-codednon-parameters.Thanks,Jim
View 2 Replies
View Related
Aug 10, 2006
Hello,
i am new to ssrs and am trying to generate a report with 4 parameters. 2 of which are dates. The other 2 are drop downlists. Now the report works fine when i enter all 4 parameters. But in some cases i want to leave one of the parameters unentered . It doesnt all me to do that. Gives a error saying i need to enter the parameter. How do u get aroud this issue ?
I have seperate dataset for this parameters list and am using a where clause in my main query.
Can anyone please help me out with this?
Thx
Ashish
View 5 Replies
View Related
May 8, 2006
The following query is from the SQL 2005 documentation:
SELECT ProductNumber, wholesalePrice,Vendors.VendorID, VendName
FROM Product_Vendors JOIN Vendors
ON (Product_Vendors.VendorID = Vendors.VendorID)
WHERE WholesalePrice > $100
AND VendName LIKE N'L%'
GO
Can anyone enlighten me on the N in front of the 'L%'?
TIA
nickedw
View 1 Replies
View Related
May 29, 2007
Hi, I'm trying to generate a report on a DMX query I have created and I would like to pass a parameter into a DMX query containing an OPENQUERY statement. Currently I just do the following:
...OPENQUERY([data source],'SELECT ''@CompanyName'' AS [CompanyName]')...
It does not pass the parameter through though... my query always returns no results! Could anyone help please? Thanks!
View 3 Replies
View Related
Nov 23, 2007
I am trying to call a query based on a little logic....I.E
if dateRange is null then
call this querey
else
call this other query
ok simple enough right.....right.....
My issues come in because i am not sure if the varibles are like they are within some code like do they have some sort of scope that they live in becuase i have this query but all my parameters say they still need to be declared but i declared them at the top....any help on how i need to structure this query would be great...
Code Block
CREATE PROCEDURE GetUsersExpenseReport
@LoginID nvarchar(50)
@DateRangeFrom datetime,
@DateRangeTo datetime
as
Begin
if @DateRangeFrom is null And @DateRangeTo is null
Begin
Select OPS_TimeCards.TimeCardID, OPS_TimeCards.DateCreated,OPS_Employees.LoginID, OPS_Employees.FirstName + ' ' + OPS_Employees.LastName As FullName, OPS_TimeCardExpenses.ExpenseAmount
From OPS_TimeCards
Inner Join OPS_Employees On OPS_Employees.EmployeeID = OPS_TimeCards.EmployeeID
Inner Join OPS_TimeCardExpenses On OPS_TimeCards.TimeCardID = OPS_TimeCardExpenses.TimeCardID
Where OPS_Employees.LoginID = @LoginID
order by OPS_TimeCards.DateCreated desc
else
Select OPS_TimeCards.TimeCardID, OPS_TimeCards.DateCreated,OPS_Employees.LoginID, OPS_Employees.FirstName + ' ' + OPS_Employees.LastName As FullName, OPS_TimeCardExpenses.ExpenseAmount
From OPS_TimeCards
Inner Join OPS_Employees On OPS_Employees.EmployeeID = OPS_TimeCards.EmployeeID
Inner Join OPS_TimeCardExpenses On OPS_TimeCards.TimeCardID = OPS_TimeCardExpenses.TimeCardID
Where OPS_Employees.LoginID = @LoginID And OPS_TimeCards.DateCreated Between @DateRangeFrom AND @DateRangeTo
order by OPS_TimeCards.DateCreated desc
End
End
go
View 6 Replies
View Related
Apr 4, 2007
Hello,
I deployed my report on the Reporting services server, but i want to create a script that open my report with specific parameters.
I tryed this url but i don't know why my parameters didn't work.
http://******/ReportServer?/Statutaire/Bilan&rs:annee_bilan=2006&rs:mois_bilan=4&rs:
Entite="All"&rs:Command=Render
****=myserver
annee_bilan, mois_bilan and entite are parameters in my report.
Thank you
View 1 Replies
View Related
Jun 12, 2007
I know that you can amend the URL of a report and set parameters that way but is there a way you can do the reverse? I need users to be able to save reports to their favourites while retaining the currently selected parameters. Therefore I need the URL to display the parameters without the users having to manually type them in. Is there a way of configuring this?
View 11 Replies
View Related
Feb 8, 2008
Hi All
Beginner's question:
I have a dataset which provides two columns (Id and Name) and I'm using it to drive a dropdown list (parameter)
I want the user to see the Name but I want the Id to be used as the parameter in the subsequent query that provides data to the report.
How do I manage that?
Thanks
Pete
View 5 Replies
View Related
May 19, 2006
SELECT CodiceCliente, RagioneSociale, Articolo, Comma, descrizione, Area, dtDeroga, dtFineDeroga, SuContratto, Parametri
FROM MasterDeroghe
WHERE Articolo = (?CodArt)
I want to pass a parameter at a report of reporting services with this query but this query don't conceid it.
why?
what's wrong?
My database is access
View 7 Replies
View Related
Mar 12, 2007
Probably a pretty easy question: How do I implement 2 parameters in a OR situation? I want the users to be able to either choose a value for A OR a value for B, i.e. select the product name OR the product code. I have 3 datasets defined, 1 for each parameter and 1 for the report output. The last one has ...WHERE A=@A or B = @B. When testing, it seems to require both. I want them to be mutually exclusive, the user can only pick one.
View 4 Replies
View Related
May 30, 2007
Connected to AS/400 server through ODBC. Can submit queries, just built a matrix report, looks good.
However I want to incorporate a Parameter and have tried prefacing the parameter COSTCENTER with everything under the sun:
@COSTCENTER (SQL Server version of parameter declaration)
:COSTCENTER (Oralce version of parameter declaration)
%COSTCENTER
#COSTCENTER
etc. etc.
I keep getting the same error:
Error in WHERE clause near '@'. Unable to parse query text.
My inquiries are as follows:
1. Can you use parameters with an AS/400 connection?
2. If yes, howshould the parameter be declared?
I searched BOL, GOOGLE, so far nothing...
View 1 Replies
View Related
Dec 14, 2007
Is it possible to edit the parameter layout generated by reporting services 2005 - i have drop down lists which are too narrow (they do not widen to fit the values so one has to scroll left and right in the drop down list to see whole value).
It automatically wides if it is not a multi value param.
any advice appreciated,..
Des
View 3 Replies
View Related
Oct 6, 2006
I have a problem in SQL Reporting Services.
I have several multi-value parametersI
When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:
"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "
thanks
View 4 Replies
View Related
