EOF with SqlDataSource

I am trying to get record from a table and verify it with a textbox i have a sqldatasource.

i have a text box called txtEmail and this is my Select command. how can i get this working if its possible ?

something like txtEmail.text = SqlDataSource1.Secect then ... my code. (i dont know if this a correct way to do this)

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT FROM [t_CustomerAcct]"

thanks,

Hi,

I'm not quite clear what you want. Do you mean querying with some conditions for a return from the data table and verify if it's equal with the value from the text box or just want to make a query according the value from the text box?

If it's the first scenario, see the following sample:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:TestConnectionString2%>" ></asp:SqlDataSource>  
SqlDataSource1.SelectCommand ="select CategoryName from Categories where CategoryID='1'";DataView dw = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);string str = dw.Table.Rows[0][0].ToString();if (str ==this.TextBox1.Text) {// equal}else {// not equal}
 

If it's the second scenario, just make select statement according the value from textbox directly.

this.SqlDataSource1.SelectCommand ="select CategoryName from Categories where CategoryID=@.id";this.SqlDataSource1.SelectParameters.Add("id",this.TextBox1.Text); DataView dw = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);string str = dw.Table.Rows[0][0].ToString();

Thanks.

Environment discrepencies concerning stored procedures & data types

Im trying to move a database from a NT4 SQL2K server over to a WIN2K SQL2K box.

When i copy the stored procedures over via a generated script, 1 procedure fails to create itself, and the error i get is:

The text, ntext, and image data types are invalid in this subquery or aggregate

the error is supposedly the top line here:

SELECT *, 'cms_Document_Contents_ID'='', 'cms_Language_ID'='', 'Title'='', 'XML'='', 'search_text'='',
'File_XML'=(SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id=e.cms_Document_Id
WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId) ,
c.active, c.sequence, c.cms_Document_Section_ID, c.cms_Section_ID, c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId
END

GO

wtf is going on? this works fine on the old server. i tried running it back there and it just complains that the stored procedure already exists.
i am not familiar with SQL, this is someone elses code, i dont understand the error and NEED all the help i can get.Somehow you got single quotes stuck around your column names.

Select 'cms_Document_Contents_ID'=''
...makes no sense. I assume the procedure is createing an empty column that will be populated later. Try this and see if you get the same error:

SELECT *,
cms_Document_Contents_ID='',
cms_Language_ID='',
Title='',
XML='',
search_text='',
File_XML= (SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id = e.cms_Document_Id WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId),
c.active,
c.sequence,
c.cms_Document_Section_ID,
c.cms_Section_ID,
c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId

blindman|||Stop the SQL Server, exit the Service Manager, copy the data and log files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Data.mdf, & C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Log.ldf on my machine respectivley) to your destination server, on that machine you can import (attatch) the data and log files into SQL Server with the following command in the query analyzer (substituing MyDB and the 2 paths with your equivalents):

sp_attatch_db @.dbname = 'MyDB',
@.filename1 = 'c:\path\to\datafilename.mdf',
@.filename2 = 'c:\path\to\datafilename.mdf'

if that is successful id say your home free (refresh your database list), if not, post on this forum, they can help...

.^sUbMSg-\/.

see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp for info on this stored procedure.

ps: f*ck that DTS export b*llocks im sticking with the raw data files.

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

export copy move transfer transport duplicate mirror import mdf sql database db attach stored procedure retain keep error 2000 7 6.5 8 backup restore detatch

EnumAvailableSqlServers() returns nothing when there is no network connection

I am having a weird issue. I am using SMO's EnumAvailableSqlServers() method to fill a drop-down box. If I am connected to a network, it shows all networked Sql Servers, including the local instance. When I am not connected to any network, it shows nothing. Even if I connect to an ad-hoc wireless network, it works correctly, showing only the local instance, but if the wireless card is removed (and no other networks are enabled) it is blank. SQL Server Browser is running. I am running both SQL Server and SQL Browser services under a local administrator account (for testing).

The problem is that these are handheld devices and sometimes they will be on a network, and other times they will be inside secure facilities in which no network devices are ever allowed. So, it needs to work in either condition. And really, it's absurd that the method cannot work in an unnetworked environment - a major slip-up by MS that is about to force us to embedded linux.

So, two questions:

1. Is there a way to fix this such that it will fallback correctly and look for local instances when no network is available?

2. If not, is there a known way to "trick" a machine into thinking a network is there so this will run correctly, such as some sort of network driver that emulates a connected network?

Hello David,

The EnumAvailableSqlServers is based off of the ADO.NET SqlDataSourceEnumerator class. The implementation is based on UDP broadcast, with a timeout, so you need a network connection to run the enumeration. Here are some other good things to know about the method: it may not reliably see all servers respond before the timeout; it will not find SQL Server if the SQLBrowser switched off, or the server is marked as hidden; and the method will also fail if the local instance blocks TCP/IP Port 1433 and UDP 1434.
But all is not lost. Here is code snippet that uses SMO Wmi to retrieve a reliable list the local SQL Server 2005 instances on a machine without network access.

ManagedComputer mc = new ManagedComputer();

// Setup the collection of servers

foreach (ServerInstance i in mc.ServerInstances)

{

string servername = i.Name;

if (i.Name == "MSSQLSERVER") // This is the default instance

{

servername = ".";

} else {

servername = ".\\" + i.Name;

}

Console.WriteLine(servername);

}

Good luck and let us know if you have any more questions,

Jennifer

This posting is provided "as is" with no warranties, and confers no rights.

|||Thanks, I will attempt to implement that tomorrow. It will make everyone very happy if it works out.

The million dollar question, where is this type of thing documented so I don't run into such problems down the road? Reminds me of using API calls back in VB5 days.
|||

MSDN documents the SMO class library at

http://msdn2.microsoft.com/en-us/library/bb283710.aspx

The EnumAvailableSqlServers() is at

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlservers.aspx

And ServerInstance is at

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.serverinstance.aspx

Best of luck,

Jennifer Beckmann

|||

Hi David,

I have been down this road many times and what surprises me is that the answer is the same no matter where I look.

As you have found the EnumAvailableSqlServers() is not very reliable and requires too many stipulations to work such as SQLBrowser, Firewall settings, Network connection etc. Even if you get all that right you will find that it is still hit or miss. And good luck getting any earlier versions of SQL Server to show up including SQL Express. Jennifer's solution is great if you are only dealing with SQL 2005.

However I have discovered a slightly different approach that has not failed me yet:

Code Snippet

Dim SQLServers As RegisteredServers.RegisteredServerCollection = SmoApplication.SqlServerRegistrations.RegisteredServers

This will pick up SQL 2005, SQL Express, SQL 2000 and MSDE instances without any stipulations. No firewall issues, no network issues, no SQL Browser issues. You get the same complete list every time. And correctly named too I might add.

Hope this helps you and many more frustrated folks,

Jamie

|||Hi David,
I think the easiest solution is that you need to make sure that your SQL Browser Service is up and running and set to auto start. good luck.
Chan

Enterprise Mgr to remote database

Hi Guys,
I have a web app hosted on an external box with my dev box here. both use identical SQL2000 backends. What I would like is to have my Enterprise Manager have both servers registered so that I can work from one interface. No probs getting the local one registered, but how do I register the external DB server? It's not in my domain, but I do have an IP address and Administrator rights on the remote box. This is probably simple, but I must be missing something.
Thanks
MikeJust add your external server as linked...|||Umm . . OK.

1. So New Sql Server Registration gives me the Register Wizard.
2. I enter the IP address manually in the Available Servers field and click add. Click Next.
3. So far, so good. Can't use my current Windows Account info (different domain), so I select SQL Server login info and put in SA account details.
4. I get a Connecting dialog then "SQL Server does not exist or access denied - ConnectionOpen - (Connect()"

Sorry if I sound dense, but . . . . .|||Try using the subdomain.domain address construct rather than IP address.

Example:

somesqlservermachinename.domain.com

I've had problems connecting to one of our remote SQL Servers via IP address (odd, since the same server is in the same subnet as two other SQL Servers that I can connect fine using their IP Addresses). But the subdomain construct works just fine.

Enterprise Manager question

When I click on a server node in EM, I am prompted to
enter the sa password in hte usual dialog box. However for
one particular server I get this dialog box twice - why is
this different to the others?
TIA,
BBSounds to me like something introduced by installing a security hotfix. Chan you check if that
differ on the server or client level?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:01d601c4a1a5$83884c40$a501280a@.phx.gbl...
> When I click on a server node in EM, I am prompted to
> enter the sa password in hte usual dialog box. However for
> one particular server I get this dialog box twice - why is
> this different to the others?
> TIA,
> BB

Enterprise Manager question

When I click on a server node in EM, I am prompted to
enter the sa password in hte usual dialog box. However for
one particular server I get this dialog box twice - why is
this different to the others?
TIA,
BB
Sounds to me like something introduced by installing a security hotfix. Chan you check if that
differ on the server or client level?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:01d601c4a1a5$83884c40$a501280a@.phx.gbl...
> When I click on a server node in EM, I am prompted to
> enter the sa password in hte usual dialog box. However for
> one particular server I get this dialog box twice - why is
> this different to the others?
> TIA,
> BB

Enterprise Manager Connection Issues

Hello,
I am having trouble connecting from enterprise
manager to a sql 2000 box. I can connect to all of our
other production boxes. This is a new box that we built
and I cannot connect. When I go to register it in
Enterprise Manager the error message says "Driver Not
Capable". I'm not sure exactly what this means. All I'm
trying to do is connect to the box. Is there a driver
either on my local laptop or on the server side that
isn't set up right or maybe needs to be updated? I can
open up the database locally on the server and I can
register other servers on it and have that server
registered to other servers. I know other people on my
team are not able to connect to it as well, so I know
it's not just me. Any help would be greatly
appreciated. Thanks.
-Craig CoffaroHello -
The error message "Driver not capable" indicates the problem
to be with the SQL driver. Check the registry entry
HKEY_LOCAL_MACHNE\Software\ODBC\ODBCINST
.INI
Check the driver location in the above registry entry. Check for
SQLsrv32.dll version. If the registry location is pointing to incorrect
file, change it and make it point to the correct one.
Have you installed Crystal reports after installing SQL server?
Regards,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
"Craig Coffaro" <craig.coffaro@.kroger.com> wrote in message
news:902d01c4052c$5d59e990$a501280a@.phx.gbl...
> Hello,
> I am having trouble connecting from enterprise
> manager to a sql 2000 box. I can connect to all of our
> other production boxes. This is a new box that we built
> and I cannot connect. When I go to register it in
> Enterprise Manager the error message says "Driver Not
> Capable". I'm not sure exactly what this means. All I'm
> trying to do is connect to the box. Is there a driver
> either on my local laptop or on the server side that
> isn't set up right or maybe needs to be updated? I can
> open up the database locally on the server and I can
> register other servers on it and have that server
> registered to other servers. I know other people on my
> team are not able to connect to it as well, so I know
> it's not just me. Any help would be greatly
> appreciated. Thanks.
> -Craig Coffaro|||Chandra,
Yes I did install Crystal Reports 6 last week and ever since then
I have had problems with my SQL connections. Thanks for the advice and
I will give this a try. Thanks again.
Craig
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Chandra,
One other question I have is when I go to the registry:
HKEY_LOCAL_MACHNE\Software\ODBC\ODBCINST
.INI and I go down to SQL Server
I see in the window on the right, Driver and Setup both have the same
path with the sqlsrv32.dll in it. I also see DrverODBCver being
03.50...what exactly am I looking for or what do I need to be doing
witht this info? Is there a version of the ODBC driver that I should be
updating? Where exactly can I find this? Or is it the path
C:\WINDOWS\SYSTEM\sqlsrv32.dll that is the problem, if this is the
problem what path should it be? How does the installation of Crystal
Reports affect SQL? Any help you have for me Chandra is greatly
apprecaited. Thanks.
-Craig Coffaro
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||I'm not clear on what your question is. Do you need to find the version of
MDAC? If so, you can download the MDAC Component Checker from
microsoft.com/downloads.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Enterprise Manager and Master/temdb viewing

Hello,
Sorry for the simple question I am about to ask however I have not
found the answer elsewhere.
We have installed an SQL 200 box and are now about to grant users
access via Enterprise Manager. We have created logins for each user
with no Server Roles and access to their DB only. However when we
connect as this user through EM we can see Master and tempdb and view
the table information associated with theses DB's. We have removed
'guest' access from all other DB's. Is there any way to disable users
abilities to view the tempdb and master databases?
Thanks for your help.
CHi
Not with SQL Server 2000. This limitation is addressed in SQL Server 2005.
Everyone has rights to read sysdatabases and sysobjects.
Regards
Mike
"gg" wrote:
> Hello,
> Sorry for the simple question I am about to ask however I have not
> found the answer elsewhere.
> We have installed an SQL 200 box and are now about to grant users
> access via Enterprise Manager. We have created logins for each user
> with no Server Roles and access to their DB only. However when we
> connect as this user through EM we can see Master and tempdb and view
> the table information associated with theses DB's. We have removed
> 'guest' access from all other DB's. Is there any way to disable users
> abilities to view the tempdb and master databases?
> Thanks for your help.
> C
>|||Hi gg,
One of the thing you can do is, on the client machine, while you register
SQL server through EM, in the properties you can uncheck "Show system
database and system objects". This will prevent the client user from seeing
system database.
--
Thanks
Yogish

Enterprise Manager and Master/temdb viewing

Hello,
Sorry for the simple question I am about to ask however I have not
found the answer elsewhere.
We have installed an SQL 200 box and are now about to grant users
access via Enterprise Manager. We have created logins for each user
with no Server Roles and access to their DB only. However when we
connect as this user through EM we can see Master and tempdb and view
the table information associated with theses DB's. We have removed
'guest' access from all other DB's. Is there any way to disable users
abilities to view the tempdb and master databases?
Thanks for your help.
C
Hi
Not with SQL Server 2000. This limitation is addressed in SQL Server 2005.
Everyone has rights to read sysdatabases and sysobjects.
Regards
Mike
"gg" wrote:

> Hello,
> Sorry for the simple question I am about to ask however I have not
> found the answer elsewhere.
> We have installed an SQL 200 box and are now about to grant users
> access via Enterprise Manager. We have created logins for each user
> with no Server Roles and access to their DB only. However when we
> connect as this user through EM we can see Master and tempdb and view
> the table information associated with theses DB's. We have removed
> 'guest' access from all other DB's. Is there any way to disable users
> abilities to view the tempdb and master databases?
> Thanks for your help.
> C
>
|||Hi gg,
One of the thing you can do is, on the client machine, while you register
SQL server through EM, in the properties you can uncheck "Show system
database and system objects". This will prevent the client user from seeing
system database.
Thanks
Yogish

Enterprise Manager and Master/temdb viewing

Hello,
Sorry for the simple question I am about to ask however I have not
found the answer elsewhere.
We have installed an SQL 200 box and are now about to grant users
access via Enterprise Manager. We have created logins for each user
with no Server Roles and access to their DB only. However when we
connect as this user through EM we can see Master and tempdb and view
the table information associated with theses DB's. We have removed
'guest' access from all other DB's. Is there any way to disable users
abilities to view the tempdb and master databases?
Thanks for your help.
CHi
Not with SQL Server 2000. This limitation is addressed in SQL Server 2005.
Everyone has rights to read sysdatabases and sysobjects.
Regards
Mike
"gg" wrote:

> Hello,
> Sorry for the simple question I am about to ask however I have not
> found the answer elsewhere.
> We have installed an SQL 200 box and are now about to grant users
> access via Enterprise Manager. We have created logins for each user
> with no Server Roles and access to their DB only. However when we
> connect as this user through EM we can see Master and tempdb and view
> the table information associated with theses DB's. We have removed
> 'guest' access from all other DB's. Is there any way to disable users
> abilities to view the tempdb and master databases?
> Thanks for your help.
> C
>|||Hi gg,
One of the thing you can do is, on the client machine, while you register
SQL server through EM, in the properties you can uncheck "Show system
database and system objects". This will prevent the client user from seeing
system database.
Thanks
Yogish

Enterprise edition licensing question

Hi,
I currently have RS Standard Edition running on a box with SQL Server 2000
Standard Edition installed, licensed and running in per-processor mode.
At an ASP, I have a box (actually, the ASP's property) with SQL Server 2000
Enterprise Edition installed, licensed and running in CAL mode. Note that we
actually own the SQL Server licenses, it's just running on their hardware.
I will be connecting to both boxes for report data.
My question: Can I install RS Enterprise Edition on my box running Standard
Edition? I feel like I should be able to because I own the Enterprise
Licenses. Unfortunately, the ASP doesn't support the installation of RS on
the box they host.
ThanksYou're likely going to have to talk to your local Microsoft Sales office as
we're supposed to avoid licensing questions in the newsgroups unless we can
point you at something on our web site that explains it. We're not lawyers
and aren't supposed to pretend that we are. :-) Look around on
http://www.microsoft.com/sql/reporting/howtobuy/faq.asp and then head over
to http://www.microsoft.com/worldwide/ to find the nearest sales office.
Sorry that I can't be more helpful...
Of course, immediately after sending this, somebody is likely to jump in
knowing the answer... :-)
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"PBR" <paul@.thesak.com*nospam*> wrote in message
news:OhSq1kTaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I currently have RS Standard Edition running on a box with SQL Server 2000
> Standard Edition installed, licensed and running in per-processor mode.
> At an ASP, I have a box (actually, the ASP's property) with SQL Server
> 2000
> Enterprise Edition installed, licensed and running in CAL mode. Note that
> we
> actually own the SQL Server licenses, it's just running on their hardware.
> I will be connecting to both boxes for report data.
> My question: Can I install RS Enterprise Edition on my box running
> Standard
> Edition? I feel like I should be able to because I own the Enterprise
> Licenses. Unfortunately, the ASP doesn't support the installation of RS on
> the box they host.
> Thanks
>|||I'll jump in with my interpretation because your (Paul, not Stephen)
assumption is a wrong one. You are assuming that the license is not tied to
a machine. It definitely is. For each machine that has RS server installed
you need a license. Note that whether or not SQL Server DB is on that
machine makes no difference. A completely supported configuration is to have
RS on a web server and the db on a differnt box (which I will be doing
eventually). That requires two licenses. One for the DB Server and one for
the server where RS is installed. It makes no difference where the data is
coming from. The server license is for the machine you have RS installed on.
This is why for web farms you need to buy a license for each web server with
RS installed on it.
Bruce L-C
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:OoT4z$caEHA.1248@.TK2MSFTNGP11.phx.gbl...
> You're likely going to have to talk to your local Microsoft Sales office
as
> we're supposed to avoid licensing questions in the newsgroups unless we
can
> point you at something on our web site that explains it. We're not lawyers
> and aren't supposed to pretend that we are. :-) Look around on
> http://www.microsoft.com/sql/reporting/howtobuy/faq.asp and then head over
> to http://www.microsoft.com/worldwide/ to find the nearest sales office.
> Sorry that I can't be more helpful...
> Of course, immediately after sending this, somebody is likely to jump in
> knowing the answer... :-)
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "PBR" <paul@.thesak.com*nospam*> wrote in message
> news:OhSq1kTaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I currently have RS Standard Edition running on a box with SQL Server
2000
> > Standard Edition installed, licensed and running in per-processor mode.
> >
> > At an ASP, I have a box (actually, the ASP's property) with SQL Server
> > 2000
> > Enterprise Edition installed, licensed and running in CAL mode. Note
that
> > we
> > actually own the SQL Server licenses, it's just running on their
hardware.
> >
> > I will be connecting to both boxes for report data.
> >
> > My question: Can I install RS Enterprise Edition on my box running
> > Standard
> > Edition? I feel like I should be able to because I own the Enterprise
> > Licenses. Unfortunately, the ASP doesn't support the installation of RS
on
> > the box they host.
> >
> > Thanks
> >
> >
>|||No, not legally. The license means you can install the software on a single
machine, if you want to install any piece such as Reporting Services or
Analysis Services on another box you need another license.
Mike Kruchten
"PBR" <paul@.thesak.com*nospam*> wrote in message
news:OhSq1kTaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I currently have RS Standard Edition running on a box with SQL Server 2000
> Standard Edition installed, licensed and running in per-processor mode.
> At an ASP, I have a box (actually, the ASP's property) with SQL Server
2000
> Enterprise Edition installed, licensed and running in CAL mode. Note that
we
> actually own the SQL Server licenses, it's just running on their hardware.
> I will be connecting to both boxes for report data.
> My question: Can I install RS Enterprise Edition on my box running
Standard
> Edition? I feel like I should be able to because I own the Enterprise
> Licenses. Unfortunately, the ASP doesn't support the installation of RS on
> the box they host.
> Thanks
>