How to do it in vs2010

Question

1.00/5 (2 votes)

See more:

VS2008

VS2010

, +

i have following successful sql query but as i am a beginner of vs2010 i am not getting how exactly this query should i have to write in vs2010 so that i can insert the data successfully from UI....If anybody can help me????plz

C#

DECLARE @CategoryName VARCHAR(200),
@Category_id INT
SET @CategoryName='singh1';        
INSERT INTO tblCategory(CategoryName)
SELECT @CategoryName;
SELECT @Category_id= SCOPE_IDENTITY();  
INSERT INTO tblProduct(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
SELECT 'rahul', @Category_id , 'verma', '15', '500';

-----------------------------------------------------------------------------------
:::improvement:::

1:-- this is user interface form where i am trying to get data from user

C#

private void btnSave_Click(object sender, EventArgs e)
        {
             try
            {
                Product obj_product = new Product();

                obj_product.PName = txtProductName.Text;
                obj_product.PCompany = txtCompanyName.Text;
                obj_product.PUnitPrice = txtUnitPrice.Text;
                obj_product.PQty = Convert.ToInt32(txtQty.Text);
                //SetCategory();
                obj_product.Save();
                MessageBox.Show("Record Saved Successfully");
                LoadProduct();
                ClearScreen();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }

--------------------------------------------------------------------------
2:- this is middle tier

C#

public void Save()
       {
           clsSqlServer obj_sql = new clsSqlServer();
           obj_sql.InsertProduct(  _PName,
                                   _PCategory_FK,
                                   _PCompany,
                                   _PUnitPrice,
                                   _PQty);
       }

---------------------------------------------------------------------------

3:- this is data access tier

C#

public bool InsertProduct(string P_Name , int P_Cat_Fk , string P_Company, string P_Price, int P_Qty)
       {
           string con = ConfigurationManager.ConnectionStrings["InvoiceDB"].ToString();
           SqlConnection obj_connection = new SqlConnection(con);
           obj_connection.Open();
           try
           {
             string sqlInsert =    "'DECLARE @CategoryName VARCHAR(200),'" +
                                   "@Category_id INT " +
                                   "'SET @CategoryName='singh1';'" +
                                   "'INSERT INTO tblCategory(CategoryName)'" +
                                   "'SELECT @CategoryName;'" +
                                   "'SELECT @Category_id= SCOPE_IDENTITY();'"  +
                                   "'INSERT INTO tblProduct(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)'" +
                                   "'SELECT 'rahul', @Category_id , 'verma', '15', '50'";
              // string sqlInsert = "insert into tblProduct (Category_id_FK)" + "values('" + P_Name + "','" + P_Company + "','" + P_Price + "'," + P_Qty + ")" + "SELECT Category_id FROM tblCategory WHERE (Category_id = Category_id)";
               //string sqlInsert = "insert into tblProduct (ProductName,Category_id_FK,ProductCompany,ProductPrice,ProductQty)" + "values('" + P_Name + "'," + "SELECT Category_id FROM tblCategory WHERE (Category_id = Category_id)" + ",'" + P_Company + "','" + P_Price + "'," + P_Qty + ")";
               SqlCommand obj_SqlCommand = new SqlCommand(sqlInsert, obj_connection);
               obj_SqlCommand.ExecuteNonQuery();

               return true;
           }
           catch (Exception ex)
           {
               return false;
           }

           finally
           {
               obj_connection.Close();
           }
       }

-------------------------------------------------------------------------------

i am not getting how to communicate these all to each other...i tried to do this in many ways but not get succeed.. if any body can help me plz

Posted 20-Aug-15 5:14am

Member 11923566

Updated 20-Aug-15 6:30am

Patrice T

v4

Add a Solution

Comments

OriginalGriff 20-Aug-15 11:37am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
"VS2010" isn't a "UI", it's a development environment; an IDE. As such it supports half a dozen different programming languages, for a number of frameworks, for a number of environments.
If you are trying to execute that code in an application then you need to give us rather more detail, because I can't tell at all what you are trying to do, much less what problem you are having doing it, and what help you might need!
Use the "Improve question" widget to edit your question and provide better information.

F-ES Sitecore 20-Aug-15 11:49am

google "getting started with ado.net"

ZurdoDev 20-Aug-15 11:57am

You can do whatever you want. You can have a textbox where the user enters data and then clicks a button. You need to be way more clear on what your question is.

Member 11923566 20-Aug-15 14:09pm

if you could tell me that how to write the following query in vs2010
DECLARE @CategoryName VARCHAR(200),
@Category_id INT
SET @CategoryName='singh1';
INSERT INTO tblCategory(CategoryName)
SELECT @CategoryName;
SELECT @Category_id= SCOPE_IDENTITY();
INSERT INTO tblProduct(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
SELECT 'rahul', @Category_id , 'verma', '15', '500';

ZurdoDev 20-Aug-15 14:12pm

You don't write any sql in VS2010. You take that sql that you have and created a Stored Procedure. Then, in VS2010 you write code to call that stored procedure using the SqlConnection and SqlCommand objects.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2015-08-20T06:57:00

You need to pass the method parameters to the query as query parameter, and use those parameters to insert the data, rather than using hard-coded values.

Since you're passing in the category ID, the first part of your query is irrelevant. You don't need to insert a new category and retrieve its ID, since you already have the category ID you want to use.

Wrap your SqlConnection and SqlCommand objects in a using block to make sure they're cleaned up properly in all cases.

Don't catch the base Exception type; only catch the exceptions you can handle. In this case, you should only be catching SqlException. You should also be logging or displaying the details of the exception, so that you know what went wrong when your method returns false.

Something like this should work:

C#

public bool InsertProduct(string productName, int categoryId, string companyName, string price, int qty)
{
    const string Query = @"INSERT INTO tblProduct (ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
    VALUES (@ProductName, @CategoryID, @ProductCompany, @ProductPrice, @ProductQty);";

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["InvoiceDB"].ConnectionString))
    using (SqlCommand command = new SqlCommand(Query, connection))
    {
        command.Parameters.AddWithValue("@ProductName", productName);
        command.Parameters.AddWithValue("@CategoryID", categoryId);
        command.Parameters.AddWithValue("@ProductCompany", companyName);
        command.Parameters.AddWithValue("@ProductPrice", price);
        command.Parameters.AddWithValue("@ProductQty", qty);

        try
        {
            connection.Open();
            command.ExecuteNonQuery();
            return true;
        }
        catch (SqlException ex)
        {
            // TODO: Log or display the error somewhere.
            return false;
        }
    }
}