I have a data flow task, and in that is an OLE DB Source that upon success, connects to an OLE DB Command. The OLE DB Source runs a sql command from a variable. (I've tested the sql; it parses and returns values.) The OLE DB Command is a very simple update sql statement with one column mapping. When I execute the task, it says it is successful, but when I check the table, nothing has been updated. I have ensured the connection is successful, and I am connecting to the correct db. Any suggestions would be helpful! Thanks!
I wrote a sproc which does four things: 1. It looks at an option master to see if the record exists before inserting a new one 2. If the record is not there it inserts the optino record 3. Once the record is inserted I have to run a CASE statement on the record to determine its level 4. Once the level is determined, the record needs to be updated with the correct level. 1-3 work fine (when run without the update command). However, even though I set a criteria, UPDATE still updates and not the one records. Any idea why? set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)
BEGIN SELECT ' This option already exists in your Option Master' END ELSE BEGIN
--if the option record option does not exist, insert it
--once the option record is inserted, case it to find the its level (1-9) --update the record with the approciate level.
UPDATE Optionmaster SET optionlevel (
SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND ElevationID > '0' THEN '1' END AS OptionLevel --provides the option level required to update the record FROM optionmaster WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)) --even through I specify the above criteria, it upates all records.
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
I am trying to make a stored procedure in my website for updating an address:1 CREATE PROCEDURE dbo.UPDATE 2 ( 3 @add NVarchar(50), 4 @cit NVarchar(50), 5 @state NVarchar(50), 6 @zip NVarchar(50), 7 @CNum int 8 ) 9 10 UPDATE table_name 11 AppAdd = @add, AppCity = @cit, AppState = @state, AppZip = @zip 12 WHERE CertNum = @CNum When I try to save it it give me an error: Incorrect syntax near keyword 'UPDATE'Must declare scalar variable '@add'
Hi, I'm new to ASP.Net quite new to C# (My first attempt at a database website) and am trying to get a button to add "1" to "int" value called "Comments" a each time its pressed basically counting each time a comment is added. I also only wnat it to affect the row where "ModID" in my database is equal to the query string "ModID" I'm using on the page. I cannot find any tutorials so this is my best guess so far. This is probably a Noobie type stupid question but I'm stuck. This is the code I have so far for my Button_Click event: protected void Button_Click(object sender, EventArgs e){ SqlDataSource CommentCountDataSource = new SqlDataSource(); CommentCountDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["Main_Site_DatabaseConnectionString1"].ToString(); CommentCountDataSource.UpdateCommandType = SqlDataSourceCommandType.Text; CommentCountDataSource.SelectCommand = "SELECT (ModID, DateTimeLastComment, Comments) FROM Mods"; CommentCountDataSource.UpdateCommand = "UPDATE Mods SET (DateTimeLastComment=@DateTimeLastComment, Comments=@Comments) WHERE ModID=@ModID"; //How do i get the Where to use the query string info? CommentCountDataSource.UpdateParameters.Add("DateTimeLastComment", DateTime.Now.ToString()); CommentCountDataSource.UpdateParameters.Add("Comments", "10"); //"10" is just a value to test I'll change this to add "1" once I figure how. CommentCountDataSource.Update();} Sorry if I'm using the wrong lingo but as I say I'm new. If my code is a mile off then please can you send me in the right direction of some code that works.Thanks in advance if anyone can help me. Cheers,Alan
I am a DBA who is moving in the direction of minor database design. I have gone through the steps to get my tables normalized, and I am ready to get them set up so they will update from table to table. How do I set this up? I know SQL does not cascade like Access, so how is this done? From what I have read (Robert Vieira's book) I should use triggers? Any help would be appreciated. Dallas
strSQL = "UPDATE customers SET " strSQL &= "entryid = '" & strtheEntryid & " ' " strSQL &= "WHERE id =1 " right now in table id is my primary key field it increments by 1, I want to say get first record instead of saying id = 1, how to do that?
I have some code to hack in c#.. I have a database and when a form (C#) is loaded a copy of the table is made in a DataTable object. The form has a list box showing just the one column of info from the local DataTable. On the form it is possible to change a record, delete a record and add a record. These are all done in the local table.
The question, how do i send these changes back to the database. Currently it deletes the lot and copies all the local table values in. How do only do the affected row, UPDATE, INSERT etc
I have an ASP application that uses a stored procedure and ADO.net to update a sql server data file. The problem is I know the code is working, I don't have any errors with the ADO, no exceptions are caught. I can use the same basic code to insert a record using a different procedure. It is the update procedure that does not carry through.
So, I know I have a connection, the procedure works using the query builder directly so the procedure works, but when I run the code, I get no errors and no update to the datafile. I am not even sure how to trouble shoot this since I don't have an error to look up.
C# Code:-------------
private void UpdateIssue() { DateTime date = new DateTime(); date = Convert.ToDateTime(this.txtDate.Text);
//edit record in HelpDeskIssuesTbl here.
SqlConnection con = new SqlConnection("Data Source..."); SqlCommand comUpdateTicket = new SqlCommand("sp_UpdateHelpDeskIssues", con); comUpdateTicket.CommandType = CommandType.StoredProcedure; comUpdateTicket.Parameters.Add("@IssueID", this.GridView1.SelectedIndex.ToString()); comUpdateTicket.Parameters.Add("@EmpID", this.ddlEmployee.SelectedValue.ToString()); comUpdateTicket.Parameters.Add("@Date", date.ToShortDateString()); comUpdateTicket.Parameters.Add("@StatusID", this.ddlStatus.SelectedValue.ToString());
( @IssueID int, @EmpID int, @Date datetime, @StatusID int )
AS UPDATE HelpDeskIssuesTbl SET EmployeeID = @EmpID, IssueDate = @Date, IssueStatusID = @StatusID WHERE (IssueID = @IssueID) RETURN
Like I said the Stored Procedure does work when I run it directly in Visual Studio. I have double checked all the params and they all match up unless I am missing something.
My feeling with the SCD component is not that very solid. I have the feeling that the behavior of the insert/update strategy is not always correct an working. I will describe two problems that i encounter.
1. My destination table contains records with the value ''. Cause i don't want '' ( 2 single quotes) in our DWH i update the view that is the source with a case statement that changes the '' to NULL. But when i run the packages the '' values are not update with the NULL values. When i delete the destination table and run the package, the records are inserted with the NULL value as expected. Anyone who has experienced this problem?
2. When i create a new table and run the package so the destination table gets filled with records the SCD will insert alle records (for example 100). When i start the run directly after the first run, all records are updated instead of doing nothing what it should do cause all records exists.
I am looking for a way to update a sqldatasource what I have is a ASP Wizard applicationstep oneis a dataview with the select ability it displays an ID and Namein step two what i want it to do is take the ID from step ones select and put that into the where clause so I have select * from table where id = step1selectedID
I am trying to update a users status from Pending to either Approved or Rejected. I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{ protected void ApproveButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Approved"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); } protected void DenyButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Rejected"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); }}
I have this web store that I have been creating. When a customer goes to check out, he has to log in, then he is redirected to a page where he can view/add/or edit shipping and billing address. I have based all the sql statements on the profile username, adding records and retrieving them works just fine. When I go to change something in the info it uses an sql statement that updates based on "Where AccountUserName = @AccountUserName", I have @AccountUserName set to Profile("Username"). Keep in mind this works fine for adding new or bring up current records. I even put in code in the updated event for the sql data source to post a msgbox telling me how many rows were affected, it says 1 even though I dont see any change in the data. What am I doing wrong here, it's driving me nuts, its just a very simple update.
Appreciate your efforts in answering queries of so many newbees!I hope to find answering my query..I have created a logon screen to which i have also given the option of changing the password ... Now below is the code for updating the new password given by the user ....Imports System.Data.SqlClient Dim con As New SqlConnection("server=sys2;initial catalog=kris;integrated security=SSPI") Dim cmd As New SqlCommand("select * from u_login", con) Dim dr As SqlDataReader Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click con.Open() dr = cmd.ExecuteReader While dr.Read() If dr(0) = txtEmail.Text And dr(1) = txtoldpwd.Text Then Dim NewPwdStr As String = txtnewpwd.Text Dim OldPwdStr As String = txtoldpwd.Text Dim sqlstr As String = "Update U_Login set pwd = ('" & NewPwdStr & "') Where pwd = '" & OldPwdStr & "'" Dim cmd1 As New SqlCommand("sqlstr", con) cmd1.ExecuteNonQuery() Response.Write(" Password Changed ... Please login again") End If End While dr.Close() con.Close() End Sub The above code although doesnt throw any error however it shows a blank screen and doesnt even update the new password. Can you plz help me understand what could possibly be wrong in my code n why is that am getting the blank screen. Your help will be highly appreciated!Thanks,Brandy
I am in a fix where I cant seem to update my records. I am trying to follow a template for an Event calendar and cant seem to get the update page working correctly. basically the update on the server never occurs but the message is still returned. Very confused is it my stored procedure? Imports System.Data Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Bind() End SubSub Bind()Using Conn As New SqlConnection("Data Source=WINSERV;Initial Catalog=EventCalendar;User ID=antonio;Password=xxxx;User Instance=False") Conn.Open()Dim cmd As New SqlCommand("sp_GetEvent", Conn) cmd.CommandType = Data.CommandType.StoredProcedure Dim ParameterID As New SqlParameter("@Event_ID", SqlDbType.Int, 4)ParameterID.Value = Request.QueryString("Event_ID") cmd.Parameters.Add(ParameterID)Dim myDataReader As SqlDataReader myDataReader = cmd.ExecuteReader myDataReader.Read()txtapeDate.Text = myDataReader.Item("apeDate") txtapEvent.Text = myDataReader.Item("apEvent")txtWho.Text = myDataReader.Item("Who") txt_type.Text = myDataReader.Item("_type")txtEvent_ID.Text = myDataReader.Item("Event_ID") myDataReader.Close() Conn.Close() End UsingEnd Sub Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim APEvent As String = txtapEvent.Text Dim Who As String = txtWho.TextDim apeDate As String = txtapeDate.Text Dim Event_ID As Integer = txtEvent_ID.TextDim _type As Integer = txt_type.Text 'add it to the DBUsing Conn As New SqlConnection("Data Source=WINSERV;Initial Catalog=EventCalendar;User ID=antonio;Password=xxxx;User Instance=False") Conn.Open()Dim cmd As New SqlCommand("sp_editSingleEvent", Conn) cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@Who", Who) cmd.Parameters.AddWithValue("@apEvent", APEvent)cmd.Parameters.AddWithValue("@apeDate", apeDate) cmd.Parameters.AddWithValue("@_type", _type)cmd.Parameters.AddWithValue("@Event_ID", Event_ID) cmd.Parameters.Add(New SqlParameter("@message", SqlDbType.VarChar, 200))cmd.Parameters("@message").Direction = ParameterDirection.Output cmd.ExecuteNonQuery()Dim sMessage As String = Convert.ToString(cmd.Parameters("@message").Value) Conn.Close() lblMessage.Text = sMessage End Using End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Response.Redirect("MainEvent.aspx")End Sub End Class ===========Stored Procedure==========USE [EventCalendar] GO /****** Object: StoredProcedure [dbo].[sp_EditSingleEvent] Script Date: 06/13/2007 15:15:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER PROCEDURE [dbo].[sp_EditSingleEvent] -- Add the parameters for the stored procedure here @apeDate smalldatetime, @apEvent nvarchar(250), @who nvarchar (50), @Event_ID int, @message nvarchar (200)OUTPUT,@_type int AS Begin Set @Message = 'Hasnt added yet' UPDATE apEvent SET apeDate=@apeDate, apEvent=@apEvent, Who=@Who, _type=@_type WHERE Event_ID = @Event_ID Set @message = 'its added' END
CREATE PROCEDURE discussions_DeletePost@post_id as intAS--prepare the variable to hold the topic_id of the post we are deletingDECLARE @topic_id int--get the topic_id from the post we are going to deleteSET @topic_id = (SELECT topic_id from discussions_PostsWHERE post_id = @post_id)--now that we now what topic the post was in, we can delete the postDELETE FROM discussions_PostsWHERE post_id = @post_id--update topics, and just in case, if the post we deleted was the last post from the topic, give the topic a new last post_idUPDATE discussions_Topics SET topic_last_post_id = (SELECT MAX(post_id) FROM discussons_Posts WHERE topic_id = @topic_id) WHERE topic_id = @topic_id RETURN The problem with this is that, if I delete the last post from the topic, the topic_last_post_id feild does not update with a new post_id Any help?
Hi I'm new to all of this. I have a database that holds customer information (fictitious) and i can select that data and display it in a set of textboxes. I also have an SQL command "UPDATE" that is designed to update the text field that i want to edit. However the problem i'm having is that it'll let me write the info in the textbox but as soon as i click my update button it just flashses and goes back to what it says before e.g. FIRST NAME: LEE i enter TOM and then it reverts it back to LEE This is my SQL UPDATE command statement:
UPDATE CustomerDetails SET [First Name] = @firstnameTextBox, [Last Name] = @lastnameTextBox, [Address line 1] = @addressTextBox, Town = @townTextBox, Postcode = '@postcodeTextBox', [Tel Number] = '@telephoneTextBox' 1 1 2 2 Partial Class Update 3 3 Inherits System.Web.UI.Page 4 4 5 5 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 6 6 custIDTextBox.Text = Session("Label2") 7 7 8 8 Dim updatepage As System.Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView) 9 9 10 10 For Each update As Data.DataRow In updatepage.Table.Rows 11 11 12 12 firstnameTextbox.Text = update.Item("First Name").ToString 13 13 lastnameTextBox.Text = update.Item("Last Name").ToString 14 14 addressTextBox.Text = update.Item("Address Line 1").ToString 15 15 townTextBox.Text = update.Item("Town").ToString 16 16 postcodeTextBox.Text = update.Item("Postcode").ToString 17 17 telephoneTextBox.Text = update.Item("Tel Number").ToString 18 18 19 19 Next 20 20 21 21 End Sub 22 22 23 23 Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles updatebutton.Click 24 24 25 25 'Dim parameters firstnameTextBox, lastnameTextBox, addressTextBox, townTextBox, postcodeTextBox, telephoneTextBox 26 26 'Dim UpdateParameters As QueryStringParameter 27 27 28 28 SqlDataSource1.Update() 29 29 SqlDataSource1.UpdateParameters.Add("@CustomerDetails", System.TypeCode.String) 30 30 'SqlDataSource1.UpdateParameters.Add("@Last Name", System.TypeCode.String) 31 31 'SqlDataSource1.UpdateParameters.Add("@Address line 1", System.TypeCode.String) 32 32 'SqlDataSource1.UpdateParameters.Add("@Town", System.TypeCode.String) 33 33 'SqlDataSource1.UpdateParameters.Add("@Postcode", System.TypeCode.String) 34 34 'SqlDataSource1.UpdateParameters.Add("@Tel Number", System.TypeCode.String) 35 35 36 36 'Label2.Text = ("Update successful") 37 37 End Sub 38 38 End Class 39 39 40 41 42 anyone?
Hi,I am currently updating a column in my database table with the value typed into a text box. But it is updating with the new value, rather than taking away the value typed in from the present value of the column. 1. How would i subtract the value typed in away from the current column value?2. I guess I need to check the column value is above 0 before updating, then rebind the control to show new values after the update has happened, incase two people are using it at once. How would this be achived? Thanks for any help!!Jon P.S.My code is:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable = @qty WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); con.Close(); return true;} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@User)"+ "INSERT INTO Transactions (Itemid)VALUES (@productID)"+ "INSERT INTO Transactions (itemname)VALUES (@Itemsname)"+ "INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+ "INSERT INTO Transactions (Qty)VALUES (@qty)"+ command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.Parameters.Add("@qty", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); return true; }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Update") { SqlDataSource1.Insert(); } }}
Hi,Its probably simple but.. How do I update a column by just '1'..for example - heres my code: protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(); con.ConnectionString = "HiddenConnection"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; Label productIDLabel = (Label)DataList1.FindControl("productIDLabel"); command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID"; command.Parameters.Add("@productID", productIDLabel.Text); command.ExecuteNonQuery(); con.Close(); command.Dispose(); } As you can see in the bold text, I want to add 1 to the numberclickedin column.. and in my primative way Ive just typed +1What should I use instead?Thanks in advance,Jon
Consider i have 2 database namely database1 and database2 . both the databases are in the same server database1 has a following tables 1.the table "class" has following fields and many more fields. consider 50 fields but for the use of example i have given only three fields name and sample values. class studentname rollno (table name - class) 8th std aaaaa 100 2. the table "Fees" has the following fields and many more fields. consider 25 field but for the use of example i have gievn only 2 fields name and sample values rollno fees (table name - fees) 100 50000 101 25000 Now i have created the following tables in database2 the table "class" has the following fields only, class studentname rollnothe table "fees" has the following fields only. rollno fess Question ?please let me know if there is any tool or method . to transfer values from database1 to databse2
Hello How do u update the datafield of a row using buttonClick in vb? I have a details view whereby it will display the username and timeleft once log in. currently the datafield, timeleft on one user in the database is null. I'm using sqldatasource. Once log in, it will direct to this page where the user enters a code on the textbox and click the enter button. This will cause an update on the user's timeleft. But the code must not be entered to the database. For eg. If code = 1111 then 'update timeleft = 60 The timeleft is in minutes. 60 will be stored as double in the user's row in the database. How do i do this? Please help asap. Thanks! :)
I have two textboxes on the page...shipdate and duedate. When the page loads, shipdate has today's date loaded and duedate has a date that's 28 days later than today. When I change the dates and submit it's not updating in the database instead I'm getting the two dates in pageload. What am I doing wrong?Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
ShipDateTxt.Text = Today() DueDateTxt.Text = DateAdd(DateInterval.Day, 28, Today()) End SubProtected Sub LoanRequest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LoanRequest.Click Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)Dim cmd As New Data.SqlClient.SqlCommandWith cmd .Connection = conn .CommandType = Data.CommandType.StoredProcedure .CommandText = "UpdateloanerInfo".Parameters.AddWithValue("@requestorid", Integer.Parse(Request.QueryString("requestorid"))) .Parameters.AddWithValue("@shipdate", ShipDateTxt.Text).Parameters.AddWithValue("@duedate", DueDateTxt.Text) End With Here's updateloanerinfo stored procedure: @requestorid int,@shipdate datetime,@duedate datetime AS update LibraryRequest set [shipdate] = @shipdate,[duedate] = @duedate Where requestorid=@requestorid
Hey guys, I periodically have to update maybe 300 records of the same column.. However, I've been udpating each record one by one. Is there a way I can do something the following.ID Name1 john2 chris3 adam4 ben5 steveupdate table1 set name = name where id = ? but have it run through an entire set of these? Is there any way of automating that rather than having to update it manually? Do i need another table with just the id numbers and the column I want to change? or do i need a txt file?
building some kind of ecommerce site. I want to allow the user to modify the image that he has stored for a product. The image is stored in a directory, in the table GAMME I only have the image name. When the user selects a new Image, I first delete the old image in the directory, then save the new image, but what I can't do is to update the table with the new image name, when I write sqldatasource1.update() I have an error "the dictionnary passed with old values is empty"(translated from frengh). the update statement in sqldatasource1 is: " UPDATE Gamme SET imagename = @imagename, imagesize = @imagesize WHERE (product_ID = @original_product_ID)"
on events sqldatasource1.updating I have written: e.Command.Parameters("@imagename").Value = (FileUpload1.FileName).ToString() e.Command.Parameters("@taillevignette").Value = FileUpload1.PostedFile.ContentLength e.Command.Parameters("@original_product_ID").Value = Session("Product_ID").ToString this is not written but the session("product_ID") is the good value the problem must come from the last lign, but I don't Understand the problem and how to solve it.
