form connection to SQL server

[JohnSe]

Hi,

Could someone provide some codes examples that can help me connect C# forms to SQL server that is trusted (without user name or password, just the server and database name).

Thanks

 

[Skydiver]

In your C# connection string, "Initial Catalog" is misspelled.

 

[Skydiver]

I also noticed that you included OLEDB. Perhaps the database you were connecting to was really an OLEDB database rather than a MSSQL Server database?

 

[JohnSe]

I also noticed that you included OLEDB. Perhaps the database you were connecting to was really an OLEDB database rather than a MSSQL Server database?

Thanks for your response. I fixed the spelling error. But that is the exact connection string I used. It has work for me with VB.NET. What do you think caused it not to work ? Could it be reference that wasn’t included. The database is actually Microsoft SQL server.

I still have the saved VB.NET code that visual studio created that work but how do I open that code in visual studio and into a form.vb and design_form.vb and run it after closing visual studio and the codes with it. Just want to see if it works again. I can’t explain why it didn’t work after it has work. Please advise !

 

[JuggaloBrotha]

Try this, add a new item "DataSet" from the "Data" tab when adding a new item to the project.
Add a new TableAdapter and have it generate the connection string to your sql server, once confirming it works (by adding the table) you can copy the connection string (or leave it and just use the My.Settings.<connectionstringname> in your code since it's part of the project) and use it in your code.
You can even delete the DataSet from the project, or learn how to use them which will allow you to query your data using proper code and simpler to maintain since you have the nice visual tool at your disposal.

 

[JohnSe]

Imports System.IO Imports System.Data Imports System.Data.SqlClient Imports System.Data.OleDb Public Class Form1 Private myC As SqlConnection Private mCm As SqlCommand Private myR As SqlDataReader Private res As String Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click myC = New SqlConnection("Initial Catalog = DB; Data Source = serverName; Integrated Security = SSPI;") mCm = myC.CreateCommand mCm.CommandText = "SELECT * FROM table" myC.Open()

Skydiver,

What used to work for VB.NET doesn't work anymore, maybe I need to add a reference.
I tried it for VB.NET and it connected. I tried to do it for C# as well but couldn't get it connected.
I went back to try again for VB.NET the code that works, now it has the same error message as I see in C#.
Could it be that I need to add reference, kind of like VBA need to add Microsoft ActiveX Data Objects Library.

This is what I use for VB.NET

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Form1
    Private myC As SqlConnection
    Private mCm As SqlCommand
    Private myR As SqlDataReader
    Private res As String

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        myC = New SqlConnection("Initial Catalog = DB; Data Source = serverName; Integrated Security = SSPI;")
        mCm = myC.CreateCommand
        mCm.CommandText = "SELECT * FROM table"
        myC.Open()

        myR = mCm.ExecuteReader()
    End Sub
End Class

This is what I use for C#

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

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

        private void button1_Click(object sender, EventArgs e)
        {
            String connectionstring = null;
            SqlConnection cnn;
            connectionstring = "Data Source = serverName; Initial Catalog = Database; Integrated Security = SSPI";
                cnn = new SqlConnection(connectionstring);
            cnn.Open();
        }
    }
}

Please Advise !

Try this, add a new item "DataSet" from the "Data" tab when adding a new item to the project.
Add a new TableAdapter and have it generate the connection string to your sql server, once confirming it works (by adding the table) you can copy the connection string (or leave it and just use the My.Settings.<connectionstringname> in your code since it's part of the project) and use it in your code.
You can even delete the DataSet from the project, or learn how to use them which will allow you to query your data using proper code and simpler to maintain since you have the nice visual tool at your disposal.

Juggalobrotha,

Thanks for the advise. I am new to C# and I can’t follow unless with detail or pics, sorry.
I don’t know where dataset and data tab is. Please advise

 

[NoUserHere]

Post 2 explains how to structure your code (Regardless the example is for MySQL). the structure is the same for SQL declarations. Rename everything MySQL to SQL, and provide the correct query string with an instance name for your SQL server and it will connect. What you posted in post 30 looks nothing like what I recommended on post 2. We have been asking you for the exact query string you use since post 13 and we are 36 posts in and you still haven't provided an unedited query string.

serverName is not the correct name for a SQL service instance. And this ::

I also noticed that you included OLEDB. Perhaps the database you were connecting to was really an OLEDB database rather than a MSSQL Server database?

Well spotted Skydiver, an eye of an Eagle. lol I even overlooked that hidden in VB code. On this note, I think its worth to note the following ::

Integrated Security=true; will likely throw an exception when used with the OleDb provider. ​

Integrated Security=SSPI; would be most recommended as it works with both SQLClient and also the OleDB.​

These will work with SQLClient ::

Integrated Security=true; ​

Integrated Security=SSPI;​

This will work with OleDB ::

Integrated Security=SSPI;​

You need to refer yourself back through the countless advice you've already been given on this topic.

1. Restructure your code like I demonstrated at the beginning in post 2.
2. Post your actual query string as it is (Unedited).
3. Know what you're connecting too, be it OleDB or SQLClient.
4. Provide an appropriate query statement for your command, followed by your connection to a new SQL instance that references your connection string.
5. You said :: "The database is actually Microsoft SQL server." - So you would be best using an instance based connection string and drop the password. See - .NET Framework Data Provider for SQL Server Connection Strings - ConnectionStrings.com

I am working with databases since .... since as long as i remember lol, its a long time! Following the advice step by step, you will get a working connection.

 

[JohnSe]

Sheepings,

I am new to C#, I can't restructure it like how you have it in post 2 without spending more time to it.
I can't post my actual query string, the server and database name are confidential.
I told you many times that in the Vb.NET code that I use and posted is exactly that with the server and database name changed and it has worked.
It just doesn't work recently as I have tried it. I don't know why it work with the same exact code and now it doesn't. I don't know what else do you want me to do. I was able to use vba to connect so is remote connection and the server and database name is correct.

Can you advise how your second post can be made to C# or actually have a C# that has connected ?

 

[Skydiver]

Is your current VBA code still able to connect?

Using Visual Studio Professional or Ultimate, can you connect using the Server Manager?

There are a couple of things to look at:
1) Anti-virus/nannyware: Some AV and nanny ware products will prevent an application from doing outbound connections. Often these will block on a per application basis, so may possible that your VBA from Excel and/or Word would succeed because they are white listed, but your VB.NET and C# programs are not. Unfortunately, every AV and nanny ware product has it's own custom UI for checking and modifying this settings. If you have enough permissions on your machine to temporarily disable the AV and nanny ware, try running a quick test to see if you can connect while they are all disabled. If it works, then run a process of elimination to see which piece of "security" software is blocking you. Then once you narrow it down, see if you can add any exclusion settings to allow your application to make connections.
2) Firewall settings: Your company may have pushed a global firewall setting preventing outbound connections. I don't know enough about Windows firewall settings to be able to point you directly to which settings to look at unfortunately. In the old WinXP and Win7 days, Windows would warn you that an outbound connection and you were given the opportunity to allow it, and then Windows would take care of writing the correct firewall rules to allow that connection. Those days are long gone.
3) TLS: Make sure that you are using .NET framework 4.5 or higher. Older versions of .NET Framework would use a version of TLS that has been deprecated due to security issues. Most organizations force the use of TLS 1.2, so you may need to add System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; at the entry point of your application.
4) See if you can connect locally: Install SQL Server Express on your local machine. See if you can connect. If that fails as well, then very likely there is something wrong with your .NET Framework installation. Try to run a repair, or an uninstall followed by an install of the .NET Framework on your machine.

 

[JohnSe]

Skydiver,

yes my current VBA codes and R codes is able to connect.
I was able to connect with vb.NET like about a month and a half ago now I can't with the same exact code I posted here.
I don't know why. How can you add exclusion settings to allow your app to make connections. If is fire wall wouldn't that stop VBA too ?

so add System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; would work, let me try to run repair.
How do I run repair ? I can't uninstall and reinstall. I don't have admin rights.

 

[Skydiver]

If it's at the firewall level, it should stop VBA as well. If it's an antivirus or nannyware, then they typically run at application level. Your admins may have added the apps that work into their whitelist.

If you don't have admin rights, then yes, uninstall and install will definitely be out of your reach. Repair should be available, though. Go to the Control Panel, find the .NET Framework under Apps (in Win10) or Add/Remove Programs in older Windows, right click, and Repair should be a choice.

 

[JohnSe]

If it's at the firewall level, it should stop VBA as well. If it's an antivirus or nannyware, then they typically run at application level. Your admins may have added the apps that work into their whitelist.

If you don't have admin rights, then yes, uninstall and install will definitely be out of your reach. Repair should be available, though. Go to the Control Panel, find the .NET Framework under Apps (in Win10) or Add/Remove Programs in older Windows, right click, and Repair should be a choice.

Add the app to their whitelist you meant visual studio because if they add the app, I could always create another app. Also I’m not sure what whitelist is.

I will repair and see if that works. The only thing I know that vb codes didn’t work is after when I tried C# codes and connected the database via visual studio in tools menu，connect to database where I enter server and database name. Before this I was able to connect with vb.net with the code I posted, now both doesn’t connect.

 

[Skydiver]

By adding an app to their white list, add the name of the executable that you are compiling using Visual Studio. If your executable is named "MyAwesomeApp.exe", they need to add "MyAwesomeApp.exe" to their whitelist.

Can Visual Studio still connect to the database?

While I am thinking about it, what connection string are you using for your VBA and R code?

As I recall, for VBA connection strings, you need to also need to have a "Provider=providername" as part of the connection string. That would then imply that you are using on OLEDB connection, not a SQL connection like you are now trying to use with your current VB.NET and C# code. So what provider are you giving as that provider name value?

 

[JuggaloBrotha]

Juggalobrotha,

Thanks for the advise. I am new to C# and I can’t follow unless with detail or pics, sorry.
I don’t know where dataset and data tab is. Please advise

Do you not know how to add a new Form to your project? Or a new Class? Or a Module?
A DataSet is simply another object that can be added, easiest way to find it is to click the "Data" tab on the left side of the "Add -> New Item..." window.
Also, if you know how to add new items in VB.net then you know how to in C# too.

But you could also to a Google search, this took less than a minute to find searching on "Add DataSet Visual Studio": Walkthrough: Creating a Dataset with the Dataset Designer - Visual Studio | Microsoft Docs

 

[JohnSe]

By adding an app to their white list, add the name of the executable that you are compiling using Visual Studio. If your executable is named "MyAwesomeApp.exe", they need to add "MyAwesomeApp.exe" to their whitelist.

Can Visual Studio still connect to the database?

While I am thinking about it, what connection string are you using for your VBA and R code?

As I recall, for VBA connection strings, you need to also need to have a "Provider=providername" as part of the connection string. That would then imply that you are using on OLEDB connection, not a SQL connection like you are now trying to use with your current VB.NET and C# code. So what provider are you giving as that provider name value?

I don’t think it’s added to the white list bc the exe name is not know.

Yes visual studio can connect to the database via tools connect to database and enter server and database name.

For R
Install.packages(ODBC)
Library(ODBC)
Connect <- odbcdriverconnect(connection = driver={SQL Server Native Client 11.0}; Server = servername; database = dbname; trusted_connection= yes;”)

For VBA

Constr = “Driver={SQL Server Native Client 11.0}; Server = servername; database = databasename; trusted_connection = yes;”

Set cx = new ADODB.Connection
cx.open Constr

 

[Skydiver]

Yes visual studio can connect to the database via tools connect to database and enter server and database name.

What driver did you select when testing this connection via Visual Studio?

Perhaps you should try using an OdbcConnection instead of a SqlConnection. The connection string for an ODBC connection should look like what you are using for VBA and R.