防止空白行从asp.net中的gridview插入到sqlserver中 [英] Preventing blank row insert into sqlserver from a gridview in asp.net
本文介绍了防止空白行从asp.net中的gridview插入到sqlserver中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Dim conn As SqlConnection = " constr").ConnectionString. ToString())
' Dim conn为SqlConnection =新建SqlConnection(ConfigurationManager.ConnectionStrings("ConStr").ConnectionString.ToString())
受保护的 子 Page_Load( ByVal 发件人目标 对象， ByVal e 句柄 如果 不是 Page.IsPostBack 然后
SetInitialRow()
结束 如果
结束 子
私有 Sub SetInitialRow()
Dim dt As New DataTable()
Dim dr As DataRow = 没什么
dt.Columns.Add(新建 DataColumn(" ， GetType (字符串))))
dt.Columns.Add(新建 DataColumn(" ， GetType (字符串))))
dt.Columns.Add(新建 DataColumn(" ， GetType (字符串))))
dt.Columns.Add(新建 DataColumn(" ， GetType (字符串))))
dt.Columns.Add(新建 DataColumn(" ， GetType (字符串))))
dt.Columns.Add(新建 DataColumn(" ， GetType (字符串))))
dr = dt.NewRow()
dr(" )= 1
dr(" )= 字符串 .空的
dr(" )= 字符串 .空的
dr(" )= 字符串 .空的
dr(" )= 字符串 .空的
dr(" )= 字符串 .空的
dt.Rows.Add(dr)
' 将数据表存储在ViewState中
ViewState(" )= dt
Gridview1.DataSource = dt
Gridview1.DataBind()
结束 子
私有 Sub SetPreviousData()
Dim rowIndex As 整数 = 0
如果 ViewState(" )不是 没事 然后
Dim dt As DataTable = DirectCast (ViewState (" )，数据表)
如果 dt.Rows.Count> 0 然后
对于 i As 整数 = 1 收件人 dt.Rows.Count- 1
Dim box1 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 1 ).FindControl(" )，文本框)
Dim box2 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 2 ).FindControl(" )，文本框)
Dim box3 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 3 ).FindControl(" )，文本框)
Dim box4 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 4 ).FindControl(" )，文本框)
Dim box5 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 5 ).FindControl(" )，文本框)
box1.Text = dt.Rows(i)(" ).ToString()
box2.Text = dt.Rows(i)(" ).ToString()
box3.Text = dt.Rows(i)(" ).ToString()
box4.Text = dt.Rows(i)(" ).ToString()
box5.Text = dt.Rows(i)(" ).ToString()
box5.Text = Convert.ToString(Convert.ToInt32(box3.Text)* Convert.ToInt32(box4.Text))
rowIndex + = 1
下一步
结束 如果
结束 如果
结束 子
私有 子 AddNewRowToGrid()
Dim rowIndex As 整数 = 0
如果 ViewState(" )不是 没事 然后
Dim dtCurrentTable As DataTable = DirectCast (ViewState (" )，数据表)
Dim drCurrentRow As DataRow = 什么都没有
如果 dtCurrentTable.Rows.Count> 0 然后
对于 i As 整数 = 1 收件人 dtCurrentTable.Rows.Count
' 提取文本框值
Dim box1 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 1 ).FindControl(" )，文本框)
Dim box2 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 2 ).FindControl(" )，文本框)
Dim box3 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 3 ).FindControl(" )，文本框)
Dim box4 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 4 ).FindControl(" )，文本框)
Dim box5 As TextBox = DirectCast (Gridview1 .Rows(rowIndex).Cells( 5 ).FindControl(" )，文本框)
drCurrentRow = dtCurrentTable.NewRow()
drCurrentRow(" )= i + 1
drCurrentRow(" )= box1.Text
drCurrentRow(" )= box2.Text
drCurrentRow(" )= box3.Text
drCurrentRow(" )= box4.Text
drCurrentRow(" )= box5.Text
rowIndex + = 1
下一步
' 向DataTable添加新行
dtCurrentTable.Rows.Add(drCurrentRow)
' 将当前数据存储到ViewState
ViewState(" )= dtCurrentTable
' 将网格与当前数据重新绑定
Gridview1.DataSource = dtCurrentTable
Gridview1.DataBind()
结束 如果
其他
Response.Write(" )
结束 如果
' 设置回发之前的数据
SetPreviousData()
结束 子
受保护的 子 ButtonAdd_Click( ByVal 发件人目标 对象， ByVal e 结束 子
受保护的 Sub InsertAll_Click( ByVal 发件人目标 对象， ByVal e Dim 查询 As 字符串 = "
Dim cmd As 新建 SqlCommand(查询， conn)
cmd.Parameters.AddWithValue(" ，txtTrnNo.Text)
cmd.Parameters.AddWithValue(" ，txtTrnDt.Text)
cmd.Parameters.AddWithValue(" ，txtRemarks.Text)
cmd.ExecuteNonQuery()
conn.Close()
Response.Write(Gridview1.Rows.Count)
conn.Open()
对于 i As 整数 = 0 到 Gridview1.Rows.Count- 1
Dim str As 字符串 = "
Dim cmdInsertAll As 新建 SqlCommand(str， conn)
cmdInsertAll.Parameters.AddWithValue(" ，txtTrnNo.Text)
cmdInsertAll.Parameters.AddWithValue(" ，> DirectCast (Gridview1.Rows(i).FindControl(" )，文本框).文本)
cmdInsertAll.Parameters.AddWithValue(" ， DirectCast (Gridview1.Rows(i).FindControl(" )，文本框).文本)
cmdInsertAll.Parameters.AddWithValue(" ， DirectCast (Gridview1.Rows(i).FindControl(" )，文本框).文本)
cmdInsertAll.Parameters.AddWithValue(" ， DirectCast (Gridview1.Rows(i).FindControl(" )，文本框).文本)
cmdInsertAll.Parameters.AddWithValue(" ， DirectCast (Gridview1.Rows(i).FindControl(" )，文本框).文本)
cmdInsertAll.ExecuteNonQuery()
下一步
conn.Close()
Response.Write(" )
' lblMsg.Text =已插入数据...";
结束 子
*当我想向数据库中插入一个空白行(假设我创建了3行，但填充了2行)时，发生了错误，但在其他情况下成功插入了数据，请给我一个解决方案. /div>
只是一个建议，您的列可以为空吗?如果没有，您将无法插入空白值.除了主键(显然)，希望这可以有所帮助.
Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString.ToString())
'Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConStr").ConnectionString.ToString())
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
SetInitialRow()
End If
End Sub
Private Sub SetInitialRow()
Dim dt As New DataTable()
Dim dr As DataRow = Nothing
dt.Columns.Add(New DataColumn("RowNumber", GetType(String)))
dt.Columns.Add(New DataColumn("Column1", GetType(String)))
dt.Columns.Add(New DataColumn("Column2", GetType(String)))
dt.Columns.Add(New DataColumn("Column3", GetType(String)))
dt.Columns.Add(New DataColumn("Column4", GetType(String)))
dt.Columns.Add(New DataColumn("Column5", GetType(String)))
dr = dt.NewRow()
dr("RowNumber") = 1
dr("Column1") = String.Empty
dr("Column2") = String.Empty
dr("Column3") = String.Empty
dr("Column4") = String.Empty
dr("Column5") = String.Empty
dt.Rows.Add(dr)
'Store the DataTable in ViewState
ViewState("CurrentTable") = dt
Gridview1.DataSource = dt
Gridview1.DataBind()
End Sub
Private Sub SetPreviousData()
Dim rowIndex As Integer = 0
If ViewState("CurrentTable") IsNot Nothing Then
Dim dt As DataTable = DirectCast(ViewState("CurrentTable"), DataTable)
If dt.Rows.Count > 0 Then
For i As Integer = 1 To dt.Rows.Count - 1
Dim box1 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(1).FindControl("txt_ITEMCODE"), TextBox)
Dim box2 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(2).FindControl("txt_NAME"), TextBox)
Dim box3 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(3).FindControl("txt_QTY"), TextBox)
Dim box4 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(4).FindControl("txt_RATE"), TextBox)
Dim box5 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(5).FindControl("txt_AMNT"), TextBox)
box1.Text = dt.Rows(i)("Column1").ToString()
box2.Text = dt.Rows(i)("Column2").ToString()
box3.Text = dt.Rows(i)("Column3").ToString()
box4.Text = dt.Rows(i)("Column4").ToString()
box5.Text = dt.Rows(i)("Column5").ToString()
box5.Text = Convert.ToString(Convert.ToInt32(box3.Text) * Convert.ToInt32(box4.Text))
rowIndex += 1
Next
End If
End If
End Sub
Private Sub AddNewRowToGrid()
Dim rowIndex As Integer = 0
If ViewState("CurrentTable") IsNot Nothing Then
Dim dtCurrentTable As DataTable = DirectCast(ViewState("CurrentTable"), DataTable)
Dim drCurrentRow As DataRow = Nothing
If dtCurrentTable.Rows.Count > 0 Then
For i As Integer = 1 To dtCurrentTable.Rows.Count
'extract the TextBox values
Dim box1 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(1).FindControl("txt_ITEMCODE"), TextBox)
Dim box2 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(2).FindControl("txt_NAME"), TextBox)
Dim box3 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(3).FindControl("txt_QTY"), TextBox)
Dim box4 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(4).FindControl("txt_RATE"), TextBox)
Dim box5 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(5).FindControl("txt_AMNT"), TextBox)
drCurrentRow = dtCurrentTable.NewRow()
drCurrentRow("RowNumber") = i + 1
drCurrentRow("Column1") = box1.Text
drCurrentRow("Column2") = box2.Text
drCurrentRow("Column3") = box3.Text
drCurrentRow("Column4") = box4.Text
drCurrentRow("Column5") = box5.Text
rowIndex += 1
Next
'add new row to DataTable
dtCurrentTable.Rows.Add(drCurrentRow)
'Store the current data to ViewState
ViewState("CurrentTable") = dtCurrentTable
'Rebind the Grid with the current data
Gridview1.DataSource = dtCurrentTable
Gridview1.DataBind()
End If
Else
Response.Write("ViewState is null")
End If
'Set Previous Data on Postbacks
SetPreviousData()
End Sub
Protected Sub ButtonAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
AddNewRowToGrid()
End Sub
Protected Sub InsertAll_Click(ByVal sender As Object, ByVal e As EventArgs)
conn.Open()
Dim query As String = "INSERT INTO TRN_MAST(TRN_NO,TRN_DT,REMARKS) VALUES (@TRN_NO,@TRN_DT,@REMARKS)"
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@TRN_NO", txtTrnNo.Text)
cmd.Parameters.AddWithValue("@TRN_DT", txtTrnDt.Text)
cmd.Parameters.AddWithValue("@REMARKS", txtRemarks.Text)
cmd.ExecuteNonQuery()
conn.Close()
Response.Write(Gridview1.Rows.Count)
conn.Open()
For i As Integer = 0 To Gridview1.Rows.Count - 1
Dim str As String = "INSERT INTO DTL_MAST(TRN_NO,ITEM_CODE,NAME,QTY,RATE,AMNT) VALUES (@TRN_NO,@ITEM_CODE,@NAME,@QTY,@RATE,@AMNT)"
Dim cmdInsertAll As New SqlCommand(str, conn)
cmdInsertAll.Parameters.AddWithValue("@TRN_NO", txtTrnNo.Text)
cmdInsertAll.Parameters.AddWithValue("@ITEM_CODE", DirectCast(Gridview1.Rows(i).FindControl("txt_ITEMCODE"), TextBox).Text)
cmdInsertAll.Parameters.AddWithValue("@NAME", DirectCast(Gridview1.Rows(i).FindControl("txt_NAME"), TextBox).Text)
cmdInsertAll.Parameters.AddWithValue("@QTY", DirectCast(Gridview1.Rows(i).FindControl("txt_QTY"), TextBox).Text)
cmdInsertAll.Parameters.AddWithValue("@RATE", DirectCast(Gridview1.Rows(i).FindControl("txt_RATE"), TextBox).Text)
cmdInsertAll.Parameters.AddWithValue("@AMNT", DirectCast(Gridview1.Rows(i).FindControl("txt_AMNT"), TextBox).Text)
cmdInsertAll.ExecuteNonQuery()
Next
conn.Close()
Response.Write("<Script>alert(' Record inserted')</script>")
'lblMsg.Text = "Data inserted...";
End Sub
* when i want to insert a blank row(suppose,i created 3 rows but filled 2) into db, error occurred but in other cases data inserted successfully, please give me a solution.
解决方案
Just a suggestion, Are you columns nullable?? If there not you will not be able to insert blank values. Apart from the primary key(obviously) Hopefully this helps in some way.
这篇关于防止空白行从asp.net中的gridview插入到sqlserver中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
