Whats wrong with my insert command

Question

Hi  i am trying a new command for the first time but not able to insert data within same line

pls help ..i will be very thankful .

by using :-

string vsql = string.Format("insert into Components values ('(0)','(1)','(2)','(3)','(4)','(5)',(6),(7))",pro_id.Text, pro_name.Text, sup_id.Text, cat_id.Text, Man_id.Text,rate.Text,qntty.Text);

here are table description :-

create table Components 
(
Product_ID int,
Product_Name nvarchar(50),
Supplier_ID nvarchar(50),
Category_ID nvarchar(40),
Manufacture_ID nvarchar(20),
Price int,
Quantity int,

..

Answer 1

string vsql = string.Format("insert into Components values ('(0)','(1)','(2)','(3)','(4)','(5)',(6),(7))",pro_id.Text, pro_name.Text, sup_id.Text, cat_id.Text, Man_id.Text,rate.Text,qntty.Text);

1. For Placeholders for string.Format you have to use curly bracket like {0}; because you used a wrong format (0) the placeholder are not replace!

2. You "defined" 8 placeholder 0-7, but you add only 7 values for the placeholder.

P.S: Instead of creating dynamically SQL Statements this way, you should use SqlParameter to add values in the right way.

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

>> I am trying a new command [sic: statements are not commands] for the first time but not able to insert data within same line [sic: SQL is a free text language, not line driven like FORTRAN and BASIC] <<

Using dynamic SQL is like cannibalism; you do not do it until there is no other way.

 What you did try to post is not a table. It has no key and no way to ever have a key. The data element names make no sense; just think about what “category_id” means. A data element can be a “<something>_category” or a “<something>_id”, as you would know if you had read any basic data modeling book or website. He DUNS is how we identify businesses. What is the industry standard you use for the key for the products? Prices are decimal numbers. 

Let's fix that first:

CREATE TABLE Components 
(product_upc CHAR(13) NOT NULL PRIMARY KEY,
 product_name VARCHAR(50) NOT NULL,
 supplier_duns CHAR(10) NOT NULL,
 product_category CHAR(10) NOT NULL,
 manufacturer_duns CHAR(10) NOT NULL,
 product_unit_price DECIMAL(12,5) NOT NULL,
 product_onhand_qty INTEGER NOT NULL
 CHECK (product_onhand_qty >= 0));

This is still de-normalized, but at least the names are ISO-11179 and the data types make sense. Does a product have one and only one supplier, one and only one manufacturers? 

Your example of attempted code is also problematic. This wonderful magical, universal “text” is transformed from a data type an attribute. Maybe you want SQL injection?

Just write a stored procedure. What were you trying to do? Why did you want to do it this way?

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 3

Whats wrong with this code

create table Stock_Register 
(
R_Date nvarchar(100),
Particular nvarchar(100),
Bill_No nvarchar(100),
Receipt nvarchar(100),
Issue nvarchar(100),
Opening_Balance int,
Closing_Balance int,
Balance int,
Remark nvarchar(300)
)

                                                            

private void button1_Click(object sender, EventArgs e)
        {
            string vsql = string.Format("insert into Stock_Register values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8})",date.Text,textBox2.Text,textBox3.Text,textBox4.Text,textBox5.Text,textBox6.Text,textBox7.Text,textBox8.Text,textBox9.Text);
            SqlCommand cmd = new SqlCommand(vsql, Con);

            try
            {
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                MessageBox.Show("The Data Stored !","Information",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
                this.Dispose();



            }

            catch (Exception ex)
            {
                string m = ex.Message;
                MessageBox.Show("Error Occured" + ex.Message);

            }

        }

it always show error what happen with this code

Answer 4

As Olaf said, you need to use parameters. Using String.Format is a very bad thing to do.

Re-design this code properly.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

how can i archive parameter would you code it 

thankx in advance 

Answer 6

Take a look at this sample

http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

You need to change your command to use parameters and explicitly add them one by one.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

            string vsql = string.Format("insert into Stock_Register (R_Date,Particular,Bill_No,Receipt,Issue,Opening_Balance,Closing_Balance,Balance,Remark) Values(@date,@particular,@bill,@receipt,@issue,@opening,@closing,@balance,@remark");
            SqlCommand cmd = new SqlCommand(vsql, Con);
            //cmd.CommandText = 
            cmd.Parameters.AddWithValue("@date",date.Text);
            cmd.Parameters.AddWithValue("@particular",textBox2.Text);
            cmd.Parameters.AddWithValue("@bill", textBox3.Text);
            cmd.Parameters.AddWithValue("@receipt", textBox4.Text);
            cmd.Parameters.AddWithValue("@issue", textBox5.Text);
            cmd.Parameters.AddWithValue("@opening", textBox6.Text);
            cmd.Parameters.AddWithValue("@closing", textBox7.Text);
            cmd.Parameters.AddWithValue("@balance", textBox8.Text);
            cmd.Parameters.AddWithValue("@remark",textBox9.Text);
            cmd.ExecuteNonQuery();

I am getting a error 

System.Data.SqlClient.SqlException was unhandled
  Message=Incorrect syntax near '@remark'.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=15
  LineNumber=1
  Number=102
  Procedure=""
  Server=.
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Inv_Mg.Register.button1_Click(Object sender, EventArgs e) in C:\Users\Ashish\Desktop\Inv Mg server\Inv Mg\Register.cs:line 78
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Inv_Mg.Program.Main() in C:\Users\Ashish\Desktop\Inv Mg server\Inv Mg\Program.cs:line 17
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Whats wrong with it ?

Answer 8

Try typing the whole command as one line or use StringBuilder to compose this query string.

Also, instead of AddWithValue I suggest to use a more verbose version of Parameters.Add syntax.

If you're running not Express version of SQL Server, start SQL Profiler before running your application to capture the exact command send to SQL Server.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog