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


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

Passing Xml To Stored Procedure For Inserting

Feb 16, 2007

Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?

View 1 Replies View Related

Inserting Into Tables Using Stored Procedure

Dec 7, 2007

I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
 
 

View 4 Replies View Related

Inserting Records Via Stored Procedure

Mar 23, 2006

I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
 
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
 
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
 
Please help, I am a newb and lost...as you can tell from my code...

View 4 Replies View Related

Inserting BLOB Using Stored Procedure

Sep 14, 2007

Hi,

Can we insert a blob in the database(eg: doc, jpeg, pdf, etc) from the sqlcmd prompt. I want to insert few files into my table having varbinary(max) column and i dont want to use any front end tool for making such insertions. What i am thinking of is providing a file system path for a particular file(eg: doc, jpeg, pdf, etc) to a stored procedure so that it can be inserted into the database, something that we can do via Oracle's sqlldr tool.

Regards
Salil

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

Inserting Simultaneous Tables Using Stored Procedure

Feb 8, 2007

I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReasonâ€? and “@WIPâ€?.â€? I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.

I made up the two tables just for testing, they are:
tbltest


ECID – int (PK)

View 2 Replies View Related

Can Anybody Tell Me Why The Following Stored Procedure Is Not Inserting Into My Database Table?....

Sep 7, 2007

ALTER PROCEDURE AddListAndReturnNewIDValue (
@EditorId int,@CategoryID int,
@ListTitle nvarchar(50),@Blurb nvarchar(250),
@FileName nvarchar(50),@ByLine nvarchar(50),
@HTMLCopy nvarchar(MAX),@MainStory bit,
@MainStoryImageFile nvarchar(50),@Publish bit,
@PublishDate smalldatetime,
@ListId int OUTPUT
)
AS
-- Insert the record into the database
INSERT INTO shortlist (EditorId,CategoryID,ListTitle,Blurb,FileName,ByLine,HTMLCopy,MainStory,MainStoryImageFile,Publish,PublishDate)
VALUES (@EditorID,@CategoryID,@ListTitle,@Blurb, @FileName, @ByLine, @HTMLCopy, @MainStory, @MainStoryImageFile,@Publish,@PublishDate)
-- Read the just-inserted ProductID into @NewProductID
SET @ListId = SCOPE_IDENTITY()
 
here is the sqlDataSource
<asp:SqlDataSource
id="srcShortList"
ConnectionString="<%$ ConnectionStrings:ShortList %>"
SelectCommand="SELECT Id,EditorId,CategoryID,ListTitle,Blurb,FileName, ByLine, HTMLCopy, MainStory, MainStoryImageFile, Publish,PublishDate,Date,Deleted FROM shortlist"
InsertCommand="AddProductAndReturnNewProductIDValue"SelectCommandType="StoredProcedure"
UpdateCommand="UPDATE shortlist SET CategoryID=@CategoryID,ListTitle=@ListTitle,Blurb=@Blurb,ByLine=@ByLine,HTMLCopy=@HTMLCopy,MainStory=@MainStory,Publish=@Publish,PublishDate=@PublishDate WHERE Id=@Id"
Runat="server" >
<SelectParameters>
<asp:QueryStringParameter
Name="Id"
QueryStringField="Id" />
</SelectParameters>
 
</asp:SqlDataSource>

View 2 Replies View Related

Regarding Stored Procedure For Selecting A Value From One Table And Inserting It To Another

Sep 19, 2007

Hi iam Prameela,
I want to select some dynamic values from a table and store them to another table.
Let me give u an example,its like:
I have UID,QID,Option1,Option2,Survey Name in one table called Survey Answers and i must select these values and insert them into Surevy Count table which contains some fields as QID,Opt1Cnt,Opt2Cnt,Survey Name. this is an online survey and when ever an user participate in the survey then values will be changed in Survey Answers like:
Surevy Answers Table:
UID   QID        Option1             Option2          Survey Name---------These are the fields
1         1             1                       0                    Articles
1          2            0                       1                    Articles
2           1            1                        0                      Articles
2           2             0                       1                       articles
I need to add all these Options of particular QID and store them in Survey Count table,like
QID          Opt1Cnt          Opt2Cnt          Survey Name
1                 2                     0                     Articles
2                  0                      2                   Articles
When ever the user participate in survey then there will be change in Survey answers table i.e the option count will be increased
So this count should be modified in Survey Count Table,like:
If another user participated in survey and if he voted for Option1 of QID1,Option1 of QID2 then the survey count table should be modified as:
QID           Opt1Cnt                Opt2Cnt             Survey Name
1                   3                         0                            Articles
2                   1                          2                             Articles
I need a Stored Procedure for this.
Please help me with this query.
 

View 7 Replies View Related

Question About Inserting A Row Before The First Row Of The Table ----Stored Procedure

Jan 31, 2008

Hi, I have a stored procedure. In the stored procedure, I have a table called "#temp", this table contains 50 rows that I select from other tables. Now I want to insert a row before the first row. How can I do it in SQL Server 2005? Thanks in advance.

View 2 Replies View Related

Inserting Request.Querystring Into The Stored Procedure

Feb 2, 2008

Hi i am trying to insert the value of my Request.Querystring into my stored procedure, but i am having trouble with it, how would i insert the id as a parameter which is expected from the stored procedure this is what i have doen so far;string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand comm = new SqlCommand("stream_PersonnelDetails", conn);comm.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
 
Thank you

View 2 Replies View Related

Inserting A Record Using Values From Another Stored Procedure

Aug 2, 2006

Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.

View 1 Replies View Related

Inserting 1:M Relationship Data Via One Stored Procedure

Sep 2, 2006

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

View 3 Replies View Related

Stored Procedure Format Incorrect - Inserting To Two Tables

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 1 Replies View Related

Stored Procedure Inserting Duplicate Records Randomly

Feb 1, 2005

I have a web app that calculates tax filing status and then stores data about the person.

Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.

Any help would be greatly appreciated.

There are many sqlcmdInsert.Parameters("@item").Value =

cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()

And that is it.

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

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

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







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