Depending On The Selection Of The User Of The Asp.net Web-form How Can I Do Changes In The Stored Procedure
May 11, 2007
Hi frdz, I have created the following stored procedure in sql server 2005. In my database i have one option for the payment mode which can be done thru cash or credit(cheque). I have created my web-application in asp.net with C# 2005. There i have a dropdownlist box for the user to select the option whether wants to do the payment thru cash or cheque.Depending on that selection if user selects cheque then all the reqt for cheque like it's no,dt,bankname etc...are visible.but if user selects the option as cash then the cheque details become invisible.Depending on the selection of the user of the asp.net web-form how can i do changes in the stored procedure... i can write the condition likeif paymentmode=cash then ..........else.............but where and how can it be written ...pls tell methanxs in adv...u can go thru my below SP ALTER PROCEDURE MiscellaneousStoredProcedure
begin
set nocount on
select @miscid = isnull(max(@miscid),0) + 1 from miscellaneourpay
if exists (select * from storemaster where storename = @storename)
select @storeid = storeid from storemaster where storename = @storename
if exists (select * from accountmaster where accountname =@accountname)
select @accountid = accountid from accountmaster where accountname =@accountname
if exists (select * from accountgroupmaster where groupname=@groupname)
select @groupid=groupid from accountgroupmaster where groupname=@groupname
begin transaction
insert into miscellaneourpay
(
miscid,
storeid,
accountid,
groupid,
paymentdt,
paymode,
payeename,
bankname,
chqdt,
chqno,
amt,
bal,
remarks
Hello, My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type. The dropdown options on the form will be as follows: ComplimentsComplaintsGeneral CommentsSuggestions Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item. For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter. However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated. If you need more information, I will provide whatever is needed. As always, thanks for everyone's assistance. Chris
i have a requirement that user only sees drop down list and a button. when he selects an item from drop down and click button then i need to load rdl dynamically depending on the selection from the DDL.
i have created 3 rdl files, each one contains different content. I need to show the user, the selected rdl from the drop down.
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.
IF(@sub_assembly = NULL) BEGIN IF(@part = NULL) BEGIN PRINT 'This is an error. You must have at least a part' END ELSE BEGIN SET @querycolumn = 'Part' SET @queryvalue = @part END END ELSE BEGIN SET @querycolumn = 'SubAssembly' SET @queryvalue = @sub_assembly END
SELECT SubAssembly, Part, COUNT(RecordID) FROM Table WHERE @querycolumn = @queryvalue GROUP BY SubAssembly, Part ORDER BY SubAssembly, Part
The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?
I want to display the data in datagrid using the stored procedure,
Can you please tell me, how i can create the stored procedure for the following: using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)
I want to display the stored procedure data in my Datagrid.
Thank you very much for the help. the following is complete inline code on my webform.
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection")) Dim objCmd As New SqlCommand Dim dataAdapter As SqlDataAdapter
objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"
objConn.Open()
dataAdapter = New SqlDataAdapter dataAdapter.TableMappings.Add("Table", "Orders") dataAdapter.SelectCommand = objCmd
dataSet = New DataSet("Orders") dataAdapter.Fill(dataSet) dtgOrders.DataSource = dataSet dtgOrders.DataBind()
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data. There is a web app already created that has the followig code to get the data from the database Dim strSQL ast string = "UsersSelectCommand" intLoginID = objDataReader("LoginID")
My stored procedure is the following: CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName GO The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
need help with my problem i have this view this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4 like this evry 4 month it return to 1 >2.......... ------------------------------------------ for example
if i put unit_date = 01/05/2008 and unit=1 than new_unit=1
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit") and olso create stored procedure name "minus" + so if i run this name stored procedure name "minus" this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
I have the following stored proc. which i am using on the front end to get all the record from table:
if there are any fields it has anynull values in it i am getting error dbnull value error. i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.
CREATE PROCEDURE [dbo].[sp_displayrevws] AS select r.RevID, rtrim(f.revwfunction) as revwfunction, rtrim(u.uname) as uname, CONVERT(varchar(10),r.Issued,101) as Issued, r.ReviewerComment, r.Response, r.ModuleID, rtrim(r.ModuleName) as modulename, r.ReviewerUserID,r.RevFunctionid,r.Dispositionid from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC GO
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
I've been administering SQL Server for quite a while, but have managed until now to stay out of the development side. I find myself finally writing some complex SProcs. What I want to do is to create a "switch", i.e., a parameter that will output a few lines of text characterizing the SProc, with such information as required & optional parameters, potential "gotchas", and a handful of examples of usage. I want this to be able to work like this:
EXEC spMySProc '?' or EXEC spMySproc 'help'
This has to work even if there are multiple required parameters.
I have a stored procedure that moves specific data from several tables from a database on one server to several others (using a bunch of openrowset queries). I don't have an app to call this procedure - it's usually runs from the query analyzer and the parameter values for the procedure are passed in from there.
The procedure currently skips over recipient tables that are already populated with the data that's being moved. I want the ability to either skip the table or delete the existing records from the recpient table data based on the user's response. Therefore, I somehow need to prompt the user to get a response when the data already exists in the existing table.
Is there any way to prompt the user from a stored procedure, or do I have to re-develop the procedure in DTS or write and application?
I have a SSRS report with 6 columns each column containing count of total# of applicants meeting certain criteria. User want to click on each column and see the basic information and also want to get the ability to export the data into excel.
I know that I can create 6 drillthrough reports with basic information of applicants and link it to the count from each column respectively but I was wondering if it is possible to write a Stored procedure with all 6 select queries and execute only 1 select query based on the column that user clicks on main report ?
Hi. I have records that have a status of either approved, not approved or unknown. I want to create a crystal report that shows a summary of the records - in short how many of each and what percentage. How can I do this? The approved, not approved and unknown are not number columns so I can't add them to get a total. I know I can create a cursor in T-SQL to get the counts but how do I send them back to the asp.net code? --------------------- Here is part of the cursor I have created. IF @@FETCH_STATUS = 0 BEGIN IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1 IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1 IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1
FETCH curs_Briefs INTO @txtPrintSuit END ------------ How do I get @numUnkCount, @numSuitCount and @numUnsuitCount back to the asp.net application? Can the RETURN statement hold more than one value?
I am developing a managed stored procedure in VS.NET 2005 and I am trying to add a reference to an user developed assembly (not a system one) but adding a reference to it is not possible as it doesnt let me import assemblies but rather reference the few limited ones in a list. Why is this??
Does anyone have a SP which will output all users and their permissions (dbo), (read), (write) for each database for SQL 2k? I'm trying to find a way to simply automate this so I can output the data on demand for auditors. I'm currently checking each users permissions on each database through security/logins :(
Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help.
Code:
CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2)) RETURNS VARCHAR(2) AS BEGIN IF (LEN(@sValue) < 2) SET @sValue = '0' + @sValue
I need some clarification regarding the security inside of the master db.
I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.
Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?
I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.
Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?
If so, how to continue.
In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.
Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn
Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application. I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP). On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value: ALTER PROCEDURE [dbo].[S_SPROC] ( @ID int, @TYPE CHAR(10), @NAME CHAR(20) OUTPUT )AS SELECT @NAME = NAME FROM SPROCSWHERE [ID] = @IDAND [TYPE] = @TYPE Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.Has anyone done something similar before, or could point me in the right direction to solving this problem. ThanksPhil
Can i create user forms for user to select options and then present them with data based on their selection.i want to be able to use check boxs and combo box.thanks in advance
Can i create user forms for user to select options and then present them with data based on their selection.i want to be able to use check boxs and combo box.thanks in advance
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-)
Short list of process flow:
1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER
2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient
3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient
4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page
5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.
<FLOW WITH CODE AND FUNCTIONS :>
This is not the correct syntax but is showing what I understand sort of how this is to be done so far.
This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process.
This is code in Form.asp
'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- >
If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check Postback Type
'We need to load up vendors associated with the current client.
'--------- CHECK ACCESS HERE via function ab_HasAccessToClient --------
'If the call returns 1, then the employee has access.
'Otherwise, just write out "Access to this client is denied."
'CALL SP - Not sure what parameters need to go with it or its syntax
I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.