none
sql select with c# RRS feed

  • Question

  • Hello everyone,
    I try to create a program in C# to connect with sql and retrieve some information from several tables using a select and join. However, I receive an exception when I run the program, could you tell me where is my mistake? Please, follow my program below:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Text;
    using System.Threading.Tasks;

    namespace SipeliaRingGroups
    {
        class Program
        {
            static void Main(string[] args)
            {
                SqlConnection conn = new SqlConnection("Server=(local)\\SQLEXPRESS;Database=Sipelia;USER ID-Admin;Password=123456; Integrated Security=true");
                conn.Open();
                SqlCommand cmd = new SqlCommand("select sip.Extension as Extensions, rg.name , rg.IsUserGroup, rg.IsHuntGroup, sip2.Extension  from SipServerExtensions as sip left join Devices as dev  on  sip.ExtensionId = dev.ExtensionId join SipServerRingGroupEntries as rge on rge.EntityId = dev.DeviceId join SipServerRingGroups as rg on rge.RingGroupId = rg.RingGroupId join SipServerExtensions as sip2 on sip2.ExtensionId = rg.ExtensionI", conn);

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("{0},{1},{2},{3},{4}", reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4));

                }
                reader.Close();
                conn.Close();

                if (Debugger.IsAttached)
                {
                    Console.ReadLine();
                }

                Console.ReadLine();
            }
        }
    }

    Thanks

    Jose

    Saturday, October 7, 2017 6:42 PM

All replies

  • Hello,

    Code looks good so my advise is to indicate which line the exception is thrown. If the exception is throw in the while statement then best guess is (and this is a guess since you didn't indicate the error) one of the Get's is asking for the wrong type e.g. using GetString where it should be GetBool for instance.

    Also, by using "using" statements on the command there is not need to close the reader, using a "using" statement on the connection means no need to close the connection.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 7, 2017 6:48 PM
    Moderator
  • Maybe Houdini knows what that error message is since it is nowhere to be found in your post.

    Saturday, October 7, 2017 8:15 PM
  • Hello, thanks for your answer

    Please, follow screenshot below

    How can connect with sql if I don't send the user and password?

    What is the correct option to set a connection?

    Saturday, October 7, 2017 10:11 PM
  • sorry the screenshot was not here

    this is the error

    {"Keyword not supported: 'user id-admin;password'."}

    Saturday, October 7, 2017 10:12 PM
  • See the following page, search for prompt.

    https://www.connectionstrings.com/sql-server/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 7, 2017 11:08 PM
    Moderator
  • Hi Jose,

    SqlServer Connection string was incorrect, so ADO.NET threw error message. Try to use the following code snippet to general connection string.

    using System.Data.SqlClient;
    
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString =
    "Data Source=ReplaceServerNameHere;" +
    "Initial Catalog=ReplyDatabaseNameHere;" +
    "User id=ReplaceUserNameHere;" +
    "Password=ReplacePasswordHere;";
    
    conn.Open();

    Another way, visual studio provides visual operation to connect to SqlServer, please refer to the following guide, https://msdn.microsoft.com/en-us/library/mt710791.aspx

    A code sample from MSDN, you could see sample connection string in App.config file, https://code.msdn.microsoft.com/How-to-Create-and-Execute-86922261#content

    Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 12, 2017 2:41 AM
    Moderator