Web Forms :: Input And Output Parameters For Stored Procedures In VB.NET?
Oct 22, 2010
I have several VB.NET functions which pass and receive values from executed stored procedures using parameters. However, the latest method I am using seems to be a little too specific on the datatype and data length of the parameters for my liking (i.e.
LogActivityCommand.Parameters.Add("@strErrSource", SqlDbType.NVarChar, 300).Value = strErrSource). For example:
[code]....
If I end up changing the datatype and data length properties of the variables in my SQL stored procedures in the future, I am going to have to re-visit my VB code too and alter the parameter settings here as well.
I have a stored procedure that works fine for classic asp, but how can I get my output parameters data back from in /MVC 2 / linq/ entities framework model . I have inported the function also, but when i try to code it i dont get results ...i know this is a matter of lack of / knowledge or exmple because i have been scouring the web for it and cannnot find into that accually works.
here is the stored proc.
[Code]....
Here was the one of the few tries I did:
[Code]....
but it executes fine does not give me an error, but also does not return the info i need to the viewmodel
I keep seeing stuff about "ref" on the net but "ref" shows " arbument # should not be passed with the ref keyword.
So i am not sure if there is a problem with the model, or with my understanding this, Now for your info I can do the same step with returning a dataset from a stored procedure fine, but I dont want a data set I just want excatly the data in the output parameters from the stored procedure.
When I want to get the output values its okay but I also want returning a table as a result data.But Datareader has no rows.is it possible if I want a returning query result and multiple output values togather ?I wrote a test above.I can get output values as sqlparameters. But Datareader attached to a Gridview is empty.can you detect whats wrong here and it doesnt return a query result.So stored procedure is not standart or ı am doing something wrong.this doesnt raise any exception.but not returning any data.
I have a question. How do we define date output parameters for a stored procedure?
before you answer,I should mention that I am not referring to the sql syntax definition ,which is @mydate datetime output.
This is ok,what i am asking is HOW DO I SPECIFY IT IN LINQ WHEN I NEED TO CALL THE STORED PROCEDURE IN THE SERVER SIDE CODE. I am repeating that I do I am not referring to the sql definition .
I've modified an existing strored procedure. It originally had one ouput parameter in the stored proc and it was set up as follows in the c# code that called it:
Just to clarify, this parameter was declared as OUTPUT in the stored proc but as inputoutput in the C# code. This worked fine. I have added another parameter to the same stored procedure and I want to retrieve both from the C# code. The C# code now is as follows:.................................
The database I am using has a schema called EQB and as such, my stored procedures are named as EQB.usp_SelectFunds, EQB.usp_SelectAccount, etc.
On the select tab of the Configure Data Source screen, I choose to use a stored procedure. The dropdown shows my stored procedures, however, the schema name does not show up in front of the stored procedures in the drop down. I see only usp_SelectFunds, usp_SelectAccount, etc.
I select one of the stored procedures and when I click TEST, I get the message that the stored procedure is not found. If I instead choose to use a SQL statement instead on the configuration screen and enter EXEC EQB.usp_SelectFunds and click TEST, it works fine.
Why are my stored procedures not showing up correctly in the stored procedure drop down and how can I fix this?
I'm a novice @ asp.net and in fact any dynamic website building as I'm normally the dba behind the scenes, however, circumstances arise and now I'm looking at the front end, so forgive me if my question seems so obvious or badly worded, it's only day 2 of the .net environment for me.
I have a stored procedure with several parameters (11) that I have written & tested on the server.
I am trying to call it on a web form (using visual web developer 2008 express)
I have created the text boxes & drop downs on the form.
Added a gridview table connected to my stored procedure (which then works on testing) (parameters set to control & picked from the dropdown of form boxes to match).
there is also a submit button. (no coding added) just dragged on page.
Like i said when I test the connection for the gridview of the stored procedure it works, however on the web face it doesn't. I put in the parameters click the submit button and the screen refreshes, i get done @ the bottom but no results. I tried just using the sql and got the same result. I've looked at books but can't see what I'm missing, although, i suspect the onclick event of my submit button.
And this is my other SP that I define Condition ,and according to that condition it update or insert data in database
USE [behtop] GO /****** Object: StoredProcedure [dbo].[insertestate] Script Date: 10/04/2012 11:36:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[insertestate]
[Code] ....
In first SP @code parameter is column in table that I don't want accept duplicate value and condition of that is:
IF NOT EXISTS(SELECT behcode FROM Estate_p WHERE code = @code)
And in second SP I have @code parameter that I don't want it insert duplicate data on it too. But here condition is
IF @id > 0 AND EXISTS(SELECT behcode FROM Estate_p WHERE id=@id)
ringht now i am working with one web page, in that page i have 3 sections of data [or 3 set of data] in 1 set 4 to 5 fields actions to be taken when we click on save button 1st it should save the 1st fields data in one of my sp and it should get the table level id it should loop that table level id into another set of records & insert into to the another sp
I have a situation where in a legacy app (VB6) I have a grid which is populated by any one of a large number of (100+) stored procedures. Each of the procedures returns one or more rows with an identical list of columns (including column names, column data types, column order). I am basically looking at the (best) options to keep things reasonably light weight. Each procedure has its own set of parameters, each of which would result in different query plans etc. I have tried using each query to exec select into a (temp) table which is encapsulated into a "master" query which then returns a single results set, but it breaks SQL, the query optimiser returns an infinite number of rows - this is a MS documented issue. Looking for possible ways forward: generics, model view etc
I was reviewing my old post about this issue with a accessdatasource and that was simple since the Select, Update and Delete were all written within the datasource.
But this page im having issues with is using SQLDataSource and pointing to Store procedures. My issue is that i have created the sqldatasource with Insert(storeprocedure), Select(actual select statement), Update(storeprocedure) and was trying to take advantage of gridviews capabilities for editing when connected to a datasource that has all those elements setup.
Here is my datasource and gridview code: The gridview row goes into edit mode, but the changes never take affect on the database.
I have a webform which inserts data into a sql table successfully (form is bound to SqlDataSource1). The problem I have is that I need to populate a number of the DataFields based on an entered Ref number from a button click. The Ref is passed into a Stored Procedure as the only input parameter, all other params are output params. The procedure seems to run fine, but I can not seem to display any of the output params in my form. Here is my sproc:
PROC AUMS_RETURN_LOOKUP ( @REF @COMPANY @SALUTATION @FORENAME @SURNAME @ADDRESS1 @ADDRESS2 @ADDRESS3 @ADDRESS4 @ADDRESS5 @ADDRESS6 @CITY @COUNTRY @POSTCODE BIGINT, char(10) OUTPUT, char(20) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(30) OUTPUT, char(30) OUTPUT, char(20) OUTPUT ) AS SET NOCOUNT ON SELECT @SALUTATION @FORENAME @SURNAME @ADDRESS1 @COMPANY = rtrim([COMPANY]), = RTRIM([SALUTATION]), = RTRIM([FORENAME]), = RTRIM([SURNAME]), = RTRIM([ADDRESS1]), @ADDRESS2 = RTRIM([ADDRESS2]), @ADDRESS3 = RTRIM([ADDRESS3]), @ADDRESS4 = RTRIM([ADDRESS4]), @ADDRESS5 = RTRIM([ADDRESS5]), @ADDRESS6 = RTRIM([ADDRESS6]), @CITY = RTRIM([CITY]), @COUNTRY = RTRIM[COUNTRY]), @POSTCODE = RTRIM([ZIP]) FROM HEADER WHERE REF = REF Here is my code: Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Dim Company As TextBox = DirectCast(FormView1.FindControl("TextBox9"), TextBox) Dim Forename As TextBox = DirectCast(FormView1.FindControl("TextBox10"), TextBox) Dim Surname As TextBox = DirectCast(FormView1.FindControl("TextBox8"), TextBox) Dim TESTREFTextBox As TextBox = DirectCast(FormView1.FindControl("TxtBox_ref"), TextBox) Dim TESTREF = Convert.ToInt64(TESTREFTextBox.Text) Dim CompanyText = Company.Text Dim strCon As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString Dim Con As New SqlConnection(strCon) Dim Cmd As SqlCommand Con.Open() Cmd = New SqlCommand Cmd.CommandText = "RETURN_LOOKUP" Cmd.CommandType = CommandType.StoredProcedure Cmd.Parameters.Add("@REF", SqlDbType.Int) Cmd.Parameters("@REF").Value = TESTREF Cmd.Parameters.Add("@COMPANY", SqlDbType.VarChar, 15) Cmd.Parameters("@COMPANY").Direction = ParameterDirection.Output Cmd.Parameters.Add("@SALUTATION", SqlDbType.VarChar, 20) Cmd.Parameters("@SALUTATION").Direction = ParameterDirection.Output Cmd.Parameters.Add("@FORENAME", SqlDbType.VarChar, 50) Cmd.Parameters("@FORENAME").Direction = ParameterDirection.Output Cmd.Parameters.Add("@SURNAME", SqlDbType.VarChar, 50) Cmd.Parameters("@SURNAME").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS1").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS2").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS3", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS3").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS4", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS4").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS5", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS5").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS6", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS6").Direction = ParameterDirection.Output Cmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30) Cmd.Parameters("@CITY").Direction = ParameterDirection.Output Cmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 30) Cmd.Parameters("@COUNTRY").Direction = ParameterDirection.Output Cmd.Parameters.Add("@POSTCODE", SqlDbType.VarChar, 20) Cmd.Parameters("@POSTCODE").Direction = ParameterDirection.Output Cmd.Connection = Con "@REF", SqlDbType.Int)"@REF").Value = REF"@COMPANY", SqlDbType.VarChar, 15)"@COMPANY").Direction = ParameterDirection.Output"@SALUTATION", SqlDbType.VarChar, 20)"@SALUTATION").Direction = ParameterDirection.Output"@FORENAME", SqlDbType.VarChar, 50)"@FORENAME").Direction = ParameterDirection.Output"@SURNAME", SqlDbType.VarChar, 50)"@SURNAME").Direction = ParameterDirection.Output"@ADDRESS1", SqlDbType.VarChar, 50)"@ADDRESS1").Direction = ParameterDirection.Output"@ADDRESS2", SqlDbType.VarChar, 50)"@ADDRESS2").Direction = ParameterDirection.Output"@ADDRESS3", SqlDbType.VarChar, 50)"@ADDRESS3").Direction = ParameterDirection.Output"@ADDRESS4", SqlDbType.VarChar, 50)"@ADDRESS4").Direction = ParameterDirection.Output"@ADDRESS5", SqlDbType.VarChar, 50)"@ADDRESS5").Direction = ParameterDirection.Output"@ADDRESS6", SqlDbType.VarChar, 50)"@ADDRESS6").Direction = ParameterDirection.Output"@CITY", SqlDbType.VarChar, 30)"@CITY").Direction = ParameterDirection.Output"@COUNTRY", SqlDbType.VarChar, 30)"@COUNTRY").Direction = ParameterDirection.Output"@POSTCODE", SqlDbType.VarChar, 20)"@POSTCODE").Direction = ParameterDirection.OutputDim result As String Cmd.ExecuteNonQuery() result = Cmd.Parameters("@COMPANY").Value CompanyText = result
I would like to make sure that everything that goes into my database is safe, i.e. protection from sql injection.
What I want to do is type something in a textbox, "<b>hello</b>" for example, it be encoded before it's put in the database, but when I retrieve it, I want it to display "hello" in bold.
I've tried the obvious of server.encode on input and server.decode on the output (to a label and a literal control), but couldn't get it to display the text in bold without having unencoded text in the database.
We have an existing application with a mature database where most of the business logic is in Stored Procedures that are maintained by a separate department. Application developers are not given access to the database except via stored procedures.
I am looking for an example of calling stored procedures from within MVC.
Actually, we have done almost no development until now in ASP.Net at all. Is the difficulty in interacting with stored procedures a MVC problem or is it a more generic ASP.Net problem?
I used to be a software tester. I am a little bit familiar with stored procedures from client-server apps. My understanding is that they are on a server (where they operate more efficiently than if they were a module of code on a client machine) and they are used to modify the database.
Can anyone give me information a nutshell about stored procedures and when to use them?
I am starting work on an already fully developed site. This site has no input encoding or output encoding. If some one ebters <input type="text"> in an insert form it is displayed as a real tex box when viewing that form details.
So how can i apply encoding at input or output to the WHOLE SITE?
turning on validate request is not an option as the cms wud need it off.
also this is .net 3.5 so no question od using <%: tags.
I've been working on an upgrade for an app that's currently online.
I copied the database to my development server, have been making upgrades to the db (new tables, additional stored procedures, functions, etc). The new upgraded db is now complete and ready to go back online. I developed a script for all the new tables as I created them.
Basically this new db will go with the new upgraded app at a new domain.
So my plan is this...
Copy my existing db (the original) over to the new server.Run the script I developed on my development server to upgrade the new db to the latest version. Take the original db offline once the new one is running. However when I developed the new sps and fcns I didn't script them like I did for the tables (ooops! not thinking), and I have about 100 new sps. Is there anyway to script all the sps in one hit, so that I can run one script to upgrade the new db?
