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
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 need to send a table with value as a parameter in a stored procedure. It is possible any how?
View 3 RepliesI've written a stored procedure which uses a table variable. The stored procedure is shown below:
[Code]....
The table variable @CaseNumbers is declared, and is a bunch of values are inserted using a table-valued function called SplitStringIntoInts. At this point I can select from @CaseNumbers and this works fine.But when I try to join onto @CaseNumbers, the name '@CaseNumbers' in the ON clause is highlighted with the error 'Must declare scalar variable @CaseNumbers'. What?! It's not scalar, I declared it ages ago and I have used it since!
how do I add an output parameter to a Stored Procedure that returns a code or string to the calling app
View 1 RepliesI have an application with 40 or so separate databases that share a number of identical table schemas. Each is for a separate agency that performs functions identical (nearly) to the others. I have a number of reports that involve complex queries that each agency is required to produce. There is a "top-level" agency that would like to run the same reports but have the data consolidated for ALL agencies. I have tried having the query generated simply re-produced for each sub-agency by using a "UNION" ..
SELECT .... FROM [Agency].[dbo].[Table] ... UNION SELECT ... FROM [Next Agency].[dbo].[Table]....
This works for a few agencies selected/included but when they try to select all agencies or more than a few, it fails because the query is too large. I have tested a stored procedure:
CREATE PROC Test @DB AS
SELECT ... FROM [@DB].[dbo].[Table]...
Thinking I can build a smaller query by having up to 40 items of "EXEC Test (db) UNION EXEC Test (next db)" That also fails.... won't permit the database specifier as a parameter (plus, I don't know if I can do a UNION on the EXEC results).
i made stored procedure like that
select AssetCode ,Description
,EquipmentID from [FATMS].[fnMaintenanceHeaderRead](@ContextUser,@ContextClient,@ContextLang,@ContextApp,@ContextOrg,'1',@fnXmlParamMaintenanceHeader)
MH
where MH.Status
IN
( @Status)
when i put three values for parameter (@status) not work but one value work
how to put three values please to parameter @status when excecute that stored procedure
EXEC @return_value = [FATMS].[spMaintenanceReport]
@ContextUser = N'4806',
@ContextClient = 1,
@ContextLang = N'en-US',
@ContextApp = N'FATMS',
@ContextOrg = 23,
@ActionCode = N'1',
@MaintenanceHeaderId='',
@SearchText='',
@EntryType='0',
@Status=N' '
SELECT 'Return Value' = @return_value
I 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 want to know, how do i send a multivalue parameter from C# to Stored Procedure which is used in WHERE CLAUSE in IN statement?
for eg:
CREATE PROCEDURE [dbo].[sp_GetPolicyDivOff1]
@Insid varchar(5000)=null
AS
SET NOCOUNT ON
SELECT MST_GroupCompany.GC_Name,MST_DivisionalOffice.DIV_Name FROM MST_DivisionalOffice INNER JOIN MST_GroupCompany ON
MST_DivisionalOffice.DIV_FK_GC_ID=MST_GroupCompany.GC_ID where GC_Name IN(@Insid)
I 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?
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 RepliesIf 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 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?
Here is my stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[sp_GetNextSeq] (
@p_NextSeqNo int = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
Generate Next Sequence Number
[code]...
I have a gridview that calls data via a stored procedure. I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure.
Here is my gridview:
[Code]....
And here is my Stored Procedure, I want to pass the sortExpression to the gridview:
[Code]....
I have a stored proc. that returns the USL & LSL values for each of the 8 different Metrics. say, Defect Density (Upper Specification Limit) USL = 0.30 & Defect Density (Lower Specification Limit_ LSL = 0.05). Simillarly for 8 different Metrics. Why I need these Spec. Limits ?...Because I want o compare my project's Metrics Value displayed in the Grid View with the corresponding USL & LSL value of the same Metrics. IF Metrics Value is outside of USL & LSL Then I want to Mark the Metrics in Red Color.
1. STORED PROC "Get_Spec_Limits" Does Not Return Anything
2. Error : Object Reference Not set to an Instance of an Object, in the following "OnRowDataBound' Event
OnRowDataBound="GridView1_RowDataBound"
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound
Dim strConnection As String = "Data Source=******;Initial Catalog=Metrics;Integrated Security=True"
Dim objConnection As New SqlConnection(strConnection)
Dim da As New SqlDataAdapter("Get_Spec_Limits", objConnection)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim Productivity_USL As New SqlParameter("@Productivity_LSL", SqlDbType.Float)
Dim Productivity_LSL As New SqlParameter("@Productivity_LSL", SqlDbType.Float)
Dim IDD_LSL As New SqlParameter("@IDD_LSL", SqlDbType.Float)
Dim IDD_USL As New SqlParameter("@IDD_USL", SqlDbType.Float)
Dim EDD_LSL As New SqlParameter("@EDD_LSL", SqlDbType.Float)
Dim EDD_USL As New SqlParameter("@EDD_USL", SqlDbType.Float)
Dim DRE_FS_LSL As New SqlParameter("@DRE_FS_LSL", SqlDbType.Float)
Dim DRE_FS_USL As New SqlParameter("@DRE_FS_USL", SqlDbType.Float)
Dim DRE_TS_LSL As New SqlParameter("@DRE_TS_LSL", SqlDbType.Float)
Dim DRE_TS_USL As New SqlParameter("@DRE_TS_USL", SqlDbType.Float)
Dim DRE_Code_USL As New SqlParameter("@DRE_Code_USL", SqlDbType.Float)
Dim DRE_Code_LSL As New SqlParameter("@DRE_Code_LSL", SqlDbType.Float)
Dim COQ_USL As New SqlParameter("@COQ_USL", SqlDbType.Float)
Dim COQ_LSL As New SqlParameter("@COQ_LSL", SqlDbType.Float)
Dim COPQ_USL As New SqlParameter("@COPQ_USL", SqlDbType.Float)
Dim COPQ_LSL As New SqlParameter("@COPQ_LSL", SqlDbType.Float)......................
I 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 want to return two output parameter n temp table from sql server stored procedure
View 6 Repliesi would like to retrieve data from two table, how can i create a temp table and then insert data from two different table.and then use the temp table to join other table in stored procedure.if i want to select same data from two table, how can i select all distinct data from two table into temp table
View 4 RepliesI 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
my stored procedure is always extract some data into temp table such as"select name,address,content into #TEMP1 from table1,table2,table3"
finally, select another table together with #TEMP1 to extract all desire data from one queryselect T.name,T.address,T.content,C.other from #TEMP1 T, otherTable C
where T.ID=C.IDI would like to ask if i want to join one more table T2 into final query with "otherTable left C outer join on C.ID=T2.ID"
I am a rookie developer here and I have tried looking around for an answer but I am little confused. I have a stored procedure that calls multiple stored procedures when I go to create a new contact.[Code]....
I though I could use the OUTPUT Command to copy out the NewContactID but I am getting all kinds of syntax errors. I am confused as to what I am missing here.
I am creating a nightly process using a stored procedure to check for expiring records and emailing myself and the folks that need to see the information. The trouble I am having is trying to send the data in table "like" view in the message body.Here is the Proc you can see the point at which i try to use a table vairble then use it as a parameter for calling the email proc but it doesn't seem to work.
View 5 RepliesI tried nested ListViews but can get the display wanted.In my database I have a Category Table and a Product table.They share the CG_ID value.I want to make a stored procedure that will use two select statements to produce a single table.that can be used as a ListView DataSource.
[code]...
moving some tables & stored procedures from SQL Server 2000, to SQL Server 2005.So far so good, but I've come across a problem with this stored procedure:
CREATE PROCEDURE [dbo].[user_insert]
@user_service_unit int,
@user_champion_1 varchar(50),
@user_champion_1_nt varchar(10),
@user_champion_2 varchar(50),
@user_champion_2_nt varchar(10),
@user_champion_3 varchar(50),
@user_champion_3_nt varchar(10),
@user_date_received datetime,
@user_requestor varchar(255),
@user_info_requested text,
@user_expiry_date datetime,
@user_10_days datetime,
@user_5_days datetime,
@user_2_days datetime
[code]...