update access database
 

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();
}
}

Re: update access database
 

What the problem?

Re: update access database
 

please use code tags so we can read your code.

Re: update access database
 

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

Re: update access database
 

What error do you get?

Re: update access database
 

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

Re: update access database
 

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

Re: update access database
 

Debug!

Re: update access database
 

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