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 cant 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]

Heres part of a sample from MSDN (or somewhere).

It uses SQL Server (Pubs database)

Place the Unzipped folder in your wwwroot

 

[pruebens]

thanks a bunch......Ill check it out.

 

[pruebens]

I tried this but it doesnt do what Im looking to do (or Im 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? Im 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 doesnt that push the changes back indivually? Isnt there a way to use the dataset to push the changes back globally?

Im sorry it just seems like a lot of code to do such a simple task.

 

[Robby]

Its 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 Ive 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 doesnt 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>

Its important that this is run or the server....
You cant run this client-side, nor as javascript.

 

[pruebens]

Okay I think Im 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]

Originally posted by pruebens
Thanks so much for your help.......you are very wise

Nah.. not yet

 

[pruebens]

Now does this make a difference that Im using codebehind?

 

[Robby]

Not at all, thats all I use.

As long as youre 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 Ive 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?