Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Help -- SQLConnection Problem

Reply
Thread Tools

Help -- SQLConnection Problem

 
 
Michael D Murphy
Guest
Posts: n/a
 
      09-03-2005
Hi,
I continually have problems with SQLConnections. Once I fix the one that I
am having problems with I move on. I would rather have more confidence. I
must be not understanding something or another. If I drag a SQLConnection to
the form and configure it and then drag a SQLDataAdapter to the form and
configure that, then right click on the SQLAdapter and click on Preview
Data, I always get the data. But if I use that exact SQLConnection object or
copy all the properties of that SQLConnection object to a new SQLConnection
object, and try to connect I get errors. Why would it work one way and not
the other. If you could point me to a SQLConnection primer or other tutorial
that will finally get me past these problems, I would appreciate it.
Thanks for your time,
Michael
954-452-1047


 
Reply With Quote
 
 
 
 
Jordan
Guest
Posts: n/a
 
      09-04-2005
I see no one has responded for over 6 hours - so here' my 2 cents-worth:

Don't use the SQLConnection and SQLDataAdapter objects from the toolbox.
Seriously. Instead, code it by hand. Nobody I've ever seen or heard of
actually uses those objects from the toolbox for any non trivial ASP.NET
application development.

Why?

1 -- Because of the exact sort of problem you are having - and many others.
The problem is that you don't really know what's going on behind the scenes
and why things seem to break so easily with these objects. The only place I
think they're used is in training courses and intro-level books where
they're trying to show you how "easy" connectivity is. The problem is that
it is really that easy - but only if you hand-code it all (IMHO). It's far
more difficult if you use these objects from the toolbox. The only time they
really shine is during a tutorial or class where you are supposed to follow
some exact steps. You follow them - and Bingo! it all works - very
impressive! But now you need to do some real work -and you're screwed
because they hide so much from you.

2 -- Economics: The amount of time you spend screwing around with these
"training wheels" could have been spent learning how to hand-code things
(which really is not a difficult thing to do; just a few lines of code and
you're in business). Debugging a few lines of code is far easier than trying
to figure out why an extra layer of indirection is - well, indirecting you.

I'm not being cynical here - just realistic. The bottom line is that you're
going to spend a certain amount of time getting your connectivity going -
either by learning how to write code to do it or by screwing around with
these wrappers. Your choice. I suspect that nobody is helping you with these
wrappers because they don't use or understand them... just a hunch...

-HTH


"Michael D Murphy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I continually have problems with SQLConnections. Once I fix the one that I
> am having problems with I move on. I would rather have more confidence. I
> must be not understanding something or another. If I drag a SQLConnection
> to the form and configure it and then drag a SQLDataAdapter to the form
> and configure that, then right click on the SQLAdapter and click on
> Preview Data, I always get the data. But if I use that exact SQLConnection
> object or copy all the properties of that SQLConnection object to a new
> SQLConnection object, and try to connect I get errors. Why would it work
> one way and not the other. If you could point me to a SQLConnection primer
> or other tutorial that will finally get me past these problems, I would
> appreciate it.
> Thanks for your time,
> Michael
> 954-452-1047
>



 
Reply With Quote
 
 
 
 
Jordan
Guest
Posts: n/a
 
      09-04-2005
A primer to help you really understand what's going on with connectivity:

"Microsoft ADO.NET" by David Sceppa - Microsoft Press

It's a thick book - but totally understandable to the beginner - and you
don't need to read the whole thing to get going.



"Michael D Murphy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I continually have problems with SQLConnections. Once I fix the one that I
> am having problems with I move on. I would rather have more confidence. I
> must be not understanding something or another. If I drag a SQLConnection
> to the form and configure it and then drag a SQLDataAdapter to the form
> and configure that, then right click on the SQLAdapter and click on
> Preview Data, I always get the data. But if I use that exact SQLConnection
> object or copy all the properties of that SQLConnection object to a new
> SQLConnection object, and try to connect I get errors. Why would it work
> one way and not the other. If you could point me to a SQLConnection primer
> or other tutorial that will finally get me past these problems, I would
> appreciate it.
> Thanks for your time,
> Michael
> 954-452-1047
>



 
Reply With Quote
 
Daniel Walzenbach
Guest
Posts: n/a
 
      09-04-2005
Michael,



This will help you setting up your connection string.

http://www.connectionstrings.com/



What do you mean by "copy all the properties of that SQLConnection object to
a new SQLConnection"? Do you really mean all? Do you now have two
SQLConnection Objects having the same name? If you could provide us with the
error message you get it would me much easier to find a solution to your
problem.

Basically I agree to what Jordan said. What I usually do is dragging a
SQLConnection of my form, copy the connection string, delete the
SQLConnection again and then create the SQLConnection in code behind using
the connection string I copied (I always forget about
theconnectionstrings.com).



You should also have a look at the msdn reference which comes with some nice
examples:

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





Does this help?



Daniel Walzenbach





"Michael D Murphy" <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> Hi,
> I continually have problems with SQLConnections. Once I fix the one that I
> am having problems with I move on. I would rather have more confidence. I
> must be not understanding something or another. If I drag a SQLConnection
> to the form and configure it and then drag a SQLDataAdapter to the form
> and configure that, then right click on the SQLAdapter and click on
> Preview Data, I always get the data. But if I use that exact SQLConnection
> object or copy all the properties of that SQLConnection object to a new
> SQLConnection object, and try to connect I get errors. Why would it work
> one way and not the other. If you could point me to a SQLConnection primer
> or other tutorial that will finally get me past these problems, I would
> appreciate it.
> Thanks for your time,
> Michael
> 954-452-1047
>



 
Reply With Quote
 
Michael D Murphy
Guest
Posts: n/a
 
      09-04-2005
Thanks so much for your replies!
I have done quite a lot of work in ASP.Net and am quite familiar with it.
But for some reason, I just can't understand why some connections are so
simply implemented and others are so unmanageable. I normally code the
SQLConnection but when I run into connection problems I revert back to the
drag and drop the SQLConnection Object from the toolbox onto the page. I
then go through the process of creating a SQLDataAdapter and configure it
until I can actually see the data in the 'Preview Data' window. At that
point I figure the SQLConnection object is configured correctly so now I go
into the code and enter something like this:

Dim MyConnectionString as string
MyConnectionString =
Dim MySQLConnection as New SQLConnection(MyConnectionString)
MySQLConnection.Open()

And when I rebuild the project and step through the code it bombs on the
Open!
The only thing I could check was Server,GetLastError.Message---which is set
to Object set to Nothing.

How do I track down the exact problem here in both situations:
Debugging on my local machine with data on that machine
Debugging if the database is on a remote machine and I have no way to access
the program and data outside the host's firewall--some kind of print
statements??.

Thanks again for your time.
Michael


"Daniel Walzenbach" <(E-Mail Removed)> wrote in message
news:OYo36$(E-Mail Removed)...
> Michael,
>
>
>
> This will help you setting up your connection string.
>
> http://www.connectionstrings.com/
>
>
>
> What do you mean by "copy all the properties of that SQLConnection object
> to a new SQLConnection"? Do you really mean all? Do you now have two
> SQLConnection Objects having the same name? If you could provide us with
> the error message you get it would me much easier to find a solution to
> your problem.
>
> Basically I agree to what Jordan said. What I usually do is dragging a
> SQLConnection of my form, copy the connection string, delete the
> SQLConnection again and then create the SQLConnection in code behind using
> the connection string I copied (I always forget about
> theconnectionstrings.com).
>
>
>
> You should also have a look at the msdn reference which comes with some
> nice examples:
>
> http://msdn.microsoft.com/library/de...tringTopic.asp
>
>
>
>
>
> Does this help?
>
>
>
> Daniel Walzenbach
>
>
>
>
>
> "Michael D Murphy" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:(E-Mail Removed)...
>> Hi,
>> I continually have problems with SQLConnections. Once I fix the one that
>> I am having problems with I move on. I would rather have more confidence.
>> I must be not understanding something or another. If I drag a
>> SQLConnection to the form and configure it and then drag a SQLDataAdapter
>> to the form and configure that, then right click on the SQLAdapter and
>> click on Preview Data, I always get the data. But if I use that exact
>> SQLConnection object or copy all the properties of that SQLConnection
>> object to a new SQLConnection object, and try to connect I get errors.
>> Why would it work one way and not the other. If you could point me to a
>> SQLConnection primer or other tutorial that will finally get me past
>> these problems, I would appreciate it.
>> Thanks for your time,
>> Michael
>> 954-452-1047
>>

>
>



 
Reply With Quote
 
Michael D Murphy
Guest
Posts: n/a
 
      09-04-2005
Hi,
I just went and tried to do the same thing with Northwind and get the
following message:
Cannot open database requested in login 'Northwind'. Login fails. Login
failed for user 'MDMDELL1\ASPNET'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database
requested in login 'Northwind'. Login fails. Login failed for user
'MDMDELL1\ASPNET'.

Source Error:


Line 125: Session.Add("mySqlConString", mySqlConString)
Line 126: pub_ABCEF_SQLCon = New SqlConnection(mySqlConString)
Line 127: pub_ABCEF_SQLCon.Open()
Line 128: Dim MySqlCommand As SqlCommand = New SqlCommand
Line 129: With MySqlCommand


But in the data adapter, it obviously logs in just fine because I can
preview the data.
Any thoughts?
Thanks,
Michael


 
Reply With Quote
 
Michael D Murphy
Guest
Posts: n/a
 
      09-04-2005
I corrected the problem with the SQLConnection.Open() -- I had to add the
user ASPNET to the database. And that is another thing that bothers me--why
didn't that error occur when I was previewing the data in the data adapter??
My outstanding problem at this point is how do I track down errors
(connection errors in this case) that occur on a page when the only tool I
have is FTPing my files to the remote server.

Thanks,
Michael


"Daniel Walzenbach" <(E-Mail Removed)> wrote in message
news:OYo36$(E-Mail Removed)...
> Michael,
>
>
>
> This will help you setting up your connection string.
>
> http://www.connectionstrings.com/
>
>
>
> What do you mean by "copy all the properties of that SQLConnection object
> to a new SQLConnection"? Do you really mean all? Do you now have two
> SQLConnection Objects having the same name? If you could provide us with
> the error message you get it would me much easier to find a solution to
> your problem.
>
> Basically I agree to what Jordan said. What I usually do is dragging a
> SQLConnection of my form, copy the connection string, delete the
> SQLConnection again and then create the SQLConnection in code behind using
> the connection string I copied (I always forget about
> theconnectionstrings.com).
>
>
>
> You should also have a look at the msdn reference which comes with some
> nice examples:
>
> http://msdn.microsoft.com/library/de...tringTopic.asp
>
>
>
>
>
> Does this help?
>
>
>
> Daniel Walzenbach
>
>
>
>
>
> "Michael D Murphy" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:(E-Mail Removed)...
>> Hi,
>> I continually have problems with SQLConnections. Once I fix the one that
>> I am having problems with I move on. I would rather have more confidence.
>> I must be not understanding something or another. If I drag a
>> SQLConnection to the form and configure it and then drag a SQLDataAdapter
>> to the form and configure that, then right click on the SQLAdapter and
>> click on Preview Data, I always get the data. But if I use that exact
>> SQLConnection object or copy all the properties of that SQLConnection
>> object to a new SQLConnection object, and try to connect I get errors.
>> Why would it work one way and not the other. If you could point me to a
>> SQLConnection primer or other tutorial that will finally get me past
>> these problems, I would appreciate it.
>> Thanks for your time,
>> Michael
>> 954-452-1047
>>

>
>



 
Reply With Quote
 
Jordan
Guest
Posts: n/a
 
      09-04-2005
Has the user - 'MDMDELL1\ASPNET' - been added to your SQL Server as a valid
login to both (1) the server and (2) the database? If not, give that a shot.
To verify, log in via QueryAnalyzer using that specific login.

Perhaps the SQLConnection login from the toolbox is attempting to connect AS
some other user (perhaps the currently logged in [to Windows] user - you -
and not as the MDMDELL1\ASPNet user.

-HTH


"Michael D Murphy" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi,
> I just went and tried to do the same thing with Northwind and get the
> following message:
> Cannot open database requested in login 'Northwind'. Login fails. Login
> failed for user 'MDMDELL1\ASPNET'.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
>
> Exception Details: System.Data.SqlClient.SqlException: Cannot open
> database requested in login 'Northwind'. Login fails. Login failed for
> user 'MDMDELL1\ASPNET'.
>
> Source Error:
>
>
> Line 125: Session.Add("mySqlConString", mySqlConString)
> Line 126: pub_ABCEF_SQLCon = New SqlConnection(mySqlConString)
> Line 127: pub_ABCEF_SQLCon.Open()
> Line 128: Dim MySqlCommand As SqlCommand = New SqlCommand
> Line 129: With MySqlCommand
>
>
> But in the data adapter, it obviously logs in just fine because I can
> preview the data.
> Any thoughts?
> Thanks,
> Michael
>



 
Reply With Quote
 
Jordan
Guest
Posts: n/a
 
      09-04-2005
<<how do I track down errors (connection errors in this case) that occur on
a page >>

The exception object contains a .StackTrace property. This will tell you the
specific line of code that choked. This, along with the .Message property of
the exception object will tell you what went wrong and on which line of code
the problem occurred.

Note that in order to get the correct stack trace information, you'll have
to be sure to copy the .pdb file along with the .dll file. The .pdb file
contains the debugging information that will give you the stack trace/line
number info. Whenever you compile a new dll, you'll get a new .pdb file.
Always copy the current .pdb file with the .dll.

Also, remember that exceptions are of different types. So, the SQLException
object will have different properties available to help you determine what
went wrong (e.g., name of the stored procedrue that choked, and the line
number at which it choked). Your central error logger should differentiate
between the types of Exceptions beign logged so it can log
exception-type-specific property values.

You can write a generic exception logging routine that centralizes this
work - then call it in the catch blocks of your application.

-HTH



"Michael D Murphy" <(E-Mail Removed)> wrote in message
news:ObT$(E-Mail Removed)...
>I corrected the problem with the SQLConnection.Open() -- I had to add the
>user ASPNET to the database. And that is another thing that bothers me--why
>didn't that error occur when I was previewing the data in the data
>adapter??
> My outstanding problem at this point is how do I track down errors
> (connection errors in this case) that occur on a page when the only tool I
> have is FTPing my files to the remote server.
>
> Thanks,
> Michael
>
>
> "Daniel Walzenbach" <(E-Mail Removed)> wrote in message
> news:OYo36$(E-Mail Removed)...
>> Michael,
>>
>>
>>
>> This will help you setting up your connection string.
>>
>> http://www.connectionstrings.com/
>>
>>
>>
>> What do you mean by "copy all the properties of that SQLConnection object
>> to a new SQLConnection"? Do you really mean all? Do you now have two
>> SQLConnection Objects having the same name? If you could provide us with
>> the error message you get it would me much easier to find a solution to
>> your problem.
>>
>> Basically I agree to what Jordan said. What I usually do is dragging a
>> SQLConnection of my form, copy the connection string, delete the
>> SQLConnection again and then create the SQLConnection in code behind
>> using the connection string I copied (I always forget about
>> theconnectionstrings.com).
>>
>>
>>
>> You should also have a look at the msdn reference which comes with some
>> nice examples:
>>
>> http://msdn.microsoft.com/library/de...tringTopic.asp
>>
>>
>>
>>
>>
>> Does this help?
>>
>>
>>
>> Daniel Walzenbach
>>
>>
>>
>>
>>
>> "Michael D Murphy" <(E-Mail Removed)> schrieb im
>> Newsbeitrag news:(E-Mail Removed)...
>>> Hi,
>>> I continually have problems with SQLConnections. Once I fix the one that
>>> I am having problems with I move on. I would rather have more
>>> confidence. I must be not understanding something or another. If I drag
>>> a SQLConnection to the form and configure it and then drag a
>>> SQLDataAdapter to the form and configure that, then right click on the
>>> SQLAdapter and click on Preview Data, I always get the data. But if I
>>> use that exact SQLConnection object or copy all the properties of that
>>> SQLConnection object to a new SQLConnection object, and try to connect I
>>> get errors. Why would it work one way and not the other. If you could
>>> point me to a SQLConnection primer or other tutorial that will finally
>>> get me past these problems, I would appreciate it.
>>> Thanks for your time,
>>> Michael
>>> 954-452-1047
>>>

>>
>>

>
>



 
Reply With Quote
 
Daniel Walzenbach
Guest
Posts: n/a
 
      09-04-2005
Michael,



to make things more clear to you I'd suggest the following:



1.) open query analyser and change to the database you are interested in.
Execute the following statement:



select DB_ID();



remember the result.



2.) open SQL Server Profiler and create a new trace (File -> New -> Trace)

3.) open the Tab "Events" and remove everything except

Stored Procedures, PRC: Completed,

TSQL, SQL:BatchCompleted



4.) open the Tab "Filters" and set DatabaseID equals (the Number you
selected in step 1)



5.) Preview the DataAdapter and see in Profiler which account is used





What you now will see is the following:

If you use the preview function your local user will be used.

If you step through the code you'll see that the user defined in the
connection string will be used...







This is a little example you might use:



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

' ** SqlConnection

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



' create the connection

Dim mySQLConnection As New System.Data.SqlClient.SqlConnection



Try

' assign the ConnectionString

mySQLConnection.ConnectionString = "Initial Catalog=Northwind;Data
Source=localhost;User ID=NorthwindUser;Password=xxxxxx;Packet Size=4096;"



' open the connection

mySQLConnection.Open()

Catch ex As Exception

' write errors to the page

Response.Write(ex.Message)



End Try





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

' ** SqlCommand

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



' create a SQLCommand

Dim mySQLCommand As New System.Data.SqlClient.SqlCommand



' assign the Connection to the Command

mySQLCommand.Connection = mySQLConnection



' assign the SelectCommand

mySQLCommand.CommandText = "SELECT [CustomerID], [CompanyName] FROM
[Northwind].[dbo].[Customers]"



' assign the CommandType

mySQLCommand.CommandType = CommandType.Text





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

' ** SqlDataAdapter

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



' create a SqlDataAdapter

Dim mySQLDataAdapter As New System.Data.SqlClient.SqlDataAdapter



' assign the SqlCommand to the SqlDataAdapter

mySQLDataAdapter.SelectCommand = mySQLCommand



' assign TableMapping

mySQLDataAdapter.TableMappings.Add("Table", "Customers")





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

' ** DataSet

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



' create a DataSet

Dim myDataSet As New System.Data.DataSet



Try

' fill the DataSet

mySQLDataAdapter.Fill(myDataSet)



Catch ex As Exception

' write errors to the page

Response.Write(ex.Message)



End Try




Regards

Daniel Walzenbach


"Michael D Murphy" <(E-Mail Removed)> schrieb im Newsbeitrag
news:ObT$(E-Mail Removed)...
>I corrected the problem with the SQLConnection.Open() -- I had to add the
>user ASPNET to the database. And that is another thing that bothers me--why
>didn't that error occur when I was previewing the data in the data
>adapter??
> My outstanding problem at this point is how do I track down errors
> (connection errors in this case) that occur on a page when the only tool I
> have is FTPing my files to the remote server.
>
> Thanks,
> Michael
>
>
> "Daniel Walzenbach" <(E-Mail Removed)> wrote in message
> news:OYo36$(E-Mail Removed)...
>> Michael,
>>
>>
>>
>> This will help you setting up your connection string.
>>
>> http://www.connectionstrings.com/
>>
>>
>>
>> What do you mean by "copy all the properties of that SQLConnection object
>> to a new SQLConnection"? Do you really mean all? Do you now have two
>> SQLConnection Objects having the same name? If you could provide us with
>> the error message you get it would me much easier to find a solution to
>> your problem.
>>
>> Basically I agree to what Jordan said. What I usually do is dragging a
>> SQLConnection of my form, copy the connection string, delete the
>> SQLConnection again and then create the SQLConnection in code behind
>> using the connection string I copied (I always forget about
>> theconnectionstrings.com).
>>
>>
>>
>> You should also have a look at the msdn reference which comes with some
>> nice examples:
>>
>> http://msdn.microsoft.com/library/de...tringTopic.asp
>>
>>
>>
>>
>>
>> Does this help?
>>
>>
>>
>> Daniel Walzenbach
>>
>>
>>
>>
>>
>> "Michael D Murphy" <(E-Mail Removed)> schrieb im
>> Newsbeitrag news:(E-Mail Removed)...
>>> Hi,
>>> I continually have problems with SQLConnections. Once I fix the one that
>>> I am having problems with I move on. I would rather have more
>>> confidence. I must be not understanding something or another. If I drag
>>> a SQLConnection to the form and configure it and then drag a
>>> SQLDataAdapter to the form and configure that, then right click on the
>>> SQLAdapter and click on Preview Data, I always get the data. But if I
>>> use that exact SQLConnection object or copy all the properties of that
>>> SQLConnection object to a new SQLConnection object, and try to connect I
>>> get errors. Why would it work one way and not the other. If you could
>>> point me to a SQLConnection primer or other tutorial that will finally
>>> get me past these problems, I would appreciate it.
>>> Thanks for your time,
>>> Michael
>>> 954-452-1047
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SqlConnection problem - MySQL Alan T ASP .Net 5 07-20-2010 12:37 PM
SQLDataAdapter lost (along with SQLConnection) - HELP!!! Randy Smith ASP .Net 3 05-05-2005 12:10 AM
SQLConnection Help Claudiu Tescu ASP .Net 1 03-31-2005 09:37 PM
SQLConnection event catch, please help Amar ASP .Net 3 06-11-2004 07:47 AM
Re: SqlConnection problem Rick Spiewak ASP .Net 0 08-27-2003 03:33 AM



Advertisments