GridView & SQLdatasourceproblems
-
I am having problems getting this to work. I have it where it inserts the data from some text boxes above. So It Inserts and Deletes just fine. When it comes to updating and search for in between dates I have problems. Ok in the dialog for the update command it has the command for updating the fields. So when you click update in the grid view where are the commands that make it update? It will not update. When you change something and click UPDATE it just goes right back to what it was. Also how do I search for between dates? I have two text boxes and set the SQLdatasource1.SelectCommand = String.Format("SELECT * From [Clipboard] WHERE [Date] Between {0} AND {1}", txtBeginningDate.Text, txtEndDate.Text); Now when you enter 05-05-2008 in the text boxes and hit my SEARCH button it says: Failed to convert nvarchar to int. Where is it converting to INT? I have it set to nvarchar??? BTW I am using MVS 2008 Pro, and I am not writing a lot of these commands. It is generated for me but I can't seem to get it to do those things.
-
I am having problems getting this to work. I have it where it inserts the data from some text boxes above. So It Inserts and Deletes just fine. When it comes to updating and search for in between dates I have problems. Ok in the dialog for the update command it has the command for updating the fields. So when you click update in the grid view where are the commands that make it update? It will not update. When you change something and click UPDATE it just goes right back to what it was. Also how do I search for between dates? I have two text boxes and set the SQLdatasource1.SelectCommand = String.Format("SELECT * From [Clipboard] WHERE [Date] Between {0} AND {1}", txtBeginningDate.Text, txtEndDate.Text); Now when you enter 05-05-2008 in the text boxes and hit my SEARCH button it says: Failed to convert nvarchar to int. Where is it converting to INT? I have it set to nvarchar??? BTW I am using MVS 2008 Pro, and I am not writing a lot of these commands. It is generated for me but I can't seem to get it to do those things.
well let's take this one at a time. first: the date format issue. Are you sure you don't need to do some kind of Convert.ToDateTime(txtBeginningDate.Text) in order for the STRING in that text box to convert to DateTime? beause right now it is trying to take a set of characters and do something with them. *You can only search legit by date when the field in the database is a DateTime field. On your update command in the gridview make sure you have the Where clause set correctly. As in Update [tablename] Set [column name] = value WHERE id = ? Does that make sense? Check the update parameters in the gridview code to make sure it's using all the necessary ones. And by that I mean the where clause should use a column that identifies that record only. Here's some help, could be quite a few things...if you want to narrow it down a bit :-D
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
-
well let's take this one at a time. first: the date format issue. Are you sure you don't need to do some kind of Convert.ToDateTime(txtBeginningDate.Text) in order for the STRING in that text box to convert to DateTime? beause right now it is trying to take a set of characters and do something with them. *You can only search legit by date when the field in the database is a DateTime field. On your update command in the gridview make sure you have the Where clause set correctly. As in Update [tablename] Set [column name] = value WHERE id = ? Does that make sense? Check the update parameters in the gridview code to make sure it's using all the necessary ones. And by that I mean the where clause should use a column that identifies that record only. Here's some help, could be quite a few things...if you want to narrow it down a bit :-D
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
Ok I see what you are saying.. but won't Convert.ToDateTime insert time to? I just need the Date. Also I do not have any parameters set up in the update. When someone clicks the update in the column (Update) next to the row (this is all built into the gridview) how do I specify what it is updating?
-
Ok I see what you are saying.. but won't Convert.ToDateTime insert time to? I just need the Date. Also I do not have any parameters set up in the update. When someone clicks the update in the column (Update) next to the row (this is all built into the gridview) how do I specify what it is updating?
Well I guess if I still convert the users input (after setting a validation like \d{2}\\d{2}\\d{4}) it will still convert and do the calculation right. Because if you don't specify a time it will input 12:00:00 AM for you. Also here is what I have for the update: UPDATE Clipboard SET County = @County, Type = @Type, Date = @Date WHERE (IncidentNumber = @IncidentNumber)
-
Ok I see what you are saying.. but won't Convert.ToDateTime insert time to? I just need the Date. Also I do not have any parameters set up in the update. When someone clicks the update in the column (Update) next to the row (this is all built into the gridview) how do I specify what it is updating?
Well one way to do this is to in the asp:Gridview tag code you can add DataKeyNames="[id column name here]" <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="lvl1_id" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None"> This will make whatever you put in that for a column the way gridview identify records. So if you're records have an ID column, it should be that. This is probably the method I would use. AND make sure your datasource has an update parameter set: <UpdateParameters> <asp:Parameter Name="lvl1_name" Type="String" /> <asp:Parameter Name="lvl1_id" Type="Int32" /> </UpdateParameters> This is part of the DataSOURCE code. See the update parameters....in the SQLDataSource, this is where you can set it up. what this is saying it take the text box for lvl1_name and the lvl1_id for that record and perform a sql update. So if it's not updating, I would say it's because your datasource code in the .aspx is missing those update parameters. I will include a datasource next. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:blahblah %>" DeleteCommand="UPDATE [bmj_level1] SET [enabled] = 0 WHERE [lvl1_id] = @lvl1_id" InsertCommand="INSERT INTO [bmj_level1] ([lvl1_name]) VALUES (@lvl1_name)" SelectCommand="SELECT [lvl1_id], [lvl1_name] FROM [bmj_level1] WHERE [enabled] = 1 ORDER BY [lvl1_name]" UpdateCommand="UPDATE [bmj_level1] SET [lvl1_name] = @lvl1_name WHERE [lvl1_id] = @lvl1_id"> <DeleteParameters> <asp:Parameter Name="lvl1_id" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="lvl1_name" Type="String" /> <asp:Parameter Name="lvl1_id" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="lvl1_name" Type="String" /> </InsertParameters> </asp:SqlDataSource>
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
-
Well I guess if I still convert the users input (after setting a validation like \d{2}\\d{2}\\d{4}) it will still convert and do the calculation right. Because if you don't specify a time it will input 12:00:00 AM for you. Also here is what I have for the update: UPDATE Clipboard SET County = @County, Type = @Type, Date = @Date WHERE (IncidentNumber = @IncidentNumber)
so in your datasource you need to specify under the "Update Parameters" section each one of those <UpdateParameters> <asp:Parameter Name="county" Type="String" /> <asp:Parameter Name="type" Type="Int32" /> <asp:Parameter Name="Date" Type="DateTime" /> </UpdateParameters> make sure the parameter datatypes match the sql data types.
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
-
Well one way to do this is to in the asp:Gridview tag code you can add DataKeyNames="[id column name here]" <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="lvl1_id" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None"> This will make whatever you put in that for a column the way gridview identify records. So if you're records have an ID column, it should be that. This is probably the method I would use. AND make sure your datasource has an update parameter set: <UpdateParameters> <asp:Parameter Name="lvl1_name" Type="String" /> <asp:Parameter Name="lvl1_id" Type="Int32" /> </UpdateParameters> This is part of the DataSOURCE code. See the update parameters....in the SQLDataSource, this is where you can set it up. what this is saying it take the text box for lvl1_name and the lvl1_id for that record and perform a sql update. So if it's not updating, I would say it's because your datasource code in the .aspx is missing those update parameters. I will include a datasource next. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:blahblah %>" DeleteCommand="UPDATE [bmj_level1] SET [enabled] = 0 WHERE [lvl1_id] = @lvl1_id" InsertCommand="INSERT INTO [bmj_level1] ([lvl1_name]) VALUES (@lvl1_name)" SelectCommand="SELECT [lvl1_id], [lvl1_name] FROM [bmj_level1] WHERE [enabled] = 1 ORDER BY [lvl1_name]" UpdateCommand="UPDATE [bmj_level1] SET [lvl1_name] = @lvl1_name WHERE [lvl1_id] = @lvl1_id"> <DeleteParameters> <asp:Parameter Name="lvl1_id" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="lvl1_name" Type="String" /> <asp:Parameter Name="lvl1_id" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="lvl1_name" Type="String" /> </InsertParameters> </asp:SqlDataSource>
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
Ok I am sort of following you.. Here is my SQLDataSource Code:
"
DeleteCommand="DELETE FROM [Clipboard] WHERE [IncidentNumber] = @IncidentNumber"
InsertCommand="INSERT INTO [Clipboard] ([County], [Type], [Date]) VALUES (@County, @Type, @Date)"
SelectCommand="SELECT * FROM [Clipboard]"
UpdateCommand="UPDATE Clipboard SET County = @County, Type = @Type, Date = @Date WHERE (IncidentNumber = @IncidentNumber)";DELETE and UPDATE do not work.... uhmm
modified on Tuesday, May 20, 2008 11:47 PM
-
Ok I am sort of following you.. Here is my SQLDataSource Code:
"
DeleteCommand="DELETE FROM [Clipboard] WHERE [IncidentNumber] = @IncidentNumber"
InsertCommand="INSERT INTO [Clipboard] ([County], [Type], [Date]) VALUES (@County, @Type, @Date)"
SelectCommand="SELECT * FROM [Clipboard]"
UpdateCommand="UPDATE Clipboard SET County = @County, Type = @Type, Date = @Date WHERE (IncidentNumber = @IncidentNumber)";DELETE and UPDATE do not work.... uhmm
modified on Tuesday, May 20, 2008 11:47 PM
In your code in there add this: DataKeyNames="IncidentNumber" like this: <asp:GridView ID="GridView1" runat="server" DataKeyNames="IncidentNumber" DataSourceID="SqlDataSource1" GridLines="None"> now try it.
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
-
In your code in there add this: DataKeyNames="IncidentNumber" like this: <asp:GridView ID="GridView1" runat="server" DataKeyNames="IncidentNumber" DataSourceID="SqlDataSource1" GridLines="None"> now try it.
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
-
In your code in there add this: DataKeyNames="IncidentNumber" like this: <asp:GridView ID="GridView1" runat="server" DataKeyNames="IncidentNumber" DataSourceID="SqlDataSource1" GridLines="None"> now try it.
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
Ok now my time search isnt working... You enter lets say: 1/1/2008 and 5/5/2008 and click a button.. here is the command:
SqlDataSource1.SelectCommand = String.Format("SELECT * FROM [Clipboard] WHERE [Date] Between {0} AND {1}", Convert.ToDateTime(txtBeginningDate.Text), Convert.ToDateTime(txtEndDate.Text));
But I get an Incorrect Syntax at 12?
-
Ok now my time search isnt working... You enter lets say: 1/1/2008 and 5/5/2008 and click a button.. here is the command:
SqlDataSource1.SelectCommand = String.Format("SELECT * FROM [Clipboard] WHERE [Date] Between {0} AND {1}", Convert.ToDateTime(txtBeginningDate.Text), Convert.ToDateTime(txtEndDate.Text));
But I get an Incorrect Syntax at 12?
in the sql server. in the "Clipboard" table...what is the data type of the "date" column... actually change of plans try this. SELECT * FROM [Clipboard] WHERE [Date] < {0} AND [Date] > {1} or SELECT * FROM [Clipboard] WHERE [Date] > {0} AND [Date] < {1}
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
modified on Wednesday, May 21, 2008 12:30 AM
-
I am having problems getting this to work. I have it where it inserts the data from some text boxes above. So It Inserts and Deletes just fine. When it comes to updating and search for in between dates I have problems. Ok in the dialog for the update command it has the command for updating the fields. So when you click update in the grid view where are the commands that make it update? It will not update. When you change something and click UPDATE it just goes right back to what it was. Also how do I search for between dates? I have two text boxes and set the SQLdatasource1.SelectCommand = String.Format("SELECT * From [Clipboard] WHERE [Date] Between {0} AND {1}", txtBeginningDate.Text, txtEndDate.Text); Now when you enter 05-05-2008 in the text boxes and hit my SEARCH button it says: Failed to convert nvarchar to int. Where is it converting to INT? I have it set to nvarchar??? BTW I am using MVS 2008 Pro, and I am not writing a lot of these commands. It is generated for me but I can't seem to get it to do those things.
hi Jacob Dixon,"Ok in the dialog for the update command it has the command for updating the fields. So when you click update in the grid view where are the commands that make it update? It will not update. When you change something and click UPDATE it just goes right back to what it was." for this please check the isnotpsotback property is given in Pageload event or not .
-
hi Jacob Dixon,"Ok in the dialog for the update command it has the command for updating the fields. So when you click update in the grid view where are the commands that make it update? It will not update. When you change something and click UPDATE it just goes right back to what it was." for this please check the isnotpsotback property is given in Pageload event or not .
-
in the sql server. in the "Clipboard" table...what is the data type of the "date" column... actually change of plans try this. SELECT * FROM [Clipboard] WHERE [Date] < {0} AND [Date] > {1} or SELECT * FROM [Clipboard] WHERE [Date] > {0} AND [Date] < {1}
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
modified on Wednesday, May 21, 2008 12:30 AM
Hey! Yes I have the Date column set to: DateTime. It actually wants the Date and the Time but I am fine with that. It doesn't have an option for just the date. Anyways what I did was that I forgot the: " ' ' " between the beginning and ending dates. Like I did it like this: Where [Date] Between {0} AND {1} and it should of been: Where [Date] Between '{0}' AND '{1}' it works though. thanks!
-
Hey! Yes I have the Date column set to: DateTime. It actually wants the Date and the Time but I am fine with that. It doesn't have an option for just the date. Anyways what I did was that I forgot the: " ' ' " between the beginning and ending dates. Like I did it like this: Where [Date] Between {0} AND {1} and it should of been: Where [Date] Between '{0}' AND '{1}' it works though. thanks!
i can't believe i didn't even notice that. i should have known better. it was late :(
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford
-
i can't believe i didn't even notice that. i should have known better. it was late :(
----------------------------------------------------------- "When I first saw it, I just thought that you really, really enjoyed programming in java." - Leslie Sanford