Working with a Local Data File in VS

re: Working with a Local Data File in VS

Ged Mead — Mon, 04 Jun 2007 10:35:00 GMT

Have really enjoyed viewing (and trying out) the techniques in your videos so far, Beth. Keep them coming!

In addition to covering the local data file isse in an upcoming video, may I suggest you also include the Many-To-Many DataBinding in a video also? (Just to reinforce the instructions given in your earlier blog item).

re: Working with a Local Data File in VS

Luc ROLLAND — Mon, 18 Jun 2007 15:58:00 GMT

I'm very insterested by your videos !

I redo your course but when I tried to create the stored procedures, I don't have the templates (Delet, Insert, Update) in the toolbox of my Visual Studio 2005 Professional. I've to create them manually.

What I have to do, to obtain that templates in the toolbox ?

Best regards.

re: Working with a Local Data File in VS

Beth Massi — Tue, 19 Jun 2007 00:27:04 GMT

Hi Luc,

Stored procedures are not created automatically for you. I wrote the stored procedures for the video and put them as snippets in the toolbox. You can create code snippets by selecting text in the code editor and then dragging that onto the toolbox. Then you can use that snippet of code again by dragging from the toolbox into the editor.

You do not need to create stored procs for your update, insert or delete to work on a table though. The TableAdapter wizard will generate those statements instead if you don't use stored procs.

Cheers,

-B

Deploying Interop UserControls with RegFree-COM

Beth Massi - Sharing the goodness that is VB — Fri, 29 Jun 2007 00:49:33 GMT

One of the features of the latest Interop Forms Toolkit is the ability to develop UserControls in addition

re: Working with a Local Data File in VS

Maclain Wiltzer — Thu, 26 Jul 2007 19:27:39 GMT

Thank you very much for clearly explaining everything. I am new to programming and tried everything to learn how to use SQL, but this finally helped me solve my problem!

re: Working with a Local Data File in VS

George Gomez — Thu, 01 Nov 2007 18:02:32 GMT

Please let Me Know How To Save a form in Visual basic 2005 When you have a computed columns

re: Working with a Local Data File in VS

Raj L — Wed, 16 Apr 2008 13:59:04 GMT

Thank you very much Beth, your tutorials have helped me a great deal :)

re: Working with a Local Data File in VS

Muhariz Jabeer — Mon, 12 May 2008 05:04:30 GMT

Thank you so much, i've been spending countless hours to see how i can get my winforms app to pick up the mdf file from the application running directory instead of hardcoding the project directory to the connection string. the trick was to use the macro that you suggested \|DataDirectory|\

Again that you :)

re: Working with a Local Data File in VS

Ravi — Thu, 12 Jun 2008 17:45:29 GMT

Hi,

I am in the process of a small database prog using MSAccess. I am using related data tables to add, edit, del, update. While my parent datatable gets updated and saved i am unable to update or save the child table. i am getting an error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows.".

Please let me know "how to enable Refresh Data Table in Advanced Options of Table Adapter Configuration Wizard in Visual Studio Express 2008"

Awaiting your response asap

Thanks

Ravi

ravi323in@hotmail.com

re: Working with a Local Data File in VS

ravi39 — Mon, 16 Jun 2008 07:02:46 GMT

Hi,

Please let me know "how to enable Refresh Data Table in Advanced Options of Table Adapter Configuration Wizard in Visual Studio Express 2008"

Awaiting your response asap (Posted earlier on 12/06/2008)

Thanks

Ravi

re: Working with a Local Data File in VS

Torp904 — Mon, 28 Jul 2008 20:00:59 GMT

Beth,

When you detach the mdf and then add the file into the project itself, does that mean I can transport the project to another machine that doesn't have SQL Server running and work from there?

Also, when I compile and distribute, does that mean I don't have to make sure users have SQL Express installed?

Scott

re: Working with a Local Data File in VS

Beth Massi — Mon, 28 Jul 2008 23:14:37 GMT

Hi Scott,

You still need to have SQL-Server Express installed to work with a local data file so you'll need to select the SQL Express redist in your deployment project.

A local data file is a detached MDF that can auto-attach and auto-detach itself to the service when you connect to it with a local user connection. It's a feature of SQL Server (and Express) 2005.

If you only need single-user access to a simple data store you may want to use SQL Server Compact Edition instead. This is just a simple file-based, single user data store so only a few assemblies need to be distributed and there is no separate install other than your app.

However if you want to go multi-user, you'll need to move to SQL Express.

HTH,

-B

re: Working with a Local Data File in VS

Bayan — Sun, 10 Aug 2008 23:54:04 GMT

Hello Beth,

Thx for the article. I tried to play with properties of my local database and still not having the data saved I have made an output parameter of the id of the data I'm entering to the database and outputting it on the console and still have when I run the program the id is 1 for the first time I enter values and the second time the id is 2 and no data is saved when I look in the database and if I close the application and rerun it i get the same for the 1st time the id is 1 and 2nd time is 2 and so on that's as if nothing was saved.

Thanks for your time.

Bayan

re: Working with a Local Data File in VS

Beth Massi — Tue, 12 Aug 2008 02:32:18 GMT

Hi Bayan,

Those ID's are only for the client-side dataset, the identity keys are generated by the database when you save them and may or may not be the same keys. Check the database table's contents by opening it directly in visual studio and see if the rows are there. You also should watch these videos on understanding data:

VS 2005 version: http://msdn.microsoft.com/en-us/vbasic/bb725824.aspx

VS 2008 verison: http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

HTH,

-B

re: Working with a Local Data File in VS

Karthik — Mon, 22 Sep 2008 10:54:45 GMT

sql1.Open();

SqlCommand ins_user = new SqlCommand();

ins_user.Connection = sql1;

string ins_query = "INSERT INTO User_Infoo(First_Name,Last_Name,Pass,UID,Email,Desg_ID,User_ID) Values(@Fn,@Ln,@p,@ID,@email,@DID,@user)";

SqlParameter p1 = new SqlParameter("@Fn", SqlDbType.NVarChar, 50);

SqlParameter p2 = new SqlParameter("@Ln", SqlDbType.NChar, 10);

SqlParameter p3 = new SqlParameter("@p", SqlDbType.Text);

SqlParameter p4 = new SqlParameter("@DID", SqlDbType.Int);

SqlParameter p5 = new SqlParameter("@user", SqlDbType.NChar,10);

//SqlParameter p6 = new SqlParameter("@Fn", SqlDbType.NChar, 50);

SqlParameter p6 = new SqlParameter("@email", SqlDbType.Text);

SqlParameter p7 = new SqlParameter("@ID", SqlDbType.Int);

ins_user.Parameters.Add(p1);

ins_user.Parameters.Add(p2);

ins_user.Parameters.Add(p3);

ins_user.Parameters.Add(p4);

ins_user.Parameters.Add(p5);

ins_user.Parameters.Add(p6);

ins_user.Parameters.Add(p7);

p1.Value = "Karthik";

p2.Value = "Uthaman";

p3.Value = "adfask";

p4.Value = 1;

p5.Value = "karthik_u";

p6.Value = "asdasd";

p7.Value = 1;

ins_user.CommandText = ins_query;

ins_user.CommandType = System.Data.CommandType.Text;

MessageBox.Show(ins_user.ExecuteNonQuery().ToString());

sql1.Close();

return true;

i set the Build Action = Content and Copy to Output directory = Copy if newer !!

Now i am able to insert and retreive data.. but the problem is that the inserted data is not visible when i click on the "Show Table Data" .

and also the stored data is available just for few compilations.. if i build the app for 3 pr more times , the data tht has entered before are lost... please help me out pppll ....

I don understand what kinda problem is thiss... somebodyyy plsss help me soon!!

re: Working with a Local Data File in VS

Beth Massi — Mon, 22 Sep 2008 18:35:10 GMT

Hi Karthik,

The article explains the problem in the second to last paragraph:

"One copy is in your project folder, this is the file you work with when making schema changes with the VS tools. The VS tools always use this connection. However, you'll notice that the connection in the My.Settings uses a macro called |DataDirectory| to open the connection when your app is running (AttachDBFileName=|DataDirectory|\OMS.mdf). The |DataDirectory| expands to the location the EXE is running during runtime."

If you don't want to work with two copies then I'd suggest working with an attached SQL server instance, it's a lot easier to manage at development time. You can always change the connection string in the app.config after deployment.

HTH,

-B

re: Working with a Local Data File in VS

Vivienne — Fri, 20 Feb 2009 09:18:49 GMT

Hey B!

I am new at visual basic 2005 and ur videos have been such a great help!

I have the same problem as Karthik while working with a local database file. By working with an attached SQL server instance, you mean creating a new server?

re: Working with a Local Data File in VS

Johan_Alberts — Thu, 19 Mar 2009 03:38:41 GMT

Thanks for your effort. I have tried it with .mdf, .mdb etc. same result. I make the slightest change and all the data is lost. I also cannot find the database under Program Files... am concerned as to how am I supposed to back it up. VB also doesn't allow me to point directly at the database under settings. Kindly assist!