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++;
}
View 2 Replies
ADVERTISEMENT
Aug 1, 2007
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
View 1 Replies
View Related
Oct 25, 2007
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,
View 1 Replies
View Related
May 15, 2008
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?
View 4 Replies
View Related
May 5, 2006
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?
View 2 Replies
View Related
May 23, 2006
I have set the SelectQuery in the SqlDataSource, and it get the right data.
I want to get DataSet from a SqlDataSource in source codes, how to get it?
View 1 Replies
View Related
Feb 2, 2007
is there a way to copy a SqlDataSource Data into a dataset?
View 4 Replies
View Related
Aug 31, 2007
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).
View 2 Replies
View Related
Apr 21, 2008
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.
View 2 Replies
View Related
Mar 4, 2008
Hi,
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.
Any suggestions would be helpful....
Sincerely,
Baffled by SSRS
View 1 Replies
View Related
Mar 6, 2007
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?
View 5 Replies
View Related
Nov 3, 2015
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,
[Code] ....
View 3 Replies
View Related
Mar 14, 2007
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.
View 2 Replies
View Related
Mar 14, 2007
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.
View 1 Replies
View Related
Nov 8, 2007
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
View 1 Replies
View Related
Oct 26, 2007
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.
View 1 Replies
View Related
Nov 4, 2015
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.
View 2 Replies
View Related
May 5, 2008
Hi All,
I created one report having more than two datasets. How can I display the "No result found Message" If some of the dataset having no data.
Thanks,
Aneesh
View 4 Replies
View Related
Feb 11, 2008
How can I access a main dataset from a sub report from a main report using SSRS?
View 4 Replies
View Related
Feb 6, 2007
Data Set Access from a matrix
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?
View 1 Replies
View Related
Jan 2, 2008
Hi there!
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!
Thanks, Torsten
View 3 Replies
View Related
May 28, 2007
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
Dim rst_chg As Recordset
Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _
& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _
& "STMT=set wait_timeout=100000;;"
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?
Thanks,
Guy Rivers
View 1 Replies
View Related
Oct 26, 2007
Hi
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.
How can I get round this little problem?
View 1 Replies
View Related
Apr 6, 2007
Hello all!
I have a question. I have report that have defined dataset. Can I somehow get access to data in this dataset from this report in Code section?
I need to write function that will return value from one field based on 45 parameters (they are values from 4 fields in this datset), like:
dataset fields:
RYear, RMonth, AYear, AMonth, CAtegory, Amount.
I need to get Amount based on RYear, RMonth, AYear, AMonth, Category values that I need to pass as parameters to functions.
Thank you
View 3 Replies
View Related
Apr 25, 2007
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>
Any help would be appreciated.
Thank you
Lee
View 1 Replies
View Related
May 10, 2007
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)
View 3 Replies
View Related
Aug 27, 2007
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.
View 4 Replies
View Related
Oct 6, 2005
I use an SQL task to produce a result set which I store in a package variable.
View 15 Replies
View Related
Dec 7, 2003
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)!
(I've included the SP's below.)
Michael Dallas
/*********************** sp_CrossTab ******************/
CREATE procedure sp_CrossTab
@tablename varchar(255),
@crosscolumn varchar(255),
@crossrow varchar(255),
@crossvalue varchar(255)
As
-- 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)
--set @sql = 'select ' + @crossrow + ', ' + char(13)
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)
Set @sql = @sql + '''' +
convert(varchar(128), @colname) +
''' = sum(case convert(varchar(128), ' + @crosscolumn + ')'
+ char(13) + char(10) + space(8) +
' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '
if @i < @cols
set @sql = @sql + ', ' + @CrLf + space(4)
else
set @sql = @sql + @CrLf
fetch next from cross_sql into @colname
end
close cross_sql
deallocate cross_sql
set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow
if len(@sql) >= @max_statement_len begin
raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,1)
return 0
end
exec (@sql)
Select 'Sql' = @sql
set nocount off
RETURN 1
end
end
end
/***************** End sp_crosstab *****************/
/***************** execute_crosstabs ***************/
CREATE PROCEDURE execute_crosstabs
AS
exec sp_crosstab
@tablename = 'report_sales_summary_quotedate_calc',
@crosscolumn = 'Track',
@crossrow = 'QuoteDate',
@crossvalue = 'EstCom'
RETURN
/************** End execute_crosstabs ***************/
/***************** sp_MyTables ********************/
CREATE PROCEDURE [sp_MyTables]
AS
Exec sp_tables
RETURN
/***************** End sp_MyTables *****************/
View 1 Replies
View Related
Mar 27, 2007
A collegue of mine, came with the idea of using the SqlDataSource to connect to an Access database.
Is this good practice?
View 1 Replies
View Related
Aug 18, 2007
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?
View 2 Replies
View Related
Jan 1, 2006
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,
View 1 Replies
View Related
Mar 13, 2008
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 ?
thank you,
View 3 Replies
View Related