update i sql db med SqlCommand
HejJeg håber at der er nogen som kan hjælpe mig med et problem som jeg ikke længere kan overskue. Jeg har en simpel sql database ved navn "test" med et felt "ID" som er af typen char. Denne db indeholder 10 rækker med værdier fra 0-9. Den kaster en exception og kan ikke opdaterer.
Er der nogen som kan hjælpe mig med dette problem som efter hånden har ædt en go del af min tid :-(
Jeg ønsker at kunne opdatere disse vha af følgende kode:
<%@ Page Language="VB" AutoEventWireup="True" Debug="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Guestlogin management</title>
<script language="VB" runat="server">
Dim myConnection As SqlConnection
Sub Page_Load(Src As Object, E As EventArgs)
myConnection = New SQLConnection("server=XXXXXXX;uid=xxxxxxxxxx;pwd=xxxxxxxxx;database=COMMONxxxxxxx")
If Not IsPostBack Then
BindGrid()
End If
End Sub
Sub ItemsGrid_Edit(sender As Object, e As DataGridCommandEventArgs)
' Set the EditItemIndex property to the index of the item clicked
' in the DataGrid control to enable editing for that item. Be sure
' to rebind the DateGrid to the data source to refresh the control.
grd.EditItemIndex = e.Item.ItemIndex
BindGrid()
End Sub
Sub ItemsGrid_Cancel(sender As Object, e As DataGridCommandEventArgs)
' Set the EditItemIndex property to -1 to exit editing mode.
' Be sure to rebind the DateGrid to the data source to refresh
' the control.
grd.EditItemIndex = -1
BindGrid()
End Sub
Sub ItemsGrid_Update(sender As Object, e As DataGridCommandEventArgs)
Dim updateCmd As String = "UPDATE test SET ID = @ID WHERE ID = @ID"
Dim myCommand As SqlCommand = New SqlCommand(updateCmd, myConnection)
myCommand.Parameters.Add(New SQLParameter( "@ID", SqlDbType.Int))
Dim cols() As String = {"@ID"}
Dim numCols As Integer = e.Item.Cells.Count
Dim i As Integer
Dim colvalue As String
Dim txtBox As Textbox
For i = 2 To numCols-1
txtBox = e.Item.Cells(i).Controls(0)
colvalue = txtBox.Text
If (i<6 And colvalue = "")
Message.InnerHtml = "ERROR: Null values not allowed for " & "ID and Country"
Message.Style("color") = "red"
Exit Sub
End If
myCommand.Parameters(cols(i-1)).Value = colvalue
Next i
' Append the last field, converting true/false values to 0/1.
txtBox=e.Item.Cells(numCols-1).Controls(0)
' Test whether the data was updated, and display the appropriate message to the user.
Try
' Connect to the database and update the information.
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Updated.</b><br>"+myCommand.CommandText()
grd.EditItemIndex = -1
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists" & " with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not update record," _
& " please ensure the fields are correctly filled out."
Message.Style("color") = "red"
End If
End Try
' Close the connection.
myCommand.Connection.Close()
' Rebind the DataGrid to show the updated information.
BindGrid()
End Sub
Public Sub BindGrid()
' Set the data source and bind to the Data Grid control.
myConnection = New SQLConnection("server=xxxxxx;uid=xxxxxxx;pwd=xxxxxxxx;database=xxxxxxxx")
Dim myCommand As SqlDataAdapter = New SqlDataAdapter("select * from test", myConnection)
Dim ds As DataSet= New DataSet()
myCommand.Fill(ds)
grd.DataSource = ds
grd.DataBind()
End Sub
</script>
</head>
<body style="font: 10pt verdana">
<form runat="server">
<span id="Message" EnableViewState="false"
style="font:arial 11pt;" runat="server"/><p>
<asp:DataGrid id="grd"
HorizontalAlign="center"
OnEditCommand="ItemsGrid_Edit"
OnCancelCommand="ItemsGrid_Cancel"
OnUpdateCommand="ItemsGrid_Update"
AutoGenerateColumns="false"
runat="server">
<HeaderStyle BackColor="#aaaadd">
</HeaderStyle>
<Columns>
<asp:EditCommandColumn
EditText="Edit"
CancelText="Cancel"
UpdateText="Update"
HeaderText="">
<ItemStyle Wrap="False">
</ItemStyle>
<HeaderStyle Wrap="False">
</HeaderStyle>
</asp:EditCommandColumn>
<asp:BoundColumn HeaderText="ID"
DataField="ID"/>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>
