Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
Dear all,
 
I am using visual studio 2010, I have a problem with insert data to database of SQL server.
Here is my code:
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
Edited 6-Mar-12 8:17am
v2
Comments
André Kraak at 6-Mar-12 14:18pm
   
Edited question:
Added pre tags
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

try the simple code:
 
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....
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
 
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.
 
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));
  Permalink  
Comments
phanny 2011 at 5-Mar-12 23:44pm
   
Thank so much. It works but got a problem more about the data type of dob is date and dTPdob is dateTimePicker. So how can I modified it?
Uma Shankar Patel at 5-Mar-12 23:59pm
   
use dTPdob.Value instead of dTPdob.Text
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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 + "')";
  Permalink  
Comments
phanny 2011 at 5-Mar-12 22:59pm
   
no not all, like perID is int, dob is date....so how I do?
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
phanny 2011 at 5-Mar-12 23:08pm
   
It's the same. fields are 9 and values are 9.
MP59 at 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!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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();
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
samimus at 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
MaulikDusara at 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
Rate this: bad
good
Please Sign up or sign in to vote.

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);
}
  Permalink  
Comments
CHill60 at 7-May-14 12:33pm
   
The question is over 2 years old!! And please do not shout (all capitals) - it is very rude
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 9

check your code you are not using proper '"+ dTPdob.Text+"'(Quotation marks) in your insert query .thats why it throw an error
  Permalink  
Comments
CHill60 at 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

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

  Print Answers RSS
0 Gihan Liyanage 332
1 Sergey Alexandrovich Kryukov 305
2 OriginalGriff 283
3 ClimerChinna 222
4 Abhinav S 187
0 Sergey Alexandrovich Kryukov 8,243
1 OriginalGriff 7,109
2 CPallini 2,598
3 Richard MacCutchan 1,980
4 Abhinav S 1,778


Advertise | Privacy | Mobile
Web04 | 2.8.140826.1 | Last Updated 7 May 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100