tmccrank
asked on
Difficulty adding a PK column to a DataTable
Hi,
How do I add a PK column (auto-incrementing) to my DataTable? I'm getting the error: "System.Data.NoNullAllowed Exception: Column 'dcUserAnswerID' does not allow nulls" with the way my code is now.
The error is toward the end of my code posted below.
========================== ========== ===
For Each i As RepeaterItem In rptQuestions.Items
For Each c In i.Controls
If TypeOf c Is RadioButtonList Then
Dim rbl As RadioButtonList
rbl = DirectCast(c, RadioButtonList)
'check that user input value matches corresponding value in DataTable
If rbl.SelectedItem.Text.ToSt ring.ToLow er() = Convert.ToString(dtCorrect Answers.Ro ws(itemCou nt)("Answe rs")).ToLo wer() Then
'...increment score by 1
intCorrect += 1
'instantiate a new row to add values to...
drUserAnswer = dtUserAnswerTable.NewRow
'...and add columns with values, then add to the DataTable's rows collection
drUserAnswer("dcUserName") = strAuthUserName.Remove(0, strAuthUserName.LastIndexO f("\") + 1)
drUserAnswer("dcAnswerID") = dtCorrectAnswers.Rows(item Count).Ite m("Answers ID")
drUserAnswer("dcModuleID") = Request.QueryString(0)
drUserAnswer("dcQuestionsI D") = dtCorrectAnswers.Rows(item Count).Ite m("Questio nsID")
Else
'same code as above, but without incrementing the score
'instantiate a new row...
drUserAnswer = dtUserAnswerTable.NewRow
'...and add columns with values, then add to the DataTable's rows collection
drUserAnswer("dcUserName") = strAuthUserName.Remove(0, strAuthUserName.LastIndexO f("\") + 1)
drUserAnswer("dcAnswerID") = dtAnswers.Rows(itemCount). Item("Answ ersID")
drUserAnswer("dcModuleID") = Request.QueryString(0)
drUserAnswer("dcQuestionsI D") = dtCorrectAnswers.Rows(item Count).Ite m("Questio nsID")
End If
itemCount += 1
Response.Write(drUserAnswe r("dcUserA nswerID")) <-- ERROR HERE
'add rows to the DataTable's rows collection
dtUserAnswerTable.Rows.Add (drUserAns wer)
End If
Next c
Next i
========================== ========== =====
The function for creating the DataTable:
Public Function CreateDataTable() As DataTable
'create DataTable
Dim dtUserAnswerTable As New DataTable
Dim dcUserName As New DataColumn
Dim dcAnswerID As New DataColumn
Dim dcModuleID As New DataColumn
Dim dcQuestionsID As New DataColumn
'create Primary Key column
Dim dcUserAnswerID As New DataColumn
'specify seed and increment
dcUserAnswerID.AutoIncreme nt = True
dcUserAnswerID.AutoIncreme ntSeed = 1
dcUserAnswerID.AutoIncreme ntStep = 1
'add PK column
dtUserAnswerTable.Columns. Add("dcUse rAnswerID" , System.Type.GetType("Syste m.Int32"))
'create PK column
dtUserAnswerTable.PrimaryK ey = New DataColumn() {dtUserAnswerTable.Columns ("dcUserAn swerID")}
'create and add other columns to DataTable
dtUserAnswerTable.Columns. Add("dcUse rName", System.Type.GetType("Syste m.String") )
dtUserAnswerTable.Columns. Add("dcAns werID", System.Type.GetType("Syste m.Int32"))
dtUserAnswerTable.Columns. Add("dcMod uleID", System.Type.GetType("Syste m.Int32"))
dtUserAnswerTable.Columns. Add("dcQue stionsID", System.Type.GetType("Syste m.Int32"))
Return dtUserAnswerTable
End Function
Thanks for any help!
Jens
How do I add a PK column (auto-incrementing) to my DataTable? I'm getting the error: "System.Data.NoNullAllowed
The error is toward the end of my code posted below.
==========================
For Each i As RepeaterItem In rptQuestions.Items
For Each c In i.Controls
If TypeOf c Is RadioButtonList Then
Dim rbl As RadioButtonList
rbl = DirectCast(c, RadioButtonList)
'check that user input value matches corresponding value in DataTable
If rbl.SelectedItem.Text.ToSt
'...increment score by 1
intCorrect += 1
'instantiate a new row to add values to...
drUserAnswer = dtUserAnswerTable.NewRow
'...and add columns with values, then add to the DataTable's rows collection
drUserAnswer("dcUserName")
drUserAnswer("dcAnswerID")
drUserAnswer("dcModuleID")
drUserAnswer("dcQuestionsI
Else
'same code as above, but without incrementing the score
'instantiate a new row...
drUserAnswer = dtUserAnswerTable.NewRow
'...and add columns with values, then add to the DataTable's rows collection
drUserAnswer("dcUserName")
drUserAnswer("dcAnswerID")
drUserAnswer("dcModuleID")
drUserAnswer("dcQuestionsI
End If
itemCount += 1
Response.Write(drUserAnswe
'add rows to the DataTable's rows collection
dtUserAnswerTable.Rows.Add
End If
Next c
Next i
==========================
The function for creating the DataTable:
Public Function CreateDataTable() As DataTable
'create DataTable
Dim dtUserAnswerTable As New DataTable
Dim dcUserName As New DataColumn
Dim dcAnswerID As New DataColumn
Dim dcModuleID As New DataColumn
Dim dcQuestionsID As New DataColumn
'create Primary Key column
Dim dcUserAnswerID As New DataColumn
'specify seed and increment
dcUserAnswerID.AutoIncreme
dcUserAnswerID.AutoIncreme
dcUserAnswerID.AutoIncreme
'add PK column
dtUserAnswerTable.Columns.
'create PK column
dtUserAnswerTable.PrimaryK
'create and add other columns to DataTable
dtUserAnswerTable.Columns.
dtUserAnswerTable.Columns.
dtUserAnswerTable.Columns.
dtUserAnswerTable.Columns.
Return dtUserAnswerTable
End Function
Thanks for any help!
Jens
ASKER
Is there another way to do this? I'm not quite following your logic (why merge tables? I only have one.)
I would prefer to stick closely to the code I have already as well.
Thanks!
I would prefer to stick closely to the code I have already as well.
Thanks!
ASKER
It seems that the problem is figuring out why I'm getting a "System.Data.NoNullAllowed Exception: Column 'dcUserAnswerID' does not allow nulls" exception.
That column is supposed to be auto-incrementing...
That column is supposed to be auto-incrementing...
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Yup, that did it!
Thanks Nauman.
Jens
Thanks Nauman.
Jens
use following code
DataTable oDt= new DataTable("Search");
oDt.Columns.Add("RESID", System.Type.GetType("Syste
oDt.Columns.Add("RESName",
oDt.Columns.Add("Column1",
oDt.Columns.Add("Column2",
oDt.PrimaryKey = new DataColumn[]{oDt.Columns["
oDt.Rows.Add(new object[]{1,"Res #1","Date 1","Status 1"});
oDt.Rows.Add(new object[]{2,"Res #2","",""});
oDt.Rows.Add(new object[]{3,"Res #3","Date 2","Status 2"});
oDt.Rows.Add(new object[]{4,"Res #4","Date 3","Status 3"});
oDt.Rows.Add(new object[]{7,"Res #7","Date 3","Status 7"});
oDt.AcceptChanges();
// Set up a another DataTable that I will use to Merge
DataTable oDtMoreColors= new DataTable("Search");
oDtMoreColors.Columns.Add(
oDtMoreColors.Columns.Add(
oDtMoreColors.PrimaryKey = new DataColumn[]{oDtMoreColors
oDtMoreColors.Rows.Add(new
oDtMoreColors.Rows.Add(new
oDtMoreColors.Rows.Add(new
oDtMoreColors.Rows.Add(new
oDtMoreColors.Rows.Add(new
oDtMoreColors.AcceptChange
// Create a DataSet so I can merge the DataTable objects
DataSet oDs = new DataSet("MergeTable");
oDs.Tables.Add(oDt);
oDs.Merge(oDtMoreColors);
DataGrid1.DataSource = oDs.Tables[0];
DataGrid1.DataBind();
Thanks