?
Solved

How do I validate if an numeric field address exist in a database?

Posted on 2009-04-21
22
Medium Priority
?
345 Views
Last Modified: 2013-12-17
I have to validate my OSHPDID to make sure it exist in a table in my database, otherwise the user has to enter another OSHPDID.

See below my save button
protected void btnSave_Click(object sender, EventArgs e)
    {
 
      
        SqlCommand cmd;
        cmd = new SqlCommand("sp_AddNew_HospitalLogin", myConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@OSHPDID", txtOSHPDID.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@FullName", txtFullName.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@UserID", txtUserID.Text.Trim()));
 
        cmd.Parameters.Add(new SqlParameter("@Pwd", txtPwd.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@Email", txtEmail.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@PhoneNo", txtPhoneNo.Text.Trim()));
        try
        {
            myConnection.Open();
            cmd.ExecuteNonQuery();
            Response.Redirect("Updates_Inserts.aspx", true);
        }
        catch (Exception s)
        {
            lblMessage.Text = "UserID already exist. Enter a valid UserID.";
            
        }
        finally
        {
            myConnection.Close();
            myConnection.Dispose();
        }
    }

Open in new window

0
Comment
Question by:Romacali
  • 11
  • 9
  • 2
22 Comments
 
LVL 12

Expert Comment

by:wht1986
ID: 24197620
How about somthing like

SELECT COUNT(*) FROM HospitalLogin where OSHPDID = @OSHPDID

using ExecuteScalar  then you will get back a 1 if it exists or a 0 if it doesnt
0
 

Author Comment

by:Romacali
ID: 24197709
Sorry i'm new to ASP, how can I write it?
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24197824
Little tip to help out; The proc you're calling: sp_AddNew_HospitalLogin

It's not a good idea to prefix with sp_ as SQL will then think it's a system procedure and look in the wrong place for it and thus slow things a little.

Best Practice is to call it usp_

HTH
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:wht1986
ID: 24197866
good point st3ve, sql server likes to use sp_ for its own evil purposes

SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM HospitalLogin where OSHPDID = @OSHPDID", connection);
cmd.Parameters.Add("@OSHPDID", int.Parse(txtOSHPDID.Text));

int rtn = cmd.ExecuteScalar();

rtn will be a 1 if that ID exists and 0 if it does not

sorry for any syntax errors, im responding via my iphone and cant validate it against vstudio
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24197949
Also;

Something I was told some time ago; I would use a SELECT COUNT(1) as opposed to SELECT COUNT(*) as depending on your connection method/driver; i'll assume ODBC; it will count every column for x rows, where as specifying '1', it only counts the rows for the first column; thus using less memory in the ODBC layer...

HTH
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24198120
That is correct st3ve i think as long as column 1 is indexed and non null (aka the primary key column) it should indeed be a little faster and more efficiient.  If that is the case, follow st3ve's advice and use that instead of count(*)
0
 

Author Comment

by:Romacali
ID: 24198541
I tried:
        SqlCommand cmd1 = new SqlCommand("SELECT COUNT(*) FROM Hospital where OSHPDID = @OSHPDID", myConnection);
        cmd1.Parameters.Add("@OSHPDID", int.Parse(txtOSHPDID.Text));

        int rtn = cmd1.ExecuteScalar();
and it gave me this error: pointing to :Line 25:         int rtn = cmd1.ExecuteScalar();
 Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0266: Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)

Source Error:

Line 23:         cmd1.Parameters.Add("@OSHPDID", int.Parse(txtOSHPDID.Text));
Line 24:
Line 25:         int rtn = cmd1.ExecuteScalar();
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24198587
int rtn = (int)cmd1.ExecuteScalar();
0
 

Author Comment

by:Romacali
ID: 24198622
when I tried to save the data it gave me this error:
Server Error in '/' Application.
Value was either too large or too small for an Int32.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.OverflowException: Value was either too large or too small for an Int32.

Source Error:
0
 

Author Comment

by:Romacali
ID: 24198799
I got this error now: pointing to:       cmd.ExecuteNonQuery();
 Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0103: The name 'cmd' does not exist in the current context

see below my code for this save button
Source Error:

Line 44:         {
Line 45:             myConnection.Open();
Line 46:             cmd.ExecuteNonQuery();
Line 47:             Response.Redirect("Updates_Inserts.aspx", true);
Line 48:         }

 protected void btnSave_Click(object sender, EventArgs e)
    {
        SqlCommand cmd0 = new SqlCommand("SELECT COUNT(*) FROM Hospital where OSHPDID = @OSHPDID", myConnection);
        cmd0.Parameters.Add("@OSHPDID", int.Parse(txtOSHPDID.Text));
 
        int rtn = (int)cmd0.ExecuteScalar();
             if (rtn == 1) 
             {
        
                 SqlCommand cmd;
        cmd = new SqlCommand("sp_AddNew_HospitalLogin", myConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@OSHPDID", txtOSHPDID.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@FullName", txtFullName.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@UserID", txtUserID.Text.Trim()));
 
        cmd.Parameters.Add(new SqlParameter("@Pwd", txtPwd.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@Email", txtEmail.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@PhoneNo", txtPhoneNo.Text.Trim()));
             }
             else {
             } lblMessage2.Text = "Enter a valid OSHPDID.";
 
        try
        {
            myConnection.Open();
            cmd.ExecuteNonQuery();
            Response.Redirect("Updates_Inserts.aspx", true);
        }
        catch (Exception s)
        {
            lblMessage.Text = "UserID already exist. Enter a valid UserID.";
            
        }
        finally
        {
            myConnection.Close();
            myConnection.Dispose();
        }
    }

Open in new window

0
 
LVL 12

Expert Comment

by:wht1986
ID: 24199416
its because you defined the SqlCommand cmd inside the if block, therefore it is out of scope when it gets to the try catch block,  check out my code below, it should work for you, just replace the empty strings with the textbox values.  By enclosing the connection in the using statement, you are ensured no matter what happens the connection object will be closed and disposed of when the scope ends.
using (SqlConnection myConnection = new SqlConnection("your_connection_string_here"))
{
 
    // create the check command
    SqlCommand cmd0 = new SqlCommand("SELECT COUNT(*) FROM Hospital where OSHPDID = @OSHPDID", myConnection);
    cmd0.Parameters.AddWithValue("@OSHPDID", 1);
 
    // create the insert command
    SqlCommand cmd = new SqlCommand("sp_AddNew_HospitalLogin", myConnection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@OSHPDID", "");
    cmd.Parameters.AddWithValue("@FullName", "");
    cmd.Parameters.AddWithValue("@UserID", "");
    cmd.Parameters.AddWithValue("@Pwd", "");
    cmd.Parameters.AddWithValue("@Email", "");
    cmd.Parameters.AddWithValue("@PhoneNo", "");
 
    try
    {
        myConnection.Open();
 
        if ((int)cmd0.ExecuteScalar() == 1)
        {
            cmd.ExecuteNonQuery();
            Response.Redirect("Updates_Inserts.aspx", true);
        }
        else
        {
            lblMessage2.Text = "Enter a valid OSHPDID.";
        }
 
    }
    catch (Exception exc)
    {
    }
    finally
    {
        myConnection.Close();
    }
}

Open in new window

0
 

Author Comment

by:Romacali
ID: 24199708
now I go this:

 Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1026: ) expected

Source Error:

Line 23:    protected void btnSave_Click(object sender, EventArgs e)
Line 24:    {
Line 25:       Using (SqlConnection myConnection = new SqlConnection("SCPSCConnectionString2"))
Line 26:    {
Line 27:  
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24199789
I'm on my iPhone again but like it says you are missing a closing } bracket
0
 

Author Comment

by:Romacali
ID: 24199809
when I click on SAVE
it gives me:
Server Error in '/' Application.
Format of the initialization string does not conform to specification starting at index 0.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Source Error:
0
 

Author Comment

by:Romacali
ID: 24199831
Another thing, I had the try.. catch to validate my UserID that cannot be the same
0
 

Author Comment

by:Romacali
ID: 24199842
in this add new user I need to make sure the OSHPDID exist on table Hospital
and userID is unique on table Login

thanks for taking the time to help me
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24200276
I think that is cause you used  new SqlConnection("SCPSCConnectionString2"))

"SCPSCConnectionString2" is not a valid connection string

I assume your connection string is in your web.config file?

so the correct syntax would be something like

 new SqlConnection(ConfigurationManager.ConnectionStrings["SCPSCConnectionString2"].ConnectionString))

Assuming SCPSCConnectionString2 is the name of the key in the connectionstring section of the web.config

a single try catch should be fine. if the user is found to exist it sets the error message and exits.  I'm hanging out at my sons b-day party atm, if you still have problems later ill give you actual working code.
0
 

Author Comment

by:Romacali
ID: 24200353
ok thanks. I will try
have a great party :)
0
 

Author Comment

by:Romacali
ID: 24200408
I tried the new thing but I got this:
 Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0118: 'System.Configuration.ConfigurationManager.ConnectionStrings' is a 'property' but is used like a 'method'

Source Error:

Line 42:     protected void btnSave_Click(object sender, EventArgs e)
Line 43:     {
Line 44:         using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings("SCPSCConnectionString2").ConnectionString))
Line 45:         {
Line 46:             // create the check command
  protected void btnSave_Click(object sender, EventArgs e)
    {
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings("VaioString2").ConnectionString))
        {
            // create the check command
            SqlCommand cmd0 = new SqlCommand("SELECT COUNT(*) FROM Hospital where OSHPDID = @OSHPDID", myConnection);
            cmd0.Parameters.AddWithValue("@OSHPDID", 1);
 
            // create the insert command
            SqlCommand cmd;
            cmd = new SqlCommand("sp_AddNew_HospitalLogin", myConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@OSHPDID", txtOSHPDID.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@FullName", txtFullName.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@UserID", txtUserID.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Pwd", txtPwd.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Email", txtEmail.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PhoneNo", txtPhoneNo.Text.Trim()));
 
 
            try
            {
                myConnection.Open();
                if ((int)cmd0.ExecuteScalar() == 1)
                {
 
                    cmd.ExecuteNonQuery();
                    Response.Redirect("Updates_Inserts.aspx", true);
                }
 
                else
                {
                    lblMessage2.Text = "Enter a valid OSHPDID";
                }
            }
            catch (Exception exc)
            {
                lblMessage.Text = "UserID already exist. Enter a valid UserID.";
 
            }
 
 
            finally
            {
                myConnection.Close();
                //myConnection.Dispose();
            }
        }
    }

Open in new window

0
 
LVL 12

Expert Comment

by:wht1986
ID: 24200498
no no i used square brackets not () in the connection string
0
 
LVL 12

Accepted Solution

by:
wht1986 earned 2000 total points
ID: 24200519
Also can you tell me, are all the values strings or are some integers or dates, etc?

You will want to cast them to the correct types for the SqlParameters, e.g.

if OSHPDID might be an integer
cmd.Parameters.Add(new SqlParameter("@OSHPDID", int.Parse(txtOSHPDID.Text.Trim())));
           

done forget to change
cmd0.Parameters.AddWithValue("@OSHPDID", 1);
to
cmd0.Parameters.AddWithValue("@OSHPDID", txtOSHPDID.Text.Trim());
cmd0.Parameters.AddWithValue("@OSHPDID", int.Parse(txtOSHPDID.Text.Trim()));

if you want tell me what the table structure is for each table and i will give you working code.
0
 

Author Closing Comment

by:Romacali
ID: 31572946
thanks!!! you are the best!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

750 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