Click here to Skip to main content
13,593,048 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi!

I have public SQL server and i want to resolve, user fill the postal code and leave textbox then execute one sqlquery to what autofill the correct city for postal code

What I have tried:

private void txt_tanul_irszam_Leave(object sender, System.EventArgs e)
{
    if (txt_tanul_irszam.Text!=null)
    {
        string connectionstring = null;
        SqlConnection con;
        connectionstring = "Data Source ="";
        con = new SqlConnection(connectionstring);
    }
}
Posted 14-Jun-18 11:25am
Updated 3 days ago
v2
Comments
Eric Lynch 14-Jun-18 17:47pm
   
Postal codes are a tricky country-by-country thing. You have three issues to solve: 1) finding the postal code in a free-form address (hopefully you have a fixed field address) 2) a good source of postal code information and 3) a very easy lookup. For the second, check out GeoNames.org. For the third, its really easy to code. Once you know the postal code and have a source of information, simply describe the schema (format of the database) and anyone here can provide a simple solution.
5 days ago
   
The country is fix. I have one country's postal codes with city names
5 days ago
   
Postalcode table(Postal code city column)
Eric Lynch 5 days ago
   
You're reply is a bit terse (and unclear)...not exactly a schema. So, I'm forced to make some assumptions. I'll assume the following: you know your connection string, your table name is "Postalcode", your column names are "postalcode" and "city", both columns are compatible with string (e.g. varchar, nvarchar, etc.), the table is (minimally) indexed by postal code (for performance), you want to use ADO.NET (instead of LINQ to SQL or LINQ to Entities), and your question is how to get the name of a city given a postal code.


If this is all true (a huge IF), the first thing you should know is that most SQL classes in C# are IDisposable, so you should make use of the "using" statement.


Your solution should be approximately as follows. Note, I'm typing it by hand here, so there may be some slight syntax errors.

using (var connection = new SqlConnection(connectionString))
{
connection.Open();

using (var command = new SqlCommand("SELECT city FROM Postalcodes WHERE postalcode=@PostalCode", connection))
{
command.Parameters.AddWithValue("postalcode", "01010");

using (var reader = command.ExecuteReader())
{
string city = reader.Read() ?
reader[0] as string : "Unknown";

if (reader.Read())
city = "Ambiguous";
}
}
}
5 days ago
   
This program will student registartion form. the user typing the correct postal code to postal code textbox when user leave (leave event) the textbox then select city from postalcode where postalcode = what user given. That result print out to city textbox
Eric Lynch 5 days ago
   
Previous response (with possibly corrected table / column names) should do what you want. Instead of assigning to city string, assign to text box.
5 days ago
   
public student_regist_form()
{
InitializeComponent();
}

private void txt_st_postalcode_Leave(object sender, System.EventArgs e)
{
using (var connection = new SqlConnection("Data Source=mypublicip\\SQLEXPRESS2017;Initial Catalog=tanuloiregrendszer; User = myuser; Password=mypassword;"))
{
connection.Open();

using (var command = new SqlCommand("SELECT * City FROM postalcodes WHERE Postalcode=@Postalcode", connection))
{
command.Parameters.AddWithValue("", "01010");

using (var reader = command.ExecuteReader())
{
string txt_st_city = reader.Read() ?
reader[0] as string : "City";

if (reader.Read())
txt_st_city = "City";
}
}
}

}
}

I wrote the code. Run the program without error, but nothing happened.
Eric Lynch 5 days ago
   
I suggest stepping through it in the debugger, "nothing" is a bit too broad of a category to help with. You'd need to isolate which line failed to give you the expected result. At a minimum, your SQL query (which includes "* City") does not appear to be valid. The "*" is generally used to select all columns. In which case, the first column (reader[0]) may not be the city column. The original query I suggested, only includes the city column.

Additionally, the first parameter of AddWithValue omits the parameter name, so that won't work correctly.

Finally, I don't see where in your code you assign a value to the text box. You only assign it to the local variable txt_st_city, which is then discarded (when it passes out of scope).

I'd suggest maybe finding a brief tutorial out there that covers the basics and working your way through that first. Sorry, but Q&A really isn't a place to cover the basics. Its more to answer specific questions.
5 days ago
   
Sorry, i search methods for this way, but i don't find. I have 3 colunm in postalcode SQL table (1. Postalcode (float) 2. City(nvarchar(255)) 3.PartofCity(nvarchar)(255)) Allow Nulls are enabled on each.
Eric Lynch 4 days ago
   
In fairness, I tried to find a nice article on ADO to recommend. Most had some problems. That said, I think you need to work at the basics (walk before you run) a little bit more.

I really want to keep helping, but I think I may be doing more harm than good.

Knowing that "Postalcode" is a datatype of float, you would need to adjust the following line...

command.Parameters.AddWithValue("postalcode", 1010F);

To be clear, it is important that your understand that the first parameter is the name of a SQL parameter (which must match the SQL query). It is also important that you understand that the value is a floating point value (by data type) that matches what the user entered for the postal code.

I still think you need to learn more (SQL, ADO, C#, et al) before you continue. This is the best (ill-advised) that I can continue to help you. After this, I'm done. Programming is difficult when you first start. You truly won't learn anything (or achieve your goal) unless you work through some of the basics yourself.

Apologies if I was at all condescending. Your guesses indicate you may have a talent for this. But, you won't learn unless you try to work some of this out for yourself.
4 days ago
   
I learn software development and this a thesis will be his little part, what we did not learn yet. This a game like that, that if i don't care quite a bit, you get lost.


Richard MacCutchan 5 days ago
   
You just need to do a SQL query to find the city that matches the postal code. What is the problem if you already have the data in your tables?
5 days ago
   
My problem is that, what is method in C# Visual studio after user filled the postal code textbox and when user leave the postal code textbox then autofill the city textbox with the correct city which the user filled the postal code textbox.
F-ES Sitecore 5 days ago
   
Simply restating the problem is not a clarification.

Let's start with the basics...do you have a table that contains which city is related to which postcode? Or is your actual question how to get that data?
5 days ago
   
yes i have that complete table. i have question how to get that data when user enter postalcode to textbox.
If postalcode is correct then fill the city textbox with correct city
Richard MacCutchan 5 days ago
   
You search the table for the postcode. What exactly is your problem in coding a simple search?
5 days ago
   
what is the correct method(code) on textbox leave event?
j snooze 5 days ago
   
Is this a webform or a windows form? Either way you have to connect your xt_tanul_irszam_Leave to an event on the text box. Something like a lost focus event.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You want an SQL Server "scalar" query.

But, frankly, considering the lack of effort you've put out relative to all the people who have been trying to help, I would say you need to do "more work" before you get anymore hand outs.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.180618.1 | Last Updated 16 Jun 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100