CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Write data to SQL database

    hey supercoders.
    the title says it all. i am working on an application that requires the writing of data to a database. i have gotten the database connection working but i need to know how i write the data. i tried doing in straight forward and coded in to just write a value but it didnt work out. it crashed the app and failed. the app is a windows form app and all data is taken in from textboxes and assigned to variables. any help?

    thanks in advance

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

    Re: Write data to SQL database

    The SqlClient namespace in Msdn contains many code examples of working with SQL databases. Have you looked at any of these?

  3. #3
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Re: Write data to SQL database

    i have heard of them and glanced through them, but i will go see if i can find them.

  4. #4
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Re: Write data to SQL database

    alright. so i made a mock up of what i want to do with my application. so now i have the main one waiting for me to get this stuff understood. in the mock up i have a problem and i dont what the problem is. here is the connection string:
    Code:
     dataConnection.ConnectionString = "User ID = jsitz; Password= labR2o6; Data Source = DELL\SQLEXPRESS; Initial Catalog = [Learning_DB]";
    it gives me the error
    Code:
    Error	1	Unrecognized escape sequence	E:\Users\Owner\documents\visual studio 2010\Projects\CS_CheckInCheckOut\CS_CheckInCheckOut\Form1.cs	56	103	CS_CheckInCheckOut
    now i know you guys tell me try to understand the error and what it means, but this error is just straight up stupid. there is no escape sequence present in the connectionstring. it shows up at the "\" when i attempt to specify the SQL instance name, that is where the error occurs.
    any suggestions?
    thanks

  5. #5
    Join Date
    Oct 2004
    Posts
    206

    Re: Write data to SQL database

    You need to escape the backslash before the instance name:
    Code:
    dataConnection.ConnectionString = "User ID = jsitz; Password= labR2o6; Data Source = DELL\\SQLEXPRESS; Initial Catalog = [Learning_DB]";
    or
    Code:
    dataConnection.ConnectionString = @"User ID = jsitz; Password= labR2o6; Data Source = DELL\SQLEXPRESS; Initial Catalog = [Learning_DB]";

  6. #6
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Re: Write data to SQL database

    alright so now that error is fixed. now how do i fix the connection issue i am having. all the information is valid. i used the management studio to verify all of it. the server is set to accept remote connections, so that isnt an issue. but whenever i attempt to open the connection, the program crashes

    thank you for the help

  7. #7
    Join Date
    Oct 2004
    Posts
    206

    Re: Write data to SQL database

    You're going to have to show some code at this point, and provide whatever details you get at the point the program crashes.

  8. #8
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Re: Write data to SQL database

    i got it working. sorry. something with my connectionString was not good enough, bcuz i copied and pasted the string to the same database(the MSVS data connection) and it went right though. then it just told me i need to create a select statment for the data table
    Last edited by rockking; December 22nd, 2010 at 11:26 AM.

  9. #9
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Re: Write data to SQL database

    okay now it is going weird. i made my dataset with all the adapters it wanted and etc etc but it still throws this at me when i run it
    Code:
    ExecuteNonQuery: Connection property has not been initialized.
    whats is it telling me here?

    and here is the current event procedure

    Code:
            private void button1_Click(object sender, EventArgs e)
            {
                SqlConnection dataConnection = new SqlConnection();
                dataConnection.ConnectionString = "Data Source=DELL\\SQLEXPRESS;Initial Catalog=Learning_DB;User ID=jsitz;Password=labR2o6";
                SqlCommand dataCommand = new SqlCommand();
                try
                {
                    dataConnection.Open();
                    dataCommand.CommandText = "INSERT INTO Learning_Table([Last Name]) VALUES('lastName')";
                    dataCommand.ExecuteNonQuery();
                }
                finally
                {
                    dataConnection.Close();
                }
    
            }

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

    Re: Write data to SQL database

    Quote Originally Posted by rockking View Post
    okay now it is going weird. i made my dataset with all the adapters it wanted and etc etc but it still throws this at me when i run it
    Code:
    ExecuteNonQuery: Connection property has not been initialized.
    whats is it telling me here?
    It's telling you that the Connection property of the SqlCommand object hasn't been initialized. In other words, the SqlCommand object has to have a connection for it to work. You can set this in the SqlCommand constructor or with the Connection property.

    That being said, one thing you should consider is to use a 'using' block to help you close the database connection and free up resources. It also makes the code cleaner.

    Try the following code:
    Code:
    using( var conn = new SqlConnection( "Data Source=DELL\\SQLEXPRESS;Initial Catalog=Learning_DB;User ID=jsitz;Password=labR2o6" ) )
    {
      using( var cmd = new SqlCommand( "INSERT INTO Learning_Table([Last Name]) VALUES('lastName')", conn ) )
      {
        conn.Open( );
        cmd.ExecuteNonQuery();
      }
    }
    The using blocks will close the sql connection and free the objects after they go out of scope.

  11. #11
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Re: Write data to SQL database

    awesome. thank you Arjay! that worked perfectly. i had to add some characters so it would use variables from the program but here is what it is now.
    Code:
    using (var conn = new SqlConnection("Data Source=DELL\\SQLEXPRESS;Initial Catalog=Learning_DB;User ID=jsitz;Password=labR2o6"))
                {
                    using (var cmd = new SqlCommand("INSERT INTO Learning_Table([Last Name], [First Name]) VALUES('"+ lastName +"', '"+ firstName +"')", conn))
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
    it goes all the way through and will insert into the database just like i needed it to. =) thank you so much

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

    Re: Write data to SQL database

    At the very least, consider fixing up the code by using String.Format:

    using (var cmd = new SqlCommand( String.Format( "INSERT INTO Learning_Table([Last Name], [First Name]) VALUES( '{0}', '{1}' )", lastName, firstName ), conn ) )

    Also look into to "Parameterized Queries" and stored procedures.

  13. #13
    Join Date
    Jul 2010
    Location
    .NET 4.0/VS2010
    Posts
    79

    Re: Write data to SQL database

    what is the purpose of a stored procedure? and do i need parameters for inserting data? wouldn't i only want parameters when i am selecting rows to be updated or deleted?

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

    Re: Write data to SQL database

    A stored procedure is kind of like what a method is to C#.

    Consider the following sproc:
    Code:
    -- =============================================
    -- Author:		Arjay
    -- Create date: 2010-12-22
    -- Description:	Inserts a new record into the Learning table.
    --              Returns the inserted id
    -- =============================================
    CREATE PROCEDURE [Learning.Insert]
      @FirstName NVARCHAR( 100 )
      , @LastName NVARCHAR( 100 )
      , @NewLearningId INT OUTPUT
    AS
    BEGIN
    	INSERT INTO [dbo].[Learning] ( [FirstName], [LastName] )
    		VALUES ( @FirstName, @LastName )
    
    	SET @NewLearningId = SCOPE_IDENTITY( )
    END
    GO
    In C#, you can call the sproc as follows:
    Code:
    using( var conn = new SqlConnection( "Data Source=DELL\\SQLEXPRESS;Initial Catalog=Learning_DB;User ID=jsitz;Password=labR2o6" ) )
    {
      using( var cmd = new SqlCommand( "[dbo].[Learning.Insert]", conn ) )
      {
        conn.Open( );
    
        // Set the command type to a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;
    
        // Add the input params
        cmd.Parameters.AddWithValue( "@FirstName", firstName );
        cmd.Parameters.AddWithValue( "@LastName", lastName );
    
        // Add the output param (retrieves the inserted record id)
        cmd.Parameters.Add("@NewLearningID", SqlDbType.Int);
        cmd.Parameters[2].Direction = ParameterDirection.Output;
    
        // execute the stored procedure
        cmd.ExecuteNonQuery( );
    
        // Retrieve the inserted id
        newLearningId = (int) cmd.Parameters[ 2 ];
      }
    }
    Comments about stored procedures:
    1) Stored procedures can be thought of as an 'interface' layer between the database and any calling code.
    2) Stored procedures help eliminate sql injection threats.
    3) Stored procedures are usually optimized so they often run faster than client executed queries.
    4) Sprocs are a great way to enforce security. Client accounts are granted execute permissions to the sprocs (as opposed to granting table level permissions). This decreases the security footprint of clients - you no longer have to worry about a client performing an illegal or destructive query).

    That being said, I'm kind of an old school guy that prefers using sprocs over client side sql (and linq).

  15. #15
    Join Date
    Dec 2007
    Posts
    234

    Re: Write data to SQL database

    SProcs are type safe ... concatenation or using string.format is fine for simple data where you have a lot of control over the data.... until the day comes when you need to add Paddy O'Brien to the database... the ' in O'Brien will cause SQL to drop out of string mode (since the ' is the string delimiter) ... and suddenly you have a very badly formed SQL Statement... By using parameters, you don't need to worry about errant tick marks, as it will be handled gracefully.

    -tg
    * I don't respond to private requests for help. It's not conducive to the general learning of others.-I also subscribe to all threads I participate, so there's no need to pm when there's an update.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help - how to remove eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to???
    * On Error Resume Next is error ignoring, not error handling(tm). * Use Offensive Programming, not Defensive Programming.
    "There is a major problem with your code, and VB wants to tell you what it is.. but you have decided to put your fingers in your ears and shout 'I'm not listening!'" - si_the_geek on using OERN
    MVP '06-'10

Page 1 of 2 12 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
  •  





Click Here to Expand Forum to Full Width

Featured