Just won't Update

[jemckinney]

I cannot seem to figure out where my coding must be wrong. My update function is not working. Can anyone see where my mistake is?

strUpdate = "Update FaFAFSA06 Set CPSStatus=@oldID Where ssn=@stuSSN and cpsstatus=@newID"
cmdUpdate = New SqlCommand( strUpdate, c2000Conn )
cmdUpdate.Parameters.Add( "@stuSSN", txtstuSSN.Text )
cmdUpdate.Parameters.Add( "@oldID", txtoldID.SelectedItem.Value )
cmdUpdate.Parameters.Add( "@newID", txtnewID.SelectedItem.Value )

intUpdateCount = cmdUpdate.ExecuteNonQuery()
c2000Conn.Close()
cmdUpdate.ExecuteScalar()
lblResults.Text = intUpdateCount & " records updated"

--

<form runat="server">
<aspanel ID="pnlForm1" runat="server">

<p>SSN&nbsp;&nbsp;<asp:TextBox ID="txtstuSSN" runat="server" />
</p>
Old:
<aspropDownList ID="txtoldID" Runat="Server">
<asp:ListItem Text="R" Value="R" />
<asp:ListItem Text="S" Value="S" />
<asp:ListItem Text="N" Value="N" />
<asp:ListItem Text="A" Value="A" />
</aspropDownList>
&nbsp;
New:
<aspropDownList ID="txtnewID" Runat="Server">
<asp:ListItem Text="R" Value="R" />
<asp:ListItem Text="S" Value="S" />
<asp:ListItem Text="N" Value="N" />
<asp:ListItem Text="A" Value="A" />
</aspropDownList>

<asp:Button Text="Submit" runat="server" />

</aspanel>

<aspanel ID="pnlForm2" runat="server" Visible="False">
<asp:Label ID="lblResults" EnableViewState="False" runat="server" />
</aspanel>
</form>


Thanks in advance guys!

 

[ca8msm]

Can you be a little more specific than "My update function is not working" as this gives us ver little info.

Some guesses may be:

1) You populate the DDL's on potback so the update s running but setting everything to the same value

2) The parameters aren't being set as you expected

3) No rows are returned for those parameters



--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[RTomes]

if txtnewID.SelectedIndex=-1 then
lblResults.Text = "Error: New ID"
exit sub
end if
if txtoldID.SelectedIndex=-1 then
lblResults.Text = "Error: Old ID"
exit sub
end if

c2000Conn.Open()

strUpdate = "Update FaFAFSA06 Set CPSStatus=@oldID Where ssn=@stuSSN and cpsstatus=@newID"
cmdUpdate = New SqlCommand( strUpdate, c2000Conn )
cmdUpdate.Parameters.Add( "@oldID", txtoldID.SelectedItem.Value )
cmdUpdate.Parameters.Add( "@stuSSN", txtstuSSN.Text )
cmdUpdate.Parameters.Add( "@newID", txtnewID.SelectedItem.Value )

intUpdateCount = cmdUpdate.ExecuteNonQuery()
c2000Conn.Close()
'cmdUpdate.ExecuteScalar() ' Don't need this

lblResults.Text = intUpdateCount & " records updated"

 

[jemckinney]

Sorry for the lack of info. But the above code changed nothing. I have a form, one textbox and two dropdownlists, for user input. My sql command takes these three variables and uses two of them to check against the database and one of them to make a change. When I use the form and click the submit button, it just retains the info inputted and does not change the data in the db.

 

[ca8msm]

Step through the 3 bullet points I listed and see if any of them are the cause.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[jemckinney]

In leui of sounding like an idiot, I know it's not #3 because the search using the same page instead of usdate works great. How would I check 1 and 2?