Solved

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

Posted on 2010-11-29
20
729 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:bobbellows
  • 9
  • 7
  • 2
  • +2
20 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34230360
use exception

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

Expert Comment

by:HainKurt
ID: 34230384
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
0
 
LVL 6

Expert Comment

by:ajb2222
ID: 34230492
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)
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 34234830
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.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34237612
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,
0
 

Author Comment

by:bobbellows
ID: 34238615
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

0
 
LVL 6

Accepted Solution

by:
ajb2222 earned 500 total points
ID: 34239356
sorry,

you need to use the values being passed in  e.values which are the values being inserted.

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

Author Comment

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

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

Expert Comment

by:ajb2222
ID: 34254335
what line is giving you the error?
0
 

Author Comment

by:bobbellows
ID: 34256331
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)
 

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 6

Expert Comment

by:ajb2222
ID: 34257552
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.

0
 

Author Comment

by:bobbellows
ID: 34266454
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.



0
 
LVL 6

Expert Comment

by:ajb2222
ID: 34267130
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

 
0
 

Author Comment

by:bobbellows
ID: 34282730
ajb2222:

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

Bob Bellows
0
 

Author Comment

by:bobbellows
ID: 34291711
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
0
 

Author Comment

by:bobbellows
ID: 34318337
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?
0
 
LVL 6

Expert Comment

by:ajb2222
ID: 34320133
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.

 
0
 

Author Comment

by:bobbellows
ID: 34342860
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
0
 
LVL 6

Expert Comment

by:ajb2222
ID: 34348013
sorry,  

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

0
 

Author Closing Comment

by:bobbellows
ID: 34435454
The chosen solution had an error I had to research and correct
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now