PDA

Click to See Complete Forum and Search --> : errro trying to insert record to sql server

method
05-20-2005, 06:39 PM
Hi experts. i got this asp.net that supposed to add records to matches table. but when i fill the text boxes
i get the following error.I be happy if some one help me fix this broken code.Thanks



ERROR: Could not add record, please ensure the fields are correctly filled out




<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="VB" runat="server">

Dim MyConnection As SqlConnection

Sub Page_Load(Sender As Object, E As EventArgs)

MyConnection = New SqlConnection("server=(local);database=teniss2;User Id=web;Password=web;")

If Not (IsPostBack)
BindGrid()
End If
End Sub

Sub Addmatches_Click(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyCommand As SqlCommand

If MATCHNO.Value = "" Or TEAMNO.Value = "" Or PLAYERNO.Value = "" Or WON.Value = ""

Message.InnerHtml = "ERROR: Null values not allowed "
Message.Style("color") = "red"
BindGrid()
End If

Dim InsertCmd As String = "insert into matches (MATCHNO, TEAMNO, PLAYERNO ,WON, LOST) values (@LMATCHNO, @LTEAMNO, @LPLAYERNO, @LWON, @LLOST;)"

MyCommand = New SqlCommand(InsertCmd, MyConnection)

MyCommand.Parameters.Add(New SqlParameter("@LMATCHNO", SqlDbType.NVarChar, 11))
MyCommand.Parameters("@LMATCHNO").Value = Server.HtmlEncode(MATCHNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LTEAMNO", SqlDbType.NVarChar, 40))
MyCommand.Parameters("@LTEAMNO").Value = Server.HtmlEncode(TEAMNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LPLAYERNO", SqlDbType.NVarChar, 20))
MyCommand.Parameters("@LPLAYERNO").Value = Server.HtmlEncode(PLAYERNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LWON", SqlDbType.NChar, 12))
MyCommand.Parameters("@LWON").Value = Server.HtmlEncode(WON.Value)

MyCommand.Parameters.Add(New SqlParameter("@LLOST", SqlDbType.NVarChar, 40))
MyCommand.Parameters("@LLOST").Value = Server.HtmlEncode(LOST.Value)



MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()

Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"

End Try

MyCommand.Connection.Close()

BindGrid()
End Sub

Sub BindGrid()

Dim MyCommand As SqlDataAdapter = new SqlDataAdapter("select * from matches", MyConnection)

Dim DS As DataSet = new DataSet()
MyCommand.Fill(DS, "matches")

MyDataGrid.DataSource=DS.Tables("matches").DefaultView
MyDataGrid.DataBind()
End Sub

</script>

<body style="font: 10pt verdana">

<form runat="server">

<h3><font face="Verdana">Inserting A MATCHES RECORD</font></h3>

<table width="95%">
<tr>
<td valign="top">

<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>

</td>
<td valign="top">

<table style="font: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New record:</td>
</tr>
<tr>
<td nowrap>MATCHNO: </td>
<td><input type="text" id="MATCHNO" value="0000" runat="server"></td>
</tr>
<tr>
<td nowrap>TEAMNO: </td>
<td><input type="text" id="TEAMNO" value="0000" runat="server"></td>
</tr>
<tr nowrap>
<td>Playerno: </td>
<td><input type="text" id="PLAYERNO" value="0000" runat="server"></td>
</tr>
<tr>
<td>Won: </td>
<td><input type="text" id="WON" value="0000" runat="server"></td>
</tr>
<tr>
<td>Lost: </td>
<td><input type="text" id="LOST" value="0000" runat="server"></td>
</tr>

<td></td>
<td style="padding-top:15">
<input type="submit" OnServerClick="Addmatches_Click" value="Add matches" runat="server">
</td>
</tr>
<tr>
<td colspan="2" style="padding-top:15" align="center">
<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
</td>
</tr>
</table>

</td>
</tr>
</table>

</form>

</body>
</html>
Cstick
05-20-2005, 08:40 PM
Ok, you are trapping exceptions and outputting a custom message. Change this part of your code:


Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()

Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"

End Try


to this:


Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()

Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
'##########################
'### Change the below line of code.
Message.InnerHtml = Exp.Message
End If
Message.Style("color") = "red"

End Try


And then let us know what the new error says. Otherwise, we can only guess what the cause of your problem is.
method
05-21-2005, 05:51 AM
Many thanks to your reply. i made the chagnes and i get this error :

Line 1:incorrect syntax near ';'

i do not see any error in the top of this page at first line it is import library parts. i be happy if u help me fix this error.Thanks
Cstick
05-21-2005, 10:05 AM
Remove the semicolon ";" from your insert command.

This:

Dim InsertCmd As String = "insert into matches (MATCHNO, TEAMNO, PLAYERNO ,WON, LOST) values (@LMATCHNO, @LTEAMNO, @LPLAYERNO, @LWON, @LLOST;)"


should be this:

Dim InsertCmd As String = "insert into matches (MATCHNO, TEAMNO, PLAYERNO ,WON, LOST) values (@LMATCHNO, @LTEAMNO, @LPLAYERNO, @LWON, @LLOST)"
method
05-21-2005, 06:21 PM
Many thanks to your reply. i tried to run the code u posted but i get the following error when the page loads:



Violation of PRIMARY KEY constraint 'PK_MATCHES'. Cannot insert duplicate key in object 'MATCHES'.
The statement has been terminated.


But the script inserts data into the db. I be happy if u help me remove the above error and also
how i can redirect the users to a new page after submition and telling them that record was succesfully
inserted.Thank u and looking forward to your reply.
Cstick
05-22-2005, 10:38 AM
You got the error because you tryed to insert a value into the MATCHES column and that value already existed in another record. See, you set MATCHES as the primary key when you created the table and primary keys must have a unique value for each record. The point being that it is a unique identifyer that you can use to match the table with data from another table. You have 2 choices, you can either not insert and update to this column, or you can not have it be the primary key. If you don't use this column as the primary key then it is a good idea to create another column to be the primary key.

You have two choices for showing the thank you message. 1 you can redirect the user or 2 you can create a panel on your page and show the panel after the user submits.

Here is the redirect code, replace ThankYouPage.aspx with the page you want to send the user to.:

response.redirect("ThankYouPage.aspx",True)


Here is the the panel code. Basically, you show the form when the page loads and after submission you hide the form and show the thank you message. (I made changes to both VB codes and HTML:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="VB" runat="server">

Dim MyConnection As SqlConnection

Sub Page_Load(Sender As Object, E As EventArgs)

MyConnection = New SqlConnection("server=(local);database=teniss2;User Id=web;Password=web;")

If Not (IsPostBack)
pnlForm.Visible = True
pnlThankYou.Visible = False
BindGrid()
End If
End Sub

Sub Addmatches_Click(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyCommand As SqlCommand

If MATCHNO.Value = "" Or TEAMNO.Value = "" Or PLAYERNO.Value = "" Or WON.Value = ""

Message.InnerHtml = "ERROR: Null values not allowed "
Message.Style("color") = "red"
BindGrid()
End If

Dim InsertCmd As String = "insert into matches (MATCHNO, TEAMNO, PLAYERNO ,WON, LOST) values (@LMATCHNO, @LTEAMNO, @LPLAYERNO, @LWON, @LLOST;)"

MyCommand = New SqlCommand(InsertCmd, MyConnection)

MyCommand.Parameters.Add(New SqlParameter("@LMATCHNO", SqlDbType.NVarChar, 11))
MyCommand.Parameters("@LMATCHNO").Value = Server.HtmlEncode(MATCHNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LTEAMNO", SqlDbType.NVarChar, 40))
MyCommand.Parameters("@LTEAMNO").Value = Server.HtmlEncode(TEAMNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LPLAYERNO", SqlDbType.NVarChar, 20))
MyCommand.Parameters("@LPLAYERNO").Value = Server.HtmlEncode(PLAYERNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LWON", SqlDbType.NChar, 12))
MyCommand.Parameters("@LWON").Value = Server.HtmlEncode(WON.Value)

MyCommand.Parameters.Add(New SqlParameter("@LLOST", SqlDbType.NVarChar, 40))
MyCommand.Parameters("@LLOST").Value = Server.HtmlEncode(LOST.Value)



MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()

Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"

End Try

MyCommand.Connection.Close()

pnlForm.Visible = False
pnlThankYou.Visible = True
BindGrid()
End Sub

Sub BindGrid()

Dim MyCommand As SqlDataAdapter = new SqlDataAdapter("select * from matches", MyConnection)

Dim DS As DataSet = new DataSet()
MyCommand.Fill(DS, "matches")

MyDataGrid.DataSource=DS.Tables("matches").DefaultView
MyDataGrid.DataBind()
End Sub

</script>

<body style="font: 10pt verdana">

<form runat="server">

<h3><font face="Verdana">Inserting A MATCHES RECORD</font></h3>

<div id="pnlForm" runat="server">
<table width="95%">
<tr>
<td valign="top">

<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>

</td>
<td valign="top">

<table style="font: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New record:</td>
</tr>
<tr>
<td nowrap>MATCHNO: </td>
<td><input type="text" id="MATCHNO" value="0000" runat="server"></td>
</tr>
<tr>
<td nowrap>TEAMNO: </td>
<td><input type="text" id="TEAMNO" value="0000" runat="server"></td>
</tr>
<tr nowrap>
<td>Playerno: </td>
<td><input type="text" id="PLAYERNO" value="0000" runat="server"></td>
</tr>
<tr>
<td>Won: </td>
<td><input type="text" id="WON" value="0000" runat="server"></td>
</tr>
<tr>
<td>Lost: </td>
<td><input type="text" id="LOST" value="0000" runat="server"></td>
</tr>

<td></td>
<td style="padding-top:15">
<input type="submit" OnServerClick="Addmatches_Click" value="Add matches" runat="server">
</td>
</tr>
<tr>
<td colspan="2" style="padding-top:15" align="center">
<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
</td>
</tr>
</table>

</td>
</tr>
</table>
</div>
<div id="pnlThankYou" runat="server">
<p>You have successfully inserted a new Matches record!</p>
</div>
</form>

</body>
</html>
method
05-22-2005, 04:22 PM
You got the error because you tryed to insert a value into the MATCHES column and that value already existed in another record. See, you set MATCHES as the primary key when you created the table and primary keys must have a unique value for each record. The point being that it is a unique identifyer that you can use to match the table with data from another table. You have 2 choices, you can either not insert and update to this column, or you can not have it be the primary key. If you don't use this column as the primary key then it is a good idea to create another column to be the primary key.

You have two choices for showing the thank you message. 1 you can redirect the user or 2 you can create a panel on your page and show the panel after the user submits.

Here is the redirect code, replace ThankYouPage.aspx with the page you want to send the user to.:

response.redirect("ThankYouPage.aspx",True)


Here is the the panel code. Basically, you show the form when the page loads and after submission you hide the form and show the thank you message. (I made changes to both VB codes and HTML:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="VB" runat="server">

Dim MyConnection As SqlConnection

Sub Page_Load(Sender As Object, E As EventArgs)

MyConnection = New SqlConnection("server=(local);database=teniss2;User Id=web;Password=web;")

If Not (IsPostBack)
pnlForm.Visible = True
pnlThankYou.Visible = False
BindGrid()
End If
End Sub

Sub Addmatches_Click(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyCommand As SqlCommand

If MATCHNO.Value = "" Or TEAMNO.Value = "" Or PLAYERNO.Value = "" Or WON.Value = ""

Message.InnerHtml = "ERROR: Null values not allowed "
Message.Style("color") = "red"
BindGrid()
End If

Dim InsertCmd As String = "insert into matches (MATCHNO, TEAMNO, PLAYERNO ,WON, LOST) values (@LMATCHNO, @LTEAMNO, @LPLAYERNO, @LWON, @LLOST;)"

MyCommand = New SqlCommand(InsertCmd, MyConnection)

MyCommand.Parameters.Add(New SqlParameter("@LMATCHNO", SqlDbType.NVarChar, 11))
MyCommand.Parameters("@LMATCHNO").Value = Server.HtmlEncode(MATCHNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LTEAMNO", SqlDbType.NVarChar, 40))
MyCommand.Parameters("@LTEAMNO").Value = Server.HtmlEncode(TEAMNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LPLAYERNO", SqlDbType.NVarChar, 20))
MyCommand.Parameters("@LPLAYERNO").Value = Server.HtmlEncode(PLAYERNO.Value)

MyCommand.Parameters.Add(New SqlParameter("@LWON", SqlDbType.NChar, 12))
MyCommand.Parameters("@LWON").Value = Server.HtmlEncode(WON.Value)

MyCommand.Parameters.Add(New SqlParameter("@LLOST", SqlDbType.NVarChar, 40))
MyCommand.Parameters("@LLOST").Value = Server.HtmlEncode(LOST.Value)



MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()

Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"

End Try

MyCommand.Connection.Close()

pnlForm.Visible = False
pnlThankYou.Visible = True
BindGrid()
End Sub

Sub BindGrid()

Dim MyCommand As SqlDataAdapter = new SqlDataAdapter("select * from matches", MyConnection)

Dim DS As DataSet = new DataSet()
MyCommand.Fill(DS, "matches")

MyDataGrid.DataSource=DS.Tables("matches").DefaultView
MyDataGrid.DataBind()
End Sub

</script>

<body style="font: 10pt verdana">

<form runat="server">

<h3><font face="Verdana">Inserting A MATCHES RECORD</font></h3>

<div id="pnlForm" runat="server">
<table width="95%">
<tr>
<td valign="top">

<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>

</td>
<td valign="top">

<table style="font: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New record:</td>
</tr>
<tr>
<td nowrap>MATCHNO: </td>
<td><input type="text" id="MATCHNO" value="0000" runat="server"></td>
</tr>
<tr>
<td nowrap>TEAMNO: </td>
<td><input type="text" id="TEAMNO" value="0000" runat="server"></td>
</tr>
<tr nowrap>
<td>Playerno: </td>
<td><input type="text" id="PLAYERNO" value="0000" runat="server"></td>
</tr>
<tr>
<td>Won: </td>
<td><input type="text" id="WON" value="0000" runat="server"></td>
</tr>
<tr>
<td>Lost: </td>
<td><input type="text" id="LOST" value="0000" runat="server"></td>
</tr>

<td></td>
<td style="padding-top:15">
<input type="submit" OnServerClick="Addmatches_Click" value="Add matches" runat="server">
</td>
</tr>
<tr>
<td colspan="2" style="padding-top:15" align="center">
<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
</td>
</tr>
</table>

</td>
</tr>
</table>
</div>
<div id="pnlThankYou" runat="server">
<p>You have successfully inserted a new Matches record!</p>
</div>
</form>

</body>
</html>


I am very thankfull to u for your nice and helpfull replies. Well i got a db that u can see its reletionship in this url :http://i5.photobucket.com/albums/y180/method007/dbreletionship.jpg

i want be able to insert records to each of the tables encluding the matches table .you are right i will not allow the users to update the primary keys for maches table since i want to keep the primary key. I be happy if u help me how i can keep this primary key and be able to add records to matches table.Furthermore, i will be realy thank full if u have a look at my update record problem that i posted in this forum since i realy need help there too. Thank u and looking forward to your reply.

Best wishes