Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

sql server stored procedure : MyDataset has changed, i acceptedchanges, cant update it back to database,1st timer

hi this is the first time i use stored procedures in .net
i created the select,insert,update &Delete commands via a sqldataadapter. (all 3 options ticked in advanced options on adapter)
vb saved the procedures on the database
my select statement gets passed 1 parameter

this is how i fill my dataset
        dim cmd As New SqlCommand("MySelect", mySqlConnection)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@MyValue", SqlDbType.VarChar).Value = MyValue
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(MyDataset1, "MyTable")
        MyDataset1.WriteXml("c:\proc1.xml")

I can confirm that MyDataset1 gets filled correctly , as  "c:\proc1.xml" contains all the unmodified data.
now i change some of the values in the dataset e.g.
there is a  field called "MyField" and i change the value in it from <NULL> to "5000"

now i tried a few different approaches to update the dataset back to the database , and this is what it comes down to
        MyDataset1.MyTable.AcceptChanges()
        Dim cmdUpdate As New SqlCommand("MyUpdate", mySqlConnection)
        cmdUpdate.CommandType = CommandType.StoredProcedure
        da.UpdateCommand = cmdUpdate
        da.Update(MyDataset1.MyTable)
        DatasetLimitPrice1.WriteXml("c:\proc2.xml")

now i have a look and see that the values in "c:\proc2.xml"
are 100% correct, the values have changed. the code runs through fine without any errors.
but NO changes are made in the physical database.
now i tried to comment out the first line of the last pasted section :         MyDataset1.MyTable.AcceptChanges()
now it says that i need to enter parameters for all the @Fields in the database.

all data is drawn from only 1 table in the database

i dont know how to update back to the dataset, any help please!
thanks
0
jxharding
Asked:
jxharding
  • 7
  • 6
1 Solution
 
123654789987Commented:
To update the records back into the database..

       connection.Open
 dim cmd As New SqlCommand("MySelect", mySqlConnection)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@MyValue", SqlDbType.VarChar).Value = MyValue
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(MyDataset1, "MyTable")
        MyDataset1.WriteXml("c:\proc1.xml")

    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da) // U neeed this

   myDataAdapter.Update(MyDataset1, MyDataset1.MyTable)
   connection.close
0
 
RonaldBiemansCommented:
Hi jxharding,

you are accepting the changes before you update it should be after

        Dim cmdUpdate As New SqlCommand("MyUpdate", mySqlConnection)
        cmdUpdate.CommandType = CommandType.StoredProcedure
        da.UpdateCommand = cmdUpdate
        da.Update(MyDataset1.MyTable)
        MyDataset1.MyTable.AcceptChanges()

        DatasetLimitPrice1.WriteXml("c:\proc2.xml")

0
 
RonaldBiemansCommented:
hi, 123654789987

Why should he need a commandbuilder when he wants to use stored procedures ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jxhardingAuthor Commented:
hi 123654789987's  & ronaldbiemans  i tried

  Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
  myDataAdapter.Update(MyDataset1, MyDataset1.MyTable)

and it worked fine, i was not aware of sqlcommand builder.
it did not ask me for any parameters.
then i tried putting the acceptchanges at the bottom, and now it asked me for the parameters.
should i pass it every time if i use it this way?
is there something else wrong with my program that maybe caused this?
thanks!
0
 
RonaldBiemansCommented:
Hi jxharding, if you use the commandbuilder you are NOT using your storedprocedures.
0
 
jxhardingAuthor Commented:
hi ronaldbiemans,
i reckon then im prob missing something down the line , because its asking me for each parameter in the
original update query
any suggestions maybe?
thanks
0
 
RonaldBiemansCommented:
Hi jxharding, You say you have dragged a sqldataadapter to your form and configured it to use stored procedures with a parameter.

Then the only thing you should do is something like this (I assume the sqldataadapter you dragged to your form is called sqldataadapter1

sqldataadapter1.selectcommand.parameters("@MyValue").value = MyValue
sqldataadapter1.Fill(MyDataset1, "MyTable")

and to save

sqldataadapter1.update(mydataset1,"mytable")


0
 
jxhardingAuthor Commented:
my eyes are definitely opening now
the plan was just to drag the dataadapter on , so i could generate the stored procedures.
then i wanted to delete the above dataadapter, and rather instantiate the datadapter in code.
thus my sqldatadapter i dragged over was called sqldatadapter1

so now the stored procedures are on the database itself

then i want to use
 dim da as new sqldataadapter
and for selecting,
Dim cmd As New SqlCommand("MySelect", mySqlConnection)
and for updating
Dim cmdUpdate As New SqlCommand("MyUpdate", mySqlConnection)

can i do it this way? i presume that i dont need to pass anything to the update procedure then?
thanks!
0
 
RonaldBiemansCommented:
Yes, you can do it in code yourself (although I don't know why).

But then you have to create all the parameters yourself, which could be a lot of work.

Just drag a sqladapter to your form like you did before and look in the windows generated code to see which parameters you have to set, you have to generate parameters for all the fields in your table for the update and  insert commands.
0
 
jxhardingAuthor Commented:
i just went through the dataadapters properties , and i see what you mean about all the parameters!
i still get this one error though.
this is my new way.
i use only the sql dataadapter that i dragged on.
this is my fill
 SqlDataAdapter1.SelectCommand.Parameters("@MyValue").Value = MyValue
 SqlDataAdapter1.Fill(MyDataset1, "MyField")
        MyDataset1.WriteXml("c:\proc1.xml")

and for updating
       SqlDataAdapter1.Update(MyDataset1, "MyField")
        MyDataset1.WriteXml("c:\proc2.xml")
        MyDataset1.MyField.AcceptChanges()

as soon as it reaches
       SqlDataAdapter1.Update(MyDataset1, "MyField")
then its looking for parameters again, and i dont see why i have to pass parameters now.
i completely discarded of my other declarations of dataadapters.

thanks!


0
 
RonaldBiemansCommented:
actually you shouldn't have to, something else is wrong, could you post all code in the form including the windows generated code ?
0
 
jxhardingAuthor Commented:
hi , i tried making a demo, but im a bit inexperienced with sql server :)
im just going to try and delete the dataadapter and procedures and start afresh again
hopefully it solves something!
thanks!
0
 
RonaldBiemansCommented:
Ok, let me know how you are getting along.
0
 
jxhardingAuthor Commented:
hi ronaldbiemans
i redid everything and with this post, i had all the relevant knowledge, and now it works!!!
thanks so much for all the help , i definitely would have been lost without it!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now