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

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

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

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

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]";

---

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

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

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

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();
            }

        }

---

Quote:

---

Originally Posted by rockking

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.

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

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.

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?

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).

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

alright so when i make a SProc, where do i store it exactly? in the database itself? or where?

I'd propose some reading. Use some samples and read a DB book. You will be able to run (solve) most of your queries.
http://msdn.microsoft.com/en-us/libr...v=sql.80).aspx

Quote:

---

Originally Posted by talksandy

I'd propose some reading. Use some samples and read a DB book. You will be able to solve most of your queries.
http://msdn.microsoft.com/en-us/libr...v=sql.80).aspx

---

haha queries, database humor. lol

Quote:

---

Originally Posted by rockking

Quote:

---

Originally Posted by talksandy

I'd propose some reading. Use some samples and read a DB book. You will be able to run (solve) most of your queries.
http://msdn.microsoft.com/en-us/libr...v=sql.80).aspx

---

haha queries, database humor. lol

---

Excuse me, but what is the reason here to lol? :confused:

It's not like you have a choice of where to store them. by their very nature they are kept in the database. That said, it would be a good idea to store the sql scripts you used to create them so that if you need to make changes it's easier... those files you can store where ever you like, but what gets run is what's in the database.

-tg

not sure. i was looking for something to take the stress away from trying to get this working. i did make a SProc for inserting data into a database. i need to create a few more, one to update and delete. i dont think they will be to bad, i am going to use the insert one as a sort of template. the only other thing i need to get working, is using a "config" file to give the dataconnection string. this way i can have the app available on more than just one machine, because it would allow for an easy way to change database paths.

alright everyone. i have gotten the "config" file working. i have all my stored procedures created. now the ultimate part.

i need to select data in the database and return the data received. once returned it will be assigned to variables and those variable will be set to the text of the corresponding textboxes.

suggestions?

It's geared towards VB, but the principles (and objects/namespaces) are the same....
http://www.vbforums.com/showpost.php...53&postcount=2

-tg

1. I have a jsp file which has dropdown containing product names which are fetched from the sql table. 2. My problem is, when i click the product name from the drop down, it should fetch the productID associated with the product name from the database and that product ID should be updated in another table. So everytime I select product name, it's product ID must be updated in another SQL table**

Quote:

---

Originally Posted by vinayshirish

1. I have a jsp file which has dropdown containing product names which are fetched from the sql table. ...

---

And does your problem have to do with this 4.5 years old thread?
And what does this jsp file have to do with the C-Sharp Programming forum?
:confused:
Please, start a new thread in the appropriate forum and describe your problem (if any) a bit more carefully!