|
Just as a note, there are _a few_ managed providers (ie like System.Data.SqlClient) for MySql that should work well, and probably a lot faster than an ODBC approach.
I don't remember any links, but search sourceforge.net (or google) or something
Morty
|
|
|
|
|
Thanks Morty
Tweety
It's easy to sit there and say you'd like to have more money. And I guess that's what I like about it. It's easy. Just sitting there, rocking back and forth, wanting that money.
|
|
|
|
|
Hi, I need to install (MICROSOFT EXCEL DRIVER (*.xls) in a computer with win98. Anybody knows what files do I need or when can I get information about it?
My program uses :
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=TRUE;CREATE_DB=\"%s\";DBQ=%s", sDriver, sExcelFile, sExcelFile);
where sDriver must be MICROSOFT EXCEL DRIVER
|
|
|
|
|
Jet 4.0[^] is what you need, I believe.
Paul
Why don't you take a good look at yourself and describe what you see - Led Zeppelin, Misty Mountain Hop
|
|
|
|
|
Ok, thank you.
But if I want to redistribute my app, is there an easier way to do it? I don't want to force to the user to download Jet4.0 , is it not possible to install only excel driver easily?
Thanks.
|
|
|
|
|
José Luis Sogorb wrote:
is it not possible to install only excel driver easily?
That is considered "easily" . It's a 3-ish Mb download and you can even wrap it in your own installer. You probably can find a way to isolate the Excel files and registry entries but it really isn't recommended.
Paul
Why don't you take a good look at yourself and describe what you see - Led Zeppelin, Misty Mountain Hop
|
|
|
|
|
Could someonehelp me to write this stored procedure?
I have two datetime column and ID column(its integer).I pass an ID to stored procedure and want to check if today is between these two date,and return true or false.
Thanks
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
This should work:
<br />
-- create the test table<br />
create table t1(ID int, date1 datetime, date2 datetime)<br />
<br />
-- insert test rows<br />
insert into t1 (ID, date1, date2) values (1, '15 Jan 2001', '20 Feb 2003')<br />
insert into t1 (ID, date1, date2) values (2, '01 Sep 2000', '19 Apr 2001')<br />
<br />
-- drop the proc if it exists<br />
if object_id('sp_CheckDates') is not null drop procedure sp_CheckDates<br />
go<br />
-- create the proc<br />
create procedure sp_CheckDates @ID int<br />
as<br />
declare @Date1 datetime<br />
declare @Date2 datetime<br />
select @Date1 = date1, @Date2 = date2 from t1 where ID = @ID<br />
<br />
if @Date1 is not null and @Date2 is not null begin<br />
if datediff(dd, @Date1, getdate()) > 0 and datediff(dd, @Date2, getdate()) < 0 begin<br />
print 'Bingo!'<br />
return 0<br />
end else begin<br />
raiserror ('Date not in specified range', 16, 1)<br />
return 1<br />
end <br />
end else begin<br />
raiserror('Specified ID does not exist.', 16, 1)<br />
return 1<br />
end<br />
go<br />
<br />
-- test execution<br />
exec sp_CheckDates @ID=1 <br />
|
|
|
|
|
Thanks.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Create Procedure sp_CheckDate (@ID int)
As
DECLARE @Today datetime
SET @Today = GetDate()
Select
Case
When @Today Between DateColumn1 And DateColumn2 Then
CAST(1 As bit)
Else
CAST(0 as bit)
End As Result
From
TableName
Where
IDColumn = @ID
Replace the DateColumn1 , DateColumn2 , IDColumn and TableName with the real names. The procedure will return a row with a single field called Result , which will contain the value.
VBScript:
Function sp_CheckDate(connection, ID)
Dim strSql, rst
On Error Resume Next
strSql = "sp_CheckDate (" & CLng(ID) & ")"
Set rst = connection.Execute(strSql)
If rst.Eof Then
'ID not found, or there was an error
sp_CheckDate = False
Else
sp_CheckDate = CBool(rst.Fields(0).Value)
End If
rst.Close
Set rst = Nothing
End Function
C#:
bool sp_CheckDate(SqlConnection connection, int ID)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "sp_CheckDate";
command.CommandType = CommandType.StoredProcedure;
SqlParameter p = command.Parameters.Add("@ID", DbType.Int32);
p.Value = ID;
try
{
object ret = command.ExecuteScalar();
if (ret is bool)
return (bool)ret;
else
return false;
}
catch (SqlException ex)
{
return false;
}
}
|
|
|
|
|
Thanks.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
I need to take a delimited file and import it into a SQL table. The table is already setup and I have MANUALLY imported the file to populate it and get it going. This file will be updated EVERY night and I want to keep the table updated. How do I automate this? What is the SQL code? Should I drop the table and re-create it nightly?
|
|
|
|
|
In your DTS task (You do have a DTS task, don't you?! ), issue a TRUNCATE TABLE command to delete all rows from the table, and then import the data.
|
|
|
|
|
Reply to the forum, please!
Robby wrote:
i appreciate the information, but is there anyway you can give a code example? i have never created a table from a txt file, only standard SQL INSERT stuff. the file is pipe delimited and column names are on first row...any ideas?
Thanks,
Robby
- Open up SQL Server Enterprise Manager.
- Right-click on the target database, and choose
All Tasks -> Import Data . - For the source, choose
Text File , and select your text file. - Follow the instructions to set up the import.
- In the final screen, select
Scheduled DTS package for later execution , and click on the "... " button to set up the schedule.
|
|
|
|
|
Hi,
We have a SQL database which has gone out to many users and has gone through
an evolved conversion process.
The problem I have is there is a field I need to alter but because it is set
to NOT NULL and has a default value for some users (the problem) sometimes
there is a constraint and sometimes not.
I can check for the constraint using "if exists" but sometimes I do not know
what the constraint is called as its name has been generated by SQL server
and contains a GUID.
Is there a simple clean way I can drop a column and constraints that apply
to it.
I know we are going to have to tidy the overall database up for these users
in the longer term but need a short term quick fix.
We are using MS-SQL server 7 and MS-SQL server 2000.
Thanks
Dave
|
|
|
|
|
Hi!
I develop WIN32 software that uses MySQL database. My software uses MySQL
ODBC driver 3.51 and ADO interface. MDAC 2.7 is installed. My problem is
that while I'm trying to update MySQL record contained char(15) field, I get
an error "Microsoft OLE DB Provider for ODBC Drivers Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available. No
work was done." I don't have such problem with numeric fields. The numeric
fields are updating normally with the same code.
Here is a sample of my code:
try
{
_RecordsetPtr pRecordset;
HRESULT hr;
_bstr_t btmp;
hr = pRecordset->Open(vTable, vConnection,adOpenForwardOnly,
adLockOptimistic, adCmdTableDirect);
hr = pRecordset->AddNew();
...
pRecordset->Fields->GetItem(L"id")->Value=btmp;
...
pRecordset->Fields->GetItem(L"proto")->Value=btmp;
...
pRecordset->Fields->GetItem(L"src_ip")->Value=btmp;
}
The fields "id" and "proto" are numeric, the field "src_ip" is char. And I
get the above error while updating the "src_ip" field. Do you have any
ideas? Can you help me?
Yours sincerely,
Alex Bash
|
|
|
|
|
Hi,
I really need help with a major problem I have encountered. I'm trying to insert information into a SQL Server 2000 database using stored procedures. And each table that has a Primary Key (int) won't return a value using SELECT @@IDENTITY. The error states that ArtistID doesn't except null values and will not insert into table. Here's the source code for the stored procedure:
CREATE PROCEDURE ArtXchangeWeb.procManagerInsertArtist
@ArtistFName text,
@ArtistLName text,
@ArtistBirth text,
@ArtistDeath text,
@ArtistCountry text,
@ArtistNationality text,
@ArtistMovement text,
@ArtistBio text,
@ArtistPhoto varchar(255)
AS
BEGIN
INSERT INTO Artist(ArtistFName, ArtistLName, ArtistBirth, ArtistDeath, ArtistCountry, ArtistNationality,
ArtistMovement, ArtistBio, ArtistPhoto)
VALUES(@ArtistFName, @ArtistLName, @ArtistBirth, @ArtistDeath, @ArtistCountry, @ArtistNationality, @ArtistMovement, @ArtistBio, @ArtistPhoto)
/* Return the ID of the new artist */
SELECT @@IDENTITY
END
GO
If it doesn't give me the previous error it gives me a Input String not the right type error. Here's the code behind .vb:
If IsPostBack Then
'Save the new product to the database
With cmdManagerInsertArtist
.Parameters("@ArtistFName").Value = txtFName.Text()
.Parameters("@ArtistLName").Value = txtLName.Text()
.Parameters("@ArtistBirth").Value = txtBirth.Text()
.Parameters("@ArtistDeath").Value = txtDeath.Text()
.Parameters("@ArtistCountry").Value = ddlCountry.SelectedItem.Text
.Parameters("@ArtistMovement").Value = ddlMovement.SelectedItem.Text
.Parameters("@ArtistNationality").Value = ddlNationality.SelectedItem.Text
.Parameters("@ArtistBio").Value = txtBio.Text()
.Parameters("@ArtistPhoto").Value = txtArtistImg.Text()
cnn.Open()
Session("ArtistID") = .ExecuteScalar
cnn.Close()
'And redirect to the management page
Server.Transfer("ManageArtist.aspx")
End With
If you can help me out that would be greatful. I wouldn't be a programmer if it wasn't for sites like yours and I've searched around for answer to my problem and to no avail I'm still stuck in the same spot. I hope you can help me.
Thanks,
Gregory Foreman
|
|
|
|
|
Can you send us the table definition? It seems that the ArtistID column is not an IDENTITY column.. Could you check it?
Another tip: MS does not recommend you to use the @@IDENTITY for this, because it has a nasty definition that causes some weird bugs with triggers. Always use the SCOPE_IDENTITY() function.
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|
Yes, the ArtistId can't be an IDENTITY column if that message appears.
He should also consider using a different datatype than the all-mighty "text" for his input parameters.
Morty
|
|
|
|
|
|
Yeah.. and that sort of sucks because that specific type is (sometimes?) transferred differently than normal SQL statements/types in order to handle the large amount of data it MIGHT contain. (2 147 483 647 bytes to be exact)
Morty
|
|
|
|
|
I have a form that binds controls to a dataset. Some of the fields in the dataset are date fields, so I am using DateTimePicker controls to display (and allow editing of) these fields.
However, the date field in question can be null. When this occurs navigation through the records in the dataset is stopped, that is setting the Position property on the BindingContext does not update the controls.
Is there a workaround that allows binding to null dates?
Derek Lakin.
I wish I was what I thought I was when I wished I was what I am.
Salamander Software Ltd.
|
|
|
|
|
Ok so I am using a SqlDataReader in ADO.NET because I do not want or need the overhead of a DataSet.
But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh.
DOTNET247 seems to say that there is no way other than either looping through or using horrid SQL count statements.
So anybody got a magic bullet? Or should I just bite the bullet and do that SQL count?
|
|
|
|
|
Do the SQL method... This bit me too
[Edit] From what I rememebr, the fact that the datareader does not have a record count property stems from the fact that it realy contains NO records. It is more of a forward only conduit to the data. Not sure if that is true, but it certainly makes sense [/Edit]
|
|
|
|
|
You can't, because it's a forward-only cursor.
Paul Watson wrote:
But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh.
You can create an ArrayList and, at the end, use the ArrayList.CopyTo method for copying it to typed array.
Since this will only copy object references, not the actual values, it's very fast. Normally much, much faster than a SQL count.
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|