Problem with updating MDF Data file as opposed to updating a running SQL Server DB

  • Thread starter Thread starter multiformity
  • Start date Start date
M

multiformity

I posted this before, with no response, but now I have boiled the
problem down to it's core...

( see
http://groups.google.com/group/micr...5387d/c2727e5ee9d641c3?hl=en#c2727e5ee9d641c3
for the original post )



It would seem that I could query a MDF file just fine and select data
from it, but updates and inserts to the MDF file would just plain not
work. When I say query an MDF file, I mean that my connection string
pointed to a data file, rather than an activly running SQL Server
database. Attaching the MDF file, and changing the connection string to
point to the SQL Server instance rather than an MDF data file made the
existing code work as expected. This is going to be a big pain because
we do not want to require for people to be running SQL Server on their
machines.

Any Ideas on this odd issue??

Andrew Baldwin
 
I have never heard of attaching to the MDF file. Your users can run SQL MDSE
for free on clients, you know.

So you have the MDF file local on all machines??

Jeff
 
I posted this before, with no response, but now I have boiled the
problem down to it's core...

( see
http://groups.google.com/group/micr...5387d/c2727e5ee9d641c3?hl=en#c2727e5ee9d641c3
for the original post )



It would seem that I could query a MDF file just fine and select data
from it, but updates and inserts to the MDF file would just plain not
work. When I say query an MDF file, I mean that my connection string
pointed to a data file, rather than an activly running SQL Server
database. Attaching the MDF file, and changing the connection string to
point to the SQL Server instance rather than an MDF data file made the
existing code work as expected. This is going to be a big pain because
we do not want to require for people to be running SQL Server on their
machines.

Any Ideas on this odd issue??

Andrew Baldwin
Click to expand...

If you're using an MDF file in your development project that is attached to SQL
2005 Express, you cannot use that MDF file on a machine that does not have SQL
2005 Express installed. The MDF file is just a file it is not a DB Engine. The
two work together.

I think you've headed down the wrong path. If you can't have SQL Server 2005
Express installed on the user's machine you should use MS Access databases (MDB,
not MDF).

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
I don't know if you guys know what I am doing.

In Visual C# Express, I created a windows application, then "Added" a
"Database File". Doing this created an MDF file in my project folder,
and added in a connection string into my "App.config" file for me. This
connection string specifically pointed to data file, and added the
database into my database explorer window in the IDE. From there I
could create a new Data Access Layer (xsd) with a simple drag and drop
and viola, I had a typed dataset for the table in the DB. I have
watched some Atlas tutorials that microsoft provides where they have
done exactly this, but through VWD Express, and not VC# Express.

It would seem with this option, then one could use a SQL Server MDF
file and NOT have to have SQL Server running on the target machine. It
would seem that MS is trying to give people the option to use SQL
Server MDF files instead of MS Access MDB files from the get go on
their development project, with the option to host the MDF files in an
actual instance of SQL Server later down the line.

I am actually going to just use the "SaveXML" and "ReadXML" methods off
of the typed dataset that the IDE created for me, but it is still kinda
ghetto that Microsoft gives you the option to use an MDF file, but it
doesn't work fully....

AB
 
I don't know if you guys know what I am doing.

In Visual C# Express, I created a windows application, then "Added" a
"Database File". Doing this created an MDF file in my project folder,
and added in a connection string into my "App.config" file for me. This
connection string specifically pointed to data file, and added the
database into my database explorer window in the IDE. From there I
could create a new Data Access Layer (xsd) with a simple drag and drop
and viola, I had a typed dataset for the table in the DB. I have
watched some Atlas tutorials that microsoft provides where they have
done exactly this, but through VWD Express, and not VC# Express.

It would seem with this option, then one could use a SQL Server MDF
file and NOT have to have SQL Server running on the target machine. It
would seem that MS is trying to give people the option to use SQL
Server MDF files instead of MS Access MDB files from the get go on
their development project, with the option to host the MDF files in an
actual instance of SQL Server later down the line.

I am actually going to just use the "SaveXML" and "ReadXML" methods off
of the typed dataset that the IDE created for me, but it is still kinda
ghetto that Microsoft gives you the option to use an MDF file, but it
doesn't work fully....
Click to expand...

I'm not sure if this is the problem, but I think that if you add the MDF
file to your project then (by default) it's put in the project's root
directory then copied to the bin directory when you run the program. This
has two nasty side-effects: if you run the program and then look at the MDF
in the root directory you won't see any changes (but the changes will be in
the version in the bin directory); if you run the program agin the changes
in the bin directory will be lost as the file is copied again from the root
directory. I think you can change the default behaviour by altering the
"Copy to Output Directoty" property of the MDF file.

Chris Jobson
 
Well crud,

I was hoping that they had included the classes needed to connect to
the MDF file in .Net 2.0 (considering that SQL Server Express is so
..Net friendly, I had figured that this was the case).

Looks like I will simply serialize the data to XML instead, and
load/save it when the application opens/closes. Not ideal, but ohh
well. I only have one table currently, so it will be bearable, but now
I am going to have to seriously re-consider adding anything new to the
data structure...

Again, seems that they would make it run without having to have SQL
Server running on the client machine. The only benefit is like the MSDN
page said that you can have multiple copies of the same program runinng
with different database files, and not have to attach a million DB's to
the database for no reason. I guess it just isn't possible to run with
a shell set of DLL's like MDAC does to get into MS Access, but you
don't have to have Access installed. Maybe they will add this in
3.0.....

Anywho, thanks for all your help guys, this question is answered :D

AB
 
I don't know if you guys know what I am doing.
Click to expand...

I do.
In Visual C# Express, I created a windows application, then "Added" a
"Database File". Doing this created an MDF file in my project folder,
and added in a connection string into my "App.config" file for me. This
connection string specifically pointed to data file, and added the
database into my database explorer window in the IDE. From there I
could create a new Data Access Layer (xsd) with a simple drag and drop
and viola, I had a typed dataset for the table in the DB. I have
watched some Atlas tutorials that microsoft provides where they have
done exactly this, but through VWD Express, and not VC# Express.
Click to expand...

The machine used for the tutorial had SQL Server Express 2005 installed on it.
They assumed that those viewing the tutorial would understand that. Their
assumption was, in your case, wrong.
It would seem with this option, then one could use a SQL Server MDF
file and NOT have to have SQL Server running on the target machine. It
would seem that MS is trying to give people the option to use SQL
Server MDF files instead of MS Access MDB files from the get go on
their development project, with the option to host the MDF files in an
actual instance of SQL Server later down the line.

I am actually going to just use the "SaveXML" and "ReadXML" methods off
of the typed dataset that the IDE created for me, but it is still kinda
ghetto that Microsoft gives you the option to use an MDF file, but it
doesn't work fully....

AB
Click to expand...

Well, I do understand what you're doing and you can't get there from where you
are. MDF files must be attached to an installation of SQL Server to work.
That's why when you attached the file to SQL Server your queries worked.

I believe if you read the documentation on SQL Server Express and MDF files you
will have a better understanding of what you are doing.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Thanks Otis, the application is actually for all intents and purposes
done. The next step is to break out everything into the XML Dataset
files, but I will worry about that later.

Jeff, this is going to be a simple yet backup program that we may sell
for 10 bucks online. It already lets you schedule the jobs and puts
them in the task scheduler for you. You can save and load error logs of
files that didn't copy on one backup and re-try the files that could
have been locked during your previous backup, and some other snazzy
features. Having to have end users install and configure SQL Server on
their machines will be way too complicated and cumbersome for many
users. Again, it's just one table that will hold the job info for the
backup jobs, so it should be perfectly acceptable to just use an XML
file for the data.

Thanks again guys! This has been the most usefull response to any of my
postings... ever :D

AB
 
Back
Top