none
sql server creating database and tables RRS feed

  • Question

  • After figuring out the issue with the connection string. I been able to get my code to create the database. How ever I can't seem to get it to create the tables. I updated the code to include some table info I left out. I posted the new code below.

    From what I can see the error is on line 130. That is where this runs " command2.ExecuteNonQuery();"

    public bool makeSQLdb()
            {
    
                bool result = false;
    
                //conn.ConnectionString = "Data Source=(local)\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";
    
                try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                    {
                        conn.Open();
    
                        string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper'";
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            int nRet = sqlCmd.ExecuteNonQuery();
    
                            if (nRet <= 0)
                            {
    
    
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
                            }
    
                        }
    
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //int nRet = sqlCmd.ExecuteNonQuery();
    
                            conn.ChangeDatabase("mlhelper");
    
                            //Create the tables last.
                            using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY not null AUTO_INCREMENT,basename char(50),category char(10);", conn))
                            { command2.ExecuteNonQuery(); }
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY not null AUTO_INCREMENT,mapname char(50),description char(500),filename (13),filedate(8),time timestamp(8),baseid int,category char(50);", conn))
                            { command3.ExecuteNonQuery(); }
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY not null AUTO_INCREMENT,cname char(50);", conn))
                            { command4.ExecuteNonQuery(); }
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(id int PRIMARY KEY not null AUTO_INCREMENT,cname) VALUES(1 ,'jointops');", conn))
                            { command5.ExecuteNonQuery(); }
    
                            result = true;
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Creating Error :" + ex.Message + "" + ex.StackTrace.ToString(), Application.ProductName);
                    this.Close();
                }
                return result;
            }
    

    here's the error I'm getting in a screen shot.

    anyone have any idea what I'm doing wrong.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Monday, March 4, 2019 1:47 PM

Answers

  • Hi Joesoft11a,

    Thank you for posting here.

    For your question, you want to solve the error that you got in a screen shot.

    You could try the following change, the bold code is modified.

    Change

    using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY not null AUTO_INCREMENT,basename char(50),category char(10);", conn))
                            { command2.ExecuteNonQuery(); }
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY not null AUTO_INCREMENT,mapname char(50),description char(500),filename (13),filedate(8),time timestamp(8),baseid int,category char(50);", conn))
                            { command3.ExecuteNonQuery(); }
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY not null AUTO_INCREMENT,cname char(50);", conn))
                            { command4.ExecuteNonQuery(); }
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(id int PRIMARY KEY not null AUTO_INCREMENT,cname) VALUES(1 ,'jointops');", conn))
                            { command5.ExecuteNonQuery(); }
    

    Into

    using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) PRIMARY KEY not null,basename char(50),category char(10))", conn))
                            { command2.ExecuteNonQuery(); }
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) PRIMARY KEY not null,mapname char(50),description1 char(500),filename char(13),filedate char(8),datedtime timestamp,baseid int,category char(50))", conn))
                            { command3.ExecuteNonQuery(); }
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int IDENTITY(1, 1) PRIMARY KEY not null,cname char(50))", conn))
                            { command4.ExecuteNonQuery(); }
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO  category(cname) VALUES('jointops')", conn))
                            { command5.ExecuteNonQuery(); }
    

    Result:

    Hope my solution could be helpful.

    Best regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Joesoft11a Tuesday, March 5, 2019 9:34 PM
    Tuesday, March 5, 2019 8:53 AM
    Moderator

All replies

  • Try the following

    CREATE TABLE dbo.bases (Id INT IDENTITY(1, 1) NOT NULL, baseName CHAR(25) NULL,category CHAR(50) NULL, PRIMARY KEY CLUSTERED(Id ASC)  WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 4, 2019 2:44 PM
    Moderator
  • Do you have something simple for me to under stand. I never used that before. The
     AUTO_INCREMENT
    is missing, and I just don't under stand what your doing.

    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Monday, March 4, 2019 2:55 PM
  • The error says you have a syntax error near AUTO_INCREMENT. So that means your SQL command is wrong. To create an auto increment table use the IDENTITY declaration.

    -- For SQL 2016+ this should work
    CREATE TABLE bases(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    basename char(50),category char(10))

    Personally I recommend that you test such commands in SSMS or Azure Data Studio first and then move them into your code. It is easier that way. In your case it looks like the last command is what is doing it. You're using an INSERT but you still have the DDL stuff in there.

    -- Don't insert a value into Id because it is an identity column
    INSERT INTO TABLE category(cname) VALUES('jointops')

    I notice you're trying to build a database via ADO.NET. That isn't what ADO.NET was really set up for. It is a DML library more than a DDL library. If you want to build arbitrary databases then you should look into SMO instead. If this is just to set up a database for your app then it is generally easier to build the database using SSMS and then export the database to a file and then ship it as part of the app. During installation the database can be restored. This allows you to more easily adjust the database in the future. Alternatively if you're using an ORM like Entity Framework then it already has the infrastructure in place to create a database and tables as part of the database migration tools.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, March 4, 2019 3:04 PM
    Moderator
  • Michael, Thank you. See I have not done any work on sql server for about 9 years. I have the full version of sql server 2005. It's old. There has been a lot of changes make to sql server that I didn't know. I didn't think to research it. I spend so much time on google my eyes are going to pop out of my head. anyway. I got the code to create the 1st 2 tables. I can't get it to create the 3rd table or do an insert.

    I updated the code for the one table called maps. Here's the code I have.

    using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,mapname char(50),description char(500),filename char(13),filedate char(13),datedtime timestamp(8),baseid int,category char(50));", conn))
                            { command3.ExecuteNonQuery(); }
    

    I'm getting the same error as above. so I know there's something wrong with the CREATE TABLE. Also here's the insert code.

    using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(cname) VALUES('jointops'));", conn))
                            { command5.ExecuteNonQuery(); }

    And there's an error in this line all so. I just don't know where. It all looks OK. any ideas.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Monday, March 4, 2019 3:48 PM
  • You need to remove size from the datedtime field

    CREATE TABLE maps
    (id          INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED ,
     mapname     CHAR(50),
     description CHAR(500),
     filename    CHAR(13),
     filedate    CHAR(13),
     datedtime   TIMESTAMP,
     baseid      INT,
     category    CHAR(50)
    );


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 4, 2019 6:17 PM
    Moderator
  • OK, cool. see I would have never seen the error in the code. Last thing. any idea why the insert doesn't insert. ? I'm using Micheal's code and that doesn't seem to want to insert the data.

    using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(cname) VALUES('jointops')", conn))
                            { command5.ExecuteNonQuery(); }

    This is my code that I have. I don't see any thing wrong all so. I don't know if I'm over looking something. or ?

    • Edited by Joesoft11a Monday, March 4, 2019 10:51 PM
    Monday, March 4, 2019 10:43 PM
  • The issues is TABLE

    INSERT INTO category (cname) VALUES('jointops')

    See the following for syntax

    https://www.w3schools.com/sql/sql_insert.asp


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 5, 2019 12:51 AM
    Moderator
  • Hi Joesoft11a,

    Thank you for posting here.

    For your question, you want to solve the error that you got in a screen shot.

    You could try the following change, the bold code is modified.

    Change

    using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY not null AUTO_INCREMENT,basename char(50),category char(10);", conn))
                            { command2.ExecuteNonQuery(); }
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY not null AUTO_INCREMENT,mapname char(50),description char(500),filename (13),filedate(8),time timestamp(8),baseid int,category char(50);", conn))
                            { command3.ExecuteNonQuery(); }
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY not null AUTO_INCREMENT,cname char(50);", conn))
                            { command4.ExecuteNonQuery(); }
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(id int PRIMARY KEY not null AUTO_INCREMENT,cname) VALUES(1 ,'jointops');", conn))
                            { command5.ExecuteNonQuery(); }
    

    Into

    using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) PRIMARY KEY not null,basename char(50),category char(10))", conn))
                            { command2.ExecuteNonQuery(); }
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) PRIMARY KEY not null,mapname char(50),description1 char(500),filename char(13),filedate char(8),datedtime timestamp,baseid int,category char(50))", conn))
                            { command3.ExecuteNonQuery(); }
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int IDENTITY(1, 1) PRIMARY KEY not null,cname char(50))", conn))
                            { command4.ExecuteNonQuery(); }
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO  category(cname) VALUES('jointops')", conn))
                            { command5.ExecuteNonQuery(); }
    

    Result:

    Hope my solution could be helpful.

    Best regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Joesoft11a Tuesday, March 5, 2019 9:34 PM
    Tuesday, March 5, 2019 8:53 AM
    Moderator
  • I thought this was working. It's creating the database and tables just fine. The problem is it wants to create the database and tables every time the app loads, So I guess that one part doesn't seem to be working. here's my code below and I hope someone is still watching this forum.

    private void MapBase_Load(object sender, EventArgs e)
            {
                //Loads all the bms Files from the game directory
                //ChangedIt = false;
                btn_MapLocation.Enabled = false;
                catLabel.Text = catName;
                
                // this is for server based, not SQLEXPRESS,
                // try this for EXPRESS -> MSSQL$SQLEXPRESS
                var serviceName = "MSSQL$SQLEXPRESS";
                ServiceController controller = new ServiceController(serviceName);
    
                try
                {
    
                    if (controller.Status == ServiceControllerStatus.Running)
                    {
                        // Check to see if database exists
                        if (makeSQLdb())
                        {
                            MessageBox.Show("Database and tables created.", Application.ProductName);
                        }
                        else
                            this.Close();
                    }
                } 
                catch (Exception exc)
                {
                    // not found
                    MessageBox.Show(exc.Message + " Error SQL Server must be ruuning.", Application.ProductName);
                    this.Close();  return;              
                }
    
                loadDefaults();            
            }
    
            public bool makeSQLdb()
            {
    
                bool result = false;
    
                try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                    {
                        conn.Open();
    
                        string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper'";
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            int nRet = sqlCmd.ExecuteNonQuery();
    
                            if (nRet <= 0)
                            {
                                
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
    
                                conn.ChangeDatabase("mlhelper");
    
                                using (SqlCommand comm1 = new SqlCommand("CREATE TABLE games(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,gamename char(50), category char(10))", conn))
                                { comm1.ExecuteNonQuery(); }
    
                                using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,basename char(50),category char(10));", conn))
                                { command2.ExecuteNonQuery(); }
    
                                using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,mapname char(50),description char(500),filename char(13),filedate char(13),datedtime timestamp,baseid int,category char(50));", conn))
                                { command3.ExecuteNonQuery(); }
    
                                using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,cname char(50));", conn))
                                { command4.ExecuteNonQuery(); }
    
                                using (SqlCommand command5 = new SqlCommand("INSERT INTO category(cname) VALUES('jointops')", conn))
                                { command5.ExecuteNonQuery(); }
                                conn.Close();
                                result = true;
    
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Creating Error :" + ex.Message + "" + ex.StackTrace.ToString(), Application.ProductName);
                    this.Close();
                }
                            
                return result;
            }
    

    It all works. Just that after the database and tables are created. The 'nRet' doesn't seem to work. So I'm confused. I wish I had a way to test this earlier I was in the middle of a config files.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Saturday, March 9, 2019 3:13 AM
  • Can anyone help. My updating my app is stopped until this is fix.

    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Saturday, March 9, 2019 9:53 PM
  • Well, the .dbo. should not be causing a problem, but I usually don't use it at all, just to avoid confusion (I could be wrong, but we've always done it this way).

    Try changing this:

    string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper'";

    to this:

    // Note that I removed dbo, and left in both dots
    string cmddb = "SELECT * FROM master..sysdatabases where name = 'mlhelper'";




    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, March 11, 2019 5:00 AM
    Moderator