Using Datagrid Selected Value To Input To Parameter In Stored Procedure
Dec 9, 2005
could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE. I receive my 3 column headers in my datagrid but the parameter isn't doing anything
STORED PROCEDURE
CREATE PROCEDURE USP_Machcusidsearch
@Machcusid nvarchar OUTPUT
AS
SELECT dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDate
FROM dbo.Machine INNER JOIN
dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN
dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemID
where machcustomID = @Machcusid
GO
VB.NET CODE
Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great: Code: Dim cmd As New SqlDataSource cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure cmd.InsertParameters.Add("@firstName", txtFirstName.Text) cmd.InsertParameters.Add("@lastName", txtLastName.Text) cmd.InsertParameters.Add("@address1", txtAddress1.Text) cmd.InsertParameters.Add("@address2", txtaddress2.Text) cmd.InsertParameters.Add("@city", txtCity.Text) cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value) cmd.InsertParameters.Add("@zipCode", txtZipCode.Text) cmd.InsertParameters.Add("@telephone", txtTelephone.Text) cmd.InsertParameters.Add("@email", txtEmail.Text) cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value) cmd.InsertParameters.Add("@birthday", txtBirthday.Text) cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@question", txtquestion.Text) cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" cmd.InsertCommand = "sp_insertCustomer"
ERROR: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. Source Error: Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.
Here is an example of the XML being passed: <object> <property @propID="14" @propType="4" @propValue="Blah blah text" /> <property @propID="217" @propType="2" @propValue="Some other text" /> </object>
I have a table like this in my database: CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT) and I want a stored procedure that will accept XML and update this table. Here is what I am trying: CREATE PROCEDURE sp_UpdateObject @inValues XML AS BEGIN --create a temporary table DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)
--And then insert the values from the @inValues XML argument into the temporary table --I am sure the SELECT statement is VERY wrong
INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE) SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT) FROM @inValues.nodes('/object/property')
--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID AND @props.PROPTYPE != 6
END
I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.
I apologize for the long post. Thanks in advance for any help you can provide.
I have a project which will be a tools to edit different tables.
Now I need a stored_procedure to select data from different table.
For example I have a table name "TableFields" which have "tableID","FieldName", "DataType"and so on columns. It has the following records. "1","EmployeeID","Varchar" "1","FirstName","varchar" "1","LastName","varchar" "1", "EmployedDate","date"
It has the following records. "2","AddressID","int" "2","ApartNo","varchar" "2", "Address","varchar"
Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data, "001","Susan","Daka","1999-09-09", "002","Lisa","Marzs","1999-08-08", "003","David","Smith","2000-01-01",
I also have address table has columns "AddressID","ApartNo","Address" and has the following data "1","1101","1208 Mornelle Crt, Toronto", "2","1209","1940 Garden Drive, Toronto"
I need to create a stored procedure to select data from table "employee " or table "address" or even other tables according to information from "TableFields." So the table's name can be know as a input parameter, but the fields name will be a list of values and it all depends on tables.
I want to use fields name as a long string separated by",", like I have input "EmployeeID, FirstName,LastName" as an input parameter. But I don't know how to split the string.
Second, I need to create a stored procedure to insert or update data into these dynamically table.
I have a stored procedure that takes an input string parameter defined as @name nvarchar(24). The stored procedure takes this string and insert it into a table with the column also defined as nvarchar(24). When I execute this stored procedure with a string of more than 24 characters, the input string somehow get truncated and inserted successfully into the table without giving an error. Why is this the case?
If I simply execute an insert statement with a string longer than 24 characters, it will give me an error message. I tried enclosing the insert statement with a try-catch block in the stored procedure but I still can't trap any error.
i have an aliased column in an sql statement that works fine whendisplaying its output in a datagrid, but when I transfer the sqlstatement into a stored procedure , the datagrid can't see it. I get anerror "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain aproperty with the name myaliasedcolumn." }
Hi Guys, I have a sql procedure that returns the following result when I execute it in query builder: CountE ProjStatus 6 In Progress 3 Complete 4 On Hold The stored procedure is as follow: SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus This is the result I want but when I try to output the result on my asp.net page I get the following error: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:
Line 271: </asp:TemplateColumn> Line 272: <asp:TemplateColumn> Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate> Line 274: </asp:TemplateColumn> Line 275: </columns> My asp.net page is as follows: <script runat="server"> Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
' Mark the Command as a SPROC myCommandPS.CommandType = CommandType.StoredProcedure Dim num as integer num = CInt(myCommand.ExecuteScalar) 'Set the datagrid's datasource to the DataSet and databind Dim myAdapterPS As New SqlDataAdapter(myCommandPS) Dim dsPS As New DataSet() myAdapter.Fill(dsPS)
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).
I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .
Now i am using dataset with this store procedure to fill my @USER_IDS parameter
Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error
and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..
Hi, I am using a ListBox where a user can choose multiple lines. The index of the selected items are then used in a stored procedure.
I wan´t to use the ID´s in this statement: SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)
But how can I do this? If I pass them as a string, then I can´t use them as above. Can I separate the string '1,2,4,9' so I can use them in the statement above? Or can I send the values as a array to the stored procedure?
Hi I have this procedure it is creating the proc but when I execute it gives error Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@ID". Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@nextCode".
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30)
) AS BEGIN
SET NOCOUNT ON; Declare @ID int; DECLARE @SQL1 VARCHAR(2000) SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code' EXEC(@SQL1); Declare @nextCode varchar; DECLARE @SQL2 VARCHAR(2000) SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)' EXEC(@SQL2); Declare @NextAction varchar(30); Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode; Select @NextAction;
I am trying to create a stored proc that will take in a long string, but the stored proc does not allow me to take in more than 50 characters at a time. Is there a way to take away the limit? Please help me out, thanks in advance.
Display based on customerid display max of item they purchased on a order display only number like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.
using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter.
cid oid items 1 1 10 1 2 12 1 3 3 1 4 4
so if we enter 1 as custid it got to give us 12 as the result..
I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource2"> <Columns> <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False" ReadOnly="True" SortExpression="member_id" /> <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" /> <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" /> <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" /> <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" /> <asp:CommandField ShowEditButton="True" /> <asp:CommandField ShowDeleteButton="True" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>" SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure" DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" > <DeleteParameters> <asp:Parameter Name="member_id" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
hi, i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do: i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview: [<<Prev] [Selected Month] [Next>>] the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads). i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year. i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing. i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
Hi, I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.
I am trying to Execute a Stored Procedure using Call OSQL from a .bat file, and passing a DOS variable to the Stored Procedure.
The DOS variable is established in the DOS command...
set SERVERJOB=JOBNAME
I have tried...
EXEC sp_procedure %SERVERJOB%
With this, I get an error...
sg 170, Level 15, State 1, Server ABCDEFGH, Line 20 Line 20: Incorrect syntax near '%'.
If I put the variable name in quotes on the EXEC statement above, the value used is the variable name, %SERVERJOB% itself rather than the value the variable was set to, which would be JOBNAME in the above example.
I want to convert a SQL query as shown below into a stored procedure:
select name from namelist where town in ('A','B','D')
If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.
I am facing a problem while i pass xml as an intput to stored procedure. The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :
Declare @XMLString XML Set @XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" > </Company>' Exec sproc_Insert_Company @XMLString
The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2 XML parsing: line 2, character 34, illegal name character..
Its being generated because of the '&' being used in CompanyName.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
Could someone help me get this stored procedure to work? I want to give the stored procedure a long list of departments and have them added to a temp table. This only gets the first dept. in the temp table. I'm confused. Open to other suggestions, but want to use a 1col temp table to hold the depts.
After this is done, an SQL query is run using the temp table. Input for test: --csi_crystal_xxxx "pc9xp,pc8,pc7,pc6,pc6543,pc945678" --select * from ##CrystalGetCosts
create procedure csi_crystal_xxxx
@DeptResp varchar(4000) AS SET NOCOUNT ON DECLARE @SQL varchar(8000) DECLARE @Dept varchar(10) DECLARE @iLen int DECLARE @iPtr int DECLARE @iEnd int
If Exists (Select name, Type From [tempdb]..[sysobjects] where name = '##CrystalGetCosts' And Type = 'U') Drop table ##CrystalGetCosts
CREATE TABLE ##CrystalGetCosts (Dept_Resp_No varchar(10)) Set @iLen=Len(@DeptResp) Set @iPtr = 1 While @iPtr < @iLen BEGIN SET @iEND = charindex(',',@DeptResp,@iPtr) Set @Dept= Substring (@DeptResp,@iPtr,@iEnd-1) INSERT INTO ##CrystalGetCosts Values (@Dept) Set @iPtr = @iEnd + @iLen END
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference' BEGIN TRY select data from mytables END TRY
However I'm not sure this is the most efficient way to handle this.
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.PURCHLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID'
[Code] .....
My tabel is HST_MASTER.Control.
I want to have this query in a stored procedure. What syntax stored procedure i need to make to fill my table.
create procedure t1 (@a int,@b int,@c int,@d int,@e int,@f int) as begin select no,name,department from emp where a = @a and b =@b orc =@c or d = @d or e = @e or f = @f end
my problem is while executing i may get null values as input to the stored procedure . how to validate them ? any ideas . are there any arrays. if c and d are null then my condition would be where a = @a and b =@b or e = @e or f = @f how many loops shall i have to write.
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks