How do I insert data if it contains an apostrophe?
Actually my task is to upload csv file to sql server using C #, so I separated it by comma, my problem is that some field data contains apostrop and im run an insert query to load data into sql, so it gives error for my coding this way
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();
}
}
}
Username field contains data (MCCOLLISTER TRANSPORTATION), so how to pass that data
+2
a source to share
3 answers
Use prepared statements , in this case SqlParameterCollection.AddWithValue or equivalent. There are many tutorials available for this.
+6
a source to share
You are very naughty for building your sql statements this way, Santa Claus is definitely not going to visit you this year. Executing queries in a way that you open yourself up to SQL injection, intentional and unintentional, as you discovered with.
You have to use parameterized query strings or stored procedures.
const string connString = "Data Source=localhost;Initial Catalog=OnlineQuiz;Integrated Security=True";
static void Main(string[] args)
{
string query = string.Format("SELECT * FROM [User] WHERE name like @name");
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@name", "F%");
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader.GetValue(1));
}
}
}
}
}
-2
a source to share
You need to escape the apostrophe by adding a second apostrophe:
vendorname = vendorname.Replace("'", "''");
Denial of responsibility. Writing the original SQL statement without using parameters is dangerous. Ideally, you should write a complete SQL insert statement with the assumed parameters, and instead of concatenating the value directly in the string, pass it as a parameter:
string parameterizedSQL = "insert into credit_debit1 (id,region,station) values (@count, @region,@station)";
SqlCommand cmd = new SqlCommand(parameterizedSQL, con);
cmd.Parameters.Add("@count", SqlDbType.Int).Value = count;
cmd.Parameters.Add("@region", SqlDbType.VarChar).Value = region;
cmd.Parameters.Add("@station", SqlDbType.VarChar).Value = station;
cmd.ExecuteNonQuery();
-2
a source to share