unable to add a record

[alaric]

[RESOLVED] unable to add a record

Hi

Dont know what I'm doing wrong here (Im following an exercise in a book and adapt it to my own needs) but it keeps coming up with an error:
"Object reference not set to an instance of an object." when I try to Add a record. it stops at the point where I set the DataRow

I think it has something to do with my dataset. But im to "green" to figure out whats going on.

what I do (and works as I want it):

on my form I have a datagrid which is filled with a single column.

[SIZE=2]
dtDans = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2][SIZE=2]dtDans.Clear()
[SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'-- Create the data adapter and fill the data table
[/COLOR][/SIZE][SIZE=2]daDans = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter("SELECT DansID, dans FROM TDanssoort", strCnn)
daDans.Fill(dtDans)
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] oexpData [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlException
MsgBox(oexpData.Message)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]dgDans.DataSource = dtDans
[/SIZE][/SIZE][/SIZE][/SIZE]

so far so good. Now (offcourse) I want to add a record to this datagrid. I do/try this with with the content of a textbox

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] drDans [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dsDans [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cbDans [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommandBuilder
[/SIZE][SIZE=2]
dsDans.Clear()
drDans = dsDans.Tables("TDanssoort").NewRow
mdrCursistIndiv.BeginEdit()
drDans("Dans") = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtAddDans.Text
mdrCursistIndiv.EndEdit()
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]dsDans.Tables("TDanssoort").Rows.Add(drDans)
daDans.InsertCommand = cbDans.GetInsertCommand
daDans.Update(dsDans, "TDanssoort")
dsDans.Tables("TDanssoort").AcceptChanges()
daDans.InsertCommand.Connection.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'refreshing the grid by reloading it
[/COLOR][/SIZE][SIZE=2]loadOnderdelen()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE]

now Im lost

already spend more than 3 nights on this (didnt wanna give up ) but now it's time for asking,
so if someone can put me on the track...

thanks

 

[TechGnome]

LEt me guess... it cr@ps out on this line:
drDans = dsDans.Tables("TDanssoort").NewRow
Right?

That's because the line right above it,
dsDans.Clear()

clears the dataset.... afterwhich your datatable no longer exists. This results in the error when trying to access the table.

-tg

 

[alaric]

Hi TechGnome,
thx for your reply.
you're right on the "stop point"

I removed the line (as you suggested)
ds.clear
but still it gives me the error:
"Object reference not set to an instance of an object."
on the same line:
drDans = dsDans.Tables("TDanssoort").NewRow

here's my local:
cbDans | Nothing
drDans | Nothing
dsDans | System.Data.Dataset

by the way; since Im new to vb.net (did VBA in the far past). is my approach right for the problem? or are there other, more preferred methods. I have three books about vb.net, but can't make this up.

thx again

 

[vis781]

Hi, sorry to butt in tg, but have you made sure that your datatable's name is actually TDanssoort my guess is that it probably isn't. To make sure check the actual name of the datatable.Or instead you could try referencing it by index rather than by name...

drDans = dsDans.Tables(0).NewRow

rememeber that the index is zero based so if it's the only datatable in the dataset it's index will be 0.


Tg, what happend to your gnome picture, i have to say that i kinda liked it.

 

[alaric]

Hi

thx for your replay
when I change the way yoy suggested I get the error:
Cannot find table 0.

Im sure about the tablename ("TDanssoort) because filling the Datatable and set is as a datasource for de dg works fine.

But, I think I made it far to complex and just made an insert SQl command and executed it. then reload the datatable, which is connected to the dg.

Correct me if Im doing this wrong, or if a better way is available. Im still learning and want to learn the proper methods for specific jobs

thanks

 

[vis781]

have you added the table to the dataset? Not entirely sure what you mean. Did you use a SELECT command to fill the datatable.

 

[TechGnome]

Dim drDans As DataRow
Dim dsDans AsNew DataSet
Dim cbDans As SqlClient.SqlCommandBuilder
' ------ IS THERE CODE HERE???? ------ 
dsDans.Clear()
drDans = dsDans.Tables("TDanssoort").NewRow
mdrCursistIndiv.BeginEdit()
drDans("Dans") = Me.txtAddDans.Text
mdrCursistIndiv.EndEdit()

See my comment in the code above....

-tg

 

[wintermj]

Not sure if this is any help. But this is the call i use to add a new row (or multiple depending on the loop) to an SQL srv 2000 db:

Imports System
Imports System.Data
Imports System.Data.SqlClient '[B]you may need an oledb ref here?[/B]
Imports Microsoft.VisualBasic
Imports System.IO
Imports System.Drawing.Drawing2D
Imports CrystalDecisions.CrystalReports

Private Sub Ins_New_Row(ByVal txt_GRP_NO As String, ByVal txt_WEEK As String, ByVal txt_DATE As String, ByVal txt_INST As String, ByVal txt_SECT As String, ByVal txt_COMMENTS As String, ByVal txt_TOTAL As String, ByVal txt_WWO As String, ByVal txt_ABIL As String, ByVal txt_INT As String, ByVal txt_WORKR As String, ByVal txt_COND As String, ByVal txt_APP As String, ByVal txt_LNAME As String, ByVal txt_FNAME As String, ByVal txt_NO As String, ByVal txt_LEARNER_ID As String)

        Dim newRow As DataRow = DataSet1.Tables("WS_WR").NewRow()

        newRow("LEARNER_ID") = txt_LEARNER_ID
        newRow("C_NUM") = txt_NO
        newRow("FNAME") = txt_FNAME
        newRow("LNAME") = txt_LNAME
        newRow("WR_A") = txt_APP
        newRow("WR_C") = txt_COND
        newRow("WR_W") = txt_WORKR
        newRow("WR_I") = txt_INT
        newRow("WR_AFTW") = txt_ABIL
        newRow("WR_WWO") = txt_WWO
        newRow("WR_TOTAL") = txt_TOTAL
        newRow("WR_COMMENTS") = txt_COMMENTS
        newRow("WR_SECT") = txt_SECT
        newRow("INSTRUCTOR") = txt_INST
        newRow("DATE_WC") = txt_DATE
        newRow("W_NUM") = txt_WEEK
        newRow("G_NUM") = txt_GRP_NO
        If cb_1.Checked = True Then
            newRow("WR_SIG") = "1"
        Else
            newRow("WR_SIG") = "0"
        End If
        DataSet1.Tables("WS_WR").Rows.Add(newRow)

        DBConnection.Open()
        Try
            Learner_DA.Update(DataSet1, "WS_WR")
        Catch ex As SqlException
            MessageBox.Show(ex.Message)
        End Try
        DBConnection.Close()

    End Sub

 

[DavidT_macktool]

Looking at my add code it would look like this for you...

'define the table
dtDans = dsDans.Tables("TDanssoort")
'define the row
Dim drDans As DataRow
'establish a new row for the table
drDans = dtDans.NewRow()

'set values for the new row
drDans ("Column Name") = "something"
drDans ("Column Name2") = "something else"
drDans ("Column Name3") = "something entirely different"
drDans ("Column Name4") = "something the same as something else"

'add the new row to the table
dtDans.Rows.Add(drDans)

'then call an update procedure.


I don't see where dtDans gets assigned to a table, so where does the new row go?

That is the nutshell version of Code I have used many times to add new rows to a table.

Hope it helps.

 

[vis781]

I think thats what it is. You haven't added the datatable to the dataset. So when you try to reference it it's not there. Using DavidT_MackTool's code above will work for you. But if you really want to use a dataset then you have to add the datatable to it. Then your code will execute correctly. DavidT_MackTool is also correct in saying that you haven't declared that DtDans is assigned to anything, this is because you have declared it locally. If you were to make a class level declaration then you would'nt need to reference it every time you wanted to use it.

Add this line of code to add the datatable to yuor dataset.

DsDans.tables.add(DtDans)

 

[alaric]

Hi there (all of you)

tg, about your question. There's no code in there. So this is where you point to (i think) i dont fill the Dt in the ds.

this made me think again about the way I try to do something. You all have far more experience than I have, so please correct me if Im wrong (I like to learn this the right way)

situation is;
step 1. I use u datatable to be the source of a datagrid (see my first part, 1st piece of code), this works fine

than i would like to add a record;
two (or more choices, but I cant think of them right now),
1. create a ds, fill it, add record and update the dataAdapter. and start at step 1 again
OR
2. use a simple INSERT SQl statement with variables. update the table in the database, and reload as described in step 1.

[SIZE=2][COLOR=#0000ff]
Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection(strCnn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] insCmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand("Insert INTO " & strTable & " (" & strColumn & ") VALUES ('" & strVal & "')", conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]conn.Open()
insCmd.ExecuteNonQuery()
conn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
Console.WriteLine(ex.Message)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]

second method saves a lot of codewriting and does the job I want it to do, BUT (thats my question)
is this the right way to approach this (do I mis something here). I mean now its only one record in one table (containing 2 columns), what oif there's more? offcourse I would like easy code(who doesn't), but above all .. I like performance and solid methods.

for now I solved it by doing method two.

Hope you can give me some advise, which I will use in my application.

THANKS!

 

[TechGnome]

The reason I asked about the code: You've declared the DS as a local var to the sub... and never populate it... that's why the DS.Tables gives you and error... there are no tables in it.

As for adding new records.... I typicaly use SQL Server and stored procedures, so I use option 3, which wasn't given. In direct response to your question though... it depends. Am I doing a massive insert, IE several inserts in bunches (like as if I were importing a file into the DB) or am I inserting records as needed (like if the user said "New Customer")? For mass loading I would probably just add to the DT and then issue the CommitChanges to the database... for the second one, I'd proly issue the INSERT SQL manualy, then reload the DS/DT.

-tg

EDIT: Look at my sig, there's two articles on ADO.NET, give them the once over, esp the second - it shows how to use parameters in your queries to avoid SQL Injection problems.(and the dreaded apostrophe error)

 

[alaric]

thanx tg,

your explanation is clear to me.
About the articles in your signature, I read them already a while ago . they are good articles!