Data not get inserted even insert query execute successfully.

Question

0.00/5 (No votes)

See more:

Hi all

i made one window base application in VS2008 (VB.net).
i made one simple code where i can insert data.and after inserting i can see that data on grid.when i am inserting data insert query runs perfect.after that for debugging purpose i fire select query and get result in dataset and its show data too.but when go into database my inserted data not showing there.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con_str As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
        Dim con As SqlConnection = New SqlConnection(con_str)
        Dim cmd, cmd2 As SqlCommand
        Dim adp As New SqlDataAdapter
        Dim ds As New DataSet

        With adp
            .SelectCommand = New SqlCommand
            With .SelectCommand
                .CommandType = CommandType.StoredProcedure
                .CommandText = "test_insert"
                If con.State = ConnectionState.Closed Then
                    con.Open()

                End If
                .Parameters.Add("cust_name", SqlDbType.VarChar).Value = "hello"
                .Connection = con
            End With
            .Fill(ds, "insert")
        End With


        With adp
            .SelectCommand = New SqlCommand
            With .SelectCommand
                .CommandType = CommandType.StoredProcedure
                .CommandText = "test_select"
                If con.State = ConnectionState.Closed Then
                    con.Open()

                End If
                .Connection = con
            End With
            .Fill(ds, "select")
        End With





    End Sub
End Class

Table test

cust_id	numeric(18, 0)
cust_name	varchar(50)

My Store Procedures

SQL

ALTER PROCEDURE test_insert
    @cust_name varchar(50)
AS
    insert into test(cust_name) values (@cust_name)
    RETURN

SQL

ALTER PROCEDURE test_select
AS
    select * from test
    RETURN

Posted 30-Mar-10 21:02pm

RollerCoster

Updated 30-Mar-10 22:59pm

PSK_

v2

Add a Solution

3 solutions

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)

bcozican · Answer 1 · 2010-03-31T03:13:00

Solution 1

This might not be the problem but I noticed your connection string points to a database file called Database1.mdf instead of a database on a server.

Might the problem be that you're looking in a database for the data but the application inserted it in the database file?

Posted 31-Mar-10 3:13am

bcozican

RollerCoster · Answer 2 · 2010-03-31T20:17:00

Solution 2

well u might be a right.because yesterday i tried to put hard path/direct path and it works fine.in fact i got my old data too.i gave path of "bin/debug".so i check option of "Copy To Output Directory" but i selected already "Copy always" option.but still problem not solved.

Posted 31-Mar-10 20:17pm

RollerCoster

radix3 · Answer 3 · 2010-04-01T02:28:00

once u have defined the parameters of the insert statement in ur project add this small line
cmd.ExecuteNonQuery()
and u can see the data in the database

Try this out and see whether it works or not

i have tried the same in ASP.NET this is a dummy insert statement and works just fine i hope it helps u my friend

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
public static string getconnection()
{
return @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Registration form\ASP.NET.mdb;Persist Security Info=False";
}
protected void Button1_Click(object sender, EventArgs e)
{

OleDbConnection con = new OleDbConnection();
con.ConnectionString = getconnection();
con.Open();
string command = "insert into mytable values(@maiid,@name,@password)";
OleDbCommand cmd = new OleDbCommand(command, con);
cmd.Parameters.AddWithValue("@mailid", txtmailid.Text);
cmd.Parameters.AddWithValue("@name", txtname.Text);
cmd.Parameters.AddWithValue("@password", txtpassword.Text);
int x= cmd.ExecuteNonQuery();
if (x != 0)
{
// Redirecting to some other page u can use a Messagebox
Response.Redirect("Login.aspx");
}

con.Close();
}
}

This is a C# code if u look at it correctly u will be able to convert it into VB.NET code as only the syntax differs the remaining part is same..

Do Rate my answer once u find it useful

Thanks & Regards
Radix :rose: