HELP!! SQL Server Express 2005 engine connection to my DB

Preface, I am just starting to learn about database and web development.

I installed VS.net 2005 which updated my web site project file and automatically attached it to a database. It connected to ".\app_data\aspnetdb.mdf". However it messed something up and the connection didn't work. If I create a project from scratch, it connects to its DB just fine, but not my upgraded project.

However, when I publish my web site, I need to use ODBC. So, my thought was to simply set up the system locally in a way that will make it easy to connect on the web site. I was told to add a system DSN to the "Microsoft ODBC Administrator". I tried to add a "SQL Server" and give it the database name but it failed to connect.

So, then I ran the SQL Server Configuration Manager and looked at the server properties and found this string under the advanced -> startup parameters.."-dc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf"

My database is in an entirely different folder. The database it looks like it is servicing is a "master.mdf" database installed under the SQL server install folder. So, I figured that I found my problem. I just have to change this file to map to mine. I replaced these three strings with paths to my aspnetdb.mdf/ldf files, and created a LOG folder. Howwever, it wasn't able to connect to this database. It said something like "it doesn't exist or you don't have permission", even though I gave it the right path.

So, I'm at a loss for how to get my application to be able to use the ODBC interface to connect to its very own database on my local system.

What do I need to do? Please help!

You need to create a Windows Authentication account on the server for ASPNET with a password.

When an .aspx page loads and tries to access the server, it uses Windows Authentication unless you code the connectionstring to use SQL authentication.

After you create the ASPNET Account, make your connection string look like this:

connectionString="Data Source=ODC01;Initial Catalog=OEM;Integrated Security=True"

Adamus

|||

Thanks you for the feedback. Unfortunately I'm still a bit confused. My problem is that I don't have a complete picture of how this is supposed to work.

Are you saying that I must set up a the server to use an account that uses Windows Authentication before I can attach it to my own MDB file? In other words, it will only connect to the default master.mdb file until I create an account?

Also, using "local" or "system" isn't good enough, I have to specify a user account even on my own personal computer running XP? I am the only one who uses this system, and there is no windows server running on the network. This is my home setup.

This is the map that I understand. There are 3 connections that must be made for this to work.

mydb.mdb --1 (get server to serve my DB)--> SQL Server express 2005 --2 (tell ODBC about my DB exposed by the server)--> ODBC --3 (using the connection string)--> Website.

Right now I beleive none of the 3 connections are working, and I feel like I need to get the step 1 done before I can establish and verify step 2 and then finally use the connection string in step 3.

Are you saying that I must create a Windows Authentication user account before step 1 will work? Does that simply meen that I create a user called "SQL Server" with minimal access on the computer and log into it whenever I want it to connect?

Is there a "complete idiots guide to establishing a SQL server 2005 connection from your web site to your database" book that I could buy?

|||

I take it all back. You were right. Now I understand. I actually had the account set up correctly on the server side, but I wasn't logging in correctly.

Second, when I tried to attach the server to ODBC I was using my computer name alone, when I needed to use <computer_name>\SQLExpress.

Now I have to figure out the connection string. But I am finally over my biggest hurdle. Thanks!

HELP!! SQL Server Express 2005 engine connection to my DB

Preface, I am just starting to learn about database and web development.

I installed VS.net 2005 which updated my web site project file and automatically attached it to a database. It connected to ".\app_data\aspnetdb.mdf". However it messed something up and the connection didn't work. If I create a project from scratch, it connects to its DB just fine, but not my upgraded project.

However, when I publish my web site, I need to use ODBC. So, my thought was to simply set up the system locally in a way that will make it easy to connect on the web site. I was told to add a system DSN to the "Microsoft ODBC Administrator". I tried to add a "SQL Server" and give it the database name but it failed to connect.

So, then I ran the SQL Server Configuration Manager and looked at the server properties and found this string under the advanced -> startup parameters.."-dc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf"

My database is in an entirely different folder. The database it looks like it is servicing is a "master.mdf" database installed under the SQL server install folder. So, I figured that I found my problem. I just have to change this file to map to mine. I replaced these three strings with paths to my aspnetdb.mdf/ldf files, and created a LOG folder. Howwever, it wasn't able to connect to this database. It said something like "it doesn't exist or you don't have permission", even though I gave it the right path.

So, I'm at a loss for how to get my application to be able to use the ODBC interface to connect to its very own database on my local system.

What do I need to do? Please help!

You need to create a Windows Authentication account on the server for ASPNET with a password.

When an .aspx page loads and tries to access the server, it uses Windows Authentication unless you code the connectionstring to use SQL authentication.

After you create the ASPNET Account, make your connection string look like this:

connectionString="Data Source=ODC01;Initial Catalog=OEM;Integrated Security=True"

Adamus

|||

Thanks you for the feedback. Unfortunately I'm still a bit confused. My problem is that I don't have a complete picture of how this is supposed to work.

Are you saying that I must set up a the server to use an account that uses Windows Authentication before I can attach it to my own MDB file? In other words, it will only connect to the default master.mdb file until I create an account?

Also, using "local" or "system" isn't good enough, I have to specify a user account even on my own personal computer running XP? I am the only one who uses this system, and there is no windows server running on the network. This is my home setup.

This is the map that I understand. There are 3 connections that must be made for this to work.

mydb.mdb --1 (get server to serve my DB)--> SQL Server express 2005 --2 (tell ODBC about my DB exposed by the server)--> ODBC --3 (using the connection string)--> Website.

Right now I beleive none of the 3 connections are working, and I feel like I need to get the step 1 done before I can establish and verify step 2 and then finally use the connection string in step 3.

Are you saying that I must create a Windows Authentication user account before step 1 will work? Does that simply meen that I create a user called "SQL Server" with minimal access on the computer and log into it whenever I want it to connect?

Is there a "complete idiots guide to establishing a SQL server 2005 connection from your web site to your database" book that I could buy?

|||

I take it all back. You were right. Now I understand. I actually had the account set up correctly on the server side, but I wasn't logging in correctly.

Second, when I tried to attach the server to ODBC I was using my computer name alone, when I needed to use <computer_name>\SQLExpress.

Now I have to figure out the connection string. But I am finally over my biggest hurdle. Thanks!

Help with UPDATE

I have a column in one of my tables that needs to have about half of its
values updated.

The data looks like this:

A\B0andSomeVaryingStuff.mp3

I'd like to update it to:

A\C-B0andSomeVaryingStuff.mp3

The A, \-sign, B, C, .mp3 and - are literals. The
"andSomeVaryingStuff" changes. All I want to do is to replace the \B
with \C-B.

So I need to say "Find the instances where there is an A\B0 and replace
the A\B0 with A\C-B0 leaving the andSomeVaryingStuff.mp3 the same."

Note that every row will not need to be updated -- only the ones where
the column data begins A\B0.

Can you help?

Thank you,

-- RickYou could use the REPLACE function.

As a safeguard make a new column on your table and run this statement...

UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO', 'A\C-B0')

If it works good, you may be able to then use "SET myOriginalColumn" and
remove the new column. May need tweaked, but see what happens?

:)

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Guinness Mann" <GMann@.dublin.com> wrote in message
news:MPG.1a22a815a633b89898972a@.news.newsguy.com.. .
> I have a column in one of my tables that needs to have about half of its
> values updated.
> The data looks like this:
> A\B0andSomeVaryingStuff.mp3
> I'd like to update it to:
> A\C-B0andSomeVaryingStuff.mp3
> The A, \-sign, B, C, .mp3 and - are literals. The
> "andSomeVaryingStuff" changes. All I want to do is to replace the \B
> with \C-B.
> So I need to say "Find the instances where there is an A\B0 and replace
> the A\B0 with A\C-B0 leaving the andSomeVaryingStuff.mp3 the same."
> Note that every row will not need to be updated -- only the ones where
> the column data begins A\B0.
> Can you help?
> Thank you,
> -- Rick|||In article <ug8ub.4379$zz.1972696130@.newssvr30.news.prodigy.co m>,
jerry@.antech.biz.nospam says...
> You could use the REPLACE function.
> UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO', 'A\C-B0')

I like it. I'm a little confused, though. Don't I need a WHERE clause?

-- Rick|||No where clause is required unless you want to selectively update certain
data based on column values.

You could do this if you want...

UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO', 'A\C-B0')
Where myOriginalColumn LIKE '%A\BO%'

But if you are making a new column for the modified (replaced) value it will
not show up since the where clause restricts it to only the rows that need
updated. If you are not making a new column, then the where clause would be
great. Another thing... if you want this to run periodically you can setup
a Sql Agent Job to execute this statement as T-SQL and give it a schedule,
or even run when the server is at idle. Of course, the best practice would
be to eliminate the data entry problem when it's entered. But anyway...

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Jerry Boone" <jerry@.antech.biz.nospam> wrote in message
news:ug8ub.4379$zz.1972696130@.newssvr30.news.prodi gy.com...
> You could use the REPLACE function.
> As a safeguard make a new column on your table and run this statement...
> UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO',
'A\C-B0')
> If it works good, you may be able to then use "SET myOriginalColumn" and
> remove the new column. May need tweaked, but see what happens?
> :)
> --
> Jerry Boone
> Analytical Technologies, Inc.
> http://www.antech.biz
>
> "Guinness Mann" <GMann@.dublin.com> wrote in message
> news:MPG.1a22a815a633b89898972a@.news.newsguy.com.. .
> > I have a column in one of my tables that needs to have about half of its
> > values updated.
> > The data looks like this:
> > A\B0andSomeVaryingStuff.mp3
> > I'd like to update it to:
> > A\C-B0andSomeVaryingStuff.mp3
> > The A, \-sign, B, C, .mp3 and - are literals. The
> > "andSomeVaryingStuff" changes. All I want to do is to replace the \B
> > with \C-B.
> > So I need to say "Find the instances where there is an A\B0 and replace
> > the A\B0 with A\C-B0 leaving the andSomeVaryingStuff.mp3 the same."
> > Note that every row will not need to be updated -- only the ones where
> > the column data begins A\B0.
> > Can you help?
> > Thank you,
> > -- Rick|||In article <Htaub.826$xE6.230763650@.newssvr11.news.prodigy.com >,
jerry@.antech.biz.nospam says...
> You could do this if you want...
> UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn,
> 'A\BO', 'A\C-B0') Where myOriginalColumn LIKE '%A\BO%'

Thanks, Jerry. It worked great as originally posted. After I posted
the query about WHERE (yuk, yuk) I tried it on a restricted range and it
worked so I updated the data in place. (I have a backup.)

> ...the best practice would be to eliminate the data entry
> problem when it's entered.

Roger that. I found the data entry problem halfway through the entry
process and fixed it then. This is a one-time update to fix the entries
done before I fixed the process.

Thanks again!

-- Rick