Solved

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

Posted on 2004-10-08
14
178 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now