How to insert Data to Ms SQL server 2008 using C#.net

Question

5.00/5 (1 vote)

See more:

Dear all,

I am using visual studio 2010, I have a problem with insert data to database of SQL server.
Here is my code:

C#

cn.ConnectionString = @"Data Source=PHANNY-PC\PHANNY; Initial Catalog=db_stuRegisterPay; Integrated Security=SSPI";
            cn.Open();
SqlCommand com = new SqlCommand();
            com.Connection = cn;
            com.CommandType = CommandType.Text;
          
            com.CommandText = "insert into Persons_info(perID, latinName, gender, dob, pob, phone, passport, curAdd, status) values('" + txtID.Text + "','" + txtLatinName.Text + "','" + cbGender.Text + "'" + dTPdob.Text + txtPob.Text + "','" + txtPhone.Text + "','" + txtPassport.Text + "'" + txtCurAdd.Text + "'" + cbStatus.Text + " )";
com.ExecuteNonQuery();
MessageBox.Show("Saving is done!");

The error is

Incorrect syntax near 'Tuesday'. In line com.ExecuteNonQuery();

Thanks all.

Posted 5-Mar-12 16:48pm

phanny 2011

Updated 6-Mar-18 4:52am

André Kraak

v2

Add a Solution

Comments

André Kraak 6-Mar-12 14:18pm

Edited question:
Added pre tags

Member 13711852 6-Mar-18 10:56am

Same error

9 solutions

Solution 6

Use this.

cmd.parameters.Addwithvalue("@ur_parameter",Convert.toDateTime(your date value));

I hope it will remove error.

or else change datetimepicker format:- to custom
and add value in custom format Property:-dd/MM/yyyy(use month in Capital letter)

I hope it will work.
If u Got any error just write it.

Posted 6-Mar-12 3:38am

Mohd Imran Saifi

Updated 6-Mar-12 3:39am

v2

Solution 1

Hi, can you check the SQL statement below considering that all fields are string.

com.CommandText = "insert into Persons_info(perID, latinName, gender, dob, pob, phone, passport, curAdd, status) values('" + txtID.Text + "','" + txtLatinName.Text + "','" + cbGender.Text + "', '" + dTPdob.Text + "', '" + txtPob.Text + "','" + txtPhone.Text + "','" + txtPassport.Text + "', '" + txtCurAdd.Text + "', '" + cbStatus.Text + "')";

Posted 5-Mar-12 16:57pm

Charlemagne Gustilo

Comments

phanny 2011 5-Mar-12 22:59pm

no not all, like perID is int, dob is date....so how I do?

Solution 7

try the simple code:

C#

Sqlconnection con=new Sqlconnection();
con.open();
con.Connectionstring="Data Source=PHANNY-PC\PHANNY; Initial Catalog=db_stuRegisterPay; Integrated Security=SSPI";
string query="insert into Persons_info(perID, latinName, gender, dob, pob, phone, passport, curAdd, status) values('" + txtID.Text + "','" + txtLatinName.Text + "','" + cbGender.Text + "'" + dTPdob.Text + txtPob.Text + "','" + txtPhone.Text + "','" + txtPassport.Text + "'" + txtCurAdd.Text + "'" + cbStatus.Text + " )";

SqlCommand cmd=new SqlCommand(query,con);
cmd.ExecuteNonQuery();

MessageBox.show("Saving is done!");

this is the simplest coding...try this out one....

Posted 6-Mar-12 6:05am

Dinesh92

Solution 2

number of inserted values must be equal to number of fields you will insert into them,see again the code:
field are 9
but values are 5 !!
values must seperate by "," with another.
"insert into Persons_info(perID, latinName, gender, dob, pob, phone, passport, curAdd, status) values('" + txtID.Text + "','" + txtLatinName.Text + "','" + cbGender.Text + "," + dTPdob.Text+ "," + txtPob.Text + "','" + txtPhone.Text + "','" + txtPassport.Text + "," + txtCurAdd.Text + "," + cbStatus.Text + " )";
hope it help

Posted 5-Mar-12 17:05pm

FM7

Comments

phanny 2011 5-Mar-12 23:08pm

It's the same. fields are 9 and values are 9.

FM7 5-Mar-12 23:14pm

"," must be between them.if not,everyone witch have "++" between them will insert to one field! then another fiels will be empty!

Solution 5

Hi,

Here datetime field is creating problem right?

pls convert the value or format of your dTPdob.Text to date format yyyy-MM-dd and check you will successfully insert your record.

Posted 5-Mar-12 17:47pm

MaulikDusara

Comments

samimus 30-Oct-12 12:37pm

hi ,
i have one question please , i want to insert data in my database (sqlserver2008), i made two textboxs (txtnum,txtnom). so my question is how can i do if i like verified the value of txtnum befort inserting data in order to avoid duplication in my database
i search the code c#
best regards

[no name] 31-Oct-12 0:45am

Hi,
You need to create SQL Trigger . Please see below sample Trigger which is Instead of Trigger.

CREATE TRIGGER dbo.uniqueUserQuestion
ON dbo.submit_Answer
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS
(
SELECT 1
FROM dbo.submit_Answer T
INNER JOIN INSERTED I
ON T.user_name = I.user_name
AND T.question_id = I.question_id
)
BEGIN
-- Do dupe handling here
PRINT 'duplicate'
raiserror('cant submit answer to same question twice')
return
END

-- actually add it in
INSERT INTO
dbo.submit_Answer
SELECT
*
FROM
INSERTED I
END
GO

Solution 3

SQL

com.CommandText = "insert into Persons_info(perID, latinName, gender, dob, pob, phone, passport, curAdd, status) values('" + txtID.Text + "','" + txtLatinName.Text + "','" + cbGender.Text + "','" + dTPdob.Text + "','" + txtPob.Text + "','" + txtPhone.Text + "','" + txtPassport.Text + "','" + txtCurAdd.Text + "','" + cbStatus.Text + " )";
                com.ExecuteNonQuery();

Posted 5-Mar-12 17:22pm

FM7

Solution 8

CODE FOR INSERT AND UPDATE ON SINGLE BUTTON AND DELETE CODE WITH DEMO APPLICATION TO DOWNLOAD GO HERE :-
Download Application for sql insert , Update , delete using c#
try

{
SqlCommand cmd1 = new SqlCommand("select * from Agency_detail where Agency_code=" + textBox1.Text, con);
SqlDataAdapter da1 = new SqlDataAdapter();
DataTable dt1 = new DataTable();
da1.SelectCommand = cmd1;
da1.Fill(dt1);
if ((dt1.Rows.Count > 0) && (dt1.Rows[0][0].ToString() != ""))
{
if (MessageBox.Show("ID Already Exist Do you want to Update It", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
SqlCommand cmdupdate = new SqlCommand("update Agency_detail set Agency_Name=@p1,Phone=@p2,fax=@p3,Mobile_Number=@p4,DOJ=@p5,opening=@p6,Remark=@p7,Address=@p8,city=@p9,state=@p10,zip_code=@p11,email=@p12 where Agency_code=" + textBox1.Text, this.con );
cmdupdate.Parameters.AddWithValue("@p1", textBox2.Text);
cmdupdate.Parameters.AddWithValue("@p2", textBox4.Text);
cmdupdate.Parameters.AddWithValue("@p3", textBox3.Text);
cmdupdate.Parameters.AddWithValue("@p4", textBox8.Text);
cmdupdate.Parameters.AddWithValue("@p5", dateTimePicker1.Value.Date);
cmdupdate.Parameters.AddWithValue("@p6", textBox6.Text);
cmdupdate.Parameters.AddWithValue("@p7", textBox5.Text);
cmdupdate.Parameters.AddWithValue("@p8", textBox17.Text);
cmdupdate.Parameters.AddWithValue("@p9", textBox16.Text);
cmdupdate.Parameters.AddWithValue("@p10", textBox15.Text);
cmdupdate.Parameters.AddWithValue("@p11", textBox14.Text);
cmdupdate.Parameters.AddWithValue("@p12", textBox13.Text);

//con.con.Open();
cmdupdate.ExecuteNonQuery();
MessageBox.Show("Updated");
retrieve_data();
clearall();
}
else
{

}
}
else
{

if ((textBox1.Text != "") && (textBox2.Text != ""))
{
SqlCommand cmd = new SqlCommand("insert into Agency_detail values(@para1,@para2,@para3,@para4,@para5,@para6,@para7,@para8,@para9,@para10,@para11,@para12,@para13)", con);
cmd.Parameters.AddWithValue("@para1", Convert.ToInt64(textBox1.Text));
cmd.Parameters.AddWithValue("@para2", textBox2.Text);
cmd.Parameters.AddWithValue("@para3", Convert.ToInt64(textBox4.Text));
cmd.Parameters.AddWithValue("@para4", Convert.ToInt64(textBox3.Text));
cmd.Parameters.AddWithValue("@para5", Convert.ToInt64(textBox8.Text));
cmd.Parameters.AddWithValue("@para6", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@para7", Convert.ToDouble(textBox6.Text));
cmd.Parameters.AddWithValue("@para8", textBox5.Text);
cmd.Parameters.AddWithValue("@para9", textBox17.Text);
cmd.Parameters.AddWithValue("@para10", textBox16.Text);
cmd.Parameters.AddWithValue("@para11", textBox15.Text);
cmd.Parameters.AddWithValue("@para12", Convert.ToInt64(textBox14.Text));
cmd.Parameters.AddWithValue("@para13", textBox13.Text);
//con.con.Open();
cmd.ExecuteNonQuery();
retrieve_data();
clearall();
}
else { MessageBox.Show("Agency ID and Name Must Required"); }
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

Posted 7-May-14 6:31am

heemanshubhalla

Comments

CHill60 7-May-14 12:33pm

The question is over 2 years old!! And please do not shout (all capitals) - it is very rude

Solution 9

check your code you are not using proper '"+ dTPdob.Text+"'(Quotation marks) in your insert query .thats why it throw an error

Posted 7-May-14 7:12am

ashwinikumar shende

Comments

CHill60 7-May-14 14:27pm

The question is over 2 years old and already had 8 solutions. Solution 4 is the best approach - using parameterized queries means you don't have to worry about the quotation marks

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)

s_magus · Accepted Answer · 2012-03-05T17:22:00

The problem will you code is that you are missing a couple of commas in your string. The following snippet of code is where you problem exists.

C#

cbGender.Text + "'" + dTPdob.Text + txtPob.Text + "','"

By using a parametrized query you can eliminate the messy string concatenation and other problems associated with it and sql injection. It is also much easier to read and update in the future if you need to add another parameter.

C#

com.CommandText = "insert into Persons_info(perId, latinName, gender, dob, pob, phone, passport, curAdd, status) values (@perId, @latinName, @gender, @dob, @pob, @phone, @passport, @curAdd, @status)"

com.Parameters.Add(new SqlParameter("@perId", txtId.Text));
com.Parameters.Add(new SqlParameter("@latinName", txtLatinName.Text));
com.Parameters.Add(new SqlParameter("@gender", cbGender.Text));
com.Parameters.Add(new SqlParameter("@dob", dTPdob.Text));
com.Parameters.Add(new SqlParameter("@pob", txtPob.Text));
com.Parameters.Add(new SqlParameter("@phone", txtPhone.Text));
com.Parameters.Add(new SqlParameter("@passport", txtPassport.Text));
com.Parameters.Add(new SqlParameter("@curAdd", txtCurAdd.Text));
com.Parameters.Add(new SqlParameter("@status", cbStatus.Text));