Accessing data from a programmatically created SqlDataSource

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??

IfNot Page.IsPostBackThen

'Start by determining the connection string value

Dim connStringAsNew Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

'Create a SqlConnection instance

Using connString

'Specify the SQL query

Const sqlAsString ="SELECT eventID FROM viewEvents WHERE eventID=17"

'Create a SqlCommand instance

Dim myCommandAsNew Data.SqlClient.SqlCommand(sql, connString)

'Get back a DataSet

Dim myDataSetAsNew Data.DataSet

'Create a SqlDataAdapter instance

Dim myAdapterAsNew Data.SqlClient.SqlDataAdapter(myCommand)

myAdapter.Fill(myDataSet)

Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -??????

'Close the connection

connString.Close()

EndUsing

EndIf

Thanks for any help
Richard

No, you haven't programmatically created a SqlDataSource. You have used plain ADO.NET code to create and fill a DataSet. The DataAdapter is purely a bridge between the dataset and your data source (the database). It doesn't contain tables. The Dataset does though. It holds them in a zero-based collection:

Label1.Text = MyDataSet.Tables(0).Rows(0)("eventID").ToString()

But if all you want is one value from the database, you are better off using Command.ExecuteScalar():

Dim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Using connString
Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17"
Dim myCommand As New Data.SqlClient.SqlCommand(sql, connString)
Label1.Text = mycommand.ExecuteScalar().ToString()
...etc

Access user and system DSNs programmatically

Just as the ODBC Data Source Administrator lists user and system DSNs, I wan
t
to access the same information programmatically in C#. I found registry
entries for these but I was hoping there was a higher-level routine that
would provide the information. The OdbcFactory.CreateDataSourceEnumerator
looked promising at first, but it seems to list just instances of SqlServer.
Is there a .NET method that could provide this information?
If I have to use a registry lookup, how do I determine the path for the
current user in the registry?Hi,
I understand that you would like to know how to list all the DSNs installed
on a computer programmatically in C#.
If I have misunderstood, please let me know.
I recommend that you utilize either of the following two methods:
1. Use P-Invoke in C# to call SQLDataSources.
You may refer to the following articles:
SQLDataSources(ODBC32)
http://www.pinvoke.net/default.aspx...ataSources.html
Listing Available DSN / Drivers Installed
http://www.codeguru.com/vb/gen/vb_d...icle.php/c2045/
2. Write the code to analyze the following information respectively:
System DSNs are stored in the registry at:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
User DSNs are stored in ODBC.INI file that is located at:
C:\WINNT
File DSNs are stored in the following directory:
C:\Program Files\Common Files\ODBC\Data Sources
Note that this response contains a reference to a third party World Wide
Web site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.
If you have any other questions or concerns, please feel free to let me
know. Happy New Year!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||I tried to incorporate from the code samples you pointed to, but I did not
know how to properly reference the function calls (i.e. SQLDataSources,
SQLSetEnvAttr, SQLAllocHandle) that are apparently from the odbc32.dll.
However, I did manage to find a simple implementation of accessing the info
from the registry at http://www.thescripts.com/forum/thread269514.html. I
refactored the code from that thread into this short solution:
public static SortedList listAllDSN()
{
SortedList allDSN = new SortedList();
// Get User DNS Names
GetOdbcRegistryValues(allDSN, Registry.CurrentUser);
// Get System DNS Names
GetOdbcRegistryValues(allDSN, Registry.LocalMachine);
return allDSN;
}
private static void GetOdbcRegistryValues(SortedList allDSN, RegistryKey reg
)
{
reg = reg.OpenSubKey("Software");
reg = reg.OpenSubKey("ODBC");
reg = reg.OpenSubKey("ODBC.INI");
reg = reg.OpenSubKey("ODBC Data Sources");
if (reg != null)
{
foreach (string s in reg.GetValueNames())
{
try { allDSN.Add(s, null); }
catch { }
}
}
try { reg.Close(); }
catch { }
}|||Hi,
Thanks for your response.
SQLDataSources, SQLSetEnvAttr and SQLAllocHandle are indeed included in
odbc32.dll. That is why you need to use P/Invoke technology in C#/VB.NET to
call them. You need to re-declare them in C#. The first link should have
introduced how to invoke it:
//1. import it and redeclare it in C#
[DllImport("odbc32.dll", CharSet=CharSet.Ansi))]
static extern short SQLDataSources(IntPtr EnvironmentHandle, short
Direction,
StringBuilder ServerName, short BufferLength1, ref short
NameLength1Ptr,
StringBuilder Description, short BufferLength2, ref short
NameLength2Ptr);
//2. Call it directly
....
rc = SQLDataSources(sql_env_handle, SQL_FETCH_FIRST, dsn_name,
(short)dsn_name.Capacity, ref dsn_name_len, desc_name, (short)
desc_name.Capacity, ref desc_len);
For SQLAllocHandle and SQLSetEnvAttr, you can find them in the web site:
http://www.pinvoke.net/default.aspx...llocHandle.html
http://www.pinvoke.net/default.aspx...SetEnvAttr.html
Anyway I am glad to hear that you had found the resolution by yourself. If
you have any other questions or concerns, please feel free to let me know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Access user and system DSNs programmatically

Just as the ODBC Data Source Administrator lists user and system DSNs, I want
to access the same information programmatically in C#. I found registry
entries for these but I was hoping there was a higher-level routine that
would provide the information. The OdbcFactory.CreateDataSourceEnumerator
looked promising at first, but it seems to list just instances of SqlServer.
Is there a .NET method that could provide this information?
If I have to use a registry lookup, how do I determine the path for the
current user in the registry?
Hi,
I understand that you would like to know how to list all the DSNs installed
on a computer programmatically in C#.
If I have misunderstood, please let me know.
I recommend that you utilize either of the following two methods:
1. Use P-Invoke in C# to call SQLDataSources.
You may refer to the following articles:
SQLDataSources(ODBC32)
http://www.pinvoke.net/default.aspx/odbc32/SQLDataSources.html
Listing Available DSN / Drivers Installed
http://www.codeguru.com/vb/gen/vb_database/article.php/c2045/
2. Write the code to analyze the following information respectively:
System DSNs are stored in the registry at:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
User DSNs are stored in ODBC.INI file that is located at:
C:\WINNT
File DSNs are stored in the following directory:
C:\Program Files\Common Files\ODBC\Data Sources
Note that this response contains a reference to a third party World Wide
Web site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.
If you have any other questions or concerns, please feel free to let me
know. Happy New Year!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||I tried to incorporate from the code samples you pointed to, but I did not
know how to properly reference the function calls (i.e. SQLDataSources,
SQLSetEnvAttr, SQLAllocHandle) that are apparently from the odbc32.dll.
However, I did manage to find a simple implementation of accessing the info
from the registry at http://www.thescripts.com/forum/thread269514.html. I
refactored the code from that thread into this short solution:
public static SortedList listAllDSN()
{
SortedList allDSN = new SortedList();
// Get User DNS Names
GetOdbcRegistryValues(allDSN, Registry.CurrentUser);
// Get System DNS Names
GetOdbcRegistryValues(allDSN, Registry.LocalMachine);
return allDSN;
}
private static void GetOdbcRegistryValues(SortedList allDSN, RegistryKey reg)
{
reg = reg.OpenSubKey("Software");
reg = reg.OpenSubKey("ODBC");
reg = reg.OpenSubKey("ODBC.INI");
reg = reg.OpenSubKey("ODBC Data Sources");
if (reg != null)
{
foreach (string s in reg.GetValueNames())
{
try { allDSN.Add(s, null); }
catch { }
}
}
try { reg.Close(); }
catch { }
}
|||Hi,
Thanks for your response.
SQLDataSources, SQLSetEnvAttr and SQLAllocHandle are indeed included in
odbc32.dll. That is why you need to use P/Invoke technology in C#/VB.NET to
call them. You need to re-declare them in C#. The first link should have
introduced how to invoke it:
//1. import it and redeclare it in C#
[DllImport("odbc32.dll", CharSet=CharSet.Ansi))]
static extern short SQLDataSources(IntPtr EnvironmentHandle, short
Direction,
StringBuilder ServerName, short BufferLength1, ref short
NameLength1Ptr,
StringBuilder Description, short BufferLength2, ref short
NameLength2Ptr);
//2. Call it directly
.....
rc = SQLDataSources(sql_env_handle, SQL_FETCH_FIRST, dsn_name,
(short)dsn_name.Capacity, ref dsn_name_len, desc_name, (short)
desc_name.Capacity, ref desc_len);
For SQLAllocHandle and SQLSetEnvAttr, you can find them in the web site:
http://www.pinvoke.net/default.aspx/odbc32/SQLAllocHandle.html
http://www.pinvoke.net/default.aspx/odbc32/SQLSetEnvAttr.html
Anyway I am glad to hear that you had found the resolution by yourself. If
you have any other questions or concerns, please feel free to let me know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Access SQL functions through .net?

I usually access stored procedures using SQL data source. But now I need a string returned from the database. If I write a function in SQL how do I access it from an aspx.vb file?

Put it in a proc and run an EXEC query, same way as any other procedure.

Jeff

|||

If the SQL function returns a string, why not use SqlCommand.ExecuteScalar method:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
{
conn.Open();

string qstring = "SELECT dbo.fn_test('IORI')";

SqlCommand cmd = new SqlCommand(qstring, conn);
string s=cmd.ExecuteScalar().ToString();

Response.Write("The new string is:" + s);
}

|||Cool! Thanks guys!

Access shared data source inside custom assembly

I have a database connection string hard-coded in a SQL Server 2005 Reporting Services custom assembly. Since the connection string is environment-specific (dev/prod), I would like to read the connections string from a settings file, like is typically done for web.config. Using the shared data source is also a good option.

How do I accomplish this?

I looked into trying to use DataSourceReference(), but that does not seem to be allowed inside of a custom assembly. Thank you.

Only private data sources can be expression-based. You can think of the Report Server as a web application since it runs under IIS. Therefore, you can put your config settings in the C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\web.config just like you would do with a regular ASP.NET app. Assuming you've deployed your report to the report catalog, you can read these settings at runtime . Additional considerations:

1. This technique is not supported.

2. Since there is no HttpContext in in the VS.NET Report Designer, you need to check for this condition and default your settings so you report works in design mode. I have a report in this download that demostrates how you can do this.

Access shared data source inside custom assembly

I have a database connection string hard-coded in a SQL Server 2005 Reporting Services custom assembly. Since the connection string is environment-specific (dev/prod), I would like to read the connections string from a settings file, like is typically done for web.config. Using the shared data source is also a good option.

How do I accomplish this?

I looked into trying to use DataSourceReference(), but that does not seem to be allowed inside of a custom assembly. Thank you.

Check Jason's blog here

http://weblogs.asp.net/jgaylord/archive/2005/05/12/406639.aspx

HTH
Regards

|||

Thank you. I read that blog, but it just seems to mention connection strings in general. The issue I am facing is that the Reporting Services assembly does not seem to allow me to access web.config, even though there is a web.config associated with RS. When I try to write "Imports System.Web.Configuration," VS2005 says it is not available and that the WebConfigurationManager is not available.

|||

Ok, simply add the System.Configurations.Dll as a reference in your project.

Let me know if you need further help.

Regards

Access is denied: 'Interop.ADODB'.

I am using a com component in my asp.net programme and it was working fine for many days . now I am getting following error .

Source Error:

Line 196: <add assembly="System.EnterpriseServices, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 197: <add assembly="System.Web.Mobile, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 198: <add assembly="*"/>Line 199: </assemblies>Line 200: </compilation>

Source File: c:\windows\microsoft.net\framework\v1.1.4322\Config\machine.config Line: 198

Assembly Load Trace: The following information can be helpful to determine why the assembly 'Interop.ADODB' could not be loaded.

=== Pre-bind state information ===LOG: DisplayName = Interop.ADODB (Partial)LOG: Appbase = file:///e:/inetpub/wwwroot/SAPTRainingLOG: Initial PrivatePath = binCalling assembly : (Unknown).=== LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODBLOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///e:/inetpub/wwwroot/SAPTRaining/bin/Interop.ADODB.DLL.LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODB, Version=2.6.0.0, Culture=neutral, PublicKeyToken=null

---

Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300

How this can be solved ? Please help

Something has probably changed.

Check that the config file still contains a valid configuration, and that the file(s) that are needed for your app still are at the expected place(s). ie paths, filenames etc...

/Kenneth

access denied to data source reporting services 2000

I have data on server A and the report server on server B.

I have created reports that I can run through report manager (on server B).

I have depolyed the reports but when I try to run the reports on client computers I get the following error:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'ABCD'. (rsErrorOpeningConnection) Get Online Help

SQL Server does not exist or access denied.

I have tried setting different credentials ... windows security, specified username and password to credentials not required ...

All give above error.

TIA

You will have to provide more information in order to help you. Are the servers on different domains ? Are you able to open the report while being logged on to server B locally ? Aren′t you able to access the report from ServerB while logged on at clientA. Are you using the same account for logging on ? How is IIS setup on the server ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Both servers are on the same domain. The reports work from server b logged on as administrator locally.

I have configured the reports to be able to be accessed by a domain group. The group has Browser and My Reports roles assigned.

The users are logged on as themselves.

IIS is setup to have windows integrated authentication.

I have setup the report to have a sql server username and password for the datasource stored on the report server.

Hope that helps.

|||if you setup the data source for SQL Server authentication, this should work both from ServerB and ClientA. If you are using Windows authentication you will have to use the setspn command in order to enabled the security delegation for the server. Otherwise the server cannot check the credentials of the user.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de