Solved

Connection string from code to SQL Server DB

Posted on 2009-07-13
7
288 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

813 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

10 Experts available now in Live!

Get 1:1 Help Now