Huge problem with sql!

[invader7]

Hello everybody, i have a huge problem and i can't understand why. i have made a program in windows 7. it connects to database with odbc code like this many times through form_load


OdbcCommand cmd;
DataSet ds;
System.Data.Odbc.OdbcDataAdapter da;
string MyString;
using (OdbcConnection cn = new OdbcConnection())
{

cn.ConnectionString = ("dsn=mydsn;");
ds = new DataSet();
MyString = "SELECT user FROM users";
da = new System.Data.Odbc.OdbcDataAdapter(MyString, cn.ConnectionString);
cmd = new OdbcCommand(MyString, cn);
da.Fill(ds, "users");
DataRow dRow = ds.Tables["users"].Rows[0];

activeusertextBox.Text = dRow.ItemArray.GetValue(0).ToString();

cn.Close();
}


and then at the end of the many odbc connections i have this


this.myTableAdapter.Fill(this.myDataSet.charge);


in my form i have a datagird view which takes data from a datasource

in windows 7 it works fine with sql server 2005 express
in windows xp it doesn't with sql server 2005 express

i get this error while i try to debug


Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_log.ldf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".

Cannot open user default database. Login failed.
Login failed for user 'sa'.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_log.ldf" may be incorrect.


my password for sa user is correct. Any suggestions ?

[Arjay]

Since you are connecting to SQL Server, why not use the SqlConnection and SqlCommand classes in the SqlClient namespace?


using( SqlConnection cn = new SqlConnection( "connection str ") )
{

// Query the db
using( SqlCommand cmd = cn.CreateCommand() )
{
cmd.CommandText = "SELECT user FROM users";
cn.Open();

// run the query
SqlDataReader reader = cmd.ExecuteReader();

while ( reader.Read( ) )
{
activeusertextBox.Text1 = Convert.ToString( reader["col1"] );
activeusertextBox.Text2 = Convert.ToString( reader["col2"] );
}
}
}


If have to use Odbc, then you'll need to close your OdbcCommand objects (or use them in a using block).

[invader7]

i think i already done that,


using (OdbcConnection cn = new OdbcConnection())
{
cn.Close();
}


but i think cn.Close(); doesn't need to be there.


anyway


i think that my problems start when i connect first time to database, then even though i close my connections the database seems to be "locked" and this


this.myTableAdapter.Fill(this.myDataSet.charge);


doesn't work

but if i make a new form with only this


this.myTableAdapter.Fill(this.myDataSet.charge);


it works perfect!

[invader7]

ok it seems to work with sqlconnection as you posted before, but is there a way not to store the password inside my code ? is there a way to give as connection string my dsn ?



using( SqlConnection cn = new SqlConnection( "connection str ") )
{

// Query the db
using( SqlCommand cmd = cn.CreateCommand() )
{
cmd.CommandText = "SELECT user FROM users";
cn.Open();

// run the query
SqlDataReader reader = cmd.ExecuteReader();

while ( reader.Read( ) )
{
activeusertextBox.Text1 = Convert.ToString( reader["col1"] );
activeusertextBox.Text2 = Convert.ToString( reader["col2"] );
}
}
}


this is only to get values from database ?? if i want to use a insert statement ?

[Arjay]

Read my previous response carefully - I said to use a using block on the OdbcCommand objects and the OdbcConnection objects.

The problem is that you aren't freeing the resources, so eventually the db refuses you.

As far as putting the password in plain text in the config file. You can use integrated windows security or encrypt the password in the config file. There are many approaches to this. I searched google for "connection string password encryption" and came up with this one: http://ondotnet.com/pub/a/dotnet/2005/02/15/encryptingconnstring.html

To insert a record, form the INSERT statement and call cmd.ExecuteNonQuery( ). For more info and examples, search google for "SqlCommand insert example".

[invader7]

thanks, i managed to do it i'll search about password encryption