Newbie needs C# help

[CorNokZ]

Hey guys. I'm doing this project where I need to store data and search for it again. I can easily store all the data in my Access Database, and I can make it pump out all of the information again, but I can't search for a specific piece of data.

The assignment is to make a program for a garage so the can easily put cars into the system and find them again. I know that I somehow have to filter my search, but I really have no idea how to do it. I want to be able to search for the license plate number and pull out the infos on that car. I am thinking of doing so, "simply" by having a textbox, a search button and a textblock

Any help is very much appreciated

edit. It is the second button that is causing me trouble

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.Data.OleDb;
 
namespace WpfApplication1
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
 
            OleDbConnection cn = new OleDbConnection();
            cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Anders\Documents\Databaser\biler.mdb;User Id=;Password=;";
            cn.Open();
            Application.Current.Properties["DBConnection"] = cn;
        }
 
        private void button1_Click(object sender, RoutedEventArgs e)
        {
            OleDbConnection conn = (OleDbConnection)Application.Current.Properties["DBConnection"];
 
            string bil = textBox1.Text;
            string regnr = textBox3.Text;
            string reps = textBox4.Text;
            string model = textBox2.Text;
 
 
            string StrSQL = "INSERT INTO Biler (mærke, model, registreringsnummer, reparationer) VALUES ('" + bil + "', '" + model + "', '" + regnr + "', '" + reps + "')";
            OleDbCommand InsertCommand = new OleDbCommand(StrSQL, conn);
            InsertCommand.ExecuteNonQuery();
            MessageBox.Show("Data stored successfully");
 
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
        }
 
        private void button2_Click(object sender, RoutedEventArgs e)
        {
            textBlock1.Text = "";
            OleDbConnection conn = (OleDbConnection)Application.Current.Properties["DBConnection"];
 
            string StrSQL = "SELECT * FROM Biler WHERE Registreringsnummer";
            OleDbCommand SelectCommand = new OleDbCommand(StrSQL, conn);
 
            // Opret et DataReader objekt
            using (OleDbDataReader minDataReader = SelectCommand.ExecuteReader())
            {
                // Gennemløb resultaterne
                while (minDataReader.Read())
                {
                    textBlock1.Text +=
                    "Registreringsnummer: " + minDataReader["Registreringsnummer"].ToString() + "\n" +
                    "Bil: " + minDataReader["Mærke"].ToString() + "\n" +
                    "Model: " + minDataReader["Model"].ToString() + "\n" +
                    "Reparationer: " + minDataReader["Reparationer"].ToString() + "\n";
 
                }
            }
        }
    }
}

 

[Keitanz]

string StrSQL = "SELECT * FROM Biler WHERE Registreringsnummer";

should be

string StrSQL = "SELECT * FROM Biler WHERE Registreringsnummer = '" + textBox1.Text + "'";

Replace textBox1 with the box you use for the registration number and it should all work fine

 

[CorNokZ]

Awesome Keitanz!! Thanks a bunch

 

[Gahn]

Awesome Keitanz!! Thanks a bunch

Just a suggestion, avoid using default names for controls (well for anything really), choose a naming convention (ie btn = button, ddl = dropdownlist etc) and attach it something meaningful to help you read code better (btnInsert, btnSearch, etc).

Some links on naming conventions:
http://msdn.microsoft.com/en-us/library/xzf533w0(v=vs.71).aspx
http://10rem.net/articles/net-naming-conventions-and-programming-standards---best-practices

 

[caLLous]

string StrSQL = "SELECT * FROM Biler WHERE Registreringsnummer";

should be

string StrSQL = "SELECT * FROM Biler WHERE Registreringsnummer = '" + textBox1.Text + "'";

Replace textBox1 with the box you use for the registration number and it should all work fine

Who are you and what have you done with the real Keitanz??

 

[CorNokZ]

How would one go about editing in the database then? I tried with UPDATE Biler, but it just puts in a new entry in the db..

 

[JingleBells]

In generic SQL it's:

Update <table> set columnA = valueA, columnB = valueB where rowToUpdateKey = keyOfRowToUpdate

Also, I'd look into using parameterised SQL rather than string concatenation as you're asking for some miscreant to do SQL injection, the OleDbCommand appears to support it but I'm not familiar with ADO.Net stuff

 

[CorNokZ]

I've tried doing it like this:

string sSQLCommand = "UPDATE Biler WHERE registreringsnummer =  '" + textBox4.Text + "'";
            OleDbCommand UpdateCommand = new OleDbCommand(sSQLCommand, conn);
            UpdateCommand.ExecuteNonQuery();

But I get a syntax error on the ExecuteNonQuery(); and I have no idea why(or wtf I am doing)

Essentially I what to write in the textbox what I want edited and then plot it into the database

 

[Gahn]

I've tried doing it like this:

string sSQLCommand = "UPDATE Biler WHERE registreringsnummer =  '" + textBox4.Text + "'";
            OleDbCommand UpdateCommand = new OleDbCommand(sSQLCommand, conn);
            UpdateCommand.ExecuteNonQuery();

But I get a syntax error on the ExecuteNonQuery(); and I have no idea why(or wtf I am doing)

Essentially I what to write in the textbox what I want edited and then plot it into the database

When you use an Update in T-Sql you need to specify the values you want to update

So it would be Update Biler SET xyz = value, xyz2 = value2 from Biler where registeringsnummer = zyx

 

[Gahn]

In generic SQL it's:

Update <table> set columnA = valueA, columnB = valueB where rowToUpdateKey = keyOfRowToUpdate

Also, I'd look into using parameterised SQL rather than string concatenation as you're asking for some miscreant to do SQL injection, the OleDbCommand appears to support it but I'm not familiar with ADO.Net stuff

Yeh well that's true also cause it isn't real object oriented writing using string concatenations t-sql commands
Even better would be using SqlToLinq (if it even exists for Access) but i think that part is beyond the scope of the project atm.

 

[CorNokZ]

When you use an Update in T-Sql you need to specify the values you want to update

So it would be Update Biler SET xyz = value, xyz2 = value2 from Biler where registeringsnummer = zyx

I don't quite get this. I've tried with this, but I still get the syntax error.

string sSQLCommand = "UPDATE Biler SET Column1 = Mærke, Column2 = Model, Column3 = Registreringsnummer, Column 4= reparationer, FROM Biler WHERE Reparationer = " + textBox4.Text;
            OleDbCommand UpdateCommand = new OleDbCommand(sSQLCommand, conn);
            UpdateCommand.ExecuteNonQuery(); //This is where I get the Syntax error
            MessageBox.Show("Data stored successfully");

 

[- English -]

remember quotes around your new values .. i think

string sSQLCommand = "UPDATE Biler SET Column1 = 'Mærke', Column2 = 'Model', Column3 = 'Registreringsnummer', Column 4= 'reparationer', FROM Biler WHERE Reparationer = " + textBox4.Text;

 

[CorNokZ]

remember quotes around your new values .. i think

string sSQLCommand = "UPDATE Biler SET Column1 = 'Mærke', Column2 = 'Model', Column3 = 'Registreringsnummer', Column 4= 'reparationer', FROM Biler WHERE Reparationer = " + textBox4.Text;

Nope mate, getting same error Thanks tho

 

[JingleBells]

remember quotes around your new values .. i think

string sSQLCommand = "UPDATE Biler SET Column1 = 'Mærke', Column2 = 'Model', Column3 = 'Registreringsnummer', Column 4= 'reparationer', FROM Biler WHERE Reparationer = " + textBox4.Text;

You've quoted the table twice, try:

string sSQLCommand = "UPDATE Biler SET Column1 = 'Mærke', Column2 = 'Model', Column3 = 'Registreringsnummer', Column 4= 'reparationer' WHERE Reparationer = " + textBox4.Text;

 

[CorNokZ]

You've quoted the table twice, try:

string sSQLCommand = "UPDATE Biler SET Column1 = 'Mærke', Column2 = 'Model', Column3 = 'Registreringsnummer', Column 4= 'reparationer' WHERE Reparationer = " + textBox4.Text;

Still getting a Syntax error saying that "OleDbException was unhandled" :-/ I have no idea what is going on atm

 

[SilverHood]

Still getting a Syntax error saying that "OleDbException was unhandled" :-/ I have no idea what is going on atm

If you can't get it working, simplify it to the point where you can work out exactly what the issue is. In your code, you are updating 4 columns based on a user input variable. Change that to 1 column based on a static variable you hard code. Get that working. Change the static variable to the textbox4 item. Add in more columns. Once you're comfortable, you can do it all at once, but learning programming is about baby steps.

As for the update statement.... you are missing quotes around the textboxt4 item?

string sSQLCommand = "UPDATE Biler SET Column1 = 'Mærke', Column2 = 'Model', Column3 = 'Registreringsnummer', Column 4= 'reparationer' WHERE Reparationer = " + "'" + textBox4.Text+"'";

Also, the exception is unhandled because you have not specified what needs to happen in case of an exception. Use a try / generic catch to get more details. Note that it's been 4 years since I last wrote c#, so may need some tweaking.

Wrap your execute statement in this:

Try {
  InsertCommand.ExecuteNonQuery();
}
{
catch (Exception e)
System.Diagnostics.Debug.WriteLine(e)
}
 
You can then print the exception to get a better idea of what is going on

 

[Gahn]

Almost there, i bet the problem is in line with what Silverhood wrote, the problem is that you are passing a Value to the Where Clause which is intended as an Integer at the moment, i bet the Reparationer is a String instead, hence you need to pass it as a String.

The Try Catch above needs tweaking, this is the right code:

try
{
//whatever you need to try to do in Access
}
catch (OleDbException oex)
{
throw new Exception (oex.ToString()); //Or print it if you prefer as Silverhood showed.
}

 

[CorNokZ]

Finally got it working, so thanks a lot you guys

Here is how the final button came to look like:

OleDbCommand Update = new OleDbCommand("UPDATE Biler SET reparationer = '" + textBox4.Text +
            "' WHERE Registreringsnummer = " + "'" + textBlock3.Text + "'", OleDbConnection);
 
            OleDbConnection.Open();
            Update.ExecuteNonQuery();
            OleDbConnection.Close();
 
            MessageBox.Show("Data saved");

 

[Gahn]

Finally got it working, so thanks a lot you guys

Here is how the final button came to look like:

OleDbCommand Update = new OleDbCommand("UPDATE Biler SET reparationer = '" + textBox4.Text +
            "' WHERE Registreringsnummer = " + "'" + textBlock3.Text + "'", OleDbConnection);
 
            OleDbConnection.Open();
            Update.ExecuteNonQuery();
            OleDbConnection.Close();
 
            MessageBox.Show("Data saved");

Prolly not needed but the try catch is always nice to have (for any reason the command should fail, ie Db/table locked or anything could go wrong really). Anyway happy you did it!

 

[CorNokZ]

We haven't worked with try/catch so not going to venture into that

Exam is at half passed three, so cross your fingers for me

 

[CorNokZ]

Passed that shit like a fucking baws!! Thanks again for the help you guys

 

[- English -]

Passed that shit like a fucking baws!! Thanks again for the help you guys

passed 2 of 3 of my exams so far, got a 10 in database & xml, 12 in CMS & developing environments, and my last is next tuesday

 

[Wij]

Access? Ugh.

Do it in Rails. At least SQLite is nearly a proper database

And yes, I'm gonna inject SQL in you if you do it that way

 

[CorNokZ]

We had to do it in Access, so there was no way around it Wij

 

[Wij]

Not saying no should mean course fail imo.