[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

C# Connection to SQL Server error

Posted on 2012-09-13
14
Medium Priority
?
546 Views
Last Modified: 2012-09-13
Hi.

I have written a console application, that I need to call a stored procedure in.  I am not too sure if I have it done correctly, but this is what I have:

      public void Daily()
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
           
           conn = new        SqlConnection("Server=100.100.x.x\TestServer;DataBase=myDB; System;Integrated Security=SSPI");
           conn.Open();
           SqlCommand cmd  = new SqlCommand("sp_RunDaily", conn);
           cmd.CommandType = CommandType.StoredProcedure;
           rdr = cmd.ExecuteReader();
        }

I am getting an error by the "\" on my server name, saying that it is an unrecognized escape sequence.  What am I doing wrong?
0
Comment
Question by:Jasmin01
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 20

Expert Comment

by:Rikin Shah
ID: 38394818
Try-
Server='100.100.x.x\TestServer';
0
 
LVL 45

Assisted Solution

by:AndyAinscow
AndyAinscow earned 1332 total points
ID: 38394833
conn = new        SqlConnection("Server=100.100.x.x\\TestServer;DataBase=myDB; System;Integrated Security=SSPI");

Note the double \ character, a single \ is interpreted as preceding a special character to make an escape sequence such as \n for new line.
0
 

Author Comment

by:Jasmin01
ID: 38394861
Thanks.  I just have one more question, my stored procedure will return a list of results, which I need to loop through to perform work on the result.  How do I alter my code to get my results from the stored procedure returned?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 20

Expert Comment

by:Rikin Shah
ID: 38394863
Also, if that doesn't help, try following connection string-

Data Source=190.190.200.100\MyInstance,1043;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

You can replace your port number with 1043.
0
 
LVL 20

Expert Comment

by:Rikin Shah
ID: 38394886
0
 

Author Comment

by:Jasmin01
ID: 38394902
Thanks.  I have looked at the tutorial.  I don't want to pass a parameter to the stored proc.  I want to return the result of the stored proc in an array.
0
 
LVL 45

Assisted Solution

by:AndyAinscow
AndyAinscow earned 1332 total points
ID: 38394916
rdr contains the results returned by the stored procedure.  Just loop through it.


while (rdr.Read())
        {
            //rdr[0].ToString();
        }
        rdr.Close();
0
 
LVL 20

Expert Comment

by:Rikin Shah
ID: 38394935
Passing parameters are optional.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 38394977
>> I want to return the result of the stored proc in an array.

See my previous comment.  I don't think you can do that directly - you need to loop through the reader and add to the array in code (or just use the reader - that has the data).
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38394998
here are functions I use to execure stored proc with parameters:

 public int ExecuteStoredProc(string procName, SqlParameter[] parms, string strConn)
        {
            SqlConnection conn = GetConnection(strConn);
            int result = 0;
            SqlCommand cmd = new SqlCommand(procName, conn);

            // Configure the SqlCommand object
            if (!(cmd == null))
            {
                try
                {

                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;      //Set type to StoredProcedure
                    cmd.CommandText = procName;                    //Specify stored procedure to run

                    // Clear any previous parameters from the Command object
                    cmd.Parameters.Clear();

                    // Loop through parameter collection adding parameters to the command object
                    if (!(parms == null))
                    {
                        foreach (SqlParameter sqlParm in parms)
                        {
                            if (!(sqlParm == null))
                            {
                                cmd.Parameters.Add(sqlParm);
                            }
                        }
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error adding parameters to the stored procedure " + procName + ": " + ex.Message);
                }
            }

            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write("Error executing stored procedure " + procName + ": " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public SqlConnection GetConnection(string strConn)
        {
            SqlConnection conn;

            try
            {
                conn = new SqlConnection(strConn);
                conn.Open();
            }
            catch (SqlException ex)
            {
                Console.Write("SQL ERROR: " + ex.Message);
            }
            catch (Exception ex)
            {
                Console.Write("ERROR: " + ex.Message);
            }
            return conn;
        }

Open in new window

0
 

Author Comment

by:Jasmin01
ID: 38395000
Can you please post an example of how to loop through the reader?  I actually want to pass each record of the result from the stored procedure to a function called CheckName(firstname, surname).
0
 
LVL 35

Accepted Solution

by:
YZlat earned 668 total points
ID: 38395032
While(reader.read())
{
       CheckName(reader["firstname"], reader["surname"]);
}
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 38395471
>>Can you please post an example of how to loop through the reader?

I did so earlier, here it is repeated.

while (rdr.Read())
        {
            //rdr[0].ToString();
        }
        rdr.Close();
0
 
LVL 8

Expert Comment

by:hpdvs2
ID: 38396431
Since this split into a second question, you should probably post it separately.  Otherwise, Who will you award the points to if your primary question was answered by someone else than who figures out this issue.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question