Click here to Skip to main content
15,064,915 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a doubt. I have 2 textbox. 1st textbox I already set a value and 2nd textbox, the user need to enter a value (int) and there's a button to save.

I want the fixed value and the entered value to be saved in db. How? is it possible ? I WANT THE ENTERED VALUE TO BE SAVED IN "VALUE" column AND FIXED VALUE TO BE SAVED IN "LOCATION"

What I have tried:

C#
SqlCommand cmd2= new SqlCommand("Insert into LAB_SH_LINE values(@sid,@Location,@Items,@Value,)", con);
cmd2.Parameters.AddWithValue("sid", sid);
Posted
Updated 22-Sep-20 22:16pm
v2
Comments
Richard MacCutchan 23-Sep-20 4:05am
   
You refer to four parameters in your INSERT command, but add "sid" rather than "@sid". Where is the rest of the code, and what exactly is the problem?
ravitv 3-Mar-21 6:17am
   
Friends,
I need to insert an incremented value in ProfileId column(which is NOT a PrimaryKey) in a table by selecting Max(UId)+1 which IS a PrimaryKey from that same table and use cmd.Parameters.AddWithValue(.....,.....) along with other text values fetched from .aspx file.
I want to Concatnate "PM" with Max(UId)+1, for example PermanentMember and the desired value may be PM1, PM2, PM3....soon and soforth, where 1 2 3...is UID

I dont want to use Stored Procedure in SQL Server 2008.

What I did was.

protected void btnlogin_Click1(object sender, EventArgs e)
{
{
SqlConnection cn = new SqlConnection(@"Data Source=Admin-PC\SQLEXPRESS;AttachDbFilename=C:\Users\.......mdf;Integrated Security=True;User Instance=True");


String query = "Insert into EmpRegister(ProfileId,UserName,Password,Mobile,Email) SELECT MAX(UId)+1 as @ProfileID,@UserName,@Password,@Mobile,@Email)";
SqlCommand cmd = new SqlCommand(query, cn);


//Pass Values to parameters

cmd.Parameters.AddWithValue("@UserName", txtuname.Text);
cmd.Parameters.AddWithValue("@Password", txtupass.Text);
cmd.Parameters.AddWithValue("@Mobile", mobPhone.Text);
cmd.Parameters.AddWithValue("@Email", emailID.Text);

try
{
cn.Open();
cmd.ExecuteNonQuery();
//Console.WriteLine("Records Inserted Successfully");
}
catch (Exception)
{
//Console.WriteLine("Error Generated. Details: " + e.ToString());
}
finally
{
cn.Close();
}
}
}

Please help me where to insert this MAX(UId)+1 value in ProfileId column.
I tested the code without MAX(UId)+1 and removing SELECT MAX(UId)+1 as @ProfileID,@UserN...... from String query, works well.

Thanks for you time.
Ravi.

1 solution

First off, never do "anonymous inserts" - always list the columns you want to insert, in the order you want to insert them:
SQL
INSERT INTO MyTable (MyColumn1, MyCOlumns2) VALUES (@ValueForColumn1, @ValueForColumn2)


Second, you almost certainly shouldn't be inserting ID values, particularly if they are Row IDs: values which uniquely identify a row that otherwise may contain identical data. Never use the existing maximum and add one yourself - it causes intermittent problems that are a total PITA to fix.

Third, you need to specify a value for each of the parameters in your INSERT statement: my example above has two parameter values (the "@" prefix indicates a parameter value to SQL) so I would need two calls to AddWithValue to supply them:
C#
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
        {
        cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
        cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
        cmd.ExecuteNonQuery();
        }
    }
You only supply one in your example, but you tell it to expect 4 v alues.

Fourth, The parameter names must match up in the AddWithValue call and the INSERT statement: you are missing the "@" prefix in your call.

Fifth, SQL uses commas as separators, not terminators: (@C1, @C2) is fine, but (@C1, @C2,) is a syntax error as there is nothing between the final comma and the close bracket.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900