Showing posts with label net. Show all posts

Friday, March 30, 2012

How to construct the query in Stored Proc

Hi,
In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:
Codes in ASPX.VB:
Dim SQL as String="SELECT * From Table1"
IF UserOption <> Null then
SQL = SQL & " WHERE Column1=" UserOption"
End if

Now, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.
But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).
Hope you can understanding what my mean and give me advices.
Regards,
Raymond

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

|||

I n SQL Server you can use the CASE statement, try the links below for sample code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Thanks a lot! I will study it. I also find this structure may help, which introducedd in another thread in this forum:

AND ([DD1 Industry Code] LIKE @.FilterIndustries OR @.FilterIndustries ='')

|||

scu wrote:
Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX
@.OPTION1 INT
AS
DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)
SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END
EXEC (@.MYQUERY + @.MYFILTER)

Will it affect the performance when the query is built up in a dynamic string rather than build up within stored procedure using CASE/IF..THEN..ELSE/COALESCE statements? It is important concern to the effectiveness when the store procedure contains complicated queries.|||Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.
|||

scu wrote:
Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.

Thanks for the kindly sharing from all of you!|||You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick|||

nick-w wrote:
You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick

Great article! Thanks Nick!

Wednesday, March 28, 2012

how to connect with sqlexpress2005 & asp.net for update insert delete codings

I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters

import System.Data.SqlClient;

related update delete insert codings ...

How to know about the make the new connection with different databases sqlserver, oracle mysql...

Have you had a lookhttp://quickstarts.asp.net/QuickStartv20/default.aspx andhttp://www.asp.net/learn/data-access/.

Work through these samples.. They explain how to access databases using declarative coding (sqldatasource and the likes) and coe behind coding.

Hope that helps.

How to connect with SQL database?

I am new learner of ASP.NET and coding in C#.I am not able to connect the SQL database.I have written the code for connection of database but to write the connection string web.config file is neccessary which is not apearing in my application. Please help me .Here is code I have written.

string sCon =ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

SqlConnection oCon =new SqlConnection(sCon);

try

{

oCon.Open();

string sQuery ="Select Offering1,Offering2,Offering3 from Offering";SqlCommand oCmd =new SqlCommand(sQuery, oCon);

oCmd.ExecuteNonQuery();

DataSet oDs =newDataSet();

SqlDataAdapter oDa =new SqlDataAdapter(oCmd);

oDa.Fill(oDs);

DataList1.DataSource = oDs;

DataList1.DataBind();

}

catch (Exception ex)

{

ex.ToString();

}

What kind of error are you getting?

Can you post a copy of the connectionString settings from your web.config?

|||

Hii Jimmy,

Actually I do not see the web.config file in my solution explorer. So that I am unable to write the connection string. Can you please tell me why it is so ?

|||

Looks like you may have started with the empty web site template. When you try to start/debug the application, does it not automatically add a web.config for you?

Well, you can manually add one. Right click on your web site and select ADD NEW ITEM then select the WEB CONFIGURATION FILE

|||

Hey thanks,

As there are errors because I have not added the connection string into the web.cofig file it is not able to debug the file.Right now I have added web.config manually.

What the connection string format to add to web.config?

<addname="ConnectionString"connectionString="Server=(local)\SQLEXPRESS;Integrated Security=True;Database=Knowledgebase;Persist Security Info=True"/>

is it right?

Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

|||

Bluestar123:
Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

That would because you did not import the namespace

Add this to the top of your code behind for your default.aspx

using System.Data.SqlClient;

|||

The connection string can look something like this

"Data Source= xxx ;Initial Catalog=xxx; uid=xxx ;pwd=xxx"

where datasource is your database server; could be your machine name or I.P. address, Initial Catalog is the database name, UID is the login name, PWD is the login name's password

If your SQL Server is set up for windows authentication then you can do replace the UID and PWS with "Integrated Security=True"

Have a look at this link for connection string examples

http://connectionstrings.com/?carrier=sqlserver2005

|||

Hi Jimmy,

Now I have added using System.Data.SqlClient to my default.aspx.cs and that error has gone thanks for the same.

I am still not able to fetch the data to my datalist from database, is still there any problem with my connection string? in my web.cofig I have used windows authentication.

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

Please help ..........

|||

Bluestar123:
<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

for the connection string, the data source should be [your machine name]\SQLEXPRESS assuming SQLEXPRESS is the server instance name

update your code to this and give it a go

string sCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection oCon =new SqlConnection(sCon);try
{
string sQuery ="Select Offering1,Offering2,Offering3 from Offering";
 SqlCommand oCmd =new SqlCommand(sQuery, oCon);
 DataSet oDs =new DataSet();
 SqlDataAdapter oDa =new SqlDataAdapter(oCmd);
 oDa.Fill(oDs);
 DataList1.DataSource = oDs;
 DataList1.DataBind();
}
catch (Exception ex)
{
 ex.ToString();
}

Add some break points and step through the code, if there any exceptions, take not of the exception message.

|||

I have added break points at Select command and line below that ,control goes there when I debug the default.aspx and then it display the page without fetching thedata from database.So i have added the break point to the line

DataSet oDs=new DataSet();

at this line control donot go and directly display the page without showing the data from the database.

|||

So when you put a break point at the DataSet oDs=new DataSet(); and when you step over it F10, it does not proceed to the next line? Then that means an exception was raised and it should be caught in your catch. So put a break point in your catch statement, so you can check the exception being thrown if there is one at all and give us the error message

|||oops! Column name I had given was wrong .......Its OfferingName. I made that change but now when I put brk point at

DataList1.DataSource = oDs;

after pressing F10 control goes to next line i. e.

DataList1.DataBind();

but data from database is not getting displayed on the page though the page has been displayed......

oh no whats the problem ?am I doing any mistake again?

Follwing output I got in Output window

Warning: Cannot debug script code. Script debugging is disabled for the application you are debugging. Please uncheck the 'Disable script debugging' option on the Internet Options dialog box (Advanced page) for Internet Explorer and restart the process.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_d4ehzmko.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_oq4mi2vf.dll', Symbols loaded.

The program '[3368] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

|||

Well the obviously question is have you tried executing the SQL statement against your database to make sure it does return data?

after you have stepped over this line of code oDa.Fill(oDs); check it the dataset has returned a datatable with datarows.

|||

No I didnt,how can I check it ?I am using Microsoft SQL server management Studio. When go to line

oDa.Fill(oDs);

it did not return the table contents......

I tried to execute the query by using Execute SQL but not able to execute ,How to do?

Did you get fed up of my questions Jimmy??

|||

Bluestar123:
Did you get fed up of my questions Jimmy??

Not at all, just trying my best to understand your problem which is at times a little hard.

Lets try execute you select statement first in SQL Server Management Studio.

Log onto your database, and select the NEW QUERY option.

Now paste your SQL statement into this window , "Select Offering1,Offering2,Offering3 from Offering" and click execute

Does it return any results?

sql

How to connect using C#

I'm trying to connect to a MS SQL Server using ASP.NET / C#. I've
already got some VB code that works but I'm unable to translate it to
working C# code. Can anyone help me out?

Working VB code:

Dim Conn
Conn = CreateObject("ADODB.Connection")
Const ConnectionString = "DSN=dsn_name;database=database_name"
Conn.Open(ConnectionString, "user_name", "password")

Not working C# code:

string ConnectionString = "Initial Catalog=database_name;" +
"Data Source=dsn_name;" +
"User ID=user_name;" +
"Password=password;" +
"Integrated Security=SSPI;";

SqlConnection Connection = new SqlConnection(ConnectionString);
Connection.Open();
Connection.Close();Use exactly the same connection string form C# as for VB. If it works
in one, it'll work in the other.

Specifically, if you are using SSPI, you don't need to specify uid and
pw, and vice-versa.|||AHM (ingen@.spam.tak) writes:
> I'm trying to connect to a MS SQL Server using ASP.NET / C#. I've
> already got some VB code that works but I'm unable to translate it to
> working C# code. Can anyone help me out?

If you help us to help you. Don't just post:

> Not working C# code:
> string ConnectionString = "Initial Catalog=database_name;" +
> "Data Source=dsn_name;" +
> "User ID=user_name;" +
> "Password=password;" +
> "Integrated Security=SSPI;";
> SqlConnection Connection = new SqlConnection(ConnectionString);
> Connection.Open();
> Connection.Close();

Tell us in what way it is not working. Do you get an error message?
Do you get unexpected results? Does the light go out?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> string ConnectionString = "Initial Catalog=database_name;" +
> "Data Source=dsn_name;" +
> "User ID=user_name;" +
> "Password=password;" +
> "Integrated Security=SSPI;";

Note the change to 'Data Source' in the example below as well as the removal
of the unnecessary User ID and Password parameters:

string ConnectionString = "Initial Catalog=database_name;" +
"Data Source=database_server_name;" +
"Integrated Security=SSPI;";

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AHM" <ingen@.spam.tak> wrote in message
news:MPG.1cb65eb0b33ffb61989705@.news.inet.tele.dk. ..
> Hi
> I'm trying to connect to a MS SQL Server using ASP.NET / C#. I've
> already got some VB code that works but I'm unable to translate it to
> working C# code. Can anyone help me out?
> Working VB code:
> Dim Conn
> Conn = CreateObject("ADODB.Connection")
> Const ConnectionString = "DSN=dsn_name;database=database_name"
> Conn.Open(ConnectionString, "user_name", "password")
> Not working C# code:
> string ConnectionString = "Initial Catalog=database_name;" +
> "Data Source=dsn_name;" +
> "User ID=user_name;" +
> "Password=password;" +
> "Integrated Security=SSPI;";
> SqlConnection Connection = new SqlConnection(ConnectionString);
> Connection.Open();
> Connection.Close();

How to connect to the SQl server EXPRESS using 'ODBC Data Source A

Hi All,
Recently I have installed SQl server express edition. I was able to connect
to the SQL server from my .NET project using connection parameter like:
Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Raju\My
Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True
Now I need to connect to the same SQL server from 'ODBC Data Source
Aministrator' wizard, that I am unable to do. The wizard is asking me (for
system DSn and File DSN) for SQL server name (I do no know what to give, is
it (local) or something) and how to login authenticate (with windows
authentication or something). Now if I goto Services/SQl Server (SQLEXPRESS)
, click LogOn I see that 'Local System Account' is checked. Pls tell me how
to connect to this SQl server using System DSN connection wizard.
By the way I am the administrator of this machine where I have instlled the
SQl server...
Thanks in advance,
--
Sanjib SahaHi
VB.NET
Imports System.Data.Odbc
Dim oODBCConnection As OdbcConnection
Dim ConnString As String = _
"Driver={SQL Server};" & _
"Server=SQLServerName;" & _
"Database=DatabaseName;" & _
"Uid=Username;" & _
"Pwd=Password"
oODBCConnection = New Odbc.OdbcConnection(ConnString)
oODBCConnection.Open()
"sanjib" <sanjib@.discussions.microsoft.com> wrote in message
news:7BE14AB4-9460-4489-B7CB-62880592EFC6@.microsoft.com...
> Hi All,
> Recently I have installed SQl server express edition. I was able to
> connect
> to the SQL server from my .NET project using connection parameter like:
> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
> Settings\Raju\My
> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True
> Now I need to connect to the same SQL server from 'ODBC Data Source
> Aministrator' wizard, that I am unable to do. The wizard is asking me (for
> system DSn and File DSN) for SQL server name (I do no know what to give,
> is
> it (local) or something) and how to login authenticate (with windows
> authentication or something). Now if I goto Services/SQl Server
> (SQLEXPRESS)
> , click LogOn I see that 'Local System Account' is checked. Pls tell me
> how
> to connect to this SQl server using System DSN connection wizard.
> By the way I am the administrator of this machine where I have instlled
> the
> SQl server...
> Thanks in advance,
> --
> Sanjib Saha|||The OP indicates that he is using SQL Server 2005 Express' User Instance. I
do not think he can connect to User Instance through ODBC DSN Wizard (why
use ODBC anyway?). I am not should if it is possible to connect to SQL
Server Express' User Instance through OdbcConnection object in
System.Data.Odbc namespace, but it is certain your sample code will not.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23QvKqzLoGHA.4332@.TK2MSFTNGP03.phx.gbl...
> Hi
> VB.NET
> Imports System.Data.Odbc
> Dim oODBCConnection As OdbcConnection
> Dim ConnString As String = _
> "Driver={SQL Server};" & _
> "Server=SQLServerName;" & _
> "Database=DatabaseName;" & _
> "Uid=Username;" & _
> "Pwd=Password"
> oODBCConnection = New Odbc.OdbcConnection(ConnString)
> oODBCConnection.Open()
>
> "sanjib" <sanjib@.discussions.microsoft.com> wrote in message
> news:7BE14AB4-9460-4489-B7CB-62880592EFC6@.microsoft.com...
>> Hi All,
>> Recently I have installed SQl server express edition. I was able to
>> connect
>> to the SQL server from my .NET project using connection parameter like:
>> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
>> Settings\Raju\My
>> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
>> Security=True;Connect Timeout=30;User Instance=True
>> Now I need to connect to the same SQL server from 'ODBC Data Source
>> Aministrator' wizard, that I am unable to do. The wizard is asking me
>> (for
>> system DSn and File DSN) for SQL server name (I do no know what to give,
>> is
>> it (local) or something) and how to login authenticate (with windows
>> authentication or something). Now if I goto Services/SQl Server
>> (SQLEXPRESS)
>> , click LogOn I see that 'Local System Account' is checked. Pls tell me
>> how
>> to connect to this SQl server using System DSN connection wizard.
>> By the way I am the administrator of this machine where I have instlled
>> the
>> SQl server...
>> Thanks in advance,
>> --
>> Sanjib Saha
>|||Norman,
There should be some way to connect to the SQl server 2005 Express user
instance. I need to connect to this SQl server b'case one of my application
is demandint that...There should be some way, tht we do not know...Anyway
thanks...Someone pls guide me...
Thanks,
Sanjib
--
Sanjib Saha
"sanjib" wrote:
> Hi All,
> Recently I have installed SQl server express edition. I was able to connect
> to the SQL server from my .NET project using connection parameter like:
> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Raju\My
> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True
> Now I need to connect to the same SQL server from 'ODBC Data Source
> Aministrator' wizard, that I am unable to do. The wizard is asking me (for
> system DSn and File DSN) for SQL server name (I do no know what to give, is
> it (local) or something) and how to login authenticate (with windows
> authentication or something). Now if I goto Services/SQl Server (SQLEXPRESS)
> , click LogOn I see that 'Local System Account' is checked. Pls tell me how
> to connect to this SQl server using System DSN connection wizard.
> By the way I am the administrator of this machine where I have instlled the
> SQl server...
> Thanks in advance,
> --
> Sanjib Saha|||You can access an User Instance once it is started by specifying the Named
Pipe name for the instance but if you haven't already started the user
instance with a managed code connection, ODBC won't start it. If your main
application is using ODBC, I would recommend attaching the database to the
main instance so it is always running and the ODBC connection can find it.
There is more information on named instances and how to connect to them
here:
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sanjib" <sanjib@.discussions.microsoft.com> wrote in message
news:2AB671B3-FEAD-49C9-A309-8DBF8BF07815@.microsoft.com...
> Norman,
> There should be some way to connect to the SQl server 2005 Express user
> instance. I need to connect to this SQl server b'case one of my
> application
> is demandint that...There should be some way, tht we do not know...Anyway
> thanks...Someone pls guide me...
> Thanks,
> Sanjib
> --
> Sanjib Saha
>
> "sanjib" wrote:
>> Hi All,
>> Recently I have installed SQl server express edition. I was able to
>> connect
>> to the SQL server from my .NET project using connection parameter like:
>> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
>> Settings\Raju\My
>> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
>> Security=True;Connect Timeout=30;User Instance=True
>> Now I need to connect to the same SQL server from 'ODBC Data Source
>> Aministrator' wizard, that I am unable to do. The wizard is asking me
>> (for
>> system DSn and File DSN) for SQL server name (I do no know what to give,
>> is
>> it (local) or something) and how to login authenticate (with windows
>> authentication or something). Now if I goto Services/SQl Server
>> (SQLEXPRESS)
>> , click LogOn I see that 'Local System Account' is checked. Pls tell me
>> how
>> to connect to this SQl server using System DSN connection wizard.
>> By the way I am the administrator of this machine where I have instlled
>> the
>> SQl server...
>> Thanks in advance,
>> --
>> Sanjib Saha|||I agree with Roger.
If you HAVE to access the database on SQL Server Express via something other
than ADO.NET2.0's System.Data SqlClient namespace, why use User Instannce?
The whole point of using user instance is to limit the database setup/access
within current user's privilidge of access to the computer. If a user has to
use ODBC DSN Wizard, it may well requires the user has a bit more privilidge
that usual. Also, an user instance is not available to other user even the
other user uses the same computer.
"sanjib" <sanjib@.discussions.microsoft.com> wrote in message
news:2AB671B3-FEAD-49C9-A309-8DBF8BF07815@.microsoft.com...
> Norman,
> There should be some way to connect to the SQl server 2005 Express user
> instance. I need to connect to this SQl server b'case one of my
> application
> is demandint that...There should be some way, tht we do not know...Anyway
> thanks...Someone pls guide me...
> Thanks,
> Sanjib
> --
> Sanjib Saha
>
> "sanjib" wrote:
>> Hi All,
>> Recently I have installed SQl server express edition. I was able to
>> connect
>> to the SQL server from my .NET project using connection parameter like:
>> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
>> Settings\Raju\My
>> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
>> Security=True;Connect Timeout=30;User Instance=True
>> Now I need to connect to the same SQL server from 'ODBC Data Source
>> Aministrator' wizard, that I am unable to do. The wizard is asking me
>> (for
>> system DSn and File DSN) for SQL server name (I do no know what to give,
>> is
>> it (local) or something) and how to login authenticate (with windows
>> authentication or something). Now if I goto Services/SQl Server
>> (SQLEXPRESS)
>> , click LogOn I see that 'Local System Account' is checked. Pls tell me
>> how
>> to connect to this SQl server using System DSN connection wizard.
>> By the way I am the administrator of this machine where I have instlled
>> the
>> SQl server...
>> Thanks in advance,
>> --
>> Sanjib Saha|||Roger/Norman,
The whole point came like this. I am trying to install a SQl server driven
3rd party application and after the applications gets installed the database
installation wizard appears and tht wizard asks me to connect to the SQL
server using ODBC DSN, so I need to know the parameters for connecting to the
SQL server, which I don't know it seems, b'case while installing SQL server
2005 it never asked me for a server name/login password. And now it is
running using the 'Local System Account' i.e. Local Admin account that is me.
May be my SQL server 2005 is using user instance, although I am not sure.
The fact that I am not that much knowlegeble in SQL severs, much worked in
microsoft programming languages, and SQl server for me is a database server
tht always worked.
I will try from the URL provided by Roger and let u know...
Thanks,
Sanjib
--
Sanjib Saha
"Roger Wolter[MSFT]" wrote:
> You can access an User Instance once it is started by specifying the Named
> Pipe name for the instance but if you haven't already started the user
> instance with a managed code connection, ODBC won't start it. If your main
> application is using ODBC, I would recommend attaching the database to the
> main instance so it is always running and the ODBC connection can find it.
> There is more information on named instances and how to connect to them
> here:
> http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sanjib" <sanjib@.discussions.microsoft.com> wrote in message
> news:2AB671B3-FEAD-49C9-A309-8DBF8BF07815@.microsoft.com...
> > Norman,
> >
> > There should be some way to connect to the SQl server 2005 Express user
> > instance. I need to connect to this SQl server b'case one of my
> > application
> > is demandint that...There should be some way, tht we do not know...Anyway
> > thanks...Someone pls guide me...
> >
> > Thanks,
> > Sanjib
> > --
> > Sanjib Saha
> >
> >
> > "sanjib" wrote:
> >
> >> Hi All,
> >>
> >> Recently I have installed SQl server express edition. I was able to
> >> connect
> >> to the SQL server from my .NET project using connection parameter like:
> >>
> >> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
> >> Settings\Raju\My
> >> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
> >> Security=True;Connect Timeout=30;User Instance=True
> >>
> >> Now I need to connect to the same SQL server from 'ODBC Data Source
> >> Aministrator' wizard, that I am unable to do. The wizard is asking me
> >> (for
> >> system DSn and File DSN) for SQL server name (I do no know what to give,
> >> is
> >> it (local) or something) and how to login authenticate (with windows
> >> authentication or something). Now if I goto Services/SQl Server
> >> (SQLEXPRESS)
> >> , click LogOn I see that 'Local System Account' is checked. Pls tell me
> >> how
> >> to connect to this SQl server using System DSN connection wizard.
> >>
> >> By the way I am the administrator of this machine where I have instlled
> >> the
> >> SQl server...
> >>
> >> Thanks in advance,
> >> --
> >> Sanjib Saha
>
>|||From your description in this post, it seems your installation does not
install an user instance (I am not sure). However, form your first post,
according to the ConnectionString, you are trying to use an User Instance.
User Instance of SQL Server Express is a special installation of SQL Server
Express. It seems you need a bit more study on what an user instance is, so
that you can tell if your intallation is an user instance or not, and
whether you need an user instance or not. IMO, SQL Server Express's user
instance does not go with ODBC.
"sanjib" <sanjib@.discussions.microsoft.com> wrote in message
news:33A10BC3-2103-4BD3-B9E7-5AE71965D745@.microsoft.com...
> Roger/Norman,
> The whole point came like this. I am trying to install a SQl server driven
> 3rd party application and after the applications gets installed the
> database
> installation wizard appears and tht wizard asks me to connect to the SQL
> server using ODBC DSN, so I need to know the parameters for connecting to
> the
> SQL server, which I don't know it seems, b'case while installing SQL
> server
> 2005 it never asked me for a server name/login password. And now it is
> running using the 'Local System Account' i.e. Local Admin account that is
> me.
> May be my SQL server 2005 is using user instance, although I am not sure.
> The fact that I am not that much knowlegeble in SQL severs, much worked in
> microsoft programming languages, and SQl server for me is a database
> server
> tht always worked.
> I will try from the URL provided by Roger and let u know...
> Thanks,
> Sanjib
> --
> Sanjib Saha
>
> "Roger Wolter[MSFT]" wrote:
>> You can access an User Instance once it is started by specifying the
>> Named
>> Pipe name for the instance but if you haven't already started the user
>> instance with a managed code connection, ODBC won't start it. If your
>> main
>> application is using ODBC, I would recommend attaching the database to
>> the
>> main instance so it is always running and the ODBC connection can find
>> it.
>> There is more information on named instances and how to connect to them
>> here:
>> http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "sanjib" <sanjib@.discussions.microsoft.com> wrote in message
>> news:2AB671B3-FEAD-49C9-A309-8DBF8BF07815@.microsoft.com...
>> > Norman,
>> >
>> > There should be some way to connect to the SQl server 2005 Express user
>> > instance. I need to connect to this SQl server b'case one of my
>> > application
>> > is demandint that...There should be some way, tht we do not
>> > know...Anyway
>> > thanks...Someone pls guide me...
>> >
>> > Thanks,
>> > Sanjib
>> > --
>> > Sanjib Saha
>> >
>> >
>> > "sanjib" wrote:
>> >
>> >> Hi All,
>> >>
>> >> Recently I have installed SQl server express edition. I was able to
>> >> connect
>> >> to the SQL server from my .NET project using connection parameter
>> >> like:
>> >>
>> >> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
>> >> Settings\Raju\My
>> >> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
>> >> Security=True;Connect Timeout=30;User Instance=True
>> >>
>> >> Now I need to connect to the same SQL server from 'ODBC Data Source
>> >> Aministrator' wizard, that I am unable to do. The wizard is asking me
>> >> (for
>> >> system DSn and File DSN) for SQL server name (I do no know what to
>> >> give,
>> >> is
>> >> it (local) or something) and how to login authenticate (with windows
>> >> authentication or something). Now if I goto Services/SQl Server
>> >> (SQLEXPRESS)
>> >> , click LogOn I see that 'Local System Account' is checked. Pls tell
>> >> me
>> >> how
>> >> to connect to this SQl server using System DSN connection wizard.
>> >>
>> >> By the way I am the administrator of this machine where I have
>> >> instlled
>> >> the
>> >> SQl server...
>> >>
>> >> Thanks in advance,
>> >> --
>> >> Sanjib Saha
>>|||I think I may understand what you're asking now. You have two completely
unrelated elements in your posting. First you were able to use ADO.Net in
Visual Studio to create a database. This database was a user instance which
is why this came up in our answers. On a completely unrelated note you now
want to install a third party application that uses ODBC to connect to the
database.
Go into the Windows Control Panel and select Administrative Tools. One of
the tools is ODBC data sources. Select the System DSN or User DSN as
required for your application and click ADD. Select the SQL Native Client
Driver. Fill in a name which you get to choose (probably the application is
looking for a particular name), leave the description blank and put
.\SQLExpress in the server field. Select the defaults for the rest unless
you already know the database name you want to make your default database.
If not, the default will be "master".
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eQyQnkToGHA.956@.TK2MSFTNGP03.phx.gbl...
>I agree with Roger.
> If you HAVE to access the database on SQL Server Express via something
> other than ADO.NET2.0's System.Data SqlClient namespace, why use User
> Instannce? The whole point of using user instance is to limit the database
> setup/access within current user's privilidge of access to the computer.
> If a user has to use ODBC DSN Wizard, it may well requires the user has a
> bit more privilidge that usual. Also, an user instance is not available to
> other user even the other user uses the same computer.
>
> "sanjib" <sanjib@.discussions.microsoft.com> wrote in message
> news:2AB671B3-FEAD-49C9-A309-8DBF8BF07815@.microsoft.com...
>> Norman,
>> There should be some way to connect to the SQl server 2005 Express user
>> instance. I need to connect to this SQl server b'case one of my
>> application
>> is demandint that...There should be some way, tht we do not
>> know...Anyway
>> thanks...Someone pls guide me...
>> Thanks,
>> Sanjib
>> --
>> Sanjib Saha
>>
>> "sanjib" wrote:
>> Hi All,
>> Recently I have installed SQl server express edition. I was able to
>> connect
>> to the SQL server from my .NET project using connection parameter like:
>> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
>> Settings\Raju\My
>> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
>> Security=True;Connect Timeout=30;User Instance=True
>> Now I need to connect to the same SQL server from 'ODBC Data Source
>> Aministrator' wizard, that I am unable to do. The wizard is asking me
>> (for
>> system DSn and File DSN) for SQL server name (I do no know what to give,
>> is
>> it (local) or something) and how to login authenticate (with windows
>> authentication or something). Now if I goto Services/SQl Server
>> (SQLEXPRESS)
>> , click LogOn I see that 'Local System Account' is checked. Pls tell me
>> how
>> to connect to this SQl server using System DSN connection wizard.
>> By the way I am the administrator of this machine where I have instlled
>> the
>> SQl server...
>> Thanks in advance,
>> --
>> Sanjib Saha
>|||Roger/Norman,
Roger's trick to connect to the SQL server worked for me, I was able to use
the ODBC data source wizard to connect to the SQL server. It worked for me.
Now I will be happy if this SQL server let me create a user (for this SQL
server) for me, at least one user with all the DBA permission. Is tht
possible, pls let me know...
By the time I have started reading some materials for SQL server. ANother
thing with this SQL server 2005 express, there is no SQl explore window where
I can find create the SQL server Datavase/queries etc. Why is it so.
Although many thanks for your kind help and suggestions...
Sanjib
--
Sanjib Saha
"Roger Wolter[MSFT]" wrote:
> I think I may understand what you're asking now. You have two completely
> unrelated elements in your posting. First you were able to use ADO.Net in
> Visual Studio to create a database. This database was a user instance which
> is why this came up in our answers. On a completely unrelated note you now
> want to install a third party application that uses ODBC to connect to the
> database.
> Go into the Windows Control Panel and select Administrative Tools. One of
> the tools is ODBC data sources. Select the System DSN or User DSN as
> required for your application and click ADD. Select the SQL Native Client
> Driver. Fill in a name which you get to choose (probably the application is
> looking for a particular name), leave the description blank and put
> ..\SQLExpress in the server field. Select the defaults for the rest unless
> you already know the database name you want to make your default database.
> If not, the default will be "master".
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Norman Yuan" <NotReal@.NotReal.not> wrote in message
> news:eQyQnkToGHA.956@.TK2MSFTNGP03.phx.gbl...
> >I agree with Roger.
> >
> > If you HAVE to access the database on SQL Server Express via something
> > other than ADO.NET2.0's System.Data SqlClient namespace, why use User
> > Instannce? The whole point of using user instance is to limit the database
> > setup/access within current user's privilidge of access to the computer.
> > If a user has to use ODBC DSN Wizard, it may well requires the user has a
> > bit more privilidge that usual. Also, an user instance is not available to
> > other user even the other user uses the same computer.
> >
> >
> > "sanjib" <sanjib@.discussions.microsoft.com> wrote in message
> > news:2AB671B3-FEAD-49C9-A309-8DBF8BF07815@.microsoft.com...
> >> Norman,
> >>
> >> There should be some way to connect to the SQl server 2005 Express user
> >> instance. I need to connect to this SQl server b'case one of my
> >> application
> >> is demandint that...There should be some way, tht we do not
> >> know...Anyway
> >> thanks...Someone pls guide me...
> >>
> >> Thanks,
> >> Sanjib
> >> --
> >> Sanjib Saha
> >>
> >>
> >> "sanjib" wrote:
> >>
> >> Hi All,
> >>
> >> Recently I have installed SQl server express edition. I was able to
> >> connect
> >> to the SQL server from my .NET project using connection parameter like:
> >>
> >> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
> >> Settings\Raju\My
> >> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
> >> Security=True;Connect Timeout=30;User Instance=True
> >>
> >> Now I need to connect to the same SQL server from 'ODBC Data Source
> >> Aministrator' wizard, that I am unable to do. The wizard is asking me
> >> (for
> >> system DSn and File DSN) for SQL server name (I do no know what to give,
> >> is
> >> it (local) or something) and how to login authenticate (with windows
> >> authentication or something). Now if I goto Services/SQl Server
> >> (SQLEXPRESS)
> >> , click LogOn I see that 'Local System Account' is checked. Pls tell me
> >> how
> >> to connect to this SQl server using System DSN connection wizard.
> >>
> >> By the way I am the administrator of this machine where I have instlled
> >> the
> >> SQl server...
> >>
> >> Thanks in advance,
> >> --
> >> Sanjib Saha
> >
> >
>
>|||You can download Sql Server Management Studio Express (free) from MS and it
is desinged for managing SQL Server /Express,
"sanjib" <sanjib@.discussions.microsoft.com> wrote in message
news:7AC96FD6-B69E-4A90-8CB7-C5151A5BD193@.microsoft.com...
> Roger/Norman,
> Roger's trick to connect to the SQL server worked for me, I was able to
> use
> the ODBC data source wizard to connect to the SQL server. It worked for
> me.
> Now I will be happy if this SQL server let me create a user (for this SQL
> server) for me, at least one user with all the DBA permission. Is tht
> possible, pls let me know...
> By the time I have started reading some materials for SQL server. ANother
> thing with this SQL server 2005 express, there is no SQl explore window
> where
> I can find create the SQL server Datavase/queries etc. Why is it so.
> Although many thanks for your kind help and suggestions...
> Sanjib
> --
> Sanjib Saha
>
> "Roger Wolter[MSFT]" wrote:
>> I think I may understand what you're asking now. You have two completely
>> unrelated elements in your posting. First you were able to use ADO.Net
>> in
>> Visual Studio to create a database. This database was a user instance
>> which
>> is why this came up in our answers. On a completely unrelated note you
>> now
>> want to install a third party application that uses ODBC to connect to
>> the
>> database.
>> Go into the Windows Control Panel and select Administrative Tools. One
>> of
>> the tools is ODBC data sources. Select the System DSN or User DSN as
>> required for your application and click ADD. Select the SQL Native
>> Client
>> Driver. Fill in a name which you get to choose (probably the application
>> is
>> looking for a particular name), leave the description blank and put
>> ..\SQLExpress in the server field. Select the defaults for the rest
>> unless
>> you already know the database name you want to make your default
>> database.
>> If not, the default will be "master".
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Norman Yuan" <NotReal@.NotReal.not> wrote in message
>> news:eQyQnkToGHA.956@.TK2MSFTNGP03.phx.gbl...
>> >I agree with Roger.
>> >
>> > If you HAVE to access the database on SQL Server Express via something
>> > other than ADO.NET2.0's System.Data SqlClient namespace, why use User
>> > Instannce? The whole point of using user instance is to limit the
>> > database
>> > setup/access within current user's privilidge of access to the
>> > computer.
>> > If a user has to use ODBC DSN Wizard, it may well requires the user has
>> > a
>> > bit more privilidge that usual. Also, an user instance is not available
>> > to
>> > other user even the other user uses the same computer.
>> >
>> >
>> > "sanjib" <sanjib@.discussions.microsoft.com> wrote in message
>> > news:2AB671B3-FEAD-49C9-A309-8DBF8BF07815@.microsoft.com...
>> >> Norman,
>> >>
>> >> There should be some way to connect to the SQl server 2005 Express
>> >> user
>> >> instance. I need to connect to this SQl server b'case one of my
>> >> application
>> >> is demandint that...There should be some way, tht we do not
>> >> know...Anyway
>> >> thanks...Someone pls guide me...
>> >>
>> >> Thanks,
>> >> Sanjib
>> >> --
>> >> Sanjib Saha
>> >>
>> >>
>> >> "sanjib" wrote:
>> >>
>> >> Hi All,
>> >>
>> >> Recently I have installed SQl server express edition. I was able to
>> >> connect
>> >> to the SQL server from my .NET project using connection parameter
>> >> like:
>> >>
>> >> Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and
>> >> Settings\Raju\My
>> >> Documents\Sanjib SQL DBs\IBuyAdventure\IBuyAdventure.mdf;Integrated
>> >> Security=True;Connect Timeout=30;User Instance=True
>> >>
>> >> Now I need to connect to the same SQL server from 'ODBC Data Source
>> >> Aministrator' wizard, that I am unable to do. The wizard is asking me
>> >> (for
>> >> system DSn and File DSN) for SQL server name (I do no know what to
>> >> give,
>> >> is
>> >> it (local) or something) and how to login authenticate (with windows
>> >> authentication or something). Now if I goto Services/SQl Server
>> >> (SQLEXPRESS)
>> >> , click LogOn I see that 'Local System Account' is checked. Pls tell
>> >> me
>> >> how
>> >> to connect to this SQl server using System DSN connection wizard.
>> >>
>> >> By the way I am the administrator of this machine where I have
>> >> instlled
>> >> the
>> >> SQl server...
>> >>
>> >> Thanks in advance,
>> >> --
>> >> Sanjib Saha
>> >
>> >
>>

How to connect to the SQl server EXPRESS using 'ODBC Data Source A

Monday, March 26, 2012

How to Connect to SQL Server 200 objects

I using VB.NET 2005

I want to connect to my LAN SQL Server 2000 objects

using SMO How I can do that

please any answer

I using SQL Server Management Studio Express CTP

and How I can connect to My LAN SQL Server 2000 Instances

should I write "Computer_name\Instance_name" or What ?

Please My MAIN OBJECTIVE IS TO CONNECT TO SQL Server 2000 Instace ?

You should see all instances through the

EnumAvailableSqlServers

method including the instances from SQL Server 2000.

Computer_name\Instance_name is only needed if you connect to a named instance (either in 2k or 2k5). If you are connecting to a default instance you don′t need the named instance syntax.

HTH, Jens Suessmeyer.

How to connect to SQL Database, Create tables

Hi All, 1st of all happy New Year to all asp.net forum members
I am new at asp.net. I want to design a websiteusing asp.net as frontend and sql database as backend. I am able toconnect and add,update as well delete records when I use MsAcess andAsp.net using the following connection strings...
**********
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("/database/northwind.mdb"))
dbconn.Open()
sql="SELECT * FROM customers where city LIKE 'Berlin' order by city ASC"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
**********

But when I try to connect to sql database using the following connection strings I am unable to do so...

**************
SqlConnection myConnection = new SqlConnection("server=PLATINUM\VSdotNET;database=pubs;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter(" * from Authors", myConnection);

DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");

MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
MyDataGrid.DataBind();

**************
I have written the servername as "PLATINUM\VSdotNET" because when Iinstalled SQL SERVER 2000 I found a tray icon where the server name wasdisplaying the same (my computer name is PLATINUM).
When I used webmatrix I enterd the same server name and windowsauthentication I was able to create a database but How to createtable...

Please help me out
Thanks in advance...Please explain "I am unable to do so". Are you receiving an error message?|||Actually I don't know whether the connection string I am using is correct or not...
Because I am unable to create tables using the connection strigns what I told before...

when I try running this page I get the error message like
Compiler Error Message:CS1009: Unrecognized escape sequence
and the compiler shows a error in the connection string , So as far I guess there is problem with the connection string only.

If I get a sample page for what I am trying to do i.e connecting to SQLdatabase, Creating tables as well as accessing table my problem will besolved.
Thanks for your time.

|||Well, I always use this resource for building connection strings:http://www.connectionstrings.com/ Check out the SqlConnection (.NET) options under SQL Server. Yourproblem *might* be that you should use True, not Yes, forTrusted_Connection. In any case, I would try to stick as closelyas possible to the advice listed there.|||* from Authors is not a valid SQL Statement. Try SELECT * FROM Authors

How to connect to Remote SQL Server database using SQL Server 2005 Express

Hi,

I've an account with a hosting service provider online for SQL Server database. I've downloaded SQL Server 2005 Express from ASP.Net. How can I use it to connect to my SQL Server Database which is sitting on remote server? The hosting provider gave me following things to connect to the remote database.

Server Name
Database Name
User Name
Password

Regards,
A.K.R

Anyone?

|||I don't want to ask the obvious, but is there not a Read Me fileincluded with the download of SQL Express which would explain how toconnect?

Sorry I can't be more helpful, but I've only ever briefly looked at SQLExpress and decided to stick to MSDE since I could not see any benefitof using Express when MSDE is a cheap but far more powerful option thanSQL Express.

How to Connect to remote sql express from client comp/app?

Hi,

I think this is a taff problem for me when I connect to remote sql express from .net application

I use Connection String:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp/application

"... Login failed foruser MYCOMP\Guest... "

How to setup a user in the sql expss server to support Client/Server Application?

Anyone can help me?

regards.

md5

hi,

please have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2177202&SiteID=1&mode=1

regards

|||

Hi Andrea,

Ow.. I understand that.. its Loud and Clear...
Evrything goes Ok now. Thanks So munch for yur help.

regards
mudiasa sanatan (md5)

How to connect to remote MSDE database using VB.NET

Greetings
I have a VB.NET application that works great with a local MSDE database. But, when I try to revise my connection string to point to a remote MSDE server and database, it fails. The MSDE Server Manager can pick up the remote server and tell me its runni
ng, but when I try to register it using SQL Server, or use a DSN to connect to it, I get a "SQL Server does not exist or access denied. ConnectionOpen(Connect())" My connection string is:
Provider=SQLOLEDB.1;Server=DSS\VSDOTNET;Database=d atabase;User ID=sa;Password = xxxxx;Integrated Security=SSPI
Any help will be appreciated. Thanks...
- Tim B.
hi Tim
"Tim Balderramos" <pacmantab@.hotmail.com> ha scritto nel messaggio
news:28F08CF8-86CF-4C04-9E5A-900296EC78A7@.microsoft.com...
> Greetings
> I have a VB.NET application that works great with a local MSDE database.
But, when I try
>to revise my connection string to point to a remote MSDE server and
database, it fails. The
>MSDE Server Manager can pick up the remote server and tell me its running,
but when I try
>to register it using SQL Server, or use a DSN to connect to it, I get a
"SQL Server does not
>exist or access denied. ConnectionOpen(Connect())" My connection string
is:
> Provider=SQLOLEDB.1;Server=DSS\VSDOTNET;Database=d atabase;User
>ID=sa;Password = xxxxx;Integrated Security=SSPI
the most frequent causes of that kind of error are reported in
http://support.microsoft.com/default...06&Product=sql
KB article...
please verify the remote MSDE instance has enabled networkprotocols too,
using the remote Server Network Utilityes (svrnecn.exe)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I think that does it. Here is what my co-worker did:
During MSDE setup, use SECURITYMODE=SQL
Then use SVRNETCN.exe and enable TCP/IP as a network library. I did this on
both machines, but you may not need to do it for both (wasn't sure, but didn't
think it mattered).
Here's the connection string that was working -
Provider=SQLOLEDB;Data Source=wrctest;user id=sa;password=wrctest;Initial
Catalog=winthrop;Net=dbmssocn;Auto Translate=True;Persist Security
Info=False;Use Encryption for Data=False

How to connect to ODBC data source through C#. net

Hi experts,
I am doing an application which takes the DSN name from user in text
box and then open the ODBC data source dialog box. i.e. I want the
(ODBC data source administrator) form which comes after clicking on
ODBC data source icon in administrative tools.
Now my problem is that is it possible to call that system form
(utility) through my program or I have to design the same form (ODBC
data source administrator).
If I can call that form then please tell me how....i can do that?
Any help will be appreciated.
Thanks
DineshYou would be better off posting this in a C# newsgroup. This is for
Reporting Services.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1166091132.509799.4510@.f1g2000cwa.googlegroups.com...
> Hi experts,
> I am doing an application which takes the DSN name from user in text
> box and then open the ODBC data source dialog box. i.e. I want the
> (ODBC data source administrator) form which comes after clicking on
> ODBC data source icon in administrative tools.
> Now my problem is that is it possible to call that system form
> (utility) through my program or I have to design the same form (ODBC
> data source administrator).
> If I can call that form then please tell me how....i can do that?
> Any help will be appreciated.
> Thanks
> Dinesh
>

How to Connect to my Web-SiteSqlServer through Enterprise Manager?

Hi all,

I have Sql Server 2000 installed in my home and also i have a website that works with Sql Server2000 and .Net FrameWork v1.1 ,now i want to know is it possible to connect from
my home SqlServer to Host SqlServer( where my Website resides ) through Enterprise manager or Query Analizer to do some data-manipulation And how?

Thanks in advance.
Regards.

You have to ask for Enterprise Manager access from the hosting company if not they will not offer it. Then you can use the backup and restore wizard to move your databases between servers. Hope this helps.

How to Connect to a SQL server on a deferent machine using ASP.Net?

Hi,

I am trying to connect to a SQL server on a deferent machine using ASP.Net written in C#. The machine on which the SQL server reside dose not have the ASPNET user and the authentication is falling. Any ideas on how to connect using a different User. I changed the ASP.Net service on my machine to run as a deferent user MyUser, which is a domain user that have access to the server machine?

Note: We can not change the privileges on the server side.

Thanks.

Hi,

I am looking to run a web service as a deferent user; it is running, as ASPNET and I would like it to run as a domain user. I am not sure if this is a configuration issue in the Dev studio. Please help.

Thanks.

Rafat

|||

You should post on an ASP.Net forum for instructions on how to set a specific login to be used by your application for connecting to SQL Server.

If you have questions about how to setup such a login in SQL Server, let us know.

Thanks
Laurentiu

|||

Please don't open different threads on the same topic.

Thanks
Laurentiu

Friday, March 23, 2012

how to connect to .mdf file with sqmcmd?

Hi,
i have several mdf files for several asp.net applications. How can i reach
such a database with sqlcmd?
Thanks
BobThe .mdf is just the data file it is not the server. Have you looked up
sqlcmd in BooksOnLine?
Andrew J. Kelly SQL MVP
"Bob" <bob@.omail.nl> wrote in message
news:OuGOrItVHHA.496@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have several mdf files for several asp.net applications. How can i reach
> such a database with sqlcmd?
> Thanks
> Bob
>|||Take a look into the below URL:-
http://msdn2.microsoft.com/en-us/library/ms162773.aspx
Thanks
Hari
"Bob" <bob@.omail.nl> wrote in message
news:OuGOrItVHHA.496@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have several mdf files for several asp.net applications. How can i reach
> such a database with sqlcmd?
> Thanks
> Bob
>|||Thanks, i know that mdf is not the server. All i ask is: is it possible to
manage such a database with sqlcmd (something like 'use mymdf_file'). Maybe
that file is attached to the server ...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:%233v%23A1uVHHA.1036@.TK2MSFTNGP03.phx.gbl...
> The .mdf is just the data file it is not the server. Have you looked up
> sqlcmd in BooksOnLine?
> --
> Andrew J. Kelly SQL MVP
> "Bob" <bob@.omail.nl> wrote in message
> news:OuGOrItVHHA.496@.TK2MSFTNGP06.phx.gbl...
>|||Not that I know of. Ssqlcmd talks to the server instance not the file. You
can specify a db name in the connection string but not a file.
Andrew J. Kelly SQL MVP
"Ken" <kns@.shdv.sd> wrote in message
news:%23M3$h8yVHHA.4404@.TK2MSFTNGP03.phx.gbl...
> Thanks, i know that mdf is not the server. All i ask is: is it possible to
> manage such a database with sqlcmd (something like 'use mymdf_file').
> Maybe that file is attached to the server ...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
> news:%233v%23A1uVHHA.1036@.TK2MSFTNGP03.phx.gbl...
>

How to Connect SQL server Using ASP.net

Dear ASP.NET user...

I'm a new user in ASP.NET...
i have a problem facing now...
I'm using SQL server as my database...
how do i CONNECT my web application using ASP.NET to SQL server??

What should i do?
any coding do i need to insert to my web HTML?

what i heard from my friend...she said web config can connect the web application to SQL server...?...it that work?

pls help me guys and gals...

From jhwong2610
URGENTVery often the web.config is used to store the connection string - that alone does not connect you to a database - using the connection string you have to open a sqlConnection and then proceed from there.

My best recommendation would be to pick up one of the ASP.NET books and look at one of the examples that uses data binding.|||Hi:
Like This


Dim Conn As New SqlConnection("ConnectionStringHere")
Dim Conn As New SqlCommand("Select * From Table", Conn)
Dim DRs As SqlDataReader
Conn.Open()
DRs = Comm.ExecuteReader()
If DRs.Read() Then
 Response.Write(DRs("Column"))
End If
DRs.Close()
Conn.Close()

|||Add this to the web.config file:

<configuration>
<appSettings>
<add key="SQLConnStr" value="<your connection string" />
</appSettings
...

You can use the connectionstring in the code like this:

Dim str as connStr = System.Configuration.ConfigurationSettings.AppSettings("SQLConnStr")

How to connect MS AS 2005 with AMO in vb.net

Hi All,

I am working on MSAS 2005 and am new to this one but i worked on AS 2000(DSO).I just want to connect to the MS AS 2005 Server from vb.net by using AMO(Analysis Management Object).

It is really very urgent.Help needed.

Regards

vishu

Hi,

MSDN has some great examples over here Those should get you started..

C.
|||

I tried with that.But i am getting below error.

connection cannot be made.Ensure that the server is running.

What could be the problem i could not understand.I checked in my services all the services are running and i able to connect from MS Management Studio to Analysis services.I am giving my Machine Name(ServerName)/SQL2005.

But when i am trying to connect from VB.Net am getting error.

Please help on this.

Regards

vishu

|||It may help to paste your code.

C
|||

I am Placing that part of code.Please check it and let me know.

using System.AnalysisServices

//for strStringConnection am passing Data Source=ServerName

static Server ServerConnect( String strStringConnection)

{

string methodCaption = "ServerConnect method";

Server svr = new Server();

try

{

svr.Connect(strStringConnection);

}

#region ErrorHandling

catch (AmoException e)

{

MessageBox.Show( "AMO exception " + e.ToString());

svr = null;

}

catch (Exception e)

{

MessageBox.Show("General exception " + e.ToString());

svr = null;

}

#endregion

return svr;

}

Is there any wrong with the code. Please advise the same.

Regards,

vishu

|||Looks right to me, except i tag 'Provider=msolap' on the end of my connection string, like:

'Data Source=ServerName;Provider=msolap'

But i tried it without the provider, and it worked also. So, i'm not sure i can be of any help.

C
|||

Thanks.It is working now.

I need some info like can we connect Ms As 2000 and Ms As 2005 from any one like either with DSO or AMO.

it this possible or else we have to mainatin separate things for above servers.

help me on this.

|||

No you cannot connect to both AS 2000 and 2005 using one API

AMO = AS 2005

DSO = AS 2000

If your app needs to manage both you will need to use both API's. You do not need the "provider=msolap" in the connect method, you should only need to pass in the server name eg. svr.Connect("localhost")

|||

Thanks,I am sending my current environment where i am working OS:win 2003 server and MS AS 2005 and .net 2.0.In my previous environment win 2000 and MS As 2000 by using DSO and ADOMD i worked there is no issues in that.My client wants both has to work.becaz they are migrating servers one by one.coming to the code In this i had written some Webmethods through which am managing Ms As 2000 with DSO(connecting to MSAS and managing).So now they are going to change the servers in to MS As 2005 and they need the same kind of functionality should work from win 2003 and MS As 2005 & .net 2.0.For MDX purpose we used ADOMD.

This is some wht migration work.

Is there any chances to communicate and maintain the both the calls and how help needed.

|||You will have to develop 2 branches of code. Keep the existing one that works with AS2000 and develop a new one for AS2005. You can use code similar to that which I suggested here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1523691&SiteID=1 to detect which version you are talking to.|||

Thanks Darren.

It is helped me to go further.

|||

Hi Darren Gosbell

I am working on some migration project.it is from AS 2000 to AS 2005.

I have to create a role in AS 2005.Is this one is similer functionality like in AS 2000.

Plz let me know.

I am using AMO for making connection i can able to connect and now i have to create a role,In my previous development we used to create a role in database then after in cube with the help of DSO.Could you please help on this.

if possible if you have any sample to create a role in AS 2005 with AMO please send to me.

Thanks in Advance.

vishu

sql

Wednesday, March 21, 2012

how to connect data base in sql

Hi
I have a problem with my data base.
when i run my project (ASP.NET) in Vs 2005 my data base work.
but i run in localhost isn′t work.
my data base as a .mdf(sql server)
Can help-me ?
Are you getting an error or isnt simply not working? Do you meanwhen you run it under IIS [as oppose to the development server] itdoesnt work?
|||yes it is an error message and it happens when i'm runing the IIS
The Link of my server is http://ocodigo.no-ip.info
Open a "Registar" option, you full fill then spaces and press "Registar" Botton, and next you will see the error.
|||Hello WATT
I couldn't see the error message. I think u have to change something inthe "web.config" file, so we could see the error message.
Because i got this message:

Server Error in '/' Application.

Runtime Error

Description:Anapplication error occurred on the server. The current custom errorsettings for this application prevent the details of the applicationerror from being viewed remotely (for security reasons). It could,however, be viewed by browsers running on the local server machine.
Details: To enable thedetails of this specific error message to be viewable on remotemachines, please create a <customErrors> tag within a"web.config" configuration file located in the root directory of thecurrent web application. This <customErrors> tag should then haveits "mode" attribute set to "Off".

<!-- Web.Config Configuration File -->
<configuration>
 <system.web>
 <customErrors mode="Off"/>
 </system.web>
</configuration>

Notes:The current error page you are seeing can be replaced by a custom errorpage by modifying the "defaultRedirect" attribute of the application's<customErrors> configuration tag to point to a custom error pageURL.

<!-- Web.Config Configuration File -->
<configuration>
 <system.web>
 <customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
 </system.web>
</configuration>

Mow

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()

cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub

this code worked well.

we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).

Rgds

Pramod

Hi Pramod,

VB 08 (orcas) changed a lot. You can now make a connection without having to type one line of code (using Typed dataset)

But this still depend on your need !

|||

thanks verymuch for your valuable support . We tried dataset also. it dint worked.

please check the code

'================Sample Code==========

Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=test;Trusted_Connection=false;")

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

conn.Open()

Dim adp As New SqlDataAdapter("Select * From personal", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

'================Sample Code==========

Cannot open database "test" requested by the login. The login failed. Login failed for user 'Pramod\Administrator'.

plz tell how to build a connection string. (We tried datasource Configuration wizard)

Plz help

rgds

Pramod

|||

try this code

Code Snippet

'Store a connection string
Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Omar Abid\Mes Documents\data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'Create new SQL Connection
Dim sqlconnection As New SqlConnection(connectionstring)
Using sqlconnection
Dim sqlcommand As SqlCommand = sqlconnection.CreateCommand
Using sqlcommand
sqlcommand.CommandType = CommandType.Text
sqlcommand.CommandText = "select * from table1"
Dim adapter As New SqlDataAdapter(sqlcommand)
Using adapter
Dim datatable As New DataTable("user")
Using datatable
adapter.Fill(datatable)
DataGridView.DataSource = datatable

End Using
End Using
End Using
End Using

|||

Thanks dear

it worked well,

our practice is to use pass queries to database like

cn.execute (Insert into ((.......))

how the dame works with data adapter.

Plz help us with a sample code

rgds

Pramod

|||

please advice us regarding the code. is it the usual option available.

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select max(" & Field & ") + 1 as MAxid from " & tableName & IIf(Condition <> vbNullString, " where " & Condition, ""), cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

|||

sorry i don't know a lot on SQL. But I think that Adapter is the new technology.

For VB 9 there's also LINQ you may be interested on it

|||Hi

how can i connect with a client system. (in case of server we are using a path from the system). we want to connect it from networks also)

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()

cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub

this code worked well.

Rgds

Pramod

Hi Pramod,

VB 08 (orcas) changed a lot. You can now make a connection without having to type one line of code (using Typed dataset)

But this still depend on your need !

|||

thanks verymuch for your valuable support . We tried dataset also. it dint worked.

please check the code

'================Sample Code==========

Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=test;Trusted_Connection=false;")

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

conn.Open()

Dim adp As New SqlDataAdapter("Select * From personal", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

'================Sample Code==========

Cannot open database "test" requested by the login. The login failed. Login failed for user 'Pramod\Administrator'.

plz tell how to build a connection string. (We tried datasource Configuration wizard)

Plz help

rgds

Pramod

|||

try this code

Code Snippet

End Using
End Using
End Using
End Using

|||

Thanks dear

it worked well,

our practice is to use pass queries to database like

cn.execute (Insert into ((.......))

how the dame works with data adapter.

Plz help us with a sample code

rgds

Pramod

|||

please advice us regarding the code. is it the usual option available.

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select max(" & Field & ") + 1 as MAxid from " & tableName & IIf(Condition <> vbNullString, " where " & Condition, ""), cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

|||

sorry i don't know a lot on SQL. But I think that Adapter is the new technology.

For VB 9 there's also LINQ you may be interested on it

|||Hi

how can i connect with a client system. (in case of server we are using a path from the system). we want to connect it from networks also)

Friday, March 30, 2012

How to construct the query in Stored Proc

Wednesday, March 28, 2012

how to connect with sqlexpress2005 & asp.net for update insert delete codings

How to connect with SQL database?

How to connect using C#

How to connect to the SQl server EXPRESS using 'ODBC Data Source A

How to connect to the SQl server EXPRESS using 'ODBC Data Source A

Monday, March 26, 2012

How to Connect to SQL Server 200 objects

How to connect to SQL Database, Create tables

How to connect to Remote SQL Server database using SQL Server 2005 Express

How to Connect to remote sql express from client comp/app?

How to connect to remote MSDE database using VB.NET

How to connect to ODBC data source through C#. net

How to Connect to my Web-SiteSqlServer through Enterprise Manager?

How to Connect to a SQL server on a deferent machine using ASP.Net?

Friday, March 23, 2012

how to connect to .mdf file with sqmcmd?

How to Connect SQL server Using ASP.net

How to connect MS AS 2005 with AMO in vb.net

Wednesday, March 21, 2012

how to connect data base in sql

Server Error in '/' Application.

Runtime Error

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

clear log

Blog Archive

About Me