Hi, (asp.net1.1, vb.net2003standard, iis5.1, oledb)

I'm just wondering what techniques professional developers use when inserting a record?

The scenario is that i want users to insert a record but after filling in the webform for the asp.net page to check if a record exists (i.e cytcle through the primary key for an existing entry for obvious purpose), and then to say... insert if the record does not exist, if it DOES exist change this field in the exisiting record.

any techniques/ideas/articles/structured english code would help, thanks
deian

Hi,

Are you only allowing a user to input info to the table one time or are you permitting a user to update data?

In either case you will need something like...I assume you already know how to create a recordset.

Dim priKey, objRS, Keytest

''''First, write a loop to test for the primary key match
Do while not objRS.EOF
If priKey=obrRS("priKey") then
Keytest="true"
'''Write update command here
End If
objRS.MoveNext
Loop
'''Then write command to insert a record (Assuming keytest is not set to true
If Keytest<>"true" then
....Command to insert record
End if
''''Be sure second command is out of the loop...the loop only checks to see if the primary key already exists...if so it will do an update. If not, the value of keytest will never be set to true and then the insert command will be called.

Let me know if this helps
John



If a primary key is matched you can use an Update command
else
If primary key is not
end if

Hello .
Can someone please explain to me how to use this example.
I am using Dreamweaver and it tells me that EOF is not a member of Dreamweaver ctrls. What does it mean?

I am a bit confused. You ask about EOF (which sounds like classic ADO) in an Access ASP forum. However, the Dreamweaver Ctrls are ASP.NET related. Or aren't you talking about the MM .NET Controls?

Can you explain what you're talking about and provide some code?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar,
I apologise for being confusing. I am new to many of those concepts and hope you will forgive my ignorance.
I have made a web form with Dreamweaver. I would like to have only one button for update or insert. so I am trying to find a "trick" to tell the server to check whether the record is already in the database or whether it is a new record.
something like if the record exists then update (using mm_update) or if the record is not in the database then insert.

Hi dorothee,

What information are you passing to your page to edit the record in the first place? A QueryString with the record's unique ID?

If so, you can use that again to determine whether you're editing a record or not:

If Request.QueryString("ID") <> "" Then
  ' Update
Else
  'New
End If

Because you are resubmitting the page when you save the record, you probably loose the querystring. You can fix that by setting the action attribute to the page's name with the Query String (e.g. action="InsertOrUpdate.asp?<%=Request.QueryString% > ) or you save the ID of the record in a hidden form field and use that.

If this doesn't help, I suggest you explain a bit more what you want and the technology you are using and post some relevant code. I saw your other post and you are using the term DataSet. Are you working on an ASP or ASP.NET application?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar,
Thank you for your reply. I understand that I am not very clear in my post and will try to explain myself better.

I have an access database with a lot of tables and am using Dreamweaver to create a web interface.
The primary use of the web interface is for data entry at this stage.

To start, my default page is a search page. The user enters the 'ID' to query if the 'ID' exists in one table.
On submit to the search, it opens a new page where a summary of the record is shown (or if there's no record a line says "would you like to create a new record.."). There is a button on this summary page if the user wants to edit the record. This button redirects to a new page bound to one table in the database. The 'ID' all along is passed from page to page via a hidden field. .
On that new edit page, the user can update the record. There is also a ddl wich redirects to different pages depending on the choice made.
On submit, it works, the table bound to the database is updated. and the user is redirected to the right page (depending on that choice made on the ddl) and another page bound to another table in the database is open.

That's where i am having trouble. On this new page, there's a new dataset, the record may or may not exists. The user needs to fill the form and submit and again he will be redirect to a new page with another dataset bound.

Anyway, passing the 'ID' from one page to another works fine. My problem is how to tell the server to either update or insert.
So far I can only do one at a time:
If i choose mm_insert, the table get a new record but I am getting duplicates (and yes, there's no primary key...)
If i choose mm_update. it doesn't work 'cos sometimes there's no records in that table.
With Dreamweaver, i can use mm_update or mm_insert
What I am trying to do is to write some type of statement like what you've written.
If Request.QueryString("ID") <> "" Then
  ' Update
Else
  'New
End If
but i do not know how to properly write it and where.

As for some code:

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=1.0.0.0,publicK eyToken=836f606ede05d46a,culture=neutral" %>

<MM:Insert
runat="server"
CommandText='<%# "INSERT INTO AllPCsTerms (AllPCsCabledTocommsR, AllPCsCnntoWallorHub, AllPCsDeviceType, AllPCsFNLOC, AllPCsIfISPthenISPName, AllPCsInternetConn, AllPCsIT_Number, AllPCsKbrdSwipeUsed, AllPCsModem, AllPCsMouseUsed, AllPCsNetconnOrCable, AllPCsOfficeMacrosUsed, AllPCsOS, AllPCsOthCbleType, AllPCsPortNoOnWall, ALLPCsSSTPerformed) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" %>'
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAudit") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABASETYPE_connAudit") %>'
Expression='<%# Request.Form("MM_insert") = "form1" %>'
CreateDataSet="false"
Debug="true"
><Parameters>
  <Parameter Name="@AllPCsCabledTocommsR" Value='<%# IIf((Request.Form("cabledtocommsr") <> Nothing), Request.Form("cabledtocommsr"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsCnntoWallorHub" Value='<%# IIf((Request.Form("ConnWall") <> Nothing), Request.Form("ConnWall"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsDeviceType" Value='<%# IIf((Request.Form("DeviceType") <> Nothing), Request.Form("DeviceType"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsFNLOC" Value='<%# IIf((Request.Form("FNLOC") <> Nothing), Request.Form("FNLOC"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsIfISPthenISPName" Value='<%# IIf((Request.Form("ISPname") <> Nothing), Request.Form("ISPname"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsInternetConn" Value='<%# IIf((Request.Form("ddlIntConn") <> Nothing), Request.Form("ddlIntConn"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsIT_Number" Value='<%# IIf((Request.Form("ITNumber") <> Nothing), Request.Form("ITNumber"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsKbrdSwipeUsed" Value='<%# IIf((Request.Form("keyboardswipe") <> Nothing), Request.Form("keyboardswipe"), "") %>' Type="Boolean" />
  <Parameter Name="@AllPCsModem" Value='<%# IIf((Request.Form("ddlmodem") <> Nothing), Request.Form("ddlmodem"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsMouseUsed" Value='<%# IIf((Request.Form("mouseused") <> Nothing), Request.Form("mouseused"), "") %>' Type="Boolean" />
  <Parameter Name="@AllPCsNetconnOrCable" Value='<%# IIf((Request.Form("netwconn") <> Nothing), Request.Form("netwconn"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsOfficeMacrosUsed" Value='<%# IIf((Request.Form("officemacro") <> Nothing), Request.Form("officemacro"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsOS" Value='<%# IIf((Request.Form("OSlink") <> Nothing), Request.Form("OSlink"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsOthCbleType" Value='<%# IIf((Request.Form("othercable") <> Nothing), Request.Form("othercable"), "") %>' Type="WChar" />
  <Parameter Name="@AllPCsPortNoOnWall" Value='<%# IIf((Request.Form("pcwallport") <> Nothing), Request.Form("pcwallport"), "") %>' Type="WChar" />
  <Parameter Name="@ALLPCsSSTPerformed" Value='<%# IIf((Request.Form("sstperformed") <> Nothing), Request.Form("sstperformed"), "") %>' Type="Boolean" />
</Parameters>
</MM:Insert>
<MM:DataSet
id="dsAllPCsTerms"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAudit") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABASETYPE_connAudit") %>'
CommandText='<%# "SELECT * FROM AllPCsTerms WHERE AllPCsIT_Number = ?" %>'
Debug="true"
>
  <Parameters>
    <Parameter Name="@AllPCsIT_Number" Value='<%# IIf((Request.QueryString("ITNumber") <> Nothing), Request.QueryString("ITNumber"), "") %>' Type="WChar" />
  </Parameters>
</MM:DataSet>
<MM:DataSet
id="connectedtowall"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAudit") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABASETYPE_connAudit") %>'
CommandText='<%# "SELECT * FROM connectedtowall" %>'
Debug="true"
></MM:DataSet>
<MM:DataSet
id="netwconntype"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAudit") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABASETYPE_connAudit") %>'
CommandText='<%# "SELECT * FROM NetworkConnectType" %>'
Debug="true"
></MM:DataSet>
<MM:DataSet
id="OffMacros"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAudit") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABASETYPE_connAudit") %>'
CommandText='<%# "SELECT * FROM OfficeMacros" %>'
Debug="true"
></MM:DataSet>
<MM:DataSet
id="dsmodem"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAudit") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABASETYPE_connAudit") %>'
CommandText='<%# "SELECT * FROM modem" %>'
Debug="true"
></MM:DataSet>
<MM:DataSet
id="dsIntConn"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAudit") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABASETYPE_connAudit") %>'
CommandText='<%# "SELECT * FROM InternetConnection" %>'
Debug="true"
></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
<script language="VB" runat="server">
Sub SubmitBtn_Click(sender As Object, e As EventArgs)
Session("ddlmodem")=Request("ddlmodem")
Session("SerialNumber")=Request("SerialNumber")
Session("DeviceType")=Request("DeviceType")
Session("netwconn")=Request("netwconn")
Session("pcwallport")=Request("pcwallport")
Session("othercable")=Request("othercable")
Session("cabledtocommsr")=Request("cabledtocommsr" )
Session("keyboardswipe")=Request("keyboardswipe")
Session("mouseused")=Request("mouseused")
Session("OS")=Request("OS")
Session("sstperformed")=Request("sstperformed")
Session("officemacro")=Request("officemacro")
Session("ConnWall")=Request("ConnWall")
Session("ddlIntConn")=Request("ddlIntConn")
Session("ISPname")=Request("ISPname")
Session("hostswitching")= Request("hostswitching")
Session("airlines")=Request("airlines")
Response.Redirect(OSlink.SelectedItem.Value)
End Sub

</script>



<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif}
-->
</style>


<title>AllPCs_Terminals</title><form runat='server' method='POST' name='form1' class="style1">
  <table width="655" border=0 align=CENTER cellpadding=0 cellspacing=0 bgcolor=#6699CC>
    <tr>
      <th height="37" colspan=3 align=CENTER>All PCs and Laptops
      <input name="ITNumber" type="text" id="ITNumber" value="<%= Session("ITNumber") %>" readonly="true"></th>
    </tr>
    <tr>
      <td width="259">FNLOC</td>
      <td colspan="2"><input name="FNLOC" type="text" id="FNLOC" > </td>
    </tr>
    <tr>
      <td>Connected to </td>
      <td colspan="2"><asp:DropDownList ID="ConnWall" DataSource="<%# connectedtowall.DefaultView %>" DataTextField="connected to" DataValueField="connected to" runat="server"></asp:DropDownList></td>
    </tr>
    <tr>
      <td>Wall Port </td>
      <td colspan="2"><input name="pcwallport" type="text" id="pcwallport"></td>
    </tr>
    <tr>
      <td>Network Connection or Cable Type</td>
      <td width="152"> <asp:DropDownList ID="netwconn" DataSource="<%# netwconntype.DefaultView %>" DataTextField="NetworkConnectType" DataValueField="NetworkConnectType" runat="server"></asp:DropDownList>
      If Other </td>
      <td width="244"><input name="othercable" type="text" id="othercable"></td>
    </tr>
    <tr>
      <td>Cabled to (Comms Room Name) </td>
      <td colspan="2"><input name="cabledtocommsr" type="text" id="cabledtocommsr"></td>
    </tr>
    <tr>
      <td>Keyboard Swipe Used </td>
      <td colspan="2"><asp:CheckBox ID="keyboardswipe" Text='<%= Session("keyboardswipe") %>' runat="server" Enabled="true" /></td>
    </tr>
    <tr>
      <td>Mouse Used </td>
      <td colspan="2"><asp:CheckBox ID="mouseused" Text='<%= Session("mouseused") %>' runat="server" Enabled="true" /></td>
    </tr>
    <tr>
      <td>Operating System</td>
      <td colspan="2"><asp:DropDownList
        OnSelectedIndexChanged="SubmitBtn_Click" ID="OSlink" runat="server">
            <asp:ListItem value="">Choose</asp:ListItem>
            <asp:ListItem value="/DOSorOS2.aspx">DOS</asp:ListItem>
            <asp:ListItem value="/DOSorOS2.aspx">OS2</asp:ListItem>
            <asp:ListItem value="/site2.aspx">Win3.11</asp:ListItem>
<asp:ListItem value="/sessions.aspx">Win9x</asp:ListItem>
<asp:ListItem value="/sessions.aspx">Win2k</asp:ListItem>
            </asp:DropDownList>
      </td>
    </tr>
    <tr>
      <td>SST Performed </td>
      <td colspan="2"><asp:CheckBox ID="sstperformed" Text='<%= Session("sstperformed") %>' runat="server" Enabled="true" /></td>
    </tr>
    <tr>
      <td>Office Macros Used </td>
      <td colspan="2"><asp:DropDownList ID="officemacro" DataSource="<%# OffMacros.DefaultView %>" DataTextField="OfficeMacros" DataValueField="OfficeMacros" runat="server"></asp:DropDownList></td>
    </tr>
    <tr>
      <td>Modem</td>
      <td colspan="2"><asp:DropDownList ID="ddlmodem" DataSource='<%# dsmodem.DefaultView %>' DataTextField="modem" DataValueField="modem" runat="server"></asp:DropDownList></td>
    </tr>
    <tr>
      <td>Internet Connection </td>
      <td colspan="2"> <asp:DropDownList ID="ddlIntConn" DataSource="<%# dsIntConn.DefaultView %>" DataTextField="InternetConnection" DataValueField="InternetConnection" runat="server"></asp:DropDownList>
        If ISP, which one
    <input name="ISPname" type="text" id="ISPname"></td></tr>
    <tr>
      <td>Host Switching </td>
      <td colspan="2"><asp:CheckBox ID="hostswitching" Text='<%= Session("hostswitching") %>' runat="server" /></td>
    </tr>
    <tr>
      <td>Airlines</td>
      <td colspan="2"><input name="airlines" type="text" id="airlines"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td colspan="2"><asp:Button ID="Submit" runat="server" Text="Submit" /></td>
    </tr>
    <tr>
      <td align=CENTER colspan=3> </table>
  <input name="DeviceType" type="hidden" id="DeviceType" value='<%= Session("DeviceType") %>'>
<input type="hidden" name="MM_insert" value="form1">
</form>

This page only has MM_insert. I can also create a MM_update but as I said, I do not know how to write the statement if... then if... then end if...

Thanks so much for taking the time to read this.

( I have another issue with the ddl... I don't seem to be able to capture the right input. So far, the database is updated with the link not with the value of the field... for example, if the user chooses DOS, the database is updated with /DosorOS2.aspx)

Right, I finally understand the situation. The confusion was caused by the fact that you posted in the wrong forum. This is a "classic" ASP forum, while your question pertains to ASP.NET. In the future, you're probably better off posting in one of the .NET forums.

Anyway, I think you're much better of separating the Insert from the Update page. The amount of code that Dreamweaver generates with its Insert and Update behaviors make your pages pretty difficult to understand and manage, especially when you're mixing these behaviors. When I use Dreamweaver and its Server Behaviors, I usually have three pages:

1. List.aspx / Default.aspx
This page displays a list of editable records. An Update button takes you to UpdateItem.aspx. As a QueryString, I pass the ID of the record. A "Create New" button takes you to CreateItem.aspx

2. CreateItem.aspx
This pages contains a single Insert Record behavior

3. UpdateItem.aspx
This pages contains an Update Record behavior and a DataSet.

With this setup, I find things really easy to manage. The only downside is that you have to design and layout your form twice.

An alternative is to create a complete custom InsertUpdate page. Don't use the MM behaviors, but code your own logic that determine when to insert and when to update the record. Much more work, but you have full control.

That said, it think it *may* be possible to have one single CreateUpdate page with DW behaviors. If you look at each MM:Insert and MM:Update, you'll see they expose an Expression property. If you look into th source of the Dreamweaver controls (located here: C:\Program Files\Macromedia\Dreamweaver MX 2004\Configuration\ServerBehaviors\Shared\ASP.Net\ Scripts\Source\DreamweaverCtrls.cs) you'll see that Expression determines whether the control executes its internal methods. The Expression looks like this:

<%# Request.Form("MM_update") = "form1" %>

which means the Update will only be fired when the hidden MM_update contains form1. You can use this expression to determine whether to fire the Insert or Update behavior. If you merge the two forms that DW has created, you'll end up with one hidden field for the form name and one hidden field called theID that holds the ID of your record. When the ID is "", you're Inserting a record, otherwise you're updating.

So, for the MM:Insert you probably need this expression:

  Expression='<%# Request.Form("theID") = "" %>'

and for the MM:Update you need this one:

  Expression='<%# Request.Form("theID") <> "" %>'

You should also extend the expressions with something like this:

  And Request.Form("MM_insert") = "form1"

to make sure the code only runs when the page has been posted back.

As you can see, this solution requires quite some work. If I were you, I'd separate the insert and update pages. Is there any reason you want to combine these two??

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks Imar for your time and patience.

i am a bit lost now. just not used to that type of thinking I guess.

The tables I have to bind to the forms are not very easy to work with. In some cases, there's data, in some there isn't.
So I still want to try to get the update or insert on the same button.

I don't quite understand the merging of the two insert and update.
I thought there was a way of writing some code for the server to decide if the recordset is empty, insert, if it has the record, update.

Thanks again for all your help.
Dorothee

Hmmm, I am lost as well ;)

How do you arrive at your update page? How is it possible that you think you are going to update something, just to find out the record doesn't exist and you want to insert a new one? Somehow, that doesn't make sense. In most applications I have built, I know there is something to update, so I redirect to the Update page. If I know no record exists, I redirect to the Create page.

In what circumstances do you not know in advance wether the record exists?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.