Solved

Connection string from code to SQL Server DB

Posted on 2009-07-13
7
286 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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

17 Experts available now in Live!

Get 1:1 Help Now