ASP.NET conditional questions (sorry, long)
MH
A colleague and I are trying to learn ASP.net, but we're relying on
books and websites. He has a better grip on it than I do, but we need
some help.
I'm hoping one of you can tell us if we're on the wrong track, and if
so, put us on the right one.
1. We have been able to insert records into the database, but we don't
have a way to check if the record already exists. If data has already
been submitted for this ID, we don’t want to insert a new record. So we
need some kind of routine attached to the submit button that says
IF this record (ItemChoiceID in Answers where
AssessmentID=LastIncompleteAssessmentID) exists
THEN update the record
ELSE insert a new record.
2. Similarly, we have been able to compare one record to another to get
the latest date. But if there is no initial record (with a date), then
our comparison doesn't work, so we need to insert a record.
We have a page that displays an assessment date:
<p>This child's most recent assessment was completed on <%#
dsMaxDate.FieldValue("AssessmentDate", Container) %></p>
We need to be able to say
IF dsMaxDate.FieldValue("AssessmentDate", Container) = null THEN
insert a new record
ELSE
update a record
END IF
Can this be done with the DW behaviors? If not, can anyone point us to
other resources or share some code with us?
The following code is from one of the pages we are working on. The if
does not work since we are unable to access the dataset.
<%@ Page Language="vb" %>
<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls"
Assembly="DreamweaverCtrls,version=1.0.0.0,publicKeyToken=836f606ede05d46a,culture=neutral"
%>
<MM:DataSet
id="dsMaxDate"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%#
System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_connCPAT")
%>'
DatabaseType='<%#
System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_connCPAT")
%>'
CommandText='<%# "SELECT Assessment.Completed, Assessment.Assessment_ID,
Assessment.Users_Id, Assessment.Student_Id, Assessment.AssessmentDate AS
AssessmentDate FROM Assessment WHERE Assessment.Completed =1 AND
Assessment.student_ID = " & session("sessChildID") &" AND
Assessment.AssessmentDate = (SELECT MAX(Assessment.AssessmentDate)
From Assessment where Assessment.Student_ID = " &
session("sessChildID") &" GROUP BY Assessment.Student_Id )" %>'
Debug="true"
></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
<html>
<head>
</head>
<body>
<form action="" method='POST' name='frmStartNew' runat='server'>
<% if dsMaxDate.Fields.Item("Assessment_ID").Value < 1 then %>
Insert Record with stuff in our session
<% else %>
Update record with a new date and stuff in our session
<% end if %>
</form>
</body>
</html>
Thank you,
Mary
Paul Whitham TMM
If you are trying to learn ASP.Net then I would strongly recommend that you
do not use Dreamweaver to write the code for you. The reason is that MM use
a custom control to handle all of the ADO actions such as datasets, and as
it is not standard code, you will run into problems trying to reference it
with stuff you will read on other sites.
The following code (written in VB) will give you a handle on what would be
required for your insert or update. It assumes that the ID comes from a text
field and is a numeric field, and that you are using an Access database. You
would need to write parameters that match the fields that you have in the
database. The DSN for this file is stored in the web.Config file like this
<add key="dsn" value="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\DotNetConference\Database\Conference.mdb;Persist Security
Info=False" />
< script language="VB" runat="server">
Dim strSQL as String
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Sub Page_Load(sender As Object, e As System.EventArgs)
' Grab the value of the text field
Dim strID as Integer = int(ItemChoiceID.Text)
' Test to see it if is in the database
objConn = New
OleDbConnection(ConfigurationSettings.AppSettings("DSN"))
strSQL = "Select * from tablename where IDField = @FieldName"
objCmd = New OleDbCommand(strSQL, objConn)
objCmd.Parameters.Add("@FieldName", strID)
objConn.Open()
Dim objDS As New DataSet
Dim objDA As New OleDbDataAdapter(objCmd)
objDA.Fill(objDS, "TestIds")
Dim TestCount As Integer
TestCount = objDS.Tables("TestIds").Rows.Count
If TestCount > 1 Then
'The record is in the database write an insert statement
strSQL = "Insert into tablename (Fieldnames) values
(@Fieldnames)"
Else
'Write an update statement
strSQL = "Update tablename set FieldName1 = @FieldName1,
FieldName2 = @FieldName2, where IDField = @FieldName"
End If
'Carry out the action
objConn = New OleDbConnection(ConfigurationSettings.AppSettings("DSN"))
objCmd = New OleDbCommand(strSQL, objConn)
objCmd.Parameters.Add("@Fieldname", Fieldname.text)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
End Sub
</script>
--
Regards
Paul Whitham
Macromedia Certified Professional for Dreamweaver MX2004
Valleybiz Internet Design
www.valleybiz.net
Team Macromedia Volunteer for Ultradev/Dreamweaver MX
www.macromedia.com/support/forums/team_macromedia
"MH" <mary...@nospamswbell.net> wrote in message
news:4211483C...@nospamswbell.net...