如何在VB.NET数据源上运行SQL脚本 [英] How to run SQL script on a VB.NET data source

问题描述

大家好，



在VB.NET上使用SQL时，我是新手。有人可以解释我需要遵循的步骤来运行基本的SQL脚本。我正在使用Visual Studio 2010.



这是我到目前为止所做的，

http://i49.tinypic.com/o7or2b.jpg [ ^ ]



- 为您创建一个新的数据源（file = G：\ Test.accdb）可以看到上面。连接字符串是ISP_connectionstring。



- 我有一个按钮，想要点击运行以下SQl脚本

  SELECT  *  FROM  ISP  WHERE  STATE = '  AK' 

'' ISP''是Test.accdb中的一个表名



这是我到目前为止关于VB代码所做的事情（我在网上找到了这个代码，没有想法它是做什么的）

 私人  Sub  confirm_state_selection_Click（ ByVal  sender  As  System。 Object ， ByVal  e  As  System.EventArgs） Handles  confirm_state_selectio n。单击
 
  Dim  myConnection  As  SqlConnection = 新 SqlConnection（ 数据源= G：\ Test.accdb）
 
 myConnection.Open（）
 ' 执行查询等 
 MsgBox（  connected）
 myConnection.Close（）
 
 结束  Sub  

这是我遇到的错误，



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



任何建议/意见将非常感谢!!



谢谢，



SB

解决方案

如果你得到以下SQL Server 2005错误（） [ ^ ]：



发生与网络相关或特定于实例的错误建立与SQL Server的连接。服务器未找到或无法访问。验证实例名称是否正确，以及SQL Server是否配置为允许远程连接。 （提供者：SQL网络接口，错误：26 - 查找指定的服务器/实例时出错）



要检查的事项：



1.确保您的数据库引擎配置为接受远程连接

•开始>所有程序> SQL Server 2005>配置工具> SQL Server表面区域配置

•单击服务和连接的表面区域配置

•选择出现问题的实例>数据库引擎>远程连接

•启用本地和远程连接

•重启实例



2.检查SQL Server服务帐户

•如果您没有使用域帐户作为服务帐户（例如，如果您使用的是NETWORK SERVICE），您可能需要先切换它才能继续使用



3.如果您使用的是命名的SQL Server实例，请确保在ASweb P.NET应用程序的连接字符串中使用该实例名称

•通常是指定数据库服务器所需的格式是machinename \instancename

•检查你的连接字符串



4.你可能需要创建您正在使用的SQL Server实例和端口的防火墙上的例外

•开始>运行> Firewall.cpl

•单击例外选项卡

•添加sqlservr.exe（通常位于C：\Program Files（x86）\ Microsoft SQL Server \ MSSQL.x \ MSSQL \ Binn，检查安装的实际文件夹路径）和端口（默认为1433）

•检查你的连接字符串



5.如果您使用的是命名的SQL Server实例，请确保在连接字符串中使用该实例名称



6。检查SQLBrowser;检查它是否正在运行。您可能还需要在防火墙中为SQLBrowser创建一个例外。



7.检查您是否已连接到SQL Server。



注意您用于连接的内容：机器名称，域名或IP地址？检查连接时使用此选项。例如，如果您使用myserver

•开始>运行> cmd

•netstat -ano | findstr 1433

•telnet myserver 1433

•ping -a myserver

感谢响应du {DE}。



我的本地机器上有我的文件（Test.accdb）。我是否还需要SQL服务器来执行SQL脚本？

感谢您的帮助。我找到了答案。我必须包含SQL Ole.db客户端，因为它是项目中的现有数据源。



以下是Shanks的解释（MSDN）



http://social.msdn.microsoft的.com /论坛/ EN-US / vbgeneral /线程/ ff46a43f-47cd-4783-8f16-07fa1e32e7f0

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

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)

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

解决方案

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

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 ?

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

这篇关于如何在VB.NET数据源上运行SQL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！