Management Karbust 4918 Posted February 2, 2017 Management Share Posted February 2, 2017 (edited) Hello I'm doing a work for my programming class, but I'm having some troubles with mysql... Well, this is the whole function: public List<string>[] SelectUsers() { //string tabela = data; string query = "SELECT * FROM users"; //Create a list to store the result List<string>[] list = new List<string>[6]; list[0] = new List<string>(); list[1] = new List<string>(); list[2] = new List<string>(); list[3] = new List<string>(); list[4] = new List<string>(); list[5] = new List<string>(); //Open connection if (this.OpenConnection() == true) { //Create Command MySqlCommand cmd = new MySqlCommand(query, connection); //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { list[0].Add(dataReader["id_user"] + ""); list[1].Add(dataReader["login"] + ""); list[2].Add(dataReader["password"] + ""); list[3].Add(dataReader["is_admin"] + ""); list[4].Add(dataReader["game"] + ""); list[5].Add(dataReader["rank_ingame"] + ""); } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return list; } else { return list; } } The users' table structure: The games table structure: The field "jogo" on user table is a foreign key of the table games... My ideia is to get the game name instead the ID of the game... The query I use on navicat to get the game name: SELECT * FROM jogos WHERE id_jogo = (SELECT jogo FROM users WHERE id_user=1); But, I would like to use it like this on the code: "SELECT * FROM jogos WHERE id_jogo = (SELECT jogo FROM users WHERE id_user=" + dataReader["id_user"] + ")" I already tried this (adding a second datareader): string query1 = "SELECT * FROM jogos WHERE id_jogo = (SELECT jogo FROM users WHERE id_user=1)"; MySqlCommand cmd1 = new MySqlCommand(query1, connection); MySqlDataReader dataReader1 = cmd1.ExecuteReader(); while (dataReader1.Read()) { list[4].Add(dataReader1["nome"] + ""); } dataReader1.Close(); But I keep getting this error: System.ArgumentOutOfRangeException was unhandled HResult=-2146233086 Message=Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index ParamName=index Source=mscorlib StackTrace: at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource) at Projeto_24___Jogos_com_DB.Utilizadores..ctor() in C:\Users\Ragnorak\Documents\Visual Studio 2013\Projects\Projeto 24 - Jogos com DB\Projeto 24 - Jogos com DB\Utilizadores.cs:line 16 at Projeto_24___Jogos_com_DB.Form2.button1_Click(Object sender, EventArgs e) in C:\Users\Ragnorak\Documents\Visual Studio 2013\Projects\Projeto 24 - Jogos com DB\Projeto 24 - Jogos com DB\Form2.cs:line 26 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at Projeto_24___Jogos_com_DB.Program.Main() in C:\Users\Ragnorak\Documents\Visual Studio 2013\Projects\Projeto 24 - Jogos com DB\Projeto 24 - Jogos com DB\Program.cs:line 19 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException: Anyone can help me please? This is my first work on c# using database connections, and I'm a little confuse... Thanks Kind Regards, charparodar Edited August 20, 2022 by Metin2 Dev Core X - External 2 Internal Link to comment Share on other sites More sharing options...
Honorable Member NoFr1ends 755 Posted February 3, 2017 Honorable Member Share Posted February 3, 2017 You could use a join, so you only need 1 statement to execute: SELECT u.*, j.name as game_name FROM users as u INNER JOIN jogos as j on j.id_jogo = u.jogo Link to comment Share on other sites More sharing options...
Management Karbust 4918 Posted February 3, 2017 Author Management Share Posted February 3, 2017 (edited) Thanks, it's working! I've never used JOIN on classes, I didn't knew that could be used like that... Edited August 24, 2022 by Metin2 Dev Core X - External 2 Internal Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now