Click here to Skip to main content
15,922,427 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to insert data from result data of visual studio2015 to mssql.But my code show some error.

For example:
(command.ExecuteScalar())
"sqlexception was unhandled"
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll


My code is below:
App.Config:code

XML
<pre><?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Adipocytes_Diplomamunka.Properties.Settings.LipdatConnectionString"
            connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Lipdat.mdf;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>



C#
using System.Configuration;
using System.Data.SqlClient;
SqlConnection connection;
string connectionString;

        public Form1()
        {
            InitializeComponent();
            connectionString = ConfigurationManager.ConnectionStrings["Adipocytes_Diplomamunka.Properties.Settings.LipdatConnectionString"].ConnectionString;


        }

 private void button2_Click(object sender, EventArgs e)
        {

            SQLSU();
        }    

        private void SQLSU()
        {

            string query = "INSERT INTO Result VALUES(@ID, @Filename)";
            using (connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand(query, connection))
            {

                    connection.Open();

                    command.Parameters.AddWithValue("@ID", cnt);
                    command.Parameters.AddWithValue("@Filename", FileNa);
                    command.ExecuteScalar();

            }                     
        }


What I have tried:

I want to insert data from result data of visual studio2015 to mssql.But my code show some error.
Posted
Updated 3-Mar-19 6:48am
Comments
F-ES Sitecore 3-Mar-19 10:39am    
If you're not returning data then use ExecuteNonQuery rather than ExecuteScalar. Also check cnt is a valid ID, that FileNa contains text, and ensure the Result table only has an ID and Filename field and that ID is not an IDENTITY column.

For starters, you need to use the debugger to look at the exception itself, and specifically the InnerException property which will probably give you more information on what exactly happened.

There are quite a few possibilities.

1) The code you show doesn't show any value being put into either FileNa or cnt so use the debugger to look at the variables, and find out exactly what they contain. cnt is probably an integer so it'll have a value, but FileNa is probably a string, and that will default to a null value, which SQL may not like. The only way to be sure is to use the debugger.

2) Do your columns match the values? You don't specify column names in your INSERT statement, so SQL will start with the "first" column and fill from there. If you have more than 2 columns, there's a good chance the order doesn't match. Always list the column names in an INSERT or UPDATE (and also in SELECT) to avoid this.

3) ID implies a row id - and if you have an IDENTITY column in your DB, then you can;t specify it yourself, and SQL will throw an exception if you try.

4) If ID is a row ID and it isn't IDENTITY then you are making a big mistake: your code will work in dev where there is just you using it, but will fail intermittently in production and cause some really nasty DB integrity problems. Never "pre-assign" IDs - let the DB do that and fetch them after the INSERT is successful if you need them.
 
Share this answer
 
I'm surprised it's even compiling since your "using" statements aren't conforming to any pattern I'm aware of.

Or are we to assume it's a "typo" here but the program is "fine"?
 
Share this answer
 
Comments
Richard Deeming 5-Mar-19 12:41pm    
There's nothing wrong with the using statements. The fact that the SqlConnection is declared as a field instead of a local variable leaves room for improvement, but there's nothing to prevent that code from compiling.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900