Need help with DataTable /and DataAdapter

sg48 · March 23rd, 2010, 10:31 AM

I am creating datatable and adding rows in a loop commented as
"populate datatable"

I then try to use the datatable when I am adding parameters to a sql
command. I get the error message "no row at position 0"

I am new to datatables/adapters and am sure my syntax and indexing
is incorrect. Thanks in advance. - Steve

Protected Sub UpdateIssues()
'If get here there are 5 or less selected issues
'prepare dataset
Dim i As Integer
Dim Conn As New SqlConnection(SqlDataSource1.ConnectionString)

Dim adapterUpdateIssues As SqlDataAdapter
Dim IssuesDataTable As New DataTable
IssuesDataTable.Columns.Add("Issue", GetType(String))

'--------------------------------------------
' populate datatable
'--------------------------------------------

Dim SelectedItemIndex As Integer
SelectedItemIndex = 0
For i = 0 To CheckBoxListIssues.Items.Count - 1
If CheckBoxListIssues.Items(i).Selected = True Then
Dim IssueRow As DataRow = IssuesDataTable.NewRow
IssueRow("Issue") = CheckBoxListIssues.Items(i).Text
SelectedItemIndex = SelectedItemIndex + 1
End If
Next
'----------------------------------------------
' datatable populated
'----------------------------------------------
Dim cmdUpdate As New SqlCommand("procUpdateIssues", Conn)

cmdUpdate.Parameters.AddWithValue("@Issue1", IssuesDataTable.Rows(0).ItemArray(0))
cmdUpdate.Parameters.AddWithValue("@Issue2", IssuesDataTable.Rows(1).ItemArray(0))
cmdUpdate.Parameters.AddWithValue("@Issue3", IssuesDataTable.Rows(2).ItemArray(0))
cmdUpdate.Parameters.AddWithValue("@Issue4", IssuesDataTable.Rows(3).ItemArray(0))
cmdUpdate.Parameters.AddWithValue("@Issue5", IssuesDataTable.Rows(4).ItemArray(0))
cmdUpdate.Parameters.AddWithValue("@adid", Session("adid"))

adapterUpdateIssues = New SqlDataAdapter(cmdUpdate)
adapterUpdateIssues.UpdateCommand = cmdUpdate
adapterUpdateIssues.Update(IssuesDataTable)

End Sub

om_prakash · March 24th, 2010, 03:23 AM

please check the example for datatable

Code:

Public Class Form1 Inherits System.Windows.Forms.Form

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click
Dim Table1 As DataTable
Table1 = New DataTable("Customers")
'creating a table named Customers
Dim Row1, Row2, Row3 As DataRow
'declaring three rows for the table
Try
Dim Name As DataColumn = New DataColumn("Name")
'declaring a column named Name
Name.DataType = System.Type.GetType("System.String")
'setting the datatype for the column
Table1.Columns.Add(Name)
'adding the column to table
Dim Product As DataColumn = New DataColumn("Product")
Product.DataType = System.Type.GetType("System.String")
Table1.Columns.Add(Product)
Dim Location As DataColumn = New DataColumn("Location")
Location.DataType = System.Type.GetType("System.String")
Table1.Columns.Add(Location)

Row1 = Table1.NewRow()
'declaring a new row
Row1.Item("Name") = "Reddy"
'filling the row with values. Item property is used to set the field value.
Row1.Item("Product") = "[COLOR=#009900! important][COLOR=#009900! important]Notebook[/COLOR][/COLOR]"
'filling the row with values. adding a product
Row1.Item("Location") = "Sydney"
'filling the row with values. adding a location
Table1.Rows.Add(Row1)
'adding the completed row to the table
Row2 = Table1.NewRow()
Row2.Item("Name") = "Bella"
Row2.Item("Product") = "[COLOR=#009900! important][COLOR=#009900! important]Desktop[/COLOR][/COLOR]"
Row2.Item("Location") = "Adelaide"
Table1.Rows.Add(Row2)
Row3 = Table1.NewRow()
Row3.Item("Name") = "Adam"
Row3.Item("Product") = "[COLOR=#009900! important][COLOR=#009900! important]PDA[/COLOR][/COLOR]"
Row3.Item("Location") = "Brisbane"
Table1.Rows.Add(Row3)
Catch
End Try

Dim ds As New DataSet()
ds = New DataSet()
'creating a dataset
ds.Tables.Add(Table1)
'adding the table to dataset 
DataGrid1.SetDataBinding(ds, "Customers")
'binding the table to datagrid
End Sub

End Class