Send Parameter Via QueryString To A StoredProcedure
Apr 19, 2008
Hallo
i have a problem to send a parameter via QueryString to my storedProcedure.
If i run the procedure in VisualWebDeveloper everything works fine, when i enter the parameter with the DialogBox:
1 ALTER PROCEDURE dbo.StoredProcedure2
2 @appart varchar(50)
3
4 AS
5 BEGIN
6
7 SELECT category FROM immovables WHERE category = @appart
8 END
9 /* SET NOCOUNT ON */
10 RETURNThen i have a simple Page with a link <a href="Test.aspx?objtxt=34" target="_self">send</a> that send the QueryString to the Following Site: <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Unbenannte Seite" %>
</asp:SqlDataSource>
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
AutoGenerateRows="False" DataSourceID="SqlDataSource1">
<Fields>
<asp:BoundField DataField="category" HeaderText="category"
SortExpression="category" />
</Fields>
</asp:DetailsView>
</asp:Content> Now, when i run the application, i get the following message, when i fire up the Link:Für die Prozedur oder Funktion StoredProcedure2 wurden zu viele Argumente angegeben.I'm german so i get this message in german, tranlated: to many aguments wher passed to the procedure or funktion StoredProcedure2 I think the problem has thomething to do with the parameter deklaration in the storedProcedure.am I wrong?can somebody help me?Regards from Cologne (Germany) Caspar
I have a report that has 2 parameters. I use this report to do comparisons of performance data between two builds of our product. I'm not hosting this report in a seperate page, just using the standard sql reporting services web interface.
I'd like to be able to send out a link to this page that will load up the page with values for the parameters specified. I know in the report definition itself I can change the default parameter values, but I'd rather not change and redeploy the report every time I need to send it out for others to view.
My first thought was that it should be easy enough to specify the parameters as part of the url querystring and read the values in somehow using an expression for the parameters default value. Is this possible? I've done a bunch of searching but can't seem to find any good examples of this.
I am working on a form that when text is entered, the action Page is to execute the following query: SELECT PhList.Name AS Employee, PhList.Phone, Department.Name, PhList.JobTitle FROM PhList INNER JOIN Department ON PhList.Department = Department.ID WHERE (PhList.Name LIKE @Search) OR (PhList.Phone LIKE @Search) OR (Department.Name LIKE @Search)" BUT... i am having trouble making my @Search Parameter = %value% (where only 'value' was typed into the original form) Here is my SQLDataSourceCode...<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Intranet %>" SelectCommand="SELECT PhList.Name AS Employee, PhList.Phone, Department.Name, PhList.JobTitle FROM PhList INNER JOIN Department ON PhList.Department = Department.ID WHERE (PhList.Name LIKE @Search) OR (PhList.Phone LIKE @Search) OR (Department.Name LIKE @Search)" OnSelecting="SqlDataSource1_Selecting"> <SelectParameters> <asp:QueryStringParameter Name="Search" QueryStringField="Search" Type="String" /> </SelectParameters> </asp:SqlDataSource> here is my Selecting code... Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) e.Command.Parameters("Search").Value = "%" & Request.QueryString("Search") & "%" End Sub The error i am getting is... An SqlParameter with ParameterName 'Search' is not contained by this SqlParameterCollection. (From the e.command.par.........)
I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters and select the appropriate records.
I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.
I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if no records were returned...Therefore, I must get the querystring parameter that was originally passed in...
How do I populate the report control textbox with the value of querystring parameter?
Hi to all , I wrote a stiored procedure like below SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE SELET_AND_RETURN ( @opId Nvarchar(50) output)ASBEGIN SET NOCOUNT ON; SELECT @opId = NAME FROM TABLE1 PRINT @opId RETURN @opId;ENDGONow i include this procedure into my project, my problem is i dont know how to pass the output parameter from front end coding,
I am Passing one parameter Is UserID Table Name : SC_Attendence ColumnName Datatype AttendanceID int CompanyID int AttendanceDate datetime SectionID int
Table Name : SC_AttendenceDetails Columnname Datatype AttendanceDetailID int AttendanceID int UserID int IsAttended bit IsLate bit
Table : SC_UserSections columnname Datatype UsersSectionId int UserId int SectionId int CreatedDate datetime
Month NoofAttendenceDays NoofPresentdays January 25 21 Febrauary 22 19 March 25 22
Is there anyway to pass a parameter to an Excel spreadsheet (loaded in a browser) to use as a parameter in SQL statement?
I know that the Microsoft Technologies can write Excel files and then load them, but I'm looking to take an existing workbook, parameterize the SQL statement and pass parameters via the Querystring/Form Post to it.
Why you may ask? For existing PivotTables/Charts that can be filtered BEFORE they are run.
I would like some help conceptualizing how to acomplish this task. My client has requested the ASP.NET web/SQL Server application send an email to one of our locations based on whether that location complied (via the web app) to each week's report. Here's how the flow would go: The location checks the webpage weekly and selects their compliance to each record on the report. (They could overlook one of the records and not make a selection.) The SQL db would then query the compliance table and create a report of those locations with at least one "non-compliance." Then the Db would send a reminder email to each location on that report. The Db tables have the email address info assigned to each location, so I can join that to the compliance log table. Can this be done? I am using SQL Server Management Studio Express and Visual Web Developer (VB.net). Thank you for your ideas,SMc
Hi ! I have a problem with the unique identifier and don't know how to solve it. I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values. I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable. Dim cmd As New SqlCommand cmd.CommandText = "Insertar_Contacto" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = connect Thanks!!
I cant seem to make this sql work..... SELECT UserId, UserFirstName, UserLastName, UserPhone, UserName, UserEmail FROM UserTable WHERE (@SearchCat = @SearchText) This works fine (Where UserName = @SearchText) I need the "UserName" field to beable to be; UserID, UserPhone, Etc, not all at once just based on a list box My trouble lies in the @SearchCat... I need to be able to choose which column im searching in. I have 1 list box with coulmn names in it and a text box that holds the text that is being searched for. Any help would be awsome... Thanks Neil
Hi,I wrote two queries to search in three tables mp_parent, mp_page and mp_parentThe first one is: SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentName FROM mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID The second one is:SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_child.ChildName FROM mp_page INNER JOIN mp_child ON mp_page.ChiID = mp_child.ChildID I used this way to display the records in a FormView public HttpContext context = HttpContext.Current; public void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { ViewState["srch"] = context.Items["srch"]; } FormView1.DataSource = GetTable(); FormView1.DataBind(); } private DataTable GetTable() { SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=mp;Integrated Security=True;Pooling=False"); String SQL1 = "select mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentID, mp_parent.ParentName from mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID where PageContent like '%" + Convert.ToString(ViewState["srch"]) + "%'"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon); SqlCommandBuilder CB = new SqlCommandBuilder(Adptr); DataTable Dt = new DataTable(); Adptr.Fill(Dt); return Dt; SqlCon.Close(); } the question is how can I check if one record is empty to witch to another query ??Is it possible to know without using QueryString?Thank you
I have 2 pages. ( i want to pass information from a text box to the "certificate.aspx" database query) page 1 certsearch.aspx this is my script i have a label, commnad button, & textbox If txtSearchCert.Text = "" Then lblMsg.Text = "Please enter a certificate #" Else Response.Redirect("certificate.aspx" & txtSearchCert.Text) End If
page 2 certificate.aspx i am not sure what goes here.this is what i am trying Request.QueryString = (txtSearchCert.text) This is my database query on certificate.aspx page <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacsConn %>" SelectCommand="SELECT * FROM [SummaryBlue] WHERE REPORTNUMBER = ?"></asp:SqlDataSource>
I have a column name URL in Table1 with data like <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a> <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> i need to grab the news_media_i value by sql query Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance
Hi, I'm having problems passing a querystring from the datanavigateurlformatstring to be used in a insert sp on a webform. Please can someone take a look at my code and point me in the right direction. At the moment I'm gettingthis error messageCompiler Error Message: BC30451: Name 'userid' is not declared. but in the url i can see the querystring I've passed from the previous page. Thanks in advance Dave 1 Protected Sub Execute_Clicked(ByVal sender As Object, ByVal e As EventArgs) 2 3 Dim objConn As New System.Data.SqlClient.SqlConnection() 4 objConn.ConnectionString = "My connection string" 5 objConn.Open() 6 Dim objCmd As New System.Data.SqlClient.SqlCommand("test_insert", objConn) 7 objCmd.CommandType = System.Data.CommandType.StoredProcedure 8 9 10 Dim puserid As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@userid", System.Data.SqlDbType.Int) 11 Dim pdate As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@date", System.Data.SqlDbType.DateTime) 12 Dim papplicants As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@applicants", System.Data.SqlDbType.Int) 13 Dim pclients As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@clients", System.Data.SqlDbType.Int) 14 Dim pcontacts As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@contacts", System.Data.SqlDbType.Int) 15 16 17 18 19 20 puserid.Direction = System.Data.ParameterDirection.Input 21 puserid.Value = Convert.ToInt32(userid.QueryString) 22 pdate.Direction = System.Data.ParameterDirection.Input 23 pdate.Value = Convert.ToDateTime([date].Text) 24 papplicants.Direction = System.Data.ParameterDirection.Input 25 papplicants.Value = Convert.ToInt16(applicants.Text) 26 pclients.Direction = System.Data.ParameterDirection.Input 27 pclients.Value = Convert.ToInt16(clients.Text) 28 pcontacts.Direction = System.Data.ParameterDirection.Input 29 pcontacts.Value = Convert.ToInt16(contacts.Text) 30 31 32 33 command.ExecuteNonQuery() 34 35 36 objConn.Close() 37 38 End Sub
Hi, I'm using the following to send two items in the querystring over to the next page, where I can display tables based on them. Both querystrings appear fine in the URL. The first querystring works fine to single out the order specified by the orderid from the querystring, but I can't seem to get my other sqldatasource to show the userid (+contents of aspnet_users table) using the userid I passed in the querystring. <asp:HyperLinkField DataNavigateUrlFields="OrderID,UserId" DataNavigateUrlFormatString="Copy of Orders.aspx?OrderID={0}&UserId={1}" DataTextField="OrderID, UserId" /> This is what I'm using to try and pick up the userid <asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource3" DataKeyNames="UserId"> <Columns> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="SELECT [UserId], [UserName] FROM [aspnet_Users] WHERE UserId = @UserId"> <SelectParameters> <asp:QueryStringParameter DefaultValue="UserId" Name="UserId" QueryStringField="UserId" Type="object" /> </SelectParameters> </asp:SqlDataSource>
hi all this my data source <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PhoneBook %>" SelectCommand="SELECT personid,firstname,lastname,birthadate FROM [Persons] WHERE (([BirthDate] >= @BirthDate) AND ([BirthDate] <= @BirthDate2))"> <SelectParameters> <asp:QueryStringParameter Name="BirthDate" QueryStringField="InitialDate" Type="DateTime" /> <asp:QueryStringParameter Name="BirthDate2" QueryStringField="LastDate" Type="DateTime" />
</SelectParameters> </asp:SqlDataSource>
i pass parmater from button Search and his code as followingprotected void btnSD_Click(object sender, EventArgs e) {DateTime x =DateTime .Parse (txtIDate.Text); DateTime y = DateTime.Parse(txtLdate.Text);Response.Redirect("~/Search/SearchResultDate.aspx?InitialDate>=" + x + "&" + "LastDate<=" + y); } as you see i write two date in txtbox and then click on search button i get Error System.FormatException was unhandled by user code Message="String was not recognized as a valid DateTime." Source="mscorlib" StackTrace: at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) at System.DateTime.Parse(String s) at SearchWUC.btnSD_Click(Object sender, EventArgs e) in c:WebSitesAdAdo.netPhoneBookDevSearchWUC.ascx.cs:line 53 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
If I script the SQL statement with a constant, deleting the record from the database works.
If I script the SQL statement to delete based on the WHERE clause being a variable name, it will not delete the record.
The value being compared in the WHERE clause comes directly from the Sequel database.
I have a dropdown box that is filled from the database. The dropdown1.selecteditem.text is placed in a variable. The script is to delete a record from the database where the table.name in the database equals the item name selected from the dropdown box.
This querystring does not delete the record from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'variableCompareText')"
This querystring does delete the recrod from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'John')"
Why can't I delete a record with a variable in the querystring? Otherwise, you would have to always know in advance which record to specify rather than being deleted dynamically.
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();
hi. i'm building a news section for some friends of mine. i list all the news items on the main page in a gridview. i've made a custom edit linkbutton that sends the user to an edit page, passing the news id as a quarystring variable. on the edit page i first check if the querystring variable contains an id at all. if not, i redirect the user to the main page. if an id is passed with the querystring, i fetch the matching news item from the database and place it in a formview control for editing.so far, so good. but what if someone types a random id in the querystring? then the formview won't show up and i'd look like a fool. :) therefore, i need some kind of check to see if the id exists in the database. if not redirect the user back to the main page... so i started thinking: i could check the databsae in a page_load procedure. if all is well, then display the news item. since the formview is automatically filled with the correct data, does that mean that i call the database two times? i mean, one for checking if the news item exists, and one for filling the formview. logically, this would be a waste of resources.help is appreciated.
I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.
DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' + N'<table border="1">' + N'<tr><th>Vendor</th><th>Delivery Date</th>' + N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' + N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'
Hi all, I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me. Thank You
Hi I use the next StoredProcedure in Access, Inserts Users to tblUsers: INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments ) VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
I am using SQL Server 2005 now and I have a table with following columns. ID, FirstName, LastName, Email "ID" is the primary key (int) and is set auto generated (1 increment) I have a StoredProcedure to insert a new record. CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:Luca de Angelis 7 -- Create date: 22/08/2007 8 -- Description:Inserimento dei dati contabili nella tabella di log 9 -- ============================================= 10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili 11 -- Add the parameters for the stored procedure here 12 @id_gestore tinyint 13 AS 14 SET NOCOUNT ON 15 BEGIN TRANSACTION 16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 17 SELECT CEL_traffico_temp.numero_telefonico 18 , CEL_traffico_temp.anno 19 , CEL_traffico_temp.mese 20 , @id_gestore as id_gestore 21 , 1 22 FROM CEL_traffico_temp 23 INNER JOIN 24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella 25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 26 27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 28 SELECT CEL_traffico_temp.numero_telefonico 29 , CEL_traffico_temp.anno 30 , CEL_traffico_temp.mese 31 , @id_gestore as id_gestore 32 , 2 33 FROM CEL_traffico_temp 34 INNER JOIN 35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella 36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 37 38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 39 SELECT CEL_traffico_temp.numero_telefonico 40 , CEL_traffico_temp.anno 41 , CEL_traffico_temp.mese 42 , @id_gestore as id_gestore 43 , 3 44 FROM CEL_traffico_temp 45 INNER JOIN 46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella 47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 48 49 IF @@error <> 0 50 BEGIN 51 ROLLBACK TRANSACTION 52 END 53 ELSE 54 BEGIN 55 COMMIT TRANSACTION 56 END Help me please...
Visual Studio 2008 Code VB I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table. Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
AS BEGIN IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID, Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID, @Status,@CaseType,@Offense)
To all, I looked at the MS-SQL pubs sample database and execute the example stored procedure reptq2 and I got 17 results set back. Where can I find an example using Visual Studio DataGrid or any means to get all these results from this SP.
