Link to home
Create AccountLog in
Avatar of tmccrank
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.NoNullAllowedException: 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.ToString.ToLower() = Convert.ToString(dtCorrectAnswers.Rows(itemCount)("Answers")).ToLower() 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.LastIndexOf("\") + 1)
                        drUserAnswer("dcAnswerID") = dtCorrectAnswers.Rows(itemCount).Item("AnswersID")
                        drUserAnswer("dcModuleID") = Request.QueryString(0)
                        drUserAnswer("dcQuestionsID") = dtCorrectAnswers.Rows(itemCount).Item("QuestionsID")

                    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.LastIndexOf("\") + 1)
                        drUserAnswer("dcAnswerID") = dtAnswers.Rows(itemCount).Item("AnswersID")
                        drUserAnswer("dcModuleID") = Request.QueryString(0)
                        drUserAnswer("dcQuestionsID") = dtCorrectAnswers.Rows(itemCount).Item("QuestionsID")

                    End If

                    itemCount += 1

                    Response.Write(drUserAnswer("dcUserAnswerID"))  <-- ERROR HERE

                    'add rows to the DataTable's rows collection
                    dtUserAnswerTable.Rows.Add(drUserAnswer)

                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.AutoIncrement = True
        dcUserAnswerID.AutoIncrementSeed = 1
        dcUserAnswerID.AutoIncrementStep = 1

        'add PK column
        dtUserAnswerTable.Columns.Add("dcUserAnswerID", System.Type.GetType("System.Int32"))

        'create PK column
        dtUserAnswerTable.PrimaryKey = New DataColumn() {dtUserAnswerTable.Columns("dcUserAnswerID")}

        'create and add other columns to DataTable
        dtUserAnswerTable.Columns.Add("dcUserName", System.Type.GetType("System.String"))
        dtUserAnswerTable.Columns.Add("dcAnswerID", System.Type.GetType("System.Int32"))
        dtUserAnswerTable.Columns.Add("dcModuleID", System.Type.GetType("System.Int32"))
        dtUserAnswerTable.Columns.Add("dcQuestionsID", System.Type.GetType("System.Int32"))

        Return dtUserAnswerTable

    End Function


Thanks for any help!
Jens
Avatar of SystemExpert
SystemExpert
Flag of United States of America image

Hi,

use following code

DataTable oDt= new DataTable("Search");
oDt.Columns.Add("RESID", System.Type.GetType("System.Int32"));
oDt.Columns.Add("RESName", System.Type.GetType("System.String"));
oDt.Columns.Add("Column1",System.Type.GetType("System.String"));
oDt.Columns.Add("Column2",System.Type.GetType("System.String"));

oDt.PrimaryKey = new DataColumn[]{oDt.Columns["RESID"]};
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("RESID", System.Type.GetType("System.Int32"));
oDtMoreColors.Columns.Add("RESName", System.Type.GetType("System.String"));
oDtMoreColors.PrimaryKey = new DataColumn[]{oDtMoreColors.Columns["RESID"]};
oDtMoreColors.Rows.Add(new object[]{1,"Res #1"});
oDtMoreColors.Rows.Add(new object[]{2,"Res #2"});
oDtMoreColors.Rows.Add(new object[]{4,"Res #4"});
oDtMoreColors.Rows.Add(new object[]{5,"Res #5"});
oDtMoreColors.Rows.Add(new object[]{6,"Res #6"});
oDtMoreColors.AcceptChanges();

// 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
Avatar of tmccrank
tmccrank

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!
It seems that the problem is figuring out why I'm getting a "System.Data.NoNullAllowedException: Column 'dcUserAnswerID' does not allow nulls" exception.

That column is supposed to be auto-incrementing...
ASKER CERTIFIED SOLUTION
Avatar of nauman_ahmed
nauman_ahmed
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Yup, that did it!

Thanks Nauman.

Jens