Table Adapter Not Allowing Null String
Sep 27, 2007
I have an SP that I call via table adapter. If I do not pass a value in the string field ('eventRef'), the SP never gets called - it appears that the table adapter is filtering it...? I need to be able to count records that have an 'eventRef' = to the one I pass, including null. This works fine if I pass an 'eventRef', but never runs if that parm is empty.
Here is the TA definition
<asp:ObjectDataSource ConvertNullToDBNull="true" ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="ImagesByEventTableAdapters.PagedImagesByEvent" OnSelecting="ObjectDataSource1_Selecting" OnSelected="ObjectDataSource1_Selected">
<SelectParameters>
<asp:QueryStringParameter Name="CategoryID" QueryStringField="CategoryID" Type="Int32" DefaultValue="24"/>
<asp:QueryStringParameter Name="EventID" QueryStringField="EventID" Type="Int32"/>
<asp:QueryStringParameter Name="EventRef" QueryStringField="erf" Type="String"/>
<asp:QueryStringParameter Name="PageIndex" QueryStringField="PageIndex" Type="Int32" DefaultValue="0"/>
<asp:Parameter Name="NumRows" Type="Int32" DefaultValue="16"/>
<asp:Parameter Name="ImageCount" Direction="Output" Type="Int32" DefaultValue="0" />
</SelectParameters>
</asp:ObjectDataSource>
And the SP:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PagingGetProductsByEvent]
@CategoryID INT,
@EventID INT,
@EventRef CHAR(10)=NULL,
@PageIndex INT,
@NumRows INT,
@ImageCount INT OUTPUT
AS
BEGINdo my stuff....
View 1 Replies
ADVERTISEMENT
Jan 21, 2008
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
View 9 Replies
View Related
Aug 1, 2007
I have a flat file that I'm reading from and loading my tables with. In that file I have a column that has numbers (2000,1999,1998 and so on) and the column that they are being loaded into is defined as an INT. The issue I'm running into is that the first 50 or so rows in the flat file is empty for this column so I'm getting an error message. If I put numbers in that column in the flat file it works, if i remove them it fails. How can I allow for NULL values for my INT column on the database table?
here is the error I'm getting:
[OLE DB Destination [182]] Error: There was an error with input column "SalesYear" (7259) on input "OLE DB Destination Input" (195). The column status returned was: "The value could not be converted because of a potential loss of data.".
View 12 Replies
View Related
Aug 4, 2007
My users want to be able to enter nothing in a date field.
I'm using asp.net v2, vb.net, and VS 2005 for my application. I'm not sure what to do or what code to write to allow the user not to enter a date and keep from hitting the sqldatetime overflow error.
I could use some help.
Thanks
View 4 Replies
View Related
Oct 17, 2005
Hello,
I have a BIT column which accepts NULL values.
What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).
Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?
Thanks,
View 2 Replies
View Related
Dec 28, 2006
Hello,I have a foreign key constraint between two tables (Appointments andMissedAppointmentReasons) and I'd like to allow null values in Appointmentstable for the field containing the MissedAppointmentReason, but currently, Iget a Foreign Key Constraint Error when I try to add a record to theAppointments table.Any ideas how I can have the null values but still maintain the Foreign KeyConstraint?Thanks!Rick
View 3 Replies
View Related
Apr 24, 2008
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
View 3 Replies
View Related
Jun 9, 2008
Can someone tell me why I an getting the following error message for my table adapter: "incorrect syntax near '?'"
The selecte statement is below.
SELECT Questions.QuestionID, Questions.QuestionNumber, Questions.QuestionText, Questions.SampleAnswer, SAFSection.SectionNumber + ' - ' + SAFSection.SectionName as [Section], SAFSubSection.SubSectionNumber + ' - ' + SAFSubSection.SubSectionName as [SubSection], SAFSubSection.SubSectionDefinition, SAFSubSection.SubSectionQuestion, SAFSubSection.SubSectionInstruction, Answers.AnswerFROM (SAFSubSection INNER JOIN (SAFSection INNER JOIN Questions ON SAFSection.[SectionID] = Questions.[QuestionSectionID]) ON SAFSubSection.[SubSectionID] = Questions.[QuestionSubSectionID]) LEFT JOIN Answers ON Questions.QuestionID = Answers.QuestionIDWHERE Answers.SystemID = ?Order BY SAFSection.SectionID, SAFSubSection.SubSectionID,Questions.QuestionID
View 2 Replies
View Related
Dec 20, 2007
Hello,
I have a table adapter which uses a Query string to get the select view and a stored procedure for its delete portion. Not all of the columns in the select statement are required for the stored procedure. When I try to do the delete, it attempts at passing in more variables than it needs and ends up resulting in an error saying it can't find a procedure that is valid. Is there any way to restrict which columns are being passed?
Thanks,
Chris
View 1 Replies
View Related
Apr 30, 2008
Is there a way to tell an ALTER TABLE statement that truncating data from a string or binary column is okay? Meaning, if I have a NVARCHAR(50) column and I want to change it to NVARCHAR(10), any strings over 10 characters can safely be truncated to 10?
Or do I have to push the columns though an intermediary first?
View 4 Replies
View Related
Nov 26, 2007
Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like: SELECT * FROM facility_table WHERE (approved IN (1,2,3));What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@approval" should be an int32.Any suggestions?
View 1 Replies
View Related
Feb 9, 2007
I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of table adapters for pulling records from SQL. Under heavy load, we get a lot of SQL Server Timeout errors. We have run a trace on SQL Server and it shows that several of the SQL statements being passed into SQL Server, from the Table Adapters, have bad SQL.
For example, here is the SQL in one of the table adapters
SELECT HomeMsgID, messageName, messageHTML, messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)
I call it with the following:
DateTime showDate = DateTime.Today;
DateTime removeDate = DateTime.Today;
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter();
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable();
ta.FillByCPID(dt, showDate, removeDate, CPID);
What the SQL trace shows, when it fails, is this (notice the extra single
quotes around the showDate, removeDate parameters):
E000
exec sp_executesql N'SELECT HomeMsgID, messageName, messageHTML,
messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)',N'@showDate
datetime,@removeDate datetime,@CPID int',@showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3071225
1[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
I recreated the SQL to use a stored procedure, and got a similar error:
E000exec dbo.spFillHomeMsgByCPID @showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3008195
5[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
However, if I create dynamic SQL and use the following, there are no errors.
string mySql = string.Empty;
mySql = "SELECT HomeMsgID, messageName, messageHTML, messageText,
populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT
populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE
(CPID = " + CPID + "))) AND (isVisible = 1) AND (showDate <= '" + showDate +
"') AND (removeDate >= '" + removeDate + "')"; SqlDataAdapter adapter = new
SqlDataAdapter(mySql, ConfigurationManager.ConnectionStrings["MyUCR2007ConnectionString"].ToString());
DataSet RecordCount = new DataSet();
adapter.Fill(RecordCount);
DataTable testDT = RecordCount.Tables[0];
I am using VSTS with the Service Pack installed. SQL 2005 is running on W2K3 Enterprise, fully patchedThanks,James
View 1 Replies
View Related
Apr 15, 2008
Hi All,
I would like to send a query via Table adapter using parameter that is not the whole field. (Its only a part of the filed).
I will try to illustrate my needs:
We have a list of items in a data grid.
I would like to get the list of items who are answering my item name search query.
(I need to change the list every time the user enter another key letter for the search).
I would like to do so in the data adapter, but I'm not able to use the LIKE statement.
Please please try to help me
P.S.
Do I need to use stored procedure, if so, can anyone give me an idea how to do so ?
View 1 Replies
View Related
Aug 8, 2007
Hi
I am using this query to alter a table
ALTER TABLE myTable ADD age int NULL DEFAULT(0)
But above query is adding age field by storing Nulls but not with default values
So I need to add age field to the table by storing default value as 0 and by allowing Nulls
Please advice
Thanks
View 5 Replies
View Related
May 23, 2007
I'm looking for a way to pass an array of values as a parameter to a query in a table adapter. For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this. Any help would be greatly appericated.Thanks,Ryan.
View 6 Replies
View Related
Oct 27, 2015
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case
when col>'20150901' then 'NULL'
else col
end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
View 10 Replies
View Related
Apr 14, 2008
Hi All,
I'm trying to use a very simple SQL String that check if a specific string exist in the DB.
When I do the SQL and use a regular Connection I get the result.
When I use a data set and on the table adapter I need to use the "LIKE %" method I get an error.
What should I write on the table adapter in order to make this search?
ItemID is a type string.
This is my SQL:
SELECT itemID, itemName
FROM Items
WHERE (itemID LIKE @ItemIDTemp)
This sample give no result
or
SELECT itemID, itemName
FROM Items
WHERE (itemID LIKE '%' + @ItemIDTemp + '%')
This sample return an error message
"Data conversion failed. [OLEDB status value (if known)=2]"
What is the right way to do so?
View 3 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Jul 23, 2005
I don't know enough math to demonstrate that any numerical operationwith a null should yield a null; although I would guess that it's true.I just don't buy it, however, when dealing with strings and nulls. In asimple table with first, middle and last name columns, I would inferthat a null value in the middle name column means the HR person forgotto ask. A zero length string, however, tells me HR did ask and there isno middle name. Regardless of whether HR asked, when I concatenate thethree fields, I can't think of a sound reason why I souldn't get thefirst and last names.Having now started a flame war, I actually have a question: How do Iset the default for any given database or table so that concat nullyields null is permanently off? I have tried exec sp_dboption'myDB','concat null yields null',false (and many variations ondelimiting the parameters) but it doesn't do jack. I can use setconcat_null_yields_null off but that only lasts for the immediatesession.I've tried to follow the thread through the BOL but I'm left scratchingmy head on how to accomplish this.Thanks.Randy
View 7 Replies
View Related
Jun 28, 2004
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies
View Related
May 12, 2004
I want all the null (blank) values returned by a stored procedure to be shown as a string like "n/a", how to do that easily? Thanks
View 2 Replies
View Related
Jul 11, 2001
example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order
select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv
I need something similar to
Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder
So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null
Please help , thanks
View 3 Replies
View Related
Jun 20, 2007
the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.
"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"
Any ideas how I could implement this more robustly?
cheers
M
View 14 Replies
View Related
Jul 1, 2006
Folks, this isn't exactly a 'Getting Started' question, but I couldn't find a more appropriate Application Development forum.
I'm porting an open source PHP application (http://sourceforge.net/projects/gallery) to use SQL Server as a backend. One of Gallery's unit test scripts tests the ability to insert a string containing a NULL character ( ). It's OK if the string is truncated during insertion, just so long as everything before the is there.
The string being inserted looks like:
$testString = "The NULL character should be escaped !";
(Note the between "escaped " and " !")
The error that the Gallery test script is getting is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The NULL character should be escaped '.] in EXECUTE("INSERT INTO g2_PluginParameterMap (g_pluginType, g_pluginId, g_itemId, g_parameterName, g_parameterValue) VALUES ('module','unitTestModule',1,'test19476','The NULL character should be escaped !')")
It looks like SQL Server is complaining about the syntax. I've written a much simpler test script in the hopes of reproducing the problem, but I don't know if what I'm now hitting is the same problem or a different one.
My simple script is:
<?php
$localhost = exec("hostname");
$database = "gallery2";
$uid = "g2user";
$pwd = "g2pwd";
$connectString = "Host=PROVIDER=MSDASQL;DRIVER={SQL Server};";
$connectString .= "SERVER=$localhost\sqlexpress;";
$connectString .= "DATABASE=$database;";
$connectString .= "UID=$uid;PWD=$pwd";
$sqlTableDrop = "drop table ljmtemp";
$sqlTableCreate = "create table ljmtemp (col1 nvarchar(100))";
$sqlTableQuery = "select len(col1) from ljmtemp";
// Connect to the db
$db = new COM("ADODB.Connection") or die("Cannot start ADO");
$db->open($connectString);
// Drop & recreate the table
$db->Execute ($sqlTableDrop);
$db->Execute ($sqlTableCreate);
// Insert the test data
//$testString = "The NULL character should be escaped !";
$testString = "This is a test string.";
$res = $db->Execute("insert into ljmtemp (col1) values ('$testString')");
if (!$res) die ("INSERT failed");
// Disconnect from the db
$db->Close();
?>
And it results in:
C:MyServer>php testMsSqlInsertNull.php
PHP Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string 'The NULL character should be escaped '.' in C:MyServer estMsSqlInsertNull.php:27
Stack trace:
#0 C:MyServer estMsSqlInsertNull.php(27): com->Execute('insert into ljm...')
#1 {main}
thrown in C:MyServer estMsSqlInsertNull.php on line 27
I'm not sure if this is the same problem as Gallery is reporting or another one.
It looks like somebody is treating the as a string terminator, but when i double the backslash the literal ' ' is inserted into the database.
How can I get SQL Server to insert at least everything up to the ?
Hopefully once I have my simple script working I'll be able to figure out what's wrong in the Gallery test.
Thanks.
View 10 Replies
View Related
Nov 9, 2007
I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.
View 6 Replies
View Related
Jan 12, 2008
Hi:
Trying to insert null value into sql table, but not working, if I use:
if (strMyText.Length == 0) command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else command.Parameters.AddWithValue("@Text", strMyText);
When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL
Where is the problem?
Thanks a lot.
Jt
View 3 Replies
View Related
Aug 9, 2005
Hi all, I have some columns in my database which allows null. I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)
View 5 Replies
View Related
Nov 10, 2005
Hi folks,
I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.
THANKS JOHN
View 3 Replies
View Related
Jul 23, 2005
Hi!I got a column, with different dates in it (Ddata type "nvarchar")...when running a SELECT on this column, I'm trying to filter thoserecordsets out, WHERE this column is NULL (I checked the table, thereare "empty" fields in the column):"SELECT bla FROM bla WHERE myColumn NOT NULL"but I still receive those "empty" fields in my resultset ...so I tried it with:SELECT bla FROM bla WHERE myColumn <> ''again, "empty" fields in my resultset ....what's going wrong there? is there a possibility to check what kind ofvalue I got in my column!?Thanks!Peter
View 3 Replies
View Related
Aug 15, 2007
I'm using UPDATE as follows"
UPDATE Dubai
SET DB = 'D'
WHERE DB = NULL
but nothing happens. What am I doing wrong?
Thanks.
View 3 Replies
View Related
May 1, 2008
I'm importing a flat file with date fields into a sql table. I'm using a script transformation, reading the "normal" date fields into a string, massaging them, and passing them to a database table which uses standard datetime fields. No problem yet.
The file also contians a number of date fields that are all blanks. These correspond to "NULL" values. The problem is in my script I'm trying to do something like this:
(for database) (in SSIS)
date field = System.DBNULL.Value
This is wrong and I get complaints about improper data conversion, etc. What can I pass in the output string that the database will intepret as a NULL?
TIA,
barkingdog
View 4 Replies
View Related
Oct 25, 2006
How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?
View 10 Replies
View Related
Aug 22, 2014
I need to search on varchar column including zero length string and null value.What is the best way to do it?
View 5 Replies
View Related
