Jump to content
  • Advertisement
Sign in to follow this  
NDraskovic

Communication with a database

This topic is 2583 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey

I'm having a problem with communicateing with a database. For now I'm working with simple WinForm written in C#, just for testing purposes, but in future I'll work with XNA 4.0. This is the problem - I managed to connect a databse to my project (or at least I think I did - there is no connection string in my code, I just used the wizard to do the connection), and I managed to use my form to insert data into that database. But when I try to extract data from the database, it either gives me the text of the query (when I use LINQ) or some weird exeptions and errors (when I use direct SQL queries using ExecuteComand method). For this test I would like to be able to do the following - write the data into TextBoxes (each Textbox is labeled with the same name as the column it's Text is intented for), click on a button to insert that data into the database, and then by clicking on a second button to display that data in another Textbox, but this time compiled into a "sentence"

TNX

Share this post


Link to post
Share on other sites
Advertisement
Show us what you've tried. Create a minimal example (a console program would be easier than full WinForms) that demonstrates the problem. Include your expected behaviour, the actual behaviour and the error messages. You might need to include the database schema too, depending on what you are doing.

Share this post


Link to post
Share on other sites

Show us what you've tried. Create a minimal example (a console program would be easier than full WinForms) that demonstrates the problem. Include your expected behaviour, the actual behaviour and the error messages. You might need to include the database schema too, depending on what you are doing.


For start can you give me a link to I tutorail that starts form the very beginning. The schema of the database is simple, there are only 3 columns: (ID, Prva, Druga) in that particular order
At this point I have this code:

ProbniDataContext PDC = new ProbniDataContext();


In a button1_Click method :

PDC.ExecuteCommand("INSERT INTO ProbnaTablica VALUES (" + Convert.ToInt32(textBox1.Text) + ",'" + this.textBox3.Text + "','" + this.textBox4.Text + "')"); \\this part workes


And then in a button2_Click method:

textBox2.Text += Convert.ToString(PDC.ExecuteCommand("SELECT ID FROM ProbnaTablica WHERE (ID ="+ Convert.ToInt32(textBox1.Text) + ")" ));


When I run the program, all TextBoxes are empty, so for test I entered 113, Text1, Text2 into their respective textBoxes, clicked on button1, checked the database and saw that input. Then I entered just 113 into textBox1 and clicked button2, but this time it wrote only -1 into textBox2. I tried changeing that value to other ID's I know are in database but it keeps adding -1 to the textbox2

I know this might be a little ugly to post, but I can't post from my programming workstation, I have to use another computer so sorry for that

Share this post


Link to post
Share on other sites
I'm not finding any results on Google for ProbniDataContext. Is this your own type? What does ExecuteCommand() return?

As a side note you should be escaping input before adding it to a SQL query. Failure to do so will result in query syntax errors, unexpected behaviour or in some cases security issues.

Imagine if textBox1 containst the value "1", textBox3 contains the string "Mornin'" and textBox4 contains "Everybody".
What will we execute?

INSERT INTO ProbnaTablica VALUES (1,'Mornin'','Everybody')

You can see we have one quote too many.

Imagine if textBox1 containst the value "1", textBox3 contains the string "Hello" and textBox4 contains "World'); delete from ProbnaTablica; --"

What will we execute?

INSERT INTO ProbnaTablica VALUES (1,'Hello','World'); delete from ProbnaTablica; --')

Note the SQL comment at the end to get the rest of the statment fragment ignored.

This might not work, depending on the underlying implementation, but it might still cause an ugly exception that could easily be avoided.

Share this post


Link to post
Share on other sites

I'm not finding any results on Google for ProbniDataContext. Is this your own type? What does ExecuteCommand() return?

As a side note you should be escaping input before adding it to a SQL query. Failure to do so will result in query syntax errors, unexpected behaviour or in some cases security issues.

Imagine if textBox1 containst the value "1", textBox3 contains the string "Mornin'" and textBox4 contains "Everybody".
What will we execute?

INSERT INTO ProbnaTablica VALUES (1,'Mornin'','Everybody')

You can see we have one quote too many.

Imagine if textBox1 containst the value "1", textBox3 contains the string "Hello" and textBox4 contains "World'); delete from ProbnaTablica; --"

What will we execute?

INSERT INTO ProbnaTablica VALUES (1,'Hello','World'); delete from ProbnaTablica; --')

Note the SQL comment at the end to get the rest of the statment fragment ignored.

This might not work, depending on the underlying implementation, but it might still cause an ugly exception that could easily be avoided.


Yes, both caused the exeption "Column name or number of supplied values doesn not match table definition", I think that is because ID 1 already exists in database (I'm saying database because it only contains one table - like I said this is only a test, the real implementation will be different, I'v forgotten to make it Identity when creating a table). As for ProbniDataContext, that is a type that was automaticly created when I added a .dblm file to my project, like NorthwindDataContext is for that database (Probni means Test in Croatian).

Share this post


Link to post
Share on other sites

Yes, both caused the exeption "Column name or number of supplied values doesn not match table definition",
[/quote]
Both examples I gave you, or both of your original queries?


As for ProbniDataContext, that is a type that was automaticly created when I added a .dblm file to my project...
[/quote]
Ok. I haven't used C# to talk to a database, so I assumed this was a library.

ExecuteCommand appears to return an integer representing the number of rows modified. If you want data, you should be using the ExecuteQuery function instead.

It also looks like the answer to my aside is to use the argument list to Execute Query/Command:

int id = Convert.ToInt32(textBox1.Text);
PDC.ExecuteCommand("INSERT INTO ProbnaTablica VALUES ({0}, {1}, {2})", id, this.textBox3.Text, this.textBox4.Text);

// And

int inputId = Convert.ToInt32(textBox1.Text);
int outputId = PDC.ExecuteQuery(typeof(int), "SELECT ID FROM ProbnaTablica WHERE (ID = {0})", inputId)
textBox2.Text += Convert.ToString(outputId);

Expect some syntax errors in this, my C# is rusty.

Share this post


Link to post
Share on other sites


Yes, both caused the exeption "Column name or number of supplied values doesn not match table definition",

Both examples I gave you, or both of your original queries?


As for ProbniDataContext, that is a type that was automaticly created when I added a .dblm file to my project...
[/quote]
Ok. I haven't used C# to talk to a database, so I assumed this was a library.

ExecuteCommand appears to return an integer representing the number of rows modified. If you want data, you should be using the ExecuteQuery function instead.

It also looks like the answer to my aside is to use the argument list to Execute Query/Command:

int id = Convert.ToInt32(textBox1.Text);
PDC.ExecuteCommand("INSERT INTO ProbnaTablica VALUES ({0}, {1}, {2})", id, this.textBox3.Text, this.textBox4.Text);

// And

int inputId = Convert.ToInt32(textBox1.Text);
int outputId = PDC.ExecuteQuery(typeof(int), "SELECT ID FROM ProbnaTablica WHERE (ID = {0})", inputId)
textBox2.Text += Convert.ToString(outputId);

Expect some syntax errors in this, my C# is rusty.
[/quote]

I managed to get the data inserting part of my program to work properly - I made a new table in my database where ID's IsIdentity property is set to Yes. For this part I only have to add exception handleing (the only restriction is that no data is null). Now I have to get the extraction part of this program to work - in the final app it should maintain a constant number of sentences in either a multiline textBox (this I am familiar with) or DataGridView (never worked with). The idea is to give the user insight into order and timeline of upcoming events - when event is triggered, it should remove it from the display area, but not from the database. I think this is very similar to the Tetris area that shows you which block is coming next - just this is done by extracting data from database and it's displaying text instead of pictures. If you have any suggestins or ideas on how to do this it would be great

Share this post


Link to post
Share on other sites
You must design your schema to support this. So perhaps you should include an event date column. Then when you want to list upcoming events, you can select the events where this date column's value > NOW(), order by the date (soonest first) and limit to 5 events.

From my rough guess that the language is Serbian, it appears your table and column names are the equivalent of "test", so perhaps you haven't really looked into the schema part yet.

Share this post


Link to post
Share on other sites

You must design your schema to support this. So perhaps you should include an event date column. Then when you want to list upcoming events, you can select the events where this date column's value > NOW(), order by the date (soonest first) and limit to 5 events.

From my rough guess that the language is Serbian, it appears your table and column names are the equivalent of "test", so perhaps you haven't really looked into the schema part yet.


Actually its Croatian, but these languages are very similar. I made a new table that has the schema (ID, Datum, Naziv_linije, Naziv_prijevoznika, Registarska_tablica, Komentar) - ID is an indentity value, Datum is a Date (although I made it a nchar[20] and I fill it automaticly by using C#-s DateTime.Now() method), other columns are nchar columns, and they just hold textual info, nothing too important.

Now I'm trying to get the data from that table displayed in a multiline textBox by using the following code:

var tmp = PDC.ExecuteQuery<String>(@"SELECT ID, Naziv_linije FROM Tablica");
textBox2.Text += Convert.ToString(tmp);


The compiler doesent report any errors or warnings, but the text that is displayed is probobly the text of the query not the result it should produce. When I tried to use foreach loop, I got the exception "Unable to cast object of type System.Int32 to type System.String"

Share this post


Link to post
Share on other sites

The compiler doesent report any errors or warnings, but the text that is displayed is probobly the text of the query not the result it should produce.
[/quote]
Probably? Can't you post the message? I'm effectively stabbing in the dark, as I don't have a C# compiler handy.

One thing I do see is that you are selecting two values in the query, but asking it to return String instances. I'm not sure what you expect the result to be.


Datum is a Date (although I made it a nchar[20] and I fill it automaticly by using C#-s DateTime.Now() method)...
[/quote]
Storing the Datum as a date type will allow you to do data comparisons, as opposed to string comparisons. It will also allow you to use the NOW() SQL function, which is a little easier than creating a String from

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!