parse and load text file into DB using C# and Stored Procedure

Question

I'm having a problem with a continuing error of

'looking for Procedure or function 'Cup_INSFuneralHome' expects parameter '@funeralhomename', which was not supplied.'

I have made numerous alterations, but none making any effect, so I have to take from that I was looking in the wrong area.

When I'm debugging it seems as though it pulls in the correct information in the correct order. The original text file is formatted as:

company name
address
city state zip
telephone

I'm pretty stuck on this. I have taken a look through other posts but have not been able to find what I need, or able to extract what I need from the multitude of 'somewhat' similar posts. I'm afraid the problem is sitting right in front of me, but I just cannot see it. I need a fresh set of eyes. So I apologize in advance.

proc code:

ALTER procedure [dbo].[Cup_INSFuneralHome]
@funeralhomename nvarchar(50),
@addressone nvarchar(50),
@addresstwo nvarchar(50),
@CityName nvarchar(50),
@State nvarchar(10),
@Zipcode nchar(10),
@Telephone nchar(15),
@PrimaryContact nvarchar (50),
@EmailAddress nvarchar (50)
as

declare @cityid uniqueidentifier
declare @stateid uniqueidentifier 

select @cityid = (select cityid from [city] where CityName=(@CityName)) 
select @stateid = (select stateid from [State] where StateCode=ltrim(rtrim(@State)))

insert funeralhome(funeralhomename, addressone, addresstwo, cityid, stateid, zipcode,  telephone, PrimaryContact, EmailAddress)
values (@funeralhomename, @addressone, @addresstwo, @CityName, @State, @ZipCode,     @Telephone, @PrimaryContact, @EmailAddress)

C# code:

namespace abc
{
class Program
{
    static void Main(string[] args)
    {
        address myclass = new address();
        string dbConnection = "xyz";
        string path = "D:\\docs\\someDocument.txt";
        StreamReader sr = new StreamReader(path);

        int intcount=0;
        string sLine = "";
        ArrayList fhome = new ArrayList();
        ArrayList Ads = new ArrayList();

        while (sLine != null)
        {
            sLine = sr.ReadLine();
            if (sLine != null)
                fhome.Add(sLine);
            intcount=intcount+1;
        }

        sr.Close();

        int startcount=0;

        for (int n = 0; n < fhome.Count; n++)
        {
            char[] delim = {',', ' '};

            try
            {
                if (startcount == 0)
                {
                    myclass = new address();
                }

                if (fhome[n].ToString().Trim().Length > 0)
                {
                    if (!fhome[n].ToString().Contains("Funeral Home Profile"))
                    {
                        switch (startcount)
                        {
                            case 0:
                                myclass.company = fhome[n].ToString().Trim();
                                startcount = startcount + 1;
                                break;

                            case 1:
                                myclass.address1 = fhome[n].ToString().Trim();
                                startcount = startcount + 1;
                                break;

                            case 2:
                                myclass.address2 = fhome[n].ToString().Trim();
                                startcount = startcount + 1;
                                break;

                            case 3:
                                myclass.telephone = fhome[n].ToString().Trim();
                                startcount = 0;
                                Ads.Add(myclass);
                                break;
                        }
                    }
                }
            }               
            catch { }
        }

       SqlConnection conn = new SqlConnection(dbConnection);

       for(int n=0;n< Ads.Count;n++)
        {           
           address tclass = (address)Ads[n];

           int comloc;
           comloc = tclass.address2.IndexOf(",");             

              string funeralhomename = tclass.company.ToString();
              string street = tclass.address1.ToString();
              string street2 = "";
              string city = tclass.address2.Substring(0, comloc);
              string state = tclass.address2.Substring(comloc + 1, 3);
              string zip = tclass.address2.Substring(comloc + 4);
              string tel = tclass.telephone.Replace("Local:", "");
              string PrimaryContact = "";
              string EmailAddress = "";

              string tsql = ""; 

               tsql = (funeralhomename + ',' +
                      street + ',' + street2 + city + ',' +
                      state + zip + tel + PrimaryContact + EmailAddress);

           conn.Open();

           try
           {
               SqlCommand cmd = new SqlCommand("Cup_INSFuneralHome", conn);
               cmd.CommandType = CommandType.StoredProcedure;

               SqlParameter[] param = new SqlParameter[9];
               param[0] = new SqlParameter("@funeralhomename", SqlDbType.NVarChar);
               param[0].Value = funeralhomename;
               param[1] = new SqlParameter("@addressone", SqlDbType.NVarChar);
               param[1].Value = street;
               param[2] = new SqlParameter("@addresstwo", SqlDbType.NVarChar);
               param[2].Value = street2;
               param[3] = new SqlParameter("@cityname", SqlDbType.NVarChar);
               param[3].Value = city;
               param[4] = new SqlParameter("@State", SqlDbType.NVarChar);
               param[4].Value = state;
               param[5] = new SqlParameter("@zipCode", SqlDbType.NChar);
               param[5].Value = zip;
               param[6] = new SqlParameter("@Telephone", SqlDbType.NChar);
               param[6].Value = tel;
               param[7] = new SqlParameter("@PrimaryContact", SqlDbType.NVarChar);
               param[7].Value = PrimaryContact;
               param[8] = new SqlParameter("@EmailAddress", SqlDbType.NVarChar);
               param[8].Value = EmailAddress;

               cmd.ExecuteNonQuery();
           }

           catch
           {
               Debug.Print("Error with.." + tclass.company);
           }

           finally
           {
               conn.Close();
           }
           Debug.Print(tsql);
        }   
    }        
}
}
public class address 
{
private string _company;
private string _address1;
private string _address2;
private string _telephone;

public string company
{
    get { return _company; }
    set { _company = value; }
}

public string address1
{
    get { return _address1; }
    set { _address1 = value; }
}

public string address2
{
    get { return _address2; }
    set { _address2 = value; }
}

public string telephone
{
    get { return _telephone; }
    set { _telephone = value; }
}
}

cityid and stateid are uniqueidentifier??? Are you mad?? How many states and cities are you expecting? — Mitch Wheat
– Mitch Wheat, Commented Dec 15, 2010 at 14:58
the funeral homes are based on the city and state of the 'client' and / or the cemetery — drew craig
– drew craig, Commented Dec 15, 2010 at 16:24
Thank you all very much, I have implemented the necessary changes, however now i am getting 'Conversion failed when converting from a character string to uniqueidentifier'. I have even altered the proc and did away with UID just to get the data in, but the error continues — drew craig
– drew craig, Commented Dec 15, 2010 at 19:10

Klaus Byskov Pedersen · Accepted Answer · 2010-12-15 15:00:45Z

3

You are not adding the sql parameters to the cmd object ;-)

answered Dec 15, 2010 at 15:00

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xan · Accepted Answer · 2010-12-15 15:01:01Z

Looks like you need to add your parameters to your query before executing it.

You can shortcut this like so:

cmd.Parameters.Add("@thing", SqlDbType.Type).Value = value;

In your case:

cmd.Parameters.Add("@funeralhomename", SqlDbType.NVarChar).Value = funeralhomename;
cmd.Parameters.Add("@addressone", SqlDbType.NVarChar).Value = street;
cmd.Parameters.Add("@addresstwo", SqlDbType.NVarChar).Value = street2;
cmd.Parameters.Add("@cityname", SqlDbType.NVarChar).Value = city;
cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = state;
cmd.Parameters.Add("@zipCode", SqlDbType.NChar).Value = zip;
cmd.Parameters.Add("@Telephone", SqlDbType.NChar).Value = tel;
cmd.Parameters.Add("@PrimaryContact", SqlDbType.NVarChar).Value = PrimaryContact;
cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = EmailAddress;

Jeff · Accepted Answer · 2010-12-15 15:04:35Z

1

you create SqlParameter[] param but that collection is not what the SqlCommand uses when it executes the procedure. Get rid of that entire collection and use the command's Parameter property instead.

cmd.Parameters.AddWithValue("@funeralhomename", funeralhomename)

and do that with all the parameters in the query

answered Dec 15, 2010 at 15:04

Jeff

14.3k12 gold badges61 silver badges107 bronze badges

3 Comments

drew craig Over a year ago

Makes sense, and much cleaner code! However, there is now a conversion error of

drew craig Over a year ago

...Conversion failed when converting from a character string to uniqueidentifier.

Jeff Over a year ago

Sounds like you are trying to add a string value to a uniqueidentifier parameter. Try: cmd.Parameters.AddWithValue("@myparameter", new Guid(myStringValue))

Alex · Accepted Answer · 2010-12-15 15:05:19Z

You need to add your parameters to your SqlCommand object using for instance the SqlCommand.Parameters.AddRange method:

SqlCommand cmd = new SqlCommand("Cup_INSFuneralHome", conn);
               cmd.CommandType = CommandType.StoredProcedure;

               SqlParameter[] param = new SqlParameter[9];
               param[0] = new SqlParameter("@funeralhomename", SqlDbType.NVarChar);
               param[0].Value = funeralhomename;
               param[1] = new SqlParameter("@addressone", SqlDbType.NVarChar);
               param[1].Value = street;
               param[2] = new SqlParameter("@addresstwo", SqlDbType.NVarChar);
               param[2].Value = street2;
               param[3] = new SqlParameter("@cityname", SqlDbType.NVarChar);
               param[3].Value = city;
               param[4] = new SqlParameter("@State", SqlDbType.NVarChar);
               param[4].Value = state;
               param[5] = new SqlParameter("@zipCode", SqlDbType.NChar);
               param[5].Value = zip;
               param[6] = new SqlParameter("@Telephone", SqlDbType.NChar);
               param[6].Value = tel;
               param[7] = new SqlParameter("@PrimaryContact", SqlDbType.NVarChar);
               param[7].Value = PrimaryContact;
               param[8] = new SqlParameter("@EmailAddress", SqlDbType.NVarChar);
               param[8].Value = EmailAddress;

cmd.Parameters.AddRange(param );

Collectives™ on Stack Overflow

parse and load text file into DB using C# and Stored Procedure

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related