Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Varchar to Numeric help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 10/22/2014 :  14:29:43  Show Profile  Reply with Quote
Hi Everyone,

I was task with a simple program that will read a text file then insert those values into our sql tables.

I am writing the code in c#, here it is --


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.OleDb;
using System.Data.SqlClient;



namespace WindowsFormsApplication4
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
listView1.View = View.Details;
listView1.GridLines = true;
listView1.FullRowSelect = true;

listView1.Columns.Add("EMP_KEY", 80);
listView1.Columns.Add("DEBTOR_KEY", 90);
listView1.Columns.Add("EMP_NAME", 200);
listView1.Columns.Add("INDUSTRY", 100);
listView1.Columns.Add("LNAME", 100);
listView1.Columns.Add("FNAME", 100);
listView1.Columns.Add("MI", 30);
listView1.Columns.Add("ADD1", 150);
listView1.Columns.Add("ADD2", 150);
listView1.Columns.Add("CITY", 100);
listView1.Columns.Add("STATE", 50);
listView1.Columns.Add("ZIP", 60);
listView1.Columns.Add("PHONE1", 100);
listView1.Columns.Add("EXT1", 50);
listView1.Columns.Add("PHONE2", 100);
listView1.Columns.Add("EXT2", 50);
listView1.Columns.Add("FAX", 100);
listView1.Columns.Add("EMAIL", 100);
listView1.Columns.Add("OCCUPATION", 100);
listView1.Columns.Add("JOB_TITLE", 100);
listView1.Columns.Add("HIRE_DATE", 100);
listView1.Columns.Add("TERM_DATE", 100);
listView1.Columns.Add("FREQ", 70);
listView1.Columns.Add("WAGE", 70);
listView1.Columns.Add("FT_FLAG", 70);
}

private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog Ofd = new OpenFileDialog();

try
{
Ofd.CheckFileExists = true;
Ofd.CheckPathExists = true;
Ofd.DefaultExt = "txt";
Ofd.DereferenceLinks = true;
Ofd.Filter = "Text files (*.txt)|*.txt|" +
"RTF files (*.rtf)|*.rtf|" + " + Works 6 and 7 (*.wps)|*.wps|" + "Windows Write (*.wri)|*.wri|" +
"WordPerfect document (*.wpd)|*.wpd";
Ofd.Multiselect = false;
Ofd.RestoreDirectory = true;
Ofd.ShowHelp = true;
Ofd.ShowReadOnly = false;
Ofd.Title = "Select a file ";
Ofd.ValidateNames = true;

if (Ofd.ShowDialog() == DialogResult.OK)
{
string line = "";
string[] items;
ListViewItem listItem;
string fileToOpen = Ofd.FileName;
StreamReader reader = new StreamReader(fileToOpen);
while ((line = reader.ReadLine()) != null)
{
items = line.Split('\t');
listItem = new ListViewItem();
for (int i = 0; i < items.Length; i++)
{
if (i == 0)
{
listItem.Text = items[i];
}
else
{

listItem.SubItems.Add(items[i]);

}

}
listView1.Items.Add(listItem);
}
}
}
catch
{
MessageBox.Show("Can not open the file", "Employer Import Utility");
}
}

private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{

}

private void button2_Click(object sender, EventArgs e)
{

SqlConnection conn = new SqlConnection();
conn.ConnectionString =
"Data Source = [server];" +
"Initial Catalog = [table];" +
"User id = [userid];" +
"Password = [password];";
conn.Open();

foreach (ListViewItem li in listView1.Items)
{
// SqlCommand cmd = new SqlCommand("insert into employer (EMP_KEY,DEBTOR_KEY,EMP_NAME,INDUSTRY,LNAME,FNAME,MI,ADD1,ADD2,CITY,STATE,ZIP,PHONE1,EXT1,PHONE2,EXT2,FAX,EMAIL,OCCUPATION,JOB_TITLE,HIRE_DATE,TERM_DATE,FREQ,WAGE,FT_FLAG)values('" + Int32.Parse(li.SubItems[0].Text) + "','" + Int32.Parse(li.SubItems[1].Text) + "','" + li.SubItems[2].Text + "','" + li.SubItems[3].Text + "','" + li.SubItems[4].Text + "','" + li.SubItems[5].Text + "','" + li.SubItems[6].Text + "','" + li.SubItems[7].Text + "','" + li.SubItems.Text + "','" + li.SubItems[9].Text + "','" + li.SubItems[10].Text + "','" + li.SubItems[11].Text + "','" + li.SubItems[12].Text + "','" + li.SubItems[13].Text + "','" + li.SubItems[14].Text + "','" + li.SubItems[15].Text + "','" + li.SubItems[16].Text + "','" + li.SubItems[17].Text + "','" + li.SubItems[18].Text + "','" + li.SubItems[19].Text + "','" + DateTime.Parse(li.SubItems[20].Text) + "','" + DateTime.Parse(li.SubItems[21].Text) + "','" + li.SubItems[22].Text + "','" + Int32.Parse(li.SubItems[23].Text) + "','" + li.SubItems[24].Text + "')", conn);
SqlCommand cmd = new SqlCommand("insert into employer (EMP_KEY,DEBTOR_KEY,EMP_NAME,INDUSTRY,LNAME,FNAME,MI,ADD1,ADD2,CITY,STATE,ZIP,PHONE1,EXT1,PHONE2,EXT2,FAX,EMAIL,OCCUPATION,JOB_TITLE,HIRE_DATE,TERM_DATE,FREQ,WAGE,FT_FLAG)values('" + li.SubItems[0].Text + "','" + li.SubItems[1].Text + "','" + li.SubItems[2].Text + "','" + li.SubItems[3].Text + "','" + li.SubItems[4].Text + "','" + li.SubItems[5].Text + "','" + li.SubItems[6].Text + "','" + li.SubItems[7].Text + "','" + li.SubItems.Text + "','" + li.SubItems[9].Text + "','" + li.SubItems[10].Text + "','" + li.SubItems[11].Text + "','" + li.SubItems[12].Text + "','" + li.SubItems[13].Text + "','" + li.SubItems[14].Text + "','" + li.SubItems[15].Text + "','" + li.SubItems[16].Text + "','" + li.SubItems[17].Text + "','" + li.SubItems[18].Text + "','" + li.SubItems[19].Text + "','" + li.SubItems[20].Text + "','" + li.SubItems[21].Text + "','" + li.SubItems[22].Text + "','" + li.SubItems[23].Text + "','" + li.SubItems[24].Text + "')",conn);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
}


It works up to the insert, it seems like everything in a listview is a varchar and our table is data type specific....Can anyone show me how to cast or convert the listview's values to the correct data types?

Thanks,
Chris
Jr Programmer

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 10/22/2014 :  15:27:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
Switch your code to use parameterized queries and not a concatenated string that you execute. This will avoid SQL injection as well as help with the data type issue since you specify the data types with parameterized queries.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 10/22/2014 :  15:31:11  Show Profile  Reply with Quote
Tara,

Thnaks for the reply, I am currently reading msdn on parameters, first go at oop so I not sure how to get it done....Lots of reading to do

Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 10/22/2014 :  16:09:21  Show Profile  Reply with Quote
foreach (ListViewItem li in listView1.Items)
{
string cmdstr = "INSERT INTO EMPLOYER (EMP_KEY,DEBTOR_KEY,EMP_NAME,INDUSTRY,LNAME,FNAME,MI,ADD1,ADD2,CITY,STATE,ZIP,PHONE1,EXT1,PHONE2,EXT2,FAX,EMAIL,OCCUPATION,JOB_TITLE,HIRE_DATE,TERM_DATE,FREQ,WAGE,FT_FLAG) VALUES (@EMP_KEY,@DEBTOR_KEY,@EMP_NAME,@INDUSTRY,@LNAME,@FNAME,@MI,@ADD1,@ADD2,@CITY,@STATE,@ZIP,@PHONE1,@EXT1,@PHONE2,@EXT2,@FAX,@EMAIL,@OCCUPATION,@JOB_TITLE,@HIRE_DATE,@TERM_DATE,@FREQ,@WAGE,@FT_FLAG)";
using (SqlCommand command = new SqlCommand(cmdstr,conn)){
command.Parameters.Add("@EMP_KEY", SqlDbType.Int);
command.Parameters["@EMP_KEY"].Value = li.SubItems[0].Text;
command.Parameters.Add("@DEBTOR_KEY", SqlDbType.Int);
command.Parameters["@DEBTOR_KEY"].Value = li.SubItems[1].Text;
command.Parameters.Add("@EMP_NAME", SqlDbType.Char);
command.Parameters["@EMP_NAME"].Value = li.SubItems[2].Text;
command.Parameters.Add("@INDUSTRY", SqlDbType.Char);
command.Parameters["@INDUSTRY"].Value = li.SubItems[3].Text;
command.Parameters.Add("@LNAME", SqlDbType.Char);
command.Parameters["@LNAME"].Value = li.SubItems[4].Text;
command.Parameters.Add("@FNAME", SqlDbType.Char);
command.Parameters["@FNAME"].Value = li.SubItems[5].Text;
command.Parameters.Add("@MI", SqlDbType.Char);
command.Parameters["@MI"].Value = li.SubItems[6].Text;
command.Parameters.Add("@ADD1", SqlDbType.Char);
command.Parameters["@ADD1"].Value = li.SubItems[7].Text;
command.Parameters.Add("@ADD2", SqlDbType.Char);
command.Parameters["@ADD2"].Value = li.SubItems.Text;
command.Parameters.Add("@CITY", SqlDbType.Char);
command.Parameters["@CITY"].Value = li.SubItems[9].Text;
command.Parameters.Add("@STATE", SqlDbType.Char);
command.Parameters["@STATE"].Value = li.SubItems[10].Text;
command.Parameters.Add("@ZIP", SqlDbType.Char);
command.Parameters["@ZIP"].Value = li.SubItems[11].Text;
command.Parameters.Add("@PHONE", SqlDbType.Char);
command.Parameters["@PHONE"].Value = li.SubItems[12].Text;
command.Parameters.Add("@EXT1", SqlDbType.Char);
command.Parameters["@EXT1"].Value = li.SubItems[13].Text;
command.Parameters.Add("@PHONE2", SqlDbType.Char);
command.Parameters["@PHONE2"].Value = li.SubItems[14].Text;
command.Parameters.Add("@EXT2", SqlDbType.Char);
command.Parameters["@EXT2"].Value = li.SubItems[15].Text;
command.Parameters.Add("@FAX", SqlDbType.Char);
command.Parameters["@FAX"].Value = li.SubItems[16].Text;
command.Parameters.Add("@EMAIL", SqlDbType.Char);
command.Parameters["@EMAIL"].Value = li.SubItems[17].Text;
command.Parameters.Add("@OCCUPATION", SqlDbType.Char);
command.Parameters["@OCCUPATION"].Value = li.SubItems[18].Text;
command.Parameters.Add("@JOB_TITLE", SqlDbType.Char);
command.Parameters["@JOB_TITLE"].Value = li.SubItems[19].Text;
command.Parameters.Add("@HIRE_DATE", SqlDbType.DateTime);
command.Parameters["@HIRE_DATE"].Value = li.SubItems[20].Text;
command.Parameters.Add("@TERM_DATE", SqlDbType.DateTime);
command.Parameters["@TERM_DATE"].Value = li.SubItems[21].Text;
command.Parameters.Add("@FREQ", SqlDbType.Char);
command.Parameters["@FREQ"].Value = li.SubItems[22].Text;
command.Parameters.Add("@WAGE", SqlDbType.Int);
command.Parameters["@WAGE"].Value = li.SubItems[23].Text;
command.Parameters.Add("@FT_FLAG", SqlDbType.Bit);
command.Parameters["@FT_KEY"].Value = li.SubItems[24].Text;

Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 10/22/2014 :  19:00:02  Show Profile  Reply with Quote
well guys at the end I changed the .add to .AddWithValue, anyone not in the know, that takes whatever data type is in the column and sets it as the data type....its pretty slick.

example

command.Parameters.AddWithValue("@FT_FLAG",li.SubItems[24].Text);

Thanks,
Chris
Jr Programmer
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000