Solved

how to make SQL server Authentication login function.......

Posted on 2008-10-02
11
2,043 Views
Last Modified: 2008-10-05
I am making a simple login form, where i can login as SQL Server Authenticatin,  i have 2 textfields username and password for SA = user and password.....and i use a connectionstring.

But when i am steping true the code i get error message saying an object refrence is not set as an object....so i am stucked, can somone guide with this bug...?
public void SQLServerLogin(string connectionString)

        {

            SqlConnection sqlConnection3 = new SqlConnection();
 

            if (connectionString.Length == 0)

            {

                return;

            }

            else

            {

                sqlConnection3.ConnectionString = "Data Source=" + sqlConnection3.DataSource + ";initial catalog=" + sqlConnection3.Database + ";user id=" + txtUsername.Text + ";pwd=" + txtPassword.Text;
 

                SqlCommand Command = new SqlCommand(sqlConnection3.ToString());

                Command.CommandTimeout = 2000;
 

                try

                {

                    Command.Connection.Open();

                    Command.ExecuteNonQuery();

                }

                catch (Exception ex)

                {

                    System.Windows.Forms.MessageBox.Show("Couldnt execute the query! " + ex.Message.ToString());

                }

                finally

                {

                    if (Command != null)

                    {

                        Command.Dispose();

                    }

                    if (sqlConnection3 != null)

                    {

                        sqlConnection3.Close();

                        sqlConnection3 = null;

                    }

                }

            }
 

            

        }

Open in new window

0
Comment
Question by:Adnan
  • 6
  • 3
  • 2
11 Comments
 

Author Comment

by:Adnan
ID: 22631557
When i come to line num 18 - 19 i get this error,   try
                                                                              {
                                                                                       Command.Connection.Open();   ERROR!!! object ref....
0
 

Author Comment

by:Adnan
ID: 22631563
i just want to make a connection to SQL DB to execute a script and so on.....!
0
 
LVL 14

Accepted Solution

by:
jaiganeshsrinivasan earned 500 total points
ID: 22631604
this is the code that i have to test the connection....

      private bool TestConnection(string DBName)

        {

            try

            {

                SqlConnection conn = new SqlConnection(MakeConnectionString(DBName));

                conn.Open();

                return true;

            }

            catch (Exception ex)

            {

                if (ex.Message != "")

                    CommonFunctions.ShowMessage("Error in connection", string.Format("Cannot connect to server {0} - DB {1}", serverCon.cmbServer.Text, serverCon.cmbdbName.Text), MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

            return false;
 

        }
 

        private string MakeConnectionString(string DBName)

        {

            string strConString;

            if (DBName == "") DBName = "master";

//for mixed mode

            if (serverCon.optMixed.Checked == true)

                strConString = string.Format("uid ={0};pwd ={1};data source ={2};initial catalog ={3}", serverCon.txtUserName.Text, serverCon.txtPass.Text, serverCon.cmbServer.Text, DBName);

            else

                strConString = string.Format("integrated security=SSPI;data source ={0};initial catalog ={1}", ServerNameVariable, DBName);
 

            return strConString;

        }

Open in new window

0
 

Author Comment

by:Adnan
ID: 22631685
hi i am getting a error message saying that the server connection serverCon does not exist in current context.... ???
0
 

Author Comment

by:Adnan
ID: 22631788
I tyried as you saud in your code...



     private bool TestConnection(string DBName)
        {
            SqlConnection conn = new SqlConnection(MakeConnectionString(DBName));
            try
            {
                conn.Open();
                return true;
            }
            catch (Exception ex)
            {
                if (ex.Message != "")
                    MessageBox.Show("Error in connection", string.Format("Cannot connect to server {0} - DB {1}"), MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return false;

        }
       
        private string MakeConnectionString(string DBName)
        {
            string strConString;
            SqlConnection serverCon = new SqlConnection();
            if (DBName == "") DBName = "master";
            //for mixed mode
            if (serverCon != null)
            {
                strConString = string.Format("Data Source=" + serverCon.DataSource + ";initial catalog=" + serverCon.Database + ";user id=" + txtUsername.Text + ";pwd=" + txtPassword.Text);
            }
            else
            {
                strConString = string.Format("integrated security=SSPI;data source ={0};initial catalog ={1}", serverCon.ServerVersion, DBName);
            }
                return strConString;
        }
is this function correct....????!
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 14

Expert Comment

by:jaiganeshsrinivasan
ID: 22631985
no its not correct...

in the line
          strConString = string.Format("integrated security=SSPI;data source ={0};initial catalog ={1}", serverCon.ServerVersion, DBName);
   
you have to pass the parameter servercon.serverVersion as your SERVER name...
          strConString = string.Format("integrated security=SSPI;data source ={0};initial catalog ={1}", "your server name goes here", DBName);
0
 
LVL 14

Expert Comment

by:jaiganeshsrinivasan
ID: 22631995
i thing you have changed the code that i gave...this will give the proper connection string for you


        private string MakeConnectionString(string DBName)

        {

            string strConString;

            if (DBName == "") DBName = "master";

                strConString = string.Format("uid ={0};pwd ={1};data source ={2};initial catalog ={3}", txtUserName.Text, txtPass.Text, "ServerName", DBName);
 

           return strConString;

        }

Open in new window

0
 
LVL 6

Expert Comment

by:weichunglow
ID: 22632124
Not sure  if you can get in through, please try to debug, and mouse over the connection object and see what is the connection string been captured by the connection object.

The reason you get the error message is because your connection string is invalid when passed in runtime. If you would like me to help further, please post your connection string that you captured during debugging.
0
 

Author Comment

by:Adnan
ID: 22632467
hi i captured this connection string during debuging....

uid =sa;pwd =kunfou;data source =ServerName;initial catalog =
0
 

Author Comment

by:Adnan
ID: 22632506
this connection string look more correct then the last one,  

Data Source=.;initial catalog=AccountMatch;user id=sa;pwd=kunfou

i get this connection string if i use

strConString = string.Format("Data Source=" + sqlConnection.DataSource + ";initial catalog=" + sqlConnection.Database + ";user id=" + txtUsername.Text + ";pwd=" + txtPassword.Text);

**************************Function running now*****************************
 private string MakeConnectionString(string DBName)
        {
            string strConString;
            if (DBName == "") DBName = "master";
            //strConString = string.Format("uid ={0};pwd ={1};data source ={2};initial catalog ={3}", txtUsername.Text, txtPassword.Text, "ServerName", DBName);
            strConString = string.Format("Data Source=" + sqlConnection.DataSource + ";initial catalog=" + sqlConnection.Database + ";user id=" + txtUsername.Text + ";pwd=" + txtPassword.Text);
           
            //string strConString;
            //if (DBName == "")
            //{
            //    DBName = "master";
            //}
            //if (sqlConnection != null)
            //{
            //    strConString = string.Format("Data Source=" + sqlConnection.DataSource + ";initial catalog=" + sqlConnection.Database + ";user id=" + txtUsername.Text + ";pwd=" + txtPassword.Text);
            //}
            //else
            //{
            //    strConString = string.Format("integrated security=SSPI;data source ={0};initial catalog ={1}", sqlConnection.ServerVersion, DBName);
            //}
            return strConString;
        }
0
 
LVL 6

Expert Comment

by:weichunglow
ID: 22646749
alright, base on the conn string you provide earlier on, it's clear that you can't connect because you did not specified the database name (means your DBName is blank),

uid =sa;pwd =kunfou;data source =ServerName;initial catalog =

NEXT, if you say you can obtain the connection string from the following code, then I assume your problem is resolved?

strConString = string.Format("Data Source=" + sqlConnection.DataSource + ";initial catalog=" +
sqlConnection.Database + ";user id=" + txtUsername.Text + ";pwd=" + txtPassword.Text);

In fact, I don't see the need to use string.format in this context as you are not actually formatting any string, the following code will do.

strConString = "Data Source=" + sqlConnection.DataSource + ";initial catalog=" +
sqlConnection.Database + ";user id=" + txtUsername.Text + ";pwd=" + txtPassword.Text ;

In addition, it is not a good practice to "hard code" the connection string in a source code, you should try to keep it in a web.config file. For information on how to store connection string in a web.config file, please refer to the following article.

http://msdn.microsoft.com/en-us/library/ms178411.aspx
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

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

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

14 Experts available now in Live!

Get 1:1 Help Now