[RESOLVED] Need help with managing DB from code!

[Guitarcomet]

Hi!

First of all: I'm not 100% sure that this is the place to put my question, but I think it wouldn't fit in the ADO.Net forum.

Here's my question:

My application will need to be able to create new tables in an existing database, write data to database and read data. The problem is I have only been working with creating the tables first and then read the data. Not to create new tables from the code. And the code that I've been using has been generated by Visual Studio. I think I would be able to read data and I know how to add tables and connect. I would need to know how to add columns to a table (existing or not doesn't matters), and to fill it with data.

[eclipsed4utoo]

you didn't really ask a question.

do you want to know how to programmatically add columns to a database table? If so, this SQL statement will add a column named "Column15" with datatype of varchar and allowing nulls to table "Table1".

ALTER TABLE Table1 ADD
       Column15 varchar(25) null

[Guitarcomet]

Yeah, I want to programmatically add a column. I'm trying to use your code, but it never works for me. Well, I'm new to this so I'm not quite sure on how to give the command to the database. It's an *.mdf database!

[eclipsed4utoo]

Yeah, I want to programmatically add a column. I'm trying to use your code, but it never works for me. Well, I'm new to this so I'm not quite sure on how to give the command to the database. It's an *.mdf database!

are you using ACCESS or SQL Server?

[Guitarcomet]

I'm using SQL Server!

[eclipsed4utoo]

I'm using SQL Server!

the code is just a fairly simple sql query that runs and works fine with SQL 2005(and I would assume works in 2000 if you are using that).

Can you post your code?

[Guitarcomet]

I found a way in which I could create a table to database and create rows in it. But now I need to know if it's possible to get a list of all tables in a database and put it in a comboBox. And if it is, how it is done.

[eclipsed4utoo]

SqlConnection cn = new SqlConnection(connectionString);
cn.Open();
DataTable dt = cn.GetSchema();
cn.Close();

then you should be able to bind the datatable to the combobox's datasource property.

[Guitarcomet]

No, it doesn't work. The text in my combobox is System.Data.DataRowView. And that's not the names of tables.

[eclipsed4utoo]

No, it doesn't work. The text in my combobox is System.Data.DataRowView. And that's not the names of tables.

then you will need to loop through the datatable to get the SQL table names.

[eclipsed4utoo]

here is an updated version of the code above....

SqlConnection cn = new SqlConnection(Settings.GetInstance().ConnectionString.ConnectionString);
string[] restrictions = new string[4];
// name of your database
restrictions[0] = "YourDataBaseName";
// owner
restrictions[1] = "dbo";
// null because we want to return all tables
restrictions[2] = null;
// just return tables and not views
restrictions[3] = "BASE TABLE";
cn.Open();
DataTable dt = cn.GetSchema("Tables", restrictions);
cn.Close();

foreach (DataRow row in dt.Rows)
{
    comboBox1.Items.Add(row.ItemArray[2]);
}

[Guitarcomet]

Sorry, eclipsed4utoo, but neither that way worked. I had to solve this problem by creating another table holding data about what tables exists. I copied your code and edited it so that it should work for me, but no. Thanks for your help anyaway!

[eclipsed4utoo]

Sorry, eclipsed4utoo, but neither that way worked. I had to solve this problem by creating another table holding data about what tables exists. I copied your code and edited it so that it should work for me, but no. Thanks for your help anyaway!

either you are using .Net 1.1(which you should state), or you did something wrong. I copy and pasted that code directly from Visual Studio after I tested it. It works fine for me.

[Guitarcomet]

No, I'm using .Net 3.5. Nothing is added to my comboBox.

[eclipsed4utoo]

No, I'm using .Net 3.5. Nothing is added to my comboBox.

have you tried breakpointing to see what values are being added? is there a different way of adding values to a combobox in .Net 3.5?