|
This isn't very pretty...and there are probably better ways to do what you want. But, if there is only ever two drivers, you can still get it all into another table with just one SQL:
insert into voyage_end2 (voyage_id,platenumber, startdate,Driver1,Driver2)
SELECT (SELECT TOP 1 voyage_id
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate) as voyage_id,
PlateNumber,StartDate,
(SELECT TOP 1 Driver1
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate order by Driver1) as Driver1,
(SELECT TOP 1 Driver1
FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate and
(SELECT TOP 1 Driver1 FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate order by Driver1) != Driver1 order by Driver1 desc) as Driver2
FROM Voyage_End b
GROUP BY platenumber, startdate
However, your best long term solution is to do as Ennis Ray Lynch, Jr. has suggested and modify your table structure.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,
I want to do a transactional replication for a SQL database using c# code; can anyone please help me to do so ?
Of course any code on any language would do the the job for me, as I can change it to c#.
I think the best way is making use of SQLDMO, although I've got no idea on how to do it.
Thanks
farshad
|
|
|
|
|
I have web application use Database is SQLServer 2000
every day have 10000 people visit my Web site
and Size File Log increase very higth but my server end space
can i use add new database server
Some body help me
|
|
|
|
|
Can you calrify, You are talking about database log file or any extra log file like texxt file on the server to record log.
Kumar Prabhakar
|
|
|
|
|
yes when many people visit and many query to database get Data
so file Log of Database very big
|
|
|
|
|
This growing of the log-file will stop if you backup it regularly.
Or you can change the recovery model to Simple.
Wout Louwers
|
|
|
|
|
You're not backing up your transaction log. SQL Server maintains a record of all transactions within the log to enable you to restore to any point in time.
See this comment[^] of mine on someone else's similar problem.
See also here[^] for more about what the transaction log contains.
Stability. What an interesting concept. -- Chris Maunder
-- modified at 9:19 Thursday 13th July, 2006
|
|
|
|
|
FYI:
I'm programming in Visual Basic 6.0
I have an ADODC control that is linked to a .mdb file I created in Microsoft Access
The provider I am using to connect to the database is Microsoft Jet OLE DB 4.0
My Problem:
String comparisons in my SQL queries are not behaving the way I'd like:
As a silly example,
SELECT * FROM tbl WHERE 'a' = 'A' returns everything in tbl
SELECT * FROM tbl WHERE 'a' > 'A' returns nothing in tbl
SELECT * FROM tbl WHERE 'a' < 'A' returns nothing in tbl
How do I make the above comparisons so they are case sensitive?
I need my comparisons to abide by the following character ranking scheme:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffpro01/html/GettingStartedwithADO.asp
Under this scheme, the following holds:
('a' = 'A') = FALSE
('a' > 'A') = TRUE
('.0000m3d10_1' > '.0000m3d10Z') = TRUE
Your help is greatly appreciated!
-Lee``
|
|
|
|
|
I think you'll probably find that an Access DB is not case sensitive (for indexes and data) and i don't believe you can get it to be either. Why not try SQL Server 2005 Express edition instead as you will be able to set the database up with a binary or case sensitive sort order (i.e. There will be case sensitivity for strings)
You can do it in code though in access check this out here for more information.
-= Technomancer =-
-- modified at 0:09 Thursday 13th July, 2006
|
|
|
|
|
hi
Access databases in spite of tables have queries stored in them. i wanted to know do other Databases (oracle,sqlserver,..) have Queries stored in them. if so, how should i get their names or their SQL statement?
is there something in databases's SCHEMAINFORMATION for this?
|
|
|
|
|
I'm familiar with SQL Server 2000. They don't have "queries" but they have stored procedures in which you can create queries, and return results.
For example, my datagrid calls a stored procedure that looks up the information about a part, based on what the user entered as a part number.
If you have Enterprise Manager installed onto your computer, you can connect to the server, then the database. Inside the database you can look at the tables, stored procedures, users, etc. Without EM I am not sure how else to look at them unless you are on the server directly.
Hope that helps.
|
|
|
|
|
SQL Server has "Views" that are comparable to the MS Access query.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
thanks leckey for answering
yeah i think thats something like stored procedures.
i want to do that programmatictly. i want to get their names and then use those names to retrieve the SQL statement and then execute that statement.
|
|
|
|
|
SQL Server:
select * from INFORMATION_SCHEMA.VIEWS
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
|
If information_schema is supported:
Stored Procedure Names
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
Stored Procedure Definition
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
View Names
SELECT VIEW_NAME FROM INFORMATION_SCHEMA.VIEWS
View Definition
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
With SQL Server (the only thing I have to test on right now) the definitions are not just the query, but the entire definition, including the 'CREATE PROC' or 'CREATE VIEW' part of the definition. Also stored procedures often have a lot of programming in them and can be quite long.
-- modified at 12:26 Thursday 13th July, 2006
|
|
|
|
|
hi
i wamted to know how to update my data source through a DataGrid.
i have a DataTable and i bind it to a DataGrid.
when user changes the data in the datagrid the data in datatable also change.now I want to apply this change to my database file. I used DataAdapter's Update method but it gives exception. what should i do?
|
|
|
|
|
There is an 18 part series on DataGrids at
http://aspnet.4guysfromrolla.com
I'm not sure which part is the one your looking for, but I've found it very helpful.
Also, you may want to post your actual code and highlight what row you are getting the error.
|
|
|
|
|
in:
mydataadapter.Update( mytable );
the table is that bound to datagrid and changed. i want my database to change too.
|
|
|
|
|
Is your datagrid fully editable or are you just updating one row at a time with an edit button?
|
|
|
|
|
I want it to be editable. but i put that code in a button click but it didnt work.
|
|
|
|
|
I really recommend going through those articles at 4guysfromrolla. They have one section on how to make a row editable with an edit button and another section on how to make the entire datagrid editable. It goes how to make the template columns and such. The code behind is in vb though (I'm a c# person) but it's pretty straightforward.
I printed all 18 articles off (some have Go to part 2 so note that), sat down and read through the sections I thought would help. I think this would be a great place for you to start if you're not too familiar with datagrids.
|
|
|
|
|
I had asked about output parameters yesterday as I had not worked with them before. I did get a response but I am still having some issues. Here is my current stored proc:
CREATE PROCEDURE [dbo].[UpdatePart_Step_1_Test] @PartNumber varchar (15),<br />
@PartTypeDescription varchar (60),<br />
@ID int OUTPUT<br />
AS<br />
declare @pt_id integer, @ThePartNumber integer<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
SELECT @pt_id = pt.ID FROM PartTypes pt <br />
<br />
INNER JOIN Parts p on pt.ID = p.PartTypeID<br />
<br />
WHERE pt.Description = @PartTypeDescription<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@pt_id<br />
)<br />
<br />
<br />
END<br />
SELECT @ID=@@IDENTITY<br />
return 0<br />
GO
Here is the c# code that calls the stored proc. Note I have some additional parameters declared but I am not yet sending.
private void btnAdd_Click(object sender, System.EventArgs e)<br />
{<br />
strPartNumberInputReference = txtSearchPart.Text;<br />
strDwgNumber = txtDwgNumber.Text;<br />
strDwgRevision = txtDwgRevision.Text;<br />
strDwgLocation = txtDwgLocation.Text;<br />
<br />
ds2 = new DataSet();<br />
SqlParameter param1 = new SqlParameter("@PartNumber", strPartNumberInputReference); <br />
SqlParameter param2 = new SqlParameter("@DwgNumber", strDwgNumber);<br />
SqlParameter param3 = new SqlParameter("@DwgRevision", strDwgRevision);<br />
SqlParameter param4 = new SqlParameter("@DwgLocation", strDwgLocation);<br />
SqlParameter param5 = new SqlParameter("@PartTypeDescription", strPartTypeID);<br />
SqlParameter param6 = new SqlParameter(@ID, SqlDbType.Int);<br />
param6.Direction = ParameterDirection.Output;<br />
<br />
ds2=SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure,"dbo.UpdatePart_Step_1_Test", param1, param5);<br />
dt2 = ds2.Tables[0];<br />
}
The problem is that when I try to add I get the error: Procedure 'UpdatePart_Step_1_Test' expects parameter '@ID', which was not supplied
Okay, did some additional reseach and it appears I need to something like the following: (taken from a previous CodeProject post)
DECLARE @MyTitle varchar(50),<br />
@ytd int<br />
SET @MyTitle = 'Some Title'<br />
EXEC dbo.YtdSales @MyTitle, @ytd OUTPUT<br />
SELECT @ytd
I'm not sure where to insert these bits (set, exec, select)into my existing code.
Can someone help me or tell me what is wrong with the original sp?
Thanks!
|
|
|
|
|
Your call to ExecuteDataset references param5, where the parameter called @ID is actually param6. I think this is the problem!
Since your procedure actually does an INSERT, I'd look at using SqlCommand.ExecuteNonQuery since you're not actually returning a resultset. Filling a DataSet is overkill.
It often helps to prevent the client side from getting confused by putting SET NOCOUNT ON at the top of your procedures. This means that the rowcounts from any operations that don't actually return a result set aren't sent to the client. IIRC, if you don't use this SET statement, you can end up getting empty resultsets, which may appear (it's been a while since I did this) as empty DataTables in your DataSet.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Actually I need to pass all three. Thanks for catching that. Param 1 is the part number, param 5 is the part type, and param 6 the ID for output. So now I have:
ds2=SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure,"dbo.UpdatePart_Step_1_Test3", param1, param5, param6);
I revamped some earlier code. Basically the part type is a drop down box with a description, but now I send the actual partTypeID. So now my stored proc looks like:
CREATE PROCEDURE [dbo].[UpdatePart_Step_1_Test3]<br />
@PartNumber varchar (15),<br />
@PartTypeValue varchar (60),<br />
@ID int OUTPUT<br />
<br />
AS<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@PartTypeValue<br />
)<br />
<br />
END<br />
SELECT @ID = @@IDENTITY<br />
<br />
<br />
return 0<br />
GO
-- modified at 15:43 Wednesday 12th July, 2006
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
