Solved

How do I check to see if item exists in database

Posted on 2009-05-13
5
183 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
  • 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 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

829 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