在Visual Studio中使用VB.NET连接到本地SQL数据库的连接字符串问题 [英] Connection string problem connecting to local SQL database using VB.NET in Visual Studio

问题描述

我不明白为什么我无法与SQL Server Express LocalDB连接.我一直在麻烦我的连接字符串.这是我尝试过的:

I can't understand why I can't connect with my SQL Server Express LocalDB. I keep getting in to trouble with my connection string. This is what I have tried:

Imports System.Data.OleDb

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim conString As String        'Connection string
        Dim con As OleDbConnection     'Connecting to your database
        Dim Command As OleDbCommand    'Query  "What do you want in the database"

        'conString = "PROVIDER=System.Data.SqlClient v4.0; Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DatabaseInnlogging.mdf;Integrated Security=True"
        'conString = "PROVIDER=SQLOLEDB; Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DatabaseInnlogging.mdf;Integrated Security=True"
        'conString = "PROVIDER=System.Data.SqlClient; Data Source=C:\Users\Bruker\source\repos\InnloggingFørsøkv1\InnloggingFørsøkv1\DatabaseInnlogging.mdf;Integrated Security=True"
        'conString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Bruker\source\repos\InnloggingFørsøkv1\InnloggingFørsøkv1\DatabaseInnlogging.mdf;Integrated Security=True"
        'conString = "PROVIDER=System.Data.SqlClient v4.0; Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DatabaseInnlogging.mdf;Integrated Security=True"

    Try
           con = New OleDbConnection(conString)
           con.Open()

            Command = New OleDbCommand("select * from LogInTable where UserID = ? and Password = ?", con)



            Dim parm1 As OleDbParameter, parm2 As OleDbParameter

            parm1 = Command.Parameters.Add("@UserID", OleDbType.VarChar)
            parm2 = Command.Parameters.Add("@Password", OleDbType.VarChar)
            parm1.Direction = ParameterDirection.Input
            parm2.Direction = ParameterDirection.Input

            parm1.Value = Me.TextBox1.Text
            parm2.Value = Me.TextBox2.Text

            Command.Connection.Open()
            Dim reader As OleDbDataReader

            If reader.Read = True Then
                Me.DialogResult = DialogResult.OK
            Else
                MsgBox("Invalid UserID or Password")
            End If

            reader = Command.ExecuteReader

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
End Sub

我尝试了几个连接字符串，但是没有一个起作用.

I have tried several connection strings, but none is working.

但是数字4却出错了

多步OLE DB操作生成错误.检查每个OLE DB状态值

Multiple-step OLE DB operation generated errors.check each OLE DB status value

其他导致错误:

供应商未被识别

我的另一个尝试是使用 SqlClient :

My other attempt was with SqlClient:

Imports System.Data.SqlClient

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim myConn As SqlConnection
        myConn = New SqlConnection("Initial Catalog=OutComes; Data Source=localhost;Integrated Security=SSPI;")

        '"Data Source = (LocalDB) \ MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DatabaseInnlogging.mdf;Integrated Security=True")

        '  "Data Source=localhost;Integrated Security=SSPI;")

        'Create a Command object.
        Dim myCmd As SqlCommand
        myCmd = myConn.CreateCommand
        myCmd.CommandText = "SELECT UserID, Password FROM LogInTable"

        'Open the connection.
        myConn.Open()

        Dim results As String
        Dim myReader As SqlDataReader
        myReader = myCmd.ExecuteReader()

        'Traverse the DataSet and Display in GUi for Example:
        Do While myReader.Read()
            results = results & myReader.GetString(0) & vbTab &
                myReader.GetString(1) & vbLf
        Loop
        'Display results.
        MsgBox(results)

        ' Close the reader and the database connection.
        myReader.Close()
        myConn.Close()
End Sub

我在这里的错误是:

System.Data.SqlClient.SqlException:'连接到SQL Server时发生与网络相关或特定于实例的错误.找不到服务器或服务器不可用.验证实例名称正确，并且已将SQL Server配置为接受外部连接.(提供者:命名管道提供者，错误:40-无法打开与SQL Server的连接)

System.Data.SqlClient.SqlException: 'A network-related or instance-specific error occurred while connecting to SQL Server. The server was not found or was not available. Verify that the instance name is correct and that SQL Server is configured to accept external connections. (provider: Named Pipes Provider, error: 40 - Unable to open a connection to SQL Server)

我的SQL Server数据库中有一个表，其中有两列 UserID 和 password .

I have one table in my SQL Server database with two columns UserID and password.

如果有人能指出我正确的方向，我将不胜感激.我已经阅读了很多有关该主题的文章，但似乎找不到我错了.我的主要目标是连接数据库.

I would be very grateful if someone could point me in the right direction here. I have read many post on the subject but cant seem to find where I go wrong. My main goal here is to connect with my database.

推荐答案

当您是新手时，可以尝试一些视觉工具，它可以帮助您看得更清楚，我建议您尝试这种方法，这是您的一半方法解决方案:

As you are new you can try some visual tools it can help you to see more clear, I suggest you to try this approach, it's a half way to your solution :

在您的visual Studio中:

In your visual Studio :

Menu Tools

Connection to database

在对话框中

数据源: Microsoft SQL Server(sqlClient)

Data Source : Microsoft SQL Server (sqlClient)

服务器名称: ****

您应该在这里找到您的服务器，然后选择它，否则这意味着您在安装服务器时遇到了问题

you should find your server here, select it, Else that's mean you have problem with your server installation

身份验证: Windows身份验证

Authentification : Windows Authentification

正如我在您的示例中看到的那样，您没有使用SQL id，所以很好

as i see in your example your are not using SQL id so that's fine

Connection to database:

选择已创建的数据库(如果未显示)这意味着您没有创建，或者您没有访问权限

Select the Database that you already created, if nothing seen that's mean you didn't created or you don't have rights to access

然后单击按钮 Test Connection 如果成功，则单击确定

Then Click in button Test Connection if success then click OK

然后转到您的 ToolBox

Data

DataGridView 将其放入您的表单中选择数据源:在底部，您将看到 +添加数据源点击它

DataGridView drop it in your form select data source : in bottom you will see + Add the data source click on it

您将看到一个对话框，选择 Database ->数据集->选择你的数据连接您应该在组合框中看到一行您的服务器名称\您的Database.dbo

You will have a Dialog, choose Database -> Dataset -> choose you data connection you should see a line in the combobox with your server name \ your Database.dbo

选中复选框显示存储在应用程序中的连接字符串"

您将清楚看到您的连接字符串

下一步->下一个>检查表

Next -> Next > check Tables

完成

如果您停留在某个地方，请让我知道通过修改来澄清您的情况

If you stuck in some part let me know to clarify you by an Edit

这篇关于在Visual Studio中使用VB.NET连接到本地SQL数据库的连接字符串问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！