Link to home
Start Free TrialLog in
Avatar of Edward Harford
Edward Harford

asked on

Does populating a form using a SQLDataReader preclude you from then editing the form?

I am populating a form using a SQLDataReader pulling data from a Table in a Page_Load event. I want then to change some of the Field text values which show correctly. When I then Save the new data to another Table using a Stored Proc, the parameter values pick up the original values not the newly edited values. I want, for example to add 'Vegetarian' to Diet1.

Open in new window

Page_Load event:      

 Using dr = invComm.ExecuteReader

                    dr.Read()

                    Me.ID1.Text = dr("ID").ToString

                    Me.Name1.Text = dr("Fname").ToString + " " + dr("Sname").ToString

                    Me.Rank1.Text = dr("Rank").ToString

                    Me.Email1.Text = dr("Email").ToString

                    Me.Diet1.Text = dr("DietReq").ToString

          End Using

Save event:

Open in new window

Using invComm = New SqlCommand("AddGSLTick", invConn)

                invComm.CommandType = Data.CommandType.StoredProcedure

                Dim param As New SqlParameter("@ID", Data.SqlDbType.Int)

                param.Value = CInt(Me.ID1.Text)

                invComm.Parameters.Add(param)

                param = New SqlParameter("@Name", Data.SqlDbType.NVarChar)

                param.Value = Me.Name1.Text

                invComm.Parameters.Add(param)

                param = New SqlParameter("@Rank", Data.SqlDbType.NVarChar)

                param.Value = Me.Rank1.Text

                invComm.Parameters.Add(param)

                param = New SqlParameter("@Email", Data.SqlDbType.NVarChar)

                param.Value = Me.Email1.Text

                invComm.Parameters.Add(param)

                param = New SqlParameter("@DietReq", Data.SqlDbType.NVarChar)

                param.Value = Me.Diet1.Text

                invComm.Parameters.Add(param)


Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

you setup the command but then you have to:

1)  Open the connection
2)  Execute the command.
3)  Close the command.

so:
Try
    invConn.Open()
    invComm.ExecuteNonQuery()
Catch ex As Exception
   ' do error handling here.
Finally
    If invConn.State <> ConnectionState.Closed Then 
            invConn.Close()
    End If
End Try

Open in new window

Avatar of Edward Harford
Edward Harford

ASKER

I should have shown you all my code:
        
Using invConn = New SqlConnection(ConfigurationManager.ConnectionStrings("GSConnectionString").ConnectionString)
            invConn.Open()
            Using invComm = New SqlCommand("GetGSName", invConn)
                invComm.CommandType = Data.CommandType.StoredProcedure
                Using dr = invComm.ExecuteReader
                    dr.Read()
                    Me.ID1.Text = dr("ID").ToString
                    Me.Name1.Text = "Bro " + dr("Fname").ToString + " " + dr("Sname").ToString
                    Me.Rank1.Text = dr("Rank").ToString
                    Me.Email1.Text = dr("Email").ToString
                    Me.Diet1.Text = dr("DietReq").ToString
                End Using
            End Using
        End Using

Open in new window

My question is whether this method effectively binds the value of Me.Diet1.Text, not allowing me to change it?
I have other fields on this form, not populated at Page_Load which save perfectly well.
No, you're fine to do the save.  Is the stored proc updating the field?   What if you call the stored proc directly in SSMS?

Also just a note the code as shown is just setting up the command, it's not actually executing.

No, this stored proc is getting the values to populate the form which works fine. It is not updating the Field.

The Save routine to a new Table is as follows:
  Using invConn = New SqlConnection(ConfigurationManager.ConnectionStrings("GSConnectionString").ConnectionString)
            invConn.Open()
            Using invComm = New SqlCommand("AddGSLTick", invConn)
                invComm.CommandType = Data.CommandType.StoredProcedure
                Dim param As New SqlParameter("@ID", Data.SqlDbType.Int)
                param.Value = CInt(Me.ID1.Text)
                invComm.Parameters.Add(param)
                param = New SqlParameter("@Name", Data.SqlDbType.NVarChar)
                param.Value = Me.Name1.Text
                invComm.Parameters.Add(param)
                param = New SqlParameter("@Rank", Data.SqlDbType.NVarChar)
                param.Value = Me.Rank1.Text
                invComm.Parameters.Add(param)
                param = New SqlParameter("@DietReq", Data.SqlDbType.NVarChar)
                param.Value = Me.Diet1.Text
                invComm.Parameters.Add(param)
                param = New SqlParameter("@Email", Data.SqlDbType.NVarChar)
                param.Value = Me.Email1.Text
                invComm.Parameters.Add(param)
                param = New SqlParameter("@Payment", Data.SqlDbType.NVarChar)
                param.Value = Me.Pay1.SelectedValue.ToString
                invComm.Parameters.Add(param)
                param = New SqlParameter("@TDate", Data.SqlDbType.SmallDateTime)
                param.Value = Now()
                invComm.Parameters.Add(param)
                Dim sB As Boolean
                Try
                    invComm.ExecuteNonQuery()
                    sB = True
                Catch
                    Err.GetException.ToString()
                    sB = False
                Finally
                End Try

Open in new window

When I step through the Save routine Diet1.Text does not evaluate the new value 'Vegetarian'.
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial