Problem With Vb Procedure - Acting With Database

Jan 3, 2008

Hi,
I have a problem with the execution of a vb-procedure that should do 2 updates of databasetables.
The procedure runs when the eventhandler registrates that I want make an update within the <asp:detailview..>-controll.
But I get the errormessage:  End-Of-Command requested
This is the procedure:
Public Sub myDetails_Update(ByVal sender As Object, ByVal e As System.EventArgs) handle DetailsView1.Databound
    Dim conn As New SQLConnection("Data Source=.SQLEXPRESS;AttachDbFile=C:Pferdeservice KarleApp_dataPferde.mdf;Integrated Security=True Connection Timeout=30;User Instance=True")
    conn.open()
    Dim cmd As New SqlCommand()
    Dim cmd1 As New SqlCommand()
    cmd.Connection = conn
    cmd.CommandText = "UPDATE News_Kultur set Header = @Header, Ueberschrift_D = @Ueberschrift_D, Ueberschrift_E = @Ueberschrift_E, Text_D = @Text_D, News_Kultur.Text_E = @Text_E"
WHERE (((News_Kultur.ID)=@ArtikelID))"
    cmd.CommandType = CommandType.Text
    cmd1.CommandText = "UPDATE News set Datum= @Datum WHERE ID=@NewsID"
    cmd1.CommandType = CommandType.Text

    cmd.Parameters.add("@Headers", SqlDbType.nvarchar, 50)
    cmd.Parameters.add("@Ueberschrift_D", SqlDbType.nvarchar, 50)
    cmd.Parameters.add("@Ueberschrift_E", SqlDbType.nvarchar, 50)
    cmd.Parameters.add("@Text_D", SqlDbType.Text)
    cmd.Parameters.add("@Text_E", SqlDbType.Text)
    cmd.Parameters.add("@ArtikelID", SqlDbType.Int)

    cmd1.Parameters.add("@Datum", SqlDbType.Datetime)
    cmd1.Parameters.add("@NewsID", SqlDbType.Int)

    cmd.Parameters.add("@Headers").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text
    cmd.Parameters.add("@Ueberschrift_D").value = DirectCast(DetailsView1.FindControl("Ueberschrift_D"), TextBox).Text
    cmd.Parameters.add("@Ueberschrift_E").value = DirectCast(DetailsView1.FindControl("Ueberschrift_E"), TextBox).Text
    cmd.Parameters.add("@Text_D").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text
    cmd.Parameters.add("@Text_E").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text
    cmd.Parameters.add("@ArtikelID").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text
    cmd.ExecuteScalar()
    cmd1.Parameters.add("@Datum").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text
    cmd1.Parameters.add("@NewsID").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text
    cmd1.ExecuteScalar()
    conn.close()
End Sub
 
Regards 
 

View 6 Replies


ADVERTISEMENT

Database Acting Strange

Jan 20, 2004

MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.

View 9 Replies View Related

DTS Package Acting Weird

Jul 12, 2007

I have built a DTS package in SQL Server 2000 to Delete all rows from a table and then import from another table. I have scheduled it run twice a day and everytime it runs, it does not delete anything but imports all the records from another table. So when its done, I have twice the number of records in my final table. Then I go back to the server and execute the package manually and it works perfect. It deletes all and then imports new. I have enabled package logging and it shows both the steps executed perfectly (even when it executes on scheduled time).
What can be the problem?
Thanks,
Bullpit

View 1 Replies View Related

Please Help! Stored Proc Acting Odd

Jul 28, 2000

I am so confused. I hope someone can help! I wrote a large stored procedure that is meant to be called from a VB application. The store procedure collects data from several tables and buildsa new table of summary data. It is called with 4 parameters (date ranges and names for the new summary tables to create).

I tested the SP in SQL 7 while developing it, and never had any problems. But when I tried it in 6.5, it complained that I had too many subqueries. So I rewrote the procedure with less nestings, and then when I tried it I didn't get an error but it crashed with a DB Disconnected error.

I rewrote it again and then it finally worked (but only from Enterprise Manager); when I called from a VB application (using ADO), I get an error "protocol error in TDS stream." And looking in the SQL error log I see a whole lot of errors, none of which I understand, but the first mesage is "EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump.

I know that without knowing the contents of the stored procedure, there's no way anybody can tell me exactly what's going wrong, but I'm sure hoping that some people can give me some clues or directions to look. I'm desperate and have been working on this one project for too long already!

Thanks for any help or insight you can offer.

View 1 Replies View Related

IsNull For Decimal Acting Up

Sep 22, 2006

When I run this code on a column of the type float or real it's ok, but not if the column is decimal, why?

this produces an error:

SELECT IsNull(column1,'') as column1
FROM mytable

---

this code works:

SELECT IsNull(column1,0) as column1
FROM mytable

---

The problem is that this query is used in a C++ environment to build an insert into another table. The query is build together in a CString.
C++ considers a CString to have ended if it encounters a NULL, therefore I need the check on all columns.

The error message I get is:

"Error converting data type varchar to numeric."

Why is it ok to use float or rel, but not decimal??

View 1 Replies View Related

MSSQL Acting Strange

Jul 20, 2005

I have MSSQL2k SP3a on WIN2k SP4.moved a Date/log files to this server about a week ago from a SQL7serverand attached it to this new Sql2k server.everything works fine for about 24hrs and then it starts timing out !!all I have to so is restart the MSSQL service and works fine againtill the next day !there is only this one database on this server and nothing else isrunning on it.have anyone seen this before ? how could this be fixed !!thanksDon

View 4 Replies View Related

RetainConnection Acting Random

Jan 2, 2008

Happy new year all SSIS Experts,

I wonder if anyone can explain what I am doing wrong here:

I need to transport data between several tables using dataflow components. I need to wrap this in transactions to either succeed or fail completly, I do want to use my own BEGIN TRAN and COMMIT TRAN statements, not the transaction support built into SSIS.

As written in several BLOGs, I should set the "RetainConnection" property to "TRUE" to make sure each dataflow runs on the same connection as the BEGIN and COMMIT statements... and that seems to be random.

Sometimes they do run on the same connection, but I also logged (using SQL Profiler) package executions where the BEGIN TRAN was issued to SPID different than the one used in first dataflow, another SPID again for the second dataflow and finally yet another for the COMMIT... in this case COMMIT fails with "needs an open transaction".

Any idea what is wrong there?

Cheers!Ralf

View 1 Replies View Related

SQL Table Acting Strange

Jul 14, 2007

Hi,



I have a strange problem with a sql table.

One of my table, dbo.Customer, gives me different values of "row count" everytime I check the properties.

The dbo.Customer have around 12 000 rows but when I check the properties (or open the table) the value always differs. The table row count can have any value from ~9000 - 12000.

Its like the table are trying to load all rows but cant.



Anyone have any idea what the problem can be?

View 1 Replies View Related

Bulk Insert Acting Differently Than Openrowset

Feb 22, 2008

I am trying to import some data from csv files. When I try it using bulk insert I get a conversion error. When I use the exact same format file and data file with an openrowset it works fine. I would prefer to use the BULK insert as I can make some generic stored procedures to handle all my imports and not have to code the column names in the SQL. Any suggestions?



BULK Insert stuff

From 'c:projects estdatalist.txt'

with

(FORMATFILE='c:projects estdatamyformat.xml')


insert into stuff (ExternalId, Description, ScheduledDate, SentDate, Name)

select *

from OPENROWSET (BULK 'c:projects estdatalist.txt',

FORMATFILE='c:projects estdatamyformat.xml')

as t1


The destination table has more columns than the data file. The Field IDs represent the ordinal position of the columns in the destination table. Column 1 in the destination table is an int identity. The conversion failure is from trying to convert column 5 to int which makes me think bulk insert is ignoring the name attributes in the XML and just trying to insert the columns into the table in order without skipping.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="12"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="
" MAX_LENGTH="500" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="ExternalId" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Description" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="ScheduledDate" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="8" NAME="SentDate" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="9" NAME="Name" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

View 1 Replies View Related

Questions On Attributes Acting As Both Inputs And Predictive?

Apr 30, 2007

Hi, all experts here,

Thanks a lot for your kind attention.

I am having quesion about attributes acting as both inputs and predictive outputs in a mining models, I mean if we are going to predict the outputs, then I cant actually see the effects of themselves acting as inputs as well?

I dont really see through the points of it.

Could please any experts here shed me any light on it.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

View 6 Replies View Related

SQL 2012 :: Failover - Secondary Server Acting As Primary Role

Jul 20, 2015

In my environment always on is there. Today I observed that primary server fail over to secondary server .now the secondary server acting as primary role.

Can I know when is fail over is happened and who did the fail over. Is there any script to find this?

View 4 Replies View Related

A User Group Account Acting Like A Content Manager And Admin On The Report Manager????

Nov 2, 2007



A user was created with a limited privilege under the USERS group. Once this user loged in the Report Manager he is acting like an Admin and Content Manager, though he is not given even a browser role.

What do u think that this guy is acting like a Super User evenif he is restricted to a browser role on the Report Manager ????????????

I did all my best, but no luck so far

View 5 Replies View Related

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related

Granting Permission To Object In X Database From A Store Procedure In Y Database

May 16, 2000

I need to grant select, insert, update, and delete permission to an object residing in Database "X" from a store procedure in database "Y". I have already tried USE database statement. Any suggestion.
Thanks
Maroof Khan

View 1 Replies View Related

Can I Call A Procedure In One Database From A Procedure In Another?

Oct 5, 2007



Hi,
I have an integration solution that backs into two databases. Database A is custom and Database B is integrated from a COTS product. The manufacturer of B supplies a formal interface as stored procedures (and I intend to honor that contract by limiting my access to B through those stored procs).
Now, I have a search that is going to involve a subselect in the form of the following pseudocode:

select a,b from dbA.user.TableX where exists (select a from dbB.user.TableY)

I am trying to avoid creating 2 temporary cursors in the integration application and doing things in-memory (b/c of performance concerns).

So, my q is:
How can I make this happen within database A?

View 3 Replies View Related

Inserting Record Into Second Database From A Stored Procedure In First Database

Aug 10, 2005

is it possible to insert record into second database from a stored procedure which is in first database?

View 1 Replies View Related

How Can I Identify The Caller's Database From A Stored Procedure In Another Database?

Apr 2, 2008

I am wondering if there is a way to create a procedure in a logging database that can identify the source database from which it is invoked. DB_NAME() of course returns the name of the database in which the stored procedure exists. I could pass the database name as a parameter to the proc, just wondering if there is another way.

This is SQL 2005, I did look into the sys.dm_exec views but nothing seems to have a dbid reflecting the calling context.

Thanks,
Mike

View 4 Replies View Related

How To Run A Stored Procedure Located In One Database Against Another Database

Feb 14, 2008



I have a stored procedure that is located in one database and I would like to have it execute against a different database. My problem is when I go to execute it, it is still executing against the database it is stored in. Is it possible to tell this stored procedure when it runs to execute its code against this second database?

This is what I have now, which isn't working:




Code Snippet

use [Database2]
exec [Database1].[dbo].usp_SetupDatabaseUser





The end result still executes against Database1.

Thanks for any advice,

Flea#

View 9 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Stored Procedure Use Different Database

May 10, 2006

Hi,
I have a set of databases. Each user had his own database. Every database had the same stored procedures. The is one "admin" database where content about these users is stored.
When I want to update or change a stored procedure I have to update it in every database. Therefore I want to put the stored procedures in the "admin" database. From there I have to access the databases of the user. I want to send the database name as a parameter.
How can I change de database which te procedure should access?
If I use the "use" statement I complains about that I can't use that command in a stored procedure. I've tried this method too:
SELECT * FROM @databasename.dbo.tblname
but that's not accepted too.
Does anybody know how to select another database in the stored procedure?

View 3 Replies View Related

Change Database In Procedure

Apr 4, 2008

I work at a medical billing office. We provide billing services for a number of clients. The primary billing software runs on a big AIX system, but for reporting and a few other things we get a daily dump into sql server. The way the dump happens each client get's their own database in sql server. This works great for reports that go to individual clients, but it can be a real pain for internal reports that cover all clients. We end up with sql code that looks something like this:
SQL Code:






Original
- SQL Code




ALTER PROCEDURE ProceName
AS
BEGIN
CREATE TABLE #Temp
(
Client varchar(5),
FieldName varchar(20),
OtherField int
)

DECLARE @Client varchar(5)
DECLARE @SQL varchar(8000)
DECLARE curAll cursor FOR /*SQL Code to get Client list here */
OPEN curAll
FETCH NEXT FROM curAll INTO @Client
WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL =
'SELECT ''' + @Client + ''', *'
+ ' FROM dump_' + @Client + '.dbo.TableName t'
+ ' WHERE t.FieldName=''somevalue'''

INSERT INTO #Temp
Exec(@SQL)

FETCH NEXT FROM curAll INTO @Client
END

--send results to application
SELECT * FROM #Temp

--clean up
CLOSE curAll
DEALLOCATE curAll
DROP Table #Temp
END






ALTER PROCEDURE ProceNameASBEGIN    CREATE TABLE #Temp    (       Client varchar(5),       FieldName varchar(20),       OtherField int    )     DECLARE @Client varchar(5)       DECLARE @SQL varchar(8000)    DECLARE curAll CURSOR FOR /*SQL Code to get Client list here */    OPEN curAll    FETCH NEXT FROM curAll INTO @Client    WHILE @@FETCH_STATUS = 0    BEGIN          SET @SQL =             'SELECT ''' + @Client + ''', *'        + ' FROM dump_' + @Client + '.dbo.TableName t'        + ' WHERE t.FieldName=''somevalue'''         INSERT INTO #Temp            Exec(@SQL)          FETCH NEXT FROM curAll INTO @Client    END      --send results to application    SELECT * FROM #Temp     --clean up    CLOSE curAll    DEALLOCATE curAll    DROP TABLE #TempEND


As you can imagine, this is a real pain. There must be someway to dynamically change the current database in the procedure and run the code there directly rather than creating a string and calling out to exec 50+ times. Any ideas?

View 5 Replies View Related

Database Backup Procedure

May 2, 2007

--------------------------------------------------------------------------------

hi
i found the procedure to take database backup as

CREATE Procedure Backup_Database
(
@dbName varchar(100),
@Path varchar(200) = '' OUTPUT
)
As
Begin
Declare @Now varchar(100)

if Isnull(@dbName,'') = ''
Begin
RaisError('No Database Name specified while running the procedure',16,1)
return
End
if isnull(@path,'') = ''
Begin
RaisError('No file Name specified while running the procedure',16,1)
return
End
-- Make the filename
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
Select @path = @Path + '' + @dbName + '_' + @Now + '.Bak'
-- Take the backup of the database at the specified folder
Backup Database @dbName
To Disk = @Path
With Init
if @@Error <> 0
Begin
RaisError('Error Occurred while taking the Backup, Please check Error Log for Details',16,1)
return
End

Print 'Backup Taken Successfully at ' + @path
-- RETURN @path
End


GO



and iam executing procs as follows

exec Backup_Database 'Malathi','C:/New Folder (2)'

above executaion gives error as follows


Cannot open backup device 'C:New Folder (2)Malathi_20070502170331.Bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.
Error Occurred while taking the Backup, Please check Error Log for Details


Malathi Rao

View 2 Replies View Related

How To CREATE PROCEDURE In Another DATABASE?

Oct 2, 2007

Hi,

I need to write a script, which should run in a particular database (which name is not know, but I get it at runtime). It should add another database, which should be the same name with some suffix (i.e. "_Log"). And then it should create some tables and stored procedures there. The biggest problem is creating stored procedure, because CREATE PROCEDURE sentence doesn't allow to specify the database name, and it has to be the first statement in a batch (so USE doesn't help). I tried something like:




Code Block
DECLARE @logDBName nvarchar(100)
SET @logDBName = DB_NAME() + '_Log'
BEGIN TRY
EXEC ('CREATE DATABASE ' + @logDBName)
EXEC ('
CREATE TABLE [' + @logDBName + ']..Some_Log
(
id int IDENTITY(1, 1) NOT NULL,
timestamp datetime DEFAULT(GETDATE()) NOT NULL,
someText ntext NOT NULL
CONSTRAINT PK_Some_Log
PRIMARY KEY CLUSTERED (id)
)')
EXEC ('
CREATE PROCEDURE Add_To_Log
@someText ntext
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Some_Log(timestamp, someText) VALUES (GETDATE(), @someText)
END
')
END TRY
BEGIN CATCH
END CATCH




Of course this sample creates the stored procedure in the current database, and not in the newly created one. As I mentioned, if I add USE to the last EXEC, it doesn't work, because 'CREATE PROCEDURE must be first batch statement', and I can't write '[Some_Log]..Add_To_Log', because CREATE PROCEDURE syntax doesn't allow to specify the database name.
P.S.: I should also be able to alter the tables and procedures int he future from the CURRENT database, again by using

SET @logDBName = DB_NAME() + '_Log'

Thank you

View 5 Replies View Related

Trying To Add Info To Database Using A Stored Procedure

Jul 16, 2004

Hi there :)

I was hoping someone could help me figure out why this doesn't work....

I have a class called ProductsDB with a method called AddProduct that looks like this:


public String AddProduct(int categoryID, int makeID, string name, double price, double saleprice, double offerprice, int homepage, string thumbpath, string imagepath, string description)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ProductsAdd", myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;

// Add Parameters to SPROC
SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);

SqlParameter parameterMakeID = new SqlParameter("@MakeID", SqlDbType.Int, 4);
parameterMakeID.Value = makeID;
myCommand.Parameters.Add(parameterMakeID);

SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Value = name;
myCommand.Parameters.Add(parameterName);

SqlParameter parameterPrice = new SqlParameter("@Price", SqlDbType.Money, 8);
parameterPrice.Value = price;
myCommand.Parameters.Add(parameterPrice);

SqlParameter parameterSalePrice = new SqlParameter("@SalePrice", SqlDbType.Money, 8);
parameterSalePrice.Value = saleprice;
myCommand.Parameters.Add(parameterSalePrice);

SqlParameter parameterOfferPrice = new SqlParameter("@OfferPrice", SqlDbType.Money, 8);
parameterOfferPrice.Value = offerprice;
myCommand.Parameters.Add(parameterOfferPrice);

SqlParameter parameterHomepage = new SqlParameter("@Homepage", SqlDbType.Bit, 1);
parameterHomepage.Value = homepage;
myCommand.Parameters.Add(parameterHomepage);

SqlParameter parameterThumbnail = new SqlParameter("@Thumbnail", SqlDbType.NVarChar, 50);
parameterThumbnail.Value = thumbpath;
myCommand.Parameters.Add(parameterThumbnail);

SqlParameter parameterImage = new SqlParameter("@Image", SqlDbType.NVarChar, 50);
parameterImage.Value = imagepath;
myCommand.Parameters.Add(parameterImage);

SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
parameterProductID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProductID);

try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

// Calculate the ProductID using Output Param from SPROC
int productId = (int)parameterProductID.Value;

return productId.ToString();
}
catch
{
return String.Empty;
}
}



And then in another cs file for my aspx page I have an event handler for an add button in a webform, which looks like this:


// Add New Product to ProductDB database
bikescene.Components.ProductsDB accountSystem = new bikescene.Components.ProductsDB();
String productId = accountSystem.AddProduct(Int32.Parse(DDLproductcategory.SelectedValue), Int32.Parse(DDLproductmanufacturer.SelectedValue), TBproductname.Text, Double.Parse(TBproductprice.Text), Double.Parse(TBproductsaleprice.Text), Double.Parse(TBproductofferprice.Text), Int32.Parse(DDLproducthomepage.SelectedValue), thumbnailpath, imagepath, TBproductdescription.Text);


And finally, here is my stored procedure:


CREATE Procedure ProductsAdd
(
@ProductID int,
@CategoryID int,
@MakeID int,
@Name nvarchar(50),
@Price money,
@SalePrice money,
@OfferPrice money,
@Homepage int,
@Thumbnail nvarchar(50),
@Image nvarchar(50),
@Description nvarchar(500)
)
AS

INSERT INTO Products
(
productID,
categoryID,
makeID,
name,
price,
saleprice,
offerprice,
homepage,
thumbpath,
imagepath,
description
)
VALUES
(
@ProductID,
@CategoryID,
@MakeID,
@Name,
@Price,
@SalePrice,
@OfferPrice,
@Homepage,
@Thumbnail,
@Image,
@Description
)

SELECT
@ProductID = @@Identity
GO


Basically, my images are uploaded to the server just fine but nothing is being added to the database + I don't get any error messages.

Any ideas?

Many thanks indeed :)

View 1 Replies View Related

Multiple Database Stored Procedure

Apr 18, 2001

I am trying to write a stored procedure that has an inner join between two tables from two different databases on the same sql server.

Something like...

Select * FROM
DB1-table1 INNER JOIN DB2-table1
ON DB1-table1.ID = DB2-table1.ID

yada yada yada.......

Does anyone know how to do this or is it possible? If so, what database should I put the stored procedure in or does it matter?

View 2 Replies View Related

Stored Procedure Database Qualification

Nov 3, 2000

We are trying to develop some standards for SQL stored procedures and I am wandering what is the preferrable method to reference a table within a stored procedure for use within a Client Server / Web OLTP.
One:
SELECT Customer_Id, Customer_Name FROM CUSTOMER

Or TWO:
SELECT Customer_Id, Customer_Name FROM [DATABASENAME].[dbo].[CUSTOMER]

Most of my background is in working with data warehousing which is constantly loading between a staging and main DB, so I am in the habit of using TWO, but is this the best method for an OLTP and why.

Thanks
Loren

View 1 Replies View Related

Passing Database Name To A Stored Procedure

Jul 28, 2004

Hi all,
I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement.
Thank you for all the help you can give me.
Regards,
Eve

View 3 Replies View Related

Run Stored Procedure In Specific Database

Jul 20, 2007

I have created a stored procedure in the master database that can be run against any database on the server.
I call this procedure from a sql server agent job. In this job I specify the database I want the procedure to run against but it always seems to run against the master database (presumably because the procedure is in this database). I can't switch database inside the procedure so what can I do other than creating a copy of the procedure in every database?

View 4 Replies View Related

Can A Stored Procedure Access Another Database?

Jun 30, 2004

Hi all,

I have an urgent problem. Can a stored procedure create a connection or access data from another database?

Thanks in advace. :)

View 11 Replies View Related

Restoring The Database Using Stored Procedure

Mar 5, 2005

hey guyz...
i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"

i have included the stored procedure below. is the stored procedure correct?
if it is.. how can i sovle this problem?


CREATE Procedure spRestoreDatabase
@Path VARCHAR(100)
AS
Restore Database Test From Disk = @Path
GO

View 4 Replies View Related

Stored Procedure To Access Another Database...

Feb 8, 2008

Hi. I'm trying to write a stored procedure that will access another database on a different server. How can I set this up in a stored procedure? It is a SQL Server 2000 database and it will be using SQL server authentication. Thanks!

View 3 Replies View Related

Script Database Using Stored Procedure

Jan 21, 2004

Is it possible to use a stored procedure to script an entire database in MS SQL 2000?

View 8 Replies View Related

How Do You Create A Database Within A Stored Procedure

Apr 20, 2004

I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


create database @dbname
on
( name = @dbname + 'data',
filename = 'F:MSSQLData' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:MSSQLData' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )

select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

end

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved