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
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)<>" "));
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?
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 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);
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth). The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below.
Code:
Update AR_Primary_asset Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed' Where AR_Primary_Asset.ASSET_REF in ('1','2','4')
I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) .
Code:
DECLARE @Dispose binary(16) SELECT @Dispose = TEXTPTR(DESCRIPTION) FROM AR_PRIMARY_ASSET WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3')
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
