How to run SQL script on a VB.NET data source

Question

0.00/5 (No votes)

See more:

Hello all,

I am a newbie when it comes to using SQL on VB.NET. Can someone please explain the steps I need to follow to get a basic SQL script running. I am using Visual Studio 2010.

Here is what I have done so far,
http://i49.tinypic.com/o7or2b.jpg[^]

- Created a new data source (file = G:\Test.accdb) as you can see above. The connection string is ISP_connectionstring.

- I have a button and want to run the following SQl script on click

SQL

SELECT * FROM ISP WHERE STATE ='AK'

'ISP' is a table-name within Test.accdb

Here is what I've done so far regarding the VB code (I found this code online, no idea what it does)

VB.NET

Private Sub confirm_state_selection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles confirm_state_selection.Click

        Dim myConnection As SqlConnection = New SqlConnection("Data Source=G:\Test.accdb")

        myConnection.Open()
        ' execute queries, etc
        MsgBox("connected")
        myConnection.Close()

    End Sub

This is the error I am encountering,

http://i49.tinypic.com/wb48yg.png[^]

Any suggestions / comments would be highly appreciated !!

Thanks,

SB

Posted 9-Mar-13 5:49am

Member 9896989

Updated 9-Mar-13 5:54am

Thomas Daniels

v2

Add a Solution

Comments

nitin bhoyate 9-Mar-13 11:54am

try providing full Connection String
find more details about connection strings here www.connectionstrings.com

[no name] 9-Mar-13 11:56am

"[IMG]http://i49.tinypic.com/wb48yg.png[/IMG]" is not an error. Not knowing what error you are actually getting, my guess is that you are trying to use SqlConnection on an Access database when you should be using OleDbConnection.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Leo Chapiro · Answer 1 · 2013-03-09T06:18:00

If you get the following SQL Server 2005 Error ()[^]:

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ”

Things to check:

1. Make sure your database engine is configured to accept remote connections
• Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
• Click on Surface Area Configuration for Services and Connections
• Select the instance that is having a problem > Database Engine > Remote Connections
• Enable local and remote connections
• Restart instance

2. Check the SQL Server service account
• If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application
• Usually the format needed to specify the database server is machinename\instancename
• Check your connection string as well

4. You may need to create an exception on the firewall for the SQL Server instance and port you are using
• Start > Run > Firewall.cpl
• Click on exceptions tab
• Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn, check your installs for the actual folder path), and port (default is 1433)
• Check your connection string as well

5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

7. Check that you have connectivity to the SQL Server.

Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
• Start > Run > cmd
•netstat -ano| findstr 1433
•telnet myserver 1433
•ping -a myserver

Member 9896989 · Answer 2 · 2013-03-09T06:22:00

Solution 2

Thanks for the response du{DE}.

I have my file (Test.accdb) on my local machine. Do I still need SQL server to execute a SQL script ?

Posted 9-Mar-13 6:22am

Member 9896989

Member 9896989 · Answer 3 · 2013-03-12T11:27:00

Solution 3

Thank you for your help guys. I found the answer. I had to include the SQL Ole.db client since it was an existing data source within the project.

Here is the explanation by Shanks (MSDN)

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/ff46a43f-47cd-4783-8f16-07fa1e32e7f0

Posted 12-Mar-13 11:27am

Member 9896989

How to run SQL script on a VB.NET data source

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us