|
I am using MYSQL for generating some reports using LogiXML.The problem is that date format in mysql is yyyy-mm-dd and i need this to be converted to UK Format(dd/mm/yyyy).In report i am inputting date in dd/mm/yyyy and should return value matching corresponding dates.If i'm directly querying a value with date like
select DATE_FORMAT(dateIn, '%d/%m/%Y') as 'Date Arrived' from Employee where DATE_FORMAT(dateIn, '%m/%d/%Y') BETWEEN
DATE_FORMAT('2008-04-23', '%m/%d/%Y') AND DATE_FORMAT('2008-05-13', '%m/%d/%Y') is returning records which are not between these dates.
This is very urgent
Thanks
Anup
|
|
|
|
|
Table A :
id name sid jid
Table B:
bid img sid jid
select A.id,A.name,A.sid,A.jid,B.bid,B.img
from A left outer join B
ON A.sid=B.sid AND A.jid=B.jid
I wrote sql like this.
is right?
"ON A.sid=B.sid AND A.jid=B.jid" is key.
|
|
|
|
|
Yes. This usage is porper, to check for multiple columns.
Regards
KP
|
|
|
|
|
|
Hi Friends,
I am facing a very bad problem.I have added an SQL Database using VS 2005>>Add New Item>>Sql Database.The problem I am facing is not able to execute an INSERT or UPDATE.But the thing is that just after inserting and viewing the contents of the same table in the same event,the inserted record is displaying.But if I close the application and look into table manually I couldn't see the newly entered record.
ExecuteNonQuery() is working and no exception is thrown.Please Help.Seems like some weird is happening .I dont have an SQL Server installed.I am using VS 2005 only to develop a database application.
Thanks in advance.
|
|
|
|
|
I'd say your ExecuteNonQuery is NOT working.
At the end if your insert procedure (I assume you are using a stored proc and have an identity field on the table) add Select @@Identity as ID and user executeScalar to get the ID of the inserted record into a variable and inspect that variable in your UI.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Holmes,
Thanks for the reply.
I think the ExecuteNonQuery() is working because I have displayed the return value of cmd.ExecuteNonQuery().Also I have displayed the identity value as you mentioned.It is showing.Only thing is after closing the app new record is not showing up in the db when I see manually.
After adding a database like I mentioned before this is the connection string that is generated in app.config : -
<add name="crStudy.Properties.Settings.Database1ConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
|
|
|
|
|
Guys,
I have a problem with a stored proc in SQL Server 2000. I'm logged in as 'sa' so have complete access to the DB. I have a table defined as follows:
CREATE TABLE Conduit (
Index_i int IDENTITY (1,1) NOT NULL Primary Key,
Name_vc nvarchar(125) UNIQUE NOT NULL,
StartDate_dt datetime
)
GO
I then have 2 stored procs that access it:
CREATE PROCEDURE ReadConduitNames
AS BEGIN
SET NOCOUNT ON
SELECT Name_vc FROM Conduit
END
GO
and
CREATE PROCEDURE ReadConduit
{
@Name_vc nvarchar(125)
}
AS BEGIN
SET NOCOUNT ON
SELECT * FROM Conduit WHERE Name_vc = @Name_vc
END
GO
The first stored proc runs fine, but the second one gives the following error:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
I've tried various forums and msdn, but can't find an answer as to why the second stored proc won't load.
Any thoughts?
Thanks in advance,
John.
|
|
|
|
|
replace
jgrogan wrote: CREATE PROCEDURE ReadConduit
{
@Name_vc nvarchar(125)
}
with
CREATE PROCEDURE ReadConduit<br />
(<br />
@Name_vc nvarchar(125)<br />
)
for more info check this google result[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Many thanks - that worked a treat!
|
|
|
|
|
No problem 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Rename you to Eagle Eyes - well done
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah - I was staring at it for 2 minutes before I realised where the difference was 
|
|
|
|
|
Hi again and sorry for the delay. Thank you very much for your answer and here is the code I am using
RSClientPrint.MarginLeft = 0;
RSClientPrint.MarginTop = 0;
RSClientPrint.MarginRight = 0;
RSClientPrint.MarginBottom = 0;
RSClientPrint.Culture = 2058;
RSClientPrint.UICulture = 2058;
RSClientPrint.PageWidth = 85.2;
RSClientPrint.PageHeight = 279.4;
RSClientPrint.Print('http://ServerName\SQLinstance/ReportServer', '/FolderName/Impresion_Factura/Factura&CODIGO_SERIE='+serie+'&ID_SUCURSAL='+sucursal+'&CAJERO='+cajero+'&NIT='+nit+'&SUBTOTAL='+subtotal+'&DESCUENTOS='+descuentos+'&CODIGO_CAJERO='+codigo_cajero, 'Factura');
I really appreciate your help. You guys always get me out of trouble!!!
|
|
|
|
|
And you question is?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi, sorry about not posting the question again. My problem is that when I preview the report, there is no problem with the first page, but if it is a long report, and I try to view the second page, it shows an error.
In the report server, all pages can be viewed ok. So this happens only when previewing and printing the report.
Thnk you very much for your help.
|
|
|
|
|
Try to fix it by adding code in report event (something like Initialize) which calls report data from database.
What error message do you get?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
hi
how to insert multiple records in the table using procedure but records will be dynamic
means I know Howmany records will be inserted but not fix depend on input provided by user ?
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
on your stored procedure use input parameters, something like this:
CREATE PROCEDURE procedureName @maxInt int<br />
AS<br />
--insert values via looping based on maxInt parameter
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I can provide maxInt(mean howmany record) but how can I insert multiple values which is in table in .net server side code.
means how can I pick up all those records in procedures.
what varaiable i can use like-array
so that i can insert all records using loop.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Try using XML to pass multiple records to your stored proc
create proc xyx (@recs xml)
then unpack the xml to insert the records.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
here it is
CREATE PROCEDURE ProcedureName @maxInt int AS<br />
<br />
declare @i as int<br />
set @i=0<br />
set @maxInt=@maxint <br />
<br />
while @i < @maxInt <br />
begin<br />
set @i = @i + 1<br />
insert into myTable values (@i) --Do your inser which according to your needs<br />
end
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
modified on Friday, May 16, 2008 3:42 AM
|
|
|
|
|
now how to get multiple records for procedure from asp.net.
and how to pick up by procedure.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Now I need you to tell me how to write method for executing a procedure and returnd a dataset
The stored procedure (statement outlined at the bottom of the word document) will give you a list of orders (first table) and order items (second table) that contain mail lists.
The first table will contain the order info itself. This is where you will get the additional fields for the “Master Mail list file” such as order number, name of person who placed the order etc. The second table will contain the specific item info (such as item name/description).
You will use the Order number (the actual column name is SOPNumbe) to reference the path where the order files are stored.
An order folder will have one or more package folders.
Add a method to DataObjects.FeedsDO.vb to execute the stored procedure and return a DataSet.
So once you have your dataset.
the below one is the SQl statement so I also need Code for writing a procedure which gives me two records
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ClientCode varchar(5), @ClientID int
SELECT @ClientCode = 'cmc'
SELECT TOP 1 @ClientID = ClientID FROM ODClient WHERE Code = @ClientCode AND ParentID IS NULL
DECLARE @Orders TABLE (OrderID bigint)
--Set the id of the mdb output data feed for reference in next statement
DECLARE @DataFeedID int
SELECT @DataFeedID = 5
INSERT INTO @orders
SELECT o.OrderID FROM ODOrder o
OUTER APPLY (SELECT TOP 1 oh1.CreateDate, oh1.OrderID, oh1.StatusID FROM ODOrderHistory oh1 WHERE oh1.OrderID = o.OrderID AND oh1.StatusID = 22) oh
WHERE o.ClientID IN (SELECT ChildID FROM dbo.funGetClientChildren(@ClientID))
AND o.OrderID IN
(SELECT OrderID FROM ODOrderShipment os
INNER JOIN ODOrderItem oi ON oi.OrderShipmentID = os.OrderShipmentID
WHERE oi.HasMailingList = 1)
AND
(oh.StatusID = 22 AND oh.CreateDate >= (SELECT MAX(CreateDate) FROM ODDataFeedLog WHERE DataFeedID = @DataFeedID AND DataFeedLogEventTypeID=3) OR NOT EXISTS (SELECT 1 FROM ODDataFeedLog WHERE DataFeedID = @DataFeedID))
SELECT o.OrderID, o.SOPNumbe, su.LoginID, c.FirstName, c.LastName,
StatusDate = (SELECT TOP 1 CreateDate FROM ODOrderHistory oh WHERE oh.OrderID = o.OrderID ORDER BY CreateDate DESC)
FROM ODOrder o
INNER JOIN ODSysUser su ON su.SysUserID = o.SysUserID
INNER JOIN ODContact c ON c.ContactID = su.ContactID
WHERE o.OrderID IN (SELECT OrderID FROM @Orders)
SELECT * FROM ODOrderShipment os
INNER JOIN ODOrderItem oi ON oi.OrderShipmentID = os.OrderShipmentID
INNER JOIN ODPackage p ON p.PackageID = oi.PackageID
WHERE oi.HasMailingList = 1
AND os.OrderID IN (SELECT OrderID FROM @Orders)
|
|
|
|
|
Sorry, but this is obviously a homework question. Have a try and post your code if it doesn't work and someone will probably help you. Nobody will do your homework for you.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
