Hi I am very new to both ASP and SQL but I am having a problem which doesnt give off any signals as to what is actually wrong.
I am creating a simple webpage using an Access Database as the datasource along with a GridView and DetailsView in order to insert/delete/edit and show information from the database neatly on the page.
Unfortunately once setup, the form only adds information from 3 of the 5 fields into the database, leaving me with blanks in the first two columns, I have no idea why this is happening and I was hoping someone could help me;
This is my source code of the Commands the form is using.
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/db1.mdb"
DeleteCommand="DELETE FROM [table] WHERE [No] = ? AND [Reg No] = ? AND [Forename] = ? AND [Surname] = ? AND [Course] = ? AND [Entry] = ?"
InsertCommand="INSERT INTO [table] ([Reg No], [Forename], [Surname], [Course], [Entry]) VALUES ([Reg No], [Forename], [Surname], [Course], [Entry])"
SelectCommand="SELECT * FROM [table]"
UpdateCommand="UPDATE [table] SET [Reg No] = ?, [Forename] = ?, [Surname] = ?, [Course] = ?, [Entry] = ? WHERE [No] = ? AND [Reg No] = ? AND [Forename] = ? AND [Surname] = ? AND [Course] = ? AND [Entry] = ?"
I am at a loss currently and would appreciate some help[:)]
Apologies, the SELECT seems to work fine but the INSERT is what seems to be causing the problem, though there appears to be no fault with the statement;
well, Access is not my forte, however, I don't believe you can call a column "no" because that is a reserved word. Perhaps you should rename your columns to something a bit more obfuscated so that you aren't using a reserved word.
richasr87
Member
1 Points
33 Posts
DetailsView/GridView Forms producing blank fields in Access Database.
Oct 30, 2007 03:29 PM|LINK
Hi I am very new to both ASP and SQL but I am having a problem which doesnt give off any signals as to what is actually wrong.
I am creating a simple webpage using an Access Database as the datasource along with a GridView and DetailsView in order to insert/delete/edit and show information from the database neatly on the page.
Unfortunately once setup, the form only adds information from 3 of the 5 fields into the database, leaving me with blanks in the first two columns, I have no idea why this is happening and I was hoping someone could help me;
This is my source code of the Commands the form is using.
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/db1.mdb"
DeleteCommand="DELETE FROM [table] WHERE [No] = ? AND [Reg No] = ? AND [Forename] = ? AND [Surname] = ? AND [Course] = ? AND [Entry] = ?"
InsertCommand="INSERT INTO [table] ([Reg No], [Forename], [Surname], [Course], [Entry]) VALUES ([Reg No], [Forename], [Surname], [Course], [Entry])"
SelectCommand="SELECT * FROM [table]"
UpdateCommand="UPDATE [table] SET [Reg No] = ?, [Forename] = ?, [Surname] = ?, [Course] = ?, [Entry] = ? WHERE [No] = ? AND [Reg No] = ? AND [Forename] = ? AND [Surname] = ? AND [Course] = ? AND [Entry] = ?"
I am at a loss currently and would appreciate some help[:)]
jpuckett
Contributor
5802 Points
1036 Posts
Re: DetailsView/GridView Forms producing blank fields in Access Database.
Oct 30, 2007 05:24 PM|LINK
Im confused at to what is " the form only adds information from 3 of the 5 fields into the database " means.
Does this mean your SELECT works fine and you can display all 5 columns but the insert fails?
richasr87
Member
1 Points
33 Posts
Re: DetailsView/GridView Forms producing blank fields in Access Database.
Oct 30, 2007 07:21 PM|LINK
Apologies, the SELECT seems to work fine but the INSERT is what seems to be causing the problem, though there appears to be no fault with the statement;
DataFile="~/App_Data/db1.mdb" DeleteCommand="DELETE FROM [table] WHERE [No] = ?" InsertCommand="INSERT INTO [table] ([RegNo], [Surname], [Course], [Entry]) VALUES (?, ?, ?, ?)" SelectCommand="SELECT [No], [RegNo], [Surname], [Course], [Entry] FROM [table]"
UpdateCommand="UPDATE [table] SET [RegNo] = ?, [Surname] = ?, [Course] = ?, [Entry] = ? WHERE [No] = ?">That is what Web Developer has generated, I'm not coding this myself exactly as I'm learning as I go along so to speak.
jpuckett
Contributor
5802 Points
1036 Posts
Re: DetailsView/GridView Forms producing blank fields in Access Database.
Oct 30, 2007 08:31 PM|LINK
alrighty, do me a favor and post your entire aspx and your code behind so I can peek at it.
richasr87
Member
1 Points
33 Posts
Re: DetailsView/GridView Forms producing blank fields in Access Database.
Oct 30, 2007 08:36 PM|LINK
Ok this is my source code from the aspx page, this is all Web Developer has created for me.
<%
@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><
script runat="server"> </script><
html xmlns="http://www.w3.org/1999/xhtml"><
head runat="server"> <title>Untitled Page</title></
head><
body> <form id="form1" runat="server"> <div>
</div> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/db1.mdb" DeleteCommand="DELETE FROM [table] WHERE [No] = ?" InsertCommand="INSERT INTO [table] ([RegNo], [Surname], [Course], [Entry]) VALUES (?, ?, ?, ?)" SelectCommand="SELECT [No], [RegNo], [Surname], [Course], [Entry] FROM [table]"
UpdateCommand="UPDATE [table] SET [RegNo] = ?, [Surname] = ?, [Course] = ?, [Entry] = ? WHERE [No] = ?"> <DeleteParameters> <asp:parameter Name="No" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:parameter Name="RegNo" Type="String" /> <asp:parameter Name="Surname" Type="String" /> <asp:parameter Name="Course" Type="String" /> <asp:parameter Name="Entry" Type="String" /> <asp:parameter Name="No" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:parameter Name="No" Type="Int32" /> <asp:parameter Name="RegNo" Type="String" /> <asp:parameter Name="Surname" Type="String" /> <asp:parameter Name="Course" Type="String" /> <asp:parameter Name="Entry" Type="String" /> </InsertParameters> </asp:AccessDataSource> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="No" DataSourceID="AccessDataSource1" Height="50px" Width="125px"> <Fields> <asp:boundfield DataField="No" HeaderText="No" InsertVisible="False" ReadOnly="True" SortExpression="No"></asp:boundfield> <asp:boundfield DataField="RegNo" HeaderText="RegNo" SortExpression="RegNo"> </asp:boundfield> <asp:boundfield DataField="Entry" HeaderText="Entry" SortExpression="Entry"></asp:boundfield> <asp:boundfield DataField="Course" HeaderText="Course" SortExpression="Course"></asp:boundfield> <asp:boundfield DataField="Surname" HeaderText="Surname" SortExpression="Surname"> </asp:boundfield> <asp:commandfield ShowInsertButton="True"></asp:commandfield> </Fields> </asp:DetailsView> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="No" DataSourceID="AccessDataSource1"> <Columns> <asp:boundfield DataField="No" HeaderText="No" InsertVisible="False" ReadOnly="True" SortExpression="No"></asp:boundfield> <asp:boundfield DataField="RegNo" HeaderText="RegNo" SortExpression="RegNo"> </asp:boundfield> <asp:boundfield DataField="Entry" HeaderText="Entry" SortExpression="Entry"></asp:boundfield> <asp:boundfield DataField="Course" HeaderText="Course" SortExpression="Course"></asp:boundfield> <asp:boundfield DataField="Surname" HeaderText="Surname" SortExpression="Surname"> </asp:boundfield> </Columns> </asp:GridView> </form></
body></
html> <script runat="server"> </script> <form id=form1 runat="server"> <div></div></form>jpuckett
Contributor
5802 Points
1036 Posts
Re: DetailsView/GridView Forms producing blank fields in Access Database.
Oct 30, 2007 08:49 PM|LINK
well, Access is not my forte, however, I don't believe you can call a column "no" because that is a reserved word. Perhaps you should rename your columns to something a bit more obfuscated so that you aren't using a reserved word.