Solved

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

Posted on 2004-10-08
14
182 Views
Last Modified: 2010-04-23
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
Comment
Question by:jxharding
  • 7
  • 6
14 Comments
 
LVL 10

Expert Comment

by:123654789987
ID: 12257325
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12257457
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12257494
hi, 123654789987

Why should he need a commandbuilder when he wants to use stored procedures ?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:jxharding
ID: 12257568
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12257574
Hi jxharding, if you use the commandbuilder you are NOT using your storedprocedures.
0
 

Author Comment

by:jxharding
ID: 12257591
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
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 500 total points
ID: 12257699
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
 

Author Comment

by:jxharding
ID: 12257772
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12257832
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
 

Author Comment

by:jxharding
ID: 12258055
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12258079
actually you shouldn't have to, something else is wrong, could you post all code in the form including the windows generated code ?
0
 

Author Comment

by:jxharding
ID: 12258702
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12258813
Ok, let me know how you are getting along.
0
 

Author Comment

by:jxharding
ID: 12258982
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question