Dream.In.Code > Programming Help > C#

Welcome to Dream.In.Code
	Getting C# Help is Easy! Join 132,655 C# Programmers for FREE! Get instant access to thousands of C# experts, tutorials, code snippets, and more! There are 1,170 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a C# Expert

Insert data into SQL Db

Insert data into SQL Db, Passing in variables instead of strings

Options

Premier2k	9 Sep, 2008 - 05:31 AM Post #1
New D.I.C Head Joined: 26 Aug, 2008 Posts: 22 My Contributions	Hi all, Let me first post my method; CODE private void btnNext_Click(object sender, RoutedEventArgs e) { string first = txtFirst.Text; string last = txtLast.Text; string nick = txtNick.Text; if (first == "" \|\| last == "" \|\| nick == "") { MessageBox.Show("Please ensure all fields are entered", "Error", MessageBoxButton.OK, MessageBoxImage.Error); } else { SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=SW-DEV-PROWLAND\SQLEXPRESS08;Initial Catalog=playerDb;User ID=***;Password=****;Pooling=False"; SqlCommand dataCommand = new SqlCommand(); dataCommand.Connection = dataConnection; dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES ('first', 'last', 'nick')"); dataConnection.Open(); dataCommand.ExecuteNonQuery(); } I am trying to insert a record, the user is entering a first name, last name and a nickname into a text box. I am storing the input into strings as such; string first = txtFirst.Text; string last = txtLast.Text; string nick = txtNick.Text; I'm then attempting to INSERT these into my database. If I write this line like this; dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES ('first', 'last', 'nick')"); then it inserts first, last and nick rather than the actual values (because of the single quotes I'm guessing). If I write this line as this; (without quotes) dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES (first, last, nick)");** then I get an error written below; Invalid column name 'first'. Invalid column name 'last'. Invalid column name 'nick'. How do I pass in variables rather than actual strings? Totally confused! Thanks all, Premier2k
	This Post Was Helpful! ^New!


Register for Free to Make These Ads Go Away!

PsychoCoder

9 Sep, 2008 - 05:46 AM

Post #2

using DIC.Core;

Joined: 26 Jul, 2007
Posts: 8,933

Windows

Thanked 118 times

Dream Kudos: 8525

Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net

My Contributions

The best way to do this is with SqlParameters. This creates what is known as a parametrized query, which also goes a long way in fighting SQL Injection attacks.

The way you were attempting this was doing two things, with your first attempt it was viewing your variables as the actual values you wanted inserted, thus it was doing exactly what you were telling it to do. In your second attempt it was viewing your variables as column names, and sicne your table doesnt contain those columns it was complaining at you.

When you use parameters (as you will see in the modifications I made to your code below) it knows exactly what to dow ith them. Take a look at the changes I made:

csharp


private void btnNext_Click(object sender, RoutedEventArgs e)
{
    string first = txtFirst.Text;
    string last = txtLast.Text;
    string nick = txtNick.Text;

    if (first == "" || last == "" || nick == "")
    {
        MessageBox.Show("Please ensure all fields are entered", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
    else
    {
        System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection();
        dataConnection.ConnectionString =
            @"Data Source=SW-DEV-PROWLAND\SQLEXPRESS08;Initial Catalog=playerDb;User ID=*****;Password=********;Pooling=False";

        System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
        dataCommand.Connection = dataConnection;

        //tell the compiler and database that we're using parameters (thus the @first, @last, @nick)
        dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES (@first, @last, @nick)");
       
        //add our parameters to our command object
        dataCommand.Parameters.AddWithValue("@first", first);
        dataCommand.Parameters.AddWithValue("@last", last);
        dataCommand.Parameters.AddWithValue("@nick", nick);

        dataConnection.Open();
        dataCommand.ExecuteNonQuery();
    }
}

Hope that helps and makes sense

This Post Was Helpful! ^New!

Premier2k	9 Sep, 2008 - 06:12 AM Post #3
New D.I.C Head Joined: 26 Aug, 2008 Posts: 22 My Contributions	Yes, looking at the code that seems to make sense. I shall go over this carefully and pick it apart to see how it all works! [Addition] I have implemented this into my code and it seems to work brilliantly! Many thanks! Premier2k
	This Post Was Helpful! ^New!

PsychoCoder	9 Sep, 2008 - 06:21 AM Post #4
using DIC.Core; Joined: 26 Jul, 2007 Posts: 8,933 Thanked 118 times Dream Kudos: 8525 Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net My Contributions	No problem, glad I could help. Now go forth and bring C# data access to the world
	This Post Was Helpful! ^New!

Premier2k	9 Sep, 2008 - 12:05 PM Post #5
New D.I.C Head Joined: 26 Aug, 2008 Posts: 22 My Contributions	Damn these SQL commands! Now I'm trying to delete a set of records. I've tried this; CODE private void btnNext_Click(object sender, RoutedEventArgs e) { string first = txtFirst.Text; string last = txtLast.Text; string nick = txtNick.Text; if (first == "" \|\| last == "" \|\| nick == "") { MessageBox.Show("Please ensure all fields are entered", "Error", MessageBoxButton.OK, MessageBoxImage.Error); } else { System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=SW-DEV-PROWLAND\SQLEXPRESS08;Initial Catalog=playerDb;User ID=*******;Password=****;Pooling=False"; System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand(); dataCommand.Connection = dataConnection; dataCommand.CommandText = ("DELETE from playerDetails where nickName = @nick"); dataCommand.Parameters.Remove(@nick); dataConnection.Open(); dataCommand.ExecuteNonQuery(); txtFirst.Clear(); txtLast.Clear(); txtNick.Clear(); } } But I get an error message; The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects. I'm trying to delete the entire row from the Db. I also tried using RemoveAt like this; dataCommand.Parameters.RemoveAt(@nick); but this too results in An SqlParameter with ParameterName 'nick' is not contained by this SqlParameterCollection. error message. I'm really starting to hate SQL.... Premier2k
	This Post Was Helpful! ^New!

PsychoCoder	9 Sep, 2008 - 12:17 PM Post #6
using DIC.Core; Joined: 26 Jul, 2007 Posts: 8,933 Thanked 118 times Dream Kudos: 8525 Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net My Contributions	Why are you trying to remove a SqlParameter? When you're executing a sql command you always add the parameter like csharp dataCommand.Parameters.AddWithValue("@nick", nick); Unless I'm misunderstanding what you're asking?
	This Post Was Helpful! ^New!

Premier2k	9 Sep, 2008 - 12:25 PM Post #7
New D.I.C Head Joined: 26 Aug, 2008 Posts: 22 My Contributions	QUOTE(PsychoCoder @ 9 Sep, 2008 - 01:17 PM) Why are you trying to remove a SqlParameter? When you're executing a sql command you always add the parameter like csharp dataCommand.Parameters.AddWithValue("@nick", nick); Unless I'm misunderstanding what you're asking? ah I see! I'm adding the parameter with that line, not defining what I want to do! I was thinking I wanted to remove the row that's why I picked the Remove option. Just inputted that correctly and its fine now. I'm still not liking SQL commands though... Perhaps someone should write a tutorial on this? Maybe me, if I ever learn this part. Thanks again! Premier2k
	This Post Was Helpful! ^New!

eclipsed4utoo	9 Sep, 2008 - 12:49 PM Post #8
D.I.C Regular Joined: 21 Mar, 2008 Posts: 314 Thanked 17 times Dream Kudos: 25 My Contributions	QUOTE(Premier2k @ 9 Sep, 2008 - 04:25 PM) QUOTE(PsychoCoder @ 9 Sep, 2008 - 01:17 PM) Why are you trying to remove a SqlParameter? When you're executing a sql command you always add the parameter like csharp dataCommand.Parameters.AddWithValue("@nick", nick); Unless I'm misunderstanding what you're asking? ah I see! I'm adding the parameter with that line, not defining what I want to do! I was thinking I wanted to remove the row that's why I picked the Remove option. Just inputted that correctly and its fine now. I'm still not liking SQL commands though... Perhaps someone should write a tutorial on this? Maybe me, if I ever learn this part. Thanks again! Premier2k Google has about 10,000 of them.
	This Post Was Helpful! ^New!

tenby	15 Oct, 2008 - 05:01 AM Post #9
New D.I.C Head Joined: 15 Sep, 2008 Posts: 2	QUOTE(Premier2k @ 9 Sep, 2008 - 06:31 AM) Hi all, Let me first post my method; CODE private void btnNext_Click(object sender, RoutedEventArgs e) { string first = txtFirst.Text; string last = txtLast.Text; string nick = txtNick.Text; if (first == "" \|\| last == "" \|\| nick == "") { MessageBox.Show("Please ensure all fields are entered", "Error", MessageBoxButton.OK, MessageBoxImage.Error); } else { SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=SW-DEV-PROWLAND\SQLEXPRESS08;Initial Catalog=playerDb;User ID=***;Password=****;Pooling=False"; SqlCommand dataCommand = new SqlCommand(); dataCommand.Connection = dataConnection; dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES ('first', 'last', 'nick')"); dataConnection.Open(); dataCommand.ExecuteNonQuery(); } I am trying to insert a record, the user is entering a first name, last name and a nickname into a text box. I am storing the input into strings as such; string first = txtFirst.Text; string last = txtLast.Text; string nick = txtNick.Text; I'm then attempting to INSERT these into my database. If I write this line like this; dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES ('first', 'last', 'nick')"); then it inserts first, last and nick rather than the actual values (because of the single quotes I'm guessing). If I write this line as this; (without quotes) dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES (first, last, nick)");** then I get an error written below; Invalid column name 'first'. Invalid column name 'last'. Invalid column name 'nick'. How do I pass in variables rather than actual strings? Totally confused! Thanks all, Premier2k
	This Post Was Helpful! ^New!

newProgram	28 Oct, 2008 - 10:29 PM Post #10
New D.I.C Head Joined: 28 Oct, 2008 Posts: 20	QUOTE(tenby @ 15 Oct, 2008 - 06:01 AM) QUOTE(Premier2k @ 9 Sep, 2008 - 06:31 AM) Hi all, Let me first post my method; CODE private void btnNext_Click(object sender, RoutedEventArgs e) { string first = txtFirst.Text; string last = txtLast.Text; string nick = txtNick.Text; if (first == "" \|\| last == "" \|\| nick == "") { MessageBox.Show("Please ensure all fields are entered", "Error", MessageBoxButton.OK, MessageBoxImage.Error); } else { SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=SW-DEV-PROWLAND\SQLEXPRESS08;Initial Catalog=playerDb;User ID=***;Password=****;Pooling=False"; SqlCommand dataCommand = new SqlCommand(); dataCommand.Connection = dataConnection; dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES ('first', 'last', 'nick')"); dataConnection.Open(); dataCommand.ExecuteNonQuery(); } I am trying to insert a record, the user is entering a first name, last name and a nickname into a text box. I am storing the input into strings as such; string first = txtFirst.Text; string last = txtLast.Text; string nick = txtNick.Text; I'm then attempting to INSERT these into my database. If I write this line like this; dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES ('first', 'last', 'nick')"); then it inserts first, last and nick rather than the actual values (because of the single quotes I'm guessing). If I write this line as this; (without quotes) dataCommand.CommandText = ("INSERT playerDetails (FirstName, LastName, NickName) VALUES (first, last, nick)"); then I get an error written below; Invalid column name 'first'. Invalid column name 'last'. Invalid column name 'nick'. How do I pass in variables rather than actual strings? Totally confused! Thanks all, Premier2k QUOTE what i will show you is another way in inserting a value on the database CODE SqlCommand command = new SqlCommand("insert into playerDetails(FirstName, LastName, NickName) values('"+TextBox1.Text+"','"+TextBox2.Text+"','"+TextBox3.Text+"')",connect); command.ExecuteNonQuery(); note that the "connect" is your connection string. textbox is where the data come from. This post has been edited by newProgram**: 29 Oct, 2008 - 12:13 AM
	This Post Was Helpful! ^New!