How do I do if user leave the postal code textbox then fill the city textbox from SQL server

Question

1.00/5 (3 votes)

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:

C#

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

Member 13873270

Updated 16-Jun-18 15:18pm

User 4180254

v2

Add a Solution

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.

Member 13873270 15-Jun-18 2:35am

The country is fix. I have one country's postal codes with city names

Member 13873270 15-Jun-18 2:38am

Postalcode table(Postal code city column)

Eric Lynch 15-Jun-18 6:27am

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";
}
}
}

Member 13873270 15-Jun-18 6:55am

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 15-Jun-18 7:08am

Previous response (with possibly corrected table / column names) should do what you want. Instead of assigning to city string, assign to text box.

Member 13873270 15-Jun-18 10:02am

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 15-Jun-18 10:30am

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.

Member 13873270 15-Jun-18 11:34am

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 15-Jun-18 23:07pm

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.

Member 13873270 16-Jun-18 8:58am

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 15-Jun-18 4:16am

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?

Member 13873270 15-Jun-18 5:34am

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 15-Jun-18 5:46am

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?

Member 13873270 15-Jun-18 6:27am

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 15-Jun-18 7:28am

You search the table for the postcode. What exactly is your problem in coding a simple search?

Member 13873270 15-Jun-18 7:32am

what is the correct method(code) on textbox leave event?

Richard MacCutchan 15-Jun-18 7:42am

SQL SELECT Statement[^].

j snooze 15-Jun-18 17:24pm

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Answer 1 · 2018-06-16T15:19:00

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.

Posted 16-Jun-18 15:19pm

Gerry Schmitz