Comparing values in database

Question

0.00/5 (No votes)

See more:

i m devloping timetable as my project. i wanted to compare two field timeslots and class. if they are already available in database then print approprite message otherwise it will store in database. pls help...

Posted 30-Jan-12 1:18am

Jignesh J patel

Updated 30-Jan-12 1:51am

MaulikDusara

v3

Add a Solution

Comments

OriginalGriff 30-Jan-12 7:24am

And what have you tried?
Where are you stuck?

Jignesh J patel 30-Jan-12 7:33am

String str = "insert into TIMETABLE values ('" + SUBJECT_CODE.Text + "','" + OFFER_ID.Text + "','" + CLASS_ID.Text + "','" + DropDownList1.SelectedItem + "','" + TIME.Text + "','" + FACULTY_ID.Text + "','" + DropDownList2.SelectedItem + "','" + DURATION.Text + "') ";
cmd = new SqlCommand(str, con);
dr = cmd.ExecuteReader();
if (dr[2] == CLASS_ID.Text && dr[4] == TIME.Text && dr[3] == DropDownList1.SelectedIndex)
{
Response.Write(" Slot Already Given Please Choose another Class");
}

OriginalGriff 30-Jan-12 7:43am

Yes?
And?
Apart from "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." what do you expect us to say?
What does it do that it shouldn't, or not do that it should?

Sanjay K. Gupta 30-Jan-12 7:28am

First try yourself and give the problem details,if you faced.

manognya kota 30-Jan-12 7:30am

Where are you comparing? Are you getting the data to front end or comparing in the back end?

Jignesh J patel 30-Jan-12 7:36am

yes..

2 solutions

Solution 1

Hi,

From the code, you can pass the 2 variables as parameter to SP and get the count of rows in the DB.If the records exists return 1 else insert return 0 from the SP.

OR use a merge statement.

XML

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
THEN <merge_ matched> ];

Refer the below link for example

Merge Statement in SQL Server 2008[^]

Hope this helps.

Posted 30-Jan-12 1:29am

manognya kota

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)

CRDave1988 · Accepted Answer · 2012-01-30T01:45:00

this may not be 100% currect but it will give some idea

C#

cmd = new SqlCommand("select * from timetable where clo1='"+CLASS_ID.Text+"' and col2='"+TIME.Text+"'", con);
 dr = cmd.ExecuteReader();
if(!dr.HasRows)
{
	String str = "insert into TIMETABLE values ('" + SUBJECT_CODE.Text + "','" + OFFER_ID.Text + "','" + CLASS_ID.Text + "','" + DropDownList1.SelectedItem + "','" + TIME.Text + "','" + FACULTY_ID.Text + "','" + DropDownList2.SelectedItem + "','" + DURATION.Text + "') ";
            cmd2 = new SqlCommand(str, con);
            cmd2.ExecuteNonQuery();
}