Re: Query to find last 10 records - Database Discussion Boards

Cory Kimble9-Feb-08 5:28

9-Feb-08 5:28

I figured this out! I has to do with that the web app is logging in using sql authentication and I wasn't.

I have an issue and I don't really know how to put it in words in order to put it in a search engine and hope to get an answer, so I will tell you my problem and I hope you can help me.

My software accesses a SQl Server database using the connection string

("Data Source=.\SQLEXPRESS;AttachDbFilename=" + txtbxdatabase.Text + ";Integrated Security=True;Connect Timeout=30;User Instance=True")

(I go the sintax from the connection string created from wizard)

As you can see it gets the name of the database from a text box. This works perfectly until we try to use our software with a third party vender's software.

Third Party software uses ASP to connect to the Sqlserver on station (PC) and writes transaction data to it. When my program tries to open the same Sqlserver on the station (PC) it gets an error (I did not write it down) that, in so many words, would not let my program access the database because it is already in use. I think my connection string is the problem, but I have not found anything helpful online.

Any ideas?

modified on Tuesday, February 12, 2008 8:53 AM

SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Sujit Gupta8-Feb-08 23:28

Sujit Gupta

8-Feb-08 23:28

n my C# program, I get the following error when trying to write to a
table:

"Dynamic SQL generation for the UpdateCommand is not supported against
a SelectCommand that does not return any key column information."

i use this code :
da = new OracleDataAdapter(command, con);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
comB = new OracleCommandBuilder(da);

da.Fill(ds, "product");
ds.Tables["product"].PrimaryKey = new System.Data.DataColumn[] { ds.Tables["product"].Columns["item_code"] };

comB.GetUpdateCommand();
da.Update(ds, "product");

Sujit

Re: SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

^That's Aragon9-Feb-08 0:02

^That's Aragon

9-Feb-08 0:02

Hi Sujit!

Do you have a primary column defined in the Table? If not then defined it.
You must have a primary key defined in your database tables to avoid this error.

I hope this will help you.

Regards.

"Save water,It's precious" Smile | :)

"Don't forget to vote" Wink | ;)

Re: SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Sujit Gupta10-Feb-08 6:40

Sujit Gupta

10-Feb-08 6:40

Hi friend
Basically i have have a product table which have one primary key item_id. i use this statement for fetching data in dataset ( select itemcode,.......... from table) then i make a primary key in dataset on itamcode.after updating all data in dataset. i use da.update(ds) then it generate error. Where i am wrong please suggest me thanks

Sujit

Query to find last 10 records

sirisha guttikonda8-Feb-08 20:58

sirisha guttikonda

8-Feb-08 20:58

hi all

can u plz help me...

here iam writing the query to get top 10 records from tbl_person table.
i.e select Top 10 * from tbl_person

here it is diplaying top 10 ..that is ok

but i want to display the last 10 records(last entered)...
is there any possibilities....

thankyou.

siri

Re: Query to find last 10 records

pmarfleet8-Feb-08 21:23

pmarfleet

8-Feb-08 21:23

Apply a sort so that the last entered records are output first.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Query to find last 10 records

sirisha guttikonda8-Feb-08 21:27

sirisha guttikonda

8-Feb-08 21:27

hi,

can u plz help me to that...
i have very poor knowledge in dat.

thank u...

siri

Re: Query to find last 10 records

pmarfleet8-Feb-08 21:56

pmarfleet

8-Feb-08 21:56

sirisha guttikonda wrote:
can u plz help me to that...
i have very poor knowledge in dat.

I'm not going to write your code for you. However I suggest you add an ORDER BY clause to your query to sort the records in the right order. Then select the records you want.

If you have poor knowledge of SQL, I suggest you purchase a SQL book.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Query to find last 10 records

^That's Aragon8-Feb-08 21:56

^That's Aragon

8-Feb-08 21:56

Hi Sirisha!

sirisha guttikonda wrote:
but i want to display the last 10 records(last entered)...

Is there any Datetime column in Table,which store the record with it's created time? If yes then you can easily retrieve the desired output.

select top(10) * from tbl_person order by Date_Created desc

Even If you have autogenerate column in table then also you can find record from above query,just replace Date_Created with the autogenerate column.

I hope this will help you.
Regards.

"Save water,It's precious" Smile | :)

"Don't forget to vote" Wink | ;)

Re: Query to find last 10 records

Sabarinathan T8-Feb-08 22:20

Sabarinathan T

8-Feb-08 22:20

Hi siri,

U can write the query like

Select top 10 * from tbl_person order by (Keycolumn) desc.

Does this help?

Sabarinathan T, Chennai, India.

The interests are to know something about everything and everything about something...

Re: Query to find last 10 records

sirisha guttikonda8-Feb-08 23:22

sirisha guttikonda

8-Feb-08 23:22

Thanks to all

siri

Syntax help - How to subtract two years from current date

Smokeywade8-Feb-08 7:40

Smokeywade

8-Feb-08 7:40

I need help with the syntax below to subtract two years from the current date

select oe_hdr.customer_id
from oe_hdr
where year (oe_hdr.order_date) >= year (getdate()-2 )

Thank You

Re: Syntax help - How to subtract two years from current date

AlbertEiki8-Feb-08 7:46

AlbertEiki

8-Feb-08 7:46

YEAR(DATEADD(yy, -2, GETDATE()))

Re: Syntax help - How to subtract two years from current date

Pallab_GT9-Feb-08 7:53

Pallab_GT

9-Feb-08 7:53

Use the datediff function.

Aggregate Function [modified]

Lash208-Feb-08 3:37

Lash20

8-Feb-08 3:37

I need to write a script that gets the first time an employee attended a live class, the script I wrote below works but I need to also display the name of the class, but the result set goes out of whack when I include the className field to the select statement. Can someone please tell me how I can display the class name in my result set. The className field is in tblClass. Thanks

SELECT tblAttendance.EmployeeID, vwEmployeeInformation.FullName, MIN(tblAttendance.AttendanceDate) AS FirstAttendanceDate
FROM tblAttendance
INNER JOIN tblClassInstance ON tblAttendance.ClassInstanceID = tblClassInstance.ClassInstanceID
INNER JOIN tblClass ON tblClassInstance.ClassID = tblClass.ClassID
INNER JOIN vwEmployeeInformation ON tblAttendance.EmployeeID = vwEmployeeInformation.EmployeeID
WHERE (tblClass.ClassFormat = 'On Demand') AND (tblAttendance.AttendanceDate BETWEEN '08/10/07' AND GetDate())
GROUP BY tblAttendance.EmployeeID, vwEmployeeInformation.FullName

modified on Friday, February 8, 2008 10:01 AM

Re: Aggregate Function

Michael Potter8-Feb-08 4:45

Michael Potter

8-Feb-08 4:45

It should go out-of-whack when you add class name since you would then be asking the server to give you the first attendance date of EACH class each student took. I have to assume that a student can take multiple classes in a day and that AttendanceDate does not respect the time of the class (only the date).

Here is a messy start that should work on SQL Server. I used your original query to aquire the necessary root data for finding the first class name. TOP 1 is used in case the student attends more than 1 class that day and time is not embedded in AttendanceDate.

SELECT 
    EmployeeID,
    FullName,
    FirstAttendanceDate,
    (SELECT TOP 1
         tblClass.className
     FROM
         tblAttendance
     INNER JOIN 
         tblClassInstance 
         ON (tblAttendance.ClassInstanceID = tblClassInstance.ClassInstanceID)
     INNER JOIN 
         tblClass 
         ON (tblClassInstance.ClassID = tblClass.ClassID)
     WHERE 
         tblAttendance.EmployeeID = root.EmployeeId,
         tblAttendance.AttendanceDate = root.FirstAttendanceDate) AS ClassName
FROM		
    (SELECT 
          tblAttendance.EmployeeID, 
          vwEmployeeInformation.FullName, 
          MIN(tblAttendance.AttendanceDate) AS FirstAttendanceDate
     FROM 
          tblAttendance
     INNER JOIN 
          tblClassInstance 
          ON (tblAttendance.ClassInstanceID = tblClassInstance.ClassInstanceID)
     INNER JOIN 
          tblClass 
          ON (tblClassInstance.ClassID = tblClass.ClassID)
     INNER JOIN 
          vwEmployeeInformation 
          ON (tblAttendance.EmployeeID = vwEmployeeInformation.EmployeeID)
     WHERE 
          (tblClass.ClassFormat = 'On Demand') AND 
          (tblAttendance.AttendanceDate BETWEEN '08/10/07' AND GetDate())
     GROUP BY 
          tblAttendance.EmployeeID, 
          vwEmployeeInformation.FullName) AS Root

Re: Aggregate Function

Lash208-Feb-08 5:20

Lash20

8-Feb-08 5:20

Thanks a lot Michael, just what I needed... I've learnt something new..

exporting sqlserver table data into .csv files

rajeshkumar g8-Feb-08 1:05

rajeshkumar g

8-Feb-08 1:05

how we can export table into text .csv files using sqlserver enterprise manager

Re: exporting sqlserver table data into .csv files

Giorgi Dalakishvili8-Feb-08 1:22

Giorgi Dalakishvili

8-Feb-08 1:22

Google not working for you? There are many results [^] available

#region signature
my articles
#endregion

Re: exporting sqlserver table data into .csv files

rajeshkumar g10-Feb-08 22:26

rajeshkumar g

10-Feb-08 22:26

if it i can find in google . iam not asking you .if you know just give me idea otherwise silent. in google i get solution use ems. but with out using ems i am asking

Re: exporting sqlserver table data into .csv files

Vasudevan Deepak Kumar12-Feb-08 2:48

Vasudevan Deepak Kumar

12-Feb-08 2:48

Too rude. I would first suggest you read the forum posting guidelines at http://www.codeproject.com/kb/scrapbook/forumguidelines.aspx[^] before you venture out with any technical explorations.

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson

SQL2005 + Service Broker + CLR Stored Procedure + XML + Temp Tables

Malcolm Smart7-Feb-08 23:57

Malcolm Smart

7-Feb-08 23:57

This is killing me...and not slowly.

I have a stored procedure (a) which calls another stored procedure (b).

Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...

select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements

.

If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.

But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except out XML files were empty.

Further investigation showed that if we call

select fld1, fld2, fld3, fld4, fld5from #tmptable

- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiles, but the XmlReader refuses to read.

The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.

D'Oh! | :doh:

Any ideas?

so you answer don't be scared of failure
The only failure is never to try
Things You've Never Done - Passenger -2008

Crystal Report

techpub7-Feb-08 23:22

techpub

7-Feb-08 23:22

How to Populate Crystal Report dynamically with database and tables?

Re: Crystal Report

John_Adams8-Feb-08 1:09

John_Adams

8-Feb-08 1:09

Hi,

You can populate Crystal Report dynamically using ADO.Net. This is how you do it using OLEDB, and a table view in MSSQL. The Following code assumes that you have setup your report viewer with the wizard.

-------------------------------------------------------------------------
BEGIN CODE

dataSet21.Clear();
oleDbDataAdapter1.Fill(dataSet21,"your_table_view");

CrystalReport2 custReport = new CrystalReport2();
custReport.SetDataSource(dataSet21);

TableLogOnInfo logOnInfo = new TableLogOnInfo();
logOnInfo = custReport.Database.Tables["your_table_view"].LogOnInfo;
ConnectionInfo connectionInfo = new ConnectionInfo ();
connectionInfo = logOnInfo.ConnectionInfo;

// Set the Connection parameters.
connectionInfo.DatabaseName = yourcatalog;
connectionInfo.ServerName = workstationID;
MessageBox.Show(connectionInfo.ServerName);

//connectionInfo.Password = "yourpassword";
//connectionInfo.UserID = "youruserid";

custReport.Database.Tables ["Vw_Address_Label"].ApplyLogOnInfo(logOnInfo);
crystalReportViewer1.ReportSource = custReport;
crystalReportViewer1.Refresh();
crystalReportViewer1.RefreshReport();

END CODE
-------------------------------------------------------------------------

I hope this helps Smile | :)

Regards,
John Adams
ComponentOne LLC

Re: Crystal Report

techpub8-Feb-08 3:26

techpub

8-Feb-08 3:26

In my case I am not connecting the database/table with the Crystal Report through the wizard, I want to coonnect the database and table with the crystal report at page_load.
in that case I need to get that code that connect with the database and table with the crystal report.
thanks

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