|
Does anyone know how to execute a parameterized stored procedure that returns a recordset using ADO .NET? I noticed neither direct Parameter members on the DataAdapter nor Fill members on a db_Command. My workaround has been to use an Adapter to execute a query such as "EXECUTE Procedure 1, 2, 3". I would prefer not to execute a reader.
--1st method
System::Data::OleDb::OleDbCommand ^db_Command=gcnew System::Data::OleDb::OleDbCommand(gcnew System::String(Procedure_Name), db_Conn);
db_Command->CommandType = System::Data::CommandType::StoredProcedure;
db_Command->Parameters->Add("usr_id", System::Data::OleDb::OleDbType::Integer, 4);
db_Command->Parameters[0]->Value=1;
--2nd method --- I get an error about the parameter not being supplied.
System::Data::OleDb::OleDbDataAdapter ^db_Adapter=gcnew System::Data::OleDb::OleDbDataAdapter( "exec "+gcnew System::String(Procedure_Name), db_Conn);
db_Adapter->SelectCommand->Parameters->Add("usr_id", System::Data::OleDb::OleDbType::Integer, 4);
db_Adapter->SelectCommand->Parameters[0]->Value=1;
System::Data::DataSet ^db_DataSet=gcnew System::Data::DataSet;
int const Fill_Result=db_Adapter->Fill( db_DataSet); //note that an error will be thrown if the query is invalid
modified on Thursday, November 26, 2009 3:13 AM
|
|
|
|
|
You should have posted this in C++ forum.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
I tried my best to Normalize the tables but someone suggested me there is something wrong.
Could anyone please help me out.
COURSE[CourseCode, CourseName]
INSTRUCTOR[InstructorNumber, InstructorName]
CLASS[CourseCode, ClassCode, InstrNo, InstrName, CourseStartDate]
ENROLLEMENT[CourseCode, ClassCode, StudentNumber, StudentName,Grade]
Relationships: The relationship I have defined are as below.
Instructor and Class tables: 1 to many.
Class and Course: 1 to many
Thanks
|
|
|
|
|
Generally speaking, each data item (that is not a primary or foreign key) should only appear in one table, otherwise you will have referential integrity issues.
Course table - looks fine.
Instructor table - looks fine.
Class table - Probably doesn't need a composite key between ClassCode and CourseCode, however I assume InstrName is the Instructor name - this doesn't need to be in there, as it's in the Instructor Table.
Enrollment table - Students enrol in a class. You probably need a StudentNumber and a ClassCode, and a Grade. This of course depends on changes you make to your class table.
Add a Student table in - StudentNumber, StudentName etc etc.
Hope this points you in the right direction.
|
|
|
|
|
I suggest you get your naming convention sorted first, decide whether you are going to use CourseCode, CourseNo, CourseID and stick to it religiously, your life will be much happier. As a principle I avoid composite keys, this is a personal choice, same as I wash my hands after taking a pee. I would have class with a primary key of classid and a foreign key to course unless it is a many to many in which case you need another table ClassCourseLink
Course is ok
Instructor is ok
Can a class only have 1 instructor - are you sure, what about relief instructors
Can a class be in only 1 course, then use a many to many table
Enrolment should be Student > Class no refernce to course
|
|
|
|
|
Mycroft Holmes wrote: As a principle I avoid composite keys
Composite indices can on the other hand under certain circumstances considerably speed up a query.
|
|
|
|
|
Jörgen Andersson wrote: Composite indices can on the other hand under certain circumstances considerably speed up a query
Ah but I was being derogetry about composite KEYS not indexes. Composite indexes are a basic tool for tuning a database, composite keys annoy me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks you fellows for your guidance. I have corrected alot of mistakes I had made earlier. I have revised and resposted my work again. I am not sure of the Class and Enrollement tables relationship.
Isn't whenever the Foregin Key is involved it becomes 1 to many relationship. Please correct me. Also, shouldn't the CourseStartDate and CourseExpiryDate be in the Class table and student enrolledDate in Enrollement table.
STUDENT[StudentID(PK), StudentFName]
COURSE[CourseID(PK), CourseName]
INSTRUCTOR[InstructorID(PK), InstructorName]
CLASS[ClassID(PK),CourseID(FK), InstructorID, Location, CourseStartDate,CourseExpiryDate]
ENROLLEMENT[ClassID(PK), StudentID(PK),Grade, EnrolledDate]
Relationships are as follows:
Course and Class: 1 to many
Class and Instructor: 1 to many.
modified on Thursday, November 26, 2009 11:34 AM
|
|
|
|
|
Looking good, however.
It looks like your CourseStartDate and CourseExpiryDate should be on your course table. The classes probably have thier own dates within the course date range, unless they should be the ClassStartDate and ClassExpiryDate.
Personaly I would prefer to call it the EndDate rather than ExpiryDate. 'ExpiryDate' makes it sound like the class will begin to smell if you leave it in the cupboard to long. 
|
|
|
|
|
netJP12L wrote: Isn't whenever the Foregin Key is involved it becomes 1 to many relationship
You are correct! A many to many relationship requires a link table. Your structure loks good and I have yet to run across a smelly table no matter what the fields are named.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have the following code
SET @sql = 'SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + AND tbl_Employee.Surname LIKE %' + @Surname + '%'
If i remove the Like part it works fine but with it added it's casuing some problems. I think I'm concatenating the sql wrong.
If someone can help it would be much appreciated.
ASP all the way
|
|
|
|
|
Do this and check your SQL:
print @sql
|
|
|
|
|
The single quote before the AND keyword is missing, it should be:
SET @sql = 'SELECT * FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + ' AND tbl_Employee.Surname LIKE %' + @Surname + '%'
|
|
|
|
|
I presume there is a good reason why you are using dynamic SQL, the select statement does not require it!
This will work without dynamic SQL
Set @Surname = @Surname + '%'
SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = @Department_ID
AND tbl_Employee.Surname LIKE @Surname
As Shameel suggested print the @SQL, copy the result back and try to run it, then fix it so it runs and make the changes to your dynamic sql construct.
|
|
|
|
|
Thanks guys. The reason for dynamic sql is because this is part of a much larger section of code for custom paging. Again thanks
ASP all the way
|
|
|
|
|
Hi all,
I want to start to enter my records from specific number.
let's say I would like to start entry in database table from 1000 instead of 1.
means right now my records are entered in database from 1.
Instead of 1 I would like it to start from 1001. Is it possible? if yes then how?
Believe Yourself™
|
|
|
|
|
Try This :
DBCC CHECKIDENT (tableName, RESEED, 1000)
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
That worked!!
Thanks Abhijit 
Believe Yourself™
|
|
|
|
|
Great ! 
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
Make your question as clear as possible, so others can understand it properly. What you are referring to is probably start an identity column in a table.
When creating the column, you can specify the seed and the increment.
CREATE TABLE Tab1
(
Col1 int IDENTITY (1000, 1)
)
If you want to change the seed of an existing column that may already have data, use this:
DBCC CHECKIDENT ("Tab1", RESEED, 1000);
|
|
|
|
|
Both worked. Thanks. 
Actually I am going to start it from 300000.
Does this huge starting number could create any issue when the records increases??
If there would be any limitation for increasing the amount of the records in future then also please mention.
Believe Yourself™
|
|
|
|
|
There is no known problem with using IDENTITY columns. It works well. If you want to really have 'big' numbers, use bigint datatype instead of int. The syntax remains the same.
|
|
|
|
|
Hey All,
I find myself here asking a question around SSIS which is somewhat out my comfort zone of C# but I have been tasked with some work, and I have came across a problem which I can't seem to over come.
I have an SSIS Package which I am calling from the command line so its in the file system. Its encrypted with password etc. When I am logged in under my own domain user name the script executes perfectly. However; there is a network service user our support department has set up to run this job. When I run this package under this new user, the pacakge fails.
My package has several tasks, however; the task which appears not to run in the Script task. Again, it runs under my own logon, just no with the service user logon.
As a test, I added the service user to the administrator group on the computer the package is running, and the script task worked perfectly. (I think this might be important).
I'm guessing I have a permissions issue with my new user. My thoughts are that it cannot execute vb scripts which I believe are internal to SSIS?
If anyone has any thoughts, could you please help.
Thanks in advance
TF
|
|
|
|
|
You need a Configuration File to store the credentials to the Server. To make sure if what you are saying 9is what am assuming. can you please send us the error ?
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
