How do I validate a zipcode in a textbox and have the corresponding state/city output to their respective labels

I'm trying to write a pretty straight forward program that pulls data from my SQL Server database. And outputs the correct state and city in they're respective labels from a valid zip code entered in a textbox.

Here's some code I was messing around with. It obviously doesn't work I was just trying out.

I was able to populate a combobox with all of the states associated with a zip/city, but that's it.

// Connection string.
String cnStr;
SqlConnection cn = new SqlConnection();

cnStr = "Data Source=000.00.000.00;Initial Catalog= ;User ID= ;Password= ";

// Assign Connection string to the connection object
cn.ConnectionString = cnStr;

// Open the connection to the SQL Server
cn.Open();

// This statement creates the command object and passes in the SQL statement
// then associates the command to the cn connection object
SqlCommand cmd = new SqlCommand("select distinct state, city from tblZipcodes order by state", cn);

// Open a DataReader
SqlDataReader rdrZip = cmd.ExecuteReader();

cn.Close();

The validation of a zipcode entered in a textbox and have the resulting state and city outputted to their respective labels, from SQL Server.

2 answers

  • answered 2019-04-15 07:52 Kattarina

    Blockquote

    If this code is only for filling up the combobox with proper values, depending on inputted zip code, then first you need a parameter for your function, which you will pass to your sql query.

    public void MyFunction ( string myZipCode) 
    {
      String cnStr;
      SqlConnection cn = new SqlConnection();
      cnStr = "Data Source=000.00.000.00;Initial Catalog= ;User ID= ;Password= ";
      cn.ConnectionString = cnStr;
      cn.Open(); 
      var sql = String.Format("select distinct state, city from tblZipcodes where zipcode  = '{myZipCode}', state, city");
      SqlCommand myCommand = new SqlCommand(sql);
      SqlDataReader rdrZip = myCommand.ExecuteReader();
      cn.Close();
    } 
    

  • answered 2019-04-15 08:50 GuidoG

    Always use parameters, never ever build a sql query without parameters, because it leaves your application wide open for sql injection.
    You should use a where clause like this

    var sql = "select distinct state, city from tblZipcodes where zipcode = @ZipCode";
    
    using (SqlCommand cmd = new SqlCommand(sql))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.VarChar) { Value = myZipCode });
    
        SqlDataReader rdrZip = cmd.ExecuteReader();
    }
    

    Use SqlDbType.NVarChar in case your columntype is NVarChar in stead of VarChar