If I have the inventory class and want to make it to be xml and passed to web services and then web services will call stored procedure with xml as input parameters.
how to do in coding?
I am calling a stored procedure, that exists in a SQL Server 2005 database, using ADO.NET, in which I instantiate a sql command object.
The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.
Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in a stored procedure?
how can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
CREATE STORED PROCEDURE SP_CATEGORY
@CATEGORY VARCHAR(30)
AS
BEGIN
SELECT LATIN_NAME, ENGLISH_NAME, ARABIC_NAME, CATEGORY
FROM FLORA, CATEGORY_LIST
WHERE FLORA.CATEGORY=CATEGORY_LIST.CATEGORY_NAME AND CATEGORY_LIST.CATEGORY_NAME IN (SELECT * FROM SPLITLIST(@CATEGORY, ','))
END
where can I write this code ?!
I have created stored procedure and student database and also asp.net application for asp.net page but it could not found stored procedure what is the mistake actually I don't no
View 7 RepliesI have this procedure
[Code]....
I don't know what variable should be put in where clause for tblVisitor.visitorID because there's a variable that was already passed by an output identity parameter by a different stored procedure. What will I put in the where clause for visitor id?
I am trying to create the following stored procedure in sql server Lat and Lng are the parameters being passed from c# code behind .But I am not able to create this stored procedureit indicates with error saying undefined column name Lat,Lng
CREATE FUNCTION spherical_distance(@a float, @b float, @c float)
RETURNS float
AS
BEGIN
RETURN ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) )
END
sqlda.SelectCommand.CommandText = "select *, spherical_distance( Lat, 57.2958, Lng) as distance
from business
[code]...
I want to send an image to a stored procedure variable that is varbinary(max) field, It works correctly when an image selected by fileupload and my image property has image file, and it can send to stored procedure. but when fileupload is empty I want to send NULL to stored procedure variable that is varbinary(max). I use DbNull.Value in C# code. But error occurs. What should I do for send Null to stored procedure?
View 3 RepliesI have a SQL stored procedure on a SQL server that uses a variable to create a new database. This works in SQL I have a connection string that works I have added a sqldatasource to a webform, and configured the datasource to connect to the remote SQL database and am able to select my stored procedure. I am able to bind the variable to a control in the sqldatasource wizard (often two of the same control appear- if that is significant)
When I test the sqldatasource in the configuration wizard and put in a manual entry, the procedure runs and works
It is on the web page I am having troubles. I want the user to enter a name in a text box, click a button/link, and run the procedure. Ive used buttons and links...but not in the right way I think, becuase when I click them the stored procedure doesnt run.
As mentioned above, I have selected the right control in the sqldatasource wizard. So what do I need to do now?
Is it button click event? If so whats the syntax?
Should I be embedding my textbox in a dataview?
My stored procedure expects a uniqueidentifier as a parameter. However, this unique identifier is stored in a table variable, which looks like this:
DECLARE @GiftGuid uniqueidentifie
Instead of dynamically building an sql to update data based on certain fields that are filled in, I would like to try and do it via a stored procedure I have three fields passing potentially passing to a stored procedure
myCommand.Parameters.AddWithValue("@Field1",
Field1.Text)
myCommand.Parameters.AddWithValue("@Field2",
Field2.Text)
myCommand.Parameters.AddWithValue("@Field3",
Field3.Text)
Stored Procedure accepts 3 parameters
@field1 nvarchar(20), @field2 nvarchar(20), @field3 nvarchar(20)
I want to be able to pass parameters in a variety of ways
field1, field2, field3
or
field1, field3
or
field2, field3
When I attempt thsi, it provides an error of a missing parameter when I do not supply all three. Is there any way to pass exclude parameters at times. The most important thing is that I do not want to update the field to a null when a parameter is not passed, I want it to exclude updating that field altogether
I'm looking to pass a list into a stored procedure, stored as List<myClass>. I've passed a table in before but I'm not sure how to pass a list.
View 1 RepliesI have an aggregate stored procedure (named sp2) that returns a count of records meeting a certain criteria ASrecCount. I am using a DAL per the ASP.NET tutorial and have created a Table Adapter (named
sp2TableAdapter) with GetData and Fill methods. I have a button on an ASP.NET page that launches an event handler. In the button's event handler, I have this line: sp2TableAdapter sp2 = new sp2TableAdapter();
What works so far:
This code will bind the value of sp2 to a data control:
sp2TableAdapter sp2 = new sp2TableAdapter();
DropDownList1.DataSource = sp2.GetData();
DropDownList1.DataValueField = "recCount ";
DropDownList.DataTextField = "recCount";
DropDownList1.DataBind();
When running this page and clicking the button, the numeric value of recCount returned from sp2 is bound to the DropDownList control and 1 row is poplated with the recCount value (in my case 1451).
Problem:
In the event handler, I do not want to bind the value of recCount to a control, but want to to use it in some logic as an integer. What is the C# code that will do this? (In place of the DropDownList control lines above)
I have "HUGE" stored Procedure... so I just decided to separate this big SP to some small sp ( for easy to maintain)I meet a problem is in SP A
create table #tmp
(
)
select .
from.
// then call sp B
exec B -- would it possible to pass my temp table to SP B? so I can do next operate in SP B
I would like to take your help for a small task of mine. I have dataset whose contents have been converted as xml, the contents of which needs to be sent to a stored procedure. How do i go about creating methods in the data layer and the stored procedure.What should be parameter type in the data layer's method and what should be the parameter type in the stored proc. I dont want to use a varchar at the stored proc level because it is limited to a length of only 8000 characters.
View 3 RepliesI have one more question. How can I use a column in the stored prcedure and pass it to a parameter in the code above?
Can we access a column like that?
Stored procedure ALTER proc [dbo].[spSearchCombo](@searchBy nvarchar(50),@searchKey nvarchar(50)) as select * from CD_DETAILS where @searchBy like '%' +@searchKey+ '%' I'm using ASP.net2.0 with c# to extract rows basedon search key from a text box and searchBy for the column to be searched. When i use column name instead of @searchBy which comes from value selected from a ddropdownlist i get the desired result . There seems to be a problem with format of @searchBy and i get a blank page.
protected void btnSearch_Click(object sender, EventArgs e)
{
string constring = ConfigurationManager.AppSettings.Get("con").ToString();
SqlConnection conn = new SqlConnection(constring);
conn.Open();
SqlCommand cmdSP = new SqlCommand("spSearchCombo", conn);
cmdSP.CommandType=CommandType.StoredProcedure;
cmdSP.Parameters.Add(new SqlParameter("@searchBy",SqlDbType.NVarChar,50));
cmdSP.Parameters["@searchBy"].Value=ddlSearchBy.SelectedValue.ToString();
cmdSP.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar, 50));
cmdSP.Parameters["@searchKey"].Value=txtSearch.Text.Trim();
SqlDataAdapter da=new SqlDataAdapter(cmdSP);
DataSet ds=new DataSet();
da.Fill(ds);
this.dgv1.DataSource=ds.Tables[0].DefaultView;
dgv1.DataBind();
}
I would like to create a common stored procedure for all insert operations performed in my Database. So i created a stored procedure as below.
[Code]....
But now my problem is how to pass parameters for this stored procedure. I have got the column names of the table passed. But i would like to pass the parameters for each insert operation of each table to identify which value is passed for which parameter.
how to pass parameters for this stored procedure.
(Parameter can be Column name prefixed @ i.e. @Column name)
It is possible to get the column names in another Stored Procedure . But how to prefix @ for each column and pass this as parameter to the Common Insert Stored Procedure?
I am unable to date as dynamic parameter to stored procdure with pivot.i am getting
error
Msg 8114, Level 16, State 1, Procedure Sample, Line 3 Error converting data type nvarchar to datetime. Msg 473, Level 16, State 1, Procedure Sample, Line 3 The incorrect value "@date1" is supplied in the PIVOT operator.
below is my stored procedure
[code].....
In my application I have 7 listboxes.Each list box has morethan 100 listitems.
the best way to pass the selected listitem values(including text values also) to stored procedure in sql server 2005.
I'm trying to create a stored procedure that get a Key and Name (both varchar), the procedure will check if a the key allready exists in the table, if it exists it will update the name, if its not exists it will add it to the table..
View 2 RepliesI want to create a dynamic stored procedure.
How can I create one?
A procedure which can be called with or without parameters passing (something like optional parameter. but don't know how to use optional parameters. plz tell me how to create a stored procedure with optional parameters, and also if it is the only way to create the dynamic stored procedures.)
[Code]....
I want create a stored procedure for insert values for Name,Company_Name,Designation,Eamil_Id,Remark then Ref_Id should automically get the value if insert in remain feilds like Name,Company_Name,Designation,Eamil_Id,Remark with out changing table structure
what is the code for create a stored procedure in this SQL CODE?
create database employee
create table emp(emp_id int,emp name string(15))
insert into emp values(1,"X")
insert into emp values(2,"Y")
create procedure edifyp
(
@firstname varchar(50),
@lastname varchar(50),
@Username varchar(50),
@password varchar(50),
@cpassword varchar(50)
)
as insert into esignup
(
[firstname],
[lastname],
[username],
[password],
[cpassword]
)
values
(
@firstname ,
@lastname ,
@Username ,
@password ,
@cpassword
)
this is my sign up page store procedure, and i have created sign up page, its already runs very well,
but i am a begineer, and i am learning my self. so i need to get the user name from this table as well password to log in with these datails,
ahow can i write stored procedure for log in page, and what shuld i have to do inside the login button contol
I am trying to create a stored procedure that filters data from sql server by date but i get this error:
Conversion failed when converting datetime from character string.
my stored procedure is like:
[Code]....
the parameters are passed to the stored procedures as dateTime variables from c# code...