update comamnd nullreferenceexception 1

[holidayIT]

PLEASE PLEASE PLEASE someone help me.

i have a windows app that pulls data from a sql server box. i get all of the data fine. what i have setup is the user finds the record, presses a modify button, and another form opens as a dialog window, and the user types in the data they want to update the record. when they press done, it closes the form and passes the text entered back to the previous form (that shows records and has the dataadapter).

i then have it set to set the updatecommand and execute. but i get the nullreferenceexception error on my dataadapter.

i've tried all kinds of combinations and searched everywhere online, but i cannot find anything at all. please someone help.

Here is my load event code (just the part that matters):

myConn.Open()

myDA = New SqlClient.SqlDataAdapter("SELECT * FROM order, employee WHERE order.Employee_login = employee.username ORDER BY order_number", myConn)
cmdSql.Connection = myConn
cmdSql = New SqlCommand(sql, myConn)
cmdSql.CommandType = CommandType.StoredProcedure
myDA.UpdateCommand = New SqlCommand
myDA.UpdateCommand = cmdSql

myDA.Fill(myDS, "order")


And here is my code for my update procedure:

Public Sub setUpdate(ByVal WO As String, ByVal d1 As String, ByVal d2 As String, ByVal dateClose As String, ByVal EmplClose As String)

Dim sql As String

dateClose = Replace(dateClose, "AM", "")
dateClose = Replace(dateClose, "PM", "")

sql = "UPDATE order SET description = '" & d1 & "', description2 = '" & _
d2 & "', Date_time_closed = '" & dateClose & "',Empl = '" & EmplClose & "' WHERE order_number = " & _
WO & ";"
cmdSql = New SqlCommand(sql, myConn)
myDA.UpdateCommand = cmdSql
myDA.Update(myDS, "order")

myConn.Close()

End Sub

 

[Kris11]

Where are you declaring your dataset? Are you initializing dataset before using it with Fill?

It needs to be like this

Dim myDS as DataSet
myDS = new DataSet

myDA.Update(myDS, "order")

-Kris

 

[holidayIT]

sorry, yes, i declare them outside of the functions, but in the class, trying to make them global.

is there a way to just execute the sql??? like cmd.execute or something similar?

 

[Kris11]

Yes, look at ExecuteNonQuery

Eg: from MSDN

Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
    Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
    myCommand.Connection.Open()
    myCommand.ExecuteNonQuery()
    myConnection.Close()
End Sub 'CreateMySqlCommand

-Kris

 

[holidayIT]

you rock...thanks.


btw - do you know how to refresh the form with the new data after the executenonquery?

 

[Kris11]

btw - do you know how to refresh the form with the new data after the executenonquery?

depends on what control you are using, there are few examples in MSDN on this

1. If it is a datagrid try this link

2. There are some good examples if you are using controls like combobox, text boxes etc. It is worth looking at this link too.

-Kris

 

[holidayIT]

Those links had some good information, i'm just not sure that i know emough to completely understand how they are processing the information. I also didn't give you a whole lot of information. so let me try again:

i have a form with textboxes & radio buttons. my radiobuttons are set to a variable that reads from a specific field in the table. a simple if statement sets the values. my textboxes use the databinding.add technique. i have a dataadapter, connection, and dataset that are defined programmatically to the best of my ability. it read fine.

i have a button on my form that when a user click, it opens another form as a dialog, and the user then enters the information they wish to add to the record.

when they click a button on form2 it calls a public function in form1 and passes the added values as arguments. the public function in form1 then sets the values to certain variables and creates an update statement in sql. i then execute with the executenonquery. the only problem is that i would like the form to then show the new changes in the form1. it updates fine, but you cant see the data. I'd love to refresh the data, then i could just set the dataset position to the previous position, and it would be invisible to the user.

in theory it sounds like it'd be easy. but i can't do it. i've tried to set the dataset and daatadpater to nothing, then recall the select statement, and that didn't work. i'm used to the old oledb and adodb recordsets in vb6, vba and vbscript.

does this help explain my dilema?

 

[Kris11]

What I feel is that your form1 dataset->DataTable does not have the newly added row and that is the reason why it is not getting refreshed. But in this case why you want to do an ExecuteNonQuery? An easy approach would be

1. Setup Insert/Update/Delete Commands for the same dataset->Datatable which you used for Filling up your fields in form1

2. Insert newly added row to the same dataset->Datatable in Form1.

3. Do an UPDATE on this dataset. This way you can avoid one additional Fill and more over since all the controls are already bound, you don't have to do anything extra to get it refreshed.

-Kris

 

[holidayIT]

to be honest with you, i set it up the way i did because i'm trying to teach myself this without any help from my company. i only have a few books, and while they are good for some things, they lack elsewhere. i tried to drop the dataadapter and dataset, but didn't know how to get certain things to work, and i like doing it in code than using the wizards. also, i'm used to the .execute commands from other languages.

i tried to do the adapter.updatecommand and dataset.update, but i couldn't get a lot of information on how to do it, so after a few days of attempts i decided to just do the execute. this is my first program in vb.net. i've done quite a bit in other vb and used the connection recordset quite extensively. basically i am a bit slow with this stuff.

i don't know if it'd help to see my code, if so, let me know. i will give you my first born you can fix this for me.

 

[Kris11]

Try this link as a starting point. It has links to InsertCommand/DeleteCommand/SelectCommand.
Basically what you should be doing is

1. Create a dataset
2. Use Fill to retrieve data.
3. Whenever making changes make sure that this dataset is getting the correct Updates/Deletes.

While saving
4. Create a dataAdapter
5. Set it's Insert/Update/Delete Commands for the adapter. Same above link has more links (towards the end of the document) to show how these can be done through code.
6. Then do an update on DataAdapter with the dataset->datatable you created as an argument to the UPDATE.

It is fairly straight forward. But if you think that it is not so easy to follow try this book which I felt one of the best book for beginners.

-Kris