?
Solved

How do I check to see if item exists in database

Posted on 2009-05-13
5
Medium Priority
?
196 Views
Last Modified: 2013-12-17
I have a query where I am trying to get the lastname where the lastname and first name equals something, but get errors. I tried executescaler, but I am using too many values in the where clause and get a instance before reference of an object and then I tried execute reader, but get no column in the result error. This is what I am trying to do:

I am trying to check to see if a user is in the username table where the firstname=variable and lastname=variable.If it is in their, move on, but if it's not in the username table, insert it in.
using (OdbcConnection con16 = new OdbcConnection(ConnStr))
                        using (OdbcCommand cmd16 = new OdbcCommand("select LastName from usernames where FirstName='" + firstName + "' and LastName='" + lastName + "'", con16))
                        {
                            con16.Open();
                            OdbcDataReader dr1;
                            //string result = cmd16.ExecuteReader();
                            dr1 = cmd16.ExecuteReader();
 
                            try
                            {
                                if (dr1["LastName"] == Null)
                                {
                                    OdbcConnection con17 = new OdbcConnection(ConnStr);
                                    OdbcCommand cmd17 = new OdbcCommand("insert into usernames (ProjectLeader,CommercialLeader,GroupLeader,Admin,FirstName,LastName) values(ProjectLeader='" + pnewleader + "', CommercialLeader='" + cnewleader + "', GroupLeader='" + gnewleader + "', Admin='" + newadmin + "', FirstName='" + firstName + "', LastName='" + lastName + "')", con17);
                                    con17.Open();
                                    cmd17.ExecuteNonQuery();
                                    con17.Close();
                                    dr1.Close();
                                }
                            }
                            catch (Exception ee)
                                {
                                    OdbcConnection con17 = new OdbcConnection(ConnStr);
                                    OdbcCommand cmd17 = new OdbcCommand("insert into usernames (ProjectLeader,CommercialLeader,GroupLeader,Admin,FirstName,LastName) values(ProjectLeader='" + pnewleader + "', CommercialLeader='" + cnewleader + "', GroupLeader='" + gnewleader + "', Admin='" + newadmin + "', FirstName='" + firstName + "', LastName='" + lastName + "')", con17);
con17.Open();
cmd17.ExecuteNonQuery();
con17.Close();
 }

Open in new window

0
Comment
Question by:bschave2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:Aanvik
ID: 24375901
I would suggest go for a Data Adapter and user Data table and check for the row count before you check if the value is null.
0
 

Author Comment

by:bschave2
ID: 24375916
how do I do that? Could you give me an example? thanks...newbie to c#.....easy in vb.net!
0
 
LVL 5

Accepted Solution

by:
Aanvik earned 2000 total points
ID: 24375963
Here's the sample code.
OleDbCommand selectCMD = new OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
selectCMD.CommandTimeout = 30;
 
OleDbDataAdapter custDA = new OleDbDataAdapter();
custDA.SelectCommand = selectCMD;
 
DataTable custDS = new DataTable();
custDA.Fill(custDS, "Customers");
 
now check if the DataTable.Rows.Count > 0 then go for a null check... and insert and etc.

Open in new window

0
 

Author Comment

by:bschave2
ID: 24376143
i did a dataset like this... Thanks for the help.


OdbcConnection con16 = new OdbcConnection(ConnStr);
                        OdbcCommand cmd16 = new OdbcCommand("SELECT LastName, FirstName FROM usernames where FirstName='" + firstName + "' and LastName='" + lastName + "'", con16);
                        OdbcDataAdapter da16 = new OdbcDataAdapter(cmd16);
                        DataSet ds16 = new DataSet();
                        da16.Fill(ds16, "projectmanager");
 
                        if (ds16.Tables["projectmanager"].Rows.Count > 0)
                        {
                        }
                        else
                        {
                            OdbcConnection con17 = new OdbcConnection(ConnStr);
                            OdbcCommand cmd17 = new OdbcCommand("insert into usernames (ProjectLeader,CommercialLeader,GroupLeader,Admin,FirstName,LastName) values(ProjectLeader='" + pnewleader + "', CommercialLeader='" + cnewleader + "', GroupLeader='" + gnewleader + "', Admin='" + newadmin + "', FirstName='" + firstName + "', LastName='" + lastName + "')", con17);
                            con17.Open();
                            cmd17.ExecuteNonQuery();
                            con17.Close();
                        }
 
                            con16.Close();
                        }

Open in new window

0
 
LVL 5

Expert Comment

by:Aanvik
ID: 24376172
Perfect.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question