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
Newsbeitrag 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
message 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/d...ntSqlConnectionClassConnectionStringTopic.asp
Does this help?
Daniel Walzenbach
Newsbeitrag 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
