i want to insert data into a table by matching same fields of two tables

Question

0.00/5 (No votes)

See more:

this is my code

SQL

command.CommandText = "insert into BasicDtl_tbl([Nationality],[Post]) values ('" + Nattxt.Text + "','" + apldposttxt.Text + "') WHERE UserName =(select UserName from UserReg)";

i want to enter the some details into a basicdtl table by matching the unique username stored in both basicdtl and userreg table

Posted 28-May-14 4:43am

Ashwini Thakare

Updated 28-May-14 4:58am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 28-May-14 10:59am

You probably want a JOIN.

CHill60 28-May-14 11:01am

Are you trying to update a single UserName row in BasicDtl_tbl or all of them as long as they also exist in UserReg?

Ashwini Thakare 28-May-14 11:18am

i want to update single username row in basicdtl_tbl by matching username column of both basicdtl_tbl and usereg table so that data will get update in proper username

CHill60 28-May-14 11:21am

So UserReg only contains one user? Otherwise why are you concerned about that table - presumably the user name has been entered somewhere?

Ashwini Thakare 28-May-14 11:29am

actually i m working on fill application form database where i want to save data in various tables but at the same time the data should get saved in proper row so i am using one common field username in every table. userreg table contains username field as primary key which will be used to access all tables.

CHill60 28-May-14 12:33pm

Sorry I didn't make myself clear. How do you know which UserName the textbox data refers to?

Ashwini Thakare 28-May-14 12:40pm

if i enter username value into a textbox displayed in fil form application
write a query to insert data into table by using where clause like the following code

command.CommandText = "insert into BasicDtl_tbl([Nationality],[Post]) values ('" + Nattxt.Text + "','" + apldposttxt.Text + "') WHERE UserName =usertxt.Text ";

is this the right way of writing a query

Peter Leow 28-May-14 11:03am

What is the relationship between basicdtl_tbl table and userreg table? what is this UserName in the WHERE UserName?

Ashwini Thakare 28-May-14 11:21am

username is a common field in both userreg and basicdtl_tbl. username field in userreg is primary key and username field in basicdtl_tbl is foriegn key

2 solutions

Solution 1

In response to your comment

Quote:
if i enter username value into a textbox displayed in fil form application
write a query to insert data into table by using where clause like the following code

command.CommandText = "insert into BasicDtl_tbl([Nationality],[Post]) values ('" + Nattxt.Text + "','" + apldposttxt.Text + "') WHERE UserName =usertxt.Text ";

is this the right way of writing a query

- you are very close. You don't need the join to table UserReg, as you have worked out. I would advise you to always use Parameterized Queries[^] when using data entered by a user. For example

C#

string sql = "insert into BasicDtl_tbl([Nationality],[Post]) values (?,?) WHERE UserName =?";
// where conn is your connection...
using (OleDbCommand command = new OleDbCommand(SqlString, conn)) 
{
  command.CommandType = CommandType.Text;
  command.Parameters.AddWithValue("Nationality", Nattxt.Text);
  command.Parameters.AddWithValue("Post", apldposttxt.Text);
  command.Parameters.AddWithValue("Post", usertxt.Text);
  command.ExecuteNonQuery();
}

Notice all I've done is replace the textboxes in your sql with question marks (?) and added the lines to create the parameters

Posted 28-May-14 7:12am

CHill60

Comments

Ashwini Thakare 28-May-14 13:33pm

i have already inserted username in the table now i just want to insert values from textbox into nationality and post column by matching username column value with value of usertxt textbox
my table basicdtl_tbl contains 3 columns i.e username, nationality, post

Ashwini Thakare 31-May-14 9:59am

<asp:FileUpload ID="FileUploadControl1" runat="server" />
i want to save image in ms access database. image is stored as oleobject.
how to write update statement to insert image using where clause

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)

Peter Leow · Accepted Answer · 2014-05-28T14:02:00

Since you already have username in the table, you should be using update sql:

SQL

sqlcmd.CommandText = "UPDATE BasicDtl_tbl SET nationality = @nationality, post = @post WHERE username = @username";
sqlcmd.Parameters.Add(AddDbParmSpecificValue( "@nationality", nationtxt.Text));
sqlcmd.Parameters.Add(AddDbParmSpecificValue( "@post", posttxt.Text) );
sqlcmd.Parameters.Add(AddDbParmSpecificValue( "@username", usertxt.Text ) );