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
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)
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