[RESOLVED] Creating and Calling Stored Procedure in vb.net / MySQL

Here's my stored procedure

Code:

---

CREATE PROCEDURE Login
/*
(
parameter1 INT
OUT parameter2 datatype
)
*/
BEGIN

SELECT        COUNT(*) AS Expr1, Username, [Password], CanEdit
FROM            Accounts
GROUP BY Username, [Password], CanEdit
HAVING        (Username = ?) AND ([Password] = ?)

END

---

I want to call it when I click login button in my system?
I tried to find a tutorials but I cant understand because of lack of comments in the codes.

Help please. :D

Executing a stored procedure using ADO.NET is pretty much exactly the same as using inline SQL. The only differences are that where you would normally put the SQL code, you put the name of the stored procedure. Also, you set the CommandType of the command object to StoredProcedure instead of te default Text.

Follow the Database FAQ link in my signature and check out some of the .NET resources there. You'll find, amongst others, a tutorial and my own code examples. They will generally not be for MySQL but that doesn't matter. ADO.NET code is basically the same regardless of the data source. You just change the data access classes if required, e.g. MySqlConnection for MySQL instead of SqlConnection for SQL Server.

Much appreciated jmcilhinney! Thanks for that. After reading that I will post the progress and concerns, is it okay?

Absolutely

Quote:

---

If you want to change to a different database system, the chances are that you will need to re-write (or drastically modify) a reasonable large/complex section of code for each SQL statement.

---

From http://www.vbforums.com/showthread.php?t=548787

Thanks very much! I think I will spend several weeks in reading most of your signature. Very Helpful. I'll just post if I have question

Hello, I have a question regarding vb.net and MySQL. I already connect my system to MySQL and I already know on how to populate the listbox with information from my database, but I want to make a code that everytime a user clicks on the items of listbox (containing the names) the textboxes (composed of username, password, ID, names etc) will show the other info?


On the left side you can see the listbox with highlighted name, on the right side you can see the textboxes but without further info. In vb.net and ms access I already do that, piece of cake. But its hard in vb.net and MySQL

http://i1128.photobucket.com/albums/...1/system-1.png

Quote:

---

Originally Posted by cary1234

But its hard in vb.net and MySQL

http://www.vbforums.com/images/ieimages/2011/09/1.png

---

should it be the same (or at least similar)? I'm not sure now, but before, use the MySQL .net connector (or a similar name, can not remember exactly, but you can download from mysql site).

bear

First up, your new question doesn't relate to the topic of this thread so it doesn't belong in this thread. Please keep each thread to a single topic and each topic to a single thread. If you have a question on a new topic then start a new thread.

As for the question, you have choices. You can get all the data up front and bind it all to your controls. As the user selects an item in the ListBox the related data will automatically be displayed in the other controls. That would be a "chunky" application.

Alternately, you could get the minimum data up front, i.e. just what you display in the ListBox and the IDs. When the user selects an item you use the ID to retrieve the related data from the database and display it. That would be a "chatty" application. The better option depends on how much data is in a record, how many records there are, what proportion of records the user is likely to view and other factors.

You might also like to combine the two. You could create a DataTable containing all the columns but then populate only what will be displayed in the ListBox and the ID. Add an extra column containg a Boolean flag that indicates whether all data for that row has been retrieved. Bind the table to all your controls. When the user makes a selection, check the flag to see whether the data for that record has been retrieved. If it has then you're good to go. If it hasn't, retrieve it into the table and set the flag.

sorry jmcilhinney, i suppose that it is related. :D

Quote:

---

You can get all the data up front and bind it all to your controls. As the user selects an item in the ListBox the related data will automatically be displayed in the other controls. That would be a "chunky" application.

---

http://i1128.photobucket.com/albums/...1/system-2.png

Please correct me, do you mean that I will Add New Datasource? I'm still confused on vb.net and mysql. It's because I didnt add a data source yet I can still connect to my database using codes. If I add a datasource (which is my current database) is it possible to bind it and make things easier(because I can bind it like a piece of cake)?

Thanks FlyingBear,

Quote:

---

should it be the same (or at least similar)? I'm not sure now, but before, use the MySQL .net connector (or a similar name, can not remember exactly, but you can download from mysql site).

---

Yes I know that its MySQL net connector. :D It makes connection easy right?

Quote:

---

Originally Posted by cary1234

sorry jmcilhinney, i suppose that it is related. :D

---

No it isn't. This new question is nothing to do with stored procedures. It's about when to retrieve data and how to display that data.

You can create a Data Source or do it all in code. It really doesn't matter. You can bind or not bind in either case. You can retrieve the data in a batch or one record at a time either way. The Data Source just means that you don't have to write all the code yourself.

ok sorry for that.

Quote:

---

You can create a Data Source or do it all in code. It really doesn't matter. You can bind or not bind in either case. You can retrieve the data in a batch or one record at a time either way. The Data Source just means that you don't have to write all the code yourself.

---

Ahh. thanks for that. Now I know what to search for. I'll mark this thread as resolve.