473,692 Members | 2,249 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with Connection based Transaction in SQL Helper class

Getting SQL Exception when trying to implement Connection
based Trasaction using SQL Helper class.

I am using the follwing function to execute my stored
procs:

-=-=-=-
ExecuteScalar(B yVal transaction As SqlTransaction, _
ByVal spName As String, _
ByVal ParamArray parameterValues () As Object)
-=-=-=-

I traced my code to reach at the exception causing line of
code. The exception is caused by
function "DiscoverSpPara meterSet" under SQL Helper Class.

-=-=-=-=-
Dim cn As New SqlConnection(c onnectionString )

Dim cmd As SqlCommand = New SqlCommand(spNa me, cn)
Dim discoveredParam eters() As SqlParameter
Try
cn.Open() ''''' Exception is raised here
-=-=-=-=-

Exception details:
Message "Login failed for user 'UserName'."
Source ".Net SqlClient Data Provider"

StackTrace " at
System.Data.Sql Client.Connecti onPool.GetConne ction
(Boolean& isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledCon
nection(SqlConn ectionString options, Boolean&
isInTransaction )
at System.Data.Sql Client.SqlConne ction.Open()
----
----

I found on tracing that "DiscoverSpPara meterSet" usage
connection string from transaction object
i.e. "Transaction.Co nnection.Connec tionString". I noticed
the connection string retrieved from Transaction object
does not contain the "pwd: password;" part and so the
connection can not be openned.

Am I doing anything wrong in my coding? Please help.

Thanks in advance for your time and help.

[SNIP]

Dim trans As SqlTransaction
Dim conn As SqlConnection
conn = New SqlConnection
(ConfigAccess.C onnectionString ())
If conn Is Nothing Then
Throw New Exception
ElseIf conn.State <> ConnectionState .Open
Then
conn.Open()
End If
Try
trans = conn.BeginTrans action()
Try
SqlHelper.Execu teScalar
(trans, "procName1" , param1, param2, param3)
SqlHelper.Execu teScalar
(trans, "procName2" , param1, param2, param3)
trans.Commit()
Catch ex As SqlException
trans.Rollback( )
Throw ex
Catch ex As Exception
Throw ex
Finally
trans.Dispose()
trans = Nothing
End Try
Catch ex As Exception
Throw ex
Finally
If conn.State <>
ConnectionState .Closed Then
conn.Close()
conn.Dispose()
conn = Nothing
End If
End Try

[/SNIP]

--
Regards,
Rahul Anand

Nov 20 '05 #1
4 4146
Hi Rahul,

I give some answers inline (probably not all), however I think this is more
a question for the newsgroup.

Adonet
<news://msnews.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

Web interface:

<http://communities2.microsoft.com/co...s/?dg=microsof
t.public.dotnet .framework.adon et>
I hope this helps, however there is a lot of guessing in my message?

Cor
-=-=-=-
ExecuteScalar(B yVal transaction As SqlTransaction, _
ByVal spName As String, _
ByVal ParamArray parameterValues () As Object)
-=-=-=-

I traced my code to reach at the exception causing line of
code. The exception is caused by
function "DiscoverSpPara meterSet" under SQL Helper Class.

-=-=-=-=-
Dim cn As New SqlConnection(c onnectionString )

Dim cmd As SqlCommand = New SqlCommand(spNa me, cn)
Dim discoveredParam eters() As SqlParameter
Try
cn.Open() ''''' Exception is raised here
-=-=-=-=-
When the exception is raised here you have or a wrong connection string, or
not a connection at all. And if so I would in this case first make a
connection with the designer and test that.

Exception details:
Message "Login failed for user 'UserName'."
Source ".Net SqlClient Data Provider"

StackTrace " at
System.Data.Sql Client.Connecti onPool.GetConne ction
(Boolean& isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledCon
nection(SqlConn ectionString options, Boolean&
isInTransaction )
at System.Data.Sql Client.SqlConne ction.Open()
----
----

I found on tracing that "DiscoverSpPara meterSet" usage
connection string from transaction object
i.e. "Transaction.Co nnection.Connec tionString". I noticed
the connection string retrieved from Transaction object
does not contain the "pwd: password;" part and so the
connection can not be openned.

Am I doing anything wrong in my coding? Please help.

Thanks in advance for your time and help.

[SNIP]

Dim trans As SqlTransaction
Dim conn As SqlConnection
conn = New SqlConnection
(ConfigAccess.C onnectionString ())
This set a new connection not a connection open
If conn Is Nothing Then
Throw New Exception
Therefore is in my opinion the two lines above useless
ElseIf conn.State <> ConnectionState .Open
Then
conn.Open()
End If

Try
trans = conn.BeginTrans action()
Try
SqlHelper.Execu teScalar
(trans, "procName1" , param1, param2, param3)
SqlHelper.Execu teScalar
(trans, "procName2" , param1, param2, param3)
trans.Commit()
Catch ex As SqlException
trans.Rollback( )
Throw ex
Catch ex As Exception
Throw ex
Finally
trans.Dispose()
trans = Nothing
End Try
Catch ex As Exception
Throw ex
Finally
If conn.State <>
ConnectionState .Closed Then This is useless again in my opinion.
conn.Close()
conn.Dispose()
conn = Nothing
The conn.Close closes the connection
The conn.Dispose closes the connection and removes the connection string

You have to choise from two of them where the dispose is advices for
networks with more than 100 connections.

Setting it to Nothing is useless after this all, and not advices to do.

End If
End Try

[/SNIP]

--
Regards,
Rahul Anand

Nov 20 '05 #2
Hi Cor,
When the exception is raised here you have or a wrong
connection string, or
not a connection at all. And if so I would in this case
first make a
connection with the designer and test that.


I also think the same.
You are right in your guess...

As I have already stated:
I found on tracing that "DiscoverSpPara meterSet" usage
connection string from transaction object
i.e. "Transaction.Co nnection.Connec tionString". I noticed the connection string retrieved from Transaction object
does not contain the "pwd: password;" part and so the
connection can not be openned.


The problem is basically in SQL Helper (DAAB)

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnbda/html/daab-rm.asp

It uses Connection string extracted from SqlTransaction
object. But the connection string obtained from a
SqlTransaction object hides the "pwd: password" part. And
hence the connection can not be openned with this
connection string.

If anybody have used connection based transaction using
SQL Helper Class ? Please help...

Or is there any bug in SQL Helper ? Which does not allow
us to do so..

--
Thanks,
Rahul Anand

Nov 20 '05 #3
I found the root cause for this problem:

Since the "Persist Security Info" is false by default, the connection string obtained from SqlTransaction object hides the pwd part

http://msdn.microsoft.com/library/de...connections.as

I have checked my code by appending "Persist Security Info=True" to my connection string, in that case everything works fine

But as it will give access to security-sensitive information I think it is not a good option to do this

Does it mean the SqlHelper class provided as DAAB does not provide a secure method to write connection based transactions under which I can execute two different stored procs

-
Regards
Rahul Anan
----- Rahul Anand wrote: ----

Hi Cor
When the exception is raised here you have or a wrong
connection string, o
not a connection at all. And if so I would in this case
first make
connection with the designer and test that


I also think the same
You are right in your guess..

As I have already stated
I found on tracing that "DiscoverSpPara meterSet" usag
connection string from transaction objec
i.e. "Transaction.Co nnection.Connec tionString". I notice the connection string retrieved from Transaction objec
does not contain the "pwd: password;" part and so th
connection can not be openned


The problem is basically in SQL Helper (DAAB

http://msdn.microsoft.com/library/default.asp
url=/library/en-us/dnbda/html/daab-rm.as

It uses Connection string extracted from SqlTransaction
object. But the connection string obtained from a
SqlTransaction object hides the "pwd: password" part. And
hence the connection can not be openned with this
connection string

If anybody have used connection based transaction using
SQL Helper Class ? Please help..

Or is there any bug in SQL Helper ? Which does not allow
us to do so.

-
Thanks
Rahul Anan
Nov 20 '05 #4
On Thu, 3 Jun 2004 22:56:02 -0700, Rahul Anand wrote:
I found the root cause for this problem:

Since the "Persist Security Info" is false by default, the connection string obtained from SqlTransaction object hides the pwd part.

http://msdn.microsoft.com/library/de...onnections.asp

I have checked my code by appending "Persist Security Info=True" to my connection string, in that case everything works fine.

But as it will give access to security-sensitive information I think it is not a good option to do this.

Does it mean the SqlHelper class provided as DAAB does not provide a secure method to write connection based transactions under which I can execute two different stored procs ?


There is an overload to the ExecuteScalar method that takes a SqlConnection
rather than a connection string. Have you tried creating your own
connection object? Use that connection object to create the Transaction
and then use the ExecuteScalar method overload that takes both objects as
parameters.

Just a thought

--
Chris

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat, and
replace certain words in my E-Mail address.
Nov 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
9237
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered problems doing this. I wanted to implemented a generic "Helper" class like this: /** * Helper
4
5624
by: Rahul Anand | last post by:
Getting SQL Exception when trying to implement Connection based Trasaction using SQL Helper class. I am using the follwing function to execute my stored procs: -=-=-=- ExecuteScalar(ByVal transaction As SqlTransaction, _ ByVal spName As String, _ ByVal ParamArray parameterValues() As Object)
7
1857
by: Zachary Hilbun | last post by:
The below is some test code to help me learn how to update a dataset. It is supposed to read the value of UserCounter and write it back. The UserCounter is being read correctly as 0, is 1 when written back, but is not being written back to the database. Although I've used a foreach loop there is actually only 1 record in the dataset. I'm going to eventually use transactions but have commented them out for now
5
8294
by: Jason Huang | last post by:
Hi, The SqlParameter myPM =new SqlParameter("@Address", txtAddress.Text) is working for update, but SqlParameter myPM =new SqlParameter ("@Address",SqlDbType.NVarChar,90,txtAddress.Text) is not working. The Address column is DataType NVarChar 90, no problem. Any idea?
4
1548
by: Prabhat | last post by:
How do I lock a particular record that one user has opened for editing? If I use the pessimistic type, can other users view the record (but not edit it) and return a message telling that another person is editing the record, or does this type lock the record such that it is unavailable until the editor releases it? An explanation of pessimistic and optimistic lock types would be really useful, as would some example code.
3
1858
by: Sean Shanny | last post by:
To all, We are running postgresql 7.4.1 on an G5 with dual procs, OSX 10.3.3 server, 8GB mem, attached to a fully configured 3.5TB XRaid box via fibre channel. I think we have run into this issue before but I thought the code was fixed. :-( I have the following SQL:
0
1631
by: Daniel Crespo | last post by:
Hi to all, I'm using adodb for accessing mysql and postgres. My problem relies on the mysql access. Sometimes, when I try to execute a query (using ExecTrans method below), I get this error: 'NoneType' object has no attribute 'cursor'
2
2800
by: timpera2501 | last post by:
I am a newb to OOP programming and I'm only just starting with C#. I've done a TON of reading lately, and I'm currently in the process of modifying some of the function provided by the "TimeTracker Start-up Kit" for my needs. ****** BEGINING OF TIME TRACKER SUMMARY ***** First, in the "TimeTracker Start-Up Kit" they appear to have tried to set it up so that it can be expanded to any number of different datasource types (SQL, Access,...
0
3578
by: george585 | last post by:
Hello! I am new to network programming, and understand just basics. Using some sample code, and having read documentation, I managed to create a simple app in C# and VB.NET. The application is supposed to do the following: monitor ALL INCOMING TCP traffic on the local computer, and save certain parts of it as files - not log files though, but actual files that are sent to the computer as part of http or ftp. Basically if a user browse a page...
0
6898
bartonc
by: bartonc | last post by:
This is a work in progress (current and active). There are some issues to resolve in supporting multiple connection types and I plan to add PySQLite to the mix. The this update is due to my suggesting the accompanying helper functions in a recent post and noticing that the ones posted here are a bit out of date. I'll post those helpers in a separate thread. I've also been working with the following database engines: MySQL 5.x SQLite3 JET...
0
8604
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8538
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9083
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8961
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8800
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7627
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6459
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5819
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2974
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.