Problem with database - Insert command

Question

1.00/5 (1 vote)

See more:

Hi,

I am programming app for windows mobile, I am just begginer and I have problem with INSERT command. This is my source.

try
   {
       conn = new SqlCeConnection("Data Source = Super.sdf; password = ********");
       SqlCeCommand cmd = conn.CreateCommand();
       conn.Open();
       cmd = conn.CreateCommand();
       cmd.CommandText = "INSERT INTO tbl_jobs (id, company, tenant, number, address1, address2, postcode, town, van) VALUES ('"+textBox1.Text+"','"+comboBox1.Text+"','"+textBox13.Text+"','"+textBox12.Text+"','"+textBox11.Text+"','"+textBox10.Text+"','"+textBox9.Text+"','"+textBox8.Text+"','"+textBox7.Text+"')";
       cmd.ExecuteNonQuery();
       conn.Close();
   }
   catch (SqlCeException ex)
   {
   }

my question is, what is wrong with line where is INSERT command? Now is working properly, but when I want to add another item, for example jobtype and ,combobox2.text INSERT command doesn't work. How many characters can be in command INSERT? I am sorry, my english is not very good :)

thank you for your help

Patrik

Posted 5-Jan-11 10:11am

stealth_13

Updated 5-Jan-11 10:14am

v2

Add a Solution

Comments

Baji Jabbar 6-Jan-11 1:08am

What is the exception u are getting . Catch all type exceptions too and find what is the error

5 solutions

Solution 7

Well your query is all correct and I am not finding any error.

Also when you will include JobType and Combobox.Text there will not be any error.

But I am suspecting that JobType may be a type of numeric(From the name I can guess) and and the other side Combobox.Text(From It seems that it's a string)

Have you tried that ? is there a problem with ?

Posted 5-Jan-11 18:10pm

Hiren solanki

Comments

Manfred Rudolf Bihy 6-Jan-11 8:08am

Good call, Hiren! 5+
Same doubts I had about OP's table. Everything seems to be a string but jobtype may be not. It may not even be a column of the table. I also suggested he log something in case of an exception instead of that empty catch block.

stealth_13 6-Jan-11 13:34pm

no, there is no problem, JOBTYPE is string, is like as maintenance, void etc

Solution 6

Use

comboBox1.SelectedItem.ToString ();

instead of

comboBox1.Text

Posted 5-Jan-11 18:09pm

Rajesh Anuhya

Comments

Manfred Rudolf Bihy 6-Jan-11 8:14am

Reason for my vote of 3:
I find this advice actually counter productive. If OP wants Text of SelectedItem it is better style to use that property than to rely on ToString() to do the right thing. ToString() has differing semantics from class to class so if I have the choice I use the property that does what I want.
My 2cts!

Solution 1

Well, the problem may not be in the INSERT command. What is id? Could it be your primary key? if so, are you setting the same value the second time around?

Posted 5-Jan-11 10:15am

Yusuf

Comments

Manfred Rudolf Bihy 5-Jan-11 17:08pm

[Moved from OP's "answer"]
ID is my primary key, i started from 0001, 0002 etc, Everything working fine, I can add more items, but when I addanother item, INSERT command doesn't work I can't see any nuw row in my database, and I don't get any error

Solution 3

First put something into the catch block of your try-catch and output what kind of exception you're experiencing. If you want to also write data to another column like jobtype as you said, is this column present in your database table? If yes what type is it?
I can see that all your columns seem to be strings, as the values you are giving are all quoted. In order to help you solve your problem we would need to know how your table is defined.

Best Regards,
Manfred

Posted 5-Jan-11 11:18am

Manfred Rudolf Bihy

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)

Vipin Kumar Mallaya G · Accepted Answer · 2011-01-05T16:29:00

Dear Friend,
Your code seems ok but its not that much perfect.
The Cause Of error might be becaue of key constraint(primary key violation or any other constraints).
Tip->Instead of letting the user to enter the id, I think it would be better to generate a key by the application and after saving it to the database the id value is shown to the user

Better progamming practice->
It is alwasys advised to use sqlparam instead of using as string directly

Eg

SqlConnection con=new SqlConnection("....");
            con.Open();
            SqlCommand cmd = new SqlCommand("Insert into std values(@id,@name)", con);
            cmd.Parameters.Add("@id", SqlDbType.VarChar, 10);// second parameter specifies the data type of the value, third  is only used in case of strings which is the size/length of the field in database
            cmd.Parameters["@id"].Value = "ID01";//your value goes here
            cmd.Parameters.Add("@name", SqlDbType.VarChar, 10);
            cmd.Parameters["@name"].Value = "Name";//your value goes here
            cmd.ExecuteNonQuery()//to execute the command, this will return the number of rows affected by issuing the DML(insert/update/delete) statement
            con.Close();

Advantage of using sql parameters is that when inserting text containing ' it may cause error so it is always advised to use parameters

It would be better to use StringBuilder as it helps for creating dynamic strings and also delevers high performence compared to string.
ie
use a StringBuilder object and store the command in it then pass it to the command object's constructor as StingBuilderObject.toString()
Feel free to contact/replay
I hope your doubt is cleared now

Regards
Vipin Kumar Mallaya
Programmer

Problem with database - Insert command

5 solutions

Solution 7

Solution 4

Solution 6

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us