|
Have you looked around in the Oracle forums?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
hi friends
this is my stored procedure:
CREATE procedure dbo.sp_ins_logo
@companyname varchar(120),
@country varchar(80),
@url varchar(150),
@category varchar(80),
@description varchar(250),
@picturepath varchar(80),
@picturetype char(5),
@status int,
@ins_status varchar(10) output,
@lcode varchar(10) output
as
set nocount on
begin transaction
declare @logo_rowid decimal
declare @logo_code varchar(10)
declare @logo_rowcount decimal
declare @max_logoid decimal
declare @logo_codepfix char(3)
set @logo_codepfix='LGO'
select @logo_rowcount= (select count(*) from dbo.tbladdlogos1)
if @logo_rowcount = 0
begin
select @logo_rowid = 1
select @logo_code = @logo_codepfix + convert(varchar(5),@logo_rowid)
end
else
begin
select @max_logoid = (select max(row_id) from dbo.tbladdlogos1)
set @logo_rowid = @max_logoid + 1
set @logo_code = @logo_codepfix + convert(varchar(5),@logo_rowid)
end
declare @file_name varchar(80)
set @file_name = @picturepath + @logo_code + '.' + @picturetype
if not exists (select companyname from dbo.tbladdlogos1 where url = @url)
begin
if not exists (select companyname from dbo.tbladdlogos1 where companyname = @companyname)
begin
insert into dbo.tbladdlogos1 values(@logo_rowid,@logo_code,@companyname,@country,@url,@category,@description,@file_name,@status)
declare @ER as int
select @ER = @@error
if @ER <> 0
begin
set @ins_status='Failure'
set @lcode=@logo_code
end
else
begin
set @ins_status='Success'
set @lcode=@logo_code
end
end
end
commit
GO
this is my table
CREATE TABLE [tbladdlogos1] (
[row_id] [int] NOT NULL ,
[logo_code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyname ] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[url] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[picturepath] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [int] NULL
) ON [PRIMARY]
GO
i inserted value
below error is occured
Invalid object name 'dbo.tbladdlogos1'. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
can u help me
regards
saravanan
|
|
|
|
|
First, check that you're executing the above in the right database, or that you created the table in the right database. We've all done it.
Second, you've used BEGIN TRANSACTION in the above code, but you didn't tell SQL Server what you wanted to happen at the end. You should use COMMIT TRANSACTION to save the changes you've made, or ROLLBACK TRANSACTION to discard them. Generally you should check @@ERROR after every data modification statement and ROLLBACK TRANSACTION (and return) if it's not 0. Only critical errors abort the stored procedure and automatically rollback the transaction. Errors like attempting to insert a duplicate key are not considered critical; the statement will be aborted and rolled back, but execution will continue at the next statement.
SQL Server does not permit transactions opened in a stored procedure to be held open after that procedure executes. That's why you get the error. If you want a transaction to span multiple procedure calls, you'll have to use a client-side transaction (e.g. a SqlTransaction object).
|
|
|
|
|
...and in addition to that, make sure you have granted access to the user that you are when executing the call.
Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia
|
|
|
|
|
Hi,
How to refer one .RDL file created in one project(Proj1) in another project(Proj2) in the same solution in SQLServer Reporting Service(SSRS).
I am not able to refer .rdl file created in one project in another project under same solution.
Karuna
|
|
|
|
|
Have you tried including a reference to it in the solution?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
First of all Thanks for your reply.
Here, thats what the problem in this SSRS Solution. we cannot include any refernce in the project as we do in normal visual studiao IDE.only we can add Data sources and .RDL files physically.But we cannot refer any .rdl file logically as we do with dlls.
Thanks.
Karuna
|
|
|
|
|
Hi,
How to create a new folder in SQLServer Reporting Services(SSRS) Project?
If i create an SSRS project, the solution comes with two folders 'Shared Data Sources' and 'Reports' by default. But i am not able to add a new folder under that solution.
Karuna
|
|
|
|
|
|
Hi,
I am trying to clarify a doubt. Can we use Views for a insert Query?
Thanks in advance,
Sunil Nair
|
|
|
|
|
Yes, inserting into a base table from a view is possible.
however all not null columns to be included in the view
and any columns with check constraint
Regards
KP
|
|
|
|
|
I am trying to get info into a variable in a sql server 2000 stored procedure How can this be done. I thought i had it but it doesnt seem to work. This is what i have.
Declare @var1 int
Select @var1 Select max(List_ID) from table1
@var1 stays NULL
What am i doing wrong?
|
|
|
|
|
Here you go:
SELECT @Var2 = MAX(Lis_ID) from table1
|
|
|
|
|
Thanks a lot it worked I appreciate the help
|
|
|
|
|
Not a problem - I'm glad to help.
|
|
|
|
|
Set @var1 = (SELECT max(List_ID) from table1)
Select @var1
This is one way too
|
|
|
|
|
Thanks a lot it worked I appreciate the help
|
|
|
|
|
hi all,
I am creating a web application using vb.net where i have a table with some details.
I need to move the last record of the table to the first place.Like for eg,
i have records like this,
Empno Empname salary
1 aaa 5000
2 bbb 6000
3 ccc 7000
4 ddd 8000
after moving the 4th record to the first place,the order should be
Empno Empname salary
4 ddd 8000
1 aaa 5000
2 bbb 6000
3 ccc 7000
i tried adding a column as order and i tried to update the order no.but it swapped the places of the records.
is there any query in SQL to move the records within the same table.
Thanks in advance.
Regards
Anuradha
|
|
|
|
|
SQL is designed to get you information into/out of the database. This problem has to do with the display of the information, which you really should decouple from the idea of storing the information. You really need to do this via your view code otherwise you will end up storing information in your database purely for the purposes of viewing the other fields.
|
|
|
|
|
Hi
I have the following table with the values
Name Number username displayname kind path
gane 0 ganesh gane 0 \Root\
bane2 0 ganesh bane2 1 \Root\gane
aane2 0 ganesh aane2 0 \Root\
hane1 0 ganesh hane1 1 \Root\gane
I need a query which will give the output
Name Number username displayname kind path
aane2 0 ganesh aane2 0 \Root\
bane2 0 ganesh bane2 1 \Root\gane
hane1 0 ganesh hane1 1 \Root\gane
gane 0 ganesh gane 0 \Root\
Thanks in advance
|
|
|
|
|
I assume you are wanting to sort on the name column here. If so, add ORDER BY Name to the end of your select.
|
|
|
|
|
In looking at your data sorting wish, the only organization I see that makes any sense is to sort like "ORDER BY LEN([Name]) DESC, [Name] ASC".
Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia
|
|
|
|
|
Hi guys.I want to establish a connection to my MS Access database using my HTML or ASP code.The problem is that I have to load database values onto the dropdownlist when I run the page but I say its the connection because the page run with no values and no dropdown.I havent been able to run any values in other examples that Ive tries.Is there configurations that I need to change in ASP or HTML to run database connections in ASP and HTML.
Here is the code I got a problem with
<%@LANGUAGE="VBSCRIPT" %>
<%
Option Explicit
Dim objConn
Dim objRS
Dim objRS2
Dim strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objConn.ConnectionString = Server.MapPath("dropdowns.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open
'-- Sub Procedure that builds the dropdown list.
'-- Parameters c, r and table are passed when the sub is built.
Sub showDropDownList(c, r, table)
'-- SQL Statement
strSQL = "SELECT * FROM " & tblDropDownTest & " ORDER BY Title ASC"
'-- Execute our SQL statement and store the recordset
Set r = c.Execute(strSQL)
'-- START MAIN CODE BLOCK
'-- If we have records to return
if not r.eof = false then
'-- Open a form/select tag
'-- This particular example has no action attribute, which would normally...
'-- ...be required in order to post the data to another page for processing.
Response.Write ""
Response.Write "" & vbCrLf
Response.Write "Choose...."
'-- loop and build each database entry as a selectable option
While r.EOF = false
Response.Write ""
_ & r.Fields("Title").Value & "" & vbCrLf
'-- Move recordset to the next value
r.movenext
Wend
end if
'--END OF MAIN CODE BLOCK
'-- close select/form tags
Response. Write "" & vbCrLf
End Sub
%>
<title>
Dynamic dropdown lists using Active Server Pages and Microsoft Access
Below is an example of a drop down list, dynamically populated from
a database table.
<%
' -- Call the sub procedure to build the drop down list, with the connection
and recordset objects and the source table (In quotes).
call showDropDownList(objConn, objRS, "tblDropDownTest")
%>
kagiso
|
|
|
|
|
Shouldn't it say "if r.eof = false then"?
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Thatz what I said on there.If rs.EOF then
Unless there is someother rs.EOF that I forgot to change.But ive done that already and nothing.
kagiso
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
