hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust1, 1, Desc1
Cust1, 2, Desc1
Cust1, 2, Desc2
How would you make those null values 3's or the MAX(Inv#) for Cust1?
thanks,
rodcharIf this is your entire table, you might need to clean it up to include a
key. Otherwise,
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# ) + 1
FROM tbl t1 )
WHERE Inv# IS NULL ;
Anith|||Try:
SELECT NAME, ISNULL(INV,3), DESCCOL
FROM YOURTABLE
OR
SELECT NAME, ISNULL(INV,(SELECT MAX(INV)FROM YOURTABLE)), DESCCOL
FROM YOURTABLE
OR
DECLARE @.VAL INT
SELECT @.VAL = MAX(INV) FROM YOURTABLE
SELECT NAME, ISNULL(INV,@.VAL), DESCCOL
FROM YOURTABLE
HTH
Jerry
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||UPDATE YourTable
SET [Inv#] =
(
SELECT MAX([Inv#])
FROM YourTable Y1
WHERE Y1.Name = YourTable.Name
)
WHERE YourTable.[Inv#] IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||3 things for everyone:
1st: Thanks for the great replies
2nd:
UPDATE Transactions
SET InvNo = ( SELECT MAX( InvNo ) + 1
FROM Transactions t1 )
WHERE InvNo IS NULL ;
This worked for me but what i thought would happen with this is that once it
updated the first null and made it a 3 the 2nd null would become a 4. Can
anyone explain please?
3rd:
This same query didn't work in an access database it said that this wasn't
an updateable query. any ideas?
thanks again,
rodchar
"Anith Sen" wrote:
> If this is your entire table, you might need to clean it up to include a
> key. Otherwise,
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# ) + 1
> FROM tbl t1 )
> WHERE Inv# IS NULL ;
> --
> Anith
>
>|||>> This worked for me but what i thought would happen with this is that once
I am not sure what to explain, since the code is simple and clear. In case,
you are looking for sequentially incrementing value to replace the NULLs,
you'd have to use a "ranking" mechanism like:
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# )
FROM tbl t1 ) +
( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Name = tbl.Name
AND t1.Descr <= tbl.Descr
AND t1.Inv# IS NULL )
FROM tbl
WHERE Inv# IS NULL ;
Depending on the ranking variations you need, you'll have to adjust the
correlations in the subquery using COUNT(*).
Access has different updateability rules and different UDPATE dialect than
SQL Server. If you are using MS Access, consider posting this question in an
Access forum.
Anith|||i'm sorry for not being clear. your code posting is exactly what i needed. i
was just curious why it didn't make the nulls sequential. Because once it
makes the first null record a 3 wouldn't that be the new MAX(InvNo), and in
turn making the last null value a 4.
just trying to understand how the engine thinks and works. this helped a lot
.
"Anith Sen" wrote:
> I am not sure what to explain, since the code is simple and clear. In case
,
> you are looking for sequentially incrementing value to replace the NULLs,
> you'd have to use a "ranking" mechanism like:
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# )
> FROM tbl t1 ) +
> ( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Name = tbl.Name
> AND t1.Descr <= tbl.Descr
> AND t1.Inv# IS NULL )
> FROM tbl
> WHERE Inv# IS NULL ;
> Depending on the ranking variations you need, you'll have to adjust the
> correlations in the subquery using COUNT(*).
>
> Access has different updateability rules and different UDPATE dialect than
> SQL Server. If you are using MS Access, consider posting this question in
an
> Access forum.
> --
> Anith
>
>|||>> i was just curious why it didn't make the nulls sequential.
With no correlation, the value is generated only once for the entire
dataset. With a correlation, the values are generated for each matching row
in the dataset.
No problem.
Anith|||thank you everyone for the help. this has been very productive.
"Anith Sen" wrote:
> With no correlation, the value is generated only once for the entire
> dataset. With a correlation, the values are generated for each matching ro
w
> in the dataset.
>
> No problem.
> --
> Anith
>
>|||On Fri, 14 Oct 2005 10:09:04 -0700, ari wrote:
>2nd:
>UPDATE Transactions
>SET InvNo = ( SELECT MAX( InvNo ) + 1
>FROM Transactions t1 )
>WHERE InvNo IS NULL ;
>This worked for me but what i thought would happen with this is that once i
t
>updated the first null and made it a 3 the 2nd null would become a 4. Can
>anyone explain please?
Hi ari,
In SQL, all operations are done "at once". At least in theory. In
practice, they will eventuelly, somewhere deep in the engine, be
processed one row at a time, but the DB should behave as if the complete
statement is executed at once.
That's why you can swap columns without temp storage to hold the old
value, like you would in procedural languages:
UPDATE SomeTable
SET A = B,
B = A
WHERE ...
The right-hand B and A both refer to the "old" values (before the
update). The DB can process this internally ion any order it wants, as
long as the result looks as if it was all executed at once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label null. Show all posts
Showing posts with label null. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
assigning values
hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust2, null, Desc1
Cust2, null, Desc1
Cust3, null, Desc2
How would I make it:
Cust1, 1, Desc1
Cust1, 1, Desc2
Cust2, 2, Desc1
Cust2, 2, Desc1
Cust3, 3, Desc2
thanks,
rodcharselect name,right(name,1),Desc
from table
This will work for this example
http://sqlservercode.blogspot.com/
"rodchar" wrote:
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust2, null, Desc1
> Cust2, null, Desc1
> Cust3, null, Desc2
> How would I make it:
> Cust1, 1, Desc1
> Cust1, 1, Desc2
> Cust2, 2, Desc1
> Cust2, 2, Desc1
> Cust3, 3, Desc2
>
> thanks,
> rodchar|||the invoice numbers don't come from the NAME field.
it should find the MAX(InvoiceID) and assign it to all transactions for a
single customer that have a NULL value, then for the next set of transaction
s
for the next customer the Invoice number should be incremented by 1.
CustA, 1, Desc1
CustA, 1, Desc2
CustB, 2, Desc1
CustB, 2, Desc1
CustC, 3, Desc2
"SQL" wrote:
> select name,right(name,1),Desc
> from table
> This will work for this example
> http://sqlservercode.blogspot.com/
> "rodchar" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor -- the keys have nothing whatsoever to do with the display in
the front end.
Let me fix those horrible data element names with some guesses. The
only possible key is (cust_name, item_description), while silly me, I
would have thought that invoice_nbr would be unique in an invoice
table.
What you posted does not make any sense.|||Sorry about that.
Alright, let me give a quick 30,000 ft view because my logic could be off
(it has been before many times.)
I'm trying generate invoices from a transactions table. so here's what i do.
i enter all the transactions for the month for all the customers. when
invoice time comes around here are my steps:
1. I assign invoice numbers to each record in the transactions table where
InvoiceID IS NULL.
2. Then I insert the records into the invoice headers table and invoice
details table.
Transactions table:
rowID int primary key
CustomerID int
InvoiceID int
TransactionType (bill,payment)
Cost money
so my records look like this
1, 122, null, bill, $20
2, 122, null, bill, $20
3, 105, null, bill, $20
4, 101, null, bill, $20
5, 102, null, bill, $20
now if my logic is sound
my transactions table will look like the following after assigning invoice
numbers to them:
1, 122, 1, bill, $20
2, 122, 1, bill, $20
3, 105, 2, bill, $20
4, 101, 3, bill, $20
5, 102, 4, bill, $20
Please advise. Is there a more clearer way to do this whole process that i'm
missing.
thanks,
rodchar
"--CELKO--" wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to havs a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor -- the keys have nothing whatsoever to do with the display in
> the front end.
> Let me fix those horrible data element names with some guesses. The
> only possible key is (cust_name, item_description), while silly me, I
> would have thought that invoice_nbr would be unique in an invoice
> table.
> What you posted does not make any sense.
>
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust2, null, Desc1
Cust2, null, Desc1
Cust3, null, Desc2
How would I make it:
Cust1, 1, Desc1
Cust1, 1, Desc2
Cust2, 2, Desc1
Cust2, 2, Desc1
Cust3, 3, Desc2
thanks,
rodcharselect name,right(name,1),Desc
from table
This will work for this example
http://sqlservercode.blogspot.com/
"rodchar" wrote:
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust2, null, Desc1
> Cust2, null, Desc1
> Cust3, null, Desc2
> How would I make it:
> Cust1, 1, Desc1
> Cust1, 1, Desc2
> Cust2, 2, Desc1
> Cust2, 2, Desc1
> Cust3, 3, Desc2
>
> thanks,
> rodchar|||the invoice numbers don't come from the NAME field.
it should find the MAX(InvoiceID) and assign it to all transactions for a
single customer that have a NULL value, then for the next set of transaction
s
for the next customer the Invoice number should be incremented by 1.
CustA, 1, Desc1
CustA, 1, Desc2
CustB, 2, Desc1
CustB, 2, Desc1
CustC, 3, Desc2
"SQL" wrote:
> select name,right(name,1),Desc
> from table
> This will work for this example
> http://sqlservercode.blogspot.com/
> "rodchar" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor -- the keys have nothing whatsoever to do with the display in
the front end.
Let me fix those horrible data element names with some guesses. The
only possible key is (cust_name, item_description), while silly me, I
would have thought that invoice_nbr would be unique in an invoice
table.
What you posted does not make any sense.|||Sorry about that.
Alright, let me give a quick 30,000 ft view because my logic could be off
(it has been before many times.)
I'm trying generate invoices from a transactions table. so here's what i do.
i enter all the transactions for the month for all the customers. when
invoice time comes around here are my steps:
1. I assign invoice numbers to each record in the transactions table where
InvoiceID IS NULL.
2. Then I insert the records into the invoice headers table and invoice
details table.
Transactions table:
rowID int primary key
CustomerID int
InvoiceID int
TransactionType (bill,payment)
Cost money
so my records look like this
1, 122, null, bill, $20
2, 122, null, bill, $20
3, 105, null, bill, $20
4, 101, null, bill, $20
5, 102, null, bill, $20
now if my logic is sound
my transactions table will look like the following after assigning invoice
numbers to them:
1, 122, 1, bill, $20
2, 122, 1, bill, $20
3, 105, 2, bill, $20
4, 101, 3, bill, $20
5, 102, 4, bill, $20
Please advise. Is there a more clearer way to do this whole process that i'm
missing.
thanks,
rodchar
"--CELKO--" wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to havs a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor -- the keys have nothing whatsoever to do with the display in
> the front end.
> Let me fix those horrible data element names with some guesses. The
> only possible key is (cust_name, item_description), while silly me, I
> would have thought that invoice_nbr would be unique in an invoice
> table.
> What you posted does not make any sense.
>
Thursday, March 8, 2012
assign the Null value to a variable that is not a Variant data type.
Hello Everyone,
I trying to upgrade our Alpha 4v4 Dos Database to MS SQL 2000 with Access XP front end and I have four tables that won't let import my data into them. I keep recieveing a message that says "You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)"
What can I do to get rid of this stupid error, is it a problem with Access XP or SQL 2000.Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?
I tried that and I get a MS Jet Database Engine Error when I tried the import function, I know that in Access you can append data in, can you do that in SQL. What I mean is could I just link my Alpha tables into the SQL Database and then try to Append/Insert into my SQL tables. SQL is all new to me and I'm still in the process of learning it and it is alot different then Alphav4 and Access 97 functionality that I'm used to.|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?
Paul,
I can't import, append or insert. But I can copy and paste my old records into the new tables with a few error messages and only 65000 at a time. I guess that something is better than nothing. If anyone has a better way, I'm always open to try something new.|||Yes, you can append data in SQL.
There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.
Microsoft's Books Online has some good information on this.|||Originally posted by Paul Young
Yes, you can append data in SQL.
There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.
Microsoft's Books Online has some good information on this.
I've been using the DTS function and that is what I first tried to do my import with or even append and I still get an error message. If I import the whole table it will only bring in the structure, no data i get an error on that. THen if I try to append the data I get a different error. I'll look online and see if I can findout what I'm doing wrong. Or I'll just cut and paste since that seems to work.|||What were your errors?
DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.|||Originally posted by Paul Young
What were your errors?
DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.
If I import my data from Alpha into Access 97 and then into SQL I get an Insert error on 4 of my tables for certain Date fields that says Data Over flow invaild character value for cast specification. If I try to go directly into Alpha 4v4 which are Dbase 5 tables it just won't do it. I get a ms jet vb error and it will import nothing at all. Atleast in access it will import 32 of 36 tables.|||It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?|||Originally posted by Paul Young
It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?
Ok I changed my data source to dbase III and I was able to import my data directly form Alpha 4v4, plus I'm starting to usnderstand this DTS function. I was wondering if I can use it to just append the tables, not create them everytime. I noticed that everytime I use the wizard it wants to create the table then import the data. I want it to just Append the data now that I have the tables in SQL so that I can refreash the old data with the new until I'm ready to run everything in SQL. Can i physically change the SQL startments for that DTS function and is that possible.|||You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.|||Originally posted by Paul Young
You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.
Ok I can't find what your talking about "Select transformations and choose append rows?", could you do me a favor and type out the steps that you use to see if I'm going in the right direction. Appending my Bbase file and your CSV file shouldn't be that different as far as the steps go. Thank you for your help.|||ADP is the answer to all your problems.
It only deals with SQL Server-- so its much simpler than what you're talkin about..
in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..
of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..|||Originally posted by aaron_kempf
ADP is the answer to all your problems.
It only deals with SQL Server-- so its much simpler than what you're talkin about..
in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..
of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..
ADP, could you tell me more about it, I typed it into the help file and nothing came up.|||1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.|||Originally posted by Paul Young
1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.
Step four was the one that I wasn't finidng, thank you so much Paul, I did get it to work and now I just need to adjust it so that I won't get any errors when I try to append a date field. Thanks
I trying to upgrade our Alpha 4v4 Dos Database to MS SQL 2000 with Access XP front end and I have four tables that won't let import my data into them. I keep recieveing a message that says "You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)"
What can I do to get rid of this stupid error, is it a problem with Access XP or SQL 2000.Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?
I tried that and I get a MS Jet Database Engine Error when I tried the import function, I know that in Access you can append data in, can you do that in SQL. What I mean is could I just link my Alpha tables into the SQL Database and then try to Append/Insert into my SQL tables. SQL is all new to me and I'm still in the process of learning it and it is alot different then Alphav4 and Access 97 functionality that I'm used to.|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?
Paul,
I can't import, append or insert. But I can copy and paste my old records into the new tables with a few error messages and only 65000 at a time. I guess that something is better than nothing. If anyone has a better way, I'm always open to try something new.|||Yes, you can append data in SQL.
There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.
Microsoft's Books Online has some good information on this.|||Originally posted by Paul Young
Yes, you can append data in SQL.
There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.
Microsoft's Books Online has some good information on this.
I've been using the DTS function and that is what I first tried to do my import with or even append and I still get an error message. If I import the whole table it will only bring in the structure, no data i get an error on that. THen if I try to append the data I get a different error. I'll look online and see if I can findout what I'm doing wrong. Or I'll just cut and paste since that seems to work.|||What were your errors?
DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.|||Originally posted by Paul Young
What were your errors?
DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.
If I import my data from Alpha into Access 97 and then into SQL I get an Insert error on 4 of my tables for certain Date fields that says Data Over flow invaild character value for cast specification. If I try to go directly into Alpha 4v4 which are Dbase 5 tables it just won't do it. I get a ms jet vb error and it will import nothing at all. Atleast in access it will import 32 of 36 tables.|||It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?|||Originally posted by Paul Young
It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?
Ok I changed my data source to dbase III and I was able to import my data directly form Alpha 4v4, plus I'm starting to usnderstand this DTS function. I was wondering if I can use it to just append the tables, not create them everytime. I noticed that everytime I use the wizard it wants to create the table then import the data. I want it to just Append the data now that I have the tables in SQL so that I can refreash the old data with the new until I'm ready to run everything in SQL. Can i physically change the SQL startments for that DTS function and is that possible.|||You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.|||Originally posted by Paul Young
You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.
Ok I can't find what your talking about "Select transformations and choose append rows?", could you do me a favor and type out the steps that you use to see if I'm going in the right direction. Appending my Bbase file and your CSV file shouldn't be that different as far as the steps go. Thank you for your help.|||ADP is the answer to all your problems.
It only deals with SQL Server-- so its much simpler than what you're talkin about..
in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..
of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..|||Originally posted by aaron_kempf
ADP is the answer to all your problems.
It only deals with SQL Server-- so its much simpler than what you're talkin about..
in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..
of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..
ADP, could you tell me more about it, I typed it into the help file and nothing came up.|||1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.|||Originally posted by Paul Young
1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.
Step four was the one that I wasn't finidng, thank you so much Paul, I did get it to work and now I just need to adjust it so that I won't get any errors when I try to append a date field. Thanks
Wednesday, March 7, 2012
Assign Null value to Variable
I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro
The type of the value being assigned to variable "User:
Am i getting this error becasue the column has NULL value?. how can I resove this probelm?
Populate your source with 0s instead of NULLs in your source query, if applicable. (Or some arbitrary number)|||can i do thsi in Expression builder using NULL function?
if so, can you show me some examples?
|||Anywhere in an expression builder, you can do:ISNULL(ColumnOrVariable) ? 0 : ColumnOrVariable
Assign ID to distinct names
I have a table called NAMES with 2 columns - id, name. I have a bunch
of names in the table but id is null for all. I need to develop a
script that will assign a unique id to each distinct name.
Before:
ID Name
- Tom
- Tom
- Lee
- Lee
- Lee
- Jim
- Jim
After:
ID Name
1 Tom
1 Tom
2 Lee
2 Lee
2 Lee
3 Jim
3 Jim
DDL:
create table NAMES (
id int null,
name varchar(20) not null
)
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Jim');
insert NAMES values (null, 'Jim');
Any help is appreciated. Thanks."Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>
What's the point of duplicating the names? Try:
CREATE TABLE names2 (
id INTEGER NOT NULL
CONSTRAINT pk_names2 PRIMARY KEY,
name varchar(20) NOT NULL
CONSTRAINT ak1_names2 UNIQUE);
INSERT INTO names2 (id, name)
SELECT COUNT(DISTINCT N2.name), N1.name
FROM names AS N1
JOIN names AS N2
ON N1.name <= N2.name
GROUP BY N1.name ;
Result:
id name
-- --
1 Tom
2 Lee
3 Jim
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. In reality, the table has much more columns and already has an
existing primary key. The ID column described above is not apart of the
pk.
Any other solutions please. I'd like to avoid using cursors.
Thanks.|||Green wrote:
> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of
> the pk.
> Any other solutions please. I'd like to avoid using cursors.
> Thanks.
Get a list of the unique name values and put them in a temp table with
an int idenitity column. Update the original table from the temp table:
Create Table names (id int null, name varchar(50))
go
insert names values (null, 'Tom');
insert names values (null, 'Tom');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Jim');
insert names values (null, 'Jim');
go
Create Table #names (id int identity not null, name varchar(50))
go
insert into #names (
name )
Select distinct name from names
go
select * from #Names
go
Update names
Set names.id = t.id
from #names t
where names.name = t.name
go
select * from names
go
drop table #names
go
David Gugick - SQL Server MVP
Quest Software|||> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of the
> pk.
In that case adding the ID column based on the name would create a
transitive dependency in violation of the standard Normal Forms. Do I take
it that you really want to put name into a related table? Accurate DDL would
be a help here.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||In sql 2005 you can do
declare @.NAMES table (
id int null,
name varchar(20) not null
)
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Jim');
insert @.NAMES values (null, 'Jim');
select *
,dense_rank() OVER( order by name)
FROM @.NAMES n1
Result:
id name
-- -- --
NULL Jim 1
NULL Jim 1
NULL Lee 2
NULL Lee 2
NULL Lee 2
NULL Tom 3
NULL Tom 3
farmer
"Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>|||to get exact result like yours
select *
,dense_rank() OVER( order by name desc)
FROM @.NAMES n1
"Farmer" <someone@.somewhere.com> wrote in message
news:%23TiCAqkOGHA.140@.TK2MSFTNGP12.phx.gbl...
> In sql 2005 you can do
> declare @.NAMES table (
> id int null,
> name varchar(20) not null
> )
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Jim');
> insert @.NAMES values (null, 'Jim');
>
> select *
> ,dense_rank() OVER( order by name)
> FROM @.NAMES n1
>
> Result:
> id name
> -- -- --
> NULL Jim 1
> NULL Jim 1
> NULL Lee 2
> NULL Lee 2
> NULL Lee 2
> NULL Tom 3
> NULL Tom 3
>
> farmer
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>|||Thanks all. I used Gugick's suggestion and that worked well. I find
Farmer's solution quite interesting. Maybe I'll try that nexttime.
Thanks.
of names in the table but id is null for all. I need to develop a
script that will assign a unique id to each distinct name.
Before:
ID Name
- Tom
- Tom
- Lee
- Lee
- Lee
- Jim
- Jim
After:
ID Name
1 Tom
1 Tom
2 Lee
2 Lee
2 Lee
3 Jim
3 Jim
DDL:
create table NAMES (
id int null,
name varchar(20) not null
)
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Jim');
insert NAMES values (null, 'Jim');
Any help is appreciated. Thanks."Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>
What's the point of duplicating the names? Try:
CREATE TABLE names2 (
id INTEGER NOT NULL
CONSTRAINT pk_names2 PRIMARY KEY,
name varchar(20) NOT NULL
CONSTRAINT ak1_names2 UNIQUE);
INSERT INTO names2 (id, name)
SELECT COUNT(DISTINCT N2.name), N1.name
FROM names AS N1
JOIN names AS N2
ON N1.name <= N2.name
GROUP BY N1.name ;
Result:
id name
-- --
1 Tom
2 Lee
3 Jim
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. In reality, the table has much more columns and already has an
existing primary key. The ID column described above is not apart of the
pk.
Any other solutions please. I'd like to avoid using cursors.
Thanks.|||Green wrote:
> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of
> the pk.
> Any other solutions please. I'd like to avoid using cursors.
> Thanks.
Get a list of the unique name values and put them in a temp table with
an int idenitity column. Update the original table from the temp table:
Create Table names (id int null, name varchar(50))
go
insert names values (null, 'Tom');
insert names values (null, 'Tom');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Jim');
insert names values (null, 'Jim');
go
Create Table #names (id int identity not null, name varchar(50))
go
insert into #names (
name )
Select distinct name from names
go
select * from #Names
go
Update names
Set names.id = t.id
from #names t
where names.name = t.name
go
select * from names
go
drop table #names
go
David Gugick - SQL Server MVP
Quest Software|||> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of the
> pk.
In that case adding the ID column based on the name would create a
transitive dependency in violation of the standard Normal Forms. Do I take
it that you really want to put name into a related table? Accurate DDL would
be a help here.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||In sql 2005 you can do
declare @.NAMES table (
id int null,
name varchar(20) not null
)
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Jim');
insert @.NAMES values (null, 'Jim');
select *
,dense_rank() OVER( order by name)
FROM @.NAMES n1
Result:
id name
-- -- --
NULL Jim 1
NULL Jim 1
NULL Lee 2
NULL Lee 2
NULL Lee 2
NULL Tom 3
NULL Tom 3
farmer
"Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>|||to get exact result like yours
select *
,dense_rank() OVER( order by name desc)
FROM @.NAMES n1
"Farmer" <someone@.somewhere.com> wrote in message
news:%23TiCAqkOGHA.140@.TK2MSFTNGP12.phx.gbl...
> In sql 2005 you can do
> declare @.NAMES table (
> id int null,
> name varchar(20) not null
> )
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Jim');
> insert @.NAMES values (null, 'Jim');
>
> select *
> ,dense_rank() OVER( order by name)
> FROM @.NAMES n1
>
> Result:
> id name
> -- -- --
> NULL Jim 1
> NULL Jim 1
> NULL Lee 2
> NULL Lee 2
> NULL Lee 2
> NULL Tom 3
> NULL Tom 3
>
> farmer
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>|||Thanks all. I used Gugick's suggestion and that worked well. I find
Farmer's solution quite interesting. Maybe I'll try that nexttime.
Thanks.
Friday, February 24, 2012
aspnet_regsql.exe - cannot insert null values
Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errors
Setup failed.
Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
------------
Details of failure
------------
SQL Server:
Database: [AddressVerification]
SQL file loaded:
InstallCommon.sql
Commands failed:
CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)
INSERT INTO #aspnet_Permissions
EXEC sp_helprotect
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions
SQL Exception:
System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??
** I am logged in with full admin privaliges
tia
Mark.
This is because the preinstalled user defined data type sysname by default does not allow nulls--that means column of sysname data type won't accept nulls unless you explicitly set the column to allow null in column definition. So if the sp_helprotect stored procedure returns some rows with nulls in the [column] field (this is an rare issue but it does exist), the insert command will fail. So here I have to say it is a flaw of the aspnet_regsql utility.|||
I wrote a simple script to update the system table systypes to make sysname data type to accept NULLs. However the script is not fully tested and since it modifies system table directly, I don't recommend to use it and you need to consider the potential risk when using it. If some issue is caused by the script, please pass 'NoNulls' as parameter to change the systypes back to default:
CREATE PROCEDURE usp_KillUsers
@.p_DBName SYSNAME = NULL
AS
/* Check Paramaters */
/* Check for a DB name */
IF (@.p_DBName IS NULL)
BEGIN
PRINT 'You must supply a DB Name'
RETURN
END -- DB is NULL
IF (@.p_DBName = 'master')
BEGIN
PRINT 'You cannot run this process against the master database!'
RETURN
END -- Master supplied
IF (@.p_DBName = DB_NAME())
BEGIN
PRINT 'You cannot run this process against your connections database!'
RETURN
END -- your database supplied
SET NOCOUNT ON
/* Declare Variables */
DECLARE @.v_spid INT,
@.v_SQL NVARCHAR(255)
/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) = @.p_DBName
OPEN c_Users
FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.v_SQL = 'KILL ' + CONVERT(NVARCHAR, @.v_spid)
-- PRINT @.v_SQL
EXEC (@.v_SQL)
END -- -2
FETCH NEXT FROM c_Users INTO @.v_spid
END -- While
CLOSE c_Users
DEALLOCATE c_Users
CREATE PROCEDURE sp_SetSysnameAllowNulls @.Operation varchar(10)='AllowNulls'
AS
BEGIN
DECLARE @.cmd nvarchar(4000)
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
EXEC sp_msforeachdb N'use[?];
IF (DATABASEPROPERTY(db_name(),''IsReadOnly'')=1)
BEGIN
declare @.dbname sysname;
set @.dbname=db_name();
use master
EXEC usp_KillUsers @.dbname;
EXEC sp_dboption @.dbname,''read only'',''false'';
END'
IF (@.Operation = 'AllowNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status-1
WHERE name=''sysname'' AND (status&1)=1'
ELSE
IF (@.Operation = 'NoNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status+1
WHERE name=''sysname'' AND (status&1)=0'
ELSE RAISERROR('Expected @.Opertaion=''AllowNulls'' or ''NoNulls''',16,1)
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
END
Sunday, February 12, 2012
asp.net datareader+sql server+null fields=error
Following problem:
I try to select records where a field is null.
i.e.:
MyCommandGeneric.CommandText = "select A, b from object where C is null";
MyDataReader = MyCommandGeneric.ExecuteReader();
If I try now to access the values (string bla = MyDataReader["A"]), I am
getting the error, that datareader can't read, when there is no data.
But there is data!
I tried to query the sql server directly with the above query, and it gives
a record.I think you have to execute read method before accessing the data.
SqlDataReader Class
http://msdn.microsoft.com/library/d...opi
c.asp
AMB
"the friendly display name" wrote:
> Following problem:
> I try to select records where a field is null.
> i.e.:
> MyCommandGeneric.CommandText = "select A, b from object where C is null";
> MyDataReader = MyCommandGeneric.ExecuteReader();
> If I try now to access the values (string bla = MyDataReader["A"]), I am
> getting the error, that datareader can't read, when there is no data.
> But there is data!
> I tried to query the sql server directly with the above query, and it give
s
> a record.|||Oh, good god.
Of course! Now it works.
"Alejandro Mesa" wrote:
> I think you have to execute read method before accessing the data.
> SqlDataReader Class
> http://msdn.microsoft.com/library/d...o
pic.asp
>
> AMB
> "the friendly display name" wrote:
>
I try to select records where a field is null.
i.e.:
MyCommandGeneric.CommandText = "select A, b from object where C is null";
MyDataReader = MyCommandGeneric.ExecuteReader();
If I try now to access the values (string bla = MyDataReader["A"]), I am
getting the error, that datareader can't read, when there is no data.
But there is data!
I tried to query the sql server directly with the above query, and it gives
a record.I think you have to execute read method before accessing the data.
SqlDataReader Class
http://msdn.microsoft.com/library/d...opi
c.asp
AMB
"the friendly display name" wrote:
> Following problem:
> I try to select records where a field is null.
> i.e.:
> MyCommandGeneric.CommandText = "select A, b from object where C is null";
> MyDataReader = MyCommandGeneric.ExecuteReader();
> If I try now to access the values (string bla = MyDataReader["A"]), I am
> getting the error, that datareader can't read, when there is no data.
> But there is data!
> I tried to query the sql server directly with the above query, and it give
s
> a record.|||Oh, good god.
Of course! Now it works.
"Alejandro Mesa" wrote:
> I think you have to execute read method before accessing the data.
> SqlDataReader Class
> http://msdn.microsoft.com/library/d...o
pic.asp
>
> AMB
> "the friendly display name" wrote:
>
Subscribe to:
Posts (Atom)