How to fetch data and display in Excel using Macro(VBA) - Database Discussion Boards

Re: Distinct in Quey

Blue_Boy16-Jul-07 5:26

Blue_Boy

16-Jul-07 5:26

You deserve FIVE(5) and you allready get it by me, even though the soulution wasn't for me.

I Love SQL

Updating Bulk Record in oracle 10 g,

nabeelkhan16-Jul-07 1:34

nabeelkhan

16-Jul-07 1:34

Hi,
i hava dataGrid of 100,000 record ,i want to update the Edited data in the
Oracle DataBase,but i dont want to use the Adapter.update() method ,i want to
send the whole data to my storedProcedur and that procedure will update the Record in the database.
best regards
nabeel

Re: Updating Bulk Record in oracle 10 g,

andyharman16-Jul-07 5:17

andyharman

16-Jul-07 5:17

Is this[^] what you want?

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

DateTime Problem

Mohammed Elkholy16-Jul-07 1:15

Mohammed Elkholy

16-Jul-07 1:15

hi
i have a problem here

i have a column in database the data type is DateTime

i insert into this column Time only LIKE " 09 : 00 AM "
but after i insert data it stores in table date with the time i entered

the problem is
i want to select the data from this column as Time Only

i try this select convert(smalldatetime ,ExamTime,108)

but i back by the date also

how can solve my problem

thnx 4 help

MD_NADA

Re: DateTime Problem

Blue_Boy16-Jul-07 2:35

Blue_Boy

16-Jul-07 2:35

did you try to convert in varchar type?

select convert(varchar,ExamTime,108)

I Love SQL

Re: DateTime Problem

sidbaruah17-Jul-07 19:03

sidbaruah

17-Jul-07 19:03

One quick question
I am using this
replace(convert(varchar(16),cast([WEEK] as datetime),111),' ','-')

How does it differ from

Blue_Boy wrote:
select convert(varchar,ExamTime,108)

I was born dumb!!
Laugh | :laugh:

Programming made me laugh Laugh | :laugh:

!!!
--sid--

Re: DateTime Problem

Kschuler16-Jul-07 9:23

Kschuler

16-Jul-07 9:23

Another option would be to store the time as a number, and when you retrieve it from your database, you can format it in your own way. For example, I store time fields as 6 digit numbers. The first two digits for the hour (military time) the second two digits as minutes, and the last two digits as seconds. So when I store them to my data base I only have to take the datetime object and do this:

myDataBaseRow("myTimeColumn") = objDateTime.ToString("HHmmss")

I know this isn't what you asked for, but I thought another view point might be helpful.

How to fetch data and display in Excel using Macro(VBA)

Tech_spidy16-Jul-07 0:00

Tech_spidy

16-Jul-07 0:00

Hi all,

I can able to fetch a single data from Sql and display it in the Excel sheet using Macro, this is the code.
I am getting in the Text Box:
sno = TextBox1.Text
When i am clicking the Ok button:
Query = "Select sname,scity from smaster where snum='" & sno & "'"
Set QueryRs = oConn.Execute(Query)
Sheet1.Cells(1, 1) = QueryRs("sname")
Sheet.Cells(1, 2) = QueryRs("scity")
Result:
Name, city displayed in the Excel sheet1

Now my problem is how to fetch multiple data and display (like how to use loop in it)

waiting for timely help

Tech_spidy

Re: How to fetch data and display in Excel using Macro(VBA)

TomGarth16-Jul-07 4:11

TomGarth

16-Jul-07 4:11

Follow the link for 8 or 9 possible methods, including code samples.

http://support.microsoft.com/kb/247412/EN-US/[^]

Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia

how to write query

harithadotnet15-Jul-07 23:08

harithadotnet

15-Jul-07 23:08

Hi

I have created one table in sql server as

sno name desc
5 suma dckdfjdskfldkf;dsf
3 suma lklkjdkjfkjfdjbbbbb
8 suma jsakdksladksadks
4 manu nsdskdjsdsadl
2 manu klswkqewr
1 manu shdjsdksd

like this....

now i want to dispaly one record fro each user (i.e the record which is having max sno for each user)

i.e

8 suma jsakdksladksadks
4 manu nsdskdjsdsadl

how to write query for this

plz help me

Thanks in advance

Haritha

Re: how to write query

Colin Angus Mackay15-Jul-07 23:23

Colin Angus Mackay

15-Jul-07 23:23

Here is the bit that will get the highest sno for each user
SELECT MAX(sno) AS sno, name FROM MyTable GROUP BY name

Add that as a subquery and join on to it like another table. Like this:

SELECT s.sno, s.name, m.desc
FROM MyTable AS m
INNER JOIN (SELECT MAX(sno) AS sno, name FROM MyTable GROUP BY name) AS s
ON s.sno = m.sno AND s.name = m.name

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

Ready to Give up - Your help will be much appreciated.

My website

Re: how to write query

PrakashBhaskar15-Jul-07 23:28

PrakashBhaskar

15-Jul-07 23:28

for this you have to use corolated queries following the example

SELECT sno, name, desc FROM WHERE sno=(SELECT MAX(sno) FROM

Thanks
Warm Regards
Prakash-B

Re: how to write query

Colin Angus Mackay15-Jul-07 23:33

Colin Angus Mackay

15-Jul-07 23:33

PrakashBhaskar wrote:
for this you have to use corolated queries following the example

SELECT sno, name, desc FROM <table name> WHERE sno=(SELECT MAX(sno) FROM <table mame)

I think ur problem has solved

That will return the row "8 suma jsakdksladksadks" only.

Re: how to write query

PrakashBhaskar15-Jul-07 23:39

PrakashBhaskar

15-Jul-07 23:39

i am sorry i have mis-understood the question

Thanks
Warm Regards
Prakash-B

Re: how to write query

Dipti D Jadhav16-Jul-07 0:15

Dipti D Jadhav

16-Jul-07 0:15

You can use following query

select max(no),name from trial1
group by name

If have any questions, thn let me know

Re: how to write query

Colin Angus Mackay16-Jul-07 0:47

Colin Angus Mackay

16-Jul-07 0:47

d_d_jadhav wrote:
You can use following query

select max(no),name from trial1
group by name

If have any questions, thn let me know

No, that will result in:

8 suma

only. Which does not match the output the OP requested.

Re: how to write query

Dipti D Jadhav16-Jul-07 0:16

Dipti D Jadhav

16-Jul-07 0:16

You can use following query

select max(no),name from trial1
group by name

If have any questions, thn let me know

Dipti Jadhav

Re: how to write query

Sathesh Sakthivel16-Jul-07 1:08

Sathesh Sakthivel

16-Jul-07 1:08

One post is enough.

Regards,

Satips. Rose | [Rose]

Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus

Re: how to write query

Dipti D Jadhav16-Jul-07 19:03

Dipti D Jadhav

16-Jul-07 19:03

The answer for ur question is here

select sno,name,[desc] from trial1
where convert(varchar(50),sno)+name in(select convert(varchar(50),max(sno))+name from trial1
group by name)

Please let me know, if u have any questions/comments.

displaying a data in textbox

Rharzkie15-Jul-07 22:57

Rharzkie

15-Jul-07 22:57

Its me again, i making a grade data entry. I have a database of diffenrent subjects. The fields that i will going to use is these: Subject code, Subject description, and professor.

What I want is to view the subject description and the professor in the textboxes when I select a subject code in the combo box.

I had already made an OleDbDataAdapter for that, and the subject codes was the data member in the combobox. The subject codes is already the item of the combobox but my problem is, the subject description and professor was not appeared in the textbox. What I want is this to view to its corresponding textboxes.

I used this code:

For ctr = 0 To DataSet31.studentsubjects.Rows.Count - 1<br />
            dr = DataSet31.studentsubjects.Rows(ctr)<br />
            If ComboBox1.SelectedItem = dr("subject") Then<br />
                subjdesc.Text = dr("subjdesc")<br />
                prof.Text = dr("professor")<br />
            End If<br />
        Next

Is the code above correct??? If its not, what is the right code????

Where will I going to put the codes???

Re: displaying a data in textbox

Colin Angus Mackay15-Jul-07 23:28

Colin Angus Mackay

15-Jul-07 23:28

Rharzkie wrote:
ComboBox1

Can you please name your variables with good names rather than accept the defaults that Visual Studio gives you.

Rharzkie wrote:
ComboBox1.SelectedItem = dr("subject")

What does ComboBox1.SelectedItem return? Put a break point down and see what value it is returning. Also it will aid the debugging process if you assign the result of method and property calls to something then test on the local variables - it will make it easier to see what is going on in the debugger.

Re: displaying a data in textbox

Rharzkie15-Jul-07 23:59

Rharzkie

15-Jul-07 23:59

The ComboBox1 is for the list of the subject code. I use the DISTINCT in query builder to display the items in the combobox. I use the OleDbDataAdapter1 for that.

But the connection that i will going to display in textbox is OleDbDataAdapter3. In short, i use different connection from the combobox and the textboxes. Is this correct?

Re: displaying a data in textbox

Colin Angus Mackay16-Jul-07 0:53

Colin Angus Mackay

16-Jul-07 0:53

Rharzkie wrote:
The ComboBox1 is for the list of the subject code.

Yes, but ComboBox1 still isn't a good name for it, is it?

Rharzkie wrote:
OleDbDataAdapter1

Rharzkie wrote:
OleDbDataAdapter3

How do you keep track of what each of these does?

Rharzkie wrote:
Is this correct?

I have no idea. This code is obviously a mess. My suggestion is to start by naming things properly. Then it might be an idea to create a DAL and remove all your database interaction out of the user interface classes. It might also be an idea to do what I asked in my previous post and answer the question I asked. ("What does ComboBox1.SelectedItem return?" - And I want actual values and object types, not what you think it should return)

Date format in cell

Sam Heller15-Jul-07 22:19

Sam Heller

15-Jul-07 22:19

I currently have an app up and running in .NET. We have just built the server for the app to run on but there seems to be a problem with some of the returned date formats within the app itself. I checked the original test db and the date format was english style 31/12/2007 but on the new database it was amercian style 12/31/2007. Is there a setting in SQL server 2005 that would aler this?

Re: Date format in cell

Colin Angus Mackay15-Jul-07 23:35

Colin Angus Mackay

15-Jul-07 23:35

Dates are not stored any any locale dependent format. When you get data out the database you should get a DateTime object which you can then format as you need with ToString()

e.g. myDate.ToString("dd/MM/yyyy")

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.