Urgent! Asp And Sql Stored Procedure Parameters

Apr 12, 2005

Hi, I have a problem with input parameter which has Decimal DataType. Stored procedure works but it rounds all
values, i.e 5.555 input becomes 6 and 1.3 input becomes 1.
In table QTY has data type decimal(5) - precision(8) scale(3).
Please, suggest what's wrong with this:

newqty = Request.Form("quantity")
..........
cmd.Parameters.Append(cmd.CreateParameter("qty", adDecimal, adParamInput, 5, newqty))
cmd.Parameters("qty").Precision = 8
cmd.Parameters("qty").NumericScale = 3

Please, help!
Thanks in advance.

View 4 Replies


ADVERTISEMENT

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

Execute Stored Procedure From Stored Procedure With Parameters

May 16, 2008

Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY

CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC

Any assistance would be greatly appreciated.
Thank you,
Dave

View 6 Replies View Related

URGENT Help --------- Stored Procedure

Oct 21, 2004

Hi !!

We are in a situaltion.... and don't have any answer...

We have 3 stored procedure. We want to write output of all the 3 stored procedure into one text file.. How do we do this?

We thought about DTS but its not solving our problem...

View 7 Replies View Related

Stored Procedure (Urgent)

Jun 14, 2006

I need to display the records from a start record number to an end record number. I need a stored procedure for this. The procedure should accept the query, start record number and the end record number as parameters. The procedure should be optimized such that it would work fast with even 1 lac records or more in the database table. Please help.....

View 1 Replies View Related

Stored Procedure Parameters

Oct 3, 2006

Hi,I have a problem with a call to stored procedure on SQL server. I created stored procedure for inserting a row to a table.In my application I read data from ASP.NET form and prepare parameters for calling my stored procedure. Like:            SqlCommand sc = new SqlCommand("AddToMyTable");            try            {                sc.CommandType = CommandType.StoredProcedure;                sc.Connection = new SqlConnection("myconnectionstringhere");                SqlParameter param;                param = sc.Parameters.Add("@MyFirstParam", SqlDbType.NVarChar,50);                param.Value = "something";                 ..... here I repeate "add" and value assignment for all my parameters            }...When I call ExecuteNonQuery(); I get exception that one of the parameters for stored procedure is expected but not supplied. However, I can find this very same parameter in my source code to be added to list of parameters. I think my problem is caused by large number of parameters. I have 55 parameters, half of them are of type nvarchar(50) and half of them of type bit. My reasoning is based on the fact that if I remove parameter that is reported to be missing from my stored procedure and from my application, I get the same error with next parameter in the list.Where should I increase maximum number of parameters that are allowed? Do I have some buffer problem? Where to specify that I need more space for parameters? Nenad 
 

View 10 Replies View Related

A Stored Procedure With Many Parameters

Jan 1, 2008

There is a form in my project which users can add their emails through it. The maximum numbers of emails are 60. I put a textbox and a button for adding email. But I don’t know which of the following solutions is suitable:
1.       After each click by user , the insert stored procedure will be called
2.       All the emails entered by users will be saved and then, the insert stored procedure will be called. This SP must have a loop but I am not sure about the right code for this SP because I have to define 60 input parameters for this SP.
 

View 3 Replies View Related

Stored Procedure Parameters

Mar 31, 2005

Hi
what's the difference between using parameters in these two ways:
SELECT
@PortalName = Portals.PortalName ...
and
SELECT
Portals.PortalName...
first one will NOT be a result set, and second one will
Whats the basic difference between that? I need to use a NextResult method within a .cs filebut it is not working
thanks

View 1 Replies View Related

Regarding Stored Procedure Parameters

Jun 29, 2007

I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which having all 11 parameters.
If I don’t enter any one of the value I am not getting the result and it will goes on exception handling.

Now what I want is even I enter one value this must be excute………….i think some validations should be done in
Stored procedure's 'where' clause.but I don’t know how

i would be very happy if someone guides me with sample code for this.

View 7 Replies View Related

Stored Procedure Parameters

Jun 16, 2008

I know there ar in parameters and out parameters, but are there any other kinds? Are there in/out parameters? If so, how would you declare one in the stored procedure?

Thanks.

View 8 Replies View Related

Stored Procedure With Parameters

Dec 6, 2006

Hi, I am using isqlw from SQL Server 2000. Is it possible to create T-SQL script that can receive input parameter from outside?

Example, my stored procedure is:

create my_sp @inputvar varchar(12)
as
select @inputvar
go

then I will create an .sql (such as mysql.sql) file that contains line:
exec my_sp parameter

next, I would like to call this .sql file using:
isqlw.... -i mysql.sql parameter?? -o output.txt

is it possible to do that way? Or is there any other way to do so?

Thanks a bunch,
nicoart.

View 8 Replies View Related

Regarding Stored Procedure Parameters

Jun 29, 2007

I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters.
If I don’t enter any one of the value I didn’t get the result and it is raising exceptions.

View 3 Replies View Related

Parameters In Stored Procedure

Feb 14, 2008



Parameters are passed to Stored Procedure from VB.NET Application like

Dim param As SqlParameter
param = New SqlParameter("@name",DbType.String)
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)

But is there any way to specify parameters without specifying the
parameter name and type like

Dim param As SqlParameter
param = New SqlParameter
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)

But when i try like this i am getting an error saying that "Parameter1 is not a parameter in the procedure"...
Can anybody tell me how to solve this .
Thanx in advance

View 5 Replies View Related

Passing A Value To Another Stored Procedure...need Urgent Help

Dec 10, 2007

Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne      @typeID smallint=null,      @title nvarchar(MAX)=null,      @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON    INSERT INTO Publication (typeID, title)    VALUES (@typeID, @title)    SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
 

View 8 Replies View Related

How To Use The Return Value Of A Stored Procedure (URGENT)

May 11, 2008

  Hi everyone, I have a stored procedure as the following:1 ALTER PROCEDURE dbo.logincheck2 @ID int,3 @Password varchar(50),4 @Result int output5 AS6 IF EXISTS (SELECT * FROM users WHERE (ID=@ID AND Password=@Password))7 BEGIN8 SET @Result=19 END10 11 ELSE12 BEGIN13 SET @Result=014 END15 16 RETURN @Result What I'm trying to do is using @result in my login.aspx.vb to check if user exists in my database. If he is, the result should be 1 and if not it should be 0. Unfortunately, I don't know how to do it. I must submit my project in 2 days and I'm desperately seeking for help. Here is my code:   1 Dim sqlcon As New SqlClient.SqlConnection2 Dim sqlcmd As New SqlCommand()3 Dim sonuc As Integer = 04 If IsValid Then5 sqlcon.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersShadow ShooterWebSiteMainProjectApp_Datamydb.mdf;Integrated Security=True;User Instance=True"6 sqlcon.Open()7 sqlcmd.Connection = sqlcon8 sqlcmd.CommandType = CommandType.StoredProcedure9 sqlcmd.CommandText = "logincheck"10 sqlcmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")11 sqlcmd.Parameters("@ID").Value = txtUsername.Text12 13 sqlcmd.Parameters.Add("@Password", SqlDbType.VarChar, 50, "Password")14 sqlcmd.Parameters("@Password").Value = txtPassword.Text15 16 sqlcmd.Parameters.Add("@Result", SqlDbType.Int, 4, sonuc)17 sqlcmd.Parameters("@Result").Direction = ParameterDirection.Output18 19 20 sqlcmd.ExecuteNonQuery()21 22 If (sonuc = 1) Then23 24 Response.Write("USER FOUND :)")25 'cookie is set with SESSION ID.26 Response.Cookies("User").Value = Session.SessionID27 Response.Cookies("user").Expires = DateTime.Now.AddMinutes(30)28 'Response.Redirect("Welcome.aspx")29 Else30 Response.Write("USER NOT FOUND :(")31 'Response.Redirect("invalid.aspx")32 33 End If    

View 5 Replies View Related

Sql Stored Procedure Problem(very Urgent)

Jul 6, 2000

Since i am newbie to sqlserver and & stored procedure. I need some help to modify the existing stored procedure.
The values and parameters are passing to stored procedure from Asp page.
My question is in Stored procedure, there is one value is Company name (vchCompany Name) , I need to check the values
passed from the asp page, whether the company name is already exists in the table. If exists display the pop up message window.

Thanks
The code is below:
************************************************** ****
CREATE procedure wbospsiCompany
@iSiteId int,
@iCompanyId int = NULL OUTPUT,
@chLanguageCode char(4),
@vchAssignedId varchar(255),
@vchCompanyName varchar(255),
@vchAddress1 varchar(255),
@vchAddress2 varchar(255),
@vchAddress3 varchar(255),
@vchCity varchar(255),
@chRegionCode char(4),
@chCountryCode char(4),
@vchPostCode varchar(40),
@vchPhoneNumber varchar(40),
@vchEmailAddress varchar(255),
@vchURL varchar(255),
@iCompanyTypeCode int,
@iCompanySubTypeCode int,
@iFamilyId int,
@iParentId int,
@iPrimaryContactId int,
@vchContactFirstName varchar(255),
@vchContactLastName varchar(255),
@iDivisionCode int,
@iSICCode int,
@iMarketSector int,
@vchTaxId varchar(255),
@vchDunnsNumber varchar(255),
@iPhoneTypeId int,
@iAddressTypeId int,
@iSourceId int,
@iStatusId int,
@bValidAddress tinyint,
@iAccessCode int,
@bPrivate tinyint,
@vchUser1 varchar(255) = NULL,
@vchUser2 varchar(255) = NULL,
@vchUser3 varchar(255) = NULL,
@vchUser4 varchar(255) = NULL,
@vchUser5 varchar(255) = NULL,
@vchUser6 varchar(255) = NULL,
@vchUser7 varchar(255) = NULL,
@vchUser8 varchar(255) = NULL,
@vchUser9 varchar(255) = NULL,
@vchUser10 varchar(255) = NULL,
@chInsertBy char(10) = NULL,
@dtInsertDate datetime = NULL,
@tiLockRecord tinyint = 0,
@tiRecordStatus tinyint = 1,
@tiReturnType tinyint = 1
AS
/*
** ObjectName: wbospsiCompany
**
** Project: Apollo
** SubProject:
** FileName: Insert.sql
** Type: Production
**
** Description: Inserts a record into the Company table.
**
** Valid Values for @tiLockRecord Valid Values for @tiReturnType
** ------------------------------ ------------------------------
** 1 = Lock Record 1 = Result Set
** 0 = Don't Lock Record 0 = Output Only
**
** Revision History
** ----------------------------------------------------------------------------
** Date Name Description
** ----------------------------------------------------------------------------
** 1/26/99 RobertA Created
** 10/11/99 GregP Dion schema compatibility changes
**
*/
BEGIN
/*
** Declare & initialize Local Variables
*/
declare @iReturnCode int,
@iWBOCPExists int,
@iWBOCPReturn int
select @iReturnCode = 0,
@iWBOCPExists = 0,
@iWBOCPReturn = 0
/*
** Declare & initialize Local Constants
*/
declare @PRE_OPTION int,
@CORE_OPTION int,
@POST_OPTION int,
@INSERT_CODE char(1),
@chUpdateBy char(4),
@dtUpdateDate datetime,
@dtModifiedDate datetime
select @PRE_OPTION = 0,
@CORE_OPTION = 0,
@POST_OPTION = @tiLockRecord * 2, /* Lock if @tiLockRecord = 1 */
@INSERT_CODE = "I",
@chUpdateBy = NULL,
@dtUpdateDate = NULL,
@dtModifiedDate = NULL
exec @iReturnCode = ospsiCompanyPre @iSiteId OUTPUT,
@iCompanyId OUTPUT,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@PRE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
/*
** Determine if @vchAssignedId and @iIndividualId should be set by configs
*/
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsuCustomerIds @iSiteId,
"Company",
@chCountryCode,
@iCompanyId OUTPUT,
@vchAssignedId OUTPUT
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Determine if WBOCP configurable procedure exists
*/
exec @iWBOCPExists = ospObjectExists "wbocpscCompany", "p"
if @iReturnCode <= 0
begin
begin transaction
if @iWBOCPExists = 1
begin
exec @iWBOCPReturn = wbocpscCompany @INSERT_CODE,
@iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT
if @iWBOCPReturn <> 0
begin
exec @iReturnCode = ospCheckError "p", @iWBOCPReturn
end
if @iReturnCode <= 0
begin
exec @iReturnCode = espcpCheckCustomerId @iSiteId, @iCompanyId
end
end

if @iReturnCode <= 0
begin
exec @iReturnCode = ospsiCompanyCore @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@CORE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Transaction Management
*/
if @iReturnCode <=0
begin
commit transaction
end
else
begin
rollback transaction
end

if @iReturnCode <= 0
begin
/*
** Call post procedure
*/
exec @iReturnCode = ospsiCompanyPost @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@POST_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
end
/*
** Manage return: return ID of record based upon @ReturnType param
*/
exec @iReturnCOde = ospManageReturn @tiReturnType,
@iReturnCode,
@iSiteID,
@iCompanyId,
"Company",
"I"
if @iReturnCode = 0
begin
select @iReturnCode = @iWBOCPReturn
end
return @iReturnCode
END

************************************************** ****

View 1 Replies View Related

Accessing Stored Procedure Parameters Through XSD

Dec 9, 2006

Hi All,
 I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD.
Thanks,
 
Long Live Microsoft ;)
 

View 4 Replies View Related

About Inserting Some Parameters To A Stored Procedure.

Feb 4, 2007

Hello everyone,
I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance
Errors
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType' 
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand' 
 protected void Button1_Click(object sender, EventArgs e)
{
/* These two variables get the values of the textbox (i.e user input) and assign two local
* variables, This is also a good strategy against any Sql Injection Attacks.
*
*/
string Interview1 = TextBox1.Text;
string Interview2 = TextBox2.Text;

string Interview3 = TextBox3.Text;
string ProdMentioned = TextBox4.Text;

string ProdSeen = TextBox5.Text;
string Summary = TextBox6.Text;

string Compere = TextBox7.Text;
string Duration = TextBox8.Text;


//Create Sql connection variable that call the connection string
SqlConnection SqlConnection = new SqlConnection(GetConnectionString());

//Create a sql command to excute SQL statement against SQL server
SqlCommand Command = new SqlCommand();

// Set the command type as one that calls a Stored Procedure.
Command.InsertCommandType = CommandType.StoredProcedure;

//Call the stored procedure so we can pass it on the user input to retrieve user details
Command.InsertCommand = "Summaries";

//open the command connection with the connection string
Command.Connection = SqlConnection;

// Pass the user input to the Stored Procedure to check if user exists in our system.
Command.InsertParameters.Add("interview1", interview1);
Command.InsertParameters.Add("interview2", interview2);
Command.InsertParameters.Add("interview3", interview3);

Command.InsertParameters.Add("ProdMentioned", ProdMentioned);
Command.InsertParameters.Add("ProdSeen", ProdSeen);

Command.InsertParameters.Add("Compere", Compere);
Command.InsertParameters.Add("Duration", Duration);



int rowsAffected = 0;

try
{
rowsAffected = Command.Insert();

}
catch (Exception ex)
{
Resonse.Redirect("InsertSuccessfull.aspx");

}



// open the connection with the command
//Command.Connection.Open();

}

private static string GetConnectionString()
{

return ConfigurationManager.ConnectionStrings["BroadcastTestConnectionString1"].ConnectionString;

}
}  
 

View 1 Replies View Related

Stored Procedure Parameters Question

Mar 7, 2007

Hi Y'all,
On how many ways can i pass parameters IN a stored procedure? I want to boost my performance and get rid of 4 for loops in my page load.
SO how can I create a resultset (grid) with the same result as looping?
I hope i made myself clear....
Thanks in advance

View 1 Replies View Related

Passing Parameters Into Stored Procedure

Apr 11, 2007

I have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors.  How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server"            ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink"            SelectCommandType="StoredProcedure">            <SelectParameters>                        <asp:Parameter Name="Zip" Type="string" />             <asp:Parameter Name="MenuCode" Type="double" />                               </SelectParameters>
 

View 1 Replies View Related

Pass Parameters Into Stored Procedure?

Aug 11, 2007

How do I pass values from my ASP.NET page code into my Stored Procedure, to become parameters to be used in my Stored Proc?
Much thanks

View 2 Replies View Related

How To Write Stored Procedure With Two Parameters

Aug 23, 2007

 How to write stored procedure with two parametershow to check those variables containing  values or  empty in SP
 if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one       
Select * from orders  plz write this SP for me thanks  

View 3 Replies View Related

Stored Procedure Parameters In SQLDataSource

Jan 19, 2008

I want to know how to set parameters for Update Stored Procedure in SQLDataSource
 

View 5 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

Error Stored Procedure Parameters

Feb 17, 2004

Hi i'll get the following error

The SqlParameter with ParameterName 'xxxxx' is already contained by another SqlParameterCollection.

I'am trying to create an dataset in which data of the different connectionpoints are separated into different tables. Herefor i'm using an stored procedure.

Below you'll find part of the main function and the full function which will execute the stored procedure.

kind regards


main function

Dim Parameters As SqlParameter() = { _
New SqlParameter("@ConnectionPointID", SqlDbType.NVarChar, 255)}


For x = 0 To myds.Tables("ConnectionPoints").Rows.Count - 1
connectionpointID = myds.Tables("connectionpoints").Rows(x).Item("Eancode")
Parameters(0).Value = connectionpointID
mydb.doStoredProcedure("SP_EDS_Dyomes_XML", Parameters,connectionpointID.ToString, myds)
Next



Public Overloads Function doStoredProcedure( _
ByRef Mycommand As SqlCommand, _
ByRef myds As DataSet, _
ByVal Table As String)


Dim myda As New SqlDataAdapter

Mycommand.CommandTimeout = 180
If Mycommand.Connection Is Nothing Then
Dim mydb As New Database
Mycommand.Connection = mydb.generateconnection
End If
If Mycommand.Connection.State = ConnectionState.Closed Then
Mycommand.Connection.Open()
End If
CheckParameters(Mycommand)
myda.SelectCommand = Mycommand

Try
myda.Fill(myds, Table)
Catch ex As Exception
Debug.Write(ex.Message)
End Try
myda.Dispose()
Mycommand.Dispose()

End Function

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

Help Getting Parameters Back From A Stored Procedure

Nov 4, 2004

Hello everyone,

I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.

--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation

--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@RQSodfil = 'a', @BulkRan = 'a', @BulkFor = 'a', @BulkJit = 'a', @ID = '0', @RQSodfilFlag = '1', @BulkRanFlag = '0', @BulkForFlag = '0', @BulkJitFlag = '0', @DeleteFlag = '0', @ErrorNum = '0')}

--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer

' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn

' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum

' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)

' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@BulkRan")
gstrBulkForName = rsTableInfo.Fields("@BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@BulkJit")
gstrID = rsTableInfo.Fields("@ID")
intErrorNum = rsTableInfo.Fields("@ErrorNum")

End Sub

any help would be appreciated

View 1 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

Problem With Parameters In Stored Procedure

Apr 25, 2003

I've got a sp that originally took two params, but has been modified to to use three. It worked fine before, but now seems to give me the error that it's expecting a certain param but that it's not being supplied. I know for a fact that all three params are declared correctly and are all being initialized with the correct values, but the sp still doesn't seem to recognize that one of them is there. I don't know if this is relevant but right before I changed the sp we cleared the database and added a few tables but that's all that's changed.

Here's the code I use for creating the parameters. Like I said before, this worked earlier when only two parameters were being used, but it doesn't seem to work with two params now when I change it back to test it. Code:

'Call stored procedure for creating Tracking ID Number and adding Route Users to Tracking List
cmd.ActiveConnection = conn
cmd.CommandText = "getNextTrackingNumber"

'Add necessary parameters
Set paramRouteID = cmd.CreateParameter("@RouteID", adInteger, adParamInput, , routeid)
cmd.Parameters.Append paramRouteID
Set paramUser = cmd.CreateParameter("@CurrentUser", adVarChar, adParamInput, 255, UCase(Trim(CurrentUser)))
cmd.Parameters.Append paramUser
Set paramRouteType = cmd.CreateParameter("@RouteType", adVarChar, adParamInput, 255, UCase(Trim(ROUTE_APPROVAL_SHEET)))
cmd.Parameters.Append paramRouteType

I've debugged it and I'm 100% positive that there are values for these parameters after they've been created, so I'm at a complete loss as to what the problem here is. Any help would be appreciated.

Thanks,
Josh

View 4 Replies View Related

Parameters Processing Within Stored Procedure

Jun 30, 1999

I want to create a stored procedure that can have up to 20+ parameters. Then I want to be able to access those parameters within a while loop by iteratating through the values without using the specific parameter name.
example: @parm(x) where x = a subscript value.

Any suggestions?

Thanks in advance!
Sidney Ives

View 2 Replies View Related

Multiple Parameters To A Stored Procedure

Sep 6, 2006

Hi All,

I have a database with very heavy volume of data.

I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.

Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.

Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?

Thanks in advance,

HHA

View 4 Replies View Related

Passing Parameters To A Stored Procedure

Mar 8, 2004

Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)

The value I'm passing (@MyLSN) is 24/12359 (one LS Number)

The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))

The value I'm passing (@MyLSN) is '24/12359','24/12360'

This is where the problem begins because the stored procedure now doesn't return a recordset.

If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.

So if someone could point me in the right direction then that would be much appreiciated.

Thanks in advance.

View 9 Replies View Related

Passing Parameters In A Stored Procedure

Sep 19, 2013

I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.

It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?

EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]....

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved