In C# how to I query from an access database using a Windows Application?

Question

ok I have a Windows app thats hooked up to an access database and I want the user to type in a football players name in a textbox, push a button and have it select the players info from the database (first name, last name, number, etc) right now i can see the whole table with all the info filled in on the datagrid when I run the app. I can get everything else to work except the select statement.

Bryan Natusch · Answer

I finally am able to query the database, however i still need help with my code-right now its just querying whatever player i have their number set to: in Number = 7; queries Ben Roethlisberber and so on whats the proper code to query whatever name is typed in?

Bryan Natusch · Answer

ok now i have no compilation errors. It compile and runs fine..but i get a load error when trying to query the database. Any additional feedback on what direction to take next is appreciated.

Bryan Natusch · Answer

ok now im down to six errors and its with these: playerFirstName.Text = dr.GetValue(0).ToString()); playerLastName.Text = dr.GetValue(1).ToString()); playerHomeTown.Text = dr.GetValue(2).ToString()); yes i made the text boxes and using these exact names for them. I appreciate the help Scott.

Bryan Natusch · Answer

yes i put them in with the same variable name-let me check those things you mentioned and ill let you know how it goes. You don't know how much I appreciate the help Scott.

Scott Lysle · Answer

Bryan: Also on these: // assumes dropping values into a textboxes on a form playerFirstName.Text = dr.GetValue(0).ToString()); playerLastName.Text = dr.GetValue(1).ToString()); playerHomeTown.Text = dr.GetValue(2).ToString()); I just made up the names of the textboxes (playerFirstName.Text, etc). Do you actually have these text boxes in the form?

Scott Lysle · Answer

Bryan: Check the sql string: string sSql = "SELECT * FROM player WHERE (number='" + playerNum,ToString() + "')" ; it should be a period after playerNum (e.g., playerNum.ToString()) You should also have this import: using System.Data.OleDb;

Bryan Natusch · Answer

i get 8 errors in my code-heres all my code-see if you can find anything I may be missing: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.Sql; namespace WindowsApplication1 { public partial class SB : Form { public SB() { InitializeComponent(); } private void Form1_Load( object sender, EventArgs e) { // TODO: This line of code loads data into the 'steelersDataSet.Player' table. You can move, or remove it, as needed. this .playerTableAdapter.Fill( this .steelersDataSet.Player); } private void button1_Click( object sender, EventArgs e) { int playerNum = 7; if (player.Text == "b" ) { pic.Visible = true ; bio.Visible = true ; pic.Image = Image .FromFile( "C:\Documents and Settings\Brandi\Desktop\Ben.jpg" ); this .Cursor = Cursors .WaitCursor; OleDbConnection conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\Steelers.mdb" ); try { conn.Open(); string sSql = "SELECT * FROM player WHERE (number='" + playerNum,ToString() + "')" ; OleDbCommand cmd = new OleDbCommand(sSql, conn); OleDbDataReader dr = cmd.ExecuteReader(); if (dr.HasRows == true ) { while (dr.Read()) { try { // assumes dropping values into a textboxes on a form playerFirstName.Text = dr.GetValue(0).ToString()); playerLastName.Text = dr.GetValue(1).ToString()); playerHomeTown.Text = dr.GetValue(2).ToString()); } catch { // go on to the next } } } } catch ( Exception ex) { MessageBox .Show(ex.Message.ToString() + " : " + ex.StackTrace.ToString(), "Load Error" ); } finally { conn.Close(); this .Cursor = Cursors .Default; } } } private void clear_Click( object sender, EventArgs e) { bio.Visible = false ; pic.Visible = false ; } } } heres the errors: Error 1 Expected ; or = (cannot specify constructor arguments in declaration) //after playerNum, ToString() Error 2 ; expected //where + is after ToString Error 3 ; expected//after ToString on playerFirstName line Error 4 Invalid expression term ')' //same line Error 5 ; expected//same errors for all three textboxes Error 6 Invalid expression term ')' Error 7 ; expected Error 8 Invalid expression term ')'

Bryan Natusch · Answer

That is a good idea, but i do want all the players info. The user types in: Hines Ward I want it to show his whole row with his first name, last name, experience, date of birth, height, weight number, college and position. I just want to get the basics first and then build on it.

Bryan Natusch · Answer

Scott, This is the direction I'm headed in-I will have to try it. Do you think I should do away with the datagrid on my form and just use textboxes for the players information to come up? I thought somehow I could just show the particular players row in the grid. I'll try it and get back to you.

Scott Lysle · Answer

Brian: You could give this a shot (correcting the connection string and doing what you want to do with the values returned) -- this is a pretty simple example: (add something like this in your event handler) -- int playerNum = 7; //set this to whatever number you are looking for this .Cursor = Cursors .WaitCursor; OleDbConnection conn = new OleDbConnection ( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\YourFolder\YourAccessDb.mdb" ); try { conn.Open(); string sSql = "SELECT * FROM players WHERE (number='" + playerNum,ToString() + "')" ; OleDbCommand cmd = new OleDbCommand (sSql, conn); OleDbDataReader dr = cmd.ExecuteReader(); if (dr.HasRows == true ) { while (dr.Read()) { try { // assumes dropping values into a textboxes on a form playerFirstName.Text = dr.GetValue(0).ToString()); playerLastName.Text = dr.GetValue(1).ToString()); playerHomeTown.Text = dr.GetValue(2).ToString()); } catch { // go on to the next } } } } catch ( Exception ex) { MessageBox .Show(ex.Message.ToString() + " : " + ex.StackTrace.ToString(), "Load Error" ); } finally { conn.Close(); this .Cursor = Cursors .Default; }

Bryan Natusch · Answer

in the event handler for the button, I tried putting in my select statement like this: private void button1_Click( object sender, EventArgs e) { if (player.Text == "b" ) { pic.Visible = true ; bio.Visible = true ; pic.Image = Image .FromFile( "C:\Documents and Settings\Brandi\Desktop\Ben.jpg" ); Select * from player where number = '7' ; } } and its looking for semicolons after player and number

Scott Lysle · Answer

What does your code look like (including the select statement you are using)?

In C# how to I query from an access database using a Windows Application?

Answers (12)