In the device emulator when it starts I see two records that were added to the SDF in the VS2008 IDE. I added a third record and after tapping on the Save menu my navigation control shows all three records. I then closed the application. I went to Memory and stopped the form. I went to the File Editor and restarted the application. It only showed the original 2 records. In the File Editor I tapped on the SDF to open it in the Query Analyzer. It also only shows the original two records.
The Dataset appears to be updated, but not the bound table in the SDF. Can any one help? Code is below.
bsTEST is the binding SOurce
navTEST is my custom Navigation control
Private Sub HandleMenus(ByVal Sender As Object, ByVal EA As EventArgs) Handles mnuAdd.Click, mnuCancel.Click, mnuSave.Click
C#, Webforms, VS 2005, SQL Hi all, quick hit question. I'm trying to update a table with an employee name and hire date. Session variable of empID, passed from a previous page (successfully) determines which row to plop the update into. It's not working even though i compiles and makes it all the way through the code to the txtReturned.Text = "I made it" debug line...Any thoughts? 1 string szInsSql; 2 3 string sConnectionString = "Data Source=dfssql;Database=MyDB;uid=myID;pwd=myPWD"; 4 SqlConnection objConn = new SqlConnection(sConnectionString); 5 6 objConn.Open(); 7 8 szInsSql = "UPDATE empEmployee SET " + 9 "Name = '" + this.txtName.Text + "', " + 10 "HireDate = '" + this.txtHireDate.Text + "', " + 11 "WHERE empID = '" + Session[empID] + "'"; 12 13 SqlCommand objCmd1 = new SqlCommand(szInsSql, objConn); 14 objCmd1.ExecuteNonQuery(); 15 16 txtReturned.Text = "I made it"; It's got to be a ' or a , out of place but I've looked at this code for a half hour straight, trying a variety of changes...and it still doesn't update the DB...Any help would be great. Thank you! -Corby-
OK, This is an Update that I have working, But what do I do, if the customer does not exist already it doesn't add the customer? How should I remedy this? if the customer does exist works great.
UPDATE AC SET CustId = Left (CustomerId,10), CustName = Left (CustomerName,25), Addr1 = Left (Address1,25), Addr2 = Left (Address2,25), City = Left (ca.City,15), Region = Left (State,2), PostalCode = Left (Zip,5) FROM RIO.dbo.tblArCust AC INNER JOIN (SELECT CustomerCode, MAX(LastUpdatedDate) MaxDate FROM COFFEE.dbo.vueCustomerAddress GROUP BY CustomerCode) V ON V.CustomerCode = AC.CustId INNER JOIN COFFEE.dbo.vueCustomerAddress CA ON CA.CustomerCode=V.CustomerCode AND MaxDate=LastUpdatedDate WHERE CA.addresstypeid = 1
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent) SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent] FROM StageMemberUploading WHERE ID = @numValues
SET @CurrentValue = (SELECT SCOPE_IDENTITY()) IF @ClientID IS NOT NULL BEGIN INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID) VALUES (@CurrentValue, @ClientID, @UsergroupID) END IF @DateOfBirth IS NOT NULL BEGIN INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue) VALUES (@CurrentValue, 1, @DateOfBirth) END -------------------My Code Stops here ------------------------------ IF @Male = 'x' BEGIN INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) VALUES (@CurrentValue, 2, 1) END IF @Female = 'x' BEGIN INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) VALUES (@CurrentValue, 2, 3) END
I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown. The C# code: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn); cmd.CommandType = CommandType.StoredProcedure; Label accomIdLabel = (Label)DetailsView1.FindControl("lblID"); int accomId = Convert.ToInt32(accomIdLabel.Text); cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId; cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath(); try { conn.Open(); cmd.ExecuteNonQuery(); } catch(Exception ex) { throw ex; } finally { conn.Close(); } The Stored Procedure: ALTER PROCEDURE developers.fort_SaveRedirectURL ( @accomId int, @path varchar(250) ) AS DECLARE @enabled bit, @oldpath varchar(250)
/* Ensure that the accommodation has been enabled */ SELECT @enabled = enabled FROM Experimental_Accommodation WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1) BEGIN /* Now check if a path already exists */ SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL BEGIN /* If Path already exists then we should keep the existing URL */ /* Otherwise, we need to insert a new one */ INSERT INTO Experimental_Adpages_Redirect (oldpath, accom_id) VALUES (@path,@accomId) END END RETURN
I have formview and I have a SqlDatasource for it.I have few textboxes in the edit mode and bind it to the data columns or fields in the database.If the data for all those fields have content in it, then it will update just fine. However, if one of the text field is null or empty, the formview can't be updated When i try to update with empty data in one textboxData field allows null value, and type are varchar.I am suspecting it's throwing an internal exception somewhere. However, since all the operations are handled by the asp.net. I have no idea what's going on internally. Does anyone have an idea what's causing this error and how to fix it?
Hi, I have the following query:UPDATE lista SET audio='3847f5e9-4ef7-42d7-9e57-e5cbad9131b1.jpg' WHERE id='13';If the id already exists it'll modify the row correctly as expected. But if the id doesn't exist at the table I want the row to be inserted anyway, but this is not happening. The same query works well in mysql + php. The table has an identity increment of 1 for the primary key also. Any ideas?Thanks in advance.
ssql.Append("INSERT INTO FINDINGS (Facility) ") ssql.Append("VALUES ('" & Facility & "')") Try Dim NewRow As Integer = dba.ExecuteSQL_Affected(ssql.ToString) Catch ex As Exception MsgBox("There was an error saving records.", MsgBoxStyle.Information, "No Key") Exit Sub End Try
Assessment.dtblFindings_Initialize()
Code Block Public Function ExecuteSQL_Affected(ByVal sSql As String) As Integer '//Execute the query like Insert, Update and delete Dim RowsAffected As Integer Try
If Conn.State = ConnectionState.Closed Then
Conn.ConnectionString = "Data Source=" & oDBConfig.LocalDBLocation & "" & oDBConfig.LocalDBName & ";" Conn.Open() End If Dim cmd As New SqlCeCommand(sSql, Conn) cmd.CommandType = CommandType.Text RowsAffected = cmd.ExecuteNonQuery() cmd.Dispose() Conn.Close() Return RowsAffected Catch err As SqlCeException
MsgBox(Utility.ComposeSqlErrorMessage(err)) Catch ComErr As Exception
MsgBox(ComErr.ToString, MsgBoxStyle.Information) Finally End Try End Function
Code Block Assessment Form
Public Sub dtblFindings_Initialize() Dim rdr As SqlCeDataReader Dim dba As New DBAccess Dim ssql As StringBuilder = New StringBuilder ssql.Append("SELECT Facility FROM FINDINGS") rdr = dba.OpenResultSet(ssql.ToString) Try
rdr.Read()
While rdr.Read ...
So here is the problem. The normal function is to initiate the insert by pression a button. That should go through all the steps then hit the dtblFindings_Initialize command and rebuild the datatable. However when it happens for the first time (i.e. the first facility going into the database), the SELECT statement always returns nothing.
If I stop the application and Pull the database to the desktop, the row has been inserted. So I feel that I am somehow doing something wrong, not closing something, not initializing something....argh! Please help!!
insert into [dbo].[tbl_FG_Alert_Count_All_Report] ([Date] ,[Count] ,[Rule Type]) Â Â SELECT TOP 10 [Date] Â Â Â ,[Count] Â Â Â ,[Rule Type] Â FROM [dbo].[tbl_FG_Alert_Count_All] where Count <>'0' and DATEDIFF(dy,date,GETDATE()) Â = 1 order by Date desc
When I ran this T-SQL statement in SSMS; I don't get any error and as expected, I can see new data in [dbo].
[tbl_FG_Alert_Count_All_Report] table.Â
Now I created one job with same T-SQL Statement. Job completes successfully with out giving any error message; But unfortunately I don't see any new data in [dbo].[tbl_FG_Alert_Count_All_Report] table.  What would be the reason that I don't see new data when job completes successfully but I can see new data after executing same T-SQL statement in SSMS?
Hi all: I have a list of items (actually a relation in which a user has selected an item, along with a rating for the item) in an Access database table, connected to my app with a SqlDataSource and bound to a repeater. The repeater displays the items to the user along with a dropdown box to show the rating, and allow the user to update it. The page connects and displays correctly. My problem is that when the user submits the page and I iterate through the repeater items to update each rating, the updates are not being completed in the database. The update works if I hard-code a value for the rating into the query itself, but not when using an updateparameter (pTaskRating below). In other words if I replace pTaskRating with '5', all the correct records will be found and have their ratings updated to 5. That means that the mySurveyId and pTaskId(DefaultValue) parameters have to be working, because the right records are found, but I can't seem to update records based on the DefaultValue of the pTaskRating parameter, even though I can verify that the DefaultValue is correct by placing a watch on it. It seems that my problem must be in my use of that particular parameter in the query, either in properties of the parameter or in the value assigned to it. I am extremely frustrated - any ideas would be greatly, greatly appreciated. Thanks! Bruck The table I'm pulling from and updating looks like this: SURVEY_ID (Text 50), TASK_ID (Long Int), RATING_ID (Long Int) Here's my ASPX for the main data source: <asp:SqlDataSource ID="sqlTaskSelections" runat="server" ConnectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source="abc.mdb";Persist Security Info=True;Jet OLEDB:Database Password=xyz' ProviderName="System.Data.OleDb" SelectCommand="SELECT [SURVEY_ID], [TASK_ID], [RATING_ID] FROM [TBL_TASK_SELECTION] WHERE [SURVEY_ID] = mySurveyId" UpdateCommand="UPDATE [TBL_TASK_SELECTION] SET [RATING_ID] = pTaskRating WHERE ([SURVEY_ID] = mySurveyId) AND ([TASK_ID] = pTaskId)"> <UpdateParameters>
<asp:SessionParameter Name="mySurveyId" SessionField="SurveyId" DefaultValue="" /><asp:Parameter Name="pTaskId" DefaultValue="" /><asp:Parameter Name="pTaskRating" DefaultValue="" /> </UpdateParameters> And here's the repeater (the Task ID and Rating are stored in hidden fields for easy access later): <asp:Repeater ID="rptTaskSelections" runat="server">
<FooterTemplate></td></tr></table></FooterTemplate> </asp:Repeater> And here's the page load and submit VB: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'BIND / LOAD RATINGS TO DROPDOWN BOXES HEREDim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentRating As HiddenFieldrptTaskSelections.DataSource = sqlTaskSelectionsrptTaskSelections.DataBind()
I've got a listbox that displays a list of employee's names. The employee number is the value stored in the listbox. I then have a vaccinations gridview that displays all the vaccinations received by the selected employee in the listbox. For some reason, when I click edit and modify a record and then click update it doesn't want to actually update the record. It just appears to do a postback and redisplay the record without any changes. My sqldatasource control is configured as follows: SelectCommand="SELECT * FROM [tblVaccinations] WHERE ([EmpNum] = @EmpNum)" @empnum = mylistbox.selectedvalue the update command is as follows: UpdateCommand="UPDATE [tblVaccinations] SET [EmpNum] = @EmpNum, [VacType] = @VacType, [VacIssueDate] = @VacIssueDate, [VacExpDate] = @VacExpDate, [VacInstitution] = @VacInstitution WHERE [VaccinationNum] = @VaccinationNum"
I'm using a performance monitor counter with an alert to catch merge replication conflicts. As you might have seen in my earlier psting this alert keeps running even if all conflicts have been resolved. After almost 2 days searching I found that in the table master..sysperfinfo there all the PM counters are stored still has the value 4 for conflicts/sec. When I manually create a new conflict this value becomes 5 which is nonsense since it's not the number of conflicts per second but the total number of conflicts since I started the server. If I check in PM it shows the correct value, whih of course is zero 99% of the time. Has anybody ever experienced the same kind of problem??
Or does anybody know a way of resetting the values in sysperfinfo. Even after allowing direct updates to systemtables I still can't modify the table.
Hello everybody, I can't perform an operation apparently very easy: set a field to a NULL value.
This is the db: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
This is the table: CREATE TABLE [ProgettoTracce] ( [ID_Progetto] [int] NOT NULL , [MisDifDef] [real] NULL , [MisDifMeas] [real] NULL , [MisDifAna] [real] NULL , [MisDifID] [real] NULL , [MisDifCV] [real] NULL ) ON [PRIMARY] GO
This is qry: UPDATE ProgettoTracce SET MisDifDef = NULL WHERE ID_Progetto = 3444
The qry has been performed with no error. Then I execute SELECT * FROM ProgettoTracce WHERE ID_Progetto = 3444 and I find the value I tried to overwrite with NULL. If I update with 0 (for example) it works. Obviously this happens on the production db, because on the development db the update with NULL works fine. No transaction is called, db options are the same on dbs...
I have this script in my database, but it always gives 2054 rows back and if I actually DO change something it doesn't even notice...
UPDATE a SET a.[omschrijving]=SP.[omschrijving] ,a.[verkoopprijs]=SP.[verkoopprijs] ,a.[gewijzigd]=getDate() FROM [artikelen] a LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP ON a.PartNrFabrikant = sp.PartNrFabrikant WHERE ((A.omschrijving != SP.[omschrijving]) OR (A.[verkoopprijs] != SP.[verkoopprijs]))
We recently migrated from SQL Server 7 to SQL Server 2005. Now there's a curious thing with some legacy applications. I have pasted some code below. Don't judge me, because like I said, it's legacy.
You can see, that I have two connection strings. One is commented and accessing via SQL Native Client. The other one is doing this through the old SQL Server driver. Funnily enough, when I use the new Native Client driver, the exception "Run-time error '-2147467259 (80004005)' [Microsoft][SQL Native Client]Invalid attribute value" is thrown in the rsPorder2.Update line. With the old driver, this works just alright.
Is this a bug? Is there a way, to make the code run, because we don't want to search the whole application for other occurances, if not necessary.
Any insights would be greatly appreciated.
Best regards, DD
Dim ilinx As New ADODB.Connection Dim rsPorder As New ADODB.Recordset Dim rsPorder2 As New ADODB.Recordset Dim cmdLinx As New ADODB.Command Dim strConn As String
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes] @newBatches as dbo.CreateBatchList READONLY , @productId int , @cLevelRatio int , @nLevelRatio int AS set nocount on;
Hi I and using gridview. And binding the data in the code behind.I need to use update command in code behind. How do I achieve this? I protected void lookUP (object sender, EventArgs e) { string strSql, strConn;
System.Text.StringBuilder whereClause = new System.Text.StringBuilder();
Where am I going wrong? I have no idea where to start to look for any incorrect code because it all looks ok! Please help!!! I am entering a record into 5 textboxes - I can see the new record when I open the datagrid view, but when I close the programme and reopen it the record disappears, this code looks ok to me so where is it wrong? Obviously the record is not being written to the database so that when I reopen the programme there is nothing to fill the dataset..........any ideas? Your help is appreciated!!!
Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click
I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.
I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.
Thanks
Sub btnUpdate_Click_1(sender As Object, e As EventArgs) Dim UserCode as String Dim PropertyCode as String Dim UnitCode as String Dim address1 as String Dim address2 as String Dim address3 as String Dim city as String Dim zip_Code as String Dim description as String Dim price_Range as String Dim state as String Dim type_Property as String Dim property_Status as String
if chkChangeState.Checked = True then state = ddl_State.SelectedItem.Value else state = lblState.Text end if
if chkPropertyType.Checked = True then type_Property = ddlPropertyType.SelectedItem.Value else type_Property = lblPropertyType.Text end if
if chkPropertyStatus.Checked = True then property_Status = ddlPropertyStatus.SelectedItem.Value else property_Status = lblPropertyStatus.Text end if
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@Address1, [Address2]=@Address2, [Address3]=@Address3, [City]=@City, [State]=@State, [Zip_Code]=@Zip_Code, [Type_Property]=@Type_Property, [Description]=@Description, [Property_Status]=@Property_Status, [Price_Range]=@Price_Range WHERE ([Property_db].[Code] = @Code) AND ([Property_db].[Prop_Code] = @Prop_Code) AND ([Property_db].[Unit_Code] = @Unit_Code)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_code.ParameterName = "@Code" dbParam_code.Value = UserCode dbParam_code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_code) Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_prop_Code.ParameterName = "@Prop_Code" dbParam_prop_Code.Value = PropertyCode dbParam_prop_Code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_prop_Code) Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_unit_Code.ParameterName = "@Unit_Code" dbParam_unit_Code.Value = UnitCode dbParam_unit_Code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_unit_Code) Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_address1.ParameterName = "@Address1" dbParam_address1.Value = txtAddress1.Text dbParam_address1.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_address1) Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_address2.ParameterName = "@Address2" dbParam_address2.Value = txtAddress2.Text dbParam_address2.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_address2) Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_address3.ParameterName = "@Address3" dbParam_address3.Value = txtAddress3.Text dbParam_address3.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_address3) Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_city.ParameterName = "@City" dbParam_city.Value = txtCity.Text dbParam_city.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_city) Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_state.ParameterName = "@State" dbParam_state.Value = state dbParam_state.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_state) Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_zip_Code.ParameterName = "@Zip_Code" dbParam_zip_Code.Value = txtZipCode.Text dbParam_zip_Code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_zip_Code) Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_type_Property.ParameterName = "@Type_Property" dbParam_type_Property.Value = type_Property dbParam_type_Property.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_type_Property) Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_description.ParameterName = "@Description" dbParam_description.Value = txtDescription.Text dbParam_description.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_description) Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_property_Status.ParameterName = "@Property_Status" dbParam_property_Status.Value = property_Status dbParam_property_Status.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_property_Status) Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_price_Range.ParameterName = "@Price_Range" dbParam_price_Range.Value = txtPriceRange.Text dbParam_price_Range.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_price_Range)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
'if Not rowsAffected then Response.Redirect("./editproperty.aspx") 'end if End Sub
Not sure what happened to my post, it seems to have disappeared. Here we go again. I have a stored procedure that I would like some feedback on, as I feel it may be inefficient as coded:
@ZUserID varchar(10) AS SET NOCOUNT ON
DECLARE @counter int SET @counter = 0 WHILE @counter < 10 BEGIN SET @counter = @counter + 1 IF EXISTS(SELECT * FROM tblWork WHERE UserID = @ZUserID And LineNumber = @counter) BEGIN UPDATE tblWork SET TransID = Null, TransCd = Null, InvoiceNo = Null, DatePaid = Null, Adjustment = Null, Vendor = Null, USExchRate = Null WHERE UserID = @ZUserID And LineNumber = @counter END ELSE INSERT INTO tblWork (LineNumber,TransCd,UserID) VALUES (@counter,'P',@ZUserID) END
Hi all My error is as follows: Incorrect syntax near '('.Line 27: acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)Line 28: acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)Line 29: acceptOrDeclineFriendship.Update()Line 30: Line 31: End Sub Bear with me... I have a page where i use a repeater control to list users who have requested to be friends with the currently online user. The 'getFriendRequests' query looks like this: SelectCommand="SELECT * FROM Friends, UserDetails WHERE (Friends.UserID = UserDetails.UserID) AND (FriendID = @UserID) AND (ApprovedByFriend = 'False') ORDER BY Friends.Requested DESC">This works. Within each repeater template, there are 2 buttons, 'Accept' or 'Decline', like this: <asp:Repeater ID="Repeater1" runat="server" DataSourceID="getFriendRequests"> <ItemTemplate> (other stuff like avatar and username etc) <asp:Button ID="accept" runat="server" Text="Accept" commandName="Accept" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/> <asp:Button ID="decline" runat="server" Text="Decline" commandName="Decline" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/> </ItemTemplate> </asp:Repeater> The code-behind (VB) which deals with this is as follows: Protected Sub Accept_Decline_Friends(ByVal sender As Object, ByVal e As CommandEventArgs)
'retrieve id of requestee and the answer accept/decline Dim friend_id As String = e.CommandArgument.ToString Dim answer As String = e.CommandName.ToString
'add the parameters acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer) acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id) acceptOrDeclineFriendship.Update()
End Sub Since the buttons are being created dynamically, this is how i track 1. the response from the currently logged in user 'Accept/Decline' and 2. who they are responding to (by their uniqueid) This relates to a sqlDataSource on my .aspx page like this: <!---- update query when user has accepted the friendship ----> <asp:SqlDataSource ID="acceptOrDeclineFriendship" runat="server" ConnectionString="<%$ xxx %>" UpdateCommand="UPDATE Friends SET (ApprovedByFriend = @Response) WHERE (FriendID = @UserID) AND (UserID = @FriendID)"> <UpdateParameters> <asp:ControlParameter Name="UserID" ControlID="userIdValue" /> </UpdateParameters> </asp:SqlDataSource> Which is meant to update my 'Friends' table to show that 'ApprovedByFriend' (the logged in user) is either 'Accept' or 'Decline', and record who's request was responded to. I hope this is clear, just trying to suppy all of the information! The error appears to be saying that I have an issue with my code-behind, where i am telling the sqlDataSource above to UPDATE. What I can say is that for each button in the repeater, the 2 variables 'friend_id' and 'answer' are picking up the correct values. Can anyone see any obvious problems here? Any help is very much appreciated as i am well and truley stuck!
hi there, i have been wrestling with this for quite a while, as in my other post http://forums.asp.net/t/1194975.aspx, what someone advised me was to put in try catch blocks ot see whats going on, problem is i have never really done it whit this kinda thing before, and i was wondering if someone could point me in the right direction.
For example where would i put the try catch block in here, to show me if its not working public int getLocationID(int ProductID, int StockLoc) { // Gets the LocationID (Shelf ID?) for the stock column and product id // passed // The SQL will look Something like: string strSQL; strSQL = "SELECT " + " location" + StockLoc + " " + "FROM " + " tbl_stock_part_multi_location " + "WHERE " + " stock_id = " + ProductID;string sConnectionString = "Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxxx"; SqlConnection objConnGetLocationID = new SqlConnection(sConnectionString);SqlCommand sqlCmdGetLocationID = new SqlCommand(strSQL, objConnGetLocationID); objConnGetLocationID.Open();int intLocation = Convert.ToInt32(sqlCmdGetLocationID.ExecuteScalar()); return intLocation; }
Is there any way I can use a variable from my code behind file in the UpdateCommand of a sqlDataSource? I have tried <%$ strUserGuid %>and<% strUserGuid %> any help appreciated.Thanks Dave
I have dataadapter and dataset that reads/writes to SQL tables. I can read. I can create "new" records. However, I have not been able to master the "updating" of an existing row. Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below. The code I using is below. I don't get error, but changes do not get written to SQL dbase. For starters, I think I "not" supposed to use the 2nd line(....NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row. thanks to any who can help
Hi all I have a GridView on an aspx page, that is enabled for editing, deletion and sorting. In the Page_Load event of the aspx page, i add a SqlDataSource to the page, and bind the source to the GridView. When i click the update, or delete button, it makes a PostBack, but nothing is affected. I'm sure this has got something to do with the parameters. First, i tried having the GridView.AutoGenerateColumns set to True. I have also tried adding the columns manually, but no affect here either. The code for setting the commands, and adding the SqlDataSource to the page are as follows: string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; string selectCommand = "SELECT * FROM rammekategori"; SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand); ds.ID = "RammeKategoriDS"; ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = @Kategoribeskrivelse WHERE (Kategorinavn = @Kategorinavn)"; ds.DeleteCommand = "DELETE FROM rammekategori WHERE (Kategorinavn = @Kategorinavn)"; Parameter Kategorinavn = new Parameter("Kategorinavn", TypeCode.String); Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse", TypeCode.String); ds.UpdateParameters.Add(Kategorinavn); ds.UpdateParameters.Add(Kategoribeskrivelse); ds.DeleteParameters.Add(Kategorinavn); Page.Controls.Add(ds); SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource; if (m_SqlDataSource != null) { this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID; } As mentioned - no affect at all! Thanks in advance - MartinHN
SSIS seems to automatically set the metadata type and for "typed" sources like database and XML connections it seems to take whatever the source column datatype is. If you use a cast or convert in the your source SQL query, it will not change the datatype of the metadata. This becomes an issue when doing things like merge joins on data from different sources and the join columns are different types (e.g. a ZipCode in one system is a varchar and it is an int in another system). I've been working around the issue by editing the XML code and changing the datatype there. Is there any way to do this through the GUI?
All: I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update. When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#. When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening? Here is the basic shell of my SP: CREATE PROCEDURE [dbo].[spHeader_InsertUpdate] @FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime AS Declare @rtncode int IF NOT EXISTS(select * from HeaderTable where FormID=@FID) Begin begin transaction --Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE Begin begin transaction --Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID; --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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>
