Database

Frank Kerrigan4-Dec-06 3:13

4-Dec-06 3:13

Search codeproject for Excel ADO. It may be better to use Access if you can, then use Excel to pull updated data from the access db.

Blog Have I http:\\www.frankkerrigan.com

Cristoff4-Dec-06 4:04

Cristoff

4-Dec-06 4:04

Thank you for your advice. I've found some information that might be useful.

I want to make an application that uses a simple database that does not need SQL server, Access or MS Office installed on the computer.

I currently use Excel to do my work but I wan't to be able to do it without it.

Frank Kerrigan4-Dec-06 5:52

4-Dec-06 5:52

You don't need MS access or office on a machine to use an access database with a website.

Blog Have I http:\\www.frankkerrigan.com

Scott Serl4-Dec-06 6:53

Scott Serl

4-Dec-06 6:53

Using an access database is preferable to Excel, and you don't need Access or Office installed. But if you must use Excel, here is an example connection string for you:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Path\MyExcelFilename.xls";Extended Properties="Excel 8.0;HDR=NO;IMEX=1;ImportMixedTypes=Text"
I can't remember what IMEX is, but HDR is whether the first row of data is a header row or not.

Cristoff5-Dec-06 1:33

Cristoff

5-Dec-06 1:33

Thank you very much for your advice. I've found a sample on this site that seems as an excellent starting point.

I'll think about the access database too but creating an sample access database (or anyone) does require MS Access?

Need help in writting a sql2000 query

Scott Serl5-Dec-06 7:35

Scott Serl

5-Dec-06 7:35

I have not created Access databases fom code in a long time, but it can be done. I think you have to add a reference to ADOX library.
Yes, here is a link http://www.freevbcode.com/ShowCode.asp?ID=5797[^] to an example.

tmiku4-Dec-06 0:07

tmiku

4-Dec-06 0:07

hi,
i have table let in the following format
Col-A col-B col-C col-D col-E
Row-1 1 2 3 4 E1
Row-2 1 2 3 4 E2
Row-3 1 2 3 4 E3
Row-4 1 2 3 4 E4

so i need the output in the format

Col-A col-B col-C col-D col-E
Row-1 1 2 3 4 E1/E2/E3/E4

i cannt able to find the way to write the SQL for it

Re: Need help in writting a sql2000 query

Kamalonline44-Dec-06 20:05

Kamalonline4

4-Dec-06 20:05

I think there is no option to do this using queries.You have to go for the cursors.

Kamal

Re: Need help in writting a sql2000 query

Polymorpher4-Dec-06 23:54

Polymorpher

4-Dec-06 23:54

SELECT Col-E FROM tblSomething WHERE Col-A >= 1 AND Col-A <=4

Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.

Connection of SQL server using IP address

priyank_ldce3-Dec-06 23:50

priyank_ldce

3-Dec-06 23:50

Hi, I am using ODBC to connect my VC++.NET application to connect to SQL server. I can connect any server in the LAN using server name. Is it possible to connect remote server over internet using IP address? Currently I am using connection string like

"connStr = "Driver={SQL Server};Server=server_name;Database=db_name;Trusted_Connection=yes";"

Any suggestions?
Thanks in advance.

Re: Connection of SQL server using IP address

Frank Kerrigan4-Dec-06 3:15

Re: Connection of SQL server using IP address

4-Dec-06 3:15

Yes you can but the TrustedConnetion will likley not work. You have to use a SQL username and password.

Please bear in mind that exposing SQL server or another db to the internet is highly dangerous.

Blog Have I http:\\www.frankkerrigan.com

Stathread4-Dec-06 16:14

Stathread

4-Dec-06 16:14

Follow the below link. This website is a nice reference.

http://www.connectionstrings.com/

Error while accessing SqlBulkCopy class

honey1233-Dec-06 22:19

honey123

3-Dec-06 22:19

Hi,

I am trying to convert Excel Data to SqlServer for that i am using SqlBulkCopy class under System.Data.SqlClient namespace.
While definin the instance for SqlBulkCopy class i am getting error ...

c:\inetpub\wwwroot\ConvertXLStoSQL\WebForm1.aspx.cs(47): The type or namespace name 'SqlBulkCopy' could not be found (are you missing a using directive or an assembly reference?)

It's very urgent please help me to come out from this problem if posible.

Thanks,

Honey

Re: Error while accessing SqlBulkCopy class

Frank Kerrigan4-Dec-06 3:17

Query Regrding SQLDMO Foreign key column name

4-Dec-06 3:17

Try and use DTS as it is fairly easy to connect EXCEL via dts. I do it all the time with no issues.

Blog Have I http:\\www.frankkerrigan.com

ruparanju3-Dec-06 17:37

ruparanju

3-Dec-06 17:37

Hi frndz,
I want name of the foreign key column in a table using sqldmo ,can any one help me.

Rupa

query's problem with periode

bayangan13-Dec-06 11:22

3-Dec-06 11:22

supposed i have this table, the date is in d/m/y format

value date
100 6/11/2006
100 7/11/2006
100 8/11/2006
100 9/11/2006
100 10/11/2006
200 11/11/2006
200 12/11/2006
200 13/11/2006
200 14/11/2006
100 15/11/2006
100 16/11/2006
100 17/11/2006
200 18/11/2006
130 19/11/2006
130 20/11/2006

i want the result to be like this:

value min_date max_date
100 6/11/2006 10/11/2006
200 11/11/2006 14/11/2006
100 15/11/2006 17/11/2006
200 18/11/2006 18/11/2006
130 19/11/2006 20/11/2006

The only way i know to work this out is to make a cursor table, fetch each record and compare the value. If next value is different from first fetch up value then move to prior record, record it's date as max date. Put that value in temporary table.

Is it possible to retrieve the set i want like above example with only one select statement? I have failed to use select with group by clause.

Can anyone help me? I really need to get it done with only 1 select statement

mr_lasseter3-Dec-06 11:51

3-Dec-06 11:51

select value, min_date, max_date
from
(select value, min(date) min_date
from table
group by value) a,
(select value, max(date) max_date
from table
group by value) b
where a.value = b.value

Mike Lasseter

bayangan13-Dec-06 12:14

3-Dec-06 12:14

mr_lasseter wrote:
select value, min_date, max_date
from
(select value, min(date) min_date
from table
group by value) a,
(select value, max(date) max_date
from table
group by value) b
where a.value = b.value

Mike Lasseter

thanks for quick reply. Your query not doing exactly as i requested. Value 100 and 200 would be in the same block.

100 6/11/2006 17/11/2006
I need it to be:
100 6/11/2006 10/11/2006
100 15/11/2006 17/11/2006

200 11/11/2006 18/11/2006
I need it to be:
200 11/11/2006 14/11/2006
200 18/11/2006 18/11/2006

mr_lasseter3-Dec-06 12:59

3-Dec-06 12:59

I am not sure in what context you are using this table, but if I was you I would seriously consider rethinking your design. What happens if records get entered into the table out of order? Is there a primary key on this table?

In addition, AFAIK there is no guarantee that select * from table will always pull the results in the same order.

Mike Lasseter

bayangan13-Dec-06 13:23

3-Dec-06 13:23

Actually there are 3 primary keys on the original table. I only pull out 2 definitive fields (value and date) from that table which is pointing out directly to the problem i had. Both fields are not primary keys.

I'm going to use the query in reporting service as a part of DataSet.

mr_lasseter4-Dec-06 2:46

Re: query's problem with periode [modified]

4-Dec-06 2:46

How do you determine that the first group of 100 is not the same as the second group of 100?

Mike Lasseter

bayangan14-Dec-06 13:55

4-Dec-06 13:55

This is a bank's application where for each change value, i need to store it's period, calculate tenor (max date - min date) and then times it with pre-stored fee to get amount to charge. Bank charge the customer based on change value, that's why the 100 must not be grouped, it has to be split

Table's below will be included as part of customer's invoice for Nov 2006

Stock        Period                    Amount   Tenor Fee (%) CCY Charge
  Stock001    1 Nov 2006 - 23 Nov 2006 100.000     22    0.03 USD     30
             23 Nov 2006 - 25 Nov 2006 200.000      2    0.03 USD     60
             25 Nov 2006 - 30 Nov 2006 100.000      5    0.03 USD     30
                                              SubTotal:     USD    120

I just wonder whether this query with period could be done with only 1 select statement. Is it possible or not using a bit complex group by statement.

-- modified at 20:04 Monday 4th December, 2006

mr_lasseter4-Dec-06 16:31

4-Dec-06 16:31

Is period stored as a field in the table?

Mike Lasseter

bayangan14-Dec-06 23:30

4-Dec-06 23:30