Home Dashboard Directory Help
Search

Accessing SQL Server databases from Visual Studio Express 2010 by Code-player


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 716744
Opened: 1/5/2012 7:18:26 AM
Access Restriction: Public
Moderator Decision: Sent to Engineering Team for consideration
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

VS 2010 Express (Ver 4.0.30319) has a workable method for connecting to an existing Access database which I have used successfully, but my attempts to create and connect to a new SQL Server database have been unsuccessful.

My code seems to add tables and fields to the database successfully, but when I try to add a TableAdapter to my VB form a message informs me that the database is empty.

I found the following post on a VS forum:
---------------
This is a limitation of the Express editions of Visual Studio. They offer very limited designer support for databases. For example, you can't connect to a standard SQL Server database using the Visual Studio designers using an Express edition, only a "user instance" database.
You can still work with MySQL databases via code using the .NET Framework.
...
David Sceppa Marked As Answer by Parth Joshi Sunday, July 04, 2010 10:10 AM
---------------

What is a "user instance" database?
Does this mean that it is not possible to create a new SQL Server database from VS 2010 Express?
If so, how can I create a new database? Must I resort to Access?
Details
Sign in to post a comment.
Posted by Code-player on 2/7/2012 at 4:26 AM
Your reference link should prove very useful. Thanks again for your help. This problem is now resolved.
Posted by Microsoft on 2/6/2012 at 5:00 PM
Yes, what you did (through the DB Explorer add/edit schema) is the right way to define the schema for the new created database. Because that feature is quite old, related documents can be a little bit old.

You can find one here: http://msdn.microsoft.com/en-us/library/33wwc2yw(VS.80).aspx

THe database explorer is a limited version of the server explorer. You may see some function limitation of it. The server explorer is the same window in the full product. If you find other work through or documents referencing server explorer, some of the function is exposed in the database explorer as well. So, you may find documents for the server explorer to understand how to work with database in the visual studio.

Posted by Code-player on 2/4/2012 at 4:33 AM
Thank you; I have now successfully defined and connected to a new SQL database using VB in Visual Studio.

I understood the distinction between the source database and the viewing dataset, but I could find no way within Visual Studio to define the schema for a new SQL database (all my previous DBs had been MS Access). After some trial and error I finally found that right-clicking a database in DB Explorer enabled me to add tables and configure their schemas. After that it was all plain sailing. Perhaps some guidance in the documentation might be helpful for VS newbies like me.

Although I began defining DB schemas using ADABAS in 1973 (I brought the product to the UK), the mechanics of the OO paradigm are new to me, so your help was most welcome.
Posted by Microsoft on 2/2/2012 at 12:38 PM
Hi, Code-player,

After reviewing your video again, I think this might be the issue that you were confused by the concept of the data set designer. The data set designer is to define a dataset, which is like a samll in-memory version database, with some tables and relationships. Of course, the dataset itself is not a database, and the schema you defined in the dataset only exists in the dataset, it will not be imported to the SQL database.

Generally, you define the schema of your SQL database first, and then connect to it (in server explorer), and drag/drop (import) tables to the dataset. Table adaptors will be generated, they are a combination of a datatable defined in the dataset, and a bridge layer to sync data between the dataset and the database server. You can also create data tables inside the dataset, but those tables are only available in the dataset, and not in the database.

In your video, I think you create several datatables in the dataset, and try to create a table adaptor, and expect to see those tables in the wizard. You won't see those tables, because the table adaptor is to connect tables inside database (and you have to create a connection between the dataset and the database). Data tables inside dataset are not targets for table adaptors.

The easiest way is to connect to the SQL DB in the server explorer, and define new tables there, then drag them to the dataset designer surface, the connection will be created for you, and also table adaptors.

Let me know if this is the case.

Thanks
Posted by Microsoft on 2/1/2012 at 12:07 PM
Hi Code-player,

We have difficult to reproduce the issue you saw. Can you tell us what kind of database you are using? Did you create the database within the project (and a new .mdf file was added)?
The video doesn't show what is in the project, and what is in the server explorer window.

Thanks
Posted by Code-player on 2/1/2012 at 3:51 AM
I am using Microsoft Visual Studio 2010 Express Version 10.0.30319.1 RTMRel
and Microsoft .NET Framework Version 4.0.30319 RTMRel.

My application is written in Visual Basic for Windows.

I am NOT using Visual Studio Web Developer Express or Express for Windows Phone.
Posted by Microsoft on 1/31/2012 at 10:50 AM
Hi, Code-player,

We try to reproduce your issue. We do have several different Express products, which all belong to the VS Express family. Can you tell us which Express product you are using? (For example, you are using the Visual Studio Web Developer Express, or Express for Windows Phone.)

Thanks
Posted by Code-player on 1/13/2012 at 6:57 AM
I have sent you a Jing file, 716744.swf, as requested. This illustrates how I am unable to create a TableAdapter because the wizard can find no tables, although the database schema clearly shows there are three tables in home_inventoryDataSet.
Posted by Microsoft on 1/12/2012 at 5:24 AM
Hi Code-player,

Could you please help to capture a video to show the whole process?

If the video file to too large, please help to package it into a zip file and name it Feedback-716744.zip.
You can upload your file to workspace:

https://sftasia.one.microsoft.com/choosetransfer.aspx?key=5d606cdd-4289-47af-bd1d-6ab77876dfe7
Password: @tlZ3+cda


If you need help to capture a video file, you may like to try Jing: http://www.techsmith.com/jing/

It would be greatly appreciated if you could provide us with that information as quickly as possible.

Thanks again for your efforts and we look forward to hearing from you.
Posted by MS-Moderator09 [Feedback Moderator] on 1/5/2012 at 11:19 PM
Thank you for submitting feedback on Visual Studio 2010 and .NET Framework. Your issue has been routed to the appropriate VS development team for review. We will contact you if we require any additional information.
Posted by MS-Moderator01 on 1/5/2012 at 7:43 AM
Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(http://support.microsoft.com)
Sign in to post a workaround.