anthony
03-18-2004, 12:35 AM
Hello.I have a datagrid which Im trying to add a new record to.
I keep getting the sqlexception that I've echoed through a html span control:
"ERROR: Could not add record, please ensure the fields are correctly filled out"
The thing is I've outputted all the sqlparameter values through a textbox and everything appears fine.Where the problem is I don't know.In the sql
database three of the fields are varchar,one int and one money.
Any help appreciated.
Thank you
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Bindgrid()
End Sub
sub Bindgrid()
Dim constr As String
constr = "server='COMPUTER\VSdotNET'; trusted_connection=true; Database='Franchises'"
Dim sqldbc As New SqlConnection(constr)
sqldbc.Open()
Dim sqladap = New SqlDataAdapter()
sqladap.selectcommand = New SqlCommand("select*from chart", sqldbc)
Dim ds = New DataSet("chart")
sqladap.fillschema(ds, SchemaType.Source, "chart")
sqladap.fill(ds, "chart")
sqldbc.Close()
DataGrid1.DataSource = ds
DataGrid1.Databind
end sub
Sub Addfield_Click(Sender As Object, E As EventArgs)
Dim constr As String
constr = "server='COMPUTER\VSdotNET'; trusted_connection=true; Database='Franchises'"
Dim sqldbc As New SqlConnection(constr)
Dim sqladap = New SqlDataAdapter()
sqladap.selectcommand = New SqlCommand("select*from chart", sqldbc)
Dim MyCommand As sqlCommand
If h_Artist.Value = "" Or h_Genre.Value = "" Or h_Video.Value = "" Or h_Position.Value = "" Or h_Sales.Value = ""
Message.InnerHtml = "ERROR: Null values not allowed for input fields"
Message.Style("color") = "red"
End If
Dim InsertCmd As String = "insert into chart (Genre,Artist,Video,Position,Sales) Values (h_Genre,h_Artist,h_Video,h_Position,h_sales)"
MyCommand = New sqlCommand(InsertCmd,sqldbc)
MyCommand.Parameters.Add(New sqlParameter("@Genre", SqlDbType.NVarChar,30))
MyCommand.Parameters("@Genre").Value = Server.HtmlEncode(h_Genre.Value)
MyCommand.Parameters.Add(New sqlParameter("@Artist",SqlDbType.NVarChar,30))
MyCommand.Parameters("@Artist").Value = Server.HtmlEncode(h_Artist.Value)
MyCommand.Parameters.Add(New sqlParameter("@Video", SqlDbType.NVarChar,30))
MyCommand.Parameters("@Video").Value = Server.HtmlEncode(h_Video.Value)
MyCommand.Parameters.Add(New sqlParameter("@Position",SqlDbType.int))
MyCommand.Parameters("@Position").Value = Server.HtmlEncode(h_Position.Value)
MyCommand.Parameters.Add(New sqlParameter("@Sales", SqlDbType.Money))
MyCommand.Parameters("@Sales").Value = Server.HtmlEncode(h_Sales.Value)
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()
Catch Exp As sqlException
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
Message.Style("color") = "red"
MyCommand.Connection.Close()
End Try
BindGrid()
end sub
</script>
<h.t.m.l>
<head>
</head>
<body>
<center>
<form runat="server">
<ASP:DataGrid id="DataGrid1" runat="server" EnableViewState="False" HeaderStyle-BackColor="#aaaadd" Font-Size="8pt" Font-Name="Verdana" CellSpacing="2" CellPadding="3" BorderColor="black" BackColor="blue" Width="700px" BorderStyle="None" BorderWidth="1px" Font-Names="Verdana" AutoGenerateColumns="False" AllowSorting="True">
<FooterStyle forecolor="black" backcolor="blue"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="black" backcolor="orange"></HeaderStyle>
<PagerStyle horizontalalign="Center" forecolor="black" mode="NumericPages"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="black" backcolor="yellow"></SelectedItemStyle>
<ItemStyle forecolor="black" backcolor="yellow"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="Genre" HeaderText="Genre"></asp:BoundColumn>
<asp:BoundColumn DataField="Artist" HeaderText="Artist"></asp:BoundColumn>
<asp:BoundColumn DataField="Video" HeaderText="Video"></asp:BoundColumn>
<asp:BoundColumn DataField="Position" HeaderText="Position"></asp:BoundColumn>
<asp:BoundColumn DataField="Sales" HeaderText="Sales" dataFormatString="{0:$#,###.##}">
<ItemStyle horizontalalign="Right"></ItemStyle>
</asp:BoundColumn>
</Columns>
</ASP:DataGrid>
<center>
<input id="h_Genre" type="text" runat="server" />
</center>
<center>
<input id="h_Artist" type="text" runat="server" />
</center>
<center>
<input id="h_Video" type="text" runat="server" />
</center>
<center>
<input id="h_Position" type="text" runat="server" />
</center>
<center>
<input id="h_Sales" type="text" runat="server" />
</center>
<center>
<input type="submit" value="Addfield" runat="server" onserverclick="Addfield_Click" />
</center>
<center><span id="Message" runat="server" enableviewstate="false"></span>
</center>
</form>
</center>
</body>
</h.t.m.l>
I keep getting the sqlexception that I've echoed through a html span control:
"ERROR: Could not add record, please ensure the fields are correctly filled out"
The thing is I've outputted all the sqlparameter values through a textbox and everything appears fine.Where the problem is I don't know.In the sql
database three of the fields are varchar,one int and one money.
Any help appreciated.
Thank you
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Bindgrid()
End Sub
sub Bindgrid()
Dim constr As String
constr = "server='COMPUTER\VSdotNET'; trusted_connection=true; Database='Franchises'"
Dim sqldbc As New SqlConnection(constr)
sqldbc.Open()
Dim sqladap = New SqlDataAdapter()
sqladap.selectcommand = New SqlCommand("select*from chart", sqldbc)
Dim ds = New DataSet("chart")
sqladap.fillschema(ds, SchemaType.Source, "chart")
sqladap.fill(ds, "chart")
sqldbc.Close()
DataGrid1.DataSource = ds
DataGrid1.Databind
end sub
Sub Addfield_Click(Sender As Object, E As EventArgs)
Dim constr As String
constr = "server='COMPUTER\VSdotNET'; trusted_connection=true; Database='Franchises'"
Dim sqldbc As New SqlConnection(constr)
Dim sqladap = New SqlDataAdapter()
sqladap.selectcommand = New SqlCommand("select*from chart", sqldbc)
Dim MyCommand As sqlCommand
If h_Artist.Value = "" Or h_Genre.Value = "" Or h_Video.Value = "" Or h_Position.Value = "" Or h_Sales.Value = ""
Message.InnerHtml = "ERROR: Null values not allowed for input fields"
Message.Style("color") = "red"
End If
Dim InsertCmd As String = "insert into chart (Genre,Artist,Video,Position,Sales) Values (h_Genre,h_Artist,h_Video,h_Position,h_sales)"
MyCommand = New sqlCommand(InsertCmd,sqldbc)
MyCommand.Parameters.Add(New sqlParameter("@Genre", SqlDbType.NVarChar,30))
MyCommand.Parameters("@Genre").Value = Server.HtmlEncode(h_Genre.Value)
MyCommand.Parameters.Add(New sqlParameter("@Artist",SqlDbType.NVarChar,30))
MyCommand.Parameters("@Artist").Value = Server.HtmlEncode(h_Artist.Value)
MyCommand.Parameters.Add(New sqlParameter("@Video", SqlDbType.NVarChar,30))
MyCommand.Parameters("@Video").Value = Server.HtmlEncode(h_Video.Value)
MyCommand.Parameters.Add(New sqlParameter("@Position",SqlDbType.int))
MyCommand.Parameters("@Position").Value = Server.HtmlEncode(h_Position.Value)
MyCommand.Parameters.Add(New sqlParameter("@Sales", SqlDbType.Money))
MyCommand.Parameters("@Sales").Value = Server.HtmlEncode(h_Sales.Value)
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()
Catch Exp As sqlException
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
Message.Style("color") = "red"
MyCommand.Connection.Close()
End Try
BindGrid()
end sub
</script>
<h.t.m.l>
<head>
</head>
<body>
<center>
<form runat="server">
<ASP:DataGrid id="DataGrid1" runat="server" EnableViewState="False" HeaderStyle-BackColor="#aaaadd" Font-Size="8pt" Font-Name="Verdana" CellSpacing="2" CellPadding="3" BorderColor="black" BackColor="blue" Width="700px" BorderStyle="None" BorderWidth="1px" Font-Names="Verdana" AutoGenerateColumns="False" AllowSorting="True">
<FooterStyle forecolor="black" backcolor="blue"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="black" backcolor="orange"></HeaderStyle>
<PagerStyle horizontalalign="Center" forecolor="black" mode="NumericPages"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="black" backcolor="yellow"></SelectedItemStyle>
<ItemStyle forecolor="black" backcolor="yellow"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="Genre" HeaderText="Genre"></asp:BoundColumn>
<asp:BoundColumn DataField="Artist" HeaderText="Artist"></asp:BoundColumn>
<asp:BoundColumn DataField="Video" HeaderText="Video"></asp:BoundColumn>
<asp:BoundColumn DataField="Position" HeaderText="Position"></asp:BoundColumn>
<asp:BoundColumn DataField="Sales" HeaderText="Sales" dataFormatString="{0:$#,###.##}">
<ItemStyle horizontalalign="Right"></ItemStyle>
</asp:BoundColumn>
</Columns>
</ASP:DataGrid>
<center>
<input id="h_Genre" type="text" runat="server" />
</center>
<center>
<input id="h_Artist" type="text" runat="server" />
</center>
<center>
<input id="h_Video" type="text" runat="server" />
</center>
<center>
<input id="h_Position" type="text" runat="server" />
</center>
<center>
<input id="h_Sales" type="text" runat="server" />
</center>
<center>
<input type="submit" value="Addfield" runat="server" onserverclick="Addfield_Click" />
</center>
<center><span id="Message" runat="server" enableviewstate="false"></span>
</center>
</form>
</center>
</body>
</h.t.m.l>