[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I check to see if item exists in database

Posted on 2009-05-13
5
Medium Priority
?
201 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

649 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