Imports System.Data.SqlClient
Public Class Manage_RawMaterial_New
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strConnString, strSQL As String
Dim dtAdapter As SqlDataAdapter
Dim ds As DataSet
Dim rating As Integer = 0
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Function KeyStore() As String
Dim iMAX As Integer = 1
Dim intNum As String = Nothing
Dim Count As Integer
Dim cmd As New SqlCommand
strSQL = "SELECT MAX(RIGHT(RM_ID,4)) AS RM_ID FROM RawMaterial_New"
objCmd = New SqlCommand(strSQL, objConn)
objConn.Open()
Count = objCmd.ExecuteScalar()
objConn.Close()
If IsDBNull(Count) = True Then
txtRM_AddID.Text = "RM" & "000" & iMAX
Else
iMAX = Count + 1
End If
If iMAX <= 9 Then
intNum = "RM" & "000" & iMAX
ElseIf iMAX <= 99 Then
intNum = "RM" & "00" & iMAX
ElseIf iMAX <= 999 Then
intNum = "RM" & "0" & iMAX
ElseIf iMAX <= 9999 Then
intNum = "RM" & iMAX
End If
Return intNum
End Function
Private Sub AddAutoID()
ConnectDB()
Dim strKey As String
Dim result As Integer
strKey = KeyStore()
strSQL = "INSERT INTO RawMaterial_New (RM_ID) VALUES ('" & strKey & "')"
objCmd = New SqlCommand(strSQL, objConn)
objConn.Open()
result = objCmd.ExecuteNonQuery()
objConn.Close()
MsgBox("บันทึกสำเร็จ")
End Sub
Protected Sub btnRM_Add_Click(sender As Object, e As EventArgs) Handles btnRM_Add.Click
AddAutoID()
End Sub
End Class
นี่คือโค้ดที่ผมเขียน
Tag : .NET, Ms SQL Server 2008, Web (ASP.NET), VB.NET
Additional information: Violation of PRIMARY KEY constraint 'PK_RawMaterial_New'. Cannot insert duplicate key in object 'dbo.RawMaterial_New'.
View Detail : "Violation of PRIMARY KEY constraint 'PK_RawMaterial_New'. Cannot insert duplicate key in object 'dbo.RawMaterial_New'." & vbCrLf & "The statement has been terminated."
Function SQLExecute(_sql As String) As Integer
Dim objConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString())
objConn.Open()
Dim objCmd As SqlCommand = New SqlCommand(strSQL, objConn)
Dim Count As Integer= objCmd.ExecuteScalar()
objConn.Close()
End Function
Private Sub AddAutoID()
Dim Count As Integer = SQLExecute("SELECT MAX(RIGHT(RM_ID,4)) AS RM_ID FROM RawMaterial_New")
Dim iMAX As Integer = IIf(Count <= 0, 1, Count + 1)
If iMAX < 10000 Then
If SQLExecute(strSQL = "INSERT INTO RawMaterial_New (RM_ID) VALUES ('" & (IIf(Count <= 0, 1, Count + 1)).ToString("RM0000") & "')") > 0 Then
MsgBox("บันทึกสำเร็จ")
Else
MsgBox("บันทึกไม่สำเร็จ เศร้าใจสิ้นดี")
End If
Else
MsgBox("มันเกิน 9999 ไปแล้วนะ")
End If
End Sub
ผมลองนำมาปรับใช้ พอผมลบข้อมูลออกจาก DB ทั้งหมด แล้วเพิ่มข้อมูลเข้าไปใหม่ตั้งแต่แรก
มัน error แบบนี้ครับ ==> Additional information: Conversion from type 'DBNull' to type 'Integer' is not valid.
Code (VB.NET)
Imports System.Data.SqlClient
Public Class Manage_RawMaterial_New
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strConnString, strSQL As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Private Sub AddAutoID()
Dim intNum As String = Nothing
Dim Count As Integer
ConnectDB()
strSQL = "SELECT MAX(RIGHT(RM_ID,4)) AS RM_ID FROM RawMaterial_New"
objCmd = New SqlCommand(strSQL, objConn)
objConn.Open()
Count = objCmd.ExecuteScalar()
objConn.Close()
Dim iMAX As Integer = IIf(Count <= 0, 1, Count + 1)
If iMAX < 10000 Then
Dim result As Integer
strSQL = "INSERT INTO RawMaterial_New (RM_ID) VALUES ('" & iMAX.ToString("RM0000") & "')"
objCmd = New SqlCommand(strSQL, objConn)
objConn.Open()
result = objCmd.ExecuteNonQuery()
objConn.Close()
MsgBox("บันทึกสำเร็จ")
Else
MsgBox("ข้อมูลเต็ม")
End If
End Sub
Protected Sub btnRM_Add_Click(sender As Object, e As EventArgs) Handles btnRM_Add.Click
AddAutoID()
End Sub
End Class
Load balance : Server 05
