Updating Colunm
Mar 18, 2008
Hello
Can some body help me in performing this?
I want to move the data from Colunm Value to Colunm CPublicID whenever CCField = 'PublicID' as shown below in green color( Without using Cursor or While loops)
CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
101 XYZ PublicID Dimension Flag 103
101 XYZ PublicID Dimension Key 103
101 XYZ PublicID Dimension Flag 230
101 XYZ PublicID Dimension Key 230
101 XYZ PublicID Dimension Flag 5691
101 XYZ PublicID Dimension Key 5691
CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
103 XYZ PublicID Dimension Flag 103
103 XYZ PublicID Dimension Key 103
230 XYZ PublicID Dimension Flag 230
230 XYZ PublicID Dimension Key 230
5691 XYZ PublicID Dimension Flag 5691
5691 XYZ PublicID Dimension Key 5691
Also , I need to know the best practice to perform the below process. Which of them is advisable?
I have a table, say as above, which has lots of data, say 100,000 records. I need to look at each row and perform certain updates/ trasnformation to a colunm (Value). I aslo, need to hold the old colunm. I came up with two method
Process A) Clone the table. So I have table2 which is a copy of table1. Read the Table1, update / trasnfored the Table2
Table1 Colunm(Value) will have Old values and table 2 colunm(Value) will have transformed values
Process B) Clone the Colunm. So I have a colunm Value and its clone Value2. Read the table and value for Colunm Value and Update the Value2.
I am using process A. because I beleive , process B, reading and updating same table make sthe entire process slow.
Please advice on th ebest industrial practice of doing this.
Thanks in advance for all the help
View 5 Replies
ADVERTISEMENT
Mar 15, 2007
Afternoon all,
I have a colunm sName(nVarChar(255) which has first name and second name in it and is divended with a ",". Can somebody show me to put these in there own colunm sFirstName and sSecondName.
Very Kind regards.
View 1 Replies
View Related
Nov 13, 2007
Hello All
I want to creat a colunm from two colunms in a specific format
COL1 COL2 COL3
123456 01-10-2007 12070001 ( 12: From First two digit of Col1, 07 from the year of colunm2 and 0001: if combination 1207 is first else increment)
125647 01-11-2005 12050001
126756 12-25-2007 12070002 (* here since combination of 1207 is repeating it gets increment to 0002)
Can somebody help me please
View 8 Replies
View Related
Jun 15, 2006
hi
i wanted to crete an extra colunm when i retrive a set of rows...
the date colunm must show the current date eg 15/06/2006
currently im writing it as follows
select cost, '15/06/2006' as CDate
from prices
as you can see this is static date i need the date to be current whenever the query is done.
View 4 Replies
View Related
Apr 25, 2007
Afternoon all.
I have a table which has a column with has been inserting incorrect data, here is an example. Column name is description and table name is tblClientUsage:
[companyname]: 288bsl | | 13/03/2007
[companyname]: 288asl | | 08/03/2007
[companyname]: RES04sl | | 19/02/2004
[companyname]: RES01sl | | 19/02/2004
[companyname]: AAsl | | 31/12/2005
[companyname]: 652Asl | | 05/02/2007
[companyname]: GAZ1sl | | 20/03/2007
[companyname]: AAsl | | 31/12/1999
[companyname]: 363asl | | 20/01/2007
[companyname]: 288b1sl | | 02/11/2006
[companyname]: 288bsl | | 02/11/2006
Now what has been happening is that is has not been putting the company name in the column, just the text companyname. Now i have a table which has the company name and company id, i can easily join the two tables together but how can i replace just the companyname text in the description column with leaving the text after.
Any help would be great
View 3 Replies
View Related
May 22, 2006
When I submit a sql :
select * from view from column=''
the table is a view point to oracle table with ole db provider for oracle
In mssql2000, the result return rows which containing a space ' '
However, in mssql2005, the result return no rows .
Any advise on it?
View 6 Replies
View Related
Sep 21, 2006
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.
Any suggestions?
View 7 Replies
View Related
Aug 24, 2007
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'
View 1 Replies
View Related
Feb 24, 2008
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
View 5 Replies
View Related
Apr 13, 2008
guys can anyone know whats wrong with my syntax? I always get this error: ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.This is the syntax: Protected Sub btnupd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnupd.Click Dim pwing, pblood, pcolor, plcolor, pmark, pbdate, pstat, premark, ppefr, pstyle As Object Dim sql As String Dim y As New Object y = GVh.SelectedDataKey(0) pwing = txtwingb.Text pblood = txtblood.Text pcolor = txtcolor.Text plcolor = txtlcolor.Text pmark = txtmark.Text pbdate = ddday.SelectedValue & "-" & Ddmonth.SelectedValue & "-" & ddyear.SelectedValue pstat = txtstat.Text premark = txtremark.Text sql = "UPDATE [broodhen]" sql = sql & " SET bwing = '" & pwing & "'," sql = sql & " bloodlines = '" & pblood & "'," sql = sql & " color = '" & pcolor & "'," sql = sql & " lcolor = '" & plcolor & "'," sql = sql & " mark = '" & pmark & "'," sql = sql & " bday = '" & pbdate & "'," sql = sql & " status = '" & pstat & "'," sql = sql & " remarks = '" & premark & "'" sql = sql & " WHERE(bwing = '@wwing')" DShen.UpdateCommand = sql DShen.Update() MView.SetActiveView(GView) End SubThat is from my aspx.vb file and for the .aspx my datasource:<asp:SqlDataSource ID="DShen" runat="server" ConnectionString="<%$ ConnectionStrings:CSD %>" ProviderName="<%$ ConnectionStrings:CSD.ProviderName %>" SelectCommand="SELECT * FROM [broodhen]" > </asp:SqlDataSource> Note: I select records from a gridview to edit. i can delete, insert, select but i cant update. please help... tnx.
View 3 Replies
View Related
Jul 28, 2005
I am trying to update a SQL Server DB via my ASP.NET application with the following SqlCommand:
UPDATE StudentCourses
SET SC_Active = 0
WHERE (UT_ID IN (@UT_IDs)) AND (CO_ID = @CO_ID)
@UT_IDs in populated from a StringBuilder object with the following:
354,284,305,281,308,351,439,355,306,282
When I run the application to update the DB, I get the error:
System.FormatException: Input string was not in a correct format.
If I change @UT_IDs to:
354
if works.
Can anyone explain this behaviour?
Cheers,
ocann
View 1 Replies
View Related
Feb 8, 2001
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
View 1 Replies
View Related
Aug 30, 2005
I have a query that updates
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?
View 2 Replies
View Related
Nov 29, 2007
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
View 1 Replies
View Related
Mar 25, 2008
Hey
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());
try
{
con.Open();
comUpdateTicket.ExecuteNonQuery();
}
catch (Exception ex)
{
this.lblMessage.Text = "Data save error: " + ex.Message.ToString();
this.pnlMessage.Visible = true;
}
finally
{
con.Close();
}
}
----------------------------------------------------
dbo.sp_UpdateHelpDeskIssues
(
@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.
Please send help! Thanks.
View 2 Replies
View Related
Jul 17, 2007
Hi gurus,
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.
Anyone who can give me some feedback?
with regards, Arthur
View 15 Replies
View Related
Jun 27, 2006
Hi
I come from a Unix backgound and used Informix ISQL quite frequently.
I have a row that contains a compund field 'cust_and_date' and contains a value of C0003 32656
I want to write an update statement that will amend the C0003 to C00009 but leaves the 32656 as is.
I tried the following:-
update scheme.slitemm set substring(cust_and_date,1,8) = 'C00009'
This returned a syntax error
Any help gratefully received
View 5 Replies
View Related
Dec 12, 2006
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
Code:
View 1 Replies
View Related
Dec 13, 2006
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(); }}
View 4 Replies
View Related
Mar 6, 2007
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.
View 2 Replies
View Related
Mar 7, 2007
Hi All:
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
View 6 Replies
View Related
Jun 13, 2007
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
View 2 Replies
View Related
Jun 26, 2007
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?
View 3 Replies
View Related
Aug 24, 2007
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?
View 2 Replies
View Related
Nov 5, 2007
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(); } }}
View 7 Replies
View Related
Nov 6, 2007
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
View 1 Replies
View Related
Jan 19, 2008
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
View 5 Replies
View Related
Jan 29, 2008
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! :)
View 3 Replies
View Related
Feb 25, 2008
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
View 3 Replies
View Related
Mar 28, 2008
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?
View 6 Replies
View Related
Apr 18, 2008
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.
View 1 Replies
View Related
May 14, 2008
Hi!
I am trying to retrieve and update data. I am retrieving the data into the respective controls such as textboxes. The codes are follow:
Retrieve data - working
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=" & "@ID", sqlConn)
Dim paramID As New SqlParameter("@ID", SqlDbType.Int)
paramID.Direction = ParameterDirection.Input
com.Parameters.Add(paramID)
paramID.Value = e.Value
sqlConn.Open()
Dim reader As SqlDataReader = com.ExecuteReader()
While reader.Read()
StartTimeTextBox.Text = reader("StartTime")
EndTimeTextBox.Text = reader("EndTime")
CompanyNameTextBox.Text = reader("CompanyName")
PurposeTextBox.Text = reader("Purpose")
AccountManagerTextBox.Text = reader("AccountManager")
PresenterTextBox.Text = reader("Presenter")
ColorDDL.SelectedValue = reader("ColorCode")
StatusRadioButtonList.SelectedValue = reader("Status")
CommentTextBox.Text = reader("Comments")
End While
update the data - NOT WORKING
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim cmd As SqlCommand = New SqlCommand("UPDATE Demo_Theatre_DB SET StartTime = @StartTime, EndTime = @EndTime, CompanyName = @CompanyName, Purpose = @Purpose, AccountManager = @AccountManager, Presenter = @Presenter, ColorCode = @ColorCode, Status = @Status, Comments = @Comments WHERE ID=@ID")
cmd.Parameters.AddWithValue("StartTime", StartTimeTextBox.Text)
cmd.Parameters.AddWithValue("EndTime", EndTimeTextBox.Text)
cmd.Parameters.AddWithValue("CompanyName", CompanyNameTextBox.Text)
cmd.Parameters.AddWithValue("Purpose", PurposeTextBox.Text)
cmd.Parameters.AddWithValue("AccountManager", AccountManagerTextBox.Text)
cmd.Parameters.AddWithValue("Presenter", PresenterTextBox.Text)
cmd.Parameters.AddWithValue("ColorCode", ColorDDL.SelectedValue)
cmd.Parameters.AddWithValue("Status", StatusRadioButtonList.SelectedValue)
cmd.Parameters.AddWithValue("Comments", CommentTextBox.Text)
Try
sqlConn.Open()
cmd.ExecuteScalar()
Catch ex As Exception
InsertMsgLabel.Text = ex.ToString
End Try
End Sub
I am getting the following error whenever I click update button.
"ExecuteScalar: Connection property has not been initialized"
Your help will be appriciated!
Thanks!
View 24 Replies
View Related
May 16, 2008
HelloI have a problem when I want to update my textbox, I have a textbox called Textbox1, when I Insert data from it to database it is working well, even when I Update it WITHOUT SELECT field from database it is working well again,but when I Select [pagename] from database then want to Update it it is not working, nothing happend and in debuging the textbox doesnt have any value. what shoud i do? the value become null? Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Label1.Text = Request("Id").ToString() Dim conname As String = ConfigurationManager.ConnectionStrings("IPbrandConnectionString").ConnectionString() Dim con As New SqlConnection(conname) Dim sql As String = "SELECT [Id], [pagename] FROM [content] WHERE ([Id] = @Id)" Dim cmd As New SqlCommand(sql, con) cmd.Parameters.Add(New SqlParameter("@Id", SqlDbType.Int, 32)) cmd.Parameters("@Id").Value = Label1.Text.ToString() con.Open() Dim reader As SqlDataReader = cmd.ExecuteReader() Do While reader.Read() TextBox1.Text = reader("pagename") Loop reader.Close() con.Close() End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conname1 As String = ConfigurationManager.ConnectionStrings("IPbrandConnectionString").ConnectionString() Dim con1 As New SqlConnection(conname1) Dim sql1 As String = "UPDATE [content] SET [pagename] = @pagename WHERE [Id] = @Id " Dim cmd1 As New SqlCommand(sql1, con1) cmd1.Parameters.Add(New SqlParameter("@Id", SqlDbType.Int, 32)) cmd1.Parameters("@Id").Value = Label1.Text.ToString() cmd1.Parameters.Add(New SqlParameter("@pagename", SqlDbType.VarChar, 50)) cmd1.Parameters("@pagename").Value = TextBox1.Text.ToString() cmd1.UpdatedRowSource = UpdateRowSource.OutputParameters Try con1.Open() cmd1.ExecuteNonQuery() Catch err As SqlException Throw New ApplicationException("Data error.") Finally con1.Close() End Try Server.Transfer("main.aspx")
View 2 Replies
View Related
