2

I'm new to programming so please bear with me as I learn. I've been looking for days for a way to make this work, trying different solutions that I have found on here and other sites. I am using user input to create my connection string, and button 1 works great to verify that a connection has been established, button 2 not so much. I am trying to create a button that once pushed will execute a SQL command and provide the results from the command.

This is what I have so far, its button 2 that I have not been able to get to work yet.

using Microsoft.AspNet.SignalR.Infrastructure;
using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace WindowsFormsApp4
{
   public partial class Form1 : Form
   {
       public Form1()
       {
           InitializeComponent();
       }

       private void Form1_Load(object sender, EventArgs e)
       {

       }

       private void textBox1_TextChanged(object sender, EventArgs e)
       {

       }

       private void textBox2_TextChanged(object sender, EventArgs e)
       {

       }

       private void textBox3_TextChanged(object sender, EventArgs e)
       {

       }
       private void textBox4_TextChanged(object sender, EventArgs e)
       {

       }
       private void textBox5_TextChanged(object sender, EventArgs e)
       {

       }

       private void button1_Click(object sender, EventArgs e)
       {
           {
               string ServerName = textBox1.Text;
               string Database = textBox2.Text;
               string Username = textBox3.Text;
               string Pass = textBox4.Text;

               string connetionString;
               SqlConnection cnn;
               connetionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";
               cnn = new SqlConnection(connetionString);
               try
               {
                   cnn.Open();
                   MessageBox.Show("Connection Open  !");
                   cnn.Close();
               }
               catch (Exception) { MessageBox.Show("Login Failed, Information is Incorrect"); }
           }
       }

       private void button2_Click(object sender, EventArgs e)
       {
           string ServerName = textBox1.Text;
           string Database = textBox2.Text;
           string Username = textBox3.Text;
           string Pass = textBox4.Text;
           string results = textBox5.Text;

           string connetionString;
           SqlConnection cnn;
           connetionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";
           string userInput = "";
           var process = new Process();
           var startInfo = new ProcessStartInfo();
           startInfo.WindowStyle = ProcessWindowStyle.Hidden;
           startInfo.FileName = "cmd.exe";
           startInfo.Arguments = string.Format(@"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + "; "" SELECT count(*) from participanthistory, SELECT count(*) from postransaction where communicated = 0, userInput);

           process.StartInfo = startInfo;
           process.Start();
       }

       }


   }

I am trying to get the button to run this sql:

select count(*) from history
select count(*) from results where communicated = 0

I can run the SQL Query in SSMS no problem its just getting it to launch from the GUI I'm creating.

Any help is greatly appreciated.

Improve this question
10
  • 1
    Why not just use the SQLCommand object to do that? Not sure why you are using a Process object for this. Commented Feb 18, 2020 at 15:08
  • Thanks for the quick response. It was part of the last solution I had tried. I tried a few that had SQLCommand in it, but was unable to get them to work either. Commented Feb 18, 2020 at 15:15
  • Well, edit your question to show that code, and include any errors you get from it. The code you are running now needs to be thrown out. Commented Feb 18, 2020 at 15:18
  • When you open SSMS what is the server name and instance of server? Does it say windows credentials? the Server Name in connection string should be server/instance. Then if windows credentials you need Integrated Security = True. Commented Feb 18, 2020 at 15:27
  • @jdweng I'm not useing the windows account, you define the server, dB, user, password with the text boxes to build the connection string. Commented Feb 18, 2020 at 15:32

3 Answers 3

Reset to default
4

I wanted to say that I appreciate all the help and patience from everyone on here, I was able to get this resolved on another board and wanted to share the results encase someone else needs assistance with it.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace WindowsFormsApp4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }
        private void textBox4_TextChanged(object sender, EventArgs e)
        {

        }
        private void textBox5_TextChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            {
                string ServerName = textBox1.Text;
                string Database = textBox2.Text;
                string Username = textBox3.Text;
                string Pass = textBox4.Text;

                string connetionString;
                SqlConnection cnn;
                connetionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";
                cnn = new SqlConnection(connetionString);
                try
                {
                    cnn.Open();
                    MessageBox.Show("Connection Open  !");
                    cnn.Close();
                }
                catch (Exception) { MessageBox.Show("Login Failed, Information is Incorrect"); }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string ServerName = textBox1.Text;
            string Database = textBox2.Text;
            string Username = textBox3.Text;
            string Pass = textBox4.Text;
            SqlConnection connection = new SqlConnection();

            connection.ConnectionString = @"Data Source= " + ServerName + ";Initial Catalog= " + Database + ";User ID=" + Username + ";Password= " + Pass + ";";

            SqlCommand command = new SqlCommand();

            command.Connection = connection;
            command.CommandText = "--your SQL query --";
            command.CommandType = CommandType.Text;

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int CommunicatedRecordsCount = (int)reader[0];
                    textBox5.Text = CommunicatedRecordsCount.ToString();
                }

                reader.Close();
            }
            catch
            {
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
    }
}

Where I was looking for a numerical output we changed the output slightly, but again thanks everyone for all the assistance.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

it's an old thread but be careful with the connection, always remember to close it in the finally statement.
3

Sanitize that input!

Your click event code might look something like this:

    private void button2_Click(object sender, EventArgs e)
    {
        string ServerName = textBox1.Text;
        string Database = textBox2.Text;
        string Username = textBox3.Text;
        string Pass = textBox4.Text;
        //string results = textBox5.Text;

        using (var cnn = new SqlConnection($"Data Source= \"{ServerName}\";Initial Catalog= \"{Database}\";User ID=\"{Username}\";Password= \"{Pass}\";"))
        using (var cmd = cnn.CreateCommand())
        {
            cmd.CommandText = "-- Your query here --"; 

            try
            {
                cnn.Open();
            }
            catch (Exception ex)
            {
                // Error connecting to db.
            }

            if (cnn.State == System.Data.ConnectionState.Open)
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // Read your data, row by row, here. ...or do something with textBox5
                    }
                }
            }
        }
    }

And because you are just getting counts, you may also skip the reader and do this instead...

if (cnn.State == System.Data.ConnectionState.Open)
{
    var count = cmd.ExecuteScalar();
    //count is an object, cast as needed.
}
Improve this answer

5 Comments

Thanks for the reply, when I run the code like this it doesn't error so I'm pretty sure its connecting, and possibly running the query but I am not seeing any results or errors so I don't know if it's actually functioning properly. I know if I intentionally enter the wrong information into my text box for the connection string it shows cannot connect error. Any ideas?
@Saisei This code won't do anything if there is an error connecting. I just wanted to provide you with a better starting point. Set some breakpoints and step through it to see what's really going on.
thanks, I was unsure where it wasn't giving any output. I will keep trying thanks for the help.
using the breakpoint to walk through the code (again thanks Aurelius for telling me about this), the code walks through and executes on each lin but when it gets to f (cnn.State == System.Data.ConnectionState.Open) it then skips var count = cmd.ExecuteScalar();, this line of does not get executed, should that be right?
@Saisei So at that point cnn.State is NOT Open, you can check what cnn.State actually is. Because it's not Open, you should see the cnn.Open(); fail with the exception in the catch (Exception ex) block. ex will contain details about what went wrong... most useful is its Message property. Where the comment "// Error connecting to db." is, you could add MessageBox.Show(ex.Message);.
0

Something like this, open the connection with the connection string the user imputed, then read the returned rows and their fields 

SqlDataReader reader = null;
     using (SqlConnection connection = new SqlConnection(_sqlConnectionStringFromUserImput))
                    {
                        connection.Open();
                        if (connection.State == ConnectionState.Open)
                        {
                            SqlCommand sqlCommand =
                                new SqlCommand(
                                    "select count(*) from history",
                                    connection)
                                {
                                    CommandType = CommandType.Text,
                                    CommandTimeout = 20
                                };
                            reader = sqlCommand.ExecuteReader();
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    DateTime datetimefield = reader.GetFieldValue<DateTime>(0);
                                    string stringField = reader.GetFieldValue<string>(1);
                                }
                            }
                            reader.Close();
        }
                        connection.Close();
                    }
Improve this answer

8 Comments

Thanks for the reply, when I run the code like this it doesn't error so I'm pretty sure its connecting, and possibly running the query but I am not seeing any results or errors so I don't know if it's actually functioning properly. I know if I intentionally enter the wrong information into my text box for the connection string it shows cannot connect error. Any ideas?
@Saisei from what you have said there it sounds like your query just is not returning any rows, is there definitely data in your table?
@Saisei in place of "select count(*) from history" put "select GetDate()" and your reader should have 1 row in it where the first field will be a date time
there is data there, I should be getting a result of 3. I changed it to "select GetDate()" but again I click the button and do not get any information back. Is there a way to make sure that it is actually creating an output?
@Saisei you say you are new to programming, are you using visual studio? did you know that on the left side of your code beside line numbers you can click to add a red circle which is a breakpoint that you can use to stop your code while it is running and maually step through each line of code using F10 and you can check whats in all of your variables as you step through
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.