how to insert values to a table in the database

Question

2.50/5 (2 votes)

See more:

the following code doesn't cause for any error.but yet the values are not inserted.the message "Successfully Inserted" is also not running what is wrong with the code?can any one help me?

byte[] imagedata;
private int n = 0;
private string retVal;

private SqlConnection conn;
private SqlCommand com;
private SqlCommand SqlCom;
private SqlDataReader dr;

private void button3_Click(object sender, EventArgs e)
{
 if (textBox2.Text == "" || textBox3.Text == "")
   MessageBox.Show("Please make sure to fill the compulsary fields!!");
 else
  {
   textBox1.Visible = true;
   int no = generateCandNo();
   textBox1.Text = Convert.ToString(no);
    try
    {
    openConnection();
    string qry = "insert into candidates (cand_no, cand_name, party(name), party_im, name_im) values (@no, @nm,@pty,@ImageData1,@ImageData2)";

    SqlCom = new SqlCommand(qry, conn);
    SqlCom.Parameters.Add(new SqlParameter("@no",textBox1.Text));
    SqlCom.Parameters.Add(new SqlParameter("@nm", textBox2.Text));
    SqlCom.Parameters.Add(new SqlParameter("@pty", textBox3.Text));
    SqlCom.Parameters.Add(new SqlParameter("@ImageData1",(object)imagedata));
    SqlCom.Parameters.Add(new SqlParameter("@ImageData2", (object)imagedata));
    n = -1;
    n = SqlCom.ExecuteNonQuery();
    closeConnection();
    MessageBox.Show("Successfully Inserted");
    }
    catch (Exception ee)
    {string error = ee.Message;}
  }
}

private int generateCandNo()
{
 string query = "select cand_no from candidates where cand_no=(SELECT max(cand_no)FROM candidates);";
 openConnection();
 com = new SqlCommand(query, conn);
 dr = com.ExecuteReader();
 while (dr.Read())
 {  retVal = dr[0].ToString(); }
 com.Cancel();
 com.Dispose();
 closeConnection();
 int cand_no= Convert.ToInt32(retVal);
 textBox1.Text = retVal;
 cand_no++;
 return cand_no;
}

table definition

cand_no       	nvarchar(50)
cand_name       nvarchar(50)
party(name)	varchar(50)
party_im	image
name_im	        image

[edit]Code blocks added, "Ignore HTML..." option disabled - OriginalGriff[/edit]

Posted 16-Jun-11 23:54pm

Member 7779792

Updated 17-Jun-11 0:08am

OriginalGriff

v2

Add a Solution

Comments

Drazen Pupovac 17-Jun-11 5:58am

Have you checked the connection?

Member 7779792 17-Jun-11 8:04am

connection is seems to be all right.
exception says about a syntax error.is there any possible way to locate the error?

Member 7779792 17-Jun-11 9:04am

thank you every one!!!:)))

4 solutions

Solution 1

I would hazard a guess that it is the "party(name)" part of the SQL command. To check, try doing something useful with the exception:

catch (Exception ee)
{string error = ee.Message;}

That just throws it away and stops the compiler complaining - which was probably what you intended! However, it means that you can't find out what is going wrong: Try a MessageBox.Show instead, or log the error to a file.

"error is in,
n = SqlCom.ExecuteNonQuery();
what is wrong in this?"

And I am guessing that the error is "'party' is not a recognized built-in function name."?

If your column is called "party(name)" then it need to be enclosed to work. If it isn't, then you need to use the name of the column!

string qry = "insert into candidates (cand_no, cand_name, [party(name)], party_im, name_im) values (@no, @nm,@pty,@ImageData1,@ImageData2)";

Or:

string qry = "insert into candidates (cand_no, cand_name, party_name, party_im, name_im) values (@no, @nm,@pty,@ImageData1,@ImageData2)";

Posted 17-Jun-11 0:11am

OriginalGriff

Updated 17-Jun-11 3:01am

v2

Comments

Member 7779792 17-Jun-11 8:54am

error is in,
n = SqlCom.ExecuteNonQuery();
what is wrong in this?

OriginalGriff 17-Jun-11 9:01am

Answer updated

Solution 2

lets say your database connection is A-OK and established, did the values assigned to your objects are in? have you trace it?
Im referring to this :

MIDL

SqlCom.Parameters.Add(new SqlParameter("@no",textBox1.Text));
SqlCom.Parameters.Add(new SqlParameter("@nm", textBox2.Text));
SqlCom.Parameters.Add(new SqlParameter("@pty", textBox3.Text));
SqlCom.Parameters.Add(new SqlParameter("@ImageData1",(object)imagedata));
SqlCom.Parameters.Add(new SqlParameter("@ImageData2", (object)imagedata));

Posted 17-Jun-11 0:12am

Streaker

Solution 4

try this:

C#

catch (Exception ee)
    MessageBox.Show(ee.ToString());

to check the exception

Posted 17-Jun-11 0:44am

Uday P.Singh

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)

Richard Green · Accepted Answer · 2011-06-17T00:31:00

You might want to set the types on the SqlParameters ie:

SqlCom.Parameters.Add(new SqlParameter("@no", System.Data.SqlDbType.NVarChar,50));
SqlCom.Parameters.Add(new SqlParameter("@nm", System.Data.SqlDbType.NVarChar,50));
SqlCom.Parameters.Add(new SqlParameter("@pty", System.Data.SqlDbType.Nvarchar,50));
SqlCom.Parameters.Add(new SqlParameter("@ImageData1",System.Data.SqlDbType.Image));
SqlCom.Parameters.Add(new SqlParameter("@ImageData2",System.Data.SqlDbType.Image));

SqlCom.Paramters["@no"].Value = textBox1.Text;
...

There are a couple of things that byte arrays can be converted to (Binary, VarBinary, Image and timestamp) so its best to set the correct types where you can.

Also as mentioned above the party(name) column might be causing issues, I'd wrap the column names in [] to make sure that is no the case.

string qry = "insert into candidates ([cand_no], [cand_name], [party(name)], [party_im], [name_im]) values (@no, @nm,@pty,@ImageData1,@ImageData2)";