Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
connecting to sql server using c#
- Thread starter ace333
- Start date
- Thread starter
- #3
Opps.
C# version
SqlCommand cmd = new SqlCommand("Select CompanyName from Customers",new SqlConnection("Data Source=(Local);Initial Catalog=Northwind;Integrated Security=SSPI"));
DataSet ds =new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds,"Customers");
C# version
SqlCommand cmd = new SqlCommand("Select CompanyName from Customers",new SqlConnection("Data Source=(Local);Initial Catalog=Northwind;Integrated Security=SSPI"));
DataSet ds =new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds,"Customers");
- Thread starter
- #5
This is the code so far but its giving my the following error !!!
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace WebApplication1
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
SqlConnection con;
string sql;
SqlDataAdapter adExisting;
//SqlDataAdapter adPlaces;
//SqlDataAdapter adPlaceTypes;
con = new SqlConnection("Server=slon12d11012;Database=Dacari;Trusted_Connection=Yes");
sql = "select 'RCRF' as DacType, R.Id, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from dbo.ITRR_RCRFDetailComments R, RCRFDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.AmendmentInstructions *=D.AmendmentInstructions";
sql+="union";
sql+="select 'RCRF' as DacType, R.Id, R.ApproverComments as Comments,'RejectedComment' as TypeOfComment,D.ExternalID as RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_RCRFApproverComments R, RCRFDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.ApproverComments *=D.ApproverComments";
sql+="union";
sql+="select 'DAC' as DacType, R.Id, R.Comments, 'Comment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime from ITRR_DacDetailComments R, DacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.Comments *=D.Comments";
sql+="union";
sql+="select 'DAC' as DacType, R.Id, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_DacDetailRejectedComments R, DacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.RejectedComments *=D.RejectedComments";
sql+="union";
sql+="select 'CAP' as DacType, R.CapId, R.Comments, 'Comment' as TypeOfComment, D.CurrentRiskId,D.AmendedByUserId as UserId, D.AmendedDateTime from dbo.ITRR_CAPDetailComments R, CapDetail D";
sql+="where R.CapId *= D.CapId";
sql+="and R.Comments *=D.Comments";
sql+="union";
sql+="select 'CAP' as DacType, R.CapId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.CurrentRiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_CapDetailRejectedComments R, CapDetail D";
sql+="where R.CapId *= D.CapId";
sql+="and R.RejectedComments *=D.RejectedComments";
sql+="union";
sql+="select 'MODAC' as DacType, R.Id, R.MiddleOfficeComments as Comments, 'MiddleOfficeComment' as TypeOfComment, D.RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_MODacDetailComments R, MODacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.MiddleOfficeComments *=D.MiddleOfficeComments";
sql+="union";
sql+="select 'MODAC' as DacType, R.Id, R.RequestorComments as Comments,'RequestorComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_RequestorComments R, MODacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.RequestorComments *= D.RequestorComments";
adExisting = new SqlDataAdapter(sql,con);
con.Open();
DataSet ds = new DataSet();
try
{
adExisting.Fill(ds,"Existing");
}
catch
{
throw;
}
finally
{
con.Close();
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
The code generates the following error
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 76:
Line 77: adExisting = new SqlDataAdapter(sql,con);
Line 78: con.Open();
Line 79: DataSet ds = new DataSet();
Line 80: try
Source File: c:\inetpub\ Line: 78
Stack Trace:
[SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Any help would be appreciated
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace WebApplication1
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
SqlConnection con;
string sql;
SqlDataAdapter adExisting;
//SqlDataAdapter adPlaces;
//SqlDataAdapter adPlaceTypes;
con = new SqlConnection("Server=slon12d11012;Database=Dacari;Trusted_Connection=Yes");
sql = "select 'RCRF' as DacType, R.Id, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from dbo.ITRR_RCRFDetailComments R, RCRFDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.AmendmentInstructions *=D.AmendmentInstructions";
sql+="union";
sql+="select 'RCRF' as DacType, R.Id, R.ApproverComments as Comments,'RejectedComment' as TypeOfComment,D.ExternalID as RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_RCRFApproverComments R, RCRFDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.ApproverComments *=D.ApproverComments";
sql+="union";
sql+="select 'DAC' as DacType, R.Id, R.Comments, 'Comment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime from ITRR_DacDetailComments R, DacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.Comments *=D.Comments";
sql+="union";
sql+="select 'DAC' as DacType, R.Id, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_DacDetailRejectedComments R, DacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.RejectedComments *=D.RejectedComments";
sql+="union";
sql+="select 'CAP' as DacType, R.CapId, R.Comments, 'Comment' as TypeOfComment, D.CurrentRiskId,D.AmendedByUserId as UserId, D.AmendedDateTime from dbo.ITRR_CAPDetailComments R, CapDetail D";
sql+="where R.CapId *= D.CapId";
sql+="and R.Comments *=D.Comments";
sql+="union";
sql+="select 'CAP' as DacType, R.CapId, R.RejectedComments as Comments,'RejectedComment' as TypeOfComment,D.CurrentRiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_CapDetailRejectedComments R, CapDetail D";
sql+="where R.CapId *= D.CapId";
sql+="and R.RejectedComments *=D.RejectedComments";
sql+="union";
sql+="select 'MODAC' as DacType, R.Id, R.MiddleOfficeComments as Comments, 'MiddleOfficeComment' as TypeOfComment, D.RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_MODacDetailComments R, MODacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.MiddleOfficeComments *=D.MiddleOfficeComments";
sql+="union";
sql+="select 'MODAC' as DacType, R.Id, R.RequestorComments as Comments,'RequestorComment' as TypeOfComment,D.RiskId, D.AmendedByUserId as UserId, D.AmendedDateTime";
sql+="from ITRR_RequestorComments R, MODacDetail D";
sql+="where R.Id *= D.Id";
sql+="and R.RequestorComments *= D.RequestorComments";
adExisting = new SqlDataAdapter(sql,con);
con.Open();
DataSet ds = new DataSet();
try
{
adExisting.Fill(ds,"Existing");
}
catch
{
throw;
}
finally
{
con.Close();
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
The code generates the following error
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 76:
Line 77: adExisting = new SqlDataAdapter(sql,con);
Line 78: con.Open();
Line 79: DataSet ds = new DataSet();
Line 80: try
Source File: c:\inetpub\ Line: 78
Stack Trace:
[SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Any help would be appreciated
Your connection string is wrong, or the SQL Server doesn't allow windows integrated security.
Use the techniques here to get a correct connection string.
Use the techniques here to get a correct connection string.
- Thread starter
- #7
this is the new connection string
con = new SqlConnection("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=nnichol1;Initial Catalog=Dacari;Data Source=slon12d11012");
and this is the error now
Keyword not supported: 'provider'.
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.ArgumentException: Keyword not supported: 'provider'.
Source Error:
Line 22: //SqlDataAdapter adPlaceTypes;
Line 23:
Line 24: con = new SqlConnection("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=nnichol1;Initial Catalog=Dacari;Data Source=slon12d11012");
Line 25:
Line 26: sql = "select 'RCRF' as DacType, R.Id, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime";
Source File: c:\inetpub\ Line: 24
Stack Trace:
[ArgumentException: Keyword not supported: 'provider'.]
System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain) +1133
System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl) +114
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +13
System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString) +96
System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +11
System.Data.SqlClient.SqlConnection..ctor(String connectionString) +158
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
con = new SqlConnection("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=nnichol1;Initial Catalog=Dacari;Data Source=slon12d11012");
and this is the error now
Keyword not supported: 'provider'.
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.ArgumentException: Keyword not supported: 'provider'.
Source Error:
Line 22: //SqlDataAdapter adPlaceTypes;
Line 23:
Line 24: con = new SqlConnection("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=nnichol1;Initial Catalog=Dacari;Data Source=slon12d11012");
Line 25:
Line 26: sql = "select 'RCRF' as DacType, R.Id, R.AmendmentInstructions as Comments, 'Comment' as TypeOfComment, D.ExternalID as RiskId,D.AmendedByUserId as UserId, D.AmendedDateTime";
Source File: c:\inetpub\ Line: 24
Stack Trace:
[ArgumentException: Keyword not supported: 'provider'.]
System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain) +1133
System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl) +114
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +13
System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString) +96
System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +11
System.Data.SqlClient.SqlConnection..ctor(String connectionString) +158
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
- Thread starter
- #9
did that
the error is now
Login failed for user 'nnichol1'.
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: Login failed for user 'nnichol1'.
Source Error:
Line 70:
Line 71: adExisting = new SqlDataAdapter(sql,con);
Line 72: con.Open();
Line 73: DataSet ds = new DataSet();
Line 74: try
Source File: c:\inetpub\ Line: 72
Stack Trace:
[SqlException: Login failed for user 'nnichol1'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
string is
con = new SqlConnection("Persist Security Info=False;User ID=nnichol1;Initial Catalog=Dacari;Data Source=slon12d11012");
this is supposed to be the easy part of my project and i cant even do that :-(
the error is now
Login failed for user 'nnichol1'.
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: Login failed for user 'nnichol1'.
Source Error:
Line 70:
Line 71: adExisting = new SqlDataAdapter(sql,con);
Line 72: con.Open();
Line 73: DataSet ds = new DataSet();
Line 74: try
Source File: c:\inetpub\ Line: 72
Stack Trace:
[SqlException: Login failed for user 'nnichol1'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
string is
con = new SqlConnection("Persist Security Info=False;User ID=nnichol1;Initial Catalog=Dacari;Data Source=slon12d11012");
this is supposed to be the easy part of my project and i cant even do that :-(
- Thread starter
- #11
- Thread starter
- #13
- Thread starter
- #15
con = new SqlConnection("Initial Catalog=Dacari;Data Source=slon12d11012;Integrated Security=SSPI");
gave the following error
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 79:
Line 80: adExisting = new SqlDataAdapter(sql,con);
Line 81: con.Open();
Line 82: DataSet ds = new DataSet();
Line 83: try
Source File: c:\inetpub\ Line: 81
Stack Trace:
[SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
gave the following error
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 79:
Line 80: adExisting = new SqlDataAdapter(sql,con);
Line 81: con.Open();
Line 82: DataSet ds = new DataSet();
Line 83: try
Source File: c:\inetpub\ Line: 81
Stack Trace:
[SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
WebApplication1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\ System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
- Thread starter
- #17
Sorry, I was confused at first. I thought nnichol1 was a SQL Server User. It's actually your network user and your network user has access to SQL Server and the DB.
You have 2 options.
Option 1)
Assuming your SQL Server is in mixed mode(Allows windows authentication and SQL Server authentication), You can add a user to SQL Server and then give them access to the Database then you can use this connnection string
"User ID=SQLUserID;password=SQLPassword;Initial Catalog=Dacari;Data Source=slon12d11012"
Option 2)
Using Integrated secuity with ASP.Net
I will let some one else explain it
Initial Catalog=Dacari;Data Source=slon12d11012;Integrated Security=SSPI
You have 2 options.
Option 1)
Assuming your SQL Server is in mixed mode(Allows windows authentication and SQL Server authentication), You can add a user to SQL Server and then give them access to the Database then you can use this connnection string
"User ID=SQLUserID;password=SQLPassword;Initial Catalog=Dacari;Data Source=slon12d11012"
Option 2)
Using Integrated secuity with ASP.Net
I will let some one else explain it
Initial Catalog=Dacari;Data Source=slon12d11012;Integrated Security=SSPI
- Thread starter
- #19
- Thread starter
- #20
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question