After the records are inserted I need to add the amt's where freefield3 equals rebate then subtract that sum from the amt where account = 1444. So my final result would be to have 80.00 in the amt field where account equals 1444
Hi, I have a concern about adding a new field to a table with image field - which is huge. Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field? Previously this happened to me, and what was advised to put all the big fields at the end of the table. Thanks.
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
I need to add a field to a query which i have created in SQL Server, i have done this in Access no problem and it works fine but when icopy the SQL code from Access to SQL it just falls over. This is my code in Access...
SELECT AIMPDTA_CDADDR.CDCUST, AIMPDTA_CDADDR.CDADDC, AIMPDTA_CDADDR.CDNAME, AIMPDTA_CDADDR.CDADD1, AIMPDTA_CDADDR.CDADD2, AIMPDTA_CDADDR.CDADD3, [CDCUST] & [CDADDC] AS Expr1 FROM AIMPDTA_CDADDR LEFT JOIN AIMPDTA_ASAREA ON AIMPDTA_CDADDR.CDNAME = AIMPDTA_ASAREA.ASNAME WHERE (((AIMPDTA_CDADDR.CDADDC)<>" "));
I have this problem: I'm creating a weblinked database using Access and ASP. The problem is that the fields in the table won't be always the same. E.g. sometimes there will be need for an extra field, but after a while it may not be necessary anymore. Users should be able to add a field to the table (and if possible delete one later on)
I don't whether it is possible to use sql to add a field to an accessdbase. Or is there another way to solve this problem?
thanks for your reply, yours sincerely, Dimitri De Vos
how do i add a new field and insert a value into it?
I'm importing data from another table into a new one, but my source table has a char field and the target filed is (int) can I add a new field to the source file and add a value to it? and how? I have in my source table a field call companyName that contains text and the target table has a companyId which is an INT. If I create a field call companyId and add a value of '1' depending on the value of CompanyName... This is what i want ot do if CompanyName = LA then insert 1 into new filed companyID How can I do that? I have to do this for 100 or more tables. Can I write a script that will do that for me? does it make sence?
Hi! I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this? Thanks in advance!
I want to add a computed field to an existing SQL table: where Field1 is >0 and field2 is not null set newfield = 'Y' else set newfield = 'N'
I want to keep this existing table because I'm using it as the basis for an Access Report that is nearly complete.
BTW this table is the result of a DTS package that is comprised of several SQL executables so I need to be able to repeat this as part of a larger process.
Newbie question:Aside from the single quote (i.e. chr(39)) what other characters can causeMS-SQLserver to cough-up the insertion / update back in your face?TIA
Hi all, I am stuck in the following situation, I have following query:
SELECT EM1.VALUE AS 'P_ABC', EM2.VALUE AS 'P_XYZ', COUNT(EM1.VALUE) AS 'COUNTS' FROM TABLE_1 EM1, TABLE_1 EM2 WHERE EM1.EXTENDED_PROPERTY_GID IN (SELECT GID FROM TABLE_3 WHERE NAME = 'ABC' ) AND EM2.EXTENDED_PROPERTY_GID IN (SELECT GID FROM TABLE_3 WHERE NAME = 'XYZ' ) AND EM1.DOCUMENT_METADATA_ENTRY_GID = EM2.DOCUMENT_METADATA_ENTRY_GID group by EM1.VALUE,EM2.VALUE
There is foreign key relation between EXTENDED_PROPERTY_GID OF TABLE_1 AND GID FROM TABLE_3
Now, I want to create an ad hoc report but the problem is when I add EM1.VALUE to display the P_ABC, I am not able to add EM2.VALUE after that, may be because refere to same column of same table. I have to add EM1.VALUE and EM2.VALUE both to display result but I am not able to do it.
What is the solution for this problem ? Its kind of urgent.
All- Please advise how to configure a gridview so that when introducing non-editable fields to the select statement, the edit function doesn't crash with a "Procedure or function (UpdateCommand) has too many arguments specified." The scenario: Update works find when I have a gridview based on the following SPs:
SELECT: 1 ALTER PROCEDURE dbo.H2SelectCommand 2 AS 3 SET NOCOUNT ON; 4 SELECT headcount_id, person_id, act_session_id, no_answer 5 FROM headcount 6
INSERT:
1 ALTER PROCEDURE dbo.H2UpdateCommand 2 ( 3 @person_id smallint, 4 @act_session_id smallint, 5 @no_answer bit, 6 @Original_headcount_id smallint, 7 @headcount_id smallint 8 ) 9 AS 10 SET NOCOUNT OFF; 11 UPDATE [headcount] SET [person_id] = @person_id, [act_session_id] = @act_session_id, [no_answer] = @no_answer WHERE (([headcount_id] = @headcount_id)); 12
HOWEVER, when I alter the select command so that it includes a column (person.person_name) from a parent directory like this:
1 ALTER PROCEDURE dbo.H2SelectCommand 2 AS 3 SET NOCOUNT ON; 4 SELECT headcount_id, person.person_name, headcount.person_id, act_session_id, no_answer 5 FROM headcount 6 7 INNER JOIN person 8 ON headcount.person_id = person.person_id
...and then proceed to do an Edit/Update operation, I get the pesky "Procedure or function H2UpdateCommand has too many arguments specified" message. The only obvious thing I could think to try was to configure the new column in the gridview to be Read Only, but this didn't help. Do I have to alter the Update SP to somehow account for the new field, even though the warning already says that there's too many arguments in it? Any ideas on how to fix this error would be appreciated! -Kurt
I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this? Here's my code for adding information from the table to the database: '--------------------------------------------- ' name: Button_Click() '--------------------------------------------- Sub Button_Click( s As Object, e As EventArgs ) Dim strConnect As String Dim objConnect As SQLConnection Dim strInsert As String Dim cmdInsert As SqlCommand 'Get connection string from Web.Config strConnect = ConfigurationSettings.AppSettings("ConnectionString") objConnect = New SqlConnection(strConnect) strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @ClientID, @DomainName, @Registrar, @ExpirationDate )" cmdInsert = New SqlCommand( strInsert, objConnect) cmdInsert.Parameters.Add( "@ClientID", dropClient.SelectedItem.Value ) cmdInsert.Parameters.Add( "@DomainName", txtDomainName.Text ) cmdInsert.Parameters.Add( "@Registrar", txtRegistrar.Text ) cmdInsert.Parameters.Add( "@ExpirationDate", txtExpirationDate.Text ) objConnect.Open() cmdINsert.ExecuteNonQuery() objConnect.Close() 'Display the results "page" DisplayResults() End Sub Here's the code for the form: <form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()"> <div align="center"> <table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099"> <tr> <td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td> <td> </td> </tr> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Client's Name:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <asp:dropdownlist id="dropClient" runat="server" /> </p> </td> </tr> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Domain Name:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" /> </p> </td> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Registrar:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" /> </p> </td> </tr> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Expiration Date:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" /> </p> </td> </tr> <TR> <TD> </TD> <TD align="center"> <asp:Button Text="Submit" OnClick="Button_Click" Runat="Server" /> </TD> </TR> </table> </form> </div>
select max(PTR_DATE) from MPR_portfolio_transactions group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE from MPR_portfolio_transactions group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
I have following data in my table. In some of the elements there can be missing nodes. I need to find out missing nodes from each element and insert default node if it doesnt exist so it validates with schena.from below exmaple in the third element location node is missing and i would like to add default node if we see any of these.
Input CREATE TABLE #t (xmlfile XML) INSERT INTO #t
[code]....
Some times Location tag name can be Location10. like <Location10>NY</Location10>.In this case i need to update <Location10> to <Location> to validate with XML schema.
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this? ASPX.CS file protected void Page_Load(object sender, EventArgs e) { SqlConnection con = null;
con = DataBaseConnection.GetConnection(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; da.Fill(ds);
HiI have a table called cur and i want one of the fields to contain a value of two other fields multiplied togetherTable 'cur' Fields ID, A, B, Cfield C must = A*BI have this so far and it works but updates ALL of the fields C rows______________________________________________ALTER TRIGGER TG_cur ON cur AFTER INSERTAS BEGINUpdate curSET C = (i.A*i.B)from inserted i_______________________________________________RESULT:ID|A|B|C1 - 3 - 4 -202 - 6 - 6 -203 - 8 - 9 -204 - 2 - 3 -205 - 4 - 5 -20I think need a where statement at the bottom but the followings does not workwhere ID = i.IDREQUIRED RESULTID|A|B|C1 - 3 - 4 -122 - 6 - 6 -363 - 8 - 9 -764 - 2 - 3 -65 - 4 - 5 -20any help would be greatmany thanksBil
Hi , i have a trigger that start on a record insert . . .This take de ID of the inserted record and start to fill other table in the database.In this triggher i have to update an image field of a table , getting the value from another table .I've casted this value in varbinary , but var binary can take only 8000 byte , and this fileis bigger than 8000 byte . . .How can i proced to solve the problem ???Thanks
Hi - I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated. Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL. Is there a way to do this with a trigger (or an otherwise elegant way)? Thanks in advance for ANY help or ideas. Oliver
I'm tracking sales reps and the companies they represent. I have 2 tables. A header table that has the SalesRep (a key field) the name, phone, address, etc.A line file that will have the sales rep multiple times, each line having a vendor they represent.
SalesRepHeader table: SalesRep name phone VendorList Bob Bob_Smith 111-222-3333 John John_Young 123-456-56789 Mary Mary_Kerns 567-876-98765
[code]...
Whenever the SalesRepLine file is added to or updated I want the header file field VendorList to be updated with a concat of the vendors for this sales rep so the SalesRepHeader file would look like this.
SalesRep name phone VendorList Bob Bob_Smith 111-222-3333 Samsung, Kenwood, JVC John John_Young 123-456-56789 Apple, HP
I have a trigger that uses SQL Mail to generate an email everytime a new record is added. I would like the following fields included in the email with the last record that was entered:
Hello,While working through my encryption questions from preivous posts, I amfinding that I may have to resort to use triggers to do the encryption(not that this is the only way, but might be the best way for mycircumstances).I would like to create a trigger that will encrypt the field before thewrite is committed.I've found serveral posts about triggers, but nothing that fits what Iwish to do.Upon an insert or update I want to modify the ssn field with this:cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))so, ssn '123456789' in SSN would become <something encrypted> in SSNThis is the trigger I have so far, but it is generating an error:CREATE TRIGGER F_Student_SSN.eSSNON F_STUDENT_SSNINSERT, UPDATEAS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))TIARob
I've just noticed some strange behavior that seems like a bug to me. It's much easier to follow an example of it that to outright explain it, so here goes.
I have a table defined with a NOT NULL constraint on a column and a default clause: -- DROP TABLE TestTable CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )
I have a view defined on the table, in this example case, the view just mirrors the table one to one: -- DROP VIEW TestView CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable
So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field: INSERT INTO TestView (TestField0) SELECT 'test'
Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt: -- DROP TRIGGER TestTrigger CREATE TRIGGER TestTrigger ON TestView INSTEAD OF INSERT AS BEGIN INSERT INTO TestTable (TestField0, TestField1) SELECT TestField0, COALESCE(TestField1, 'X') FROM inserted END
Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error: INSERT INTO TestView (TestField0) SELECT 'test'
Server: Msg 233, Level 16, State 2, Line 1 The column 'TestField1' in table 'TestView' cannot be null.
Dear Experts,I'm an Oracle guy, who is being given more SQL Server assignmentslately.I've been looking for things on the web about this, but I can'tanything so far.In Oracle, I you can create a trigger on a table that -only- fires ifcertain fields are updated.create or replace trigger trg_some_triggerBEFORE insertOF some_field1, some_field2on tbl_some_tablefor each row....Is this also possible in SQL Server?What is the syntax please?Thanks a lot!