Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

Need help with inserting data into a sql server database.

Hello Experts,

I need some assistance with a bit of code that's not working for me.  I am attempting to insert the contents of a text box into a table.  Easy enough, but for some reason I can't get it to work.  I am not getting an error message.  When I click the submit button nothing happens.  Any help would be greatly appreciated.  

I am using Visual Studio 2012, SqlServer 2008, asp.net web forms, and c#.

Here is the c# code I am using:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

namespace saef_web_app
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection("Data Source = TESTMEPRDSQL\\TESTME;Initial Catalog=saef;Integrated Security=True");
        protected void Page_Load(object sender, EventArgs e)
        {      
        }
        
        public void peeps()
        {
            string firstName = txt_emp_fn.Text;
        }
        protected void newEmpBtn_Click(object sender, EventArgs e)
        {
            SqlCommand insertcmd = new SqlCommand("INSERT INTO tbl_emp(emp_fn) VALUES(txt_emp_fn.Text)",conn);
            
            try
            {
                conn.Open();
                insertcmd.ExecuteNonQuery();
                Literal1.Text = "Data inserted successfully";
                conn.Close();
                peeps();
            }
            catch (Exception ex)
            {
                Literal1.Text = ex.Message;
            }
        }
    }
}

Open in new window


Thanks,
J
0
ferguson_jerald
Asked:
ferguson_jerald
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> SqlCommand insertcmd = new SqlCommand("INSERT INTO tbl_emp(emp_fn) VALUES(txt_emp_fn.Text)",conn);
For starters, txt_emp_fn.Text is INSIDE the double-quotes, so the above statement would insert that exact text, and not the contents of txt_emp_fn.   I'm guessing you mean something like this...

 SqlCommand insertcmd = new SqlCommand("INSERT INTO tbl_emp(emp_fn) VALUES(" & txt_emp_fn.Text & ")",conn);

Disclaimer:  I know SQL well, but what I know about C# can be written on the back of a matchbook cover, in very large letters, using a grease pencil.
0
 
Fernando SotoCommented:
Change this line in the code

insertcmd.ExecuteNonQuery();

To this :

Dim effected As Integer = insertcmd.ExecuteNonQuery();

Then tell me what the value of effected is after the line executes.
0
 
ferguson_jeraldAuthor Commented:
Thanks for the quick responses.  

Jim, your suggestion didn't work for the code.  Instead I get the error message that "Operator '&' cannot be applied to operands of type 'string' and 'string'.

Fernando, I changed insertcmd.ExecuteNonQuery(); to Dim effected As Integer = insertcmd.ExecuteNonQuery(); and got the following errors:

'The type or namespace name 'Dim' could not be found (are you missing a using directive or an assembly reference?)'
AND
'The type or namespace 'As' could not be found (are you missing a using directive or an assembly reference?)'

Thanks for the help thus far.  Do you have any other suggestions?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Fernando SotoCommented:
Sorry, I don't know what I was thinking I gave you the code in VB not C#. Change  to this

int effected = insertcmd.ExecuteNonQuery();
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jim's also gave you a VB solution instead of C#. In C# you need to use the '+' sign to concatenate strings:
 SqlCommand insertcmd = new SqlCommand("INSERT INTO tbl_emp (emp_fn) VALUES('" + txt_emp_fn.Text + "')",conn);

Open in new window

NOTE: I also add single quote because you'll need it for text fields.
0
 
ferguson_jeraldAuthor Commented:
Thanks for the feedback.  This seemed to be a tough one to learn with, but it makes sense now.  I need to pay attention to the syntax.  I was able to get it working by implementing your fixes along with a slight change in the code.  Here's what I got to work:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

namespace saef_web_app
{
    public partial class insertTblEmp : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection("Data Source = TESTMEPRDSQL\\TESTME;Initial Catalog=saef;Integrated Security=True");
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void newEmpBtn_Click(object sender, EventArgs e)
        {
            SqlCommand insertcmd = new SqlCommand("INSERT INTO tbl_emp (emp_fn, emp_ln, emp_add, emp_city, emp_state, emp_zip) VALUES('" + txt_emp_fn.Text + "," + txt_emp_ln.Text + ", " + txt_emp_add.Text +", " + txt_emp_city.Text + ", " + txt_emp_state + ", " + txt_emp_zip.Text + "',)", conn);

            try
            {
                conn.Open();
                insertcmd.ExecuteNonQuery();
                Literal1.Text = "Data inserted successfully";
                conn.Close();
            }
            catch (Exception ex)
            {
                Literal1.Text = ex.Message;
            }
        }
    }
}

Open in new window


As you can see I added a couple more fields, and it works great.  Thanks for all of your assistance.
0
 
Fernando SotoCommented:
Not a problem, glad to help.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now