Sending updates back to the server using ASP

[pruebens]

Not sure where to post this but I have a web form that I use to pull data from a database using bound controls. That all works fine but I can't find how to send changes made to those controls (textboxes and stuff) BACK to the database. Can someone PLEASE point me in the right direction?

[Robby]

Do you mean changes to the value of the controls (ie textbox value) or to the controls themselves?

[pruebens]

Sorry I meant changes to the value of the textbox controls.

[Robby]

Here's part of a sample from MSDN (or somewhere).

 

It uses SQL Server (Pubs database)

 

Place the Unzipped folder in your wwwroot

updatesqltextbox.zip

[pruebens]

thanks a bunch......I'll check it out.

[pruebens]

I tried this but it doesn't do what I'm looking to do (or I'm doing it wrong).

 

I can pull data just fine using a SQLdataAdapter and a dataset then binding my controls using the databind method. It all displays just fine.

 

But how do I pass changes made to the text/check boxes back to the database? I'm new to all this so forgive my ignorance.

 

here is my current code:

 

Private Sub RefreshIndividual()

Me.WebProfile1.Clear()

Me.SqlDataAdapter1.Fill(Me.WebProfile1, "Table")

BindControls()

End Sub

 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Put user code to initialize the page here

 

If Not Page.IsPostBack Then

result = System.Environment.UserName

Me.SqlDataAdapter1.SelectCommand.CommandText = "SELECT * FROM Table WHERE Username = '" & result & "'"

Me.SqlDataAdapter1.Fill(Me.WebProfile1, "Table")

BindControls() --(binds controls)

RefreshIndividual()

End If

End Sub

 

Private Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles submit.Click

Me.SqlDataAdapter1.Update(WebProfile1, "Table")

Me.WebProfile1.AcceptChanges()

 

 

 

Hopefully someone can point me in the right direction......TIA.

[Robby]

In the sample, have a look at the Sub AddAuthor_Click

[pruebens]

If I am not mistaken doesn't that push the changes back indivually? Isn't there a way to use the dataset to push the changes back globally?

 

I'm sorry it just seems like a lot of code to do such a simple task.

[Robby]

It's acually one line of code, (the line with the INSERT INTO)

 

try this function if you wish...

 

   Private Function SetTableValue() As Integer
       Dim SqlCMD As SqlCommand
       Dim SqlCN As New SqlConnection(Conn) 'Conn is your connection string
       Dim recordsAffected As Integer, strSql As String
       Try
           strSql = "UPDATE myTable ...."
           'OR
           strSql = "INSERT INTO myTable..."

           If SqlCN.State = ConnectionState.Closed Then SqlCN.Open()
           SqlCMD = New SqlCommand(strSql, SqlCN)
           recordsAffected = SqlCMD.ExecuteNonQuery()
       Catch
           recordsAffected = -1
       Finally
           If SqlCN.State = ConnectionState.Open Then SqlCN.Close()
           If Not SqlCMD Is Nothing Then SqlCMD.Dispose()
       End Try
       Return recordsAffected
   End Function

[pruebens]

Thanks I will do that.........I was pretty certain I was just being a doofuss about the whole thing......I hate being such a n00b!

[pruebens]

Okay here is what I've got:

 

Private Function SetTableValue() As Integer

result = System.Environment.UserName

Dim sqlcmd As SqlCommand

Dim sqlcn As New SqlConnection("myconnectionstring")

Dim recordsAffected As Integer, strSql As String

Try

strSql = "UPDATE Table Where Username = '" & result & "'"

If sqlcn.State = ConnectionState.Closed Then sqlcn.Open()

sqlcmd = New SqlCommand(strSql, sqlcn)

recordsAffected = sqlcmd.ExecuteNonQuery

Catch

recordsAffected = -1

Finally

If sqlcn.State = ConnectionState.Open Then sqlcn.Close()

If Not sqlcmd Is Nothing Then sqlcmd.Dispose()

End Try

Return recordsAffected

End Function

 

 

However when I fire it up to test I get this error:

 

 

Microsoft JScript runtime error: Object doesn't support this property or method

[Robby]

you should be placing this function in either the Code-Behind or in VB tags like this...

 

<script language="VB" runat="server">

 

<script>

 

It's important that this is run or the server....

You can't run this client-side, nor as javascript.

[pruebens]

Okay I think I'm starting to get this.......so since I want to only have the update execute when the user clicks on a button I would put that code in the button_click event?

[Robby]

correct.

[pruebens]

Thanks so much for your help.......you are very wise :)

[Robby]

Thanks so much for your help.......you are very wise :)

 

Nah.. not yet

[pruebens]

Now does this make a difference that I'm using codebehind?

[Robby]

Not at all, that's all I use.

 

As long as you're able to Handle the click event of the button, the function will run.

[pruebens]

Cool......I see how it will add a new record and I think that I have finally figured it out (sorry I've only been programming about a month now and have only learned what I have read out of about 4 books).

 

So would I merely changed the insert command section to an update command section to send only updates?