Programmatically Accessing An SQLDataSource With A SELECT COUNT(*) Query.
Jun 20, 2007
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be
Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim installtype As Integer = row("InstallMethod")
Dim install As String = row("Install").ToString
Dim notes As String = row("Notes").ToString
The above only works on a single row, of course. If I needed more, I know I can loop it.
The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"
My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query.
Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim count As Integer = row("rowcnt")
The query here is "SELECT COUNT(*) as rowcnt FROM Software"
The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value.
Where am I going wrong here?
Hi I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example???? If Not Page.IsPostBack Then 'Start by determining the connection string valueDim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 'Create a SqlConnection instanceUsing connString 'Specify the SQL query Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17" 'Create a SqlCommand instanceDim myCommand As New Data.SqlClient.SqlCommand(sql, connString) 'Get back a DataSetDim myDataSet As New Data.DataSet 'Create a SqlDataAdapter instanceDim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand) myAdapter.Fill(myDataSet)Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -?????????????? 'Close the connection connString.Close() End Using End IfThanks for any helpRichard
Hey All for some reason I can not get this right and/or find what I am looking for. I have an SQLDataSource with a PartID set as the filtered value in the Datasource Query. I am trying to use code beside to set the value and I am failing...lol... Here is my attempt at it, SqlDataSource1.SelectParameters("PartID") = txtPartID.Text Any help would be great!
Does anyone know if it is possible to access the execution plan results programmatically through a stored procedure or .NET assembly? I have the code sample
SET SHOWPLAN_XML ON; query... SET SHOWPLAN_XML OFF;
but it can only be run from the interface. I have tried a couple of solutions including dynamic sql to try to capture the results in a variable or file with no luck.
Does anyone know of a way to programmatically capture this information? We are doing some research with distributed query processing of dynamically generated queries using multiple processing nodes, and it would be helpful to know an estimate of how large the query is before sending it away to be processed.
I have looked at the dm_exec_query_stats view; but it can only be run on a query that has already been executed. I need to know the execution plan before the query is executed. If there is a way to get a query to show up in this view without being executed, then that would work as well.
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event. heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter PUser.ControlID = "lblUser" PUser.Name = "LoginName" PUser.PropertyName = "Text" PUser.Type = TypeCode.String PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource SQLDS_Login.ID = "SQLDS_Login" SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?" SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)" SQLDS_Login.SelectParameters.Add(PUser) SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
When i run, i get this error message: The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind. I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically. Can you help me with this?
I am trying to add a number of dates into a Sql database. Basically I want the user to add the start and end date and then all the dates in between are are added to a database in unique records. I can create an ArrayList but I don't know how to bind it to an SqlDataSource Dim startdate As DateTime = tbstartdate.Text Dim enddate As DateTime = tbenddate.Text Dim datediff As TimeSpan = enddate.Subtract(startdate) Dim noofdays As Integer = datediff.Days Dim ar As New ArrayList Dim i For i = 0 To noofdays ar.Add(startdate.AddDays(i)) Next Sorry if this is a total noob question....
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted. What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0]) Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user? Thank you very much for your help.
Background - I have a page that uses a numeric value stored in a Session object variable as the parameter for three different SQLDataSource objects, which provide data to two asp:Repeaters and an asp:DataList. Also, in the Page_Load, I use this value to to seed a stored procedure and an SQLDataReader to populate several unbound Labels. This works fine. In addition, I have a collection of 6 TextBoxes, an unbound Listbox, and two Buttons to allow the user to do searching and selection of potential matches. This basically identifies a new numeric value that I store in the Session variable and PostBack the page (via one of the buttons). This also works fine.Problem - I have been tasked with taking a different page and adding six textboxes to collect the search values, but to post over to this page, populate the existing search-oriented TextBoxes, adn programmatically triggering the search. Furthermore, I have to detect the number of matching records and, if only 1, have the Repeaters and DataList display the results based on the newly selected record's key numeric value, as well as populating the unbound Labels. I have managed to get all of this accomplished except for programmatically triggering the Repeaters and DataList "refresh". These controls only populate as expected if a button is clicked a subsequent time, which makes sense, since that would trigger a PostBack and the Page_Load uses the new saved numeric key value from the Session.My history in app development is largely from Windows Forms development (VB6), this is my second foray into Web Form dev with ASP.NET 2.0. I am willing to acceptthat what I am trying to do does not fit into the ASP environment, but I have to think that this is something that has been done before, and (hopefully) there is a way to do what I need. Any ideas, oh great and wise Forum readers? *smile*
ello all Would someone be so kind as to save me from getting balder through pulling my hair out. My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource. I have achieve the problem of retrieving the data from the sqlDataSource:DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty); //Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView; foreach (DataRowView rowProduct in openReminding) { //Output the name and price lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString(); } Within the sqlDataSource the sql code is as follows:SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @Number) So at the moment my problems is being able to edit and update data to the same SELECTed data.Thank you for any help that you might have... SynDrome
Hi, All I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed: <% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind(); dataSource.UpdateCommand = "";%> <asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView> The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row. Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues. The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key: ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY() This works fine when run from Sql Server Management Studio and @NewID has the correct value. My data access code is roughly as follows: dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value. Has anyone tried to do this and can tell me what I am doing wrong? Jon
...for instance i want to select * from lists where publishdate > todayDate() do i need to include todays date as a selectparameter? can you provide example please?
I need to provide defaults and sometimes overrides for items in SQLDataSource's UpdateParameters. I am attempting to do this in a FormView's ItemUpdating and ItemInserting events as follows: //======================================================================== // FormView1_ItemUpdating: //======================================================================== protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e) { // not sure if this is the bets place to put this or not? dsDataSource.UpdateParameters["UpdatedTS"].DefaultValue = DateTime.Now.ToString(); dsDataSource.UpdateParameters["UpdatedUserID"].DefaultValue = ((csi.UserInfo)Session["UserInfo"]).QuotaUserID; } In the example above I am attempting to set new values for the parameters which will replace the existing values. I have found that using the DefaultValue property works ONLY if there is no current value for the parameter. Otherwise the values I specify are ingnored.The parameters of an ObjectDataSource provide a Value property but SQLDataSource parameters do not.How can I provide an override value without needing to place the value in the visible bound form element???If you can answer this you will be the FIRST person ever to answer one of my questions here!!!Thanks,Tony
I just want to insert a record into a table using a SqlDataSource control. But I'm having a hard time finding examples that don't use data bound controlsI have this so far (I deleted the parts not related to the insert):<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:UserPolls %>" InsertCommand="INSERT INTO [PollAnswers] ([PollId], [AnswerText], [AnswerCount]) VALUES (@PollId, @AnswerText, @AnswerCount)" <InsertParameters> <asp:Parameter Name="PollId" Type="Int32" /> <asp:Parameter Name="AnswerText" Type="String" /> <asp:Parameter Name="AnswerCount" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> This is the data source for a gridview control I have on the page. I could set up an SqlDataSource for this alone if I need to, but i don't know if it would help. From what I could find, in the code behind I should haveSqlDataSource2.Insert()and SqlDataSource2 will grab the parameters and insert the record. The problem is I need to set the Pollid (from a session variable) and AnswerText (from a text box) at run time. Can I do this?Diane
Here's what I'm trying to do: 1. Output each country in one column 2. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 1 in the next column 3. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 2 in the next column
My problem was that I was doing joins, and I was somehow ending up with orders where the OrderTypeID was NOT equal to 3, even though I declared it specifically in the WHERE clause.
Hi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically? <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
Im ripping my hair out here.I need to access the field in a datasource control of use in non presentation layer code based actions.I know the I can use a code base connection and query but I dont see why i need to make two trips the the DB when the info is already available.The datasource is attached to a details view control and the details view control is nested in a loginview controlI've tried defining but all I can get in the header name of the field but not the dataitem, the dataitem causes an error help please jim
I am sending a GUID to a form via the query string. If it exists I use helper functions to load most of the form text boxes. However, if it does not then a blank form is presented and the GUID is stored in a hidden field. Regardless, I use this hidden field to populate a grid that is attached to a sqldatasource. If I then add new datarows to the backend database programmatically, I cannot 'requery' the datasource to include those row upon a postback. I cannot seem to find a simple way to force the sqldatasource to rerun the query. Can anyone help.
Hello, I want to loop through the first 10 records that are showing in a gridview with several pages that is populated by a sqldatasource. I can loop through the sqldatasource and get the list of values, but I'm doing something wrong because the 10 records it prints out are not the same 10 records the user sees in the gridview...They can click a search button which changes the sort, and they can click on the column headings to change the sort order. Where's the best place to put the looping code? I need the result to be the same as what the users sees. 1 Protected Sub GridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Sorted2 Dim i As Integer = -13 Dim sTest As String = ""4 Dim vwExpensiveItems As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView)5 6 'Loop through each record7 i = -18 For Each rowProduct As Data.DataRowView In vwExpensiveItems9 i = i + 110 'Output the name and price11 If i > 9 Then12 Exit For13 End If14 sTest = rowProduct("employeeid")15 Response.Write("RowSorting " & i.ToString & " [" & sTest & "]<br>")16 Next17 End Sub18
Dear MS SQL Experts,I have to get the number of datasets within several tables in my MSSQL2000 SP4 database.Beyond these tables is one table with about 13 million entries.If I perform a "select count(*) from table" it takes about 1-2 min toperform that task.Since I know other databases like MySQL which take less than 1 sec forthe same taskI'm wondering whether I have a bug in my software or whether there areother mechanisms to get the number of datasets for tables or the numberof datasets within the whole database.Can you give me some hints ?Best regards,Daniel Wetzler
I have a SqlDataSource that returns a list of companies and their details by ProductID. It also returns the name of the product associated with the ProductID as the final column (which means it appears for every record returned). I already have a way of determining how many rows were returned, and use that information in a label to say "Your search has returned x records". protected void dsGetSuppliersByProduct_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; if (RecordCount == 0) { lblRecordCount.Text = "<p>No Records found"; } else { if (RecordCount == 1) { lblRecordCount.Text = "<p>Your search returned 1 record"; } else { lblRecordCount.Text = "<p>Your search returned " + RecordCount + " records"; } } string ProductName; }How can I access the ProductName value so that I can extend the label text to say "Your search has returned x records for <ProductName>" ?
I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?
I have following query in stored procedure Select ContractId, Version, CreateDate, body, IsCurrentFrom CRM_Contracts where ContractId=@ContractId I am trying to access the value of IsCurrent field in Code behind using sqlDataSource to hide a radiobutton list in Formview control's when it is in Edit Mode. How can i do this.
I am using a drap and drop SQLDataSource control. Everything works like I am wanting it to, but I want to check a value from one of the fields that I have retrieved. I am lost as how to retreive just the field. If I have to create another dataset to do the work what is the point of using the drag and drop control? I would really like to just access the dataset fields as needed.
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
First let me give you a little back ground on me. I'm very new to the ASP, ASP.NET, Visual Studio, Sql Server, Frameworks....thing. I am coming over from a PHP/MySql background of over 5 years. The change over to VBScript and VB has been to tough and I have a basic liking for the Visual Studio 2005 and the ease of putting things together. However, I have come across something that to me seems like it should be relatively simple, but haven't been able to find the documentation or samples to describe what I'm looking to do. Rough Need: 1. Start with a form will a couple labels and a singe textbox to get the lookup date from an user. 2. Query one table/view based on the users choice of date and select only one field of returned data.(Doing this by itself is not a problem and I can display my results in a Gridview, but this is where it starts getting tricky and the gridviews won't work for me.) 3. If there is something returned, I need to start a HTML table layout or possibly some form of a Gridview(I don't see how I would use the Gridview) and start a loop, adding the first returned row from this query into the first cell. 4. Now, based on that same User Date and the returned row value from the previous query, I need to query another Table/view and return another single field, which might return 1 or multiple rows, which I need to start a loop to display unique items in cells under the first on above. 5. Based on the original User Date and each returned row from the first query, I need to query two other Table/views and get some additional information. 4-5 fields will be returned and be displayed on one row in the number of necessary columns. 6. This would finish the first row from the first query, so I would need to loop back up to see if there were any further results and continue looping until the get to the last of the results from the first query. 7. Finally, I would just need to close up the Table/gridview. The basic results I'm looking for would be similar to the following: Results for 10/11/2005
Field name 1 Field name 2 Field name 3 Field name 4 Field name 5
First Result from query 11 of 4 results from Query 2, but only 1 unique result
Info from Query 3
Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3. Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4
Second Result from query 1First result from Query 2 after looping though the first query Second result from Query 2 after looping though the first query
Info from Query 3
Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3. Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4 It seems me from what I have read and slowly figuring out, is that I should be able to directly access the DataSet returned by four SqlDataSources, one for each of the above querys and then just write my own VB to handle the necessary looping, table format and such. I can easily add the for SqlDataSources to the page and add a Gridview for each one and get 4 separate chunks of info, but can't see a way with GridViews to intermingle the info like displayed above. So, if it can be done with Gridviews, then I would love to see how that is done. But, if someone could explain to me how I access the DataSets directly that I get from the 4 SqlDataSources, then that would be ok too. I have figured enough out with VB, that I can write the code to do my looping requirements, if I can just access the information I get back. Thanks for reading all the way through this long post. Jack
Can anyone point me in the direction of code that will allow me to acce3ss the data held in an SQLDataSource.
The Control is link Selection Staement has been set at run, what I want to do is loop thru any data that is rertrieved, setting oter controls on the page depending on values Thanks Steve (I have not got much hair left, please help soon)
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
hello, this is my sql query with access: Dim SQLstr2 As String = "Select count(*) as total, oDate from Order_Details where oNo = " + Request("oNo") + " group by oDate"
However, problem is, I am clueless about how to access the data that is derived from my count(*).
I tried this: Response.Write(reader2("total"))
All I get is that No data exists for the row/column.
I tried not to select oDate and even took away the WHERE, but the same problem persists. Any ideas please?
Hi,This is a very simple question but I don't have any idea of how to do it.Says I have a table with 50 records. How do I know the number of record have been return by sqldatasource when it execute a SELECT sql statement that contains a WHERE clause. Says the 30 records match the SELECT statement, what code do I have to write in order to display the number 30? Thanks
