problem with datagrid

nour mohamed

hi!

in my datagrid there are multiple records that needed to be inserted to my database. my problem is that it would be inserted to only one row and it would depend on the id.

for example:

- the datagrid will have 3 columns (student id, subject, grade) the rows will be (2006-01, Math, 88), (2006-01, English, 87).

- the database has 5 columns (student id, math, english, science, history)

-- i need to insert the grade in their corresponding columns based on student id. how could i do this

i am using vb.net2003

i'm really confused right now and any help is appreciated..

Yu Guo �C MSFT

Hi, nour mohamed,

A Pivot Table could be helpful for your scenario,

so, I wrote a sample to show how to get your table pivoted.

Code Snippet

Private dg As DataGrid

Private dg2 As DataGrid

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

datatable = New DataTable()

datatable.Columns.Add("id")

datatable.Columns.Add("subject")

datatable.Columns.Add("grade")

datatable.Rows.Add("2006-01", "Math", "88")

datatable.Rows.Add("2006-01", "English", "87")

datatable.Rows.Add("2006-02", "English", "87")

datatable.Rows.Add("2006-02", "Math", "87")

Dim dataview As New DataView(datatable)

Dim id As String = "2006-01"

dataview.RowFilter = "id='" + id + "'" 'set your filter option here

Dim forPivot As DataTable = dataview.ToTable()

forPivot.Columns.Remove("id")

Dim temp As DataTable = PivotTable(forPivot) 'the temp table contains the data you need

dg = New DataGrid()

dg.Width = 300

dg.Height = 150

dg.DataSource = datatable

Me.Controls.Add(dg)

dg2 = New DataGrid()

dg2.Width = 300

dg2.Height = 150

dg2.DataSource = temp

dg2.Top = 150

Me.Controls.Add(dg2)

'......

Dim comm As New SqlCommand("insert into yourtable (id, math, english, science, history) values (@id, @math, @english, @science, @history)", conn)

comm.Parameters.Add("@id", SqlDbType.VarChar).Value = id

If temp.Columns("Math") IsNot Nothing Then

comm.Parameters.Add("@math", SqlDbType.VarChar).Value = temp.Rows(0).ItemArray.GetValue(temp.Columns.IndexOf("Math"))

Else

comm.Parameters.Add("@math", SqlDbType.VarChar).Value = ""

End If

'......

Try

conn.Open()

comm.ExecuteNonQuery()

conn.Close()

Catch

conn.Close()

End Try

End Sub

Private Function PivotTable(ByVal source As DataTable) As DataTable

Dim dest As New DataTable("Pivoted" + source.TableName)

dest.Columns.Add(" ")

For Each r As DataRow In source.Rows

dest.Columns.Add(r(0).ToString())

dest.Rows.Add(dest.NewRow())

For c As Integer = 0 To dest.Columns.Count - 1

If c = 0 Then

dest.Rows(r)(0) = source.Columns(r + 1).ColumnName

Else

dest.Rows(r)(c) = source.Rows(c - 1)(r + 1)

End If

dest.AcceptChanges()

Return dest

End Function

End Class

Hopes this helps,

Regards

problem with datagrid

Re: Windows Forms Data Controls and Databinding problem with datagrid