Solved

App.Config Connection String - Null Exception

Posted on 2014-03-12
15
879 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
  • 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In order to have all security and back ups taken care of, WordPress users can sign up for services with WP Engine.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now