c# winform application sql connection not working

Question

0.00/5 (No votes)

See more:

Please help, the below code is from my btnSave click event. Honestly feel it is something simple I am missing due to lack of sleep.

I did say of sleep :P Ok this is my btnclick event, there are no crash events or errors, just the test data not filling the sql table.

regards

D

SqlConnection conn = new SqlConnection();

            conn.ConnectionString = @"Data Source=localhost;" + "Initial Catalog=iPhotographer;Integrated Security=SSPI";

            string sqlInsert = @"INSERT INTO clients(Client, Contact, Address, Telephone, NumberOfJobs, LastJob)VALUES('" + this.Client + "','" + this.Contact +"','" + this.Address +"','" + this.Telephone +"','" + this.NumberOfJobs +"','" + this.LastJob + "')";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlInsert);
                cmd.ExecuteNonQuery();

            }
            catch (Exception error)
            {
                Console.WriteLine("Error: " + error);
            }
            finally
            {
                conn.Close();

            }
            MessageBox.Show("A new client has successfully been added to the client database.");

            pnlClients.Visible = false;

Kind regards

D

Posted 20-Nov-11 1:53am

TeacherDean

Updated 20-Nov-11 15:28pm

v2

Add a Solution

Comments

Mehdi Gholam 20-Nov-11 7:56am

What is the error you are getting?

TeacherDean 20-Nov-11 21:36pm

There are no errors/crashing everything runs fine, but the test data does not insert into the sql table.

Sergey Alexandrovich Kryukov 20-Nov-11 16:38pm

Not a question.
--SA

TeacherDean 20-Nov-11 21:36pm

There are no errors/crashing everything runs fine, but the test data does not insert into the sql table.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2011-11-20T02:05:00

Without knowing the error message it is difficult to even guess - it may be as simple as you have a field name wrong.
But please, please, please do not concatenate strings to form an SQL command! It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead:

C#

string sqlInsert = @"INSERT INTO clients(Client, Contact, Address, Telephone, NumberOfJobs, LastJob)VALUES(@CL, @CN, @AD, @TN, @JC, @LJ)";

try
{
    conn.Open();
    SqlCommand cmd = new SqlCommand(sqlInsert);
    cmd.Parameters.AddWithValues("@CL", this.Client);
    cmd.Parameters.AddWithValues("@CN", this.Contact);
    cmd.Parameters.AddWithValues("@AD", this.Address);
    cmd.Parameters.AddWithValues("@TN", this.Telephone);
    cmd.Parameters.AddWithValues("@JC", this.NumberOfJobs);
    cmd.Parameters.AddWithValues("@LJ", this.LastJob);
    cmd.ExecuteNonQuery();

And it is a lot easier to read!

RaisKazi · Answer 2 · 2011-11-20T02:07:00

Solution 2

Check you are able to connect to your SQL Server(Editor) Instance - "localhost", by Windows-Authentication Mode.

You may refer below links for more details on different types of connection strings for SQL-Server.

http://connectionstrings.com/sql-server

http://connectionstrings.com/sql-server-2005

http://connectionstrings.com/sql-server-2008

Posted 20-Nov-11 2:07am

RaisKazi

Updated 20-Nov-11 2:08am

v2

Comments

TeacherDean 20-Nov-11 21:35pm

There are no errors/crashing everything runs fine, but the test data does not insert into the sql table.

RaisKazi 20-Nov-11 22:56pm

1) Check you are refferring to correct database.
2) Also try by adding below statement.
cmd.CommandType = CommandType.Text;

TeacherDean 21-Nov-11 1:49am

tried all of the above, and nothing. I have also tried changing the datasource name, from localhost to ./SQLEXPRESS to mypcname/SQLEXPRESS... *sigh*

RaisKazi 21-Nov-11 5:12am

Try by generating your connection string using "Data Source Configuration Wizard".
http://msdn.microsoft.com/en-us/library/wxt2cwcc%28v=vs.80%29.aspx
http://msdn.microsoft.com/en-us/library/w4dd7z6t%28v=vs.80%29.aspx

Kschuler · Answer 3 · 2011-11-21T11:12:00

You setup all of the values to be strings. Is this correct?

Of hand, your code doesn't look incorrect, but the problem could be your data. What IS your data? If for example this.Contact contains a single quote...then you've messed up your SQL statement because it will think the string ends there. You can fix this by taking OriginalGriff's suggestion of using parameters, which is just a good idea anyway.

When you debug, are you able to confirm that the data you're anticipating is properly set in the variables like this.Contact, etc.?

thatraja · Answer 4 · 2011-11-20T02:20:00

Solution 3

You didn't provide important details, anyway check this answer which may be similar to your issue.
Facing problem when connect sql 2008 and VS2010[^]

Posted 20-Nov-11 2:20am

thatraja

Comments

TeacherDean 20-Nov-11 21:35pm

There are no errors/crashing everything runs fine, but the test data does not insert into the sql table.

c# winform application sql connection not working

4 solutions

Solution 1

Solution 2

Solution 5

Solution 3

Add your solution here

Preview 0