Create a table in *.mdb

[ventsislav]

Hi! Ok, I have existing *.mdb with nothing within. How can I create an empty table with a name "1A", for example? I heard about some connections... I don' t know.

[cloud255]

Hi

You really need to be more specific here. This question spans 2 technologies, C# database connects and SQL.

I suggest you consult the MSDN for the code required to connect to a DB and one of the millions of sights regarding SQL code.

The C# section of your question is fairly easy to answer, please feel free to post any code which is causing you problems and we will have a look at it.

As for the SQL, this can be really simple or rather complex depending on what exactly you want to do with the table you create. I know a fair bit about SQL and could help you with some of the code but if you want indexes, primary keys and relations I really think you should post that section of your code in the Access Forum as its not in the scope of C#.

Again, read up on the code and feel free to post any code which is unclear to you and we'll have a look at it.

To give you a short explanation:
You need to create a database connection which uses a connection string containing the authentication information. Next you need to create an SQL command and finally execute this command against your database.

You will most probably also have to look at the security settings of your database to ensure that you can indeed execute the command.

Your next step will be to consider .config files which store the connection settings and possibly a separate namespace which will control data access and encapsulate DB responses.

[ventsislav]

OK. I have an empy *.mdb. Then in the code I have:

SqlConnection con = new SqlConnection;
SqlCommand cmd = con.CreateConne ction();

Then what I do with these? I mean if I want to create table called 1A in the mdb how these help me?

[cloud255]

Hi

Well, that connection of yours wont work. You need to specify a connection string. A connection string is a string variable which contains all the information needed to connect to a database, it will specify the database name, the server, username and the password needed to connect to the database.

Your SQL command needs 2 arguments, the SQL connection and a string which contains the SQL code to execute.

You then need to open the connection and execute the command:

SqlCommand.Connection.Open();
SqlCommand.ExecuteNonQuery();

Let's start by seeing your connection string and command string, write those and put them in the code I have posted for you (remember to declare both the connection and the command using the New keyword.

Post that code and we'll take it further from there.

[ventsislav]

Ok, it 's like this. I have a form like that below, and assemble the following code.
So I understood it, but what I need to do, to make a table in the file created?

FileInfo MyFile;
SqlConnectionStringBuilder ConStrBuilder = new SqlConnectionStringBuilder();
static SqlConnection Con = new SqlConnection();
SqlCommand Cmd;
SqlDataReader DataReader;

...

private void OK_Click(object sender, EventArgs e)
    {
        string ConStr = "SELECT * FROM MyData";

        object CheckName = FileNameCombo.SelectedItem;
        object CheckTableNum = TableNumCombo.SelectedItem;
        object CheckTableChar = TableCharCombo.SelectedItem;

        if ((null != CheckName) && (null != CheckTableNum) && (null != CheckTableChar))
        {
            MyFile = new FileInfo("Data\\" + FileNameCombo.SelectedItem.ToString() + ".mdb");

            for (int i = 0; i < 23; i++) // FileName selection
                for (int j = 0; j < 8; j++) // TableNum selection
                    for (int k = 0; k < 9; k++) // TableChar selection
                    {
                        if (i == FileNameCombo.SelectedIndex)
                        {
                            MyFile.Create();

                            Close();
                        } 
                        else if (MyFile.Exists == true)
                        {
                            if(j == TableNumCombo.SelectedIndex)
                                if (k == TableCharCombo.SelectedIndex)
                                {
                                    ConStrBuilder.UserID = "My_UserID";
                                    ConStrBuilder.InitialCatalog = TableNumCombo.SelectedItem.ToString() +
                                                                   TableCharCombo.SelectedItem.ToString();
                                    ConStrBuilder.DataSource = "(local)";

                                    Con.ConnectionString = ConStrBuilder.ConnectionString;
                                    Con.Open();

                                    Cmd = new SqlCommand(ConStr, Con);

                                    DataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);

                                    DataReader.Close();
                                }
                            
                            this.Close();

                            return;
                        }
                    }
        }
        else MessageBox.Show("Missig Data!", "Creating Table", 0, MessageBoxIcon.Exclamation);
    }

[cloud255]

Hi

I looked at your code and your almost there, below I have posted a neater version of connecting to a database and executing a command to create a table in the database:

 string tableName = "MyTable"; //fill this variable with the name of the table to be created
            string colName = "ColName"; //Fill this variable with the column name
            string dataType = "CHAR"; //Fill this variable with the datatype of the column

            //This is the string containing all the information required to connect to the database
            string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydatabase.mdb;User Id=admin;Password=;";

            //This is the command to execute against the database
            string cmdString = "CREATE TABLE " + tableName + " (" + colName + " " + dataType + ")";

            OleDbConnection conn = new OleDbConnection(conString); //This is the connection to the database
            OleDbCommand cmd = new OleDbCommand(cmdString, conn); //This is the SQL command which will execute using the connection
            
            try
            {
                conn.Open(); //Open the connection to the database
                cmd.ExecuteNonQuery(); //Execute the command               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message); //Show any errors
            }
            finally
            {
                conn.Close(); //Close the connection to the database
            }

I suggest you look into using loops for creating multiple columns by concatenating to the cmdString.

You could also create a struct array which stores the column name as well as the datatype and use this in your loops to make the code a bit neater.

I used a try catch block for the opening the connection to the database and executing the command, while using a finally section to close the connection. If the connection is already closed the conn.Close() will not do anything.

On a general note, instead of looping like you did above:

for (int i = 0; i < 23; i++)

You created a control with a number of options and you loop through all the possible values supplied by this control, you can make the code more robust by going:

for (int i = 0; i < comboBox1.Items.Count; i++)

I hope this helps

[ventsislav]

No, no, the loops are only for constructing the name of the table from multiple choices in the comboboxes. I' ll get this combox1.Items.C ount. Thanks!