Is there any way to use a parameter as a databas or tabel name? I like to use a parameter where I now use "PulpQMain" I like to read the database name from a tabel in runtime so the sp works even if a change name on the database.
Thanks /Jörgen
create table #Stat_table
(
StopID varchar(50),
Location varchar(50),
StoppedAtTime datetime,
StartedUpTime datetime,
Hours real,
Seconds real,
MachineType varchar(50),
MachineName varchar(50),
ReasonForStop varchar(50),
ActionTaken varchar(50),
Signature varchar(50),
StopType varchar(50)
)
insert into #Stat_table
select * from PulpQMain..dtStopStatistics where StoppedAtTime between @FromTime and @ToTime
create table #DTR_table
Is this possible/how is it done? I tried just going through and putting a @DBName.dbo.Table1 in the FROM clause but it said invalid object name @DBName ...
I also tried this in a stored proc, but it didn't work either: quote: EXEC('USE ' + @DBName) *SELECT STATEMENT HERE*
I am working on my first project, and using Visual Web Developer 2005, and I am having one heck of a time with one of my pages. I am making a makeshift shoppingcart program for one of my classes, and I can't get the checkout page to work. All my other pages work just fine, and this is the only one that won't work. I am hoping someone is able to help meI am attaching what I think is causing the problem, hopefully someone might see something amiss: These are my AccessDataSources<asp:AccessDataSource ID="adsAddNewOrder" runat="server" DataFile="~/App_Data/GoalieStore.mdb" InsertCommand="INSERT INTO Orders(OrderID,CustomerID,SalesTaxRate,Shipping) VALUES (?,?,0.14,25)" SelectCommand="SELECT OrderID, CustomerID FROM Orders"></asp:AccessDataSource><asp:AccessDataSource ID="adsAddNewOrderDetails" runat="server" DataFile="~/App_Data/GoalieStore.mdb"InsertCommand="INSERT INTO OrderDetails(ProductID,OrderID,Quantity) SELECT ProductID,OrderID,Quantity FROM Cart WHERE (CustomerID = ?)" SelectCommand="SELECT ProductID, OrderID FROM OrderDetails"></asp:AccessDataSource><asp:AccessDataSource ID="adsClearCart" runat="server" DataFile="~/App_Data/GoalieStore.mdb" DeleteCommand="DELETE * FROM Cart WHERE (CustomerID = ?)" SelectCommand="SELECT Cart.* FROM Cart"></asp:AccessDataSource>and I have VB.Net code as follows: Protected Sub ClearCart() 'Create a Delete Query Parameter using a Session variable tag Dim paramCID As New SessionParameter paramCID.SessionField = "CustomerID" adsClearCart.DeleteParameters.Clear() 'clear all existing Delete Query parameters adsClearCart.DeleteParameters.Add(paramCID) 'add the Session variable Parameter adsClearCart.Delete() 'run the query End Sub Protected Sub btnNewOrder_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim paramCID As New SessionParameter paramCID.SessionField = "CustomerID" Dim paramOID As New SessionParameter paramOID.SessionField = "OrderID" 'add the parameters to the Order table Insert Query and run the Query adsAddNewOrder.InsertParameters.Clear() adsAddNewOrder.InsertParameters.Add(paramOID) adsAddNewOrder.InsertParameters.Add(paramCID) adsAddNewOrder.Insert() 'add the parameter to the OrderDetails table Insert Query and run the Query adsAddNewOrderDetails.InsertParameters.Clear() adsAddNewOrderDetails.InsertParameters.Add(paramCID) adsAddNewOrderDetails.Insert() 'Clear Cart ClearCart() Server.Transfer("confirm.aspx") End Sub The user presses a button to confirm there order, and thats when I get the error "No Value given for one or more required parameters." Hope someone can help!Thanks,Greg
Hi, i have a big problem. I´m having trouble with the select statement in SQL query language. The problem is that I need to retrieve various data from database and the input object is a list. The simple way of doing this is:SELECT <return values>FROM <datatable name>WHERE (<select data parameter>)My problem is that my where parameter needs to be an array list. The simple way of solving the problem would be using a for statement in my c# code and call my store procedure various times, but my array list can be too long and take a long time to connect and search data for each statement, so I need to access the database once.Can anybody help; I would appreciate it very much thx, Malcolm
I am trying to create a report against an Access 2003 database using the provider as follows: "Provider=Microsoft.Jet.OLEDB.4.0;"
Simple reports with no parameters are fine (e.g. using a text query of 'Select * from BERs'
But how do I pass in a report parameter to limit the results coming back ? I have only used stored procs in the past which work fine but how to achieved this using Access has me stumped.
An example query I need would be 'Select * from BERs WHERE Year = [Year?]'
I have reportviewer control into the aspx page and i want to save the parameters informations to the database. but i also want to stick with reportviewer parameter UI instead of my own UI.
Hallo is it possible to perform an insert/update query upon the execution of a report, using the report Visual Basic expressions? I mean, the user enters some values for the report parameters, and i would like to store them, along with the resulting recordset data, into a dedicated table in the database.
I was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
Hello!Is it possible to pass a stored procedure a parameter, say @table anduse it as a table in the sql command?Finally i want a proc to copy tables from a database to another database.THERE IS MY CODE:CREATE PROCEDURE [user].[copytable]@dbSRC varchar(100),@dbTRGT varchar(100),@table varchar(100)ASBEGIN TRANSACTION FreeAndCopyTableTRUNCATE TABLE [@dbTRGT].admin.[@table]INSERT INTO [@dbTRGT].admin.[@table]SELECT *FROM [@dbSRC].admin.[@table]COMMIT TRANSACTION FreeAndCopyTableIF @@error <0 GOTO E_Copy_FailE_Copy_Fail:ROLLBACK TRANSACTION FreeAndCopyTableGOthanks in advance,muh
In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.
Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.
I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition, (2) SQL Server Standard Edition, or (3) by an external tool.
In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.
Is this the intended behavior going forward?
In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.
[junit] The database name component of the object qualifier must be the name of the current database. [junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of the current database. [junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)
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)
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.
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.
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???
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?
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,
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
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
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);
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
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.
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")
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.
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
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?
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.
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?
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
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.
