[Najla]

hi..
I am building a website (digital repository for learning objects)with Microsoft visual studio 2005/ vb.net and sql2005

I used the following code which allows the user to create his own personal collection of learning objects
(the user enters the name of the personal collectionin atext box and clicks on create)
then the table of personal collection shold be changed to include the user inputs.

Protected Sub CreatePC_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreatePC.Click

		Dim myConnection As SqlConnection
		Dim myTransaction As SqlTransaction = Nothing


		Dim cmd As String = "INSERT INTO PersonalCollection(PCID,PCName,AccID) Values (@PCID,@PCName,@AccID)"

		Try

			myConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString1").ToString())

			myConnection.Open()

			myTransaction = myConnection.BeginTransaction()

			Dim myCommand As SqlCommand = New SqlCommand(cmd, myConnection, myTransaction)
			myCommand.Parameters.AddWithValue("PCID", 4)
			myCommand.Parameters.AddWithValue("PCName", PCName.Text)
			myCommand.Parameters.AddWithValue("AccID", 53)
			myCommand.ExecuteNonQuery()

		Catch ex As Exception
			If (myTransaction.Equals(Nothing) = False) Then
				myTransaction.Rollback()
			End If
			myTransaction.Commit()
		Finally
			myConnection.Dispose()

		End Try
	End Sub

but unfortunatly an error appears for the line:
If (myTransaction.Equals(Nothing) = False) Then
the error:"NullReferenceException was unhandled by user code"
"Object reference not set to an instance of an object"

help please

[PsychoCoder]

Your first issue is when you declare your SqlTransaction Object you immediately set it to nothing

Dim myTransaction As SqlTransaction = Nothing

So when you try and reference it it doesn't exist. Try the following changes I made to your code

Protected Sub CreatePC_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreatePC.Click
    'Create and open our SqlConnection
    Using myConnection  As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString1").ToString())
        'Open the connection
        myConnection.Open()
        'Generate our query
        Dim cmd As String = "INSERT INTO PersonalCollection(PCID,PCName,AccID) Values (@PCID,@PCName,@AccID)"
        'Create a command
        Dim myCommand As SqlCommand = myConnection .CreateCommand()
        'Declare our SqlTransaction Object
        Dim myTransaction  As SqlTransaction
        'Start a local transaction
        myTransaction  = myConnection.BeginTransaction("CreatePC_Transaction")

        ' Must assign both transaction object and connection
        ' to Command object for a pending local transaction.
        myCommand.Connection = myConnection 
        myCommand.Transaction = myTransaction 

        Try
            'Set the properties of our SqlCommand
            myCommand.CommandText = cmd  'What is it executing
            myCommand.CommandType = CommandType.Text  'What kind of query are we executing
            'Add our Parameters
            myCommand.Parameters.AddWithValue("PCID", 4)
            myCommand.Parameters.AddWithValue("PCName", PCName.Text)
            myCommand.Parameters.AddWithValue("AccID", 53)
            'Execute the query
            myCommand.ExecuteNonQuery()
            'Attempt to commit our Transaction
            myTransaction.Commit()
        Catch ex As Exception
           'An Exception occurred so we need to
           'roll the transaction back
            myTransaction.Rollback()
        Finally
            'Close our connection now
            myConnection.Dispose()
        End Try
    End Using
End Sub

[Najla]

PsychoCoder
thanks very much for your fast reply
I tried your code but the same error appeaes but with the line:

Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString1").ToString())

if you have any idea about what causes this error please help me
thanks agian

This post has been edited by Najla: 02 April 2008 - 12:22 PM

[PsychoCoder]

Change the line with the error to

Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString1").ConnectionString

Also, show how you have your connection string in your web.config file if you can

[Najla]

PsychoCoder
I hope that my questions are not bothering you.,,,
the same error appears with the same line after your changes..
this is my web.config

<?xml version="1.0"?>
<!-- 
	Note: As an alternative to hand editing this file you can use the 
	web admin tool to configure settings for your application. Use
	the Website->Asp.Net Configuration option in Visual Studio.
	A full list of settings and comments can be found in 
	machine.config.comments usually located in 
	\Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
	<appSettings/>
	<connectionStrings/>
	<system.web>
		<!-- 
			Set compilation debug="true" to insert debugging 
			symbols into the compiled page. Because this 
			affects performance, set this value to true only 
			during development.

			Visual Basic options:
			Set strict="true" to disallow all data type conversions 
			where data loss can occur. 
			Set explicit="true" to force declaration of all variables.
		-->
		<roleManager enabled="true" />
  <compilation debug="true" strict="false" explicit="true"/>
		<pages>
			<namespaces>
				<clear/>
				<add namespace="System"/>
				<add namespace="System.Collections"/>
				<add namespace="System.Collections.Specialized"/>
				<add namespace="System.Configuration"/>
				<add namespace="System.Text"/>
				<add namespace="System.Text.RegularExpressions"/>
				<add namespace="System.Web"/>
				<add namespace="System.Web.Caching"/>
				<add namespace="System.Web.SessionState"/>
				<add namespace="System.Web.Security"/>
				<add namespace="System.Web.Profile"/>
				<add namespace="System.Web.UI"/>
				<add namespace="System.Web.UI.WebControls"/>
				<add namespace="System.Web.UI.WebControls.WebParts"/>
				<add namespace="System.Web.UI.HtmlControls"/>
			</namespaces>
		</pages>
		<!--
			The <authentication> section enables configuration 
			of the security authentication mode used by 
			ASP.NET to identify an incoming user. 
		-->
		<authentication mode="Forms" />
		<!--
			The <customErrors> section enables configuration 
			of what to do if/when an unhandled error occurs 
			during the execution of a request. Specifically, 
			it enables developers to configure html error pages 
			to be displayed in place of a error stack trace.

		<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
			<error statusCode="403" redirect="NoAccess.htm" />
			<error statusCode="404" redirect="FileNotFound.htm" />
		</customErrors>
		-->
	</system.web>
</configuration>

This post has been edited by Najla: 02 April 2008 - 12:36 PM

[PsychoCoder]

Well we now know why you're getting this error. You don't have a connection string in your web.config file. You wil notice at the top of your web.config a section dedicated to connection string (<connectionStrings/>, that is where you connection string must go. It must be like this

< connectionStrings>
	<add name="YourConnectionName"
   connectionString="Persist Security Info=False;
   Data Source=YourDatabase;
   Initial Catalog=YourTable;
   Integrated Security=SSPI;
   Trusted_Connection=TRUE;
   Application Name=SampleVBNetApplication"
   providerName="System.Data.SqlClient" />
  </ connectionStrings>

Just replace the items that begin with Your with your information, like your database name, your table, password, username, etc.

[Najla]

PsychoCoder
thank you very much
it works now

[PsychoCoder]

No problem, glad I could help