"INSERT INTO" - Question

Hello,

if I have a row full of Data, lets call it Row "A". Now i have the "Insert Into" Command in my Application. So, when the User executes the Command again, will it update Row "A" or will it produce just a double?

This is important for my further Work with SQL.

I am using SQL Server 2006 Express or something like this. The Table in which Row "A" is has no primary key!

Greetz,

Eroli

Unless you have constraints/triggers or your INSERT violates the defaults/constraints, it will add a NEW ROW to the table irrespective of the existing data.

|||

Hi,

but when i put it all into a for-command like this one

for(int i = 0; i != 5;i++)

{

//All the Commands

}

I get only one row.

So, whats right now?

Greetz,

Eroli

|||Depends on what the commands are. Post all the relevant code and that might help.|||

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "INSERT INTO UsersTable([User], [FirstStart], [Commercial], [Name], [MailAddresse], [Addresse], [Addresse2], [Location], [PostCode], [Country], [PhoneNumber], [PhoneNumber2], [Sex], [ShowLastBoughts], [StartPage], [Notifications], [RemainingTime], [HomepageUrl], [Employees], [Owner], [Evaluations]) VALUES (@.User,@.FirstStart,@.Commercial, @.Name,@.MailAddresse,@.Addresse,@.Addresse2,@.Location,@.PostCode,@.Country,@.PhoneNumber,@.PhoneNumber2,@.Sex,@.ShowLastBoughts,@.StartPage,@.Notifications,@.RemainingTime,@.HomepageUrl,@.Employees,@.Owner,@.Evaluations)";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", -1);
SqlCommand.Parameters.AddWithValue("@.Commercial", -1);
SqlCommand.Parameters.AddWithValue("@.Name", "");
SqlCommand.Parameters.AddWithValue("@.MailAddresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse2", "");
SqlCommand.Parameters.AddWithValue("@.Location", "");
SqlCommand.Parameters.AddWithValue("@.PostCode", -1);
SqlCommand.Parameters.AddWithValue("@.Country", "");
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", -1);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", -1);
SqlCommand.Parameters.AddWithValue("@.Sex", "");
SqlCommand.Parameters.AddWithValue("@.ShowLastBoughts", "");
SqlCommand.Parameters.AddWithValue("@.StartPage", -1);
SqlCommand.Parameters.AddWithValue("@.Notifications", -1);
SqlCommand.Parameters.AddWithValue("@.RemainingTime", -1);
SqlCommand.Parameters.AddWithValue("@.HomepageUrl", "");
SqlCommand.Parameters.AddWithValue("@.Employees", -1);
SqlCommand.Parameters.AddWithValue("@.Owner", "");
SqlCommand.Parameters.AddWithValue("@.Evaluations", "");
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

I've got only one Row.

|||

(1) Do you have any constraints/triggers on the table?

(2) Are you changing the values of the parameters for each loop or are youe xpecting to insert 5 rows with same values?

|||

The User Column will be everytime the same, but maybe some other colums will change.

Is the UPDATE-Command better? When yes, so how can i use this?

Im confused, because i have now more rows when i tried my application again. Seems your are right.

|||You need to explain more clearly what you are trying to do and what you intend to do. INSERT and UPDATE are 2 different commands that accomplish 2 different things. so there's no question of one "better" over another. You need to use either one depending on what how application needs to behave. Simply, INSERT inserts data into the table (meaning rows will increase), UPDATE updates te existing data (and how many rows affected depends on the WHERE condition in the query).|||

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

|||

Eroli:

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Yes.

Eroli:

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

Yes. You need to do put your logic accordingly.

|||Ok, thanks for your advice!|||

Ok, i changed my Commands.

This is my UPDATE Command:

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();</p><p>

It is in an ASP.NET Webapplication in C#! I get no failure, but nothing gets updated...

The obversely Row is filled with Dummy-Values before starting this Command.(MS SQL Server 2005 Express or so)

Greetz,

Eroli

|||

I hope this looks better...

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

|||

Ok, i solved it: I have just forgotten to put the [ ] round my Columns.

But there is another Problem: THe Condition does not work, because the User Column is no Identify-Column. How to solve this?

|||I did not understand your question. Can you rephrase?