Access DB Via SqlDataSource....need Result In DataSet
Jun 21, 2006
Hi all...I've set the DataSourceMode = SqlDataSourceMode.DataSet, and did a .Type return and found that it is actually returning a DataView. A component I am trying to avoid rewriting....requires a dataset that loops through the table, does some cool formatting to a datagrid and then rebinds.
Here's the code that I'm trying to send the dataset to....maybe there's just a couple of changes that could make it work with a DataView?
int i = 0;
string prevsub = "";
while (i <= ds.Tables[0].Rows.Count - 1)
{
DataRow dr = ds.Tables[0].Rows[i];
string sub = dr["SubHeading"].ToString();
if (sub != prevsub)
{
prevsub = sub;
DataRow newrow = ds.Tables[0].NewRow();
newrow["Title"] = "SubHeading";
newrow[columnName] = dr[columnName];
ds.Tables[0].Rows.InsertAt(newrow, i);
i++;
}
i++;
}
hi, i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case. How to catch the resultset of SQLDataSource? -nero
Dear sir or madamI have a problem related to using sqldatasource and dataset.I heard that using dataset is faster than using sqldatasource,but I think that in sqldatasource have a DataSourceMode property allowing user to choose to use with dataset or datareader.Now,I still wonder why they said that dataset is faster than sqldatasource. I look forward to hearing from you.Thank you in advance.Best wishes,
I can find lots of examples of how to extract a DataSet from a SqlDataSource, but not the opposite. I have a DataSet that has been populated with 3 DataTables, and I want to create a SqlDataSource, and populate it with the first DataTable in the DataSet. Is this possible?
Hi At the moment I gave a function (GetProfile) which returns a dataset (dSet) something like this Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)Dim dSet As System.Data.DataSet = New System.Data.DataSetdataAdapter.Fill(dSet) return dSet I am using the .Tables property of the dataset to loop round the values. like this (below), I can't use the SQLDataSource control to bind to a form or controls due to the functionality of what I'm doing. Dim dataSet As System.Data.DataSet = GetProfile(UserID) With dataSet If .Tables(0).Rows.Count = 1 Then With .Tables(0).Rows(0) (some code like) lblName.Text = .Item("Name").ToString Page.Title = "Member profile::" & lblName.Text etc... End With End If End With So my question: is there a way of using an SQLDataSource control to populate a dataset rather than using my function?
How does one programmatically retrieve the results from a sqldatasource that has had a filterexpression applied to it? Let me elaborate, here is my code: SqlDataSource1.FilterExpression = "MenuType = 'myfilteredvalue'" Dim _dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView) _dv.Table.TableName = "Menu" Dim ds As New DataSet("Menus") ds.Tables.Add(_dv.Table.Copy()) 'Add relation: ParentID under MenuID Dim relation As New DataRelation("ParentChild", ds.Tables!Menu.Columns!MenuID, ds.Tables!Menu.Columns!ParentID, True) relation.Nested = True ds.Relations.Add(relation) What I'm doing is, I have a sqlDataSource that pulls all the data from my DB. With this data in the sqlDataSourceSource, I want to then populate an XMLDatasource (via a dataset) with only the records I need, i.e. the filter. However, after I apply my filter above, the sqlDataSoruce still contains all the records. I thought maybe if I did a sqlDataSource.Bind() after my SqlDataSource.FilterExpression, but that didn't do anything either.In laymans terms:I want to say something like: dataset = sqldatasource.filter(my filter expression).
hi,I have a page Price List with GridView to display only two columns of a table (Product name & price). Page size=20. I use SqlDataSource for it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"In my case the SqlDataSource control return data as a DataSet object. So the DataSet object contains all the data in server memory. I need to create Print Preview page with other columns (Product name & price & Vendor and Date Issue) without paging.I'm going to save dataSet in Session and in Print Preview page use it as datasource (without having to run another query).But I have no idea how to save this DataSet in Session. I don't know the DataSet Name. Any ideas? Thanks.
Running into a quirk with creating a new dataset in RS 2005.
The dataset is based on a SQL proc, which I can identify and execute in the Data tab page and see the correct results when matched against the same params in SQL Studio.
However, the DataSets window only lists a single field call "ID" under my available fields listing, even though I can see all the correct fields in the pane right next to.
The structure of the sProc is similar to many that I have create reports on previously.
I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?
Trying to create a dataset for a report. I need to bring back percentage in the result set. The fields that I am using to get the percentage have valid data but the result field is 0.00%. Is there reason I cant bring back a percentage field?
Code: SELECT JobNum, Mailed, LT_7, BT_6_10, GT_10, Format([LT_7]/[Mailed], 'p') AS PctScaned1, Format([BT_6_10]/[Mailed], 'p') AS PctScaned2, Format([GT_10]/[Mailed], 'p') AS PctScaned3,
I have an SqlDataSource that has SelectCommand as Stored Procedure. This stored procedure returns two tables. But my SqlDataSource can see first table only. How can I see second table in Selected event of my SqlDataSource for example ? SqlDataReader has method NextResult() for this task. How in SqlDataSource ? I'm sorry for my English.
I have an SqlDataSource that has SelectCommand as Stored Procedure. This stored procedure returns two tables. But my SqlDataSource can see first table only. How can I see second table in Selected event of my SqlDataSource for example ? SqlDataReader has method NextResult() for this task. How in SqlDataSource ? I'm sorry for my English.
hi all..... i select some records from my database using mysqldatareader.......but i want to load it's result sets into my datatable or dataset.... is it possible and how can i do it this way ? thanks
Ive run into a situation where some code im using will not function the same when between these two cases. So, this has me wondering what is going on behind the scenes. What is the sequence of events that are occurring that would possibly be messing things up.Here is the sample codeIts a gridview that is using a sqldatasource control. The code works fine, but if you want to bind the grid to a dataset and call databind yourself, things dont work as expected and the other features that the code performs just isnt happening, at least not for me.
I'm using Report builder 3.0 to create a report. In the design, datatset query i have a sql to calculate difference between two dates (date1-date2) as processing time. I have this (processing time ) ONLY in my ORDER by clause and when i execute the statement it does exactly what i was looking for , however when i run the report the sort order is not delivered. I checked each column tablix properties and the sorting order is set for Processing Time.
I am trying to create a schedule by room number report. I am using a matrix. The column group that I am using is room number. There is no row group. When you group by room it is only allowing me access to the first data item for that room. Even though there are other data items for that room I can not access them. I can not access all data items in that grouping expression. Is there a way to get unobstructed access to a dataset while working in a matrix and grouping?
For setting different languages on the column haeders i need to access a dataset from a custom code function call. I want to pass a parameter to the function that will allow me to find a specific item in the dataset - like a lookup function. I cannot find a way to get access to the entire dataset and to iterate through its components.
Is there any solution? Every hint will be helpful!
I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?
My VBA code in Access used to be this:
Code Snippet
Dim ws As Workspace Dim strConnection As String Dim dbs As Database
Set dbs = ws.OpenDatabase("", True, True, strConnection)
Set rst_chg = dbs.OpenRecordset("SELECT ...")
'process the recordset ...
I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?
I'm all very new to SQL Reporting Services so I am hoping that someone will be able to help me.
I have two datsets. Both contain the same array of information pertaining to a particular site. For example, how much sales we had, how much revenue was made, how much commission was created at the end of each day, the usual kind of metrics. We have a stored procedure which takes a final total and puts into a table we can access by date.
I want to produce a report that can compare the data from two dates. I use two datasets to run the quries that will return the relevant data. The only thing that differs between the dataset is the date that the data is based on. So to say first dataset will have data on one date and the second dataset will have data on a another date.
I was trying to make a table where I could include fields from both datasets, mainly for making comparison easier.
so id have
columns online hits (dateone) | online hits (datetwo) Rows Data date1 | Data date2
and so on.
in fact i had this as the data from the first dataset in an example field: =Fields!OnlineSales.Value
but i couldnt get the second dataset to work even if i tried entering : =(Fields!OfflineBookings.Value, "SecondDatePicker")
I can't get the table to include field results from the second dataset as a table can only be linked to one dataset.
Hello, Is it possible to access both my database and the ASPNETDB within the same SQLDATASOURCE using SQL Express ? I want to pull out some user details from the aspnet_* tables within the ASPNETDB In my database I have created the 'UserId' fields so the relevant joins can take place. In my web.config: <connectionStrings> <add name="mydb" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|mydb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings> In my asp (showing only mydb): <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:mydb %>" SelectCommand="SELECT [CreateDtTime], [UserId], [Comment] FROM [Log_Dtl] WHERE ([LogId] = @LogId)"> <SelectParameters> <asp:QueryStringParameter Name="LogId" QueryStringField="LogId" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
I am trying to acces an SQLDatasource in the code page, I have the following code but get the error as below, any one help please The SQLDataSource returns 1 value named [ShippingRegion], I think that has somethjing to do with it ??!! dsShippingRegion.Select(DataSourceSelectArguments.Empty)Dim myReader As Data.IDataReader = CType(dsShippingRegion.Select(DataSourceSelectArguments.Empty), Data.IDataReader)If myReader.Read Then If Convert.IsDBNull(myReader("ShippingRegion")) Then Beep() Else Beep() End IfEnd If System.InvalidCastException was unhandled by user code Message="Unable to cast object of type 'System.Data.DataView' to type 'System.Data.IDataReader'." Source="App_Web_rd5quiy1" StackTrace: at admin_administer_shop_productaddnew.Page_Load(Object sender, EventArgs e) in E:Web DevelopmentWebSitesAJAX_sirs2hersadminadminister_shopproductaddnew.aspx.vb:line 25 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) dsShippingRegion.Select(DataSourceSelectArguments.Empty)
Hello all,I have what I think should be a simple problem to solve but I just don't quite understand what to do (.NET Framework Developer Center confused me :( ).My code should load the highest SiteID, known as Last_ID into the siteIdBox when the form is in insert mode. My difficulty is in understanding how to use the sqlDataSource correctly. Dim dsLastId As New SqlDataSource() Dim siteIdBox As TextBox Dim dsmLastId As SqlDataSourceMode If FV_Site.CurrentMode = FormViewMode.Insert Then Try dsLastId.ConnectionString = ConfigurationManager.ConnectionStrings("AquaConnectionString1").ToString dsLastId.SelectCommandType = SqlDataSourceCommandType.Text dsLastId.SelectCommand = "SELECT MAX(Site_ID) AS Last_ID FROM Tbl_Site"
dsmLastId = SqlDataSourceMode.DataReader
siteIdBox.Text = dsmLastId.?????
Finally dsLastId = Nothing End Try End If If anyone could tell me the code for getting the value from the select statement into the textbox, I would be most grateful.Thank you, m00sie.
SPs in Access 2000 (SQL) / Crosstab problem / returning dataset I've recently "upsized" from Access97 (Jet) to Access 2000 (SQL) client/server using MS SQL Server 2000. As a result, I'm new to the concept of Stored Procedures. I am trying to work out a general solution to the fact SQL doesn't allow an easy way to create dynamic crosstab queries (from within Access client/server).
I've included the SP code I found (sp_crosstab) to create the crosstab solution. To execute the sp_crosstab, I use another SP (execute_crosstabs) which defines the input parameters.
If I run the SPs in Query Analyzer, the results are returned as a dataset. However, if I run them in MS Access 2000, the following message is returned:
"The stored procedure executed successfully but did not return records." Likewise, if I attach an Access form to the SP, it returns the same message.
I've seen ADO code which could return the records (to Access), but I would prefer an alteration to the SP (sp_crosstab) which would return the records automatically.
For example, if I run the SP below (sp_MyTables which executes sp_tables), a dataset is returned automatically instead of the message "The stored procedure executed successfully but did not return records." If I attach sp_MyTables to an Access form, the records are returned in the form as well.
My question is this: How can I get sp_crosstab to act like sp_tables (executed by sp_MyTables) to return a dataset instead of the infernal message?
I've looked all over the Internet and have not seen this issue addressed directly. Your help would be EXTREMELY appreciated (and will probably make Internet history)!
-- Work variables declare @ReturnSet varchar(255), @sql varchar(8000), -- Hold the dynamically created sql statement @colname varchar(255), -- The current column when building sql statement @i smallint, -- know when we reached the last column (@i = @cols) @cols smallint, -- Number of columns @longest_col smallint, -- the len() of the widest column @CrLf char(2) -- Constants declare @max_cols_in_table smallint, @max_col_name_len smallint, @max_statement_len smallint, -- @sql7 bit, -- 1 when version 7, 0 otherwise. @err_severity int
set nocount on
set @max_cols_in_table = 255 set @max_statement_len = 8000 set @max_col_name_len = 128 set @err_severity = 11 set @CrLf = char(13) + char(10)
-- Check inputs if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin raiserror ('Missing parameter(s)!',@err_severity,1) return @@rowcount end
-- Check for existence of the table. if (not exists(select * from sysobjects where name like @tablename))begin raiserror ('Table/View for crosstab not found!',@err_severity,1) return 0 end
-- Don't check for columns because we may actually get an expression as the column name
-- prepare for future feature of checking database version to validate -- inputs. Default to version 7 --set @sql7 = 1 --if (patindex('%SQL Server 7.%',@@version) = 0) begin -- set @sql7 = 0 --end
-- Extract all values from the rows of the attribute -- we want to use to create the cross column. This table -- will contain one row for each column in the crosstab. create table #crosscol (crosscolumn varchar(255)) set @sql = ' insert #crosscol Select Distinct ' + @crosscolumn + ' From ' + @tablename --+ --' Group By ' + @crosscolumn --print @sql exec (@sql) set @cols = @@rowcount
if @cols > @max_cols_in_table begin raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1) return 0 end else begin if @cols = 0 begin raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1) return 0 end else begin -- Check if any of the data is too long to make it a name of a column select @longest_col = max(len(convert(varchar(129),crosscolumn))) from #crosscol
if @longest_col > @max_col_name_len begin raiserror ('Value for column name exceeds legal length of column names',@err_severity,1) return 0 end else begin
-- All Validations OK, start building the dynamic sql statement
set @sql = '' -- Use tmp table rows to create the sql statement for the crosstab. -- each row in the table will be a column in the cross-tab set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As ' + @crossrow + ', ' + @CrLf + space(4)
declare cross_sql cursor for select crosscolumn from #crosscol order by crosscolumn
--print 'Sql cross statment: ' + @sql
open cross_sql fetch next from cross_sql into @colname -- Use "@i" to check for the last column. We need to input commas -- between columns, but not after the last column set @i = 0 while @@FETCH_STATUS = 0 begin set @i = @i + 1 set @colname = isnull(@colname,'Undefined') set @crossvalue = isnull(@crossvalue, 0)
I have <asp:SqlDataSource> in aspx page that is being used by an updateable GridView. However, there are a couple fields in the dataset that contain filenames that I want to access directly in code and place them into some image tags on the page. This line should give me access to the dataset but how to I access the fields? Dim myDataSource As DataView = DirectCast(SqlDataSourceGridView.[Select](DataSourceSelectArguments.Empty), DataView) When the SqlDataSource is a DataReader I would access it with the code below but since this SqlDataSource is a DataSet I can't access it with this code.If myDataSource.Read Then If Convert.IsDBNull(myDataSource("CusAgentPhoto")) Then ImageAgent.ImageUrl = "/photos/nophoto.gif" Else ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString End If If Convert.IsDBNull(myDataSource("CusCompanyLogo")) Then ImageCompany.ImageUrl = "/photos/nophoto.gif" Else ImageCompany.ImageUrl = OfficeImagePath & myDataSource("CusCompanyLogo").ToString End IfEnd If What would be the correct way to get at the DataSet fields that contain the filenames?
How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup? I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control Many thanks,
Good morning, I'm trying to create a view on SQL Server based on a query that i use on Access. The query returns detailed data from a table based on ID's selected and saved into another table.
the thing is, on access it returns back 5 records<which is a correct output> , while on SQL. its returning only one record. what could be the issue here ?