Sql commands not working in VB.NET

Question

0.00/5 (No votes)

See more:

I am writing a little data migration tool for home use. I have written many such programs professionally and since I retired, but this one is stopping me cold. At the top of my vb.net form code file, I've added an Imports line for System.Data.sqlClient and even though I am using data types and commands dependent on it, the line gets greyed out as if it is not being referenced. The commands and data types I use in subsequent code get errors claiming that they are not defined. What the heck? I am essentially copying code from other projects I have that work.

This us using Microsoft Visual Studio Community 2022 (64-bit) Version 17.4.3 and SQL Server Management Studio v18.12.1 on Windows 11 Home version 23H2, OS build 22631.3593, by the way. I am coding for x86 CPUs as I am exporting to a 32-bit version of SQLite (the target database on a media player with a 32-bit OS).

What I have tried:

Imports System.Data.SqlClient
...

Using connection As New SqlConnection("Data Source=" + System.Environment.MachineName + "\SQLEXPRESS;Initial Catalog=MusicCollection;Integrated Security=True")
	Dim command As New SqlCommand("SELECT * FROM dbo.Tracks", connection)
	command.Connection.Open()
	SqlDataReader Reader = command.ExecuteReader()
End Using

Posted 30-May-24 9:50am

Member 13474292

Add a Solution

Comments

Dave Kreskowiak 30-May-24 16:29pm

You forgot to mention which version of the .NET Framework or .NET (Core) you're writing the code against.

I suspect you don't have a reference to the appropriate library defined.

Member 13474292 30-May-24 19:41pm

The target framework is .NET 6.0. I suspect you're right about not having a reference to the appropriate library but I'm not sure where to look. It's been 7 years since I've retired and I sometimes think I've forgotten more than I ever learned! Other than the Imports statement, I can't think how to reference a library that would include support for working with SQL Server. Simply adding a reference to System.Data.SQLite.dll was enough to get me going with the output but the input from SQL Server not working is making me feel stupid!

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2024-05-30T15:31:00

Right-click the solution line in Solution Explorer and click "Manage NuGet Packages for Solution...".

Click the Browse tab in the window that shows up and search for "Microsoft.Data.SqlClient". The first result should be the library of that name. Click once on it and in the right pane that shows up, check the box next to the project you want to install it to, then click Install.

DO NOT USE "System.Data.SqlClient". The Microsoft namespaced one mentioned above is replacing this one.

The .NET Framework (v4.8.x) and lower are all monolithic frameworks that had everything built in and only worked on Windows.

.NET Core (now a deprecated name) and .NET are more modular and usable on multiple platforms, from Windows to Linux, to Mac and Android. The libraries, like the SqlClient, are not shipped with the framework anymore to make it more portable. If you're app uses something like that, you add it to your app through a NugGet package and only ship with your app what you're using.

Dave Kreskowiak · Answer 2 · 2024-05-31T12:35:00

The connection string needs to be updated a bit because SQL Server Native Client changed with version 19 of SQL Server (all editions).

The part you should have Googled is part of the error message, "The certificate chain was issued by an authority that is not trusted".

For a local SqlExpress installation, on the same machine as the running app, and assuming you're using Windows account for authentication, your connection string should be:

Server=.\SQLEXPRESS;Database=MusicCollection;Trusted_Connection=True;TrustServerCertificate=true"

The last part will tell the SQL Client to ignore the server certificate validation failure and just trust it.

Sql commands not working in VB.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Sql commands not working in VB.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us