Help with UPDATE RECORD form

[upplepop]

I'm working on a page where the user can search for the ID of a Agency they want to update, the Agency record will appear so it can be edited, and the user can submit those changes.

The code I have written thus far does not seem to work, and I am requesting some input to what could be wrong. I am still pretty new to ASP, and some of the code was inherited from a page made with Dreamweaver, hence the added code. There is much more to this form, but I have removed the extraneous fields to make it easier to troubleshoot.

The data is drawn from two different database, which is why there are two connection strings. Let me know if you need any more info.

<%
qrySearchValue = Trim(Request("searchValue"))
strSearchValue = qrySearchValue

sqlProviders = "SELECT * FROM providers WHERE uid = '" & qrySearchValue & "' "
sqlAddlElements = "SELECT * FROM providers_addl_elements WHERE provider_id = '" & qrySearchValue & "' "

sqlUpdateAddlElements = "UPDATE providers_addl_elements SET common_name='"&request("common_name")&"' WHERE provider_id = '" & qrySearchValue & "' "

IF Request("Update")="Submit" THEN
rsAddlElements.Execute (sqlUpdateAddlElements)
END IF


Set rsProviders = Server.CreateObject("ADODB.Recordset")
rsProviders.ActiveConnection = MM_hmis_data_STRING
rsProviders.Source = sqlProviders
rsProviders.CursorType = 1
rsProviders.CursorLocation = 2
rsProviders.LockType = 1
rsProviders.Open()
rsProviders_numRows = 0

Set rsAddlElements = Server.CreateObject("ADODB.Recordset")
rsAddlElements.ActiveConnection = MM_coc_data_STRING
rsAddlElements.Source = sqlAddlElements
rsAddlElements.CursorType = 1
rsAddlElements.CursorLocation = 2
rsAddlElements.LockType = 1
rsAddlElements.Open()
rsAddlElements_numRows = 0

Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rsProviders_numRows = rsProviders_numRows + Repeat1__numRows
%>

<form name="Search" method="get">
<% if strSearchValue = "" then strSearchValue = "" %>
<strong>Provider ID:</strong>&nbsp;
<input type="text" name="searchValue" value="<%=strSearchValue%>">&nbsp;
<input type="submit" id="Search" name="Search" value="Search">&nbsp;
</form>

<% if rsProviders.EOF then Response.Write "<strong>Sorry, no results found." end if %>
		
<% if qrySearchValue <> "" then 
	 While ((Repeat1__numRows <> 0) AND (NOT rsProviders.EOF)) %>

<form name="Update" method="post" enctype="multipart/form-data">
    <label for="provider_name">Provider Name: </label><input type="text" name="name" id="name" value="<%=(rsProviders.Fields.Item("name").Value)%>" /><br>
    <label for="common_name">Common Name: </label><input type="text" name="common_name" id="common_name" value="<%=(rsAddlElements.Fields.Item("common_name").Value)%>" /><br>
<input type="submit" id="Update" name="Update" value="Update Record">

<%
    Repeat1__index=Repeat1__index+1
    Repeat1__numRows=Repeat1__numRows-1
    rsProviders.MoveNext()
	wend
	end if
%>

</form>

<%
rsProviders.Close()
Set rsProviders = Nothing

rsAddlElements.Close()
Set rsAddlElements = Nothing
%>

 

[DotNetGnat]

what is the error that you are getting...

-DNG

 

[upplepop]

No error message actually. After pressing the 'Update Record' button, the page reloads, but the fields do not update.

 

[DotNetGnat]

try this:

IF Request("Update")<>"" THEN
rsAddlElements.Execute (sqlUpdateAddlElements)
END IF

-DNG

 

[upplepop]

I replaced

IF Request("Update")="Submit" THEN 
rsAddlElements.Execute (sqlUpdateAddlElements)
END IF

with this

IF Request("Update")<>"" THEN 
rsAddlElements.Execute (sqlUpdateAddlElements)
END IF

However, the data still does not update.

 

[DotNetGnat]

oops...my bad...

your code should look like this...

objconn.execute(strsql)

and not like this

rsobj.execute(strsql)

hope you got the point...

replace your recordset object with the connection object...

-DNG

 

[upplepop]

DotNetGnat,

Sorry, I can't quite figure out how to modify the code as per your suggestion.

Doesn't this part:
Set rsAddlElements = Server.CreateObject("ADODB.Recordset")

make it a connection object?

 

[DotNetGnat]

Set rsAddlElements = Server.CreateObject("ADODB.Recordset")

no... in this part you are creating your recordset object...

you have to create a connection object:

Set objConn = Server.CreateObject("ADODB.Connection")

then do this:

objConn.ConnectionString = MM_coc_data_STRING

and then do this:

rsAddlElements.ActiveConnection = objConn

Now finally you can do this:


IF Request("Update")<>"" THEN
objConn.Execute (sqlUpdateAddlElements)
END IF


i am not completely familiar with this syntax but you are missing to declare a connection object...Update query doesnt need a recordset object....

-DNG

 

[upplepop]

The code recommended by DotNetGnat doesn't seem to be working. I am getting this error: Object doesn't support this property or method: 'ConnectionString'
Here is the code as I have it in the page:

Set objConn = Server.CreateObject("ADODB.Recordset")
objConn.ConnectionString = MM_coc_data_STRING
rsAddlElements.ActiveConnection = objConn

IF Request("Update")<>"" THEN
objConn.Execute (sqlUpdateAddlElements)
END IF

 

[DotNetGnat]

Set objConn = Server.CreateObject("ADODB.Recordset")

should be

Set objConn = Server.CreateObject("ADODB.[red]Connection[/red]")

Please read the posts thoroughly...

-DNG

 

[upplepop]

Thanks DotNetGnat, sorry I missed that.

I am still having trouble getting that code to work.

I changed the CreateObject to a connection, and get this error:

Operation is not allowed when the object is open.
(On this line: rsAddlElements.ActiveConnection = objConn)

Taking out the rsAddlElements.Open line gives me this error (on the same line of code):
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Not sure how to proceed.

 

[upplepop]

Does anyone have a general idea on how to update a record in a database table from ASP? Perhaps I can proceed from there.

 

[DotNetGnat]

this is the simple sample code...

Dim strSQL, objConn

Set objConn = Server.CreateObject("ADODB.Connection")

obj.Conn.Open " your connection string here"

'dummy sql statement

strSQL = "Update mytable SET field1='blah' where RecordID= 5 "

objConn.execute(strSQL)

as you can see above you dont need a recordset object for updating/deleting or inserting operations...

-DNG

 

[upplepop]

And so how would this process be executed upon pressing one of the "Submit" bottons on the page?

 

[DotNetGnat]

something like this:

<html>
<body>
<form method="post">
<input type= "text" name="recid">
<input type= "submit" name="submit">
</form>
</body>
</html>
<%
if request.form("submit")<>"" then
Dim strSQL, objConn

Set objConn = Server.CreateObject("ADODB.Connection")

obj.Conn.Open " your connection string here"

'dummy sql statement

strSQL = "Update mytable SET field1='blah' where RecordID= " & Request.Form("recid")

objConn.execute(strSQL)

set objConn = Nothing
objConn.Close

End if
%>

the above code is freehand written so please check for errors...

-DNG