Solved

Connection string from code to SQL Server DB

Posted on 2009-07-13
7
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

617 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