SQL Tutorial err msg

Getting an error msg an can't seem to locate how and why

Page 1 of 1

2 Replies - 732 Views - Last Post: 28 May 2009 - 12:50 PM Rate Topic: -----

#1 LEMarshall  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 04-May 09

SQL Tutorial err msg

Posted 28 May 2009 - 10:25 AM

I'm not undersatnding the error msg I'm getting. In the immediate window the value for the configuration settings seems to be correct, Intellisense seems to indicate it is correct, but the error indicates the reference is Null
I've attached a jpeg showing the error and the immediate window as well as the entire code is below. I'm trying to fill in a JanusGridEx once I can get the proper settings.

Thansk for any assistance.
Larry

 Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings


Public Class Form1
	Private gridDataSource As DataSet

	''' <summary>
	''' Function to retrieve the connection from the app.config
	''' </summary>
	''' <param name="conName">Name of the connectionString to retrieve</param>
	''' <returns></returns>
	''' <remarks></remarks>
	Public Shared Function GetConnectionString(ByVal conName As String) As String
		'variable to hold our connection string for returning it
		Dim strReturn As New String("")
		'check to see if the user provided a connection string name
		'this is for if your application has more than one connection string
		If Not String.IsNullOrEmpty(conName) Then
			'a connection string name was provided
			'get the connection string by the name provided
			strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString
		Else
			'no connection string name was provided
			'get the default connection string
			'strReturn = ConfigurationManager.ConnectionStrings("YourConnectionName").ConnectionString
			strReturn = "Persist Security Info=False;" & _
							"Data Source=c:\ecologics\data; " & _
							"Initial Catalog=ecoSystem; " & _
							"Integrated Security=SSPI; " & _
							"Trusted_Connection=TRUE; " & _
							"Application Name=Ecologics " & _
							"providerName = System.Data.SqlClient"

		End If
		'return the connection string to the calling method
		Return strReturn
	End Function

	Public Shared Function GetBindingSource(ByVal cmd As SqlCommand) As BindingSource
		'declare our binding source
		Dim oBindingSource As New BindingSource()
		' Create a new data adapter based on the specified query.
		Dim daGet As New SqlDataAdapter(cmd)
		' Populate a new data table and bind it to the BindingSource.
		Dim dtGet As New DataTable()
		'set the timeout of the SqlCommandObject
		cmd.CommandTimeout = 240
		dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
		Try
			'fill the DataTable with the SqlDataAdapter
			daGet.Fill(dtGet)
		Catch ex As Exception
			'check for errors
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error receiving data")
			Return Nothing
		End Try
		'set the DataSource for the BindingSource to the DataTable
		oBindingSource.DataSource = dtGet
		'return the BindingSource to the calling method or control
		Return oBindingSource
	End Function

	Public Shared Sub HandleConnection(ByVal conn As SqlConnection)
		With conn
			'do a switch on the state of the connection
			Select Case .State
				Case ConnectionState.Open
					'the connection is open
					'close then re-open
					.Close()
					.Open()
					Exit Select
				Case ConnectionState.Closed
					'connection is open
					'open the connection
					.Open()
					Exit Select
				Case Else
					.Close()
					.Open()
					Exit Select
			End Select
		End With
	End Sub

	Public Shared Function GetRecords() As BindingSource
		'The value that will be passed to the Command Object (this is a stored procedure)
		Dim sSQL As String
		'If using inline sql format is as such
		sSQL = "SELECT ecoLease.Name, ecoCostCenter.CCName, ecoCostCenter.CCCode, " & _
				"ecoWell.WellNumber FROM ecoCostCenter INNER JOIN " & _
				"(ecoLease INNER JOIN ecoWell ON " & _
				"ecoLease.ecoLeaseID = ecoWell.ecoLeaseID) " & _
				"ON ecoCostCenter.ecoCostCenterID = ecoWell.ecoCostCenterID;"
		'If not using the Express Edition uncomment the next line
		Dim cnGetRecords As New SqlConnection(GetConnectionString("EcoTest.My.MySettings.mdfList"))
		'SqlConnection Object to use
		Dim cmdGetRecords As New SqlCommand()
		'SqlCommand Object to use
		Dim daGetRecords As New SqlDataAdapter()
		Dim dsGetRecords As New DataSet()
		'Clear any parameters
		cmdGetRecords.Parameters.Clear()
		Try
			With cmdGetRecords
				'set the SqlCommand Object Parameters
				.CommandText = sSQL
				'Tell it its inline sql 
				.CommandType = CommandType.Text 'for inline sql
				'Set the Connection for the Command Object
				'.Connection = cnGetRecords
				.Connection = cnGetRecords
			End With
			'set the state of the SqlConnection Object
			HandleConnection(cnGetRecords)
			'create BindingSource to return for our DataGrid Control
			Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)
			'now check to make sure a BindingSource was returned
			If Not oBindingSource Is Nothing Then
				'return the binding source to the calling method
				Return oBindingSource
			Else
				'no binding source was returned
				'let the user know the error
				Throw New Exception("There was no BindingSource returned")
				Return Nothing
			End If
		Catch ex As Exception
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error Retrieving Data")
			Return Nothing
		Finally
			HandleConnection(cnGetRecords)
		End Try
	End Function


	Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		GetConnectionString("mdfList")
		'GetBindingSource(GetRecords)
		GetRecords()
		'  LoadJanusGridEx()
	End Sub
End Class

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: SQL Tutorial err msg

#2 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: SQL Tutorial err msg

Posted 28 May 2009 - 11:21 AM

It is null, because a connection string by the name, "EcoTest.My.MySettings.mdflist" doesn't exist in your App.Config file.

I'm not sure why you are attempting to get the ConnectionString from a saved Resource. Unless, "EcoTest.My.MySettings.mdflist" is the name of the connection string in the App.Config file.
Was This Post Helpful? 0
  • +
  • -

#3 LEMarshall  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 04-May 09

Re: SQL Tutorial err msg

Posted 28 May 2009 - 12:50 PM

View PostJayman, on 28 May, 2009 - 10:21 AM, said:

It is null, because a connection string by the name, "EcoTest.My.MySettings.mdflist" doesn't exist in your App.Config file.

I'm not sure why you are attempting to get the ConnectionString from a saved Resource. Unless, "EcoTest.My.MySettings.mdflist" is the name of the connection string in the App.Config file.



I had been messing with a variety of setings and that was one that was left over. I've deleted several that had been saved from the file:
	<connectionStrings>
		<add name="mdfList" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EcoLogics\Data\EcoLogics.mdb"
			providerName="System.Data.OleDb" />
		<add name="EcoTest.My.MySettings.mdfList" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EcoLogics\Data\EcoLogics.mdb"
			providerName="System.Data.OleDb" />
	</connectionStrings>




I've cut out the

add name="EcoTest.My.MySettings.mdfList" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EcoLogics\Data\EcoLogics.mdb"
			providerName="System.Data.OleDb" />



but still get the same message. Where else would I get the seetings if not from a saved resource file. It seems that is the direction the SQL Tutorial indicates. I've tried saving the strings sdirectly to the variables in the immediate window:

strReturn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EcoLogics\Data\EcoLogics.mdb"



But I end up with more errors further along it the code. I thought following the tutorial would be fairly easy but it has been nothing but a headache- I'm reading a whole lot more of MSDN help than I thought I would ever read- and still don't understand much. All I can say it was so much easier in VB6....but I need to learn it. So again, any assistance or help or an easier way. I am cacluating I will need to open up the database and get records from the 16 tables with approximately 25 different SQL Select statements to complete the program. 9 of the statements will be used to fill in forms and will need to navigate Next/Previous. The other 7 statements will be to fill in different grids to displaya the data.

Thansk again in advance for any assistance, help and suggestions.
Larry
Where else would I be loading
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1