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

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

RomacaliAsked:
Who is Participating?
 
wht1986Connect With a Mentor Commented:
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
 
wht1986Commented:
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
 
RomacaliAuthor Commented:
Sorry i'm new to ASP, how can I write it?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
St3veMaxCommented:
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
 
wht1986Commented:
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
 
St3veMaxCommented:
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
 
wht1986Commented:
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
 
RomacaliAuthor Commented:
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
 
wht1986Commented:
int rtn = (int)cmd1.ExecuteScalar();
0
 
RomacaliAuthor Commented:
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
 
RomacaliAuthor Commented:
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
 
wht1986Commented:
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
 
RomacaliAuthor Commented:
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
 
wht1986Commented:
I'm on my iPhone again but like it says you are missing a closing } bracket
0
 
RomacaliAuthor Commented:
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
 
RomacaliAuthor Commented:
Another thing, I had the try.. catch to validate my UserID that cannot be the same
0
 
RomacaliAuthor Commented:
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
 
wht1986Commented:
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
 
RomacaliAuthor Commented:
ok thanks. I will try
have a great party :)
0
 
RomacaliAuthor Commented:
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
 
wht1986Commented:
no no i used square brackets not () in the connection string
0
 
RomacaliAuthor Commented:
thanks!!! you are the best!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.