UPDATE Statement Syntax Help Required C# && SqlDataSource Control
May 3, 2007
Hi,
I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code:
Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog
SqlDataSource customerDataSource = new SqlDataSource();
customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here
Hi, I am a newbie in using ASP.NET 2.0 and ADO.NET. I wrote a hangman game and want to record statistics at the end of each game. I will create and update records in the database for each authenticated user as well as a record for the Anonymous, unauthenticated user. After a win or loss has occurred, I want to programmatically use the SQLDataSource control to increment the statistics counters for the appropriate record in the database (note I don't want to show anything or get user input for this function). I need a VB.NET codebehind example that will show me how I should set up the parameters and update the appropriate record in the database. Below is my code. What happens now is that the program chugs along happily (no errors), but the database record does not actually get updated. I have done many searches on this forum and on the general Internet for programmatic examples of an update sequence of code. If there is a tutorial for this online or a book, I'm happy to check it out. Any help will be greatly appreciated. Lambanlaa CODE - Hangman.aspx.vb 1 Protected Sub UpdateStats()2 Dim playeridString As String3 Dim gamesplayedInteger, gameswonInteger, _4 easygamesplayedInteger, easygameswonInteger, _5 mediumgamesplayedInteger, mediumgameswonInteger, _6 hardgamesplayedInteger, hardgameswonInteger As Int327 8 ' determine whether player is named or anonymous9 If User.Identity.IsAuthenticated Then10 Profile.Item("hangmanplayeridString") = User.Identity.Name11 Else12 Profile.Item("hangmanplayeridString") = "Anonymous"13 End If14 15 playeridString = Profile.Item("hangmanplayeridString")16 17 ' look up record in stats database18 Dim hangmanstatsDataView As System.Data.DataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)19 20 gamesplayedInteger = 021 gameswonInteger = 022 easygamesplayedInteger = 023 easygameswonInteger = 024 mediumgamesplayedInteger = 025 mediumgameswonInteger = 026 hardgamesplayedInteger = 027 hardgameswonInteger = 028 29 If hangmanstatsDataView.Table.Rows.Count = 0 Then30 31 ' then create record with 0 values32 statsSqlDataSource.InsertParameters.Clear() ' don't really know what Clear does33 statsSqlDataSource.InsertParameters("playerid").DefaultValue = playeridString34 statsSqlDataSource.InsertParameters("GamesPlayed").DefaultValue = gamesplayedInteger35 statsSqlDataSource.InsertParameters("GamesWon").DefaultValue = gameswonInteger36 statsSqlDataSource.InsertParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger37 statsSqlDataSource.InsertParameters("EasyGamesWon").DefaultValue = easygameswonInteger38 statsSqlDataSource.InsertParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger39 statsSqlDataSource.InsertParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger40 statsSqlDataSource.InsertParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger41 statsSqlDataSource.InsertParameters("HardGamesWon").DefaultValue = hardgameswonInteger42 43 statsSqlDataSource.Insert()44 End If45 46 ' reread the record to get current values47 hangmanstatsDataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)48 Dim hangmanstatsDataRow As System.Data.DataRow = hangmanstatsDataView.Table.Rows.Item(0)49 50 ' set temp variables to database values51 gamesplayedInteger = hangmanstatsDataRow("GamesPlayed")52 gameswonInteger = hangmanstatsDataRow("GamesWon")53 easygamesplayedInteger = hangmanstatsDataRow("EasyGamesPlayed")54 easygameswonInteger = hangmanstatsDataRow("EasyGamesWon")55 mediumgamesplayedInteger = hangmanstatsDataRow("MediumGamesPlayed")56 mediumgameswonInteger = hangmanstatsDataRow("MediumGamesWon")57 hardgamesplayedInteger = hangmanstatsDataRow("HardGamesPlayed")58 hardgameswonInteger = hangmanstatsDataRow("HardGamesWon")59 60 ' update stats record61 'statsSqlDataSource.UpdateParameters.Clear()62 'statsSqlDataSource.UpdateParameters("playerid").DefaultValue = playeridString63 64 If Profile.Item("hangmanwinorloseString") = "win" Then65 66 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 167 statsSqlDataSource.UpdateParameters("GamesWon").DefaultValue = gameswonInteger + 168 Select Case Profile.Item("hangmandifficultyInteger")69 Case 170 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 171 statsSqlDataSource.UpdateParameters("EasyGamesWon").DefaultValue = easygameswonInteger + 172 Case 273 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 174 statsSqlDataSource.UpdateParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger + 175 Case 376 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 177 statsSqlDataSource.UpdateParameters("HardGamesWon").DefaultValue = hardgameswonInteger + 178 End Select79 80 81 ElseIf Profile.Item("hangmanwinorloseString") = "lose" Then82 83 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 184 Select Case Profile.Item("hangmandifficultyInteger")85 Case 186 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 187 Case 288 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 189 Case 390 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 191 End Select92 End If93 94 statsSqlDataSource.Update()95 96 End Sub97 CODE - Hangman.aspx 1 <asp:SqlDataSource ID="statsSqlDataSource" runat="server" ConflictDetection="overwritechanges" 2 ConnectionString="<%$ ConnectionStrings:lambanConnectionString %>" DeleteCommand="DELETE FROM [Hangman_Stats] WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon" 3 InsertCommand="INSERT INTO [Hangman_Stats] ([PlayerID], [GamesPlayed], [GamesWon], [EasyGamesPlayed], [EasyGamesWon], [MediumGamesPlayed], [MediumGamesWon], [HardGamesPlayed], [HardGamesWon]) VALUES (@PlayerID, @GamesPlayed, @GamesWon, @EasyGamesPlayed, @EasyGamesWon, @MediumGamesPlayed, @MediumGamesWon, @HardGamesPlayed, @HardGamesWon)" 4 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT PlayerID, GamesPlayed, GamesWon, EasyGamesPlayed, EasyGamesWon, MediumGamesPlayed, MediumGamesWon, HardGamesPlayed, HardGamesWon FROM Hangman_Stats WHERE (PlayerID = @playerid)" 5 UpdateCommand="UPDATE [Hangman_Stats] SET [GamesPlayed] = @GamesPlayed, [GamesWon] = @GamesWon, [EasyGamesPlayed] = @EasyGamesPlayed, [EasyGamesWon] = @EasyGamesWon, [MediumGamesPlayed] = @MediumGamesPlayed, [MediumGamesWon] = @MediumGamesWon, [HardGamesPlayed] = @HardGamesPlayed, [HardGamesWon] = @HardGamesWon WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon"> 6 <DeleteParameters> 7 <asp:Parameter Name="original_PlayerID" Type="String" /> 8 <asp:Parameter Name="original_GamesPlayed" Type="Int32" /> 9 <asp:Parameter Name="original_GamesWon" Type="Int32" /> 10 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" /> 11 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" /> 12 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" /> 13 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" /> 14 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" /> 15 <asp:Parameter Name="original_HardGamesWon" Type="Int32" /> 16 </DeleteParameters> 17 <UpdateParameters> 18 <asp:Parameter Name="GamesPlayed" Type="Int32" /> 19 <asp:Parameter Name="GamesWon" Type="Int32" /> 20 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" /> 21 <asp:Parameter Name="EasyGamesWon" Type="Int32" /> 22 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" /> 23 <asp:Parameter Name="MediumGamesWon" Type="Int32" /> 24 <asp:Parameter Name="HardGamesPlayed" Type="Int32" /> 25 <asp:Parameter Name="HardGamesWon" Type="Int32" /> 26 <asp:Parameter Name="original_PlayerID" Type="String" /> 27 <asp:Parameter Name="original_GamesPlayed" Type="Int32" /> 28 <asp:Parameter Name="original_GamesWon" Type="Int32" /> 29 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" /> 30 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" /> 31 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" /> 32 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" /> 33 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" /> 34 <asp:Parameter Name="original_HardGamesWon" Type="Int32" /> 35 </UpdateParameters> 36 <InsertParameters> 37 <asp:Parameter Name="PlayerID" Type="String" /> 38 <asp:Parameter Name="GamesPlayed" Type="Int32" /> 39 <asp:Parameter Name="GamesWon" Type="Int32" /> 40 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" /> 41 <asp:Parameter Name="EasyGamesWon" Type="Int32" /> 42 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" /> 43 <asp:Parameter Name="MediumGamesWon" Type="Int32" /> 44 <asp:Parameter Name="HardGamesPlayed" Type="Int32" /> 45 <asp:Parameter Name="HardGamesWon" Type="Int32" /> 46 </InsertParameters> 47 <SelectParameters> 48 <asp:ProfileParameter Name="playerid" PropertyName="hangmanplayeridString" /> 49 </SelectParameters> 50 </asp:SqlDataSource>
I've just finished configuring the SELECT command for the SqlDataSource in my ASP.NET 2.0 web app. It works fine and runs against a SQL Server 2005 database, using a stored procedure that I've written. So, then I went to configure the SqlDataSource for the UPDATE and INSERT commands, and I've written two SP's for those as well. In the designer the second form of the wizard asks for the Select statement. I've already given that for the SELECT statement in the third form, and I also select the INSERT tab to specify the SP I want to use for inserting data and the UPDATE tab to specify the SP I want to use for updating data. However, there appears to be no way that I can specify what the parameters are supposed to be for anything other than the SELECT command, through the designer. Is that correct, or have I missed something?
Hi all, I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:
As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work). So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event. What is the best approach? Anyone come up against this issue before? Thanks, KB
I can select the tables and fields, but when I click on Advanced the Check Box to Create the Commands is not available. they are dimmed out. Why is this happening, is it a setting that I have missed. Any help is appreciated.
Hi, I'm reasonably new to ASP.NET 2.0 I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete. For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures). I desperately need the syntax to: a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need). b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters). c) syntax on how to set the values for these parameters (again...coming from form controls and session variables) d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource. If anybody has sample code or a link or two, I would be most appreciative. Thank you in advance for any help!
I am unable to update the data using record by record below scenario.
Required output:
patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.
Current Output:
Calculation: From index_admission discharge date to next admit_visit date,
1) if the diff is having less than 30 days, readmission=1 and Index=0
else readmission=0 and Index=1 should be update.
For checking this every time should check using the latest index_admission discharge_date.
To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.
UPDATE Readmission SET Index_AMI = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM (SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub) , FPR.Admit_Date_Time) between 0 and 31 THEN 0 ELSE 1 END),
Hi forum, im converting some code and have an issue with th following code!!! many thanks for good advice, Paul Line 55: SQLa = "UPDATE counted SET " & y & " = " & intClick & ", total = " & intTotal & " WHERE ID = " & RSpc.Fields("pc").ValueLine 56: Conn.Execute(SQLa) System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Dim SQLa As String
I have place a lblmessage.text = ex.Message to trace what happen and it show me a syntax error in update happen. I don't know whats wrong with my syntax. Can someone help me on this?
'set up connection dim conn as new oledbconnection _ ("provider = microsoft.jet.oledb.4.0;" & _ "data source = c:aspnetdataanking.mdb")
sub page_load(sender as object, e as eventargs) if not page.ispostback then filldatagrid() end if end sub
sub submit (sender as object, e as eventargs) 'insert new data dim i,j as integer dim params(7) as string dim strtext as string dim blngo as boolean = true
j = 0
for i = 0 to addpanel.controls.count -1 if addpanel.controls(i).gettype is _ gettype (textbox) then strtext = ctype(addpanel.controls(i), _ textbox).text if strtext <> "" then params(j) = strtext else blngo = false lblmessage.text = lblmessage.text & _ "you forgot to enter a value for " & _ addpanel.controls (i).id & "<p>" lblmessage.style ("forecolor")= "red" end if j=j+1 end if next
sub dgdata_edit (sender as object, e as datagridcommandeventargs) filldatagrid(e.item.itemindex) end sub
sub dgdata_delete (sender as object, e as datagridcommandeventargs) dim strsql as string = "DELETE FROM tblusers " & _ "WHERE userid = " & e.item.itemindex + 1
executestatement(strsql) filldatagrid() end sub
sub dgdata_update (sender as object, e as datagridcommandeventargs) if updatedatastore(e) then filldatagrid(-1) end if end sub
sub dgdata_cancel (sender as object, e as datagridcommandeventargs) filldatagrid(-1) end sub
sub dgdata_pageindexchanged (sender as object, e as datagridpagechangedeventargs) dgdata.databind() end sub
function updatedatastore (e as datagridcommandeventargs) as boolean dim i,j as integer dim params(7) as string dim strtext as string dim blngo as boolean = true
j = 0
for i =1 to e.item.cells.count - 3 strtext = ctype(e.item.cells(i).controls(0), _ textbox).text if strtext <> "" then params(j) = strtext blngo = true j= j+1 else blngo = false lblmessage.text = lblmessage.text & _ "you forgot to enter a value<p>" end if next
if not blngo then return false exit function end if
executestatement (strsql) return blngo end function
sub filldatagrid (optional editindex as integer = -1) ' open connection dim objcmd as new oledbcommand _ ("select * from tblusers", conn) dim objreader as oledbdatareader
try objcmd.connection.open () objreader = objcmd.executereader() catch ex as exception lblmessage.text = "error retrieving from the " & _ "database." end try
dgdata.datasource = objreader if not editindex.equals(nothing) then dgdata.edititemindex = editindex end if
hi friends, I created sqldatasource control. In select command i written the query like this "select * form emp" and bounded in grid.How can I change the query for searching the details according the date wise when i click the search button.
Hello experts i have a SqlDataSource Control on my web form. Here is the source for the control <asp:SqlDataSource ID="sqlSearchDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbefomsConnectionString %>" SelectCommand="SELECT [icon], [file_name], [path] FROM [tblfile] WHERE ([file_name] = @file_name)"> <SelectParameters> <asp:ControlParameter ControlID="txtSearch" DefaultValue="0" Name="file_name" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> This control displays icon, file_name, path in GridView Control The problem is How can i store the above field values in an asp.net variable. Thanks Regards Ali
Do you have to use the sqldatasource in conjuction with another control like gridview or dropdown list? I'd like to check to see if a record exists in one table before inserting data into another.
Hello.I'm new to ASP.NET and trying to write data to a Microsft SQL Server. I have created a SqlDataSource control, which is 'connected' to my SQL server.Now my question is how i can put data in the database throw the control, and read data from it. I've read the site, but it's still unclear for me..Greetings!
Hello: I am having a little issue with an error that is frustrating me. I have a SqlDataSource within an asp:label control and also have a two dropdownlists inside that asp:label control also. When I select from the first dropdownlist, it should enable the second dropdownlist with the populated data, but instead I receive. The SqlDataSource control 'sqlGetMLSByCity' does not have a naming container. Ensure that the control is added to the page before calling DataBind. Any ideas why this would be? Here is the web from portion: <asp:Label ID="recip_form_request" runat="server" Visible="false"> <ul> <li>Step one: Select listing type.</li> <li>Step two: Select the City that you are reciprocating to.</li> <li>Step three: Complete the Recip Form.</li> </ul> <asp:DropDownList ID="recip_form_type" runat="server" AutoPostBack="true" Visible="False"> <asp:ListItem Value="0" Text="(Select Form)"></asp:ListItem> <asp:ListItem Value="1" Text="Residential"></asp:ListItem> </asp:DropDownList> <br /> <asp:DropDownList ID="outside_assoc_cities" runat="server" AutoPostBack="True" Enabled="False" DataTextField="city" DataValueField="id" Visible="False"></asp:DropDownList><br /> <asp:Label ID="city_mls_desc" runat="server"></asp:Label><br /> <asp:Button ID="page1_btn" runat="server" Text="Next >>" Visible="False" /> <asp:SqlDataSource ID="sqlCities" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ AppSettings:connectionstring %>" SelectCommandType="Text" SelectCommand="SELECT id, city FROM tCityMaster"></asp:SqlDataSource> <asp:SqlDataSource ID="sqlGetMLSByCity" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ AppSettings:connectionstring %>"> <SelectParameters> <asp:ControlParameter ControlID="outside_assoc_cities" Name="City" /> </SelectParameters> </asp:SqlDataSource></asp:Label> then the code behind: Protected Sub outside_assoc_cities_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles outside_assoc_cities.SelectedIndexChanged If outside_assoc_cities.SelectedIndex > -1 Then Dim ItemId As Integer = Integer.Parse(outside_assoc_cities.SelectedValue) Dim MLSID As String = Nothing Dim cmd As New SqlCommand Dim con As New SqlConnection cmd.Connection = con Dim sqlGetMLSByCity_sql As SqlDataSource = CType(Page.FindControl("sqlGetMLSByCity"), SqlDataSource) 'using connectionstring from SqlDataSource Control con.ConnectionString = sqlGetMLSByCity_sql.ConnectionString 'con.ConnectionString = sqlGetMLSByCity.ConnectionString cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT mls_id FROM pwaordev..tCityMaster WITH(NOLOCK) WHERE ID=@ID" cmd.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ItemId Try con.Open() MLSID = cmd.ExecuteScalar con.Dispose() cmd.Dispose() Catch ex As Exception con.Dispose() cmd.Dispose() Response.Write(ex.ToString()) End Try 'Further logic to display button depending on MLS If Not String.IsNullOrEmpty(MLSID) Then If MLSID = "B" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Greater South Bay MLS." page1_btn.Visible = True ElseIf MLSID = "A" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Big Bear BOR, MLS, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "C" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Combined L.A./Westside MLS." page1_btn.Visible = True ElseIf MLSID = "F" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Crisnet MLS, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "Y" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is DCAoR - Yucca Valley Area, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "D" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Desert Area MLS." page1_btn.Visible = True ElseIf MLSID = "L" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is i-Tech Glendale/Pasadena." page1_btn.Visible = True ElseIf MLSID = "M" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is MRM - Multi-Regional MLS." page1_btn.Visible = True ElseIf MLSID = "R" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is RIM - Rim of the World MLS, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "G" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is SDI - San Diego, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "S" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is SOCAL MLS - Southern California MLS." page1_btn.Visible = True ElseIf MLSID = "T" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Outside Area, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False End If 'test.Text = MLSID 'If test.Text = "L" Then 'mls_text.Text = "i-Tech" 'End If End If End If End Sub Any help would be great, thank you!
I have an SqlDatSource that I have fully setup at design time, but I don't want it to open and load as soon as the page loads, instead I want to open it based on a condition. How do you do that with a DataSource? I know that I can simply remove the Select query, and then set it at run time, but I'm looking for something better, that allows me to have the Select query set at design time, in part because I have a lot of parameters.
Hello: I have two SqlDataSource controls on two different pages: one is updating a table and the other is reading from the same table. Now is it possible that the reader SqlDataSource control can be refreshed immediately (to reflect updates) when the other control updates the table?
Hello all, I am trying to update the record which involed the modification of key in Datakeynames, but when i click the update button from gridview, it doesn't allow to change the value of modified column. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>" OldValuesParameterFormatString="old_{0}" SelectCommand="SELECT * FROM [ServiceAgents]" InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)" UpdateCommand="UPDATE ServiceAgents SET AGENT=@AGENT, PHONE=@PHONE WHERE (STATE=@STATE and CITY=@CITY and AGENT=@old_AGENT and EQUIPMENT=@EQUIPMENT)" DeleteCommand="DELETE FROM ServiceAgents WHERE (STATE=@STATE and CITY=@CITY and AGENT=@AGENT and EQUIPMENT=@EQUIPMENT)" > <UpdateParameters> <asp:Parameter Type="String" Name="AGENT" /> <asp:Parameter Type="String" Name="PHONE" /> <asp:Parameter Name="old_AGENT" /> <asp:Parameter Type="String" Name="STATE" /> <asp:Parameter Type="String" Name="CITY" /> <asp:Parameter Type="String" Name="EQUIPMENT" /> </UpdateParameters> <InsertParameters> <asp:ControlParameter Name="Ser_STATE" ControlID="TextBox_state"/> <asp:ControlParameter Name="Ser_CITY" ControlID="TextBox_city"/> <asp:ControlParameter Name="Ser_AGENT" ControlID="TextBox_agent"/> <asp:ControlParameter Name="Ser_PHONE" ControlID="TextBox_phone"/> <asp:ControlParameter Name="Ser_EQUIPMENT" ControlID="TextBox_equip" /> </InsertParameters> <DeleteParameters> <asp:Parameter Type="String" Name="STATE" /> <asp:Parameter Type="String" Name="CITY" /> <asp:Parameter Type="String" Name="AGENT" /> <asp:Parameter Type="String" Name="EQUIPMENT" /> </DeleteParameters> </asp:SqlDataSource> Does anyone got any ideas about it? Where is wrong in the control?
I have a SQLDatasource control on a web page. It is the datasource for a gridview control.I want the gridview to show all jones if the FirstName textbox is left blank or empty. Right now I have to put a % in the FirstName textbox to get what I want. If I make the FirstNameTextBox empty or remove the % from the FirstNameTextbox it returns all the names in the database no matter what the last name is.How do I get it to work without having to use the % in the FirstName Textbox? THANKS!FilterExpression="LastName LIKE '%{0}%' and FirstName LIKE '%{1}%'"><FilterParameters> <asp:ControlParameter ControlID="LastNameTextBox" Name="LastName" PropertyName="Text" DefaultValue="" /> <asp:ControlParameter ControlID="FirstNameTextBox" Name="FirstName" PropertyName="Text" DefaultValue="" /></FilterParameters> Last Name: Jones___________First Name: %_____________FILTERBUTTON GridviewLast Name First NameBob JonesBill Jones
I have not found anything useful on the Wizard control. Anything I can find gets to the last step and then just takes the info you supplied and applies them to a bunch of Label.Text's - none that do work. So I am building a Wizard control for an HR system that will insert the information into SQL through a SqlDataSource. The problem is, only the controls that are present in whichever step is currently selected in VWD will appear in the Command and Parameter Editor. I tried to work around it by selecting the first step, then applying the controls in step 1 within the Parameter Editor, but when I go back to do step 2, it erases everything I just did for step 1. How do I implement the Wizard with a SqlDataSource?
I have a gridview that is tied to a SQLDataSource control. As the gridview is filled some of the ASPX code calls some code in the code behind page. Where I am having trouble is that in one of these calls I need to some how pass the id filed ("LISTID")of the table for the SQL statement in the code behind page and I'm not sure how to do it. The code behind function is always using the same id and I do not know how to change it. Below is all the code.
Here is the calling code in the ASPX page. "DeleteBtn" Visible='<%# IsDeleteBtnVisible %>' runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" ForeColor="#003399"> Here is the entire ASPX code. class="header"> class="title">My Lists "Span1"> Current List Filter: "DropDownList2" cssclass="filterdropdown" AutoPostBack="true" runat="server"> "Active" Value="False"> "Done" Value="true"> --------------------------------------------------------------------------------"separator2"/> "list2"> "ScriptManager1" EnablePartialRendering="true" runat="server"> "Up2" UpdateMode="Conditional" runat="server"> "GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="LISTID" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" EnableViewState="False" GridLines="None" AlternatingRowStyle-BackColor="#FFFFCC" AlternatingRowStyle-Font-Size="Small" ForeColor="#FFFFCC" Font-Size="Small" AlternatingRowStyle-ForeColor="Black" Width="100%" HeaderStyle-ForeColor="White"> "False"> "LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update" ForeColor="#003399"> "LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" ForeColor="#003399"> "LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" ForeColor="#003399"> "DeleteBtn" Visible='<%# IsDeleteBtnVisible %>' runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" ForeColor="#003399"> "buttons"> "commands"> "ITEM_DETAILS" HeaderText="Details" SortExpression="ITEM_DETAILS" ControlStyle-ForeColor="#003399"> "name_edit"> "name"> "name"> "Pri" itemStyle-HorizontalAlign="Center" SortExpression="Priority" ControlStyle-ForeColor="#003399"> "DropDownList1" Width="75" SelectedValue='<%# Bind("Priority") %>' runat="server"> "High" Value="3"> "Medium" Value="2"> "Low" Value="1"> "pri" Text='<%# FormatPriority(Eval("Priority")) %>' runat="server"> "priority"> "#003399"> "priority"> "DUEDATE" itemStyle-HorizontalAlign="Center" HeaderText="Due Date" DataFormatString="{0:MM/dd/yyyy}" SortExpression="DUEDATE" > "Center"> "COMPLETEDON" itemStyle-HorizontalAlign="Center" HeaderText="Completed On" DataFormatString="{0:MM/dd/yyyy}" SortExpression="COMPLETEDON" > "Center"> "Done" itemStyle-HorizontalAlign="Center" SortExpression="IsComplete"> "CheckBox1" runat="server" Checked='<%# Bind("IsComplete") %>'> "iscomplete"> "iscomplete"> "Done" Text='<%# FormatDone(Eval("IsComplete")) %>' runat="server"> "Button2" runat="server" CommandName="Something" DataTextField="LISTID" DataTextFormatString="{0} active items" text='<%# Eval("LISTID") %>'> "Empty">No lists "White"> "#FFFFCC" Font-Size="Small" ForeColor="Black"> "Div1"> "Up3" UpdateMode="Conditional" runat="server"> Add New List: "AddItem2" cssclass="newitem" runat="server"> "Button1" runat="server" OnClick="AddListBtn_Click" Text="Add"> "txtdate" runat="server"> "Button1" EventName="Click"> "SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FastTrackConnectionString %>" SelectCommand="SELECT * FROM [TODO]"> Here is the code behind page code. Protected Function IsDeleteBtnVisible() As Boolean ' Return IIf(itemCount = 0, True, False) 'This is for my gridview Dim strConn2 As SqlConnection = New SqlConnection Dim cmd2 As SqlCommand = New SqlCommand Dim rs2 As SqlDataReader Dim test1 As String Dim test2 As String strConn2.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("FastTrackConnectionString2").ToString() strConn2.Open() cmd2.Connection = strConn2 cmd2.CommandText = "SELECT LISTID, (SELECT COUNT(0) AS ItemCount FROM TODO_SUB_ITEMS WHERE(LISTID = TODO.LISTID) AND (ISCOMPLETE ='False')) AS ItemCount FROM TODO WHERE (ISCOMPLETE ='False')" rs2 = cmd2.ExecuteReader() rs2.Read() Dim testlistid As Integer = rs2(0) Dim intholder As Integer = rs2(1) strConn2.Close() cmd2 = Nothing strConn2 = Nothing Return IIf(intholder = 0, True, False) End Function
I have a gridview that is filled from a SQLDataSource control when the page loads. I need to do some updates and delets on this data and I wanted to know can you add more than one SQL statement to a SQLDataSource control or do I have to have a seprate control for each operation? The SQL currently is just the standard SELECT *, I cannnot see a way to add statments. Thanks, Ty
There is something I don't understand or I am just missing something here. Why is it that I can place an SqlDataSource1.Insert() or update, or delete, but not an SqlDataSource.Select() inside a button click event "for an example" without getting an Compilation Error? Thanks, xyz789
I have dragged 3 SqlDataSource Controls to the WebDesigner and none are appearing graphically in the Designer View, but they are in the Source View. How can I get the control to appear in the designer.
When I configure a SQLDataSource control I want the following where clause... WHERE (RegionID = @RegionID AND DistrictID IS NULL AND CampusID IS NULL) OR (DistrictID = @DistrictID AND CampusID IS NULL ) OR(CampusID = @CampusID AND UserRole = 'CampusAdmin') OR (CampusID = @CampusID)but it gets reformated as.... WHERE (RegionID = 'Region10') AND (DistrictID IS NULL) AND (CampusID IS NULL) OR (DistrictID = 2) AND (CampusID IS NULL) OR (CampusID = '999999103') AND (UserRole = 'CampusAdmin') OR (CampusID = '999999103')I don't think these are functionaly equivelant, are they?If not how do I stop the 'wizard' from reformatting the SQL where clause?Thanks
I set up a Sqldatasource control in 2.0 and I can retrieve data through a SQL Server connection from a stored procedure. My problem is when I set up the insert command object through the wizard for the Sqldatasource control with another stored procedure for inserting data and call the insert method of my Sqldatasource object i get nothing not even an error it just goes through the code like nothing was wrong and I don't get anything inserted. I don't know if this could be a problem but one of the parameters in the stored procedure is declared like this: @return tinyint output. I don't know how the Sqldatasource accounts for return parameters. Here is the code for the insert for the Sqldatasource object.<asp:SqlDataSource ID="sdsMain" runat="server" ConnectionString="<%$ ConnectionStrings:SN_CUSTOMERConnectionString %>"InsertCommand="uspSNOrder_Promo_Live" InsertCommandType="StoredProcedure" SelectCommand="uspOPFillPromo"SelectCommandType="StoredProcedure"><InsertParameters><asp:ControlParameter ControlID="ddlPromo" Name="promoid" PropertyName="SelectedValue"Type="Int32" /><asp:Parameter DefaultValue="1" Name="datasourceid" Type="Int32" /><asp:Parameter DefaultValue="0" Name="datasourcekey" Type="String" /><asp:Parameter DefaultValue="9" Name="salesroomid" Type="Int32" /><asp:Parameter DefaultValue="9999" Name="userid" Type="Int32" /><asp:ControlParameter ControlID="txtFName" DefaultValue="" Name="firstname" PropertyName="Text"Type="String" /><asp:ControlParameter ControlID="txtLName" Name="lastname" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtAddress" Name="address" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtCity" Name="city" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtState" Name="state" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtZip" Name="zip" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtPhone" Name="phone" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtEmail" Name="email" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="ddlStatus" Name="status" PropertyName="SelectedValue"Type="Int32" /><asp:Parameter DefaultValue="0" Direction="InputOutput" Name="return" Type="Byte" /></InsertParameters></asp:SqlDataSource>Thanks in advance
Dear all,I am couping a dropdownlist and a sqldatasource control to display a column from a database of SQL/Server on browser. However, when I tried to configure the sqldatasource to let it get data from SQL/Server, it always report an error. It normally happens at the first step of creating new connection. After I specified the correct sql/server instance name, using SQL server authentication, and select a database name. Even the connection test can sucessfully passed. However, when I click the "OK" button, an error message saying "object reference not set to an instance of an object" popped out and I could not add new connections. The strange thing is that after I reintall the visual studio 2005 service pack 1. It will function properly for a while. After sometime, it will fail again. Anybody has suggestions? Thanks a lot!
I have made a SQLDataSource control with the select command: SELECT COUNT(*) AS 'Antall' FROM Utgivelse WHERE (medieID = @medieID) I want to use the "Antall" result programmatically in C# code. I try the following statement: IDataReader MyReader;
MyReader = CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty),IDataReader); but it doesnot work. Can somebody help me how to get the data from th control ? Tom
Is it possible to place a stored procedure in a SQLDataSource control that needs a variable input then run it later? DETAILS I have 2 pages with gridview controls that are linked to a SQLDataSource controls. What I would like to do is when the user clicks on a link in the gridview of the first page I want to take the value of the link clicked and pass it to the second page where it is placed into a variable that would be used in a WHERE clause to fill the second pages gridview. I have no problem getting the value into a variable in the page load event of the second page. What I would be nice is since the gridview is already tied to a SQLDataSource control with the columns specified is if I could some how pass the variable to a stored procedure in the control so I don't have to manually pull and fill the data. Any thoughts would be apperciated. Thanks, Ty
I am trying to use a 'timestamp' type column with SQLDataSource for concurrency control with SQL Server. I'm getting the following error: Operand type clash: timestamp is incompatible with sql_variant It appears that the problem is that the value for the Type property of the Parameter (which was generated by the SqlDataSource wizard) is Object which maps to 'sql_variant' rather than to 'timestamp'. I know that the older way of doing things with SqlDataAdapter, SqlCommand, SqlParameter can handle timestamps because SqlParameter has a SqlDbType property that can have a value of SqlDbType.Timestamp, but I don't see how to do this with the newer SqlDataSource, Parameter classes because the Parameter.Type property (of type TypeCode) doesn't have a Timestamp value. Has anyone been able to use a 'timestamp' type field with SqlDataSource? Here's my sample code: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="MyTestPage.aspx.cs" Inherits="MyTestPage" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title></head><body><form id="form1" runat="server"><div><asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"EmptyDataText="There are no data records to display." DataKeyNames="MyID"><Columns><asp:CommandField ShowEditButton="True" /><asp:BoundField DataField="MyID" HeaderText="MyID" InsertVisible="False" ReadOnly="True"SortExpression="MyID" /><asp:BoundField DataField="MyDesc" HeaderText="MyDesc" SortExpression="MyDesc" /></Columns></asp:GridView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:CtrlConnectionString1 %>" DeleteCommand="DELETE FROM [MyTest] WHERE [MyID] = @original_MyID AND [MyDesc] = @original_MyDesc AND [timestamp] = @original_timestamp"InsertCommand="INSERT INTO [MyTest] ([MyDesc], [timestamp]) VALUES (@MyDesc, @timestamp)" OldValuesParameterFormatString="original_{0}"ProviderName="<%$ ConnectionStrings:CtrlConnectionString1.ProviderName %>" SelectCommand="SELECT [MyID], [MyDesc], [timestamp] FROM [MyTest]"UpdateCommand="UPDATE [MyTest] SET [MyDesc] = @MyDesc WHERE [MyID] = @original_MyID AND [MyDesc] = @original_MyDesc AND [timestamp] = @original_timestamp"><DeleteParameters><asp:Parameter Name="original_MyID" Type="Int32" /><asp:Parameter Name="original_MyDesc" Type="String" /><asp:Parameter Name="original_timestamp" Type="Object" /></DeleteParameters><UpdateParameters><asp:Parameter Name="MyDesc" Type="String" /><asp:Parameter Name="timestamp" Type="Object" /><asp:Parameter Name="original_MyID" Type="Int32" /><asp:Parameter Name="original_MyDesc" Type="String" /><asp:Parameter Name="original_timestamp" Type="Object" /></UpdateParameters><InsertParameters><asp:Parameter Name="MyDesc" Type="String" /><asp:Parameter Name="timestamp" Type="Object" /></InsertParameters></asp:SqlDataSource></div></form></body></html>
how can i display the result of an asp:SqlDataSource into a lable control.the sqldatasource returns the count for some thing ie "select count(*) as total from tbl"please help