C# sql INSERT INTO Throwing Exception System.Data.SqlClient.SqlException

Question

Thank you in advance for any help that you may have to offer. I am currently building a program with Visual Studio 2015 and am trying to write a method that will perform an INSERT INTO a table that I have already established.

When I run my code I get

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll"

exception message in my console. I am very new to SQL so any basic principles and advice would be greatly appreciated!

private void InsertIntoAccount(int number, string type, string date)
{
    using (SqlConnection connection1 = new SqlConnection(connectionString))
    {
        //Establish a connection
        connection1.Open();
        string sqlstring = "INSERT INTO Account (AccountNumber, Type, DateOpened) VALUES (@number, @type, @date)";

        SqlCommand cmd = new SqlCommand(sqlstring, connection1);

        //Fill in parameters
        cmd.Parameters.Add("@number", SqlDbType.Int).Value = number;
        cmd.Parameters.Add("@type", SqlDbType.VarChar, 100).Value = type;
        cmd.Parameters.Add("@date", SqlDbType.VarChar, 100).Value = date;

        //Execute
        //<REMOVED> cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
        connection1.Close();
    }   
}

What are the SQLdata types of the columns AccountNumber, Type and DateOpened — Luc Morin
– Luc Morin, Commented Dec 2, 2015 at 3:09
Im sorry, but can you be more specific? during runtime that is the only exception that occures. On a side note, (a lingering area of concern) i think my connection string is proper because I have a working username/password form in visual studio that will open and close a test connection EDIT: account number is a int and the other two are strings — David Bell
– David Bell, Commented Dec 2, 2015 at 3:09
@DavidBell when debugging with Visual Studio, you can always inspect the Exception and look at the inner exception, where most of the time the real information is provided — Luc Morin
– Luc Morin, Commented Dec 2, 2015 at 3:12

Bug Maker · Accepted Answer · 2015-12-02 03:09:52Z

1

Try

            cmd.CommandTimeout = 300;
            cmd.CommandType = CommandType.Text;

Also with your DateOpened field, is that of type varchar or is that of type date/datetime

if it is of type date/datetime try to convert your string to a date object and then change the line to

cmd.Parameters.Add("@date", SqlDbType.Date, 100).Value = date;

answered Dec 2, 2015 at 3:09

Bug Maker

5157 silver badges26 bronze badges

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

8 Comments

Luc Morin Over a year ago

Questions should be asked as comments, not as part of your "answer"

Bug Maker Over a year ago

yeah unfortunately to do that i would need 2 more reputation points, so as an attempt to answer the question, i have also tried to ask questions that would allow OP to go back and look at the data structure

David Bell Over a year ago

Im quite new to visual studio so I appreciate you all bearing with me here. I did notice that in the 'events' section it gives more information on the error. It writes "Invalid Object name "Account" inside the exception parameters. However upon inspecting my diagram, from which visual studio generated the SQL to make the tables, that the name is in fact Account. thanks again

Hogan Over a year ago

The name might be Account, but it could be created in a schema. Did you check the fully qualified name?

Luc Morin Over a year ago

Like Hogans said, check which schema the Account table was created under. By default it's dbo, which depending on your access settings to the database, you might have to access as dbo.Account instead of just Account

|

Byren Higgin · Accepted Answer · 2015-12-02 03:23:12Z

0

Make sure your database data types are correct. You have a date field, yet you are assigning a value with the data type VARCHAR. Try change your 3rd parameter declaration to something like this:

cmd.Parameters.Add("@date", SqlDbType.Date).Value = date;

answered Dec 2, 2015 at 3:23

Byren Higgin

5624 silver badges13 bronze badges

Comments

marc_s · Accepted Answer · 2015-12-02 06:00:28Z

0

It's highly likely that your connection string value is incorrect. The error means there is no table Account in DB that your are specifying using connection string.

In you example, your passing a string value as connection string, assuming you have defined it somewhere in code.

Connection string :

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;

Password=myPassword;

Example :

connectionString="Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=test; Integrated Security=True;"

Crosscheck this variable. The initial catalog should be the database name.

A better approach could be to read the connection string from web.config.

SqlConnection connection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString)

edited Dec 2, 2015 at 6:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 2, 2015 at 4:23

Avinesh Adhikthikar

265 bronze badges

Collectives™ on Stack Overflow

C# sql INSERT INTO Throwing Exception System.Data.SqlClient.SqlException

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related