SQL Server 2008 in C# console
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 5 1234 ... LastLast
Results 1 to 15 of 72

Thread: SQL Server 2008 in C# console

  1. #1
    Join Date
    Apr 2009
    Location
    Ohio Summit County
    Posts
    114

    SQL Server 2008 in C# console

    What I'm trying to to do is insert a new record into a Sql Server 2008 database file. I fount some samples its just they just don't work. Does any one have any idea on how to do this.

    below is just one out of 5 samples I fount.


    try
    {
    string UserConnection = Properties.Settings.Default.UsersConnectionString;
    SqlConnection MyConnection = new SqlConnection(UserConnection);

    MyConnection.Open();

    String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('addr.name', 'addr.telnetaddr')";
    SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);

    MyCmd.ExecuteScalar();
    MyConnection.Close();
    RMDoor.WriteLn("New Entry Added");

    }
    catch(SqlException e)
    {
    RMDoor.WriteLn(e.Message.ToString());
    }
    ToppersBBS on the web - http://toppersbbs.dtdns.net
    Forums Section - http://toppersbbs.dtdns.net/smf

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,999

    Re: SQL Server 2008 in C# console

    Please use CODE TAGS.

    What error are you getting? I don't have SQL 2008 installed, but just read an article a few days ago that I'll share.

    http://msdn.microsoft.com/en-us/magazine/dd695918.aspx
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Apr 2009
    Location
    Ohio Summit County
    Posts
    114

    Re: SQL Server 2008 in C# console

    Quote Originally Posted by dglienna View Post
    Please use CODE TAGS.

    What error are you getting? I don't have SQL 2008 installed, but just read an article a few days ago that I'll share.

    http://msdn.microsoft.com/en-us/magazine/dd695918.aspx
    CODE TAGS. Sorry I have no idea what code tags are.
    And I'm not getting any error, It just does insert.
    ToppersBBS on the web - http://toppersbbs.dtdns.net
    Forums Section - http://toppersbbs.dtdns.net/smf

  4. #4
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,999

    Thumbs down Re: SQL Server 2008 in C# console

    Well, you probably have more code than that. Can you do anything else like query data? Is your connection working?

    Code tags make the code that you post look pretty and line up, so you don't end up with lines all merged together. Type [ code] and then [ /code] (without the first space in each)

    Code:
    '    this is '                    code tags
    '    and it lines up
    this isn't

    ' this is NOT
    ' code tags
    ' and it does NOT line up
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: SQL Server 2008 in C# console

    What is the exception that it shows? Your code looks ok to me. Tell us which line of the code is highlighted and what is the exact error message.

  6. #6
    Join Date
    Apr 2009
    Location
    Ohio Summit County
    Posts
    114

    Re: SQL Server 2008 in C# console

    Thats just it. I'm not gettings any errors at all. Its just not inserting. Below is the full code.

    Code:
     public static void add()
            {
                RMDoor.ClrScr();
                
                Person addr = new Person();
                
                RMDoor.Write("Enter Name : ");
                addr.name = RMDoor.ReadLn();
    
                
                RMDoor.Write("Address : ");
                addr.telnetaddr = RMDoor.ReadLn();
                
                RMDoor.ClrScr();
    
                RMDoor.WriteLn("BBS Name : " + addr.name);
                RMDoor.WriteLn("Telnet Address : " + addr.telnetaddr);
                RMDoor.WriteLn();
    
                RMDoor.Write("Is this what you want Y/N");
                string Q = RMDoor.ReadLn();
                Q = Q.ToUpper();
                savelog("User has just added an entry");
                    if (Q == "Y")
                    {
    
    
                        string UserConnection = Properties.Settings.Default.UsersConnectionString;
                        SqlConnection MyConnection = new SqlConnection(UserConnection);
                                            
                        String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('" + addr.name + "', '" + addr.telnetaddr + "')";
                        SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
    
                        MyConnection.Open();
                        try
                        {
                            MyCmd.ExecuteNonQuery();    
                            Int32 rowsAffected = MyCmd.ExecuteNonQuery();
                            RMDoor.WriteLn("RowsAffected: " + rowsAffected);
                            MyConnection.Close();
                        }
                        catch (SqlException ex)
                        {
                           RMDoor.WriteLn(ex.Message);
                        }
    
                        
                    }
                    else
                    {
                        RMDoor.WriteLn("Save Aborted");
    
                    }
                
                RMDoor.WriteLn("Press Enter to Continue");
                RMDoor.WriteLn();
    
            }
    Thanks for any help.
    Last edited by Shuja Ali; May 30th, 2009 at 04:34 PM. Reason: Added Code tags
    ToppersBBS on the web - http://toppersbbs.dtdns.net
    Forums Section - http://toppersbbs.dtdns.net/smf

  7. #7
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,999

    Re: SQL Server 2008 in C# console

    Still haven't figured out to HIGHLIGHT your code, and push the CODE button?
    Code:
    Properties.Settings.Default.UsersConnectionString

    What does your connection string look like? Can you test it to make sure that it is valid?
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: SQL Server 2008 in C# console

    Why do you have ExecuteNonQuery twice?
    Code:
                            MyCmd.ExecuteNonQuery();    
                            Int32 rowsAffected = MyCmd.ExecuteNonQuery();
    Also how are you verifying whether the data was inserted or not. Are you connecting to the SQL Server separately and then checking or are you checking through some piece of code?

  9. #9
    Join Date
    Apr 2009
    Location
    Ohio Summit County
    Posts
    114

    Re: SQL Server 2008 in C# console

    I compiles and runs with no errors. I try adding a new record and nothing. No errors. Nothing at all. It just doesn't add any thing. I don't know of any thing more I can tell you. The code is posted. and doesn't work. Will some one help. If you need more details. Please ask.
    ToppersBBS on the web - http://toppersbbs.dtdns.net
    Forums Section - http://toppersbbs.dtdns.net/smf

  10. #10
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: SQL Server 2008 in C# console

    You did not answer my questions. Easiest way to see what is happening is by debugging. Put a break point on string UserConnection = Properties.Settings.Default.UsersConnectionString;
    line and run the code. Step through each line and see what is happening. Visual Studio's Local's window will tell you what is happening with which object. This way you should be able to see what is happening in the code and why the record is not being added.

  11. #11
    Join Date
    Apr 2009
    Location
    Ohio Summit County
    Posts
    114

    Re: SQL Server 2008 in C# console

    I fount it. I went back and check all my spelling again. and well its the Table. The tables reads UserData and it should read UsersData.

    Thanks guys. One more thing.

    string sname = "Tom Cat";
    How can I do a search for this name. If it finds it. It displays the records or if not. It throws an error.
    ToppersBBS on the web - http://toppersbbs.dtdns.net
    Forums Section - http://toppersbbs.dtdns.net/smf

  12. #12
    Join Date
    Apr 2009
    Location
    Ohio Summit County
    Posts
    114

    Re: SQL Server 2008 in C# console

    Quote Originally Posted by Shuja Ali View Post
    You did not answer my questions. Easiest way to see what is happening is by debugging. Put a break point on string UserConnection = Properties.Settings.Default.UsersConnectionString;
    line and run the code. Step through each line and see what is happening. Visual Studio's Local's window will tell you what is happening with which object. This way you should be able to see what is happening in the code and why the record is not being added.
    Ok, Your referring to the application settings file. The Connection String for the database is in there. When you add a database to a application thats where it saves the connection string at.

    string UserConnection = Properties.Settings.Default.UsersConnectionString;
    Console.WriteLine(" Your Connection string reads as : ", UserConnection)
    ToppersBBS on the web - http://toppersbbs.dtdns.net
    Forums Section - http://toppersbbs.dtdns.net/smf

  13. #13
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,419

    Re: SQL Server 2008 in C# console

    Just thought I'd mention that in the interest of conserving resources, you'll probably want to close your connections and command objects.

    While you could just call Close() on each object, it's kind of cumbersome because, you'd have to do add a finally block and do some checks, etc.

    A cleaner way is to use a using block as follows:

    Code:
    try
    {
        using( SqlConnection cn = new SqlConnection( UserConnection ) )
        {
          using( SqlCommand cmd = new SqlCommand( MyString, cn ) )
          {
            cn.Open();
            cmd.ExecuteNonQuery();    
          }
    } 
    catch( ...... )
    {
    }
    The code above closes the connection and releases the sqlcommand

  14. #14
    Join Date
    Apr 2009
    Location
    Ohio Summit County
    Posts
    114

    Re: SQL Server 2008 in C# console

    Thank you for the information. How ever I still can't see hoe thats going to help. The problem is that I'm getting too many errors that are being thrown by the program.

    look here
    String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('addr.name', 'addr.telnetaddr')";

    On this line I was getting an ITY_INSERT is set to off. Well I have to add this to the line
    String MyString = @"INSERT INTO ITY_INSERT SET UserData ON (Name, telnetaddr) VALUES('addr.name', 'addr.telnetaddr')";

    I had to add this option to my line just to try and get it to insert. And it makes no since at all. After getting this to work. Now I'm getting a Incorrect syntax near 'Name'. Well I just check and every one is telling me that the line is right. So It should insert.

    So I'm lost on this. I can't understand why this thing doesn't work.
    Last edited by bigjoe11a; May 31st, 2009 at 09:42 AM.
    ToppersBBS on the web - http://toppersbbs.dtdns.net
    Forums Section - http://toppersbbs.dtdns.net/smf

  15. #15
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,419

    Re: SQL Server 2008 in C# console

    In post #11 you had mentioned that you solved the problem, so my previous post was a suggestion to clean up your code a bit (and release resources as soon as possible).

    As far as your problem, I suggest that you break the problem down. Is it the .Net Code? Is it the SQL code?

    To find out, set a break point on the MyCmd.ExecuteNonQuery(); line and copy the query text from the object (see one of the SqlCommand properties to get this text).

    Then paste that text into the Sql Query Analyser window and inspect the query. Does the query execute properly? If it does then the query portion works so look for connection issues, insufficient permissions, etc.

    If it doesn't take a look at what is wrong with the query statement.

    As an aside, I generally don't put SQL code into my business layer. I don't know if I'm old school or new school, but I always use a stored procedure as the interface between SQL and .Net code. Of course this doesn't help your particular problem (file this under Tips), but the benefit is that I can test the stored procedure on the SQL side and know it works. Then to test it on the .Net side, I merely verify that the parameters are passed in properly. The added bonus is that I can modify the stored procedure on the SQL side at any time and as long as I don't change the param list (i.e., the interface remains the same), I don't have to recompile the .Net code.

Page 1 of 5 1234 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center