Click here to Skip to main content
15,947,958 members
Please Sign up or sign in to vote.
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
Updated 20-Dec-18 11:24am
v2
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[^]

1 solution

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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900