help me please i really cant solve it

nidhelp

hi i did this: in my Access db table, i've rows of data. each row have columns (i've a column representing ID number and other columns for other data). in VS, i've a textbox for the user to enter an ID number and then press OK button. how to do this: the pressing of OK button should retrieve the entire row of data belonging to THAT ID number keyed in and then displaying the data in a form in VS. do i have to do anything regarding filtering, parameters or setting criteria? can i have some sample codes/examples? thanks very much!

Colin Angus Mackay

You send a query to your Access Database to request the row. Something like this:

OleDbCommand cmd = new OleDbCommand(myConnection,
"SELECT id, dataColumn1, dataColumn2, etc FROM myTable WHERE id = ?")
cmd.Parameters.Add(id);
OleDbDataReader reader = cmd.ExecuteReader();
int id = reader.GetInt32(0);
string myValue = reaser.GetString(1); // etc.

My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

nidhelp

sorry for my ignorance but how do i actually go about to send a query?

Colin Angus Mackay

That is what the code I gave you does. It sends the query to the database and retrieves the result. Here is again, but annotated more fully.

// Create and Open the connection to the database
OleDbConnection myConnection = new OleDbConnection(myConnectionString);
myConnection.Open();

// Define the command to send to the database.
OleDbCommand cmd = new OleDbCommand(myConnection,
"SELECT id, dataColumn1, dataColumn2, etc FROM myTable WHERE id = ?");
cmd.Parameters.Add(id);

// Execute the commend, requesting a reader object to get the results.
OleDbDataReader reader = cmd.ExecuteReader();

// Get the results from the reader
int id = reader.GetInt32(0);
string myValue = reaser.GetString(1); // etc.

// Close the connection.
myConnection.Close();

Also, the MSDN documentation will explain each of these classes and methods with further examples. * DISCLAIMER: I typed this by hand without running it through a compiler. There may be some small errors.

My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

nidhelp

oh thanks so much for a more detailed description! it's more clear to me what those codes mean now. =) but what i meant was what actually are the steps to send a query to my database? what am i supposed to click on to enter those codes that you have shown me? >sorry im new to database stuff and i really need some help in making things work.. and seriously, i learn better this way, through step by step guidance and hands-on experience.. nevertheless, i'll be reading up on the relevant links that may be helpful.. thanks again! =)

Colin Angus Mackay

nidhelp wrote: but what i meant was what actually are the steps to send a query to my database? The steps are those I've shown you above. nidhelp wrote: what am i supposed to click on to enter those codes that you have shown me? Umm... The text editor. Then you start typing. (I know that sounds a stupid answer, but there is nothing to click on. No magic buttons to press. You really have to do the work).

My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

nidhelp

SORRY im still stuck...i cant figure out where to type the codes. which section of my program should i type? THANKS!

Colin Angus Mackay

There are many ways to do this. One of the correct ways is to create a DAL (Data Abstraction Layer) in your application. This is really just a class, or set of classes (if your application is big) that communicates with the database for you. It then returns the data to what ever part of the application needed it. e.g.

class Dal
{
private string connectionString;
public Dal()
{
connectionString = ConfigurationSettings.AppSettings["connectionString"];
}

private SqlConnection Connection
{
    get
    {
        return new SqlConnection(connectionString);
    }
}

// Example of returning a single row.
public Hashtable GetMyData()
{
    // Some code that accesses the database and gets the data through a datareader
    // see previous forum post for an example.
    Hashtable result = new Hashtable();
    for(int i=0; i<myDataReader.FieldCount;i++)
    {
        string columnName = myDataReader.GetName(i);
        object columnValue = myDataReader.GetObject(i);
        result\[columnName\] = columnValue;
    }
    return result
}

}

Then somewhere in the code that needs the data from the database you can write something like this:

Dal dal = new Dal();
Hashtable data = dal.GetMyData();
// Do something with data (e.g. populate controls)

Does this help?

My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

tatchung

Hey! I've done something similar to this and i know just how you feel. My suggestion is that you separate your queries (data gathering from database) from you UI form. Create another form which would contain your connection string as well as all your queries then pass it to your main form. Try this: public class DS_Company { OdbcConnection conn = new OdbcConnection("ConnectionString"); string strSQL; public DS_Company() { conn.Open(); //opens the database connection } public DataSet GetID(string strID) { strSQL = " SELECT "; //standard SQL command strSQL += " * "; //selects all the data in the table strSQL += " FROM Table"; //what table you are aiming at strSQL += " WHERE IDnumber='"+ strID +"'"; //gets data according to what ID you specify DataSet ds = new DataSet(); //creates a storage for your data OdbcDataAdapter adpDS = new OdbcDataAdapter(strSQL,conn); adpDS.Fill(ds); //fills your dataset with the data return ds; //passes the dataset to you main form } } `hope that helps Kampai!!!`

tatchung

Well to tell you the truth im a beginner too but i'l try my best to be of assistance. Go ahead and post your code or better yet email me your form. I'l see what i can cook-up Kampai!!!

nidhelp

OH YES!!!!!!! I managed to solve it already!

nidhelp

oh no! my problem's not solved yet... im doing parameterised query. i managed to load my data and correctly inside the textbox controls which are placed on the same page as where i've placed my textbox and Go button. if this is not clear, i mean i managed to do this> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkwalkthroughdisplayingdatainwindowsformusingparameterizedquery.asp however, what i want to do is, the user types a criterior in the textbox and press the Go button which are in form1. the Go button sends query to Access db and loads the data inside the textbox controls in form2. thanks

Muammar

try this link