Problem with local database

[Klaurac]

Hey

I need to store data in a database.
I actually don't really need to get data from it.
I created a local database in VS2010.
With some tables, I added the database as a new data source.
Now I'm stuck. I don't know how to insert data to it.
I tried:
testDataSet.Customer.Rows.Add(new Object[] {"Smith", 2}); //Name, ID

But I don't see any data appearing to it.
I haven't found any logical tutorials. What is database Explorer? Does it just show the tables or can I see the data in the rows? what is the .xsd file etc.?
I am familiar with MySQL and quering it.
Is there something like this in c#?

Thanks for your time

[Momerath]

You need to call AcceptChanges on the dataset before it will update the database. You should be able to see everything in the database explorer.

[Klaurac]

It won't work. It gave something like: path error, I can't remember anymore.

CustomersTableAdapter1.Insert(NameTextBox.Text, 0);
this.CustomersTableAdapter1.Fill(ShopDataSet1.Customers);

It doesn't give any error but the data won't show up.

If you know any good tutorials about local database in c#, let me know.
I want to know what is datase, adapter etc.

[Mitja Bonca]

What are we talking here? Or DataBase or DataTable? This is no way you will insert data to dataBase.
For inserting data into database, you need on open sql connection and created sql command, which will insert data.

Didn`t you confuse it witl dataTable (or dataSet)?

[Mitja Bonca]

This is the right way of using database and insert command:

try
{
 using (SqlConnection sqlConn = new SqlConnection( connectionString ) ) 
 {
  //query string and opening the connetion
  sqlConn.Open();
 }
}
catch ( Exception ) 
{
 //catch errors
}
finally
{
 //closing connection
}
 

Example:

private static void CreateCommand(string queryString, string connectionString)
{
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
    string insertString  = "INSERT INTO MyDataTable VALUES(specify all values you want to insert here) " +
                           "WHERE if where clause needed specify it here";
    SqlCommand command = new SqlCommand(insertString, connection);
    command.Connection.Open();
    command.ExecuteNonQuery();
  }
}

Is this what you wanted?

[Klaurac]

I chose add from VS2010 my project.
Added "Local Database", I guess it's a database.
Then I chose from Data menu add new data source.
Then I dragged the DataSet created into my form from the toolbox.
And the code I posted previously is the insert code.
Does Your code still apply?

[Mitja Bonca]

Even if its a local db, it NEEDS to have a connection string. So find this string and use it (take a look in the upper my example how you use it).

And to answer on yout question, of course it applies on my code too.

PS:You will slowely learn thats better to create dataSets (or dataTables manually) - at least I like it more. Its always good to know whats really going on in the code.

[Klaurac]

I found the connection string from my project settings:
"Data Source=|DataDirectory|\MyDatabase.sdf";.
I escape the backslash with another one.

It gives me this error:
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

I do agree that it is good to know the innerworkings.
I really like the Visual Studio, the drag drop, intellisense etc.
But It has also made me too comfortable. It sometimes hinders my progress.

[Mitja Bonca]

If you use "|DataDirectory|" the database has to be embedded in the project. If its not, you have so specify the full path to the it (like : C:\myFolder\MyProject\MyDataBase.sdf)

[Klaurac]

It now doesn't seem to recognize the path now.
I use the following as connection string:
@"C:\Users\owner\Desktop\My Project\My Project\MyDataBase.sdf"

The @ symbol should escape the empty spaces and backslashes.
The database file exists, this I know.

Gives me this error:
Format of the initialization string does not conform to specification starting at index 0."

I now tried
@"Data Source= C:\Users\owner\Desktop\My Project\My Project\MyDataBase.sdf"
Now it gets past the path thing.
But still gets stuck in opening the connection.

[Mitja Bonca]

Try to write it as:

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\MyPriject\myDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Which DB do you have? if you have MsSql express, do exactly this way!! only change the path to the db!

[Klaurac]

Hmmmm. The same thing. Error when opening the connection.

I found that this is the database type.
Microsoft SQL Server Compact.
I tried using SQLCE and SQL CE, but the same thing happens.

Is there any other way of entering data into the tables?
Like I did it in the first place.

[Mitja Bonca]

If you cannot salve the issue, you can send the whole project to me. I will try to look at it.

[Klaurac]

I tried to create a new project to see if the problem remains.
It did remain. I added the project as a attachment.
The solution could be really simple or it could be some installation problem.