c# program inserting rows in sql database

Question

I know I have done this a few times before but for the life of me can't remember how. I have a database I have created and I want to make a software that only inputs information into the database. The program works but my sql connection is the problem. So to test it out I basically tried to do it direct inserting hard-coded info but it still will not go. where am I going wrong?:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Data.Sql;

    namespace InventoryTracker
    {
        public partial class Form1 : Form
        {
          public Form1()
            {
                InitializeComponent();
            }


            public static void CreateCommand()
            {
                SqlConnection myConnection = new SqlConnection("User Id=Jab" + "password=''" + "Data Source=localhost;" + "Trusted_Connection=yes;" + "database=InventoryTracker;" + "Table=Inventory;");

                try
                {
                    myConnection.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }
                SqlCommand myCommand = new SqlCommand("INSERT INTO Inventory (ItemName, SerialNumber, Model, Department, Quantity, Notes) " + "Values (string,string,string,string, 1, string)", myConnection);

            }
    }
}

Thank you in advance! :-)

You don't need to use + in your connection string. And, nobody can know right connection string except you. — Soner Gönül
– Soner Gönül, Commented Aug 30, 2013 at 19:07
I don't think you need username and password in the connection string if you say it's a trusted connection... trusted connection is telling SQL to use windows authentication. Username and password tells it to use SQL authentication. — Paul D'Ambra
– Paul D'Ambra, Commented Aug 30, 2013 at 19:13

Community · Accepted Answer · 2017-05-23 10:25:17Z

3

Your sql connection string is messed up, you need semi-colons between all parameters and your parameters are messed up too. I.e., something like

"Server=localhost;Database=InventoryTracker;Trusted_Connection=True;"

You are mixing trusted mode and specifying the user id -- trusted connection means to use your windows login credentials.

TableName does not go in the connection string.

This site is great for connection string examples http://www.connectionstrings.com/sql-server-2008

You SQL command, "INSERT INTO Inventory (ItemName ..." is pretty messed up too. Should be something like

INSERT INTO Inventory (ItemName ...) values(@ItemName ...)

You then pass in the values like

myCommand.Parameters.Add("ItemName", SqlType.VarChar).Value = "Dozen Eggs";

See Insert data into SQL Server from C# code for a simple example

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Aug 30, 2013 at 19:16

Gary Walker

9,2043 gold badges22 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jay_the_noob Over a year ago

Thank you all. All replies helped

DanteTheEgregore · Accepted Answer · 2013-08-30 19:42:26Z

3

Just use the SqlConnectionStringBuilder class.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx

Instead of:

"User Id=Jab" + "password=''" + "Data Source=localhost;" + "Trusted_Connection=yes;" + "database=InventoryTracker;" + "Table=Inventory;");

Try:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.UserID = "Jab";
builder.Password = "";
builder.DataSource = "localhost";
builder.InitialCatalog = "InventoryTracker";

edited Aug 30, 2013 at 19:42

answered Aug 30, 2013 at 19:35

DanteTheEgregore

1,5505 gold badges23 silver badges44 bronze badges

1 Comment

Gary Walker Over a year ago

I've never used SqlConnectionStringBuilder. My connection strings have always been in a configuration file so that it is not compiled into the app. But a potentially useful alternative I've never notice before. +1

user2737615 · Accepted Answer · 2013-09-01 16:30:11Z

3

// Don't put table name in your connection string
    string connection_str = "Data Source = localhost ; uid = db_user; pwd = db_pass; database = db_name; ";
     conn = new SqlConnection(connection_str);
     conn.Open();

answered Sep 1, 2013 at 16:30

user2737615

Comments

user1557232 · Accepted Answer · 2013-08-30 19:25:29Z

1

Try changing

"User Id=Jab" + "password=''" + "Data Source=localhost;" + "Trusted_Connection=yes;" + "database=InventoryTracker;" + "Table=Inventory;"

to

"User Id=Jab; " + "Password=''; " + "Data Source=localhost; " + "Trusted_Connection=yes; " + "Initial Catalog=InventoryTracker;"

(Changed upper/lower case, "Database" to "Initial Catalog", removed "Table" and added ";")

Also, you might want to try replacing "Data Source" by "Server".

edited Aug 30, 2013 at 19:25

answered Aug 30, 2013 at 19:18

user1557232

1071 gold badge2 silver badges8 bronze badges

1 Comment

Gary Walker Over a year ago

Data Source and Server are synonyms. Database and Initial Catalog are synonyms. Connection string is not case sensitive though password, etc. in the string are case sensitive.

Nicholas Post · Accepted Answer · 2013-08-30 19:47:31Z

0

Don't forget to call myCommand.ExecuteNonQuery to get it to actually execute your query. Without this, you are just creating a command, but not running it.

edited Aug 30, 2013 at 19:47

answered Aug 30, 2013 at 19:22

Nicholas Post

1,8573 gold badges18 silver badges31 bronze badges

Collectives™ on Stack Overflow

c# program inserting rows in sql database

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related