Problem inserting record using visual studio code

method · May 20th, 2005, 05:22 PM

Hi experts. I made a form in visual studio.net that has 5 text boxes and a button. I want this form
to take my input and insert it into sql server db.When i run the script and input data and click the button
the data disapears but when i check the sql server i do not see the new record added!could any one help
me fix this problem.Thanks

Code:

Public Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.DataSet11 = New updateplayer.DataSet1
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT MATCHNO, TEAMNO, PLAYERNO, WON, LOST FROM MATCHES"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "Data Source=(local);Initial Catalog=teniss2;User ID=web;Password=web;"
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO MATCHES(MATCHNO, TEAMNO, PLAYERNO, WON, LOST) VALUES (@MATCHNO, @TEAM" & _
        "NO, @PLAYERNO, @WON, @LOST); SELECT MATCHNO, TEAMNO, PLAYERNO, WON, LOST FROM MA" & _
        "TCHES WHERE (MATCHNO = @MATCHNO)"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MATCHNO", System.Data.SqlDbType.SmallInt, 2, "MATCHNO"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TEAMNO", System.Data.SqlDbType.SmallInt, 2, "TEAMNO"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PLAYERNO", System.Data.SqlDbType.SmallInt, 2, "PLAYERNO"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@WON", System.Data.SqlDbType.SmallInt, 2, "WON"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LOST", System.Data.SqlDbType.SmallInt, 2, "LOST"))
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE MATCHES SET MATCHNO = @MATCHNO, TEAMNO = @TEAMNO, PLAYERNO = @PLAYERNO, WO" & _
        "N = @WON, LOST = @LOST WHERE (MATCHNO = @Original_MATCHNO) AND (LOST = @Original" & _
        "_LOST OR @Original_LOST IS NULL AND LOST IS NULL) AND (PLAYERNO = @Original_PLAY" & _
        "ERNO) AND (TEAMNO = @Original_TEAMNO) AND (WON = @Original_WON OR @Original_WON " & _
        "IS NULL AND WON IS NULL); SELECT MATCHNO, TEAMNO, PLAYERNO, WON, LOST FROM MATCH" & _
        "ES WHERE (MATCHNO = @MATCHNO)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MATCHNO", System.Data.SqlDbType.SmallInt, 2, "MATCHNO"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TEAMNO", System.Data.SqlDbType.SmallInt, 2, "TEAMNO"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PLAYERNO", System.Data.SqlDbType.SmallInt, 2, "PLAYERNO"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@WON", System.Data.SqlDbType.SmallInt, 2, "WON"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LOST", System.Data.SqlDbType.SmallInt, 2, "LOST"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_MATCHNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "MATCHNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_LOST", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "LOST", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PLAYERNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PLAYERNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_TEAMNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TEAMNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_WON", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "WON", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM MATCHES WHERE (MATCHNO = @Original_MATCHNO) AND (LOST = @Original_LOS" & _
        "T OR @Original_LOST IS NULL AND LOST IS NULL) AND (PLAYERNO = @Original_PLAYERNO" & _
        ") AND (TEAMNO = @Original_TEAMNO) AND (WON = @Original_WON OR @Original_WON IS N" & _
        "ULL AND WON IS NULL)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_MATCHNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "MATCHNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_LOST", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "LOST", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PLAYERNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PLAYERNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_TEAMNO", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TEAMNO", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_WON", System.Data.SqlDbType.SmallInt, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "WON", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "MATCHES", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("MATCHNO", "MATCHNO"), New System.Data.Common.DataColumnMapping("TEAMNO", "TEAMNO"), New System.Data.Common.DataColumnMapping("PLAYERNO", "PLAYERNO"), New System.Data.Common.DataColumnMapping("WON", "WON"), New System.Data.Common.DataColumnMapping("LOST", "LOST")})})
        Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox3 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox4 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox5 As System.Web.UI.WebControls.TextBox
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents DataSet11 As updateplayer.DataSet1

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SqlDataAdapter1.InsertCommand.Parameters(0).Value = TextBox1.Text

        SqlDataAdapter1.InsertCommand.Parameters(1).Value = TextBox2.Text

        SqlDataAdapter1.InsertCommand.Parameters(2).Value = TextBox3.Text

        SqlDataAdapter1.InsertCommand.Parameters(2).Value = TextBox4.Text

        SqlDataAdapter1.InsertCommand.Parameters(3).Value = TextBox5.Text

        SqlDataAdapter1.Update(DataSet11)

        Response.Redirect("WebForm1.aspx")




    End Sub
End Class

Code:


<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="updateplayer.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
    <HEAD>
        <title>WebForm1</title>
        <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
        <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
        <meta name="vs_defaultClientScript" content="JavaScript">
        <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
    </HEAD>
    <body MS_POSITIONING="GridLayout">
        <form id="Form1" method="post" runat="server">
            <asp:TextBox id="TextBox1" style="Z-INDEX: 101; LEFT: 32px; POSITION: absolute; TOP: 16px" runat="server"
                Width="161" Height="32px"></asp:TextBox>
            <asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 32px; POSITION: absolute; TOP: 62px" runat="server"
                Width="161" Height="24px"></asp:TextBox>
            <asp:TextBox id="TextBox3" style="Z-INDEX: 103; LEFT: 32px; POSITION: absolute; TOP: 100px" runat="server"
                Width="161" Height="24px"></asp:TextBox>
            <asp:TextBox id="TextBox4" style="Z-INDEX: 104; LEFT: 32px; POSITION: absolute; TOP: 138px" runat="server"
                Width="161" Height="24px"></asp:TextBox>
            <asp:TextBox id="TextBox5" style="Z-INDEX: 105; LEFT: 32px; POSITION: absolute; TOP: 176px" runat="server"
                Width="161" Height="24px"></asp:TextBox>
            <asp:Button id="Button1" style="Z-INDEX: 113; LEFT: 248px; POSITION: absolute; TOP: 24px" runat="server"
                Width="112px" Height="32px" Text="Button"></asp:Button>
        </form>
    </body>
</HTML>

planoie · May 20th, 2005, 08:22 PM

Your code defines some update and insert commands for when you update the data in the dataset. However, you are explicitly setting the parameter values from the textboxes. When you call .Update(), the data adapter is looking at your dataset and seeing no changes. In order for the .Update() method to actually update anything (insert new records or update changed records) it needs to see those changes on the datatables. You can execute the command directly by means of calling the update command's .ExecuteNonQuery() method.

-Peter

method · May 21st, 2005, 01:57 AM

Quote:

quote:Originally posted by planoie
Your code defines some update and insert commands for when you update the data in the dataset. However, you are explicitly setting the parameter values from the textboxes. When you call .Update(), the data adapter is looking at your dataset and seeing no changes. In order for the .Update() method to actually update anything (insert new records or update changed records) it needs to see those changes on the datatables. You can execute the command directly by means of calling the update command's .ExecuteNonQuery() method.

-Peter

Thank u for u reply.Well i am new to visual studio.net 2003 so alot of the code was generated by the system. do u mean i place only one
ExecuteNonQuery() inside on click event handler ?furtheremore,do u think there is problem in increating dataadapter and datasat?I be happy if u explain me more about usage of executenonquery.Thank u and looking forward to u reply.

planoie · May 21st, 2005, 11:06 AM

What you have in your page is one of my biggest problems with Visual Studio. VS is generating a whole lot of code that you don't need at all. If your goal is to insert a record then all you really need is the following:

Dim objCmd As New SqlCommand
objCmd.CommandText = "INSERT INTO MATCHES(MATCHNO, TEAMNO, PLAYERNO, WON, LOST) VALUES (@MATCHNO, @TEAMNO, @PLAYERNO, @WON, @LOST);"
objCmd.Connection = New SqlConnection("Data Source=(local);Initial Catalog=teniss2;User ID=web;Password=web;")
objCmd.Parameters.Add(New SqlParameter("@MATCHNO", SqlDbType.SmallInt)).Value = TextBox1.Text
objCmd.Parameters.Add(New SqlParameter("@TEAMNO", SqlDbType.SmallInt)).Value = TextBox2.Text
objCmd.Parameters.Add(New SqlParameter("@PLAYERNO", SqlDbType.SmallInt)).Value = TextBox3.Text
objCmd.Parameters.Add(New SqlParameter("@WON", SqlDbType.SmallInt)).Value = TextBox4.Text
objCmd.Parameters.Add(New SqlParameter("@LOST", SqlDbType.SmallInt)).Value = TextBox5.Text
objCmd.ExecuteNonQuery()

All that extra stuff that visual studio is putting in there is great and wonderful when you are working with a "live" form such as in a winforms application. When in the web context, all that extra code is just chewing up resources that are loaded every time the page is requested whether or not you actually do anything in it.

-Peter