Data is not getting inserted in to the database

Question

User-785566496 posted

Hi experts,

I recently started working in asp.net c#. right now working on a sample project but having some trouble.  When i insert the data into database nothing gets inserted except for that table's primary key without any error. here is the code. 

database table

CREATE TABLE [dbo].[product] (
[p_id] INT IDENTITY (1, 1) NOT NULL,
[p_name] VARCHAR (50) NULL,
[description] VARCHAR (MAX) NULL,
[datetime] VARCHAR (50) NULL,
[image] VARCHAR (50) NULL,
[p_price] VARCHAR (50) NULL,
[quantity] INT NULL,
[category] VARCHAR (50) NULL,
CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED ([p_id] ASC)
);

asp.net design page

<section id="main-content" class=" ">
<section class="wrapper main-wrapper row" style=''>
<div class='col-xs-12'>
<div class="page-title">

<div class="pull-left">
<!-- PAGE HEADING TAG - START -->
<h1 class="title">Add a Product</h1>
<!-- PAGE HEADING TAG - END -->
</div>

<div class="pull-right hidden-xs">
<ol class="breadcrumb">
<li>
<a href="index.aspx"><i class="fa fa-home"></i>Home</a>
</li>
<li>
<a href="Products.aspx">Products</a>
</li>
<li class="active">
<strong>Add Product</strong>
</li>
</ol>
</div>

</div>
</div>
<div class="clearfix"></div>
<!-- MAIN CONTENT AREA STARTS -->
<div class="col-xs-12">
<section class="box ">
<header class="panel_header">
<h2 class="title pull-left">Basic Info</h2>
<div class="actions panel_actions pull-right">
<a class="box_toggle fa fa-chevron-down"></a>
<a class="box_setting fa fa-cog" data-toggle="modal" href="Settings.aspx"></a>
<a class="box_close fa fa-times"></a>
</div>
</header>
<div class="content-body">
<div class="row">
<div class="col-xs-12 col-sm-9 col-md-8">

<div class="form-group">
<label>Name</label>
<asp:TextBox runat="server" CssClass="form-control" ID="t1"></asp:TextBox>
</div>
<div class="form-group">
<label class="form-label">Product Name</label>
<span class="desc"></span>
<div class="controls">

</div>
</div>

<div class="form-group">
<label class="form-label">Image</label>
<span class="desc"></span>
<div class="controls">
<p>
<label for="image">
No Image Selected<br />
<asp:Button runat="server" ID="browseImg" CssClass="btn btn-primary" Text="Browse" /></label>
</p>
</div>
</div>

<div class="form-group">
<label class="form-label" for="description">Description</label>
<span class="desc"></span>
<div class="controls">
<asp:TextBox runat="server" CssClass="form-control" ID="t2"></asp:TextBox>
</div>
</div>
<div class="form-group">
<label class="form-label" for="price">Price</label>
<span class="desc"></span>
<div class="controls">
<asp:TextBox runat="server" CssClass="form-control" ID="t3"></asp:TextBox>
</div>
</div>
<div class="form-group">
<label class="form-label" for="qty">Quantity</label>
<span class="desc"></span>
<div class="controls">
<asp:TextBox runat="server" CssClass="form-control" ID="t4" ></asp:TextBox>
</div>
</div>
<div class="form-group">
<label class="form-label" for="category">Category</label>
<span class="desc"></span>
<div class="controls">
<asp:TextBox runat="server" CssClass="form-control" ID="t5"></asp:TextBox>
</div>
</div>
<div class="col-xs-12 col-sm-9 col-md-8 padding-bottom-30">
<div class="text-left">
<asp:LinkButton runat="server" CssClass="btn btn-primary" ID="saveButton" Text="Add" OnClick="saveButton_Click" ></asp:LinkButton>
<asp:LinkBUtton runat="server" CssClass="btn btn-primary" id="deleteButton" text="delete" OnClick="deleteButton_Click"></asp:LinkBUtton>
</div>
</div>
<div>
<asp:Label runat="server" class="form-label" ID="status" Text="" /></div>
</div>
</div>

</div>
</section>
</div>
<div>
</div>

</section>

</section>

code behind button

protected void saveButton_Click(object sender, EventArgs e)
{
DateTime dt = new DateTime();
dt = DateTime.Now;
string mydate = dt.ToString();
con.Open();
string str = "INSERT INTO product([p_name],[description],[datetime],[image],[p_price],[quantity],[category])VALUES('"+t1.Text+"','"+t2.Text+"','"+mydate+"','"+null+"','"+t3.Text+"','"+t4.Text+"','"+t5.Text+"')";
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
}

i've been through so many tutorials and lectures but nothing is of real help until now.

please take a look at this and tell me where I am going wrong and why the garbage values are getting inserted in to the database. I expect a quick responose brothers. it'll be a lot of help.

Thanks

Answer 1

User753101303 posted

Hi,

You could use https://docs.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour an inspect for example t1.Text, t2.Text etc... to see if it is "blank" (edit : or just see which SQL statement you finally have in your "str" string).. Event the [datetime] column is not populated ?

Not directled related but once past the start (or maybe right now to start with good habits ?) see http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

Using a quey with parameters that are then populated before being executed is preferred over building yourself a SQL string (for example once this code works, you'll see that it breaks if you enter a ' character inside a textbox.

Answer 2

User-785566496 posted

Thanks for reply.

But I am not getting any error at any time. its just that the data is not getting in!

Answer 3

User753101303 posted

Yes as I told this is something for a later time.

For now based on what you described it seems your textboxes are "blank". Do you clear them in Page_Load maybe ? So as suggested first you should check which values you have in your textboxes or your str string so that you can understand which SQL statement runs. For now I suspect that your SQL statement just provide blank values for all columns expect maybe one or two.

Answer 4

User-785566496 posted

I am taking values on run time. when i run the project using F5 key then i enter the values in text boxes and nothing goes in the database.

Answer 5

User475983607 posted

Learn how to use the Visual Studio debugger to check your logic as empty table field indicate issues outside the posted code.  Otherwise, post enough code to reproduce the stated issue.

https://msdn.microsoft.com/en-us/library/y740d9d3.aspx

Answer 6

User753101303 posted

Could you clarify if no row at all is inserted into the db or if you have a new row with mostly blank columns ?

What I'm trying to tell is that you shouldn't stop at seeing that it doesn't work but to see what actually happens. With https://docs.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour :

• you can put a "breakpoint" on a line of code (such as when your SQL query runs)
  - you can then launch your app in debug mode and proceed. It will stop when the "breakpoint" is reached
  - you can then check the "str" value to see the SQL statement you are about to run and likely see it doesn't include expected values. If confirmed you can then check it is because textboxes are empty and then see why (cleared somewhere else in your code ?).

Or change your code to show str in a Label if you prefer. Do you see an insert statements with mostly '' values ? If yes it would confirm that your textboxes are empty (maybe cleared somewhere else in your code ?)

Answer 7

User-785566496 posted

a row gets inserted but with empty columns.

Answer 8

User753101303 posted

And textbox values are not show blank when the page is shown again ? As I told multiple times it looks like your textboxes are cleared at some point. Don't you have code such as t1.Text=""; somewhere that would clear your textboxes ?

If not, with the debugger you could see if the value for t1.Text is ok when Page_Load starts and check if it is really later "" in  saveButton_Click. You'll then know if it is cleared between Page_Load and saveButton_Click or if the problem happens earlier and progressively narrow down the problem.

Answer 9

User475983607 posted

a row gets inserted but with empty columns.

Debugging code is not rocket science but it is a learned art.  Take time to learn how to use the debugging features in Visual Studio (previous links) before moving forward.  

Answer 10

User-785566496 posted

And textbox values are not show blank when the page is shown again ? As I told multiple times it looks like your textboxes are cleared at some point. Don't you have code such as t1.Text=""; somewhere that would clear your textboxes ?

If not, with the debugger you could see if the value for t1.Text is ok when Page_Load starts and check if it is really later "" in  saveButton_Click. You'll then know if it is cleared between Page_Load and saveButton_Click or if the problem happens earlier and progressively narrow down the problem.

i don not have t1.text="" anywhere in the code. its right on your screen you can see that.

Answer 11

User-785566496 posted

but when i insert the data using a string and pass it directly into the query then it gives data in table. but on the other hand when i insert using pure query it does not.

Answer 12

User475983607 posted

arral

but when i insert the data using a string and pass it directly into the query then it gives data in table. but on the other hand when i insert using pure query it does not.

You tested the SQL and it works. Great!  Now debug the code behind.  Set a breakpoint in every event handler and single step through the code to find the bug.

Frankly, I don't see anything in the posted code that looks wrong.  I assume the bug is code that you have not posted.  However, the posted code does have a code smell and you should look into using a parameter query rather than string built SQL but refactoring can be handled at a later time.  Your first step is learning how to use the Visual Studio debugger and single step through the code behind.

Answer 13

User-785566496 posted

because there is no such code that i didn't mention here. it is the only code on a single button yet.

Answer 14

User-785566496 posted

I think I should make it again from start. may this time there would be no error

Answer 15

User475983607 posted

There's not form element so you must have a Master Page?  Perhaps client side script we cannot see as well.  The debugger will show the values submitted to the server.  There's also the browser's dev tools (F12).

Try changing the link button to an ASP button.

Answer 16

User347430248 posted

Hi arral,

I try to make a test with your code and simply try to print the query to check whether it contains the value from textbox or not. Below is my testing result.

You can see that values are added to the query from text boxes. 

So now, You can try to check further with your database.

You can try to execute this query directly in SQL and check whether it insert the data or not.

If it inserts the data then something is wrong with your code in datbase connection.

Try to check it and let us know about the result.

We will try to provide you further suggestions to solve the issue.

Regards

Deepak