Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Connection string from code to SQL Server DB

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
doramail05
Asked:
doramail05
  • 4
  • 2
1 Solution
 
ccupoCommented:

            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
 
doramail05Author Commented:
heythere, i got this error:

Keyword not supported: 'provider'.
0
 
RiteshShahCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
doramail05Author Commented:
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
 
ccupoCommented:
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
 
doramail05Author Commented:
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
 
doramail05Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now