An established connection was aborted by the software in your host machine.

Question

1.00/5 (1 vote)

See more:

Hi everybody,
I'm facing a problem with SQL2012 server after i migrated access 2013 database to it,
saving or updating to sql database performs in normal way but after hitting the save button this error message appears '

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.)

'
This is my code

VB

Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
        Using con As New SqlConnection(cs)
            con.Open()
            Using cmd As New SqlCommand("update Product set AVNo=@a  
                                           where ProductCode=@b", con)
                cmd.Parameters.Add("@a", SqlDbType.Bit).Value = CheckBox1.Checked
                cmd.Parameters.Add("@b", SqlDbType.Int).Value = Val(txtProductCode.Text)

                'If con.State = ConnectionState.Open Then
                '    con.Close()
                'End If
                'con.Open()
                'cmd.ExecuteNonQuery()
                'con.Close()
            End Using
        End Using
    End Sub

This happens in my local machine .
And this is my Connection string

 Public cs As String = "Data Source=KMA18214\SQLEXPRESS,1433;Network Library=DBMSSOCN;Initial Catalog=Stock;" &
"Persist Security Info=True;User ID=amr;Password=****;Trusted_Connection=True;"

And this error occurred after saving and then updating(i.e saving process done without error and after hit the update button the error occurred)

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

This is the code of update button

Private Sub Update_Record_Click(sender As Object, e As EventArgs) Handles Update_Record.Click

       If txtProductCode.Text = GenerateID("Product", "ProductCode") Then
           MessageBox.Show("يجب حفظ الصنف اولا", "Product Details", MessageBoxButtons.OK, MessageBoxIcon.Information)
           Exit Sub
       End If
       If Len(Trim(ComboBox1.Text)) <> 0 Then
           MessageBox.Show("استخدم زرار نقل من باكية لاخرى", "Product Details", MessageBoxButtons.OK, MessageBoxIcon.Information)
           Button5.Focus()
           Exit Sub
       End If
       If Len(Trim(cmbCategory.Text)) = 0 Then
           MessageBox.Show("ادخل اسم الباكية", "Product Details", MessageBoxButtons.OK, MessageBoxIcon.Information)
           Exit Sub
       End If

       ' for enforce user to use category screen
       conn = New SqlConnection(cs)
       conn.Open()
       Dim n As String = "select CategoryName from InventoryCategory where CategoryName='" & cmbCategory.Text & "' "
       cmd = New SqlCommand(n)
       cmd.Connection = conn
       Dim datatable1 As New DataTable
       Dim datadap As New SqlDataAdapter(cmd)
       datadap.Fill(datatable1)

       If datatable1.Rows.Count = 0 Then
           MessageBox.Show(" يجب تعريف باكية جديدة اذهب لشاشة تعريف باكية", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
           cmbCategory.Text = ""
           cmbCategory.Focus()

           conn.Close()
           Exit Sub
       End If

       conn = New SqlConnection(cs)
       conn.Open()


       Dim cb As String = "update Product set
                           ProductName=@d1,
                           Price=@d2,
                           SellPrice=@d3,
                           DamNo=@d4,
                           AVNo=@d5,
                           SumStockNo=@d11
                           where Productcode =@d6"

       cmd = New SqlCommand(cb)
       With cmd.Parameters
           .Add("@d1", SqlDbType.VarChar).Value = txtProductName.Text
           .Add("@d2", SqlDbType.Int).Value = Val(txtPrice.Text)
           .Add("@d3", SqlDbType.Int).Value = Val(txtSellPrice.Text)
           .Add("@d4", SqlDbType.Int).Value = Val(txtDamage.Text)
           .Add("@d5", SqlDbType.Bit).Value = CheckBox1.Checked
           .Add("@d11", SqlDbType.Int).Value = Val(TextBox4.Text)
           .Add("@d6", SqlDbType.Int).Value = Val(txtProductCode.Text)
       End With

       cmd.Connection = conn


       If conn.State = ConnectionState.Open Then
           conn.Close()
       End If

       conn.Open()
       cmd.ExecuteNonQuery()
       conn.Close()

       '''''''''''''''''

       conn = New SqlConnection(cs)
       conn.Open()

       Dim cb2 As String = "insert into ProLog(ProductCode,ProductName,Category,Price,SellPrice,StockNo,EntryDate,NewStockNo,UpdatedDate)
                            VALUES(@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9)"

       cmd = New SqlCommand(cb2)

       cmd.Connection = conn

       cmd.Parameters.Add(New SqlParameter("@d1", SqlDbType.Int, 20, "ProductCode"))
       cmd.Parameters.Add(New SqlParameter("@d2", SqlDbType.VarChar, 150, "ProductName"))
       cmd.Parameters.Add(New SqlParameter("@d3", SqlDbType.VarChar, 150, "Category"))
       cmd.Parameters.Add(New SqlParameter("@d4", SqlDbType.Float, 20, "Price"))
       cmd.Parameters.Add(New SqlParameter("@d5", SqlDbType.Float, 20, "SellPrice"))
       cmd.Parameters.Add(New SqlParameter("@d6", SqlDbType.Int, 20, "StockNo"))
       cmd.Parameters.Add(New SqlParameter("@d7", SqlDbType.Date, 20, "EntryDate"))
       cmd.Parameters.Add(New SqlParameter("@d8", SqlDbType.Int, 20, "NewStockNo"))
       cmd.Parameters.Add(New SqlParameter("@d9", SqlDbType.Date, 50, "UpdatedDate"))


       cmd.Parameters("@d1").Value = Val(txtProductCode.Text)
       cmd.Parameters("@d2").Value = txtProductName.Text
       cmd.Parameters("@d3").Value = cmbCategory.Text
       cmd.Parameters("@d4").Value = CDbl(Val(txtPrice.Text))
       cmd.Parameters("@d5").Value = CDbl(Val(txtSellPrice.Text))
       cmd.Parameters("@d6").Value = Val(txtStockNo.Text)
       cmd.Parameters("@d7").Value = DateTimePicker1.Value
       cmd.Parameters("@d8").Value = Val(txtNewStockUpdate.Text)
       cmd.Parameters("@d9").Value = ToolStripStatusLabel4.Text

       cmd.ExecuteReader()

       If conn.State = ConnectionState.Open Then
           conn.Close()
       End If

       conn.Close()
       '''''''''

       If txtStockID.Text = GenerateID("Stock", "StockID") Then
           Dim ct As String = "select ProductCode,Category from Stock where ProductCode=@find and Category=@find1"
           Using conn = New SqlConnection(cs)
               conn.Open()
               Using cmd As New SqlCommand(ct)
                   cmd.Connection = conn
                   cmd.Parameters.Add(New SqlParameter("@find", SqlDbType.Int, 20, "ProductCode"))
                   cmd.Parameters.Add(New SqlParameter("@find1", SqlDbType.VarChar, 100, "Category"))
                   cmd.Parameters("@find").Value = Val(txtProductCode.Text)
                   cmd.Parameters("@find1").Value = cmbCategory.Text
                   rdr = cmd.ExecuteReader()

                   If rdr.Read Then
                       MessageBox.Show("المنتج موجود بهذه الباكية قم بتعديله", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                       If Not rdr Is Nothing Then

                           rdr.Close()
                       End If
                       Exit Sub
                   Else

                       Dim con1 = New SqlConnection(cs)
                       con1.Open()

                       Dim cb3 As String = "insert into Stock(StockID,ProductCode,ProductName,Price,SellPrice,Category,StockDate,StockNo,User_Name)VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9)"

                       Dim cmd11 As New SqlCommand(cb3)

                       cmd11.Connection = con1

                       cmd11.Parameters.Add(New SqlParameter("@d1", SqlDbType.Int, 20, "StockID"))
                       cmd11.Parameters.Add(New SqlParameter("@d2", SqlDbType.Int, 20, "ProductCode"))
                       cmd11.Parameters.Add(New SqlParameter("@d3", SqlDbType.VarChar, 150, "ProductName"))
                       cmd11.Parameters.Add(New SqlParameter("@d4", SqlDbType.Float, 20, "Price"))
                       cmd11.Parameters.Add(New SqlParameter("@d5", SqlDbType.Float, 20, "SellPrice"))
                       cmd11.Parameters.Add(New SqlParameter("@d6", SqlDbType.VarChar, 150, "Category"))
                       cmd11.Parameters.Add(New SqlParameter("@d7", SqlDbType.Float, 20, "StockDate"))
                       cmd11.Parameters.Add(New SqlParameter("@d8", SqlDbType.Int, 20, "StockNo"))
                       cmd11.Parameters.Add(New SqlParameter("@d9", SqlDbType.VarChar, 20, "User_Name"))


                       cmd11.Parameters("@d1").Value = Val(txtStockID.Text)
                       cmd11.Parameters("@d2").Value = Val(txtProductCode.Text)
                       cmd11.Parameters("@d3").Value = txtProductName.Text
                       cmd11.Parameters("@d4").Value = CDbl(Val(txtPrice.Text))
                       cmd11.Parameters("@d5").Value = CDbl(Val(txtSellPrice.Text))
                       cmd11.Parameters("@d6").Value = cmbCategory.Text
                       cmd11.Parameters("@d7").Value = DateTimePicker1.Value
                       cmd11.Parameters("@d8").Value = Val(txtStockNo.Text)
                       cmd11.Parameters("@d9").Value = ToolStripStatusLabel2.Text

                       MessageBox.Show("تم التعديل بنجاح و اضافة باكية جديدة للصنف", "عملية التعديل", MessageBoxButtons.OK, MessageBoxIcon.Information)

                       If con1.State = ConnectionState.Open Then
                           con1.Close()
                       End If

                       con1.Open()
                       cmd11.ExecuteNonQuery()
                       con1.Close()
                       Exit Sub
                   End If
               End Using
           End Using

       End If

       Dim con = New SqlConnection(cs)
       con.Open()

       Dim cb1 As String = "update Stock set ProductName=@d3,Price=@d4,SellPrice=@d5,Category=@d6,
                           StockDate=@d7,StockNo=@d8,User_Name=@d9
                           where StockID=@d1"

       Dim cmd1 As New SqlCommand(cb1)

       cmd1.Connection = con

       cmd1.Parameters.Add(New SqlParameter("@d3", SqlDbType.VarChar, 150, "ProductName"))
       cmd1.Parameters.Add(New SqlParameter("@d4", SqlDbType.Float, 20, "Price"))
       cmd1.Parameters.Add(New SqlParameter("@d5", SqlDbType.Float, 20, "SellPrice"))
       cmd1.Parameters.Add(New SqlParameter("@d6", SqlDbType.VarChar, 150, "Category"))
       cmd1.Parameters.Add(New SqlParameter("@d7", SqlDbType.Date, 20, "StockDate"))
       cmd1.Parameters.Add(New SqlParameter("@d8", SqlDbType.Int, 20, "StockNo"))
       cmd1.Parameters.Add(New SqlParameter("@d9", SqlDbType.VarChar, 50, "User_Name"))
       cmd1.Parameters.Add(New SqlParameter("@d1", SqlDbType.Int, 20, "StockID"))


       cmd1.Parameters("@d3").Value = txtProductName.Text
       cmd1.Parameters("@d4").Value = CDbl(Val(txtPrice.Text))
       cmd1.Parameters("@d5").Value = CDbl(Val(txtSellPrice.Text))
       cmd1.Parameters("@d6").Value = cmbCategory.Text
       cmd1.Parameters("@d7").Value = DateTimePicker1.Value
       cmd1.Parameters("@d8").Value = Val(txtStockNo.Text)
       cmd1.Parameters("@d9").Value = ToolStripStatusLabel2.Text
       cmd1.Parameters("@d1").Value = Val(txtStockID.Text)


       cmd1.ExecuteReader()


       MessageBox.Show("تم التعديل بنجاح", "عملية التعديل", MessageBoxButtons.OK, MessageBoxIcon.Information)
       Save.Enabled = False
       Update_Record.Enabled = True
       Delete.Enabled = True
       DataGridView1.DataSource = Nothing
       autocomplete()

       txtSumTotal.Text = ""
       TextBox1.Text = ""
       txtStockNo.Text = ""
       TextBox4.Text = ""
       cmbCategory.Text = ""
       ComboBox1.Text = ""
       TextBox2.Text = ""
       txtStockID.Text = GenerateID("Stock", "StockID")

       If con.State = ConnectionState.Open Then
           con.Close()
       End If
       con.Close()


       ''''''''''''''
       fillCategory()
       fillProduct()


   End Sub

I intended to create the tables again instead of migration and investigate but i said asked after i perform that
Thanks in advance ........................

What I have tried:

I tried to restart the sql services and check TCP/IP in the sql configuration manager the IP2 node that is my IP address is exist and the TCp port is 1433

Posted 20-Dec-18 1:26am

amr aly

Updated 20-Dec-18 11:24am

v2

Add a Solution

Comments

CHill60 20-Dec-18 8:03am

Try removing the ;Trusted_Connection=True; in your connection string if you are using username + password, or remove User ID=amr;Password=****; if you are using a trusted connection

amr aly 20-Dec-18 8:14am

Thanks for fast replaying ...
I tried your suggestion but the same error after updating

Richard Deeming 20-Dec-18 8:53am

Try removing the port and network library from the connection string:

Public cs As String = "Data Source=KMA18214\SQLEXPRESS;Initial Catalog=Stock;Trusted_Connection=True;"

https://www.connectionstrings.com/sql-server/[^]

Also, since you're using SQL Express, make sure it's configured to accept remote connections:
Connecting to SQL Express Remotely // Josh Wright[^]

amr aly 20-Dec-18 9:52am

OK, I did what you suggested my connection string became as follow

Public cs As String = "Data Source=KMA18214\SQLEXPRESS;Initial Catalog=Stock;Trusted_Connection=True;" &
"Persist Security Info=True;User ID=amr;Password=****;"

But a new error occurred

The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following:  the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

in this textChanged

 Private Sub cmbCategory_TextChanged(sender As Object, e As EventArgs) Handles cmbCategory.TextChanged
        Dim cat As String = "select ProductCode,Category from ProductSold where ProductCode=@find and Category=@find1"
        Using conn As New SqlConnection(cs)
            conn.Open()
            Using cmd As New SqlCommand(cat)
                cmd.Connection = conn
                cmd.Parameters.Add(New SqlParameter("@find", SqlDbType.Int, 20, "ProductCode"))
                cmd.Parameters.Add(New SqlParameter("@find1", SqlDbType.VarChar, 100, "Category"))
                cmd.Parameters("@find").Value = Val(txtProductCode.Text)
                cmd.Parameters("@find1").Value = cmbCategory.Text
                rdr = cmd.ExecuteReader()

                If rdr.Read Then
                    'MessageBox.Show("الباكية تم التعامل عليها و لا يمكن تعديلها", "تعديل خاطئ", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    cmbCategory.Enabled = False
                    If Not rdr Is Nothing Then
                        rdr.Close()
                    End If

                    Exit Sub
                Else

                    cmbCategory.Enabled = True
                End If
            End Using
        End Using
    End Sub

Richard Deeming 20-Dec-18 9:56am

Did you set SQL Express up to allow remote connections over TCP/IP?

Configuring SQL Server 2016 Express On LAN For C# Connection String[^]

amr aly 20-Dec-18 10:03am

Of course yes, I configure it in my firewall

Richard Deeming 20-Dec-18 10:05am

You need to configure SQL Express too, not just your firewall.

amr aly 20-Dec-18 11:30am

I configured it on the SQL EXPRESS Server ...
I have seen this web site and more before in order to know how to configure SQL server
Thanks ......

Dave Kreskowiak 20-Dec-18 10:14am

You did it again. You're using both TrustedConnection and Username/Password. You can only use ONE of them.

Trusted Connection means you do not supply a username and password in the connection string, but since you still have to login to SQL Server, the connection will use the Windows account of the user that is running the application to authenticate to the server. No username and password is required.

If you supply a username and password in the connection string, you are automatically telling the connection to NOT use TrustedConnection. The username and password has to exist in the Security/Logins section of SQL Server and appropriate permissions assigned to those accounts for each database and object you want those accounts to have access to.

amr aly 20-Dec-18 11:28am

OK, My connection string became

"Data Source=KMA18214\SQLEXPRESS;Initial Catalog=Stock;" &                  "User ID=amr;Password=****;"

and another error occurred

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Dave Kreskowiak 20-Dec-18 11:52am

The error says it's trying to connect over Shared Memory. Shared Memory only works if the SQL Server and the application connecting to it are running on the same machine.

If you're trying to connect to SQL Server over TCP/IP (network), then you have to configure SQL Server to listen on the network. Did you go through what's laid out in this article: Connecting to SQL Express Remotely // Josh Wright[^]

amr aly 20-Dec-18 12:18pm

Yes the SQL server and vs 2105 on the same machine i'm debugging the application in order to build it to client PC but the scenario on the client side is
One server for SQL server 2012 and two PCs have my application and connect to the SQL server .... By the way i have gone through the article ' Connecting to SQL Express Remotely // Josh Wright'

Dave Kreskowiak 20-Dec-18 12:36pm

OK, now you have to make sure that user exists in the Security of SQL Server and has appropriate permissions to the database.

amr aly 20-Dec-18 13:27pm

In security node beneath the database in amr login properties has in server roles (public and sysadmin) and user mapping (on Stock database) has (db_owner and public)
And on the Stock database itself in the security node i added the user and permission is- in membership (db_owner)

Dave Kreskowiak 20-Dec-18 13:45pm

Does the user exist in the Security, Logins node just under the server? I'm not looking at the database yet, and neither is your connection. It's trying to get into the server itself at this point. Once it connected, then it can move on to connecting to the database.

amr aly 20-Dec-18 14:54pm

Of course yes ,I tried another application with another database and it works well on the same machine and the same SQL 2012 server ..... And I created a new database with new tables(with another name) and the last error still persist

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

1 solution

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)

amr aly · Accepted Answer · 2018-12-20T11:24:00

Finally I fixed this issue , Firstly I want presenting my warm regards to every one that participate in this thread to help me to find the solution specially

Dave Kreskowiak

... Many thanks
And i want to share how my problem has fixed to help any one has this issue
I go to the SQL log in this pass

C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Log

and look at the end of the file and found that this line

Could not connect because the maximum number of '5' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]

then i go to this link
Error while connecting to SQL Server – “Could not connect because the maximum number of ‘1’ user connections has already been reached.” – SQL BI / Data Access Technologies[^] then my problem solved .....
Many thanks guys