I'm runing an access application that has an sql server 2000 as its back end. Up to few weeks ago everything was fine, but now when a user tries to add a record from an access form into the database I get an error that it cannot insert a null value into the field.
The two fields that cause the problem do not allow nulls as they keep the user name and the time that the record was inserted into the table.
I trace the access program but I cannot pinpoint where the valuees are inserted into the table. All the other fields are bound on the form. When the problem first appeard I found out that at that same day the active directory server that keeps the logins to the network was restarded. The problem appears on the live database that the users use and as well as on my test sql server that is not connected to the main database server but is instaled on my pc together with the access application.
But I still have to login onto the same network to use my pc that is part of the same domain
The authentication on the sql server 2000 is windows only.
I'm sorry, this question has a pretty long preamble, but bear with me as this will fully explain my problem, and save a number of clarification questions.I am using Visual Web Developer 2005 Express, with SQL Server 2005. After reading a Microsoft article saying that it was much better to use a three tier architecture with data adapters than to write explicit queries, I have used this approach extensively, and generally it works very well. Typical code patterns are: - Dim taPassenger As New ShippingTableAdapters.VPassengerTableAdapter Dim tbpassenger As Shipping.VPassengerDataTable Dim trpassenger As Shipping.VPassengerRow ..... ' to process all passengers from a voyage: - tbpassenger = taPassenger.GetDataByVOYid(Voyageid) For Nbr As Integer = 1 To tbpassenger.Count trpassenger = tbpassenger(Nbr - 1) ' I now have the row available in properly-typed fields, eg trpassenger.vpxName ... Next ' to read and process a single passenger tbpassenger = tapassenger.GetDataByVPXid(VPXid) trpassenger = tbpassenger(0)The table adapter has been created with Insert,Update, Delete methods. Thus you can assign values to row fields, and then update the row: - trpassenger.VPXName = "New Name" .... tapassenger.update(trpassenger)There are actually several overloads of the update method: you could have written a field list, like this, where the names (VPXid etc) would be defined in my program as variables of the appropriate type for the corresponding column. tapassenger.update(VPXid, VPXName, VPXAge, ....HOWEVER, this doesn't work properly if you have non-string data that could be null. Thus if the fourth field is a foreign key that could be null, if you write Dim VPXVoyId as Nullable(of GUID) VPXVoyid = nothing tapassenger.update(VPXid, VPXname, VPXAge, VPXVoyid, ...or Trpassenger.VPXVoyid = Nothing tapassenger.update(trpassenger) you do NOT put a null value into the database. Instead you store a value of "00000000-0000-0000-0000-000000000000". You can solve this annoying problem in two ways: -A. If you will ALWAYS be storing a null value at this point of your program, then simply write the update statement like this: - tapassenger.update(VPXid, VPXname, VPXAge, Nothing, ...Here "Nothing" actually means "DBNull" even though it doesn't mean this when used in a normal VB assignment (and neither does DBNull).B You can use the SetxxxxNULL method: - If trpassenger.VPXVoyid = nothing then trpassenger.SetVPXVoyidNull End If tapassenger.update(trpassenger) Thus when the program logic is complex and there are many paths to the update statement I will often put statements such as this in front of the update statement, using SetxxxxxNull for all non-string values that may be null. You don't need to worry about string values because the Nullvalue property can be used to simply say that Null values are empty, and it all works properly, but for reasons unknown Microsoft have prohibited this for other data types. Now at last we get to my question, which is "How do I do the equivalent for an Insert?" tapassenger.insert does not have any overloads, and the only form is tapassenger.insert(field list). But this creates the problem above: if VPXVoyid = nothing then I insert the not-null value of "00000000-0000-0000-0000-000000000000". If I COULD use the form tapassenger.insert(trpassenger), then I'd have another problem: if you haven't set trpassenger (for example with trpassenger = tbpassenger(0)) then it will not exist, so you will get an exception when you try to reference it. Yet you can't write If isnothing(trpassenger) then trpassenger = new shipping.vpassengerrow) (I've tries this both as an assignment, and as a Dim statement) end ifSo far I can think of only two solutions, both awful: -a/ Use multi-choice logic to choose an appropriate insert statement. If there is only one or two possibly-null fields, I could write:- If field4 = nothing then trpassenger.insert (VPXid, VPXname, VPXAge, Nothing, .. However if there is more than one possibly-null field, you need (2 to the power of nbr-of-possibly-null-fields) insert statements, with of course a lot of testing to choose which oneb/ I could insert the record with nulls, then read it back and update it. Surely there is a better way! Help! Thank you, Robert Barnes.
Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual (empno, accrued_vacation, accrued_sick_effective_date, accrued_sick, import_date)
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows. Here is my logic - 1. Create Ole DB source to RemoteSERVER - using SELECT stmt 2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output). 3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows 4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE; But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
I have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table. So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy] EXCEPT SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
Hi friends, I am trying to add records from one table into another.
SQL use riqdb2 INSERT INTO tblItemGrades (chrvItemGradeStaticComment,decItemGrade) SELECT chrvItem2StaticComment,decItem2Grade FROM tblItems2 GO
Error msg /*------------------------ use riqdb2 INSERT tblItemGrades (chrvItemGradeStaticComment,decItemGrade) SELECT chrvItem2StaticComment,decItem2Grade FROM tblItems2 GO ------------------------*/ Msg 515, Level 16, State 2, Line 2 Cannot insert the value NULL into column 'ItemGradesID', table 'RIQDB2.dbo.tblItemGrades'; column does not allow nulls. INSERT fails. The statement has been terminated.
Please advise how to insert new records - Is it because I havent set up Identity (1,1)????
CREATE TABLE First_Table (id INTEGER IDENTITY(1,1) NOT NULL, titre VARCHAR(50) NOT NULL, annee INTEGER NOT NULL, idMES INTEGER, genre VARCHAR(20) NOT NULL, resume TEXT, codePays VARCHAR(4), CONSTRAINT PKFilm PRIMARY KEY (idFilm), FOREIGN KEY (idMES) REFERENCES Artiste, FOREIGN KEY (codePays) REFERENCES Pays);
I'ld like fill in this tables records inserting in the column id values I got in the one other table. In Oracle it is possible to do it using sourceTable.nextval where sourceTable is created as: CREATE SEQUENCE sourceTable; How can I do it in MS SQL or Transact-sql?
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields. My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated." The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView. How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them. Any suggestions on my options would be greatly appreciated! Thanks Capella07
I have 2 tables (Dept and Emp) The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
In SQL SERVER 2005, how can I get the ID of the record I just insert to table? I defined a table MyTable, and I insert a record into the table using the SQL below Insert into MyTable (Name) values ("User Name") You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.How can I know the ID of the record I just insert to table?
CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](10) NOT NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Does anyone have a stored procedure for sending an email (using SQLMail) when a new record is added to a database table?
I have SQLMail all setup, I just need a good example of a stored procedure or trigger that will sending an email with the details for a new record entry into a table.
anyone can help me? i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.
so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".
so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?
Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com
Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .
INSERT INTO #tmpTable (eventName) values ('Another One')
DROP TABLE #tmpTable
this causes a null insert issue
(0 row(s) affected)
Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'statuspid', table 'tempdb.dbo.#tmpTable___________________________________________________________________________________________________________000000000130'; column does not allow nulls. INSERT fails.
The statement has been terminated.
So how do I allow the null, as the not null is coming from the ES table. But I want to allow the insert to happen without having to create the table first, this code works in SQL 2000 but fails in 2005, inserting all fileds into the insert also has it's own issues as some of the fields are delibertly left blank so in some circumstances the data returned to a grid displays correctly.
This method has been used in quite a lot of stored procedures and will be a nightmare to correct if each has to be edited.
One example of the use of is to return a dataset and then add a row at the bottom which is a sum of all the rows.
Greeting All, Again I stuck from foxpro into sql. could use some help.
I have 2 tables, Contact and Service. In the contact table I have an ID number that I would to insert into the Service table based on the Service table user name.
I cant use a Foxpro replace all command with some function attached to the script. I cant seem to find a SQL version of what I would like to do.
So here is my layout. If someone might be able to point me in a direction, I would be greatful
Contact Table "grid is the auto id field" grid, Anumber, Name, Address
Service Table grid, Anumber, Name, Address
service.name = contact.name add 'anumber' from contact table to service table where service.name lives.
I will keep looking on my own. any help would be great Thanx..
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure: USE [DB1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated the data in row 1 was not committed Error source .Net SqlClient Data provider. Error Message: the operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim Line 90: sContact.Email = sEmail.Text.Trim Line 91: sContact.Save() Line 92: Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
I am trying to create package something like that..
1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
I am simply trying to use SQLCommand in .net to insert a record into a SQL Server 2005 table. There are 2 fields being pulled from a user input for that could have no values selected. In this case I want to insert a null value into the record. I get an error that Null is no longer supported and that I should use System.DBNull, but that can't be used as an expression. How do I do this? Thank you in advance.
Hello, I am using sql server 2000 as my backend.If there is any null record in my table , I am getting following error:*********** Conversion from type 'DBNull' to type 'String' is not valid. ************** to overcome this error I want to check if its null then don't do anything else do something.I have tried like this:If rs.fields("ph").value="NULL" thenelse.................end if But this does not solve my problem.Please help me i anyone can.Thanks.
Seems that when i use the edit functionality of the formview, and there are NULLs in the record, that my sql update statement (default from the FormView wizard) doesnt work (nothing updates). However, if i populate all fields, everything works fine. I have modified the FormView edit template to add my own textboxes and dropdowns to simplify the user record editing. Because it works when all fields are populated with non-NULL values, Seems my Bind's are all correct. ---Jim
