|
Hi guys,
I've a problem opening a CRecordset object on an Oracle database
using the Microsoft ODBC for Oracle.
The query I'm using is something like the following:
SELECT MAX(OBJECTID) FROM MY_TABLE
the CRecordset object is a dynaset.
When I execute the code I get an error from the ODBC driver
tha says that is not possible to use keyset cursor in join
operations with distinct, union, intersect or minus conditions
or on read only dataset.
Can someone tell me how to solve the problem ?
Thanks a lot !
|
|
|
|
|
hi,
In my application i need to import the excel sheet data into a database table "Emp_Profile_Tbl" which is created in the database.
Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Team wise EMP Profile (2).xls") + ";Extended Properties=Excel 8.0;"
Dim objconn As New OleDbConnection(connstr)
Dim ds As New DataSet
Dim objadapter As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", objconn)
objadapter.TableMappings.Add("Table", "Emp_Profile_Tbl")
Try
'in excel: insert->name->seleziono il range:nomi
objconn.Open()
objadapter.Fill(ds)
dg_empprofile.DataSource = ds.Tables(0).DefaultView
dg_empprofile.DataBind()
Finally
objconn.Close()
End Try
i'm getting the values into datgrid but the values are not saving in "Emp_Profile_Tbl" database table.
pls help me to sort this out.
Thank u,
modified on Thursday, December 13, 2007 2:37:51 AM
|
|
|
|
|
sivaram praveen wrote: i'm getting the values into datgrid but the values are not saving in "Emp_Profile_Tbl" database table.
You haven't written any code to save the data back to your database, so it's hardly surprising that nothing is happening. You need to specify insert/update/delete commands for your data adapter so that changes to the dataset can be propagated back to the database. Read this article[^] for more information.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
The Compete Guide to ODBC Escape Sequences in Firebird and Interbase.
ODBC Escape Sequences are SQL extension for writing SQL queries which will be compatible with many DBMS. Guide includes descriptions and samples for 69 scalar functions and 3 escape sequences.
Get it here
Table of Contents
1. What are ODBC Escape Sequences?
2. Why use ODBC Escape sequences?
3. Where can I use ODBC Escape Sequences?
4. Date, Time and Timestamp Escape Sequences.
5. Procedure Call Escape Sequence.
6. Scalar Function Escape Sequence.
7. Which functions supports in my database server?
• String functions.
• Numeric functions.
• Date and time functions.
• System functions.
• Explicit Data Type Conversion Function.
8. Functions Description and the samples of functions calls.
• String functions.
• Numeric Functions.
• Date and time functions.
• System functions.
• Explicit Data Type Conversion Function.
Read complete article about ODBC Escape Sequences
|
|
|
|
|
Guys pls Help me,
below is my procedure, i need to flag it as *, here is what iam doing
ALTER PROC [dbo].[uprUpfronts_GetByIntroducerDate]<br />
@IntroducerID int,<br />
@BalanceID int,<br />
@UserID varchar(10)<br />
AS<br />
BEGIN<br />
<br />
--SET LoanID = U.LoanID as varchar (10)<br />
SELECT CASE<br />
WHEN EXISTS (SELECT LoanID FROM FastLoan_Applications.dbo.tblLoanPurpose WHERE LoanID = U.LoanID) <br />
THEN ' * ' + CAST(U.LoanID AS varchar(10)) <br />
ELSE U.LoanID <br />
END [Loan Number],<br />
A.ShortName, <br />
CASE<br />
WHEN U.LoanIncreaseID IS NULL THEN LS.StageDate<br />
ELSE LIS.StageDate<br />
END [Settlement], U.LoanAmount, U.UpfrontRate,<br />
U.UpfrontAmount, U.ApplicationFee, U.UpfrontGST, U.UpfrontTotal,<br />
ISNULL(US.Name, L.IntroducerRef) [SalesPerson], <br />
U.CommissionRunID<br />
FROM FastLoan_Commissions.dbo.tblUpfront U<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = U.LoanID<br />
LEFT JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS ON LS.LoanID = L.LoanID<br />
AND LS.LoanStageTypeID = 6<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS ON LIS.LoanIncreaseID = U.LoanIncreaseID<br />
AND LIS.LoanStageTypeID = 6<br />
LEFT JOIN FastLoan_Applications.dbo.zstblUser US ON US.UserID = A.IntroducerSalesPersonID<br />
WHERE U.BalanceID = @BalanceID<br />
AND U.IntroducerID = @IntroducerID<br />
<br />
ORDER BY Settlement
but when i select te=he report i got the following error Can pls any one help me
Thanks in advance..
|
|
|
|
|
well I'm sorry I didnt read ur complete code properly but I think u need to use ISNULL (If its SQL SERVER) to convert the nulls to zero or aply a check to see if the value is '' then change it to '0'
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Thx for the reply, my requirement is i need to add '*' to the column, pls go through the query once, if you find time.
|
|
|
|
|
Sorry i need to convert Int to varchar to add * or flag it, to identify that is exist in another table.
thanks heaps..
|
|
|
|
|
I use "INSERT INTO [Flash Turn Parameters] " +
"VALUES ('" + toolPosition1.ToS .....
to create a new record in a database table
and use cmd.ExecuteNonQuery(); to execute the SQL from C# ADO
the 1st field is an auto increment IDENTITY field. How can I find out the value after the record has been created ?
Thanks
|
|
|
|
|
use a stored proc like this:
create proc Insert @value int
declare @id int
insert into [bad table name] values (@value)
set @id = scope_identity()
return @id
You could also make it an out parameter
create proc Insert @value int, @id int out
insert into [bad table name] values (@value)
set @id = scope_identity()
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You need to write a stored procedure that performs the INSERT operation and then returns the latest IDENTITY value as an OUTPUT parameter. The latest IDENTITY value can be determined by using the SCOPE_IDENTITY[^] command.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hello everyone,
how can i create an updatable view in sql server 2005.
Thanks.
|
|
|
|
|
I suggest you read this article[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hello,
i have to develop an application to compare a dataset (with around 1000s of records)with a database table which has almost more than 4 million records.
and if the dataset has matches in the table i will mark them as processed. is there any way to achive this .
note: i already have tried comparing them with 2 loops iterating manually in each record...i want some other way
aneef
|
|
|
|
|
Assuming you are using SQL Server, a possible solution would be:
1. Serialize the contents of your dataset as XML and submit the data to your database using a stored procedure and the OPENXML command.
2. In your stored procedure, load the serialized data into a table variable whose structure matches the dataset. Add an extra bit field called Processed that can be used to mark matched records.
3. Run an UPDATE query on your table variable, joining it to your master database table. Set the value of your Processed field in the matching records to 1.
4. From your stored procedure, return all records in the table variable where Processed = 1.
Hope this helps.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
I want to know what are the naming conventions for creating the above mentioned? I went through some of the adventure works tables and looking at the indexes they either start with IX or AK, for example:
IX_EmployeeDepartmentHistory_DepartmentID
Please advise.
Brendan
|
|
|
|
|
Hi,
I have 3 tables, namely:
CategoryGroup
CategoriesInCategoryGroup
Categorty
CategoriesInCategoryGroup has only 2 fields, which I made the primary key each, namely CategoryGroupId and CategortyId, and they each reference the corresponding table. So basically they are foreign keys.
I have a question when creating an index in the CategoriesInCategoryGroup table. Do I create 1 index, or should I create a separate index for CategoryGroupId and CategortyId in the CategoriesInCategoryGroup table?
Please advise.
Brendan
|
|
|
|
|
Is always a good idea to create an index for each field (or group of fields) that forms a FK
Only in few cases you could not create such an index, i.e. when it is the first field in an already existing index or is equal to de PK.
Habetis bona deum
|
|
|
|
|
Hi Brendan
I would expect CategoriesInCategoryGroup to have CategoryGroupId and CategortyId as a clustered primary key. You would then normally create a separate non-clustered index on the CategoryId column.
If you are not using SQL-Server then the second index should be combine CategortyId and CategoryGroupId.
Regards
Andy
|
|
|
|
|
OK, I realise this is generally a SQL server-oriented forum, but I'm a bit stumped so I'll post anyway.
I was debugging an Oracle package which does a massive number of updates (9,6000,000 approx.) and it hung. I tried to kill the process but that didn't work either, and I had to kill TOAD eventually. When I went back in, I found that Oracle had placed a lock on the package, so I can't compile, change, or even drop the object. Does anyone know where I need to go in Oracle to release this lock? I don't have a DBA on-site here, I'm pretty much on my own.
Smokie, this is not 'Nam. This is bowling. There are rules.
|
|
|
|
|
Hi,
if we are trying to add the several columns in select statement without using Aggregate functions and only one condition in group by.
how can we do that ??
|
|
|
|
|
I'm not sure I understand you. Can you throw some SQL my way...
"When you have made evil the means of survival, do not expect men to remain good. Do not expect them to stay moral and lose their lives for the purpose of becoming the fodder of the immoral. Do not expect them to produce, when production is punished and looting rewarded. Do not ask, `Who is destroying the world?' You are."
-Atlas Shrugged, Ayn Rand
|
|
|
|
|
praveenanand wrote: if we are trying to add the several columns in select statement without using Aggregate functions and only one condition in group by.
You can't. Each column not specified in the GROUP BY clause must be part of an aggregate on the SELECT. If you think about what it is doing you will see why.
|
|
|
|
|
Hello,
I'm working on a project and its deadline is comming really close. I have a State and cities Table to store the names of the states and cities of the US. I've populated the State Table manually but the cities is like quite a hectic task. Is there some way that I can load the names of teh cities programaticaly (thru a file or something) in to the db
I'm using SQL 2005 and .NET 2.0
Any suggestions, guidlines are welcome
Thanks in advance
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
When I needed to load a list of all the countries in the world I used an XMLDataSource and found an XML file with all the countries in it. I think I found the file at the US Census Bureau web site if I'm not mistaken.
I'm sure you could find an XML list with all the cities and then you could load it and save it to the db.
And how do you manage to submit a post on the 13th of December? Pretty crazy.
"When you have made evil the means of survival, do not expect men to remain good. Do not expect them to stay moral and lose their lives for the purpose of becoming the fodder of the immoral. Do not expect them to produce, when production is punished and looting rewarded. Do not ask, `Who is destroying the world?' You are."
-Atlas Shrugged, Ayn Rand
|
|
|
|