How To DataList To Read Stored Procedure Data To A Href Link
Sep 26, 2007
I am coding a DataList control for 2 column data from a stored procedure. The 1st column is
programName and the 2nd is programLink. I used Edit Template -> Edit DataBonding to add
<h ref="http.....>, but can not get it right. Please help. Also, what is the Parameter Source
for using Logon User ID as a stored procedure paramter when configuring a SqlDataSource?
None, cookie, control,..? TIA,
Jeffrey
I have tried to code a href link to bind the data from a Stored Procedure, as shown below.This herf string failed. How to use these special characters, ' < " > ) ?Or how can I use sb(string builder), which I have used successfully for display the data. TIA,Jeffrey<%cmd.CommandText = "MyProgramsA"cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.Add(New SqlParameter("@meid", SqlDbType.Int))cmd.Parameters("@meid").Value = EmpIdcmd.Connection = sqlConnection2sqlConnection2.Open()reader4 = cmd.ExecuteReader()While reader4.Read()%> <a href="http://sca3a56/'<% reader4(i).ToString() %>' "><% Response.Write(sb3) %></a><br /> <br />
Hi I need to know if it is possible to write on a table from another database and this using a stored procedure. If yes what is the correct syntax to connect to the other database.
I am wondering if you guys have any experience with failing Stored Procedures running inside a SSIS package with the following error:
===================================================== Message Executed as user: GAALPSVR034FSYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:51:22 AM Error: 2008-05-19 09:06:55.15 Code: 0x00000000 Source: usp_SLIM_Site_PreProcess Description: TCP Provider: The specified network name is no longer available. End Error Error: 2008-05-19 09:06:55.18 Code: 0xC002F210 Source: usp_SLIM_Site_PreProcess Execute SQL Task Description: Executing the query "Exec usp_SLIM_Site_PreProcess" failed with the following error: "Communication link failure". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:51:22 AM Finished: 9:06:55 AM Elapsed: 932.203 seconds. The package execution failed. The step failed. ======================================================
Sometime it fails when gets called through a job and sometimes even when a package is open in a design mode. The actual Stored Procedure NEVER fails if called in SQL Server Management Studio.
Any ideas or suggestions will be very helpful and appreciated Thanks for your help!
I am having problem in selecting the data from my datalist. Here is teh sample code <asp:datalist id="dlAttrName" runat="server" OnItemCommand="dlAttrName_SelectData" DataKeyField="attrID"> <ItemTemplate> <asp:LinkButton ID="lbSelect" Runat="server" CommandName="Select" style="display:none">Select</asp:LinkButton> <%# Container.DataItem("attrDef1")%> </ItemTemplate> </asp:DataList> Private Sub dlAttrName_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles dlAttrName.ItemDataBound If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then 'Add eventhandlers for highlighting 'a DataListItem when the mouse hovers over it. e.Item.Attributes.Add("onmouseover","????") e.Item.Attributes.Add("onmouseout", "????") 'Add eventhandler for simulating 'a click on the 'SelectButton' e.Item.Attributes.Add("onclick", ?????????????????????, String.Empty)) ??????????????????? this should have something like this Me.Page.ClientScript.GetPostBackEventReference(e.Item.Controls(1) But I am not able to get the ClientScript option, I don't know which namespace should I import?
I don't know whether I am right or not. Its urgent please reply back soon,any help will be useful. Thanks!!!!!!!
Hi,Using Chandu Thota's book Mappoint.net I attempted to set up a findnearby query using his example Implementing Spatial Search Using SQL Server. The book uses C# I have attempted to convert the code to call the stored procedure as follows: TryDim units As Int16 = 0 Dim cmd As SqlCommand = New SqlCommand("FindNearby") 'Assign input values to the sql command cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.AddWithValue("@CenterLat", latitude) cmd.Parameters.AddWithValue("@CenterLon", longitude)cmd.Parameters.AddWithValue("@SearchDistance", distance) cmd.Parameters.AddWithValue("@Units", units)Dim con As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("VehicleMarshallConnectionString").ConnectionString) If con.State = ConnectionState.Closed Then con.Open() If con.State = ConnectionState.Open Then cmd.Connection = conDim dreader As SqlDataReader = cmd.ExecuteReader() If dreader.Read() Then If Not dreader.IsDBNull(1) ThenDim ordnum As String = dreader(1).ToString End If End If End IfIf con.State = ConnectionState.Open Then con.Close() Catch ex As Exception Finally End Try
The code returns no values but when I execute the stored procedure from the server explorer in visual studio I get the records I would expect in the output window. How would I read the returned values from my code. Output Window Running [dbo].[FindNearby] ( @CenterLat = 53.10531, @CenterLon = -2.4769, @SearchDistance = 100, @Units = 0 ). ID ordnum ProxDistance ----------- ------------------------- ------------------------- 1 009999/USP 0 2 109999/USP 14.5971373147639 3 119999/USP 57.7144756947325 No rows affected. (3 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[FindNearby]. Regards, JoeBo
Hi I have decided to approach the problem here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103310
by using a Stored procedure, someone mentioned it's possible for a SP to read the contents of a text file ?
IS it ?
For example i have a DB table with fields to,from,subject,body
problem is the body field references a file (full path) and i need to extract all info into a txt file the full txt body, so can i perform some sort of inner operation on the select body .. part of the SP ?
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
CREATE PROCEDURE TestDataRead @TestString varchar(20) OUTPUT AS
SET @TestString = 'Mr.String' GO ----------------------------------------------------------------------- SSIS package details.
OLEDB connection manager; Connection works fine. Execute SQL Task Editor properties are as follows. ResultSetà None SQLStatementà exec TestDataRead SQLSourceTypeirectInput Parameter Mapping: VariableName: selected the user variable from the list. Direction: Output DataType:varchar Parameter Name: 0 Parameter Type: 20 When I run I am getting the following error.
SSIS package "TEST SSIS1.dtsx" starting. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec TestDataRead" failed with the following error: "Procedure 'TestDataRead' expects parameter '@TestString', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ........................................... ...................................................................
Everything works great on my development box. I am using GoDaddy for production (ASP.Net v2, SQL 2000). I am not receiving any errors, so I am stumped; no data from the database is displaying on the GoDaddy pages. I updated the connection string in web.config to this:
But I am unsure if this is the issue?? Any insights? This is the page I am working on: www.sugarandspicebakery.com/demo/bakery/default.aspx. So, the page displays fine, but it should be showing data from the database. This particular page uses a DataList with ItemTemplate. There is definitely data in the database, and I have even ran the same exact query from the code using the Query Analayzer on GoDaddt and it returned results I know there isn't much info to go by, but I am hoping someone has some insight since I have been trying to figure this out for days now! Thank youJennifer
Hi I wounder if there is a sp in the master db that can give me what usergroup a username is connected to / and or give me info if the username is valid (Windows NT login). If not: then how do I read what usergroup a username is connected to? sysxlogin table in master db? And can I connect to a sp in master db from another db? Can I create a sp in my db that reads in the master db? Thanks!! Best Regards Staffan
Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).
If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.
On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).
Environment:
Windows NT 4.0 (Service Pack 5 ) SQL Server 7.0 ( Service Pack 3 ) Multi Processor ( 2 - 500 Mhz )
In a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.
Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).
Alter Procedure spWod_rptWoStatusSummary_9
As BEGIN CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20)) INSERT INTO tblStatusSummary SELECT TckStaffCd_F, RecType END
Hello!I have two tables:Pressreleases:| Pressrelease_ID | PressDate | FilePath | Title |PressLinks:| PressLink_ID | PressLinkDate | PressLinkUrl | PressText |I would like to select the the TOP 3 with the most recent dates from either Pressreleases or PressLinks, and present them in a DataList. How do I do that? Selecting from one table is no problem:SELECT TOP 3 Pressreleases.PressDate, Pressreleases.Filepath, PressReleases.Title FROM Pressreleases ORDER BY PressDate DESCHow do I select from both tables and rename the columns to Date, Path, Text so that I can use it in a Datalist?Thank you in advance!
I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like:
[DatabaseCleaner] ! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd> ! -- <N> must be a successive number starting from 1 ... TableToBeCleaned_1=Transactions,24,24 TableToBeCleaned_2=Payments,24,24 TableToBeCleaned_3=PresenceTickets,24,24
As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP.
In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.
So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.
Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?
I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.
What role or system privilege do I need to grant to a user if he need to read the data from a table which is in a link server object? where I can find the document about these commands.
I'm reading a Flat File. The 1ste record containts special Info that is needed for first preparing the database on a buld insert of the remaining lines.
How could i realize this, just read 1 line, store some of that data into variables, execute some proc's and then read the rest of the file ?
I'm working on a report to show financial transactions from a table over a certain period. For most transactions there is a PDF document that is stored in a separate table in a binairy format. In my report I would like to include a link on every line with transaction information in the report that opens the PDF that is linked to that transaction. Just to be clear, I don't want to embed the PDF in the report but I want the users of the report to have the option to view the PDF that is related to that transaction in their standard pdf reader (adobe).
Code to do the following:
Once a user clicks on the link to view the PDF I need the code to get the binairy data of the PDF file from the table, convert it back to a PDF and open it in the default pdf reader (for example adobe reader). If it can't directly open the file then it's maybe possible to activate the 'open or download' pop up that you also get when you download something from a website.Â
What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task. I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert. Thanks
Hi, I'm having some troubles getting my UPDATE to work with my DataList control. I am trying to get Optimistic Concurrency working, but i'm getting an exception stating that the dictionary for oldValues is empty. I understand this is referring to the original values it compares with before performing the update, however, my question is how do I fill this dictionary in with the original values? I have included a code sample of the sort of thing i am trying to achieve, it is just a simplified example of how I am trying to UPDATE the underlying data source. Am I on the right track? What do I need to do in order to get this to work? Thankstrenyboy
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hello Guys,I am really not very happy with all the possibilities you have in ASP.Net other than in Classic ASP. Back then I made a loop in which you access to a Database with your SQL-Statement and fill the Recordset into HTML-Tables. Now I cannot really do that anymore (at least not in this existing project). My task is to display additional content.We are using the Timetracker-SDK and are displaying the hours spent on any projects.Now I shall add the number of bugs, suggestions and so on that are in the database for the specific project.An ObjectDatasource is accessing to a DotProcedure (written correctly?) and displaying it in a DataList.I really do not have any knowledge to modify itSo my question: Can I add a SqlDatasource and modify the "Select-" Statement in the runtime and add the Output to the Datalist?Thanks in advance on any reply here!Below the .ASPX-Part of the Site: <asp:ObjectDataSource ID="ProjectReportData" runat="server" TypeName="ASPNET.StarterKit.BusinessLogicLayer.Project" SelectMethod="GetProjectByIds"> <SelectParameters> <asp:SessionParameter Name="ProjectIds" SessionField="SelectedProjectIds" Type="String" /> </SelectParameters> </asp:ObjectDataSource> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:timetrackerConnectionString2 %>" SelectCommand="select Count(*) as Anzahl from issuetracker_issues where projectid =9 and issuecategoryid=16 and issuestatusid =17"> </asp:SqlDataSource> <table> <tr> <td colspan="2"> <asp:DataList ID="ProjectList" RepeatColumns="1" RepeatDirection="Vertical" runat="server" DataSourceID="projectReportData" OnItemCreated="OnProjectListItemCreated"> <HeaderStyle CssClass="header-gray" /> <HeaderTemplate> Project Report </HeaderTemplate> <ItemTemplate> <table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%"> <tr> <td valign="top"> </td> </tr> <tr> <td> <table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%"> <tr> <td width="180" class="report-main-header"> Project Name</td> <td width="70" align="right" class="report-main-header"> Est. Hours</td> <td width="100" align="right" class="report-main-header"> Actual Hours</td> <td width="100" align="right" class="report-main-header"> Est. Completion</td> <td width="100" align="right" class="report-main-header"> Issues</td> <td width="100" align="right" class="report-main-header"> Solved</td> <td width="100" align="right" class="report-main-header"> Reported</td> <td width="100" align="right" class="report-main-header"> Not Qualified</td> </tr> <tr> <td class="report-text"> <%# Eval("Name") %> </td> <td class="report-text" align="right"> <%# Eval("EstimateDuration") %> </td> <td class="report-text" align="right"> <%# Eval("ActualDuration") %> </td> <td class="report-text" align="right"> <%# Eval("CompletionDate", "{0:d}") %> </td> </tr> </table>
Hi all, I'm trig to create a simple cart in ASP.NET/C#. I've create a table named cart in which there are 2 field (user_id and id_product). In my page product I show all product and the cart icon (this is made by an asp.net image_button). When a person click on the cart there's and event in .cs which should insert the relative product in cart table. But how a can retrieve the product id?This is my .cs page: using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class provacarrello : System.Web.UI.Page{ private string connectionString = WebConfigurationManager.ConnectionStrings["MySQLconnection"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { } protected void ImageButton1_Click(object sender, ImageClickEventArgs e) { MembershipUser mu = Membership.GetUser(User.Identity.Name); Guid myGuid = (Guid)mu.ProviderUserKey; string insertSQL = "INSERT INTO cart ("; insertSQL += "userid, id_product)"; insertSQL += "VALUES("; insertSQL += "@userid, @id_product)"; SqlConnection myConnection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(insertSQL, myConnection); //Add the parameters cmd.Parameters.AddWithValue("@userid", myGuid); cmd.Parameters.AddWithValue("@id_product", DataList1.??? <--- ??? }} Thank u in advance!
I have written the following stored procedure to export a csv file. I am wanting to put a If statement in here so if view UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW returns nothing then the procedure does not run.
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_LINE_VIEW
I have created a link server in SQL Server 2005 to connect to Oracle 7.3. The driver in link server is MS-OLEDB. I have created a dynamic procedure in SQL Server 2005 using OpenQuery method to connect to Oracle. When I execute this procedure in SQL this gives me the results I want from Orcale.
In SSRS 2005, I am calling this procedure with the schema name.When I do Refresh,this should automatically list me the Parameters I am using in procedure. It is NOT listing me the AUTOMATIC generation of parameters.
