update access database

[ayi_102]

I have a problem about updating access database.
there are 3 columns in my database : ID, searchkeyword, count

in the form there are button n textbox
if user enter a new search keyword, the database updated all

if the keyword already exist, just update count column +1

this is what i got so far

private void Insert()
    {
        if (TextBox1.Text == null || TextBox1.Text == "")
        {
            lblError.Text = "Item ID is missing.";
        }

        string _ddsearch = "";

        try
        { _ddsearch = Return_search; }
        catch { }

        if (_ddsearch == null || _ddsearch == "")
        {
            lblError.Text = "Item ID is missing.";
        }

        else
        {
            // See if the site can be found
            mydb thedb = new mydb();
            MyGlobal oapp = new MyGlobal();

            System.Data.OleDb.OleDbDataReader theReader = null;
            string _sql = "SELECT * FROM search WHERE ddsearch = " + _ddsearch;
            string _sqlupdate = "";
            theReader = thedb.SQLExecute_Reader(_sql);


            if (theReader != null)
            {





                if (theReader.HasRows == false )
                {
                    while (theReader.Read())
                    {
                        int _newnum = 0;

                        string _oldnum = theReader["searchcount"].ToString();
                        _newnum = Convert.ToInt32(_oldnum) + 1;

                        _sqlupdate = "Update tracking Set  searchcount = " + _newnum.ToString() + " where ddsearch = " + _ddsearch;

                    }
                }
                else
                {
                    // New Record
                    _sqlupdate = "Insert into search (id,searchcount) values (" + _ddsearch + ", 1)";

                }

                thedb.SQLExecute(_sqlupdate);
            }
        }



    }

    private string Return_search
    {
        get
        {
            return Request.QueryString["ddsearch"].ToString();
        }
    }

[Ramy Mahrous]

What the problem?

[LizR]

please use code tags so we can read your code.

[ayi_102]

coz i am a newbie, so i dont know how to use the code tag.

this is test the code tag

private void Search_Click()
    {

        string strSearch = "";

        //grab the value from html txtsearch (works fine)
        strSearch = Request.Form["txtSearch"].ToString();

        string _ddsearch = "";


        //make strSearch as _ddsearch and grab the value (works fine)
        try
        { _ddsearch = strSearch; }
        catch { }

        if (strSearch == null || strSearch == "")
        {
            lblError.Text = "Item ID is missing.";
        }

        else
        {
            // See if the site can be found
            mydb thedb = new mydb();
            MyGlobal oapp = new MyGlobal();

            //define the reader
            System.Data.OleDb.OleDbDataReader theReader = null;

            //defining sql
            string _sql = "SELECT * FROM search WHERE ddsearch = " + _ddsearch;
            string _sqlupdate = "";

            //execute the reader
            theReader = thedb.SQLExecute_Reader(_sql);

            if (theReader != null)
            {
                if (theReader.HasRows == true)
                {
                    while (theReader.Read())
                    {
                        int _newnum = 0;

                        // adding count increment 1
                        string _oldnum = theReader["searchcount"].ToString();
                        _newnum = Convert.ToInt32(_oldnum) + 1;

                        _sqlupdate = "Update search Set  searchcount = " + _newnum.ToString() + " where ddsearch = " + _ddsearch;

                    }
                }
                else
                {
                    // New Record

                    int _newID = 0;

                    // adding count increment 1
                    string _oldID = theReader["ID"].ToString();
                    _newID = Convert.ToInt32(_oldID) + 1;

                    _sqlupdate = "Insert into search (ID,ddsearch,searchcount) values ( " + _newID + " ," + _ddsearch + ", 1)";

                }

                thedb.SQLExecute(_sqlupdate);
            }
        }

    }

my problem is it cannot read the access database using sql reader

well, basically i am developing a website so i grab the value from html txtSearch with request .... and put it in variables
and the read the access database

if the value in the ddsearch is exist, update just by increment the count
if not exist insert a new row like

ID, ddsearch , searchcount

1 abc 15 if the keyword found, update the search count

if some one enter efg which is not in the ddsearch, insert whole row

ID, ddsearch , searchcount

1 abc 15
2 efg 1 ID become 2 , dd search is efg and search count start from 1

hope this explanation clear enogh

[LizR]

What error do you get?

[ayi_102]

no error actually

when u set break point, it is not getting the reader value

i mean it cannot read the database

theReader = thedb.SQLExecute_Reader(_sql);

if (theReader != null)

after the if, it goes to the end of loop

it is not execute the next line to read the database

i wonder whether my sql query wrong

[LizR]

I dont see any code for where you open your access database?

[Ramy Mahrous]

Debug!

[ayi_102]

mydb thedb = new mydb();
MyGlobal oapp = new MyGlobal();

ups, the code oleDb connection is in the mydb() method
i put it in separate file

well, i found my mistake

there are no error

the error is my access database coz it did not read the search table

when i read the other table, the code can read the rows

i have to recreate the access database again

thank you for the help