How to solve error : Column 'Column' does not belong to table 'Table' ? Code attached

Hello 

 

I have a dataset which is successfully being filled. However whenever i try 

 

to access a givewn row of one of the tables in the dataset i get the error 

 

"Column 'ContactId' does not belong to table 'SalesAgent'" 

 

Except that it does and if i use an ordinal reference i can access the value 

 

of the column. If i print the schema sure enough the column is called 

 

ContactId. I have also noticed that if i hover the cursor over the 

 

contactrow i can access a treeview which lists all columns in the contactrow 

 

and then tells me that none of them belong to table salesagent. 

 

This was a conversion from 1.1 to 2.0. Any ideas?  

 

Bellow is my code:

 

Dim ds As New DataSet

ds = objAdmin.Get_Login_Info(txtLogin.Text.Trim(), txtPwd.Text.Trim())

then after i call bellow function

Public Function Get_Login_Info(ByVal User_Login_Id As String, ByVal Pass As String) As DataSet

ds = New DataSet

Dim p(0) As SqlHelper.sqlParameteres p(0) = New SqlHelper.sqlParameteres

p(0).xName = "@UName"

p(0).xValue = User_Login_Id

p(0).xDbType = SqlDbType.VarChar

p(0).xDirection = ParameterDirection.Input

ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure,
"Usp_Login", p) Return ds

End Function

 SQL Helper Class:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Collections

Imports System.Diagnostics

'*********************************************************************

'

' The SqlHelper class is intended to encapsulate high performance, scalable best practices for

' common uses of SqlClient.

'

'*********************************************************************

Public NotInheritable Class SqlHelper Private Shared IntCommandTimeOut As Integer = 1000

'*********************************************************************

'

' Since this class provides only static methods, make the default constructor private to prevent

' instances from being created with "new SqlHelper()".

'

'*********************************************************************

Public Structure sqlParameteres

Dim xName As String

Dim xValue As Object

Dim xDirection As System.Data.ParameterDirection

Dim xSize As Integer

Dim xDbType As System.Data.SqlDbType

End Structure

Private Sub New()

IntCommandTimeOut = 1000

End Sub 'New

Public Shared Property CommandTimeOut() As Integer

Get

Return IntCommandTimeOut

End Get

Set(ByVal NewCommandTimeOut As Integer)

IntCommandTimeOut = NewCommandTimeOut

End Set

End Property

'******************************

'*********************************************************************

'

' This method is used to attach array of SqlParameters to a SqlCommand.

'

' This method will assign a value of DbNull to any parameter with a direction of

' InputOutput and a value of null.

'

' This behavior will prevent default values from being used, but

' this will be the less common case than an intended pure output parameter (derived as InputOutput)

' where the user provided no input value.

'

' param name="command" The command to which the parameters will be added

' param name="commandParameters" an array of SqlParameters tho be added to command

'

'*********************************************************************

Private Shared Sub AttachParameters(ByVal command As SqlCommand, ByVal commandParameters() As SqlParameter)

Dim p As SqlParameter

For Each p In commandParameters

'check for derived output value with no value assigned

If p.Direction = ParameterDirection.InputOutput And p.Value Is Nothing Then

p.Value = Nothing

End If

command.Parameters.Add(p)

Next p

End Sub 'AttachParameters

'*********************************************************************

'

' This method assigns an array of values to an array of SqlParameters.

'

' param name="commandParameters" array of SqlParameters to be assigned values

' param name="parameterValues" array of objects holding the values to be assigned

'

'*********************************************************************

Private Shared Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal parameterValues() As Object)

Dim i As Short

Dim j As Short

If (commandParameters Is Nothing) And (parameterValues Is Nothing) Then

'do nothing if we get no data

Return

End If

' we must have the same number of values as we pave parameters to put them in

If commandParameters.Length <> parameterValues.Length Then

Throw New ArgumentException("Parameter count does not match Parameter Value count.")

End If

'value array

j = commandParameters.Length - 1

For i = 0 To j

commandParameters(i).Value = parameterValues(i)

Next

End Sub 'AssignParameterValues

Private Shared Sub PrepareCommand(ByVal command As SqlCommand, _

ByVal connection As SqlConnection, _

ByVal transaction As SqlTransaction, _

ByVal commandType As CommandType, _

ByVal commandText As String, _ ByVal commandParameters() As SqlParameter)

'if the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

End If

'associate the connection with the command

command.Connection = connection

 

 

'set the command text (stored procedure name or SQL statement)

command.CommandText = commandText

'if we were provided a transaction, assign it.

If Not (transaction Is Nothing) Then

command.Transaction = transaction

End If

'set the command type

command.CommandType = commandType

'Added By dinesh on 16-Jan-2008

command.CommandTimeout = IntCommandTimeOut

'attach the command parameters if they are provided

If Not (commandParameters Is Nothing) Then

AttachParameters(command, commandParameters)

End If

Return

End Sub 'PrepareCommand

 

Public Overloads Shared Function ExecuteNonQuery(ByVal commandType As CommandType, _

ByVal commandText As String, _

ByVal strOutParamName As String, _ ByVal ParamArray Parameters() As Object)

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim retval As Integer

Dim connection As SqlConnection 'Used to hold the connection object

Dim sqlSPParam() As SqlParameter 'Used to hold array of SP Parameters of SQLParameter type

Dim strOutParamVal As String

'Call the internal function to build the SQLParameter from the array object

sqlSPParam = CreateParameters(Parameters)

'Get the connection from the connection pool

connection = ConnectionPool.GetConnection

'** Jignesh Temporary solution - Need to get help from TA

If connection Is Nothing Then

connection = ConnectionPool.GetConnection

If connection Is Nothing Then

Return Nothing

Exit Function

End If

End If

'*******************************

'Call the internal function to prepare the command object

PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, sqlSPParam)

'finally, execute the command.

retval = cmd.ExecuteNonQuery()

'If name of the output parameter is specified then retrive its value from the command object

If strOutParamName <> "" Then

strOutParamVal = cmd.Parameters(strOutParamName).Value

End If

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

ConnectionPool.ReleaseConnection(connection)

Return strOutParamVal End Function 'ExecuteNonQuery

 

'*************************************************************************************

Public Overloads Shared Function ExecuteNonQuery(ByVal commandType As CommandType, _ ByVal commandText As String, _

ByVal Transaction As SqlTransaction, _

ByVal strOutParamName As String, _ ByVal ParamArray Parameters() As Object)

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim retval As Integer

Dim connection As SqlConnection 'Used to hold the connection object

Dim sqlSPParam() As SqlParameter 'Used to hold array of SP Parameters of SQLParameter type

Dim strOutParamVal As String

'Call the internal function to build the SQLParameter from the array object

sqlSPParam = CreateParameters(Parameters)

'Get the connection from the connection pool

'connection = ConnectionPool.GetConnection

connection = Transaction.Connection

If connection Is Nothing Then

Return "-1"

Exit Function

End If

'Call the internal function to prepare the command object

PrepareCommand(cmd, connection, Transaction, commandType, commandText, sqlSPParam)

'finally, execute the command.

retval = cmd.ExecuteNonQuery()

'If name of the output parameter is specified then retrive its value from the command object

If strOutParamName <> "" Then

strOutParamVal = cmd.Parameters(strOutParamName).Value

End If

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

'ConnectionPool.ReleaseConnection(connection)

'**Do not release the connection here as it used in transaction

Return strOutParamVal

End Function 'ExecuteNonQuery

*************************************************************************************

'Purpose : The function will execute the Stored Procedures which contains the update queries.

'Within a same transaction

'*************************************************************************************

Public Shared Function BeginTrans() As SqlTransaction

Dim connection As SqlConnection

Dim Transaction As SqlTransaction

connection = ConnectionPool.GetConnection()

Transaction = connection.BeginTransaction

Return Transaction

End Function

Public Shared Function CommitTrans(ByVal myTransaction As SqlTransaction)Dim myCon As SqlConnection

myCon = myTransaction.Connection

If myCon.State <> ConnectionState.Open Then

myCon.Open()

End If

myTransaction.Commit()

ConnectionPool.ReleaseConnection(myCon)

End Function

Public Shared Function RollBackTrans(ByVal myTransaction As SqlTransaction)Dim myCon As SqlConnection

myCon = myTransaction.Connection

If myCon.State <> ConnectionState.Open Then

myCon.Open()

End If

myTransaction.Rollback()

ConnectionPool.ReleaseConnection(myCon)

End Function

'*********************************************************************

Private Overloads Shared Function ExecuteScalar(ByVal connectionString As String, _ ByVal spName As String, _

ByVal ParamArray parameterValues() As Object) As Object

Dim commandParameters As SqlParameter()

'if we receive parameter values, we need to figure out where they go

If Not (parameterValues Is Nothing) And parameterValues.Length > 0 Then

'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)

commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName)

'assign the provided values to these parameters based on parameter order

AssignParameterValues(commandParameters, parameterValues)

'call the overload that takes an array of SqlParameters

Return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters)

'otherwise we can just call the SP without params

Else

Return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName)

End If

End Function 'ExecuteScalar

 

Private Overloads Shared Function ExecuteScalar(ByVal connection As SqlConnection, _

ByVal commandType As CommandType, _

ByVal commandText As String, _

ByVal ParamArray commandParameters() As SqlParameter) As Object

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim retval As Object

PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters)

'execute the command & return the results

retval = cmd.ExecuteScalar()

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

Return retval

End Function 'ExecuteScalar

Public Overloads Shared Function ExecuteDataset(ByVal commandType As CommandType, _ ByVal commandText As String, _ ByVal ParamArray Parameters() As Object) As DataSet

 

Dim connection As SqlConnection

'Try

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim ds As New DataSet

Dim da As SqlDataAdapter

Dim myParam() As SqlParameter

'' Dim Temp_Cmd As String

 

If Parameters.Length > 0 Then

myParam = CreateParameters(Parameters)

End If

connection = ConnectionPool.GetConnection

'** Jignesh Temporary solution - Need to get help from TA

If connection Is Nothing Then

connection = ConnectionPool.GetConnection

If connection Is Nothing Then

Return Nothing

Exit Function

End If

End If

PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, myParam)

'cmd.CommandTimeout = 0

'create the DataAdapter & DataSet

da = New SqlDataAdapter(cmd)

'fill the DataSet using default values for DataTable names, etc.

da.Fill(ds)

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

ConnectionPool.ReleaseConnection(connection)

'return the dataset

Return ds

'Catch ex As Exception

' ConnectionPool.ReleaseConnection(connection)

'Finally

' ConnectionPool.ReleaseConnection(connection)

'End Try

End Function 'ExecuteDataset

Public Overloads Shared Function ExecuteDataset(ByVal commandType As CommandType, _

ByVal commandText As String, _

ByVal Transaction As SqlTransaction, _

ByVal myCon As SqlConnection, _

ByVal ParamArray Parameters() As Object) As DataSet Dim connection As SqlConnection

' Try

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim ds As New DataSet

Dim da As SqlDataAdapter Dim myParam() As SqlParameter

If Parameters.Length > 0 Then

myParam = CreateParameters(Parameters)

End If

'connection = myCon

'If connection Is Nothing Then

connection = ConnectionPool.GetConnection

If connection Is Nothing Then

Return Nothing

Exit Function

End If

'End If

'*******************************

PrepareCommand(cmd, connection, Transaction, commandType, commandText, myParam)

'cmd.CommandTimeout = 0

'create the DataAdapter & DataSet

da = New SqlDataAdapter(cmd)

'fill the DataSet using default values for DataTable names, etc.

da.Fill(ds)

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

'ConnectionPool.ReleaseConnection(connection)

'return the dataset

Return ds

'Catch ex As Exception

' ConnectionPool.ReleaseConnection(connection)

'Finally

' ConnectionPool.ReleaseConnection(connection)

'End Try

End Function 'ExecuteDataset

Public Overloads Shared Function ExecuteDataset(ByVal commandType As CommandType, _

ByVal commandText As String, _

ByVal Transaction As SqlTransaction _ ) As DataSet

' Try

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim ds As New DataSet

Dim da As SqlDataAdapter

'*******************************

PrepareCommand(cmd, Transaction.Connection, Transaction, commandType, commandText, CType(Nothing, SqlParameter()))

'create the DataAdapter & DataSet

da = New SqlDataAdapter(cmd)

'fill the DataSet using default values for DataTable names, etc.

da.Fill(ds)

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

'ConnectionPool.ReleaseConnection(connection)

'return the dataset

Return ds

'Catch ex As Exception

' ConnectionPool.ReleaseConnection(Transaction.Connection)

'Finally

' ConnectionPool.ReleaseConnection(Transaction.Connection)

'End Try

End Function 'ExecuteDataset

'This function will create the Array of SqlParameter and returns it Private Shared Function CreateParameters(ByVal ParamArray Parameters() As Object) As SqlParameter()

'Dim mParamsArray As System.Array

Dim intParamCnt As Integer

Dim mParamsArray() As DataAccessLayer.SqlHelper.sqlParameteres

Try 'for 0 parameters

If Parameters Is Nothing Then

Return Nothing

End If

If Parameters.Length > 0 Then

mParamsArray = Parameters(0)

intParamCnt = UBound(mParamsArray)

Dim mySqlParam(intParamCnt) As SqlParameter

If mParamsArray.Length > 0 Then

Dim i As Integer

For i = 0 To intParamCnt

mySqlParam(i) = New SqlParameter

With mySqlParam(i)

.ParameterName = mParamsArray(i).xName

.SqlDbType = mParamsArray(i).xDbType

.Direction = mParamsArray(i).xDirection

If .Direction = ParameterDirection.Input Then

.Value = mParamsArray(i).xValue

ElseIf .Direction = ParameterDirection.Output Then

.Size = mParamsArray(i).xSize

End If

End With

Next

Return mySqlParam

End If

End If

Catch EX As Exception

End Try

End Function

 

#Region " "

'*********************************

' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.

' e.g.:

' Dim orderCount As Integer = CInt(ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"))

' Parameters:

' -transaction - a valid SqlTransaction

' -commandType - the CommandType (stored procedure, text, etc.)

' -commandText - the stored procedure name or T-SQL command

' Returns: an object containing the value in the 1x1 resultset generated by the command

Public Overloads Shared Function ExecuteScalar(ByVal transaction As SqlTransaction, _ ByVal commandType As CommandType, _

ByVal commandText As String) As Object

'pass through the call providing null for the set of SqlParameters

Return ExecuteScalar(transaction, commandType, commandText, CType(Nothing, SqlParameter()))

End Function 'ExecuteScalar

' Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction

' using the provided parameters.

' e.g.:

' Dim orderCount As Integer = CInt(ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)))

' Parameters:

' -transaction - a valid SqlTransaction

' -commandType - the CommandType (stored procedure, text, etc.)

' -commandText - the stored procedure name or T-SQL command

' -commandParameters - an array of SqlParamters used to execute the command

' Returns: an object containing the value in the 1x1 resultset generated by the command

Public Overloads Shared Function ExecuteScalar(ByVal transaction As SqlTransaction, _

ByVal commandType As CommandType, _

ByVal commandText As String, _

ByVal ParamArray commandParameters() As SqlParameter) As Object

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim retval As Object

Dim sqlSPParam() As SqlParameter 'Used to hold array of SP Parameters of SQLParameter type

'Call the internal function to build the SQLParameter from the array object

sqlSPParam = CreateParameters(commandParameters)

PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, sqlSPParam)

'execute the command & return the results

retval = cmd.ExecuteScalar()

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

Return retval

End Function 'ExecuteScalar

' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.

' e.g.:

' Dim orderCount As Integer = CInt(ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"))

' Parameters:

' -commandType - the CommandType (stored procedure, text, etc.)

' -commandText - the stored procedure name or T-SQL command

' Returns: an object containing the value in the 1x1 resultset generated by the command

Public Overloads Shared Function ExecuteScalar(ByVal commandType As CommandType, _

ByVal commandText As String) As Object

'pass through the call providing null for the set of SqlParameters

Dim connection As SqlConnection

connection = ConnectionPool.GetConnection

Try

Return ExecuteScalar(connection, commandType, commandText, CType(Nothing, SqlParameter()))

Finally

ConnectionPool.ReleaseConnection(connection)

End Try

End Function 'ExecuteScalar

 

' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.

' e.g.:

' Dim orderCount As Integer = CInt(ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"))

' Parameters:

' -strSpName - the stored procedure name

' -commandParameters - an array of SqlParamters used to execute the command

' Returns: an object containing the value in the 1x1 resultset generated by the command

Public Overloads Shared Function ExecuteScalar(ByVal strSpName As String, _

ByVal ParamArray commandParameters() As Object) As Object

Dim sqlSPParam() As SqlParameter 'Used to hold array of SP Parameters of SQLParameter type

'Call the internal function to build the SQLParameter from the array object

sqlSPParam = CreateParameters(commandParameters)

'pass through the call providing null for the set of SqlParameters

Dim connection As SqlConnection

connection = ConnectionPool.GetConnection

Try

Return ExecuteScalar(connection, CommandType.StoredProcedure, strSpName, sqlSPParam)

Finally

ConnectionPool.ReleaseConnection(connection)

End Try

End Function 'ExecuteScalar

' Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in

' the connection string.

' e.g.:

' Dim result as Integer = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders")

' Parameters:

' -connectionString - a valid connection string for a SqlConnection

' -commandType - the CommandType (stored procedure, text, etc.)

' -commandText - the stored procedure name or T-SQL command

' Returns: an int representing the number of rows affected by the command

Public Overloads Shared Function ExecuteNonQuery(ByVal commandType As CommandType, _

ByVal commandText As String) As Integer

Dim cn As SqlConnection

'nitin 19-Aug-04 this function is created only for use in NIFTServer Process classes for storing data in NIFT database

'pass through the call providing null for the set of SqlParameters

cn = ConnectionPool.GetConnection

Try

Return ExecuteNonQuery(cn, commandType, commandText, CType(Nothing, SqlParameter()))

Finally

ConnectionPool.ReleaseConnection(cn)

End Try

End Function 'ExecuteNonQuery

 

' Execute a SqlCommand (that returns no resultset) against the specified SqlConnection

' using the provided parameters.

' e.g.:

' Dim result as Integer = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24))

' Parameters:

' -connection - a valid SqlConnection

' -commandType - the CommandType (stored procedure, text, etc.)

' -commandText - the stored procedure name or T-SQL command

' -commandParameters - an array of SqlParamters used to execute the command

' Returns: an int representing the number of rows affected by the command

Private Overloads Shared Function ExecuteNonQuery(ByVal connection As SqlConnection, _

ByVal commandType As CommandType, _

ByVal commandText As String, _

ByVal ParamArray commandParameters() As SqlParameter) As Integer

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim sqlSPParam() As SqlParameter 'Used to hold array of SP Parameters of SQLParameter type

'Call the internal function to build the SQLParameter from the array object

sqlSPParam = CreateParameters(commandParameters)

Dim retval As Integer

Try

PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, sqlSPParam)

'finally, execute the command.

retval = cmd.ExecuteNonQuery()

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

Finally

'connection.Close()

End Try

Return retval End Function 'ExecuteNonQuery

 

'

Public Overloads Shared Function ExecuteNonQuery(ByVal transaction As SqlTransaction, _ ByVal commandType As CommandType, _

ByVal commandText As String) As Integer

'pass through the call providing null for the set of SqlParameters

Return ExecuteNonQuery(transaction, commandType, commandText, CType(Nothing, SqlParameter()))

End Function 'ExecuteNonQuery

' Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction

' using the provided parameters.

' e.g.:

' Dim result as Integer = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))

' Parameters:

' -transaction - a valid SqlTransaction

' -commandType - the CommandType (stored procedure, text, etc.)

' -commandText - the stored procedure name or T-SQL command

' -commandParameters - an array of SqlParamters used to execute the command

' Returns: an int representing the number of rows affected by the command

Private Overloads Shared Function ExecuteNonQuery(ByVal transaction As SqlTransaction, _

ByVal commandType As CommandType, _

ByVal commandText As String, _

ByVal ParamArray commandParameters() As SqlParameter) As Integer

'create a command and prepare it for execution

Dim cmd As New SqlCommand

Dim retval As Integer

Dim sqlSPParam() As SqlParameter 'Used to hold array of SP Parameters of SQLParameter type

'Call the internal function to build the SQLParameter from the array object

sqlSPParam = CreateParameters(commandParameters)

PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, sqlSPParam)

'finally, execute the command.

retval = cmd.ExecuteNonQuery()

'detach the SqlParameters from the command object, so they can be used again

cmd.Parameters.Clear()

Return retval

End Function 'ExecuteNonQuery

'*********************************

#End Region

#Region "Update DataSet"

Private Shared Sub IdentityAdded(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)

Try

If e.StatementType = StatementType.Insert Then

Dim ObjCon As SqlConnection

ObjCon = CType(sender, SqlDataAdapter).InsertCommand.Connection

Dim ObjTran As SqlTransaction

ObjTran = CType(sender, SqlDataAdapter).InsertCommand.Transaction

Dim ObjCommand As New SqlCommand("Select Ident_current('" + e.Row.Table.TableName + "') ", ObjCon, ObjTran)

e.Row(0) = ObjCommand.ExecuteScalar()

End If

Catch ex As Exception Throw ex

End Try

End Sub

#End Region

 

Protected Overrides Sub Finalize()

MyBase.Finalize()

End Sub

End Class

 

ConnectionPoll Class :

Imports System.Data.SqlClient

Imports System.Xml

Imports System.IO

'Connection pool management, must be moved to a separate file

'This class implements a connection pooling mechanism where upto 20 connections

'are cached for reuse.

'The size of connection pool (currently 20) must be eventually read from a config file

Public Class ConnectionPool

Public Shared connections As New ArrayList 'Array to hold the connetions

Private Shared conn As SqlConnection

Public Shared mStrDBConn As String = ""

Private Shared CONST_NUM_IV As String = "#@4ep3"

Private Shared CONST_NUM_KEY As String = "&38sd92"

'If there are connections available in the pool, return one from the pool.

'Else return a new connection

Public Shared Function GetConnection() As SqlConnection

If connections.Count > 0 Then

conn = connections.Item(0)

connections.RemoveAt(0)

Else

Try

conn = New SqlConnection(GetConnectionStringNew("ConnString")) ''ConnString as SERVER=INGMUMTKP1DTSA1;DATABASE=ISMS;User Id=isms;password=isms;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2

 conn.Open()

Catch ex As Exception

If conn.State <> ConnectionState.Closed Then

conn.Close()

End If

conn = New SqlConnection(GetConnectionStringNew("ConnStringTimeOut"))

''ConnStringTimeOut  as SERVER=INGMUMTKP1DTSA1;DATABASE=ISMS;User Id=isms;password=isms;Min Pool Size=5;Max Pool Size=60;Connect ''Timeout=45

conn.Open()

End Try

End If

Return conn

End Function

'If pool has less than 20 connections then add connection to the pool

'Else close the connection

Public Shared Function ReleaseConnection(ByVal conn As SqlConnection) As String

'If connections.Count < 50 Then

' connections.Add(conn)

' 'mStrDBConn = ""

'Else

' 'conn.Dispose()

' conn.Close()

' 'conn = Nothing

' '*************************************

'End If

conn.Close()

End Function

Private Shared Function GetEVal(ByVal strValue As String) As String

Dim strNewValue As String

Dim objCrypt As New DataAccessLayer.Crypt

objCrypt.IV = CONST_NUM_IV

objCrypt.KEY = CONST_NUM_KEY

strNewValue = objCrypt.ETAP(strValue)

objCrypt = Nothing

Return strNewValue

End Function

Private Shared Function GetDVal(ByVal strValue As String) As String

Dim strNewValue As String

Dim objCrypt As New DataAccessLayer.Crypt

objCrypt.IV = CONST_NUM_IV

objCrypt.KEY = CONST_NUM_KEY

strNewValue = objCrypt.DTAP(strValue)

objCrypt = Nothing

Return strNewValue

End Function

 

Private Shared Sub GetDBConnStringFromFile()

Dim objXMLdoc As New XmlDocument

Dim objXMLNodeLst As XmlNodeList

Try

objXMLdoc = DecryptXML()

objXMLNodeLst = objXMLdoc.GetElementsByTagName(
"Settings") mStrDBConn = objXMLNodeLst.ItemOf(0).Item("ConnString").InnerText

If mStrDBConn.Trim = "" Or mStrDBConn Is Nothing Then

Throw New Exception("Invalid Connection String retrieved from Config File")

End If

Catch ex As FileNotFoundException

Throw New Exception("APPLICATION CONFIG FILE MISSING")

Catch ex As Exception Throw ex

Finally

objXMLNodeLst = Nothing

objXMLdoc = Nothing

End Try

End Sub

Private Shared Function DecryptXML() As XmlDataDocument Dim xmlDOC As New System.Xml.XmlDataDocument

'''' TO DO Kindly provide the exact name & path of the xml file

Dim fil As New StreamReader(AppDomain.CurrentDomain.BaseDirectory & "\XMLData\Connection.xml")

'xmlDOC.LoadXml(GetDVal(fil.ReadToEnd))

xmlDOC.LoadXml(fil.ReadToEnd)

fil.Close()

fil = Nothing

Return xmlDOC

End Function

Private Shared Function DecryptXML1() As XmlDataDocument Dim xmlDOC As New System.Xml.XmlDataDocument

'''' TO DO Kindly provide the exact name & path of the xml file

Dim fil As New StreamReader(AppDomain.CurrentDomain.BaseDirectory & "\XMLData\ConnectionTimeOut.xml")

'xmlDOC.LoadXml(GetDVal(fil.ReadToEnd))

xmlDOC.LoadXml(fil.ReadToEnd)

fil.Close()

fil = Nothing

Return xmlDOC End Function

End Class

i am waiting for your feedback.

 

Thanks  

Dinesh Patel

Philips Electronics India Ltd 

0
tirthdinesh
7/9/2008 5:41:08 AM
asp.net.access-datasource 4679 articles. 0 followers. Follow

1 Replies
1141 Views

Similar Articles

[PageSpeed] 43

It shouldn't be any problem to do what you want. Please post the code you're using to access the DataSet that's causing the exception, and we'll help you figure it out.

Don


Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
7/9/2008 6:11:58 AM
Reply:

Web resources about - How to solve error : Column 'Column' does not belong to table 'Table' ? Code attached - asp.net.access-datasource

Resources last updated: 12/26/2015 11:25:21 PM