|
I thought i had the solution but no. i wrote first:
If Not DBNull(4) Then
'There is a date
Else
'There is no date
End If
But it dit work if there was a date present. The second one was also wrong:
If Not IsDBNull(objConnReader.GetString(4)) Then
'There is a date
Else
'There is no date
End If
This one dint work at all so now i need some help
|
|
|
|
|
I said to use IsDbNull() on the DataReader
Use: myDataReader.IsDbNull(columnOrdinal)
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
Founf the solution. Have to use the stament:
If IsDBNull(4) Then
This works perfect
|
|
|
|
|
I'm getting a "Failed to open database using..." error when attempting to connect to SQL Server located on a remote machine, using a user account login. Do I need to use a different Account setting? I'm currently using ServiceAccount.LocalSystem.
Marc
VS2005 Tips & Tricks -- contributions welcome!
|
|
|
|
|
just wondering... Does the user account you are using to login to remote DB has right to access to?
<< >>
|
|
|
|
|
If i perform sql query in SQL Server 2000, how can i select a particular range of values returned by query result?
For example if i want top 20 results, i can use the "Select Top 10 ....".
But, suppose I want to select query results from 51 to 60, how can i do it?
Thanks in advance
|
|
|
|
|
For your answer check out this blog entry[^] by Bo Nørgaard[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
I have this giant stored procedure executing many small queries. In between these queries, i want to be able to return a record or just some number to the record set so that i can update the value for a progress bar in my program. I have the asyncFetch part set when i do an open:
Set mRS = New ADODB.Recordset
mRS.CursorType = adOpenStatic
mRS.CursorLocation = adUseClient
mRS.LockType = adLockBatchOptimistic
mRS.Properties("Initial Fetch Size") = 1
mRS.Open sTmp, gdb, adOpenStatic, adLockBatchOptimistic, adCmdText + adAsyncExecute + adAsyncFetch
I also set up an event in VB to catch the record set's fetch:
Private Sub mRS_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Debug.Print "Fetch: " & Progress & _
" Max: " & MaxProgress
End Sub
.. but it never gets there.
-- modified at 20:33 Wednesday 21st December, 2005
|
|
|
|
|
Hi everybody,
I like to asked anyone who is very good at sql to help me display this data in a table format that duplicate headings, subheadings or other columns won't appear twice or more. Can anyone help me? I need to sort them and load them in a Datatable. Loop thorugh all records and display them in a label control created at run time. Thanks in advance.
I have posted this topic on other forums, some solutions given but they are using approach for .Net Framework 2.0, I am using .Net Framework 1.1. I am trying to avoid using a lot of select Distinct sql queries to database and looping the data to be able to remove duplicates. Do anyone knows another approach using less code and less sql access?
Sample Data:
Topics Table
TopicId TopicLevel Topic ParentId
1 1 First 0
2 1 AnotherFirst 0
3 2 Second 1
4 2 AnotherSecond 2
5 3 Third 3
Contents Table
ContentID Heading SubHeading SubSubHeading1 NumberText Names Content1 ParentId
1 How are you? Hello Someone 1 Ben some text 5
2 How are you? Hello Someone 1 John other text 5
3 How are you? Hello Anybody 2 Ben some text 5
4 How are you? Hello Anybody 2 Mike other text 5
5 How are you? Greet Anywhere 1 Ben some text 5
6 How are you? Greet Anywhere 1 Luke some text 5
7 I miss you. Really When null null some text 5
8 I miss you. Really When null null some text 5
Display Format:
How are you?
----------------------------------------------
Hello
-----------------------------------------------
1 Someone
----------------------------------------------
Ben some text
---------------------------------------------
John other text
---------------------------------------------
2 Anybody
---------------------------------------------
Ben some text
---------------------------------------------
Mike other text
--------------------------------------------
Greet
-------------------------------------------------
1 Anywhere
-----------------------------------
Ben some text
-------------------------------------
Luke some text
================================================
I miss you
--------------------------------------------------
Really
------------------------------------------------
When
-----------------------------------------------
some text
----------------------------------------
some text
------------------------------------------
denpsia
-- modified at 19:33 Wednesday 21st December, 2005
|
|
|
|
|
|
Thanks for reply, toxcct. I know that but to do this would requires a lot sql Select Distinct queries to filter each column and loop arround results to get next distinct columns and display them at run time.
denpsia
-- modified at 19:09 Thursday 22nd December, 2005
|
|
|
|
|
if you have a lot of distinct to put, it is because the sql query is not well written...
you have to set to correct restrictions in the where clause.
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
|
|
|
|
|
Toxcct,
I know that, just asking if there are much simpler and shorter way to do this. Thanks for reply.
denpsia
|
|
|
|
|
Hi i need a query that can manipulate i.e count records on hour basis from a table depending upon time_in field....
|
|
|
|
|
You are going to have to be more specific.
You can do a count with something like this:
SELECT COUNT(*) FROM MyTable
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
How do you write a query that horizontally merges records from different tables? For example, say I have Person, Phone, and Email tables where Phone and Email have foreign key relationships to the Person table, and store 0 to n email and phone records for each person record.
The result query should include the person's name, all email addresses and all phone numbers, and the email addresses and phone numbers should be listed "side by side". To illustrate in CSV format;
--------------------------------------------------------------
LName, FName, EmailAddress, PhoneNumber
Jones, Sue, sjones@nowhere.com, 123-555-0203
Jones, Sue, sj@somewhere.com, 098-555-2382
Jones, Sue, SueJones@AspenState.com, NULL
Smith, Joe, jsmith@nowhere.com, 123-555-0202
Smith, Joe, js@home.com, 098-555-2382
Smith, Joe, NULL, 230-555-3298
Smith, Joe, NULL, 234-555-0982
Washington, Peter, NULL, NULL
...
--------------------------------------------------------------
In this example Sue has more email records than phone records, so the third record in the result set is NULL for the PhoneNumber field. Similarly, Joe has more email records than phone records so rows 6 and 7 have NULL for the EmailAddress field. Finally Peter has no email or phone records so both PhoneNumber and EmailAddress are NULL.
Any ideas? Thanks
|
|
|
|
|
Hi djsdjsdjsdjs,
Have you tried using DataRelations in DataTable or Joining tables? Just a thought.
denpsia
-- modified at 19:33 Wednesday 21st December, 2005
|
|
|
|
|
While this data will go into an ADO.Net Dataset I'm mainly concerned in how to write the SQL query that gets the data into a single row set that I can use to fill a Dataset.
|
|
|
|
|
if you have this schema :
table1 : table2 :
------------- --------------
col1 <-----\ col1
col2 \ col2
\---- col_tab1_id
then you can "merge horizontally" the two tables like this :
SELECT t1.col1,
t1.col2,
t2.col1,
t2.col2
FROM table1 t1,
table2 t2
WHERE (t1.col1 = t2.col_tab1_id);
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
-- modified at 10:19 Thursday 22nd December, 2005
|
|
|
|
|
This query produces the Cartisian product of the Email and Phone tables and doesn't have any relation to the Person table. These kinds of joins produce every possible combination of rows from the joined tables, but I don't want every possible combination.
|
|
|
|
|
but you add the where clause that condition your query as needed !
i did not read the first message entierly.
ok, just change the query, adding this :
... were Table1.id = Table2.tab1_id;
(or something like this)... d'you follow me ?
[edit]
i modified my first answer... i think it is now as you want.
[/edit]
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
-- modified at 10:22 Thursday 22nd December, 2005
|
|
|
|
|
i've created a new database with 2 table in microsoft access 2003 (.mdb)
I've created a query too, embedded in the .mdb file.
Can i use directly this query in ADO.NET avoiding to write SQL comand in c# code?
|
|
|
|
|
Yes u can. You can refer to this query as the store procedure in C# and call at as you are calling a store procedure.
Akif
|
|
|
|
|
Does anyone know how to use just the time portion of a DateTime object when calling an SQL Query (using SQL Server). I have a field in the database that stores only the Time, and want to do a select based on this field, but it forces me to match the Date portion of the field or it will not work. So, as simple example, if wanted to get all records with time of 9:30am, would use a Command something like:
<br />
DateTime timeToGet = new DateTime(1900,1,1,9,30,0);<br />
SqlCommand command = new SqlCommand();<br />
command.CommandText = "Select * from MyTable where MyTime = @TimeParam";<br />
SqlParameter parameter = new SqlParameter();<br />
parameter.ParameterName = "TimeParam";<br />
parameter.SqlDbType = SqlDbType.DateTime;<br />
parameter.Value = timeToGet;<br />
command.Parameters.Add(parameter);<br />
This will work fine as long as Date portion of field in database = 1/1/1900. But, if date portion is something else then will not find record.
Maybe it would work if could pass it a Time-Only object (instead of DateTime object) but not sure if object like that exists, and if did, not sure parameter object could handle it.
If anyone has any ideas, please let me know.
Thanks,
BK
|
|
|
|
|
SELECT * FROM mytable WHERE CONVERT(CHAR(8), mytime, 8) = CONVERT(CHAR(8), @TimeParam, 8)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
