How to get the max value from a column in SQLite to WPF C#

Question

0.00/5 (No votes)

See more:

Greetings!

I was having trouble in getting a specific value from a column in my SQLite Database to my WPF C# application.

C#

using System;
using System.Windows;
using System.Windows.Input;

using System.Data.SQLite;
namespace IEEE_UESB_Database
{
    /// <summary>
    /// Interaction logic for Window2.xaml
    /// </summary>
    public partial class Window2 : Window
    {
        string dbConnectionString = @"Data Source=ieeeuesbmembers.sqlite;Version=3";
        int memID;
        public Window2()
        {
            InitializeComponent();
        }

        private void cmdAddNew_Click(object sender, RoutedEventArgs e)
        {
            memID++;
            tbxMemID.Text = Convert.ToString(memID);
            tbxLastName.Text = "";
            tbxFirstName.Text = "";
            tbxMiddleName.Text = "";
            tbxNickName.Text = "";
            tbxStudentNumber.Text = "";
            cbxYearLevel.Text = "";
            tbxContactNumber.Text = "";
            tbxEmailAddress.Text = "";
            cbxCourse.Text = "";
            tbxAddress.Text = "";
            cbxCommittee.Text = "";
            cbxPosition.Text = "";
        }

        private void cmdDelete_Click(object sender, RoutedEventArgs e)
        {

        }

        private void cmdSave_Click(object sender, RoutedEventArgs e)
        {
            SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);
            //Open connection to database
            try
            {
                sqliteCon.Open();
                string Query = "insert into ieeeuesbmembers (memberID,lastName,firstName,middleName,nickName,studentNumber,yearLevel,contactNumber,emailAddress,course,address,committee,position) values ('" + this.tbxMemID.Text + "','" + this.tbxLastName.Text + "','" + this.tbxFirstName.Text + "','" + this.tbxMiddleName.Text + "','" + this.tbxNickName.Text + "','" + this.tbxStudentNumber.Text + "','" + this.cbxYearLevel.Text + "','" + this.tbxContactNumber.Text + "','" + this.tbxEmailAddress.Text + "','" + this.cbxCourse.Text + "','" + this.tbxAddress.Text + "','" + this.cbxCommittee.Text + "','" + this.cbxPosition.Text + "')";
                SQLiteCommand createCommand = new SQLiteCommand(Query, sqliteCon);
                createCommand.ExecuteNonQuery();
                MessageBox.Show("Record successfully saved!");
                sqliteCon.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void cmdLogout_Click(object sender, RoutedEventArgs e)
        {
            MessageBox.Show("Thank you for using this software!");
            this.Close();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);
            //Open connection to database
            try
            {
                sqliteCon.Open();
                SQLiteCommand createCommand = new SQLiteCommand(sqliteCon);
                SQLiteDataReader dataReader = createCommand.ExecuteReader();
                memID = Convert.ToInt32(dataReader["select max(memID) from ieeeuesbmembers"]);
                tbxMemID.Text = Convert.ToString(memID);
                sqliteCon.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }
    }
}

My problem is on the Windows_Loaded event, everytime the application is opened, I wanted the tbxMemID to always have the max value that was recorded as a primary key with autoincrement function on the database, so that I won't overlap any records everytime I tried to add one.

It keeps giving this error message.

C#

Value cannot be null.
Parameter: s

Can someone help me out?
Thanks!

Posted 21-Jul-13 20:59pm

rogelsab

Add a Solution

3 solutions

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)

Naz_Firdouse · Answer 1 · 2013-07-21T21:09:00

Solution 1

Modify your SQLiteCommand like this

C#

SQLiteCommand createCommand = new SQLiteCommand("select max(memID) from ieeeuesbmembers",sqliteCon);

and then use ExecuteReader. then you will have the data in your dataReader object.

Posted 21-Jul-13 21:09pm

Naz_Firdouse

Comments

rogelsab 22-Jul-13 4:40am

Hey Naz_Firdouse,

Do you mean like this?

private void Window_Loaded(object sender, RoutedEventArgs e)
{
SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);
try
{
sqliteCon.Open();
SQLiteCommand createCommand = new SQLiteCommand("select max(memberID) from ieeeuesbmembers", sqliteCon);
SQLiteDataReader dataReader = createCommand.ExecuteReader();
memID = Convert.ToInt32(dataReader["memberID"]);
tbxMemID.Text = Convert.ToString(memID);
sqliteCon.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

It shows another error:

Index was outside the bounds of the array.

Thanks!

midnight_ · Answer 2 · 2013-07-21T21:12:00

Solution 2

In SQLite you have a nice proc for that....

Just replace your query with

SQL

SELECT seq FROM sqlite_sequence WHERE name='ieeeuesbmembers'

As long as you have a active connection, you can use

SQL

SELECT last_insert_rowid() FROM ieeeuesbmembers

Posted 21-Jul-13 21:12pm

midnight_

Updated 21-Jul-13 23:24pm

v3

Comments

rogelsab 22-Jul-13 4:44am

Hey midnight_,

Where should I place that query? In my SQLite GUI or in WPF C#?

I think that's only for the SQLite GUI.
Also, when I tried it, it returns null value since I haven't entered any data yet.
That query would only return something during the time that I added a new data, when the application closes, it returns to null.

midnight_ 22-Jul-13 5:21am

sorry, my fault, will improve my answer... -> Place it in your query in wpf app

cocis48 · Answer 3 · 2013-07-22T12:44:00

Solution 3

keep your code the way it is, just declare your MemID like this:
var memID = dataReader["select max(memID) from ieeeuesbmembers"];
if (memID == null)
{
memID=0
}

Posted 22-Jul-13 12:44pm

cocis48