Insert data into SQL Db

Passing in variables instead of strings

Page 1 of 1

9 Replies - 53809 Views - Last Post: 28 October 2008 - 11:29 PM Rate Topic: -----

#1 Premier2k  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 180
  • Joined: 26-August 08

Insert data into SQL Db

Post icon  Posted 09 September 2008 - 06:31 AM

Hi all,

Let me first post my method;

		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

Is This A Good Question/Topic? 0
  • +

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Insert data into SQL Db

Posted 09 September 2008 - 06:46 AM

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:

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 :)
Was This Post Helpful? 2

#4 Premier2k  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 180
  • Joined: 26-August 08

Re: Insert data into SQL Db

Posted 09 September 2008 - 07:12 AM

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
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Insert data into SQL Db

Posted 09 September 2008 - 07:21 AM

No problem, glad I could help.

Now go forth and bring C# data access to the world
Was This Post Helpful? 1

#6 Premier2k  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 180
  • Joined: 26-August 08

Re: Insert data into SQL Db

Posted 09 September 2008 - 01:05 PM

Damn these SQL commands!

Now I'm trying to delete a set of records.

I've tried this;

		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.... :crazy:


Premier2k
Was This Post Helpful? 1

#7 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Insert data into SQL Db

Posted 09 September 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

dataCommand.Parameters.AddWithValue("@nick", nick);



Unless I'm misunderstanding what you're asking?
Was This Post Helpful? 0
  • +
  • -

#8 Premier2k  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 180
  • Joined: 26-August 08

Re: Insert data into SQL Db

Posted 09 September 2008 - 01:25 PM

View PostPsychoCoder, on 9 Sep, 2008 - 01:17 PM, said:

Why are you trying to remove a SqlParameter? When you're executing a sql command you always add the parameter like

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... :D
Perhaps someone should write a tutorial on this? Maybe me, if I ever learn this part.


Thanks again!

Premier2k
Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: Insert data into SQL Db

Posted 09 September 2008 - 01:49 PM

View PostPremier2k, on 9 Sep, 2008 - 04:25 PM, said:

View PostPsychoCoder, on 9 Sep, 2008 - 01:17 PM, said:

Why are you trying to remove a SqlParameter? When you're executing a sql command you always add the parameter like

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... :D
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.
Was This Post Helpful? 0
  • +
  • -

#10 tenby  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 15-September 08

Re: Insert data into SQL Db

Posted 15 October 2008 - 06:01 AM

View PostPremier2k, on 9 Sep, 2008 - 06:31 AM, said:

Hi all,

Let me first post my method;

		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

Was This Post Helpful? 0
  • +
  • -

#11 newProgram  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 160
  • Joined: 28-October 08

Re: Insert data into SQL Db

Posted 28 October 2008 - 11:29 PM

View Posttenby, on 15 Oct, 2008 - 06:01 AM, said:

View PostPremier2k, on 9 Sep, 2008 - 06:31 AM, said:

Hi all,

Let me first post my method;

		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
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 October 2008 - 01:13 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1