Problem inserting record using visual studio code behind asp.net

[method]

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


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
'
.
.
.
'
'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

[Cstick]

The problem with your code was that you setup your SqlDataAdapter to update based on a dataset. The way you had it setup, you would use the "Update" method of your SqlDataApapter and pass it a dataset. .Net would then compare the rows in the dataset with the rows in your Database and perform insert, update, and deletes so that the two matched. But, you are simply inserting a record, so you can use the code below. Otherwise you can use your code by first passing the textbox values into a dataset and passing your SqlDataAdapter the dataset as I described earlier.


Imports System.Data.SqlClient
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()
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

'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
Try
Dim myConn as New SqlConnection("Data Source=(local);Initial Catalog=teniss2;User ID=web;Password=web;")
Dim myCmd As New SqlCommand("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)", myConn)
myCmd.Parameters.Add("@MATCHNO", TextBox1.Text)
myCmd.Parameters.Add("@TEAMNO", TextBox2.Text)
myCmd.Parameters.Add("@PLAYERNO", TextBox3.Text)
myCmd.Parameters.Add("@WON", TextBox4.Text)
myCmd.Parameters.Add("@LOST", TextBox5.Text)

myConn.Open()
myCmd.ExecuteNonQuery
myConn.Close()

myCmd.Dispose()
MyConn.Dispose()
Catch Ex As Exception
Response.Write(Ex.Message)
Exit Sub
End Try

Response.Redirect("WebForm1.aspx")
End Sub
End Class

[method]

MAny thanks to your reply. Well i think your way is better way to insert the data. so u mean i do not have to insert data adapter and data set in to my form design ? since i am new to visual studio i was thinking i need to define dataset and data adapter just like i do datagrid. Any ways i be happy if u help me understand the diffrence.Thanks

[Cstick]

If you are just doing something simple like inserting 5 textbox values into a table, then it would be overkill for you to create a dataset, fill the dataset with the values and then use a SqlDataAdapter.

In some cases it may benifit you to use a dataset however. For example if you fill a datagrid with a dataset and have the user makes changes and additions to the data in the datagrid, then you can take the changed data, put it into a dataset, create your Update, Insert, and Delete commands, the pass the SqlDataAdapter your dataset and it will automatically delete rows from the db that were deleted in the datagrid, insert rows into the db that were inserted into the datagrid, and update rows on the db that where updated in the datagrid. So, sometimes it may benifit you to use a dataset.

[method]

Many thanks to your helpfull explaination. i tried to auto increment primary key playerno. i excluded the playerno and also the where clause. when i run the code i see the palayrno getting incremented from 113 not from zero/one!! Could you help me fix this problem and how to redirec to new page after insert?Furthermore, i be thankful to u if u have a look at my update record problem i posted in this section.Thanks


Imports System.Data.SqlClient
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()

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 TextBox6 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox7 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox8 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox9 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox10 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox11 As System.Web.UI.WebControls.TextBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label6 As System.Web.UI.WebControls.Label
Protected WithEvents Label7 As System.Web.UI.WebControls.Label
Protected WithEvents Label8 As System.Web.UI.WebControls.Label
Protected WithEvents Label9 As System.Web.UI.WebControls.Label
Protected WithEvents Label10 As System.Web.UI.WebControls.Label
Protected WithEvents Label11 As System.Web.UI.WebControls.Label

'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
'Put user code to initialize the page here
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim myConn As New SqlConnection("Data Source=(local);Initial Catalog=teniss2;User ID=web;Password=web;")

Dim myCmd As New SqlCommand("INSERT INTO PLAYERS(NAME,INITIALS,BIRTH_DATE,SEX,JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO) VALUES (@NAME, @INITI" & _
"ALS, @BIRTH_DATE, @SEX, @JOINED,@STREET,@HOUSENO,@POSTCODE,@TOWN,@PHONENO,@LEAGUENO); SELECT NAME,INITIALS,BIRTH_DATE,SEX,JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO FROM PL" & _
"AYERS ", myConn)
myCmd.Parameters.Add("@NAME", TextBox1.Text)
myCmd.Parameters.Add("@INITIALS", TextBox2.Text)
myCmd.Parameters.Add("@BIRTH_DATE", TextBox3.Text)
myCmd.Parameters.Add("@SEX", TextBox4.Text)
myCmd.Parameters.Add("@JOINED", TextBox5.Text)
myCmd.Parameters.Add("@STREET", TextBox6.Text)
myCmd.Parameters.Add("@HOUSENO", TextBox7.Text)
myCmd.Parameters.Add("@POSTCODE", TextBox8.Text)
myCmd.Parameters.Add("@TOWN", TextBox9.Text)
myCmd.Parameters.Add("@PHONENO", TextBox10.Text)
myCmd.Parameters.Add("@LEAGUENO", TextBox11.Text)

myConn.Open()
myCmd.ExecuteNonQuery()
myConn.Close()

myCmd.Dispose()
myConn.Dispose()
Catch Ex As Exception
Response.Write(Ex.Message)
Exit Sub
End Try
End Sub
End Class

[CardboardHammer]

... i tried to auto increment primary key playerno. i excluded the playerno and also the where clause. when i run the code i see the palayrno getting incremented from 113 not from zero/one!! ...

Deleting the records from a table does not cause it to reset the autoincrement back to the initial seed. To reset to to the initial seed, first drop the identity column, then recreate it.

[method]

Deleting the records from a table does not cause it to reset the autoincrement back to the initial seed. To reset to to the initial seed, first drop the identity column, then recreate it.
well initially i did not set the identity ,after inserting a few records i saw that there they increment from 113. i hate some data already before setting the identity.