Solved

App.Config Connection String - Null Exception

Posted on 2014-03-12
15
937 Views
Last Modified: 2014-04-01
Hi!

I have the following settings for my connection string which is set up in App.Config.  I keep getting an error, Null Exception and cannot figure out why.  

Any help will be greatly appreciated.

<appSettings >
    <add key="DBConn" value="Data Source=Server; Initial Catalog=Database; user ID=User; 
                                       password=Password Integrated Security=True"/>
  </appSettings>
  <connectionStrings >
    <add name="DBConn" providerName="System.Data.SqlClient" connectionString="Data Source=Server; Initial Catalog=Database; user ID=User; 
                                       password=Password Integrated Security=True"/> 
  </connectionStrings> 

Open in new window



public List<Parts> GetPartsDetailFromPTNum(string PartNum)
        {
           
            List<Parts> partsList = new List<Parts>();
            var strConnection = ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString;


            SqlConnection myConn = new SqlConnection(strConnection);

            myConn.Open();
            string commandString = "GetECRPartsData";

            SqlCommand myCommand = new SqlCommand(commandString, myConn);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add("@PartNum", System.Data.SqlDbType.VarChar);
            myCommand.Parameters["@PartNum"].Value =PartNum ;
            
            //SqlDataReader myReader = myCommand.ExecuteReader();
            using (IDataReader myReader = myCommand.ExecuteReader()) 
            
            
            while (myReader.Read())
            
            {
                
                partsList.Add(new Parts() 

            { 
                ID = (myReader.IsDBNull(myReader.GetOrdinal("ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("ID"))),
                DESCRIPTION = (myReader.IsDBNull(myReader.GetOrdinal("DESCRIPTION")) ? (string)null : myReader.GetString(myReader.GetOrdinal("DESCRIPTION"))),
                ENGINEERING_MSTR = (myReader.IsDBNull(myReader.GetOrdinal("ENGINEERING_MSTR")) ? (string)null : myReader.GetString(myReader.GetOrdinal("ENGINEERING_MSTR"))),
                COMMODITY_CODE = (myReader.IsDBNull(myReader.GetOrdinal("COMMODITY_CODE")) ? (string)null : myReader.GetString(myReader.GetOrdinal("COMMODITY_CODE"))),
                FABRICATED = (myReader.IsDBNull(myReader.GetOrdinal("FABRICATED")) ? (string)null : myReader.GetString(myReader.GetOrdinal("FABRICATED"))),
                PURCHASED = (myReader.IsDBNull(myReader.GetOrdinal("PURCHASED")) ? (string)null : myReader.GetString(myReader.GetOrdinal("PURCHASED"))),
                DRAWING_ID = (myReader.IsDBNull(myReader.GetOrdinal("DRAWING_ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("DRAWING_ID"))),
                DRAWING_REV_NO = (myReader.IsDBNull(myReader.GetOrdinal("DRAWING_REV_NO")) ? (string)null : myReader.GetString(myReader.GetOrdinal("DRAWING_REV_NO"))),
                //QTY_AVAILABLE_ISS = (myReader.IsDBNull(myReader.GetOrdinal("QTY_AVAILABLE_ISS")) ? (string)null : myReader.GetString(myReader.GetOrdinal("QTY_AVAILABLE_ISS"))),
                USER_1 = (myReader.IsDBNull(myReader.GetOrdinal("USER_1")) ? (string)null : myReader.GetString(myReader.GetOrdinal("USER_1"))),
                HTS_CODE = (myReader.IsDBNull(myReader.GetOrdinal("HTS_CODE")) ? (string)null : myReader.GetString(myReader.GetOrdinal("HTS_CODE"))),
                REVISION_ID = (myReader.IsDBNull(myReader.GetOrdinal("REVISION_ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("REVISION_ID"))),
                //QTY_ON_HAND = (myReader.IsDBNull(myReader.GetOrdinal("QTY_ON_HAND")) ? (string)null : myReader.GetString(myReader.GetOrdinal("QTY_ON_HAND"))),
                USER_2 = (myReader.IsDBNull(myReader.GetOrdinal("USER_2")) ? (string)null : myReader.GetString(myReader.GetOrdinal("USER_2"))),
                USER_6 = (myReader.IsDBNull(myReader.GetOrdinal("USER_6")) ? (string)null : myReader.GetString(myReader.GetOrdinal("USER_6"))),
             
               
            

            });
                        

            }
            return partsList;

            myConn.Close();

            }

Open in new window

0
Comment
Question by:Annette Wilson, MSIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 39925350
Can you post inner text of your exception.
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 39925581
There might be some issue in your app.config file. If there is any error(syntax error or anything) in app.Config file and then it will not load config file and you will get null reference. So I would suggest please check your config file.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39925800
Getting a NullException at which point?
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:Annette Wilson, MSIS
ID: 39926700
I have attached the error screen.

Thank you for your help.
ConnectionStringError.JPG
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39926740
Ok, assuming you have included a reference to the System.Configuration assembly, and have an using statement for the System.Configuration namespace, then there is nothing fundamentally wrong with your code.

Where in your config file is the connectionStrings section defined? Relative to the top of the document i mean - it might help if you can post the whole config file (with any sensitive information redacted of course).
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39926889
This is my app.config file contents:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  
    <system.serviceModel>
      <behaviors>
        <serviceBehaviors>
          <behavior name="debug">
            <serviceDebug includeExceptionDetailInFaults ="true"/>
          </behavior>
        </serviceBehaviors>
       
      </behaviors>
        <bindings />
        <client />
      <services>
        <service name="ECN_ECRWCFService" behaviorConfiguration ="debug"/>
      </services>
    </system.serviceModel>
  
  <connectionStrings >
    <add name="DBConn" providerName="System.Data.SqlClient" connectionString="Data Source=Server; Initial Catalog=ONTIC; user ID=username; password=password"/> 
  </connectionStrings>
  <appSettings >
    <add key="DBConn" value="Data Source=server; Initial Catalog=ONTIC; user ID=userid; password=password"/>
  </appSettings>
</configuration>

Open in new window



There server name has a '\' in it. Does that make a difference?
the password has an '!' in it.  does that make a difference
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39927070
if the backslash is a problem, do you think I should put this in an external settings file?
If so, How would I write the code?
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39927366
I originally had the connection string defined in the webservice but had to move it because the production server has a backslash in the name and it was causing an error.

This is for SharePoint.  should I configure the configuration connection string differently?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39927380
You'd normally need to escape the backslash by using 2 slashes. Are youable to read anything from your config, or is it just the connection strings?
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39927480
It looks like it's just the connection string.
0
 
LVL 12

Expert Comment

by:Ammar Gaffar
ID: 39927505
Dear

Your App Config file you have :
 <connectionStrings >
    <add name="DBConn" providerName="System.Data.SqlClient" connectionString="Data Source=Server; Initial Catalog=ONTIC; user ID=username; password=password"/> 
  </connectionStrings>
  <appSettings >
    <add key="DBConn" value="Data Source=server; Initial Catalog=ONTIC; user ID=userid; password=password"/>
  </appSettings>

Open in new window


You are using DBConn in two sections connectionStrings and appSettings,  rename your connectionString name to DBConn1 and do the same in your code and try again.

Good Luck
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39927718
Ammar,
I tried renaming and it did not work.  I changed the connection string name in the config and where it is referenced in the service.  It still did not work.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39928657
It wouldn't make any difference, because it is perfectly valid to have two entries with the same name in different sections of the config.

You need to go back to basics, and start by checking if you can read anything from the config at all.... it may be that it isn't hooked up properly for some reason, so isn't seeing the config file, rather than just not seeing the connectionStrings section.
0
 
LVL 12

Accepted Solution

by:
Ammar Gaffar earned 500 total points
ID: 39928761
Dear technette,

You mentioned  This is for SharePoint , so your code will be executed in SharePoint server right?
if yes, then your code is trying to access configuration file for your SharePoint server and that is why you get this error, because your key is not stored there.

If you are developing Farm solution, then you can refere to bellow link (describing how to add and remove entries from web config)

http://smartrider.wordpress.com/2011/03/03/how-to-add-and-remove-entries-from-web-config-using-spwebconfigmodification/


Good Luck
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39952067
I check the event viewer and it appears that I needed to set up Alternate Access Mapping.  I will let you know the results shortly.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question