January 11, 2006 at 5:02 am
this may be a little off topic, but maybe someone here can help
i've inherited a little asp.net app that downloads data from SQL server using dataadaptors into a dataset for display on an aspx page.
controls on the page are databound to the dataset
when calling the update method Me.SqlDataAdapter1.Update(Me.EdiT_PATIENT1)
the code does not seem to post back the data to SQL server (it almost seems like the dataset has not been changed)
here is some sample code
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.SelectCommand.CommandText = "SELECT TBL_PATIENT_DETAILS.* FROM TBL_PATIENT_DETAILS,TBL_PATIENT_FLOW WHERE TBL_PATIENT_FLOW.PATIENT_ID=TBL_PATIENT_DETAILS.PATIENT_ID and TBL_PATIENT_FLOW.ID=" + Me.Session("FLOWNO")
iresult = Me.SqlDataAdapter1.Fill(Me.EdiT_PATIENT1)
Me.TxtTitle.DataBind()
End Sub
Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim iresult As Integer
iresult = Me.SqlDataAdapter1.Update(Me.EdiT_PATIENT1)
Me.TxtTitle().DataBind()
End Sub
hope someone can help.....
MVDBA
January 12, 2006 at 7:17 am
Without seeing more code, I'd have to hazard a guess that either your original dataadapter/dataset is not being cached between postbacks or its being overwritten upon each postback and before your UPDATE method is called. Do you have any code in your page load that checks to see if the page is posting back and/or the dataadaptor/dataset exists in session or cache?
The next area I might check is if a command builder was used to help create your dataadaptor and/or the INSERT/UPDATE/DELETE commands on the dataadaptor function properly.
If it is possible, please post the Page Load method for review.
January 12, 2006 at 8:15 am
ok, below is the page load sub.
I think the problem is that the dataset is filled on page, load, which is overwriting any changes when the page is posted.
i've tried several other ways to get round this such as not binding the controls and then picking the values and doing a dataset.tables(0).rows(0).item(x)=....
but i'm sure i'm doing this wrong - or i've missed the point basically the page is used to edit data about a "patient" the screen has a few bound text boxes and 2 datasets and i'm looking to alter the values in the text boxes - hit save and then post the record back to SQL server - there is only 1 record ever shown on screen at a a time.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim iresult As Integer
'Dim deptcode As Integer
'deptcode = Session("WSubDepartment")
'Put user code to initialize the page here
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.SelectCommand.CommandText = "SELECT TBL_PATIENT_DETAILS.* FROM TBL_PATIENT_DETAILS,TBL_PATIENT_FLOW WHERE TBL_PATIENT_FLOW.PATIENT_ID=TBL_PATIENT_DETAILS.PATIENT_ID and TBL_PATIENT_FLOW.ID=" + Me.Session("FLOWNO")
iresult = Me.SqlDataAdapter1.Fill(Me.EdiT_PATIENT1)
Me.SqlDataAdapter2.SelectCommand.CommandText = "SELECT TBL_PATIENT_FLOW.* FROM TBL_PATIENT_FLOW WHERE TBL_PATIENT_FLOW.ID=" + Me.Session("FLOWNO")
iresult = Me.SqlDataAdapter2.Fill(Me.EdiT_FLOW11)
'Me.TxtTitle.DataBind()
Me.TxtTitle.DataBind()
End Sub
MVDBA
January 12, 2006 at 9:13 am
What type of control is TxtTitle? I assume a textbox (but you know what they say about assumption). Based on the code given, you don't seem to be databinding any other controls. Also, what are you using the second SQLDataApaptor for?
If this represents sum total of the DataAdaptor code, then you'd probably want to make a change like the following:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
if Not Page.IsPostback Then
Dim iresult As Integer
'Dim deptcode As Integer
'deptcode = Session("WSubDepartment")
'Put user code to initialize the page here
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.SelectCommand.CommandText = "SELECT TBL_PATIENT_DETAILS.* FROM TBL_PATIENT_DETAILS,TBL_PATIENT_FLOW WHERE TBL_PATIENT_FLOW.PATIENT_ID=TBL_PATIENT_DETAILS.PATIENT_ID and TBL_PATIENT_FLOW.ID=" + Me.Session("FLOWNO")
iresult = Me.SqlDataAdapter1.Fill(Me.EdiT_PATIENT1)
Me.SqlDataAdapter2.SelectCommand.CommandText = "SELECT TBL_PATIENT_FLOW.* FROM TBL_PATIENT_FLOW WHERE TBL_PATIENT_FLOW.ID=" + Me.Session("FLOWNO")
iresult = Me.SqlDataAdapter2.Fill(Me.EdiT_FLOW11)
Session("PatientDS") = Me.EdiT_PATIENT1
Session("FlowDS") = Me.EdiT_FLOW11
Else
'''Should put a check here to see if
'''Session is empty for whatever reason (e.g. Timeout)
Me.EdiT_PATIENT1 = ctype(Session("PatientDS"),dataset)
Me.EdiT_FLOW11 = ctype(Session("FlowDS"),dataset)
End if
'Me.TxtTitle.DataBind()
Me.TxtTitle.DataBind()
End Sub
Now this is the type of code that would have the dataset created once, and stored in Session for other uses. It looks that you are using Visual Studio genereated objects (based upon naming conventions), so I am assuming (probably incorrectly) that you are setting things like the UPDATE/INSERT/DELETE commands via the Visual Studio Wizard. To get the Update method of the DataAdaptor to work, you will need some mechanism to set the appropriate parameters of the SQL statements from your controls.
If this is a simple "Display a record, Update or not" type of application, then SQLDataAdaptor might be more than you need. You can create a Command/DataReader to get your data(In the Load Method), and then create a command to update the data (in the button click). Let me know.
January 13, 2006 at 1:43 am
thats exctly what i was looking for (if Not Page.IsPostback Then)
you are correct on all counts, it is visual studio 2003 (i'm usually a c++/vb developer, so asp.net has a few bits that i stuggle with.
I did also remove a load of code - bindings etc for clarity.
this is the only page where i'm using a data adaptor as it seemed the obvious choice where i'm loading a patient record on screen, updating fields and posting back to sql, but if there's a better way then please feel free to post me an example.
p.s - if you bind a textbox control to a dataset and type in a value into the text box the value of txtbox.text does not = the value you typed - it still holds the value in the dataset. Is there any way to retreive this without posting back ?
Cheers
mike
MVDBA
January 13, 2006 at 6:20 am
Sounds like a perfect example of where executing a stored procedure to perform the updates would be the best solution for security and performance. Do you have access to create SPs on the SQL Server?
January 13, 2006 at 6:52 am
The above poster is correct, a stored proc (or any method to get data back into the DB) followed by reloading and rebinding the data would be a great solution. As far as not posting back, I honestly cant think of away of getting the data back into the server without posting back. Even if you were to use some type of AJAX solution, you'd still have to post back to the web server
You still have one DataAdaptor call from your original post, in the btnSave_Click method. Try this: Move your code where you load and bind the dataset (the stuff in the if portion of the code) into another method mysteriously named LoadData. Change your btnSave_Click method so that if created a command that does your update (via stored proc or inline SQL, your choice), loading in the values from the textboxes. after successfully executing your command, call your LoadData to load and rebind your updated data.
January 13, 2006 at 7:29 am
Thanks for your help on that one -
i had planned to paste these as stored procedures, but for the purposes of development i've used sql until the customer approves screen layouts and the full field listings.
I eventually moved away from sqldataadaptors as it was the adaptor.update(dataset) that just didn't seem to be even connectoing to sql server (i ran a profiler trace - all the selects were fine, but the updates didn't even fire)
the screen works perfectly now (it's the only screen where data is updated)
thanks again guys
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply