updating syntax

[PeOfEo]

ok what is the sql syntax to update a data base. I am using asp.net by the way. Ok I am looking at the insert command but how is an update ifferent. Because i am looking at
dbupdate.commandtext="update memlst set " _
& "status = 'inactive'

well that somewhat what I mean. Ribeyed on my register page, how would I change that sql statement into an update. I want to use about the same form but the user will see it after he logs in on the login page and will see his or her current stats already filled in on the form. That part is easy, but how can I change my sql statement into an update? Is it just as simple is changing insert to update?

[Ribeyed]

hi Nick here is what i have so far. Getting a error with the syntax for the sql but you notice its quite different:


<Script runat="server">
Sub SubmitBtn_Click (s As Object, e As EventArgs)
Dim DBConn As OleDbConnection
Dim strUpdate As String
Dim cmdUpdate As OleDbCommand
Dim intUpdateCount As Integer
DBConn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" _
& Server.MapPath("members.mdb;"))
strUpdate = "Update Members Set MemberName=@MemberName, [Password]=@Password, EmailAddress=@EmailAddress Where MemberID=@MemberID "

cmdUpdate = new OleDbCommand(strUpdate, DBconn)
cmdUpdate.Parameters.Add("@MemberName", OleDbType.VarChar, 255)
cmdUpdate.Parameters.Add("@Password", OleDbType.VarChar, 255)
cmdUpdate.Parameters.Add("@EmailAddress", OleDbType.VarChar, 255)
cmdUpdate.Parameters.Add("@MemberID", OleDbType.VarChar, 255)

cmdUpdate.Parameters("@MemberName").Value = Replace(txtMemberName.text, "'", "''")
cmdUpdate.Parameters("@Password").Value = Replace(txtPassword.text, "'", "''")
cmdUpdate.Parameters("@EmailAddress").Value = Replace(txtEmailAddress.text, "'", "''")
cmdUpdate.Parameters("@MemberID").Value = "1"
DBConn.Open()
intUpdateCount = cmdUpdate.ExecuteNonQuery()
DBConn.Close()
lblResults.Text = intUpdateCount & "records updated!"
end Sub
</Script>

[PeOfEo]

ur getting an error? It looks fine to me. 500? Yea its a bit different Let me look at what I have this other method, see what it can do ill play around with it.

[Ribeyed]

hi Nick,
Did you not get an errror?
and no not a 500 :D this time, lol

[PeOfEo]

This works. I did a lil bit o tweaken, like made the meberid the session memid, and now it works fine. You can just set to a value i think because the it says there is already a prime key field with this value and it wont work, and you can set it as 0 which was my first try, but then it was not on the list I mean zero wont do a thing. So I thought wait lets just try the session since the have to login anyway for their name. So this code seems to work fine, unless you see any synatax errors that will give me the dreaded 500 down the road....


<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<script runat="server">
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)

If len(session("memberid"))= 0 then
response.redirect("./log2.aspx")
end if
lblname.text= (session("memname"))
end sub


Sub SubmitBtn_Click(Sender As Object, E As EventArgs)
if txtrank.selecteditem.value = "Peasent" then
lblrnkval.text="1"
end if
if txtrank.selecteditem.value = "Soldier" then
lblrnkval.text="2"
end if
if txtrank.selecteditem.value = "Page" then
lblrnkval.text="3"
end if
if txtrank.selecteditem.value = "Guard" then
lblrnkval.text="4"
end if
if txtrank.selecteditem.value = "Knight" then
lblrnkval.text="5"
end if
if txtrank.selecteditem.value = "Elite" then
lblrnkval.text="6"
end if
if txtrank.selecteditem.value = "Lord" then
lblrnkval.text="7"
end if
if txtrank.selecteditem.value = "Preist" then
lblrnkval.text="8"
end if
if txtrank.selecteditem.value = "Paladin" then
lblrnkval.text="9"
end if
if txtrank.selecteditem.value = "Apprentice" then
lblrnkval.text="10"
end if
if txtrank.selecteditem.value = "Noble" then
lblrnkval.text="11"
end if

Dim DBConn As OleDbConnection
Dim strUpdate As String
Dim cmdUpdate As OleDbCommand
Dim intUpdateCount As Integer
DBConn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" _
& Server.MapPath("/knightsempire/db/members/members.mdb;"))



Strupdate = "Update members set memname=@memname, [password]=@password, bots=@bots, rank=@rank, rankval=@rankval, sc=@sc, d2=@d2, wc3=@wc3, email=@email where memberid=@memberid"
cmdUpdate = new OleDbCommand(strUpdate, DBconn)

cmdupdate.Parameters.Add("@memname", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@password", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@bots", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@rank", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@rankval", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@sc", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@d2", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@wc3", OleDbType.VarChar, 255)
cmdupdate.Parameters.Add("@email", OleDbType.VarChar, 255)
cmdUpdate.Parameters.Add("@MemberID", OleDbType.VarChar, 255)



cmdupdate.Parameters("@memname").Value = Replace(lblName.text, "'", "''")
cmdupdate.Parameters("@password").Value = Replace(txtPassword.text, "'", "''")
cmdupdate.Parameters("@bots").Value = Replace(txtbots.text, "'", "''")
cmdupdate.Parameters("@rank").Value = Replace(txtrank.selecteditem.value, "'", "''")
cmdupdate.Parameters("@rankval").Value = Replace(lblrnkval.text, "'", "''")
cmdupdate.Parameters("@sc").Value = Replace(ddlsc.selecteditem.value, "'", "''")
cmdupdate.Parameters("@d2").Value = Replace(ddld2.selecteditem.value, "'", "''")
cmdupdate.Parameters("@wc3").Value = Replace(ddlwc3.selecteditem.value, "'", "''")
cmdupdate.Parameters("@email").Value = Replace(txtEmailAddress.text, "'", "''")
cmdUpdate.Parameters("@MemberID").Value = (session("memberid"))
DBConn.Open()

intUpdateCount = cmdUpdate.ExecuteNonQuery()
DBConn.Close()
response.redirect("./list.aspx")
end Sub


</SCRIPT>

[Ribeyed]

hi,
thats great good stuff, and no can't see any syntax errors :D

[PeOfEo]

Well I have tested and tested and I couldnt manage to force an error and some people have already used it and no error reports yet so, hopefully I got that done with now I need to see If I can't completly redo that forum with what I have learned. I am going to try to use data grids not instead of lables and I have new ideas to keep users from posting without logging in and Some new layout ideas. Ill need to go back to the java script forum and look at that sting that dude gave me (its in vb) because I am too lazy to write my own even though its easy :)