Object Reference not set to an instance of object

[gopal2006]

Hi all

I am editing the data in the table employee through form 'employee master'. I am getting the error "Object Reference not set to an instance of object" . I am not able to make it out why I am getting this error here although i have initialized the value. I am enlcosing my code below.

txtempname.Enabled = True
cbxtype.Enabled = True
dt = DataSet21.Tables("employee")
dr = dt.Rows.Find(emp_lid)
emp_lid = dr("emp_lid")
dr.BeginEdit()
dr(0) = emp_lid
dr(1) = txtempname.Text
dr(2) = cbxtype.Text
dr.EndEdit()
OleDbDataAdapter1.Update(DataSet21, "employee")
txtempname.Enabled = False
cbxtype.Enabled = False
MsgBox("employee updated")

Can anyone tell me the correction in the above code?

 

[vis781]

On what line are you getting this error?

 

[jelo]

Heyy, it is not easy to figure out the exception in this code. Just an advice to check if u have filled the dataset with the appropriate table

 

[gopal2006]

Object Reference

I get the error on the line
emp_lid = dr("emp_lid")

 

[vis781]

Well i must admit that is where i thought you were having the trouble. So what i need to know is what are you trying t accomplish with this line of code. From what i can gather emp_lid is a column in your datatable. So why are you trying to assign emp_lid which is clearly nothing to a column in your datatable?

 

[gopal2006]

to retrieve employee id

I am trying to retrieve employee id with that statement. Employee id field name is "emp_lid" in sql database. Here also I have declared emp_lid as string below the windows forms generated code. I want to initialze that one to the datarow where "emp_lid" is there. So I am confused here .Guid me. Suggest an alternative code.

 

[vis781]

First step is to step through your code and make sure that dr actually equals something on this line.....

dr = dt.Rows.Find(emp_lid)

my guess is that dr wil equal nothing. If so then it's not returning a datarow. So there must be something wrong with what you are searching for in the find method.

 

[gopal2006]

corrected

Mr vis781

I checked the thing you have told me. Error has gone.Thanks. Now I have another problem. I am enclosing my code below


Private Sub edit()
dt = DataSet21.Tables("employee")

Dim conn AsNew OleDb.OleDbConnection("User ID=sa;Data Source=COMP8;Tag with column collation when possible=False;Initial Catalog=defectlog;Use Procedure for Prepare=1;Auto Translate=True;Persist Security Info=False;Provider=SQLOLEDB.1;Workstation ID=COMP8;Use Encryption for Data=False;Packet Size=4096")
Dim dtcmd AsNew OleDb.OleDbCommand
Dim str1$
txtempname.Focus()
Dim empid AsInteger
Try
conn.Open()
Dim str AsString = ("select emp_lid from employee where emp_sname='" & txtempname.Text & "'")
dtcmd = New OleDb.OleDbCommand(str, conn)
empid = CInt(dtcmd.ExecuteScalar)
dtcmd.CommandText = str
conn.Close()
Catch ex As Exception
MsgBox("excetion" & ex.ToString)
EndTry
Try
dtcmd.Connection = conn
dtcmd.CommandType = CommandType.Text
str1 = "update employee set emp_lid='" & empid & "', emp_sname='" & txtempname.Text & "', emp_stype='" & cbxtype.SelectedItem & "' where emp_lid='" & empid & "'"
dtcmd.CommandText = str1
conn.Open()
dtcmd.ExecuteNonQuery()
conn.Close()
Catch ex As OleDb.OleDbException
MsgBox("excpetion" & ex.ToString)
EndTry
EndSub


Here I want to update the Table "employee". Employee ID is store in table. Which is not shown in the form. It is "emp_lid". When I update the table after clicking on the button 'edit' in the form and save it by clicking 'save' button,the record having employee id '0' is updated. I have checked this one by inserting breakpoint at the line
Dim str AsString = ("select emp_lid from employee where emp_sname='" & txtempname.Text & "'") and running the application. I observed the process in autos window (debug ) . First it is fetching the correct employee ID. After coming to the line conn.close() it goes to the line flag=2(which i have declared in the edit button click event. The fucntion edit is also called in the edit click event. This edit class is also called in the frmsave class.(I have created class frmsave for saving of records. i.e.for click event of save button in the form) I have also enclosed the code for edit & save click event and also their classes i have created for them.

Edit click event

Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
Call edit()
btnsave.Enabled = True
txtempname.Enabled = True
cbxtype.Enabled = True
flag = 2
EndSub

edit class shown on top of the page

save click event

PrivateSub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Call frmsave()


save class

PrivateSub frmsave()
Dim conn AsNew OleDb.OleDbConnection("User ID=sa;Data Source=COMP8;Tag with column collation when possible=False;Initial Catalog=defectlog;Use Procedure for Prepare=1;Auto Translate=True;Persist Security Info=False;Provider=SQLOLEDB.1;Workstation ID=COMP8;Use Encryption for Data=False;Packet Size=4096")
Dim dtcmd AsNew OleDb.OleDbCommand
Dim str1$

emp_lid = ctr
If flag = 1 Then
Try
dtcmd.Connection = conn
dtcmd.CommandType = CommandType.Text
If txtempname.Text = "" Or cbxtype.Text = "SELECT ONE" Then
MsgBox("No fields can be left blank")
If txtempname.Text = "" Then
txtempname.Focus()
EndIf
If cbxtype.Text = "SELECT ONE" Then
cbxtype.Focus()
EndIf
Else
str1 = "insert into employee values('" & emp_lid & "','" & txtempname.Text & "','" & cbxtype.Text & "')"

dtcmd.CommandText = str1
conn.Open()
dtcmd.ExecuteNonQuery()
conn.Close()
MsgBox("Employee Added to the Table")
lbempname.Items.Add(txtempname.Text)
txtempname.Enabled = False
cbxtype.Enabled = False
txtempname.Text = ""
cbxtype.Text = "SELECT ONE"
OleDbDataAdapter1.Fill(DataSet21)
EndIf
Catch errs As OleDb.OleDbException
MsgBox("exception" & errs.ToString)
EndTry
ElseIf flag = 2 Then
MsgBox("employee updated")
ElseIf flag = 3 Then
)
MsgBox("employee deleted")
txtempname.Text = ""
cbxtype.Text = "SELECT ONE"
EndIf
EndSub

Just Suggest me in which part I am doing the misatake and suggest changes. It will be really helpful because I am doing the project assigned to me by my Company. It may be a minor thing for you. But I am a fresher in the programming side. So I am stuck. Please help me out

 

[vis781]

I have to say, that is just far too much code fo rme to read through whilst i am at work.Can you say exactly what is going wrong and what you are expecting it to do? i.e

Is it updating the database?
Are you getting any exceptions?

At a glance the problem could be in your SQL statement. You've used string concatenation which is very difficult to read. Try using 'parameterised queries' instead.
Also i take it emp_lid is your primary key in your database. So in the WHERE clause of the update command you use this column to find the row you want to update. So what is the value of empid when the code executes?
Use a messagebox to display the command text of the query to see if it is correct...

Messagebox.show(dtcmd.commandtext)

 

[gopal2006]

Got the solution

hi Mr vsi
There was no exception thrown in this code. After chacking thoroughly the code I found that I had mad a mistake in the line

Dim str AsString = ("select emp_lid from employee where emp_sname='" & txtempname.Text & "'")

Here I should have written lbempname.text instead of txtempname.text.I corrected this. Now the data is stored in the table properly. As suggested by you I feel it is convenient and easy to use parametrized queries instead of conactenation. Since i have not used parametrized queries earlier can you just show some parametrized queries in my lines of code. Thanks

 

[vis781]

No problem, Using parameterised queries is not much more complicated, it just requires that a few extra lines of code are added.
So this part of your code....

Dim str AsString = ("select emp_lid from employee where emp_sname='" & txtempname.Text & "'")
dtcmd = New OleDb.OleDbCommand(str, conn)
empid = CInt(dtcmd.ExecuteScalar)
dtcmd.CommandText = str

Would now look like this....

Dim str AsString = ("SELECT emp_lid from employee WHERE [COLOR=red]emp_sname= ?)[/COLOR]
dtcmd = New OleDb.OleDbCommand(str, conn)
empid = CInt(dtcmd.ExecuteScalar)
dtcmd.CommandText = str
[COLOR=red]dtcmd.parameters.add("@tempname",oledbtype.varwchar,255,"tempname").value = texttempname.text
[/COLOR]

I have hilighted the parts you need to change/add. The question mark is now the placeholder for your parameter value. Remember that when using parameters. The first argument is the name you are giving to that parameter, the second argument is the type of data the parameter is using as oledbtype. The third argument is the size of the field, and the last argument is the name of the field in your datatable/database. The parameters must be added in the order that they appear in your query. Hope this helps.

 

[gopal2006]

Thanks

Thank You very much for the valuable information