Creating a tableadapter for web database

All replies

• 

  

  0

  Sign in to vote

  With an SQL Server database you should be using the same connection string either locally or remote.  You shouldn't be connecting to the mdf file because it can cause access violations where the database is shared.  The SQL Server has a service that runs which resolves conflicts when mutiple users try tp access the data at the same time.  The service locks data to prevent sharing errors. When you connect directly to the mdf file you are bypassing the service.

  The connection string I normally use for SQL is shown below.  If you have any issues with connection string go to www.connectionstrings.com

  Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
  Data Source=myServerAddress\SQLExpress;Initial Catalog=myDataBase;Integrated Security=SSPI;
  Data Source=myServerAddress\SQLStandard;Initial Catalog=myDataBase;Integrated Security=SSPI;
  

  If you don't know the connection string then use the one shown in the SQL Server management Studio (SSMS). the only thing that may change from a local and remote connection is the credentials ( Integrated Security, username, password).  There are lot of ways to setup credentials. the are three factors for credentials

  1) Group Policy where password are obtained from a password are verifued with a remote server.  This is used in a corporate intranet.

  2) Windows Credentials

  3) SSMS credentials.

  All three credentials effect the acess right to the database.

  ---

  jdweng

  Saturday, January 18, 2014 7:59 PM
• 

  

  0

  Sign in to vote

  Hi
  thank you for your response, Joel
  I found www.connectionstrings.com informative.
  However my connections strings work fine for listing the table, but not for creating a tableadapter.
  
    <connectionStrings>
      <add name="CS_OnLine" connectionString="Provider=SQLOLEDB;Data Source=winsqleu01.1and1.co.uk,1433;Initial Catalog=db33123456;User Id=dbo33123456;Password=xxxxxx;" providerName="System.Data.SqlClient"/>
    </connectionStrings>
  
  I took it from your reply that you thought I should be able to attach to my shared hosted database through SSMS.  
  I believe I would need more permissions to do this.  
  
  Would I need to be able to connect directly to the hosted version  of the tables to create the tableadapter?
  Can I turn off the validation of the "Choose your data connection" screen to allow me to use my online connection?
  
  Thanks Again

  Sunday, January 19, 2014 4:17 PM
• 

  

  0

  Sign in to vote

  I usually use DataAdapter and not TableAdapter so I checked th emsdn site to findoput what the differeneces are (see below).  The Table adapter looks like is can contain multiple dataadapters.  The dataadapter/table is just the mechanism for making the connection through the network and doesn't contain data.  A datatable is a local storage object inside the net library that contains temporary memory storage that is part of a project.  When you modify the SQL Server database you are using a GIU that is part of visual Studio Menu structure and it is not part of an actual released project.  there isn't a manual way of modifing a SQL Databae inside a released project unless you build you own GUI.

  The permission are complicated for configuring a SSMS database (especiially for remove access) and I would leave it to an expert who installed the database.  It requires knowledge of windows credentials and SSMS credentials as well as an understanding of networking.  I was a network engineer for 5 years and sometimes have issues with the credentials because I'm not an adminstrator who has sufficient priveledges on my company network to do all the configurations required.  

  TableAdapter

  Unlike standard data adapters, TableAdapters can contain multiple queries to fill their associated data tables. You can define as many queries for a TableAdapter as your application requires, as long as each query returns data that conforms to the same schema as its associated data table. This enables loading of data that satisfies differing criteria. For example, if your application contains a table of customers, you can create a query that fills the table with every customer whose name begins with a certain letter, and another query that fills the table with all customers located in the same state. To fill a Customers table with customers in a given state you can create a FillByState query that takes a parameter for the state value: SELECT * FROM Customers WHERE State = @State. You execute the query by calling the FillByState method and passing in the parameter value like this: CustomerTableAdapter.FillByState("WA"). For more information, see How to: Create TableAdapter Queries.

  In addition to queries that return data of the same schema as the TableAdapter's data table, you can add queries that return scalar (single) values. For example, creating a query that returns a count of customers (SELECT Count(*) From Customers) is valid for a CustomersTableAdapter even though the data returned does not conform to the table's schema.

  ---

  jdweng

  Sunday, January 19, 2014 6:10 PM
• 

  

  0

  Sign in to vote

  The multi table aspect of the tableadapter fit well with my requirement to subset a list based on different drop lists.I shall have to find a different way to achieve this if I can't get it to work.

  I suspect the easiest method may be to upgrade to a dedicated server where I can have more control.

  Thanks

  Sunday, January 19, 2014 7:39 PM
• 

  

  1

  Sign in to vote

  Hello,

  Thank you for your post.

  I am afraid that the issue is out of support range of VS General Question forum which mainly discusses the usage of Visual Studio IDE such as WPF & SL designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System and Visual Studio Editor.

  Because your issue is related to ASP.NET Web Application development, I suggest that you can consult your issue on ASP.NET forum: http://forums.asp.net/  for better solution and support.

  Best regards,

  ---

  We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
  Click HERE to participate the survey.

  • Proposed as answer by Just Karl Monday, January 20, 2014 8:07 PM
  • Marked as answer by Just Karl Monday, February 3, 2014 6:44 PM

  Monday, January 20, 2014 9:31 AM
• 

  

  0

  Sign in to vote

  Thank you Amanda, I shall repost there as it does look more suitable.
  

  Monday, January 20, 2014 9:52 AM