none
Deleting multiple records from a SQL Express table RRS feed

  • Question

  • I have a SQL Express DB used only by my application which is distributed to a couple hundred users.  This DB shows in my server explorer, and I access it through data binding on my forms.  However I have a download function that retrieves data from another server and then inserts it into this database on demand.  That part works great.  The problem is that when the user clicks the button to download the latest data, I need to delete the existing rows in the table for that set of data.  Easy enough right?  I just need to execute a SQLCommand with a DELETE statement.  Problem is I can't connect to the SQL Express DB to save my life...

     

    SqlConnection thisConnection = new SqlConnection();

    //@"Data Source=.\SQLEXPRESS;database=mydb.mdf;Connect Timeout=30;User Instance=True;Integrated Security=SSPI;"

    thisConnection.ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=SSPI";

    thisConnection.Open();

     

    SqlCommand tmpcmd = new SqlCommand("DELETE FROM mytable WHERE ServerName = '" + Server["ServerName"].ToString() + "'", thisConnection);

    tmpcmd.ExecuteNonQuery();

     

    Results in...

     

    Cannot open database "mydb" requested by the login. The login failed.
    Login failed for user 'DELLP4\Andrew'.

     

    I have tried every combination I can think of for the connection string and always the same thing, but I know the database is working because I can do anything else with the data through data binding, just not creating a seperate connection to it.

     

    If anyone can assist, I would GREATLY appreciate it.

     

    Thanks!

     

    Andrew

    Sunday, January 13, 2008 5:55 AM

Answers

All replies

  • Hi Andrew

    Can you please crosscheck if your connection string is correct ?? i think there is a problem with connection strnig.

    Thanks n Regards
    Anu Viswan
    Sunday, January 13, 2008 6:38 AM
  • I am assuming that it is the connection string, but I can't seem to find any examples of a connection string that will work.  I either get an error telling me it can't log in, or that the database is already attached or exists in the project.

    Sunday, January 13, 2008 5:46 PM
  • I should mention that I have even tried using the connection string builder to make sure I wasn't doing something wrong...

     

    SqlConnectionStringBuilder tmpBuilder = new SqlConnectionStringBuilder();

    tmpBuilder.ConnectTimeout = 30;

    tmpBuilder.DataSource = @".\SQLEXPRESS";

    tmpBuilder.InitialCatalog = "mydb";

    tmpBuilder.IntegratedSecurity = true;

    tmpBuilder.UserInstance = true;

    thisConnection.ConnectionString = tmpBuilder.ConnectionString;

    thisConnection.Open();

     

    Cannot open database "mydb" requested by the login. The login failed.
    Login failed for user 'DELLP4\Andrew'.

     

    Here is my Visual Studio 2008 generated Connection string that works fine for the binding sources.

     

    <add name="WindowsFormsApplication1.Properties.Settings.mydbConnectionString"

    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mydb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    providerName="System.Data.SqlClient" />

     

    Thanks guys and gals, really appreciate the help!

    Sunday, January 13, 2008 6:32 PM
  • Try this thread for possible fixes for this problem.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2658277&SiteID=1

     

    Monday, January 14, 2008 2:06 PM
  • Hello,

     

    Why have you :

    tmpBuilder.UserInstance = true;

     

    If you are using UserInstance, you cant'open a database which is open by another user

     

    See the link:

    http://msdn2.microsoft.com/fr-fr/library/ms254504(en-us,VS.80).aspx

    and also

    http://msdn2.microsoft.com/en-us/library/ms143684.aspx

     

    Test your prog without that line, i think you will have no problem

     

    Have a nice day

    Monday, January 14, 2008 3:44 PM