How to insert SQL parameter's value in C#

Question

0.00/5 (No votes)

See more:

string equery = "Insert into leave(UserId,leaveType,seasonLeave,Reason)values(@UserId,'" + leavetype.Text.ToString().Trim() + "',@seaonleave,'" + reason.Text.ToString().Trim() + "')";

Where in this query UserId is an foreign key, how i call the @UserId in the C#.

Posted 20-Aug-13 22:09pm

Gokul Athithan

Updated 21-Aug-13 0:48am

v3

Add a Solution

2 solutions

Solution 2

You don't call it - you need to provide it as a parameter. And while you are doing that, do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead throughout.

C#

string equery = "INSERT INTO leave (UserId, leaveType, fromDate, toDate, numdays, seasonLeave, Reason) VALUES(@UserId, @Type, xx, xx, xx, @seaonleave, @Reason)";
using (SqlCommand cmd = new SqlCommand(equery, con))
   {
   cmd.Parameters.AddWithValue("@UserId", myUserId);
   cmd.Parameters.AddWithValue("@Type", leavetype.Text.Trim());
   ... // Continue here with more parameters and so forth
   }

I added the "xx" parts to indicate where you need to specify data that you missed in your original - SQL will complain if you don;t supply sufficient values for the columns you listed.

Posted 20-Aug-13 22:39pm

OriginalGriff

Comments

Joezer BH 21-Aug-13 4:41am

5ed!

Adarsh chauhan 21-Aug-13 4:58am

Good One.. +5

Gokul Athithan 21-Aug-13 5:01am

what is tha myUserId

OriginalGriff 21-Aug-13 5:02am

The value you want to insert in the table...

Gokul Athithan 21-Aug-13 6:01am

how i want to specify the myUserId in the Program

OriginalGriff 21-Aug-13 6:06am

How do you expect us to know? It's your foreign key so it has to match a value in a different table! Only you can tell what table, or how you decided that it is the value you wanted to save the data against...

Asking us to tell you based on the info you have given us is like me asking you "where are my keys?". Can you tell me, to within even a 1Km radius? Never mind which room, or drawer / pocket / bowl/ where the cat has taken them - a 1Km radius of the actual location will be fine.

Gokul Athithan 21-Aug-13 6:53am

Please give one example for the function of myUserId. And What myUserId is meant in that above example You have given.

OriginalGriff 21-Aug-13 8:30am

I can't - I do not know what your user id is!

Gokul Athithan 21-Aug-13 7:25am

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_leave_Users1". The conflict occurred in database "Employee",table "dbo.Users", column UserId.
The statement has been terminated.

I am having this Exception

OriginalGriff 21-Aug-13 8:32am

Yes - because the user id you have supplied is not in the other table! That is waht a Foreign key is: it's a value which links two tables. You can only insert values into a foreign key column if a matching value exists in the other table.

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)

**Joezer BH** · Accepted Answer · 2013-08-20T22:36:00

Add parameters to your Command Parameters using:
command.Parameters.AddWithValue(...)

Example Given with your query string:

C#

string equery = "Insert into leave(UserId,leaveType,fromDate,toDate,numdays,seasonLeave,Reason)values(@UserId,'" + leavetype.Text.ToString().Trim() + "',@seaonleave,'" + reason.Text.ToString().Trim() + "')";

SqlConnection connection = new SqlConnection(MyConnectionString);
SqlCommand command = new SqlCommand(equery , connection);

// Adding the value to the parameter "UserID"
command.Parameters.AddWithValue("UserId", txtUserID.Text); 

// Add all your parameters this way ...

Cheers,
Edo