Solved

C# Return Complete SQL Query and Values?

Posted on 2012-03-15
5
309 Views
Last Modified: 2012-03-17
For the purpose of troubleshooting, I would like to return the complete SQL syntax and values to a label for either desktop or web page. I tried using sqldataadapter and  commandbuilder and still only return the sql syntax and no values.

[Query]

private void InsertTable(string lastname, string midname, string firstname)
{
      string sql = " Insert Into Table1 (col1, col2, col3) values ('" + lastname + "', '" + midname + "', '" + firstname + "')";

     SqlConnection cn = new SqlConnection(cnPath);
     SqlDataAdapter da = new SqlDataAdapter(sql, cn);
     cn.Open();
     da.SelectCommand.ExecuteNonQuery();    

     label1.Text = da.InsertCommand.CommandText;

     cn.Close();
}

[Current Return]
Insert Into Table1 (col1, col2, col3) values ('lastname', 'midname', 'firstname')

[Desired Return]
Insert Into Table1 (col1, col2, col3) values (Doe, J, Jane)

Help!
0
Comment
Question by:pointeman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 166 total points
ID: 37724482
if you are using a SQL database, start a profiler, you will see all the real queries that are sent to the database.
0
 
LVL 12

Accepted Solution

by:
Anuradha Goli earned 167 total points
ID: 37724572
This is the sample code I worked based on your conditions, review and do changes to relate to your database.

 
protected void BtnSubmit_Click(object sender, EventArgs e)
    {
        InsertTable("Testdata", "Testprice", "TestDesc");
    }
    private void InsertTable(string Name, string Price, string Desc)
    {
        string sql = " Insert Into tblProduct (Name,Price,Desc) values ('" + Name + "', '" + Price + "', '" + Desc + "')";

        SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString);
        SqlDataAdapter da = new SqlDataAdapter(sql, cn);
        cn.Open();
        SqlCommand com = new SqlCommand();
        com.Connection = cn;
        da.InsertCommand = com;
        da.InsertCommand.CommandText = sql;
        da.InsertCommand.CommandType = CommandType.Text;
        da.InsertCommand.ExecuteNonQuery();
        label1.Text = da.InsertCommand.CommandText;

        cn.Close();
    }

Open in new window



Web.config

<connectionStrings>
  <add name="ConnectionString2" connectionString="Data Source=SQLDB1210;Initial Catalog=SampleDB;Persist Security Info=True;User ID=sa;Password=Password;MultipleActiveResultSets=False;Packet Size=4096;Application Name=&quot;Microsoft SQL Server Management Studio&quot;"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

Open in new window

0
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 167 total points
ID: 37724668
You are using string concatenation to build your queries (almost always a bad idea), so you should see the actual values stored in the string. If you are seeing the literal value of "lastname" output to the variable holding the query, then that means the lastname actually has the value "lastname" contained within it.

Now, if you were instead using parameterized queries (the better practice) to submit queries, then you would not see the values substituted, and you would have to do something like what emoreau mentioned.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 37728014
0
 

Author Closing Comment

by:pointeman
ID: 37732649
I am using parameterized queries although I didn't mention it, oops. It's a new concept and not learning how secure they really are.

I split the points because you all have excellent info. thx
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dynamics CRM Plugin Query TotalRecordCount returning -1 13 60
Reactjs with .NET 3 74
Upgrade code from VS 2010 to VS 2015 7 32
aspx ascx, c# 7 9
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question