MS SQL Server :: Learn To Access Stored Procedures With ADO.NET 2.0-VB 2005:How To Work With Output Parameters && Report Their Values In VB Forms?

Some Problems With Output Parameters In Stored Procedures

Jul 6, 2004

I have written a simple C# console application that create my own Stored Procedures
the code is here
----------------------------------------------------------------------------

static void Main(string[] args)
{
SqlConnection cn;
string strSql;
string strConnection;
SqlCommand cmd;
strConnection="server=(local);database=Northwind;integrated security=true;";
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount";
cn=new SqlConnection(strConnection);
cn.Open();
cmd=new SqlCommand(strSql,cn);
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine("Procedure Created!");
}

------------------------------------------------------------------------------------
but it has some errors becuase of my strSql
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount";
I mean in creating the stored procedure
if i delete the Output parameter from my stored procedure
and my strSql would be somethimg like this
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID ";
There will be no errors
I use Visual Studio.NET 2003(full versoin)and MSDE(not Sql Server)
Could someone help me solve this problem?
Thanks and Regards.

View 1 Replies View Related

Output Parameters Versus Recordsets In Stored Procedures

Jul 20, 2005

I've read that stored procedures should use output parameters instead ofrecordsets where possible for best efficiency. Unfortunately I need toquantify this with some hard data and I'm not sure which counters touse. Should I be looking at the SQL Server memory counters or somethingelse.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

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

Passing Parameters To Action Stored Procedures Using ADO, In Access Project

Jul 20, 2005

There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko

View 2 Replies View Related

Question About Stored Procedures, Dlls And Forms...

Feb 24, 2003

I'm just wondering what the effect of including a form in a dll would have on a system? I created a dll that actually has a hidden form in it and is called from a stored procedure using SQL Server 2000. I needed some of the functionality of the richTextBox control, only I didn't want to actually display the form with the box on it. This seems to work when called from a stored procedure (the method called returns correct values and the form never shows up on the server), but are there any inherent problems with this I'm not aware of? I wouldn't think it would be a memory leak and we're not too worried about security. I'm using this as a shortcut around having to write something that converts rich text into plain text and allows us to search the document for keywords. I know this might be a ghetto way around it, but it seems to work.

View 2 Replies View Related

How To Call A Stored Procedure On MS Sqlserver From Web Forms And Get Back Output

Apr 7, 2006

Hi,
How to call a stored procedure on MS Sqlserver from web forms and get back output
Thanx,
Nishu

View 1 Replies View Related

Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output && 2 Input Parameters In Param. Coll.?

Feb 23, 2008

Hi all,

In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":

CREATE PROC [dbo].[spInvTotal3]

@InvTotal money OUTPUT,

@DateVar smalldatetime = NULL,

@VendorVar varchar(40) = '%'

This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub printMyInvTotal()

Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"

Dim conn As SqlConnection = New SqlConnection(connectionString)

Try

conn.Open()

Dim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[dbo].[spInvTotal3]"

Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)

param.Direction = ParameterDirection.Output

cmd.Parameters.Add(param)

cmd.ExecuteNonQuery()

'Print out the InvTotal in TextBox1

TextBox1.Text = param.Value

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

conn.Close()

End Try

End Sub

End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?

Please help and advise.

Thanks in advance,
Scott Chang

View 7 Replies View Related

Control Access To SQL Server 2005 Database Only Through Stored Procedures Issue

Apr 14, 2008

Hello,

I have a database (SQL 2005) with two schemas (dbo and s1) and with tables defined in dbo and tables defined in s1. The stored procedures are also defined in both schemas, some of them in dbo some of them in s1. Some of the stored procedures query tables from dbo and s1 at the same time.
I want to have a new db role with access to the database only through sps and no other access read/write to the tables. I created a new db role and granted execute permission to it and assigned a user to it.
When I execute stored procedure defined in dbo with query against dbo tables, it works as expected.
However, if I run stored procedure defined in s1 with query to table in dbo, I receive error about missing select permission for the table in dbo. I am not sure why, but I can assume there is an issue with the ownership chain.
I can grand read/write permission for the tables, but this will break our original requirement for limited access to the db only through sp.
The other option is to have another role r2, with read/write, privilege and to use EXECUTE AS r2 in the sp.

I would like to ask first why the error for missing select permission happens and is there another way to have role restricted to only execute permission for all stored procedures.

Thanks,
IT

View 5 Replies View Related

Calling An SQL Server 2005 Stored Procedure Within Microsoft Access And Reading Values

Jan 14, 2008

Goodday.

I have finally been able to create a connection from Access to the SQL 2005 Server and was able to call a stored proc (in Server) in the following way

Code Block
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

cnn.ConnectionString = "Provider='sqloledb'; Data Source='Private';" & _
"Initial Catalog='DBName';Integrated Security='SSPI';"

cnn.Open

cmd.ActiveConnection = cnn
cmd.CommandText = "sp_DefaultEntityData"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute

rst.MoveFirst
Do While Not rst.EOF
lstEntity.AddItem (rst.Fields(0)), 0
'lstEntity.AddItem (rst.Fields(1)), 1
rst.MoveNext
Loop

The Stored Proc is as follow:

Code Block
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_DefaultEntityData]
AS
BEGIN

SET NOCOUNT ON;

SELECT tblEntities.[Name], tblEntities.PrimaryKey
FROM tblEntities
ORDER BY tblEntities.PrimaryKey;

END

The table contains 24 entries

As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data.

I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work.

Here are my Q's:
1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment?
2) How do I read the values into the listbox, without displaying the primary key in die box?

Thank you in advance!
Any help is very much appreciated.

View 2 Replies View Related

Access 2003 / SQLSEE 2005 - Using Forms To Filter Recordsets

Aug 7, 2006

I upsized an Access 2003 database to SQL Server Express Edition 2005, and have converted all tables, queries, reports and macros successfully so far. The snag I'm running into involves using macros embedded in buttons on certain forms using Access as the frontend.

With these forms, the idea is to navigate to a certain record, and hit a button to bring up a print preview view of a report. Based on the record you're looking at in the form, it should check against the data in the table to which it refers, and pull up a single record in the report.

In access as a standalone, using the OpenReport command, the code was as follows:

[MRB Report Data]![MRB Report Number]=[Forms]![MRB Data Input]![MRB Report Number]

MRB Report Data is the table containing data, and MRB Report Number is the primary key (autonumber). MRB Data Input is the form in question, and the second MRB Report Number is the control field that references back to the MRB Data Input table.

When I try to run this using Access as a frontend, an error occurs at the first '!' - is there a different syntax I need to specify to reference a form in this case?

Thanx in advance.

View 4 Replies View Related

SQL Express 2005 Forms: Default Field Format After Access Up-conversion

Apr 12, 2008

I am converting an Access 2003 database into SQL 2005 Express for purposes of evaluating the SQL server environment as a future home for my data. One of the motivating factors in the conversion is the integration of LINQ with VB 2008.

Assuming that there was no conversion tool to migrate Access forms for use with SQL and VB 2008, I converted the Access data table and used the VB 2008 form designer to databind textbox controls to fields in the converted SQL database. Here are some basic questions:

The conversion from Access to SQL apparently did not include the default numeric formatting (currency, percentage, etc.) which was part of the Access data table. Is there a place in the SQL server environment to supply a default data format so that forms and reports referencing the field do not need to be manually formatted for each reference?

After converting the data table and spending 2 hours designing the dataform for the 80+ fields, I inadvertantly changed the table structure and found that the dataform was not happy (oops). I corrected the databindings manually for the few errant field references, but wonder if there is some wizard to do this automatically?

Is there a way to print out the dataform itself? I used the following code snippet in my Access form code-behind and I wonder if there is an equivalent VB 2008 function:

Code Snippet

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection

Finally, Access can instantly change from dataform to datasheet presentation screens. Can this be done in VB 2008 with two views simultaneously presenting the same SQL data?

Please excuse my naivete, but in contrast to Access 2003 where program functionality is encapsulated into readily apparent controls, menus, and dialogs, the SQL server environment seems foreign, spartan, and all the words are different. Thanks, -BGood

View 3 Replies View Related

Sp_sproc_columns Does Not Seem To Work On Managed Stored Procedures

Jul 10, 2007

Hi,

I have written a stored procedure using C#.NET. I deployed it to SQL Server, and everything works fine.

However, an Third-Party app needs to see what parameters it can use for this SP by executing sp_sproc_columns <SP name>

sp_sproc_columns

Seems to work on normal SP's written in T-SQL, but not on Managed SP's. On my SP, sp_sproc_columns does not seem to work. It does not return the parameters. On normal SP's, it does.

Anyone have an idea why this is?

View 1 Replies View Related

Stored Procedures With Output Variables

May 25, 2007

Hi,

What is the syntax calling a stored procedure (let say named 'myproc') with OUTPUT variables (lets say the proc has an integer output variable named 'myvar') ?

Thanks,

Dror.

View 10 Replies View Related

Stored Procedure With Output Parameters

Feb 11, 2008

i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.

can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.

View 3 Replies View Related

Stored Procedure Output Parameters

Aug 3, 2004

Hi

I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.

I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?

View 3 Replies View Related

Stored Procedure And Output Parameters

Dec 3, 2005

EXEC('SELECT COUNT(docid) AS Total FROM docs WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?

View 2 Replies View Related

Stored Procedure And Output Parameters

Mar 23, 2007

Hi,

I hope someone can help.

I've written a stored procedure that returns a value via an output parameter.

I'm calling the stored procedure in an sql session is a loop, and it passes the value back correctly the first time, but all subsequent calls the output parameter appears to have the same value. I believe that I'm making some very basic mistake, but I can't work it out.

Here's how I'm calling the stored procedure several times
begin
declare @instrumentid int
exec GetInstrumentId 'OFX,AUDCHF,p,1,2007-03-20 16:54:21.843,2007-06-20,100.1', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDUSD,c,2,2007-03-20 16:54:21.843,2007-06-20,100.2', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDCHF,p,3,2007-03-20 16:54:21.843,2007-06-20,100.3', @instrumentid output;
select @instrumentid
end

And this is the start of the stored procedure

Create PROCEDURE [dbo].[GetInstrumentId]
(@Ticket varchar(250), @InstrumentId int output)
AS

If I call the stored procedure once it gives the corect output, if I call it several times the output parameter (@instrumentid ) never changes.

Sean

View 3 Replies View Related

OUT And OUTPUT - Stored Procedure Parameters.

Nov 19, 2006

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET----------------------------------------------------------------------------------------------------------

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @Product varchar(40)

, @MaxPrice money

, @ComparePrice money OUTPUT

, @ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;

-- Populate the output variable @ListPprice.

SET @ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);

-- Populate the output variable @compareprice.

SET @ComparePrice = @MaxPrice;

GO

--- USE

DECLARE @ComparePrice money, @Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@ComparePrice OUT,

@Cost OUTPUT

IF @Cost <= @ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

----------------------

--- Partial Result Set

----------------------

--Product List Price

---------------------------------------------------- ------------------

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

View 4 Replies View Related

Stored Proc - Output Parameter Does Not Work With Nested Level

Apr 26, 2004

Anyone can help with this question: thanks

in a asp .net application, I call a stored procedure which have a output parameter.
the output parameter works find in sql session, but not in the asp .net application.

if I put select msg_out = "error message" in position A(see below for stored proc), it works fine
if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session
The stored proc was created like this:

Create procedure XXXXXXX
(@msg_out varchar(80) OUTPUT
)
as
begin

while exists (*******)
begin
//position A
if certain condition
begin

select msg_out = "error message"
return 1
end

end

end

end

It seems to me that anything inside if - the second begin...end - it wont get executed.

Anyone has got a clue

Any help much appreciated!

View 5 Replies View Related

Stored Procedures And Parameters

Nov 22, 2006

How many parameters can I use for a Stored Procedure.

View 4 Replies View Related

Using Stored Procedures With Parameters

Jun 18, 2004

Hi all,

Just a quick question regarding the use of stored procedures with parameters.

How do I call a stored procedure with a different number of parameters?

For example if:

new SqlParameter("@SectionID", "3"),
new SqlParameter("@Sessionid", Session["ID"])

SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, sqlParams);

were to change to the following on a different page:

new SqlParameter("@SectionID", "3"),
new SqlParameter("@Sessionid", Session["ID"]),
new SqlParameter("@ExtraVariable", ExtraVariable)

SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, sqlParams);

How would I handle the different number of parameters in the Stored Procedure?

cheers,

Pete

View 1 Replies View Related

Stored Procedures And Parameters

Jun 23, 1998

I want to create a stored procedure with SQL Server 6.5 that CAN take 3 parameters, all of which are optional. The declaration for the stored procedure is :
CREATE PROCEDURE BackOrdersII @CustCode varchar(10), @FromDate datetime, @ToDate datetime AS
SELECT * FROM ISO_Details
WHERE DATEDIFF(dd, fldEst_Del_Date, getdate()) > 0 AND CONVERT(char(12),fldActual_Del_Date,3)=`01/01/00` AND CONVERT(char(12),fldEst_Del_Date,3)<>`01/01/00` AND fldDeleted<>1
ORDER BY DATEDIFF(dd, fldEst_Del_Date, getdate()) DESC

How do I formulate the procedure to allow me to select from the table, keeping the current WHERE clause but adding extra items to allow the results to be further filtered depending upon which of the parameters are given to the procedure. Any one, two, or all three parameters could be given.

Sorry if this seems like a simple question but I am only just getting into using stored procedures.

Thanks Rupert

View 1 Replies View Related

Stored Procedures And Parameters

Aug 24, 2004

Have looked everywhere and cannot find the answer! So perhaps someone here can answer.

I have an Access 2000 front-end to a SQL Server 2000 database.

I know how to create Stored Procedures that receive parameters; and also how to open a Stored Procedure in the query results window using the DoCmd object. For example,

Application.DoCmd.OpenStoredProcedure "MyProc", acViewNormal, acReadOnly

Does anyone know of a way to pass parameters to Stored Procedures & open the result using the DoCmd object? Without getting a parm input dialog?

Alternatively, does anyone know of a way to open a Stored Procedure in the query results view without using the DoCmd object?

Will appreciate any guidance you can provide! Thanks!

CarlR

View 3 Replies View Related

Stored Procedures With Parameters (in .adp)

Jul 20, 2005

I use a stored procedure that is calling several other stored procedurewhich update or append values in several tables. All of them are storedprocedures with input parameters by which they filter rows to be updated orinserted into other tables.Filtration is based on certain actual values on forms (form with severalsubforms).My question is following: How to pass parameters to those stored proceduresthat are triggered by a button?Those stored procedures are not recordset of forms, so I can't pass it usingInput Parameters property of forms (or I can?).Thanks.Zlatko

View 1 Replies View Related

Using Forms Authentication With The Report Viewer Control And SQL Server Reporting Services 2005

Jan 12, 2007

here is my error

System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Logon failed. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon failed. at Microsoft.ReportingServices.WebServer.RSCustomAuthentication.LogonUser(String userName, String password, String authority) at Microsoft.ReportingServices.WebServer.ReportingService.LogonUser(String userName, String password, String authority) --- End of inner exception stack trace --- at .................

Please help me

View 1 Replies View Related

Restored SQL 2000 Backup, Stored Procedures Don't Work

May 6, 2006

Hello
I have restored a SQL 2000 backup of my database, ever since my web applications are unable to use any of the stored procedures.
I get the following error:
Could not find stored procedure 'xxx'.

If I use enterprise manager and go to the procedures tab the procedure appears there.

Any ideas what is up?

PS: The username has been changed, before the backup it was "user1" now its something else. For some reason it still shows that the procedure object is owned by "user1". Could this be the problem?

Thanks in advance.

View 1 Replies View Related

Parameters Approach To Fill Report Header With Source Data Doesn't Work

Jan 19, 2007

It's well known issue, that one can't use any dataset fields in a
report header/footer directly. One of the approach is to create
query-based parameter that basically equals
=First(Fields!@FieldName@.Value, "@DataSetName@") and use that
parameter value instead. But it doesn't work in my case!

My report displays some entity description and is parametrized with
EntityID param. Its header contains entity name that, according to the
approach, is queried from the data source through the EntityName
report parameter. There's important issue: the report is displayed in
ReportViewer control, that is embedded into my application and entity
ID parameter isn't ser by user in ReportViewer parameters area. Its
default value is changed by the application with SetReportParameters()
web method every time a user wants to view the report according to the
entity the user is exploring in the application. But after the report
has been rendered, its header always contains not actual (outdated)
entity name. Nevertheless, the report body contains actual data
(including entity name). If I alter entity ID parameter in ReportViewer
or in web-based Report Manager and refresh report, header displays
correct entity name.

What's wrong in the workflow described?

View 3 Replies View Related

How Output All Stored Procedures To A Table Or File

Feb 22, 2005

I wish to ultimately have the content of all stored procedures related to a database in a single ASCII file for review. Is that doable? If so, how?

Thanks,

Peter

View 4 Replies View Related

Output Stored Procedures As Text Problem

Sep 13, 2005

I'm using the function below to output all of my stored procedures intoa text file. Fice, except that the output file does not reflect thenames of the stored procedures correctly if the name has been changed.For example, I create a stored procedure named: "sp123" and then renameit to "sp123DELETE" or "sp123 DELETE" or "sp123OLD" or "sp123 OLD" andwhat I end up with is four entries in the output file all having thestored procedure name "sp123."I stop the service and restart before outputting the file.Any help is appreciated.lqFunction ExportSP(myPath As String)Dim objSQLServer As New SQLDMO.SQLServerDim dbs As New SQLDMO.DatabaseDim sp As SQLDMO.StoredProcedureDim sptext As StringobjSQLServer.Connect <Servername>, <Username>, <Password>Set dbs = objSQLServer.Databases(<databasename>)Open myPath For Output As #1For Each sp In dbs.StoredProceduressptext = sp.TextPrint #1, sptext & _vbCrLf & vbCrLf & vbCrLf & _"*******" & _vbCrLf & vbCrLf & vbCrLfNextEnd Function

View 4 Replies View Related

How To Extract Output Parameters From A Stored Procedure.

Jan 25, 2008

I am stuck on how to syntactically retrieve an output value (@ProdCount) from a stored procedure. The SPROC works fine: the value of @ProdCount appears correctly in the output window. However, I can't retrieve it in the DAL handler (value remains 0). Does anyone have an idea on how to properly extract the return value. TIA for any pointers.SPROC (abridged): ALTER PROCEDURE and_Store_GetProductsByProdCatID_SortPage (@ProdCatID INT,...@ProdCount INT OUTPUT
)

ASSELECT @ProdCount=(SELECT COUNT(*) FROM and_StoreProduct WHERE ProdCatID= @ProdCatID)DECLARE @SQL nvarchar(4000)SET @SQL = 'WITH tmpProd AS ( SELECT ROW_NUMBER() etc.. )SELECT ProdID, etc..FROM tmpProdWHERE Row BETWEEN etc..ORDER BY etc..'

EXECUTE(@SQL)RETURNDAL handler (abridged): Public Overloads Shared Function GetProductListByCatID(ByVal ProdCatID As Integer, ..., ByVal ProdCount As Integer) As List(Of Product) Dim productList As List(Of Product) = New List(Of Product) Try
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("conAnders").ConnectionString) Dim cmd As SqlCommand = New SqlCommand("and_Store_GetProductsByProdCatID_SortPage", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@ProdCatID", ProdCatID) '...
cmd.Parameters.AddWithValue("@ProdCount", ProdCount) ' Output parm. Add dummy value.

Dim objProduct As Product 'Temp Product.

con.Open()
Using myReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) While myReader.Read() objProduct = New Product() With objProduct .ProdID = myReader.GetInt32(myReader.GetOrdinal("ProdID")) 'etc..
End With

productList.Add(objProduct)
End While Dim tmp As Object = cmd.Parameters("@ProdCount").Value ' <-- Not updated

myReader.Close() End Using End Using Catch ex As Exception Throw ' Pass up the error.
End Try Return productList End Function

View 8 Replies View Related

Stored Procedure With Parameters OUTPUT And RETURN VALUE

Oct 26, 2000

Hi

How I building a stored procedure that
return a output parameter and value return that I can
call from other stored procedure or VB ?

thank you in advance

View 1 Replies View Related

Which Is A Better Stored Procedures Verse Parameters

Mar 28, 2007

My colleague and I (both are newbie’s to .NET) are divided on whether to use stored procedures or parameters.

His viewpoint is, using stored procedures you are spreading the load i.e. SQL server and web server etc. This is not a good solution because it is not a portable when it has to be relocated.

Is he right?

I thought to avoid SQL injection it is best to use stored procedures but I do see his reasoning as well.

Yazzy is Very confused!!
Thanks in advance for any of your thoughts

View 2 Replies View Related

Learn To Access Stored Procedures With ADO.NET 2.0-VB 2005:How To Work With Output Parameters &&amp; Report Their Values In VB Forms?

Learn To Access Stored Procedures With ADO.NET 2.0-VB 2005:How To Work With Output Parameters && Report Their Values In VB Forms?