I'd like to have a chart with a category "group on" expression like this:
=Fields![Parameters!MyParameter.Value].Value
The above doesn't work. Is it because it can't be done, or because I don't have the right syntax?
I have a stored procedure that calls some UDF User Defined Functions,the purpose of which is to create row strings out of numerous columnstrings for matching uniqueIDs.The problem is I need to join that SP with some other tables.The SP I have reads something like:mySPName@myUserID intSELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningStringFROM myTEMPTableNameGROUP BY myUniqueIDWHERE myTEMPTableName.UserID = @myUserIDI need to join that result with myTableName on myUniqueID such as:Select myTableName.myField1, myTableName.myField2,mySPName.myRunningStringFrom ...-- joining myTableName.myUniqueID = mySPName.myUniqueIDCan this be done?The reason I don't just do it with a View instead of an SP is that Ihave that parameter that must be passed to filter the records inmyTEMPTableName.Any help is appreciated.lqoh...the UDF looks like:Create Function dbo.fn_myFunctionName(@myUniqueID as int) returnsnvarchar(500)ASBEGINDECLARE @ret_value nvarchar(500)SET @ret_value=''SELECT @ret_value=@ret_value + ';' + myStringFROM myTEMPTableNameWHEREmyUniqueID =@myUniqueIDRETURN RIGHT(@ret_value,Len(@ret_value)-2)END
View 1 Replies View RelatedHopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.
First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend.
My SQL database is relational and is used to store names, addresses, etc of other companies.
What I need to be able to do is have 2 transactions, one nested within the other. In pseudo-code:
BEGIN TRANSACTION1
BEGIN TRANSACTION2
INSERT INTO COMPANY TABLE
COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2
GET COMPANYID JUST ADDED
PERFORM REMAINING INSERTS
COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2
Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit. Therefore, I can't retrieve the companyID halfway through.
Is what I'm trying to do even possible? Thanks in advance for the help!
Hi, i have to nest 2 cursors together and frankly i'm lost.
Here's what I have so far
CREATE PROCEDURE SeeAllColumns
AS
SET NOCOUNT ON
DECLARE @strMessageVARCHAR(100)
DECLARE @strColumnVARCHAR(100)
DECLARE @strTableVARCHAR(100)
DECLARE @strCommandVARCHAR(250)
SELECT @strMessage = 'SELECT ALL TABLES'
PRINT @strMessage
DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'
OPEN crsTables
FETCH NEXT FROM crsTables INTO @strTable
WHILE (@@FETCH_STATUS = 0) BEGIN
SELECT @strMessage = 'SELECT ALL COLUMNS'
PRINT @strMessage
SELECT @strCommand = ' SELECT ' + @strColumn + ' FROM ' + @strTable
EXECUTE (@strCommand)
FETCH NEXT FROM crsTables INTO @strTable
END
DEALLOCATE crsTables
PRINT 'DONE'
What i need to do is select all columns from all tables, but none of the actual data within the columns, just the names. I just don't know where or how to insert the 2nd cursor.
Can someone help me?
Thanks!
Hi,
I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.
So i build a admin
|
boss
|
worker
|
subworker
Nested Role
Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:
SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))
But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.
What am i doing wrong?
Thanks.
Hi!
If I have two procedures that call each other, I run out of the 32 level maximum recursion.
Is there a workaround to rewrite that without hitting the 32 level maximum?
Any help is appreciated!
-Bahman
hi I have another question to ever so helpful forum
I am trying to nest stored procedure but I guess it is not the way to do it as it does not work:
CREATE PROCEDURE dbo.GetSharesTransactionsbyDates
(
@Startdate as char(10),
@Enddate as char(10)
)
AS
dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @Startdate and @Enddate
I get complain that dbo is incorrect syntax
ST.Proc. which I try to call call is basicly a select statement with no parameters:
ALTER PROCEDURE dbo.GetSharesTransactionsData
AS
SELECT TRANS_DATE, TYPE_DESCRIPTION, SHARE_SYMBOL, SHARES_QUANTITY,
ROUND(PRICE_PER_SHARE,2) AS PRICE_PER_SHARE, COMMISSION_VALUE,
STAMP_DUTY,
dbo.GetShareTransactionTotalValue(PRICE_PER_SHARE,STAMP_DUTY,COMMISSION_VALUE,
SHARES_QUANTITY,CASH_AMOUNT, TYPE_DESCRIPTION) AS TOTAL_VALUE,/*calling function*/
ACCOUNT_NAME
FROM SHARES_TRANSACTIONS
WHERE (TYPE_DESCRIPTION = 'Sell') OR
(TYPE_DESCRIPTION = 'Buy') OR
(TYPE_DESCRIPTION = 'Cash Divident')
ORDER BY TRANS_DATE
I am wondering if there is a way that we could do a nesting case statement in an SQL Query?
This is what I have now... FicaWages = CASE WHEN (UPR00900.FICAMWGS_1 + UPR00900.FICAMWGS_2) >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' WHEN UPR00400.SBJTSSEC = 0 THEN '0' ELSE (UPR30100.GRWGPRN - (SELECT SUM(UPR30300.UPRTRXAM) FROM UPR30300 WHERE UPR30300.EMPLOYID = UPR00100.EMPLOYID AND UPR30300.PAYROLCD LIKE '3%' AND AUCTRLCD = 'UPRCC00000007')) END
What I want:
FicaWages = CASE
WHEN
('Sql Statement') = 'GRM'
THEN
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
ELSE
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
END
I hope this is clear enough.
It's me, one more time. My last request didn't quite filter all my records,I have one more field I need to evaluate in the script.DESCR TYPE SELL StartDate EndDate Number65048 04 Price A 4/21/2004 4/26/2004 3545665048 06 Price C 4/20/2004 4/27/2004 3545965048 08 Price B 4/22/2004 4/28/2004 3455965049 04 Price A 4/19/2004 4/24/2004 3859565049 06 Price B 4/22/2004 4/25/2004 3859465049 06 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 3811265050 06 Price B 4/18/2004 4/28/2004 3855065050 07 Price C 4/17/2004 4/29/2004 38110Descr, Type, Sell and Number are CHARStartDate and EndDate are SmallDatetimeI need a simple query that would display the records with the followingcriteria:#1. "Date I Enter" >= Startdate#2. "Date I Enter" <= Enddate#3. Highest TYPE for each DESCR#4. Highest NUMBER for each TYPE/DESCR (the tie-breaker)Results for ("Date I Enter" = 4/23/2004) should be:65048 08 Price B 4/22/2004 4/28/2004 3455965049 09 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 38112I REALLY appreciate the help!Thanks!!-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
View 1 Replies View RelatedDear all,
I am constantly running into this error when calling CLR functions from triggers (even when the triggers are CLR functions themselves): The context transaction which was active before entering user defined routine, trigger or aggregate "[name of CLR sp]" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
However, there is no cyclic nesting going on here. In fact, the error only appears when the CLR function is writing to the database. Only executing SELECT statements inside the statements doesn't freak out the SQL server (2005 SP1 in my case). I am not ending any transaction at all. This error shows up in many places: as exceptions from ASP.NET web pages, as errors in the msmerge_conflicts_info etc. Now I saw in the thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457818&SiteID=1
..that this was supposedly a known bug in CLR. But in my case, it appears that I can't call a single CLR inside a trigger if the CLR is supposed to write something to the database, which is a pretty severe limitation of CLR imho.
I was trying to figure out if I can avoid this error by escaping the transaction context, I tried that inside CLR (Using New TransactionScope(TransactionScopeOption.Suppress)) but it didn't work. Also, calling COMMIT TRANSACTION before calling the CLR doesn't seem to solve the problem since it's not suppose to end the ambient transaction of the trigger according to BOL, and in many cases it doesn't seem wise to do so, e.g. if the trigger is fired in merge replication as in my case.
I have sort of a weird error when attempting to create a database in SQL 2005 (migrating from SQL 2000 to SQL 2005).
I am able to run the CREATE PROC script just fine in SQL 2000, but I get this error in SQL 2005.
Msg 191, Level 15, State 1, Procedure udf_JCMS_ConcurrencyCheck, Line 2518
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries
I can't easily past up the whole script(it's 2655 lines), but it basically takes the following form:
CREATE FUNCTION dbo.Ugly (
@PK1 int,
@PK2 int,
@PK3 int,
@TableID int,
@UpdateBy char(30),
@LastUpdate datetime ) RETURNS BIT
DECLARE @UpdatedStatus as bit
DECLARE @RecordCount as smallint
SET @RecordCount = 0
If @TabelID = 1-- Comment about TableName
BEGIN
SELECT
@RecordCount =count(UNIQUE_ID)
FROM
dbo.MyTable
WHERE
UNIQUE_ID = @PK1
ANDPROXY_ID = @PK2
ANDLTRIM(RTRIM(ISNULL(LAST_UPDATE_BY,'')))= LTRIM(RTRIM(ISNULL(@UpdatedBy,'')))
AND
(
isnull(LAST_UPDATE_DATE,'') = isnull(@UpdatedDt,'')
or
convert(datetime,convert(varchar(10),LAST_UPDATE_D ATE,101)
+ ' ' + convert(char(8), LAST_UPDATE_DATE,8)) = @UpdatedDt
)
END
If @TableID = 2 -- Comment about TableName
BEGIN
.
.
.
END
If @TableID = 3 -- Comment about TableName
BEGIN
.
.
.
END
...
If @TableID = 156 -- Comment about TableName
BEGIN
.
.
.
END
IF @RecordCount = 0
SET @UpdatedStatus = 1
ELSE
SET @UpdatedStatus = 0
RETURN @UpdatedStatus
First, THIS IS NOT MY CODE.
Second, it appears that they are trying to create a function to test if a record has been updated (I cut out a bit of that).
I don't really get why SQL 2005 throws an error here. Yes, the code is hideous and not something that I would write myself. But it's not like the function is calling itself recursively or anything.
Can anyone shed any light?
Regards,
hmscott
I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.
To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?
I'd be happy to post the RDL if anyone is interested.
Thanks,
Casey
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
Hello,
I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Please let me know what should be the reason?
Thanks,
Ravi
Hi,
I face this error when i try to run my store procedure.
The sample of store procedure as following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON
DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END
OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)
SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END
CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF
anyone can help me identify this error?
Thanks
Regards,
Jojomay
Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?
View 1 Replies View RelatedI have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?
View 4 Replies View RelatedHi 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
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 RelatedHi,
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???
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,
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
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,
HI
Can someone explain me in detail when we use <asp:parameter> and when we use <asp:controlparameter>
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);
Thanks
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
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.
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
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,
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!
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.