-
December 15th, 2010, 02:11 PM
#1
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
-
December 15th, 2010, 02:22 PM
#2
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?
-
December 16th, 2010, 11:29 AM
#3
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.
-
December 20th, 2010, 09:34 AM
#4
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
-
December 20th, 2010, 11:16 AM
#5
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]";
-
December 21st, 2010, 10:07 AM
#6
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
-
December 21st, 2010, 11:57 AM
#7
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.
-
December 22nd, 2010, 11:07 AM
#8
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.
-
December 22nd, 2010, 11:15 AM
#9
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();
}
}
-
December 22nd, 2010, 11:32 AM
#10
Re: Write data to SQL database
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.
-
December 22nd, 2010, 11:43 AM
#11
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
-
December 22nd, 2010, 11:56 AM
#12
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.
-
December 22nd, 2010, 12:03 PM
#13
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?
-
December 22nd, 2010, 02:32 PM
#14
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).
-
December 22nd, 2010, 03:09 PM
#15
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|