下記のｺｰﾄﾞですがエラーが発生した場合Protected Sub On_Sql_Updatedの
Catch ex As Exception
OtherProcessSucceeded = False
End Try
のEnd Tryの部分で
「文字列データまたはバイナリ データが切り捨てられます。 ステートメントは終了されました」となり画面が表示されなくなってしまいます。
HPでこのエラーを元に検索してみたのですが、どう対処したらいいのかわかりません。
また、UPDATEの時ドロップダウンで性別を選択するとサイズが性別ごとに
男→M,L,LL
女→S,M,Lというように表示をわけたいのですがどのようにしたらいいのでしょうか？
この文↓を追加したいのですが、DropDownListにDropDownListは宣言されていませんというエラーが出て
使い方がわからないのですがよろしくお願いします。
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If DropDownList1.text = "<選択>" Then
DropDownList2.Items.Clear()
ElseIf DropDownList1.text = "男" Then
DropDownList2.Items.Clear()
DropDownList2.Items.Add("M")
DropDownList2.Items.Add("L")
DropDownList2.Items.Add("LL")
ElseIf DropDownList1.text = "女" Then
DropDownList2.Items.Clear()
DropDownList2.Items.Add("S")
DropDownList2.Items.Add("M")
DropDownList2.Items.Add("L")
End If
End Sub
<%@Page Language="VB" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Diagnostics" %>
<%@ 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">
<script runat="server">
Dim fieldNames As New System.Collections.Generic.List(Of String)()
Dim oldValues As New System.Collections.Generic.List(Of String)()
Dim newValues As New System.Collections.Generic.List(Of String)()
Sub CustomerDetail_ItemInserted(ByVal sender As Object, _
ByVal e As DetailsViewInsertedEventArgs)
CustomersView.DataBind()
End Sub
Sub CustomerDetail_ItemInserting(ByVal sender As Object, _
ByVal e As DetailsViewInsertEventArgs)
For i As Integer = 0 To e.Values.Count - 1
If e.Values(i) IsNot Nothing Then
e.Values(i) = Server.HtmlEncode(e.Values(i).ToString())
End If
Next
End Sub
Sub CustomerDetail_ItemDeleted(ByVal sender As Object, _
ByVal e As DetailsViewDeletedEventArgs)
CustomersView.DataBind()
End Sub
Sub CustomerDetail_ItemUpdating(ByVal sender As Object, _
ByVal e As DetailsViewUpdateEventArgs)
For Each entry As DictionaryEntry In e.OldValues
fieldNames.Add(DirectCast(entry.Key, String))
oldValues.Add(DirectCast(entry.Value, String))
Next
For Each name As String In fieldNames
newValues.Add(DirectCast(e.NewValues(name), String))
Next
End Sub
Public DBAdd As New SqlCommand
Public transaction2 As DbTransaction
'
Protected Sub On_Sql_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)
Dim command As DbCommand
Dim connection As DbConnection
Dim transaction As DbTransaction
'UPDATEコマンドUpdateCommand="UPDATE 名前 SET 名前 = @名前, 性別 = @性別, サイズ = @サイズ WHERE ID = @ID"
command = e.Command
'接続の宣言
connection = command.Connection
'接続を開く
connection.Open()
'開いた接続にトランザクションをする宣言
transaction = connection.BeginTransaction()
command.Transaction = transaction
End Sub
Protected Sub On_Sql_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
Dim command As DbCommand
Dim connection As DbConnection
Dim transaction As DbTransaction
Dim OtherProcessSucceeded As Boolean = True
'UPDATEコマンドUpdateCommand="UPDATE 名前 SET 名前 = @名前, 性別 = @性別, サイズ = @サイズ WHERE ID = @ID"
command = e.Command
connection = command.Connection
'トランザクションをする宣言
transaction = command.Transaction
Dim rowCount As Integer
Try
rowCount = e.AffectedRows
If rowCount <> 0 Then
'If rowCount = -1 Then
Dim insertQuery As String = "Insert Into 履歴 (変更項目, 変更内容, 変更時間) " & "values (@変更項目, @変更内容, @変更時間)"
Dim sqlCom As New SqlCommand(insertQuery, DirectCast(connection, SqlConnection))
With sqlCom
.Transaction = DirectCast(transaction, SqlTransaction)
.Parameters.Add("@変更項目", SqlDbType.VarChar)
.Parameters.Add("@変更内容", SqlDbType.VarChar)
.Parameters.Add("@変更時間", SqlDbType.DateTime)
End With
Dim i As Integer
For i = 0 To oldValues.Count - 1
If oldValues(i) <> newValues(i) Then
sqlCom.Parameters("@変更項目").Value = fieldNames(i)
If newValues(i) = Nothing Then
sqlCom.Parameters("@変更内容").Value = ""
Else
sqlCom.Parameters("@変更内容").Value = newValues(i)
End If
sqlCom.Parameters("@変更時間").Value = Now
sqlCom.ExecuteNonQuery()
End If
Next
Else
OtherProcessSucceeded = False
End If
Catch ex As Exception
OtherProcessSucceeded = False
End Try
If (OtherProcessSucceeded) Then
transaction.Commit()
Label2.Text = "更新しました!"
Else
transaction.Rollback()
Label2.Text = "更新できませんでした"
Return
End If
connection.Close()
CustomersView.DataBind()
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If DropDownList1.text = "<選択>" Then
DropDownList2.Items.Clear()
ElseIf DropDownList1.text = "男" Then
DropDownList2.Items.Clear()
DropDownList2.Items.Add("M")
DropDownList2.Items.Add("L")
DropDownList2.Items.Add("LL")
ElseIf DropDownList1.text = "女" Then
DropDownList2.Items.Clear()
DropDownList2.Items.Add("S")
DropDownList2.Items.Add("M")
DropDownList2.Items.Add("L")
End If
End Sub
</script>
<html >
<head id="Head1" runat="server">
<title>DetailsView Example</title>
</head>
<body>
<form id="Form1" runat="server">
<h3>DetailsView Example</h3>
<table cellspacing="10">
<tr>
<td>
<asp:GridView ID="CustomersView" DataSourceID="Customers"
AutoGenerateColumns="False"
DataKeyNames="ID" runat="server">
<HeaderStyle BackColor="Blue" ForeColor="White" />
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="名前"
HeaderText="名前" />
</Columns>
</asp:GridView>
</td>
<td valign="top">
<asp:DetailsView ID="CustomerDetail" DataSourceID="Details"
AutoGenerateRows="False"
AutoGenerateInsertButton="True"
AutoGenerateEditButton="True"
AutoGenerateDeleteButton="True"
EmptyDataText="No records."
DataKeyNames="ID"
OnItemInserted="CustomerDetail_ItemInserted"
OnItemInserting="CustomerDetail_ItemInserting"
OnItemDeleted="CustomerDetail_ItemDeleted"
OnItemUpdating="CustomerDetail_ItemUpdating"
runat="server">
<HeaderStyle BackColor="Navy" ForeColor="White" />
<RowStyle BackColor="White" />
<AlternatingRowStyle BackColor="LightGray" />
<EditRowStyle BackColor="LightCyan" />
<Fields>
<asp:TemplateField HeaderText="ID" InsertVisible="False">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="名前">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server"
Text='<%# Bind("名前") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" MaxLength="5"
Text='<%# Bind("名前") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("名前") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="性別">
<EditItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("性別") %>'></asp:Label>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
SelectedValue='<%# Bind("性別") %>' OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem><選択></asp:ListItem>
<asp:ListItem>男</asp:ListItem>
<asp:ListItem>女</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBox ID="TextBox2" runat="server"
Text='<%# Bind("性別") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("性別") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="サイズ">
<EditItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("サイズ") %>'></asp:Label>
<asp:DropDownList ID="DropDownList2" runat="server"
SelectedValue='<%# Bind("サイズ") %>'>
<asp:ListItem>S</asp:ListItem>
<asp:ListItem>M</asp:ListItem>
<asp:ListItem>L</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBox ID="TextBox3" runat="server"
Text='<%# Bind("サイズ") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("サイズ") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="Customers" runat="server"
ConnectionString=
"<%$ ConnectionStrings:project_jobConnectionString2%>"
SelectCommand="SELECT ID, 名前, 性別, サイズ FROM 名前">
</asp:SqlDataSource>
<asp:SqlDataSource ID="Details"
ConnectionString=
"<%$ ConnectionStrings:project_jobConnectionString2%>"
runat="server"
SelectCommand="SELECT * FROM 名前
WHERE (ID = @ID)"
DeleteCommand="DELETE FROM 名前
WHERE ID = @ID"
InsertCommand="INSERT INTO 名前 (
名前, 性別, サイズ)
VALUES (@名前, @性別, @サイズ)"
UpdateCommand="UPDATE 名前 SET 名前 = @名前, 性別 = @性別, サイズ = @サイズ WHERE ID = @ID"
onupdated="On_Sql_Updated" onupdating="On_Sql_Updating">
<SelectParameters>
<asp:ControlParameter ControlID="CustomersView"
Name="ID" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="ID" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="名前" Type="String" />
<asp:Parameter Name="性別" Type="String" />
<asp:Parameter Name="サイズ" Type="String" />
<asp:Parameter Name="ID" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ID" Type="String" />
<asp:Parameter Name="名前" Type="String" />
<asp:Parameter Name="性別" Type="String" />
<asp:Parameter Name="サイズ" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:Label ID="MessageLabel" runat="server" Text="Label"></asp:Label>
<asp:Label id="Label2" runat="server" Text="" />
</form>
</body>
</html>
