Varchar to Numeric help - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Varchar to Numeric help

Author

Topic

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-22 : 14:29:43

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

38200 Posts

Posted - 2014-10-22 : 15:27:48

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/

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-22 : 15:31:11

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

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-22 : 16:09:21

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

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-22 : 19:00:02

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources