Solved

Connection string from code to SQL Server DB

Posted on 2009-07-13
7
283 Views
Last Modified: 2012-05-07
I have to use connection string to connect to SQL Server in my code, but the connectionstrings seem to have problems..

below are my login credentials for SSMS.

Windows Authentication
username :  IT\myusername
password : blank

SQL Server Authentication
username : sa
password : 123

string sourceconnstringextract = "Data Source=IT01\\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True;initial catalog=RetailDB; user id=sa; password=123";
 

the error = 'Cannot open database "RetailDB" requested by the login. The login failed.

Login failed for user 'IT01\myusername'.'
 

then i tried this..
 

string sourceconnstringextract = "Data Source=IT01\\SQLEXPRESS;AttachDbFilename="C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\RetailDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
 

got this error = 

Keyword not supported: 'c:\program files\microsoft sql server\mssql.1\mssql\data\retaildb.mdf;integrated security'.

Open in new window

0
Comment
Question by:doramail05
  • 4
  • 2
7 Comments
 
LVL 1

Accepted Solution

by:
ccupo earned 500 total points
ID: 24837786

            ConnectionString :=

                'Provider=SQLOLEDB.1;' +

                'Persist Security Info=False;' +

                'User ID=' + SQL_login + ';' +

                'Password=' + SQL_pass + ';' +

                'Initial Catalog=' + SQL_Database_Name + ';' +

                'Data Source=' + SQL_Server_Name + ';' +

                'General Timeout=10;Command Timeout=10;Login Timeout=10';

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 24837809
heythere, i got this error:

Keyword not supported: 'provider'.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24837817
rather than just connection string, could you please provide us with full code which is calling connection string? It may help use more to troubleshoot.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:doramail05
ID: 24837852
string sourceconnstringextract = "Persist Security Info=False;User ID=sa; Password=123; Initial Catalog=RetailDB; Data Source=IT01\\SQLEXPRESS;";

hey again ritesh,
i filtered out some connection properties like above for testing , it does not have any error at all,
and when i ran storeprocedure (which also fine), everything seems fine. But no record updated in table

                 using (SqlConnection sqlsourceconnExtract = new SqlConnection(sourceconnstringextract))
                  {
                      try
                      {
                          SqlCommand cmdSQL = new SqlCommand("UpdateRetailDBSO2", sqlsourceconnExtract);
                          cmdSQL.CommandType = CommandType.StoredProcedure;
                          cmdSQL.Connection = sqlsourceconnExtract;

                          SqlParameter parameterdesc = new SqlParameter("@sale_id", SqlDbType.NVarChar, 50);
                          parameterdesc.Direction = ParameterDirection.Input;
                          cmdSQL.Parameters.AddWithValue("@sale_id", 1);

                          try
                          {
                              sqlsourceconnExtract.Open();
                              cmdSQL.ExecuteNonQuery();
                          }
                          catch (Exception ex)
                          {
                              lstViewLog.Items.Add(ex.Message.ToString());
                          }


                      }
                      catch (Exception ex)
                      {
                          lstViewLog.Items.Add(ex.Message.ToString());
                      }
                      finally
                      {
                           lstViewLog.Items.Add("completed");
                          sqlsourceconnExtract.Close();
                      }

                  }
0
 
LVL 1

Expert Comment

by:ccupo
ID: 24837875
This is correct:
sourceconnstringextract = "Data Source=IT01\\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True;initial catalog=RetailDB; user id=sa; password=123";

Go to SSMS > SQL Server Properties  > Security.
SQL Server and Windows Authentication mode must by enabled.
0
 
LVL 1

Author Comment

by:doramail05
ID: 24837914
There are only
1) Server authentication , i selected both
2) Login Auditing, Failed logins only
3) Server proxy account, none of them selected
4) Options , none of them selected
0
 
LVL 1

Author Comment

by:doramail05
ID: 24847489
i modified the connection string abit and it worked
the first column was nearly matched,

string sourceconnstringextract = "Persist Security Info=" + checkboxsourceextract + ";User ID= " + txtSourceUsername.Text + "; Password= " + txtSourcePassword.Text + "; Initial Catalog=RetailDB; Data Source=" + txtSourceDS.Text + ";";

                            using (SqlConnection sqlsourceconnextract = new SqlConnection(sourceconnstringextract))
                            {

                                try
                                {
                                    SqlCommand sqlcmd_insert_into_oneuSO = sqlsourceconnextract.CreateCommand();
                                    sqlcmd_insert_into_oneuSO.CommandText = "USE " + txtSourceDatabase.Text + "; INSERT INTO dbo.ONEU_Sales_Order (sale_id, sale_order_no, sale_order_invoice, sale_datetime, sale_status, sub_total, discount_percent, discount_amount, tax_percent, tax_amount, service_charge_percent, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) " +
                                " VALUES ('" + generated_saleid + "', '" + dsdual.Tables[0].Rows[i]["sosoorderno"].ToString() + "', '" + dsdual.Tables[0].Rows[i]["so_invoice_no"].ToString() + "', '" + Convert.ToDateTime(dsdual.Tables[0].Rows[i]["sol_date_stamp"])
                                + "', '" + dsdual.Tables[0].Rows[i]["so_order_status"] + "', " + calculated_sub_total + ", " + dsdual.Tables[0].Rows[i]["sol_line_amount"].ToString() + ", 0, 0, 0, 0, 0, 0, '', '', '', 0)";

                                    //sqlcmd_insert_into_1uSO.CommandText = "UPDATE ONEU_Sales_Order SET sale_id='" + generated_saleid + "',sale_order_no='" + dsgetselectedrow.Tables[0].Rows[j]["so_order_no"].ToString() + "', sale_order_invoice='" + dsgetselectedrow.Tables[0].Rows[j]["so_invoice_no"].ToString() +
                                    //    "', sale_date=" + Convert.ToDateTime(dsgetselectedrow.Tables[0].Rows[0]["sol_date_stamp"]) + ", sale_status='" + dsgetselectedrow.Tables[0].Rows[j]["so_order_status"] + "', sub_total='" + calculated_sub_total + "', discount_amount='" + dsgetselectedrow.Tables[0].Rows[j]["sol_line_amount"].ToString() + "'";

                                    sqlsourceconnextract.Open();
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now