(If this is not the right forum, please advise and I'll repost).
I am using Visual Web Developer 2005 Express (ASP .NET 2,0), with a SQL 2005 database. I have programmed a Data Access Layer using table adaptors: this is my standard way of using the database.
I had defined a table, Voyages: -
CREATE TABLE [dbo].[Voyages](
[VOYid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Voyages_VOYid] DEFAULT (newid()),
[VOYUser] [varchar](50) NOT NULL,
[VOYShipName] [varchar](255) NOT NULL,
[VOYDepartPort] [uniqueidentifier] NOT NULL,
[VOYDepartDate] [datetime] NULL,
[VOYArrivePort] [uniqueidentifier] NOT NULL,
[VOYArriveDate] [datetime] NULL,
[VOYCaptain] [varchar](50) NULL,
[VOYComment] [varchar](1024) NULL,
[VOYUpdateid] [uniqueidentifier] NULL,
CONSTRAINT [PK_RESVoyages] PRIMARY KEY CLUSTERED
I defined a table adaptor based on the query
SELECT Voyages.*
FROM Voyages
and defined variables in my VB code: -
Dim tavoyages As New ShippingTableAdapters.VoyagesTableAdapter
Dim tbvoyages As Shipping.VoyagesDataTable
Dim trvoyages As Shipping.VoyagesRow
and read data into these variables with:-
tbvoyages = tavoyages.GetDataByVoyid(Voyageid)
If tbvoyages.Count > 0 Then
trvoyages = tbvoyages(0)
....
All of this works perfectly, and trvoyages contains all the correct data.
I wanted to set VOYUpdateID to NULL, so I coded
trvoyages.VOYUpdateid = Nothing
tavoyages.update(trvoyages)
However this set the database field to "00000000-0000-0000-0000-000000000000", not to NULL. However this code, in which we use another of the Update overloads naming all variables and "Nothing" in the VOYUpdateid position, works perfectly: -
tavoyages.Update(trvoyages.VOYid, trvoyages.VOYUser, trvoyages.VOYShipName, trvoyages.VOYDepartPort, trvoyages.VOYDepartDate, trvoyages.VOYArrivePort, trvoyages.VOYArriveDate,
trvoyages.VOYCaptain, trvoyages.VOYComment, Nothing, trvoyages.VOYid)
Is there any way in which trvoyates.VOYUpdateid can be set to Null. I tried dbnull, but just got an error message.
Thank you,
Robert Barnes.
Hi, I need to add to a database table.I have 5 variables that need to be added. But, i need to add 3 of them in one procedure and then the other2 in another procedure...so i'm using dboperation add to add the first 3 and dboperation edit to add the other 2 vars. However, when i add the first 3 vars, i need to set the other 2 to null...so in my datasetop proc i'm using Optional Byval var as string="".....but this doesnt work...it says "conversion from dbnull to string is not valid".......ive also tried db.null, "N/A" and nothing...but it still gives me the error?
Hi I set up some routes incorrectly on one of my servers and included a mirror address when i shouldnt have. now i cant get rid of the mirror address - when i try
alter route remote_route with mirror_address = null
i get an error saying
incorrect syntax near null
Does anybody know how i can clear out the mirror address for a route?
I have a result set stored in a temporary table variable. The fields need to have heirarchy when they finaly get displayed to the user. I need to replace the repeating fields per group with NULL and leave out only one set of unique records per group.
I want to do something like this- make the fields in red NULL while leaving out the ones in green.
ValB ValB2 ValB3 ValB40 ValA50 { Row. Group B ValB ValB2 ValB3 ValB41 ValA51 }
I was able to achieve this by using @@ROWCOUNT with in a while loop by performing one UPDATE at a time (SET ROWCOUNT = 1). But i have to run Count(*) each time I loop through the update query to figure out the remaining number of rows I have per group as I need to leave out one untouched. But the Count function is taking too much time and I'm having performace issues.
Any idea how I might be able to do this in my stored proc. efficiently?
I am using a Ms-Access DS which is accessed by a website's server-side scripts.
What I would like to do is set an existing record's date/time field to null. I have tried to simply alter its value by not including any data within the sharps (##), however that did not work.
Hi, I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields. I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is: SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept) so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful. Cheers, Tom
On MS-SQL 2k I have a created user. Security for the SQL instance isset to mixed.When I attempt to connect to SQL using Enterprise client on anothermachine on the local network using Windows Auth I get the error: DBName-Login Failed for user ('null')- Reason: Not associated with atrusted SQL Server Connection.What am I over looking?TIA
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database. I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields. It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table. In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters. The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%') If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null> My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>. For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
I am creating an image gallery system, where there is an admin andmultiple users upload images. Each user is restricted to uploading 8images. Currently, I have this hard coded into a trigger. I wouldlike it to use a table field instead... one that the admin can changeon the fly. I want to call this table Settings, but I want to createinsert 1 record, and then block all subsequent INSERT statements(there's only going to be 1 setting) so that the Settings table willonly have 1 record in it for its entire lifetime. What's the best wayto do this?
I am having 2 tables Groups and GroupMembers table . the following are the structures for the above two tables
Group ---------
GroupID GroupName CashassignmentType
Groupmembers ----------------------
MemberID memberName GroupID Payment
Group table is referenced by groupmembers table through Group ID Column Here while i am inserting a row (member) into a Groupmembers tables i want to crosscheck with CashAssinmenttype column of Group table. if cashassignment type i want to set the payment with 0 other wise the payment specified should be inserted
Groupmembers.Payment <---> If Group.CashassignmentType=0
Then 0 Else Payment
I want to set this Condition while creating tables itself
Please let me know what could be the best option to proceed here
Can you dynamically set the name of the table in the SelectCommand section of the SqlDataSource? (If it is relevant, I code in C#) For example, <asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test-MySQL %>" ProviderName="<%$ ConnectionStrings:Test-MySQL.ProviderName %>" SelectCommand="SELECT * FROM TestTable"> I would like to replace 'TestTable' with the name of a table that is extracted from a string array in the code-behind. Appreciatively,Peter
I'm working on Reporting service 2005. When I give an expression for visibility of a table in a report based on a parameter, the contents are coming in a single page and it is not based on the interactive size of the report. I want the data to be coming on different pages based on the interactive size of the page. If the visibility is set directly it works. Could you please help me to solve this issue?
I'm looking for a query I can use to alter table relationships. What I want to do in particular, is to set every relationship to cascade on update. Can anyone point me out to a solution? MSDN seems very vague in this subject.
I try to activate the map document control on my report. My Report is composed of a table in which I group by 2 criterias. When I set the document map label of the first entry of the group list then I get an error on the preview :
"An internal error occurred on the report server. See the error log for more details. "
Can someone tell where can I find the error log file ?
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB 01/01/11 100 5 02/01/11 102 4
Share Price....
Date CompA CompB 01/01/11 100 100 02/01/11 101 99
Event Data...
Date Company 01/01/11 CompA 02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance: SELECT Event.Date, Event.Company FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column WHERE Column = Event.Company AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
Hello,I want to store two information (so 2 columns) for 2/3rd of the rowsthat will be in a tableand only one information (1 column is suffecient) for 1/3rd of therows of the table.ex:jb_id, previous_jb_id -------- for 2/3rd of the rowsjb_id -------- for 1/3rd of the rowsWhat is better#1) add both the column (jb_id, previous_jb_id ) in the table itself.disadvantage:By this for 1/3rd of the rows, previous_jb_id will be NULL#2) create different table for storing previous_jb_id for requiredrows only.disadvantage:1) more table---> more Joins & more table to update2) more Joins --->slow& more Joins ---> bit difficult to write "select" queries.Thanks in advance.waiting for reply.
Hi,I want to check with VB.net whether a field in a SQL-table is NULL or not.This code doesnot work:If xxx = NULL then<statements>End IfI got the error, that NULL is not supported ?How do I code the check ???Help is appreciated, Gr.
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)????
I have a table that I'm loading as part of a control flow that in turn is copied to a target table by using a data flow task. I am doing this because a different set of fields may be used from the source entry to create the target entry based on a field in the source table. That same field may indicate that multiple entries need to be created in the target table from one source table entry for which I use a multi-cast transformation.
The problem I'm having is that no matter how many entries there are in the source table, the OLE DB Source during execution only shows 7,532 entries being taken from the source table. If there are less than 7,532 entries in the source table, everything processes fine. More than 7,532 and the data flow task only takes 7,532 and then seems to hang. It also seems as though only one path of the multi-cast transformation is taken when the conditional split directs a source entry down that path.
Seems like a strange problem I know, but any insight anyone could provide is appreciated. Thanks.
I have a stored procedure that adds constraints from a variable: ALTER procedure [dbo].[addMyConst](@txtAuto_ourlim money OUTPUT,@txtGen_ourlim money OUTPUT)ASEXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_auto_ourlim DEFAULT ' + @txtAuto_ourlim + ' FOR Auto_ourlim')EXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_Gen_ourlim DEFAULT ' + @txtGen_ourlim + ' FOR Gen_ourlim') This works fine unless one of the variables is null or empty. Then I get the error: "Incorrect syntax near the keyword 'FOR' Evidendly SQL Server Express sees "....DEFAULT + + FOR..." but I don't know. I've fiddled with this a long time and haven't a clue how to fix it. Any help would be appreciated. Thanks, Steve
how can you alter a table so that you can add a default value whenever the field is NULL? for example, whenever the table is brought up in a query, NULL is replaced with UNKNOWN
I'm using SQL Server 2000. I have an EXTERNAL_FUNDING table and an EXTERNAL_FUNDING_TYPE table. The DDL for the tables is listed below. Each EXTERNAL_FUNDING record has a type field, which is a foreign key to a record in the EXTERNAL_FUNDING_TYPE table. However, I would like for the type to be allowed to be null. When displaying the data, if the type is null, I'd like to show blank text. Here is the query I'm trying to use: SELECT EXTERNAL_FUNDING.id, date_requested as [Request Date],source as [Source/Sponsor],EXTERNAL_FUNDING_TYPE.type AS [Type],amount as [Amount], granted as [Granted] FROM EXTERNAL_FUNDING,EXTERNAL_FUNDING_TYPE WHERE (project_id = @project_id) AND (EXTERNAL_FUNDING.type = EXTERNAL_FUNDING_TYPE.id) ORDER BY date_requested ,source,type
But this query does not return the records with NULL for the type field. Can someone explain to me how I can also return the rows with a NULL type? Thanks!
In my script component for "Opening Balance", I set my output columns like this:
Row.AccountNumber = CDate(rowValues(1)
Row.OpeningBalance = CDec(rowValues(2))
Row.ClosingBalance = Nothing
Because I don't have a value for ClosingBalance in this row, I set ClosingBalance = Nothing.
And for "Closing Balance", I set my output columns like this, because there's no value for OpeningBalance.
Row.AccountNumber = CDate(rowValues(1)
Row.OpeningBalance = Nothing
Row.ClosingBalance = Nothing
Then, in my column mappings I map OpeningBalance = OpeningBalance and ClosingBalance = ClosingBalance.
The idea being that if there's no value, then it should be set to NULL. However, in my table, I see that instead of NULLs, there's 0.00000. Which is not what I want to see.
I have an SSIS package that uses a pivot component to summarize some date. Some columns will be 0, but I want to write this row with 0 and not null. I tried defining my table as int, not null, default 0, but I get an error basically saying I can't insert null value. What's the best way around this? Thanks
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.
running sql server 2000 sp4, viewing the sysxlogins table data, and am wondering about the three items listed with a <NULL> in the name column, all xstatus = 192, each has a unique srvid value of 0, 2 or 3.
Looking in the MS tables reference book did not help they don't cover sysxlogins, googled around but no luck, searched this forum also.
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
