ASP.NET GridView + Insert Record + 連續既ID 係個 Primary Key 既 Field 上

 

   default.aspx
 
   <asp:Table ID="Table1" runat="server">
 
      <asp:TableRow>
 
         <asp:TableCell>
            Team :
         </asp:TableCell>
         <asp:TableCell>
            <asp:TextBox ID="TextBox1" runat="server" />
         </asp:TableCell>
      </asp:TableRow>
 
      <asp:TableRow>
         <asp:TableCell>
            League :
         </asp:TableCell>
         <asp:TableCell>
            <asp:DropDownList ID="DropDownList1" runat="server" />
         </asp:TableCell>
      </asp:TableRow>
 
      <asp:TableRow>
         <asp:TableCell ColumnSpan="2">
            <asp:Button runat="server" ID="SubBtn" Text="Button" />
         </asp:TableCell>
      </asp:TableRow>
 
   </asp:Table>
 
   <asp:Label ID="Label1" runat="server" Text="" />
 

 

   default.aspx.vb
 
   Private Sub Page_Init()
 
      Dim Submit_Btn As Button = TryCast(FindControl("SubBtn"), Button)
      AddHandler Submit_Btn.Click, AddressOf Submit_Team
 
   End Sub
 
   Private Sub Submit_Team()
 
      Dim Team_ID As String = ""
      Dim Team_Name As TextBox = TryCast(FindControl("TextBox1"), TextBox)
      Dim League_ID As DropDownList = TryCast(FindControl("DropDownList1"), DropDownList)
 
      Dim ConnString As String = "Data Source=.\SqlExpress; Initial Catalog=Soccer; Integrated Security=SSPI"
      Dim Conn As SqlConnection = New SqlConnection(ConnString)
      Conn.Open()
 
      Dim SQL_Statement As String = "SELECT Count(*) AS NewID FROM dbo.Team"
      Dim cmd As SqlCommand = New SqlCommand(SQL_Statement, Conn)
 
      Dim dr As SqlDataReader = cmd.ExecuteReader()
 
      While (dr.Read())
         Team_ID = (dr("NewID") + 1).ToString.Trim
      End While
 
      Conn.Close()
      cmd.Dispose()
 
      Dim Conn2 As SqlConnection = New SqlConnection(ConnString)
      Conn2.Open()
 
      Dim SqlString1 As String = "INSERT INTO dbo.Team (Team_ID, Team_Name, League_ID) " & _
                     "VALUES(@Team_ID, @Team_Name, @League_ID)"
 
      Dim cmd2 As SqlCommand = New SqlCommand(SqlString1, Conn2)
      cmd2.Parameters.AddWithValue("@Team_ID", Team_ID.Trim)
      cmd2.Parameters.AddWithValue("@Team_Name", Team_Name.Text)
      cmd2.Parameters.AddWithValue("@League_ID", League_ID.SelectedValue)
 
      cmd2.ExecuteNonQuery()
 
      Conn2.Close()
      cmd2.Dispose()
 
      Label1.Text = "Team_ID : " & Team_ID.Trim & _
                     " / Team_Name : " & Team_Name.Text & _
                     " / League_ID : " & League_ID.SelectedValue
 
   End Sub
 

 
註: 上述條件 – Database Table 既 Team_ID 上需要全部連續及由1開始. 原因係 用 Program Generate 出來既 Team_ID 係 SQL Count 做既..