asked on

How do I check for presence of record before trying to insert another unique ID?

Dear Experts,

I need to insert students into a MSSql db. Their student ID number (student_id) is the Primary Key for the table. If I try to insert a student already in the db I get the yellow and white error page telling me I'm trying to insert a duplicate value. I am using a DetailsView Control. I've attached the code.

In the code behind I need to check for the presence of the student in the db BEFORE inserting the student. If the student is present in the db then the user needs to be redirected to another page. If the student is not present in the db the record needs to be inserted.

This is the code behind I have so far:

Partial Class Teachers_AddStudentInfoA
Inherits System.Web.UI.Page

Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
e.KeepInInsertMode = True

Dim myConnection As New SqlConnection("Data Source=****;Initial Catalog=****************;uid=********;pwd=*************")
myConnection.Open()
Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id = DetailsView1.student_id.value", myConnection)
Dim count As Integer = CInt(myCommand.ExecuteScalar())

If count > 0 Then
myConnection.Close()

Response.Redirect("http://www.finaosms.com/application/episd2/teachers/AddStudentInfoA.aspx", True)

End If
myConnection.Close()

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
DetailsView1.ChangeMode(DetailsViewMode.Insert)

End Sub

End Class

The error message I receive is:
The multi-part identifier "DetailsView1.student_id.value" could not be bound.

This should be a simple process. However, I'm somewhat new to ASP.net and VB so I'm sure I'm missing something.

Can you help me with my code to check for the presence of the student_id in the db and then depending on whether it's there or not either rredirecting the user or inserting the data.

Thank you.

<form id="form1" runat="server">
    <div>
    
    <h2>            Enter an SID</h2>         
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:EpisdConnectionString %>" 
            DeleteCommand="DELETE FROM [StudentInfoA_local] WHERE [student_id] = @student_id" 
            InsertCommand="INSERT INTO [StudentInfoA_local] ([last_name], [first_name], [student_id]) VALUES (@last_name, @first_name, @student_id)" 
            SelectCommand="SELECT [last_name], [first_name], [student_id] FROM [StudentInfoA_local]" 
            UpdateCommand="UPDATE [StudentInfoA_local] SET [last_name] = @last_name, [first_name] = @first_name WHERE [student_id] = @student_id">
            <DeleteParameters>
                <asp:Parameter Name="student_id" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="last_name" Type="String" />
                <asp:Parameter Name="first_name" Type="String" />
                <asp:Parameter Name="student_id" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="last_name" Type="String" />
                <asp:Parameter Name="first_name" Type="String" />
                <asp:Parameter Name="student_id" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" 
            AutoGenerateRows="False" DataKeyNames="student_id" 
            DataSourceID="SqlDataSource1" Height="50px" Width="125px">
            <Fields>
                <asp:BoundField DataField="last_name" HeaderText="last_name" 
                    SortExpression="last_name" />
                <asp:BoundField DataField="first_name" HeaderText="first_name" 
                    SortExpression="first_name" />
                <asp:BoundField DataField="student_id" HeaderText="student_id" ReadOnly="True" 
                    SortExpression="student_id" />
                <asp:CommandField ButtonType="Button" ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
    </div>
    </form>

Open in new window

HainKurt

use exception

try
...insert here
catch
... check for duplicate error, put a message on page
finally
... cleanup db connections
end try

HainKurt

oops, I guess I got it wrong...

use inserting event to check the id before insert really happens, if there is one, cancel the event...
if there is id in db, set cancel=true in this event

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserting.aspx

ajb2222

it is an sql error in this line

Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id = DetailsView1.student_id.value", myConnection)

it should look like this

Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id = " & DetailsView1.student_id.value.ToString(),myConnection)

HooKooDooKu

I'm not an ASP programmer, so there might be something I'm missing...

But to me, it seems the simple answer is to write a "StudentExists( ID )" subroutine that determines if the record already exists by either doing a "count" Query (SELECT COUNT(*) AS COUNTER FROM tablename WHERE ID=thegiveid and determine the value of "COUNTER"), or simply get a recordset and use EOF to determine if the recordset is empty. If no records were found that match the given ID, the function can return FALSE.

Alpesh Patel

Hi,

You primary key is identity column then no problem exist.

But if not then get Max ID from table and increment and insert it in else redirect to another page,

bobbellows

ASKER

PatelAlpesh: The student_id is given by the client. I cannot change it. It is not an identity column.

HooKooDooKu: That's pretty much what I'm trying to do. See lines in code behind.

HainKurt: Thanks for pointnig me to "Inserting" -- I missed this too. However I think the cancel event might serve the same purpose as the redirect. PLEASE CHECK CODE BELOW to see if that might work. There is still a problem with the command line. Please also double check my If/Then statements and cancel statements -- first time I ever used them -- since I'm fairly new to all of this. The error message I get is: 'student_id' is not a member of 'System.Web.UI.WebControls.DetailsView'.

ajb2222: PLEASE CHECK CODE BELOW. There is still a problem with the command line. Please also double check my If/Then statements and cancel statements -- first time I ever used them -- since I'm fairly new to all of this. The error message I get is: 'student_id' is not a member of 'System.Web.UI.WebControls.DetailsView'.

Imports System.Web.UI.WebControls.DetailsViewInsertEventArgs

Partial Class Application_episd2_Teachers_ValidateSIDtest
Inherits System.Web.UI.Page

Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
Dim myConnection As New SqlConnection("Data Source=******;Initial Catalog=**************;uid=*************;pwd=**************")
myConnection.Open()

Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id = " & DetailsView1.student_id.value.ToString(), myConnection)
Dim count As Integer = CInt(myCommand.ExecuteScalar())

If count > 0 Then
e.cancel = True
End If

myConnection.Close()

End Sub

Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
e.KeepInInsertMode = True

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
DetailsView1.ChangeMode(DetailsViewMode.Insert)

End Sub

End Class

ASKER CERTIFIED SOLUTION

ajb2222

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bobbellows

ASKER

ajb2222:
I think we're getting closer. Now I get the error:
Identifier expected.

I don't know what it's expecting.
Thanks
Bob

ajb2222

what line is giving you the error?

bobbellows

ASKER

It was the command line. I have copied it below from the error page.

Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id = " & e.Values["student_id"].ToString(), myConnection)

ajb2222

is e.Values["student_id"].ToString() giving you a valid value?

is student_id a string value in the database? if so then the select needs single quates added.

bobbellows

ASKER

ajb2222:

is e.Values["student_id"].ToString() giving you a valid value?

I don't know how to check this independent from the command line?

is student_id a string value in the database? if so then the select needs single quates added.
No, an integer in the db.

ajb2222

ok,

In the development environment, place a stop on the line that is giving you the error. This will sto the programs execution before running that line.

when the program stops at that line. At the bottom of the screen you will find a watch window. select this. THen type in "e.Values["student_id"].ToString()". this will give you the value at that point of execution.

here is a tutorial about debugging.
http://dotnetperls.com/debugging

bobbellows

ASKER

ajb2222:

I'll read the article today and try to do some of the debugging and let you know how it goes.

Bob Bellows

bobbellows

ASKER

ajb2222:

I started following the instructions -- realized the problem might be more complicated since I was using the web application that I developed. So, I created a new application with just the default page as the one which does the checking and inserting. I'll let you know how that goes. Thanks Bob

bobbellows

ASKER

ajb2222:

I put a stop in the line that throws the error "Identifier Expected" I ran debugging and I get a dialog box that tells me there were errors and did I want to run the last successful build. This confused me because there wasn't a last successful build. I chose yes. The web browser launched and I got the yellow/white error page with the Identifier Expected" error.

Ran debug again -- at the dialog box chose no. It doesn't appear to stop at the line where I put the break point. An errors panel comes up with the Identifier expected error and a new one "name count is not declared." Not worried about the 2nd one yet because don't have the first one fixed.

I don't get a watch window. All I get is the error window. I went to debugging>windows to open the locals window, as suggested by the article, so I could see the variables and there was no option to choose locals.

Also, if I choose yes at the dialog box and then close the browser, the output panel displays this at the endo of one of the lines: "Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled." so I may not have a debug setting correct.

Where do I go from here?

ajb2222

If you get that messagebox, there are errors that are preventing a build. There is a tab at the bottom that says Error List. This will list errors, warnings, and notices. If you click on an error it will take you to the line the error is on.

You might want to get a good book about visual studio. I get books from the local library.

bobbellows

ASKER

ajb2222:

The error was in your code. You had ...e.Values["student_id"].To... and the [ ] should have been ( ). I got it to finally work. I'll post the code as the solution when I get a chance.

Bob Bellows

ajb2222

sorry,

The hazards of using C# and VB. One uses [] and the other ()

bobbellows

ASKER

The chosen solution had an error I had to research and correct