how to insert data if it contain apostrophe ?
- by angel ansari
Actally my task is load csv file into sql server using c# so i have split it by comma my problem is that some field's data contain apostrop and i m firing insert query to load data into sql so its give error my coding like that
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
namespace tool
{
public partial class Form1 : Form
{
StreamReader reader;
SqlConnection con;
SqlCommand cmd;
int count = 0;
//int id=0;
FileStream fs;
string file = null;
string file_path = null;
SqlCommand sql_del = null;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog file1 = new OpenFileDialog();
file1.ShowDialog();
textBox1.Text = file1.FileName.ToString();
file = Path.GetFileName(textBox1.Text);
file_path = textBox1.Text;
fs = new FileStream(file_path, FileMode.Open, FileAccess.Read);
}
private void button2_Click(object sender, EventArgs e)
{
if (file != null )
{
sql_del = new SqlCommand("Delete From credit_debit1", con);
sql_del.ExecuteNonQuery();
reader = new StreamReader(file_path);
string line_content = null;
string[] items = new string[] { };
while ((line_content = reader.ReadLine()) != null)
{
if (count >=4680)
{
items = line_content.Split(',');
string region = items[0].Trim('"');
string station = items[1].Trim('"');
string ponumber = items[2].Trim('"');
string invoicenumber = items[3].Trim('"');
string invoicetype = items[4].Trim('"');
string filern = items[5].Trim('"');
string client = items[6].Trim('"');
string origin = items[7].Trim('"');
string destination = items[8].Trim('"');
string agingdate = items[9].Trim('"');
string activitydate = items[10].Trim('"');
if ((invoicenumber == "-") || (string.IsNullOrEmpty(invoicenumber)))
{
invoicenumber = "null";
}
else
{
invoicenumber = "'" + invoicenumber + "'";
}
if ((destination == "-") || (string.IsNullOrEmpty(destination)))
{
destination = "null";
}
else
{
destination = "'" + destination + "'";
}
string vendornumber = items[11].Trim('"');
string vendorname = items[12].Trim('"');
string vendorsite = items[13].Trim('"');
string vendorref = items[14].Trim('"');
string subaccount = items[15].Trim('"');
string osdaye = items[16].Trim('"');
string osaa = items[17].Trim('"');
string osda = items[18].Trim('"');
string our = items[19].Trim('"');
string squery = "INSERT INTO credit_debit1" +
"([id],[Region],[Station],[PONumber],[InvoiceNumber],[InvoiceType],[FileRefNumber],[Client],[Origin],[Destination], " +
"[AgingDate],[ActivityDate],[VendorNumber],[VendorName],[VendorSite],[VendorRef],[SubAccount],[OSDay],[OSAdvAmt],[OSDisbAmt], " +
"[OverUnderRecovery] ) " +
"VALUES " +
"('" + count + "','" + region + "','" + station + "','" + ponumber + "'," + invoicenumber + ",'" + invoicetype + "','" + filern + "','" + client + "','" + origin + "'," + destination + "," +
"'" + (string)agingdate.ToString() + "','" + (string)activitydate.ToString() + "','" + vendornumber + "',' " + vendorname + "',' " + vendorsite + "',' " + vendorref + "'," +
"'" + subaccount + "','" + osdaye + "','" + osaa + "','" + osda + "','" + our + "') ";
cmd = new SqlCommand(squery, con);
cmd.CommandTimeout = 1500;
cmd.ExecuteNonQuery();
}
label2.Text = count.ToString();
Application.DoEvents();
count++;
}
MessageBox.Show("Process completed");
}
else
{
MessageBox.Show("path select");
}
}
private void button3_Click(object sender, EventArgs e)
{
this.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
con = new SqlConnection("Data Source=192.168.50.200;User ID=EGL_TEST;Password=TEST;Initial Catalog=EGL_TEST;");
con.Open();
}
}
}
vendername field contain data (MCCOLLISTER'S TRANSPORTATION) so how to pass this data
