Error When Attempting To Insert Record Using Stored Procedure

Jun 21, 2006

I am trying to save user data to a sql table but keep getting the following error;

Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'.

I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows;

Public Function chkUser(ByVal strUser As String) As String
dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand
Dim ReturnString As String
Dim params As SqlParameter
Dim SelectCmd As String = "wm_CheckUser"
MyConnection = New SqlConnection(myConnectionString)
MyCommand = New SqlCommand(SelectCmd, MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

Try
params = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUser
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
ReturnString = "Record Exists!"
Catch Exp As SqlException
ReturnString = ReturnError(Exp.Number, "Users", Exp.Message)
End Try
MyCommand.Connection.Close()
Return ReturnString

End Function

Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process.

Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help.

Thanks,

Jaime

View 7 Replies


ADVERTISEMENT

Using A Stored Procedure To Insert A New Record

Apr 7, 2004

ok I have a stored procedure......
I pass in the variables that are requried....What is the best way to add a record
using my stored procedure in VB.net code in a button click event......
How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn




CREATE procedure dbo.Appt_AddAppt
(
@ClinicID int,
@AccountNum nvarchar(10),
@DOS nvarchar(12),
@POSCODE nvarchar(5)
)
as
Insert into Clinic_Appointments (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID)
Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID)



GO

View 4 Replies View Related

Stored Procedure To Insert Record

Jul 27, 2004

I have an SP to add a record to the database but i get the error shown below. Any help appreciated.

Stored Procedure:

CREATE PROCEDURE addUser
@username char(15),
@password char(12)

AS
INSERT INTO users(username, password) VALUES (@username, @password)

GO


Code calling SP:

Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)

Dim cmd As New SqlCommand("addUser", myConnection)
MyDataAdapter = New SqlDataAdapter()
'MyDataAdapter = New SqlDataAdapter("addUser", myConnection)

With cmd 'MyDataAdapter
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@username", SqlDbType.Char).Value = username
.Parameters.Add("@password", SqlDbType.Char).Value = password
End With

Try
myConnection.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Finally
myConnection.Close()
End Try


I get an error message on the line: cmd.ExecuteNonQuery()

System.FormatException: Input string was not in a correct format.

View 2 Replies View Related

A Good Insert Record If Non Existant Stored Procedure

May 26, 2004

Can anyone spare me a good SP that ....

checks if the record exists - and if so return the identity.

and if the record does not exist

inserts it and returns the identity.

Mine keep flipping up :) so I rather look at someone else and use theirs.

Thanks in advance.

View 3 Replies View Related

How To Excute Stored Procedure That Insert Record And Return Last Inserted Value

Jul 18, 2007

Dear all,
I am using C# , asp.net and sql server 2005.
Let me explain the situation.
I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can
Get last inserted variable values      
Please help 
thanks 
 

View 3 Replies View Related

Insert Stored Procedure With Error Check And Transaction Function

Jul 21, 2004

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);


My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as

if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)

/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’

commit transaction
return 0
go

View 1 Replies View Related

DB Engine :: Error Trying To Execute BULK INSERT In Stored Procedure

May 16, 2015

At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.

-2147217900
[Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement.
upImportFromICPMSRaw 'GSADC1CompanyInstrumentOutputFilesICPMSNew185367.csv', tblFromICPMSRaw

The customer has SQL Server 2008 R2 Express installed

The connection string to the database works on everything else and it is the sa account with password

On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.

View 5 Replies View Related

Transact SQL :: Insertion Procedure To Insert A Record In More Than One Table

Nov 17, 2015

Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .

View 8 Replies View Related

ASP: Error When Trying To Insert Record

Jul 6, 2007

I receive the following error when trying to INSERT INTO; am using Access db.



Microsoft OLE DB Provider for ODBC Drivers error '80040e07'[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


The code is as follows:



<%
Dim dbConn
set dbConn = server.CreateObject("adodb.connection")
dbConn.open("connect")
dbConn.Execute("INSERT INTO tbl_country (region, countryName, population, country_currency, description, imageURL, imageALT) VALUES ('" & Request.Form("region") & "', '" & Request.Form("countryName") & "', '" & Request.Form("population") & "', '" & Request.Form("country_currency") & "', '" & Request.Form("description") & "', '" & Request.Form("imageURL") & "', '" & Request.Form("imageALT") & "'),")
Response.Redirect("admin_master.asp")
%>



I would greatly appreciate any help you can give me.

View 7 Replies View Related

Problem With Getting Last Record From Stored Procedure

Mar 27, 2008

I have three tables in this shipping scenario -- one to record the Sender's Info, one to record the Recipient's Info, and one to record the Shipment Info, and I perform the inserts in that order.  I want to get the last inserted USERID (which is an INT, and the PK) from both the Sender's table and the Recipient's table so that I can record that info as a foreign key in my Shipment table. 
Everything compiles and runs.  However, every time I run it, "2" is inserted in my foreign key columns in the Shipment Info table.  I can't figure out where that 2 is coming from.
So I analyzed the SP in query analyzer, and when I run it it shows:
"(0 row(s) returned)@RETURN_VALUE=0"
Can anyone help me with this?  I'm so close I think:
First, my SP's:
***********************************************************
ALTER PROCEDURE dbo.INSERT_NEW_SENDER(@firstname nvarchar(50), @lastname nvarchar(50), @email nvarchar(50),@phone nvarchar(50))ASBEGIN TRANSACTIONDECLARE @NewID intSET NOCOUNT ONINSERT INTO SHP_USER(email, firstname, lastname, phone) VALUES(@email, @firstname, @lastname, @phone)SET @NewID = CONVERT(int, SCOPE_IDENTITY())COMMIT TRANSACTIONGO
**************************************************************
ALTER PROCEDURE dbo.SP_INSERT_NEW_RECIPIENT(@firstname nvarchar(50),@lastname nvarchar(50),@company nvarchar(50),@division nvarchar(50),@address1 nvarchar(50),@address2 nvarchar(50),@city nvarchar (50),@state nvarchar (50),@zip nvarchar (50),@country nvarchar(50),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(50),@address_type nvarchar(50),@recipient_type nvarchar(50),@NewID int OUTPUT)AS/* SET NOCOUNT ON */ INSERT INTO SHP_recipient(firstname, lastname, company, division, address1, address2, city, state, zip, country, phone, fax, email, address_type, recipient_type) VALUES(@firstname, @lastname, @company, @division, @address1, @address2, @city, @state, @zip, @country, @phone, @fax, @email, @address_type, @recipient_type)SET @NewID = CONVERT(int, SCOPE_IDENTITY())RETURN
**********************************************************************
And my .NET stuff:
//NEW USER INSERT SqlCommand myCommand;
SqlDataReader myReader; int sender_userid = 0;
int recipient_userid = 0;
//int NewID; myCommand = new SqlCommand("INSERT_NEW_SENDER", conn);
myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@firstname", str_firstname));
myCommand.Parameters.Add(new SqlParameter("@lastname", str_lastname));myCommand.Parameters.Add(new SqlParameter("@phone", str_phone));
myCommand.Parameters.Add(new SqlParameter("@email", str_email));myCommand.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
myCommand.Connection.Open();
myReader = myCommand.ExecuteReader();sender_userid = (int)myCommand.Parameters["@NewID"].Value;
myCommand.Connection.Close();
//RECIPIENT INSERT SqlCommand myCommand2;
SqlDataReader myReader2; myCommand2 = new SqlCommand("SP_INSERT_NEW_RECIPIENT", conn);
myCommand2.CommandType = CommandType.StoredProcedure;
//myCommand2.Parameters.Add(new SqlParameter("@userid", recipient_userid));myCommand2.Parameters.Add(new SqlParameter("@firstname", str_recip_firstname));
myCommand2.Parameters.Add(new SqlParameter("@lastname", str_recip_lastname));myCommand2.Parameters.Add(new SqlParameter("@company", str_company));
myCommand2.Parameters.Add(new SqlParameter("@division", str_division));myCommand2.Parameters.Add(new SqlParameter("@address1", str_recip_addr1));
myCommand2.Parameters.Add(new SqlParameter("@address2", str_recip_addr2));myCommand2.Parameters.Add(new SqlParameter("@city", str_city));
myCommand2.Parameters.Add(new SqlParameter("@state", str_state));myCommand2.Parameters.Add(new SqlParameter("@zip", str_zip));
myCommand2.Parameters.Add(new SqlParameter("@country", str_country));myCommand2.Parameters.Add(new SqlParameter("@phone", str_recip_phone));
myCommand2.Parameters.Add(new SqlParameter("@fax", str_fax));myCommand2.Parameters.Add(new SqlParameter("@email", str_recip_email));
myCommand2.Parameters.Add(new SqlParameter("@recipient_type", str_recipient_type));myCommand2.Parameters.Add(new SqlParameter("@address_type", str_Address_Type));
myCommand2.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand2.UpdatedRowSource = UpdateRowSource.OutputParameters;
myCommand2.Connection.Open();
myReader2 = myCommand2.ExecuteReader();recipient_userid = (int)myCommand2.Parameters["@NewID"].Value;
myCommand2.Connection.Close();
try
{
conn.Open();
//INSERT data into the SHP_SHIPMENTS tableSqlCommand cmd = new SqlCommand("INSERT INTO SHP_SHIPMENTS(sender, recipient, ship_method, rma, payment_method, must_pay, project, business_unity, office, attachments, shipment_description,shipment_notes,date_created) VALUES(@sender_userid, @recipient_userid, @ship_method,@rma,@payment_method,@must_pay,@project,@business_unity,@office,@attachments,@shipment_description,@shipment_notes, GetDate())", conn);
 cmd.Parameters.Add(new SqlParameter("@sender_userid", sender_userid));
cmd.Parameters.Add(new SqlParameter("@recipient_userid", recipient_userid));cmd.Parameters.Add(new SqlParameter("@ship_method", str_ship_method));
cmd.Parameters.Add(new SqlParameter("@rma", str_rma));cmd.Parameters.Add(new SqlParameter("@payment_method", str_payment_method));
cmd.Parameters.Add(new SqlParameter("@must_pay", str_must_pay));cmd.Parameters.Add(new SqlParameter("@project", str_project));
cmd.Parameters.Add(new SqlParameter("@business_unity", str_business_unit));cmd.Parameters.Add(new SqlParameter("@office", str_office));
cmd.Parameters.Add(new SqlParameter("@attachments", str_attachments));cmd.Parameters.Add(new SqlParameter("@shipment_description", str_shipment_description));cmd.Parameters.Add(new SqlParameter("@shipment_notes", str_shipment_notes));
cmd.ExecuteNonQuery();
 
}catch (Exception ex)
{FormMessage.Text = "Error: Insert Shipment - " + ex.Message;
}
finally
{
conn.Close();
}
 
 
 
 
 

View 34 Replies View Related

How Do I Loop Through A Record Set In A Stored Procedure?

Jan 17, 2006

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.
Alternatively, is there a more elgant approach that will return the same set of recordsets?
Any help would be much appreciatedThanks
ALTER PROCEDURE dbo.OPA_GetMenuItemsASDeclare @i tinyint ,@tc tinyintSet @i = 1
/* Select for top level menu items*/
SELECT id, label, url, sortFROM mainNavORDER BY sort
Set @tc = @@rowcount
while @i <= @tc
beginSet @i = (@i + 1)
/* Select for submenu itemsSELECT id, label, url, sort, mainNavIdFROM SubNavWHERE (mainNavId = @i)ORDER BY mainNavId, sortend
RETURN
 
 

View 7 Replies View Related

LIKE In Stored Procedure Returns No Record

Jun 29, 2005

I have following stored procedure:
-------------
CREATE PROCEDURE dbo.Test
@name as char(36)

as

select a, b
from testtable
where name LIKE @name +'%'
------------

when I run the select statement from query analyzer,

select a, b from testtable where name LIKE 'a%'

it returns records.

But when I call the stored procedure from query analyzer,

exec Test 'a'

it returns no record.

What might be wrong?

Any help is appreciated.

View 3 Replies View Related

How Do I Loop Thru A Record Set In A Stored Procedure?

Jan 17, 2006

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @i tinyint ,
@tc tinyint
Set @i = 1

/* Select for top level menu items*/

SELECT id, label, url, sort
FROM mainNav
ORDER BY sort

Set @tc = @@rowcount

while @i <= @tc

begin
Set @i = (@i + 1)

/* Select for submenu items*/
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @i)
ORDER BY mainNavId, sort
end

RETURN

View 6 Replies View Related

Duplicate Record Inserted With Stored Procedure

Feb 4, 2008

I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000.  Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO  Sub AddUserLog(ByVal Username As String)
Dim SqlText As String
Dim cmd As SqlCommand
Dim strIPAddress As String

'Get the users IP address
strIPAddress = Request.UserHostAddress

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
SqlText = "sp_AddUserLog"
cmd = New SqlCommand(SqlText)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con

cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username
cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress

Try
con.Open()
cmd.ExecuteNonQuery()
Finally
con.Close()
End Try

End Sub 

View 7 Replies View Related

Help Getting An ID, Back From A Record, That Has Just Been Inserted With A Stored Procedure

Mar 21, 2008

Hi,
I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID
This is my stored procedure:ALTER PROCEDURE dbo.quote
 
(@Business_Name nvarchar(50),
@Business_Type nvarchar(50),@Contact_Title nchar(10),
@Contact_First_Name nvarchar(50),@Contact_Last_Name nvarchar(50),
@Address_Line_1 nvarchar(MAX),@Address_Line_2 nvarchar(MAX),
@City nvarchar(MAX),@Postcode nchar(7),
@Tel_No nchar(11),@E_mail_Address nvarchar(50),
@Distributor_ID int,@Profile_Class int,
@Meter_Time_Code int,@Line_Loss_Factor int,
@Unique_Identifier1 int,@Unique_Identifier2 int,
@Check_Digit int,@Tariff nchar(20),
@UnitRate1AnnualUsage nchar(10),@UnitRate2AnnualUsage nchar(10),
@UnitRate1SubTotal money,@UnitRate2SubTotal money,
@QuoteTotal money
)
ASINSERT INTO client_details (Business_Name, Business_Type, Contact_Title, Contact_First_Name, Contact_Last_Name, Address_Line_1, Address_Line_2, City, Postcode, Tel_No, email_Address)VALUES (@Business_Name, @Business_Type,@Contact_Title, @Contact_First_Name, @Contact_Last_Name, @Address_Line_1, @Address_Line_2, @City, @Postcode, @Tel_No, @E_mail_Address)
 
DECLARE @Client_ID INTSET @Client_ID = scope_identity()
 INSERT INTO meter_quote (Client_ID, Tariff, Meter_Distributor_ID, Meter_Profile_Class, Meter_Time_Code, Meter_Line_Loss_Factor, Unique_Identifier1, Unique_Identifier2, Check_Digit, UnitRate1AnnualUsage, UnitRate2AnnualUsage, UnitRate1SubTotal, UnitRate2SubTotal, QuoteTotal)VALUES (@Client_ID, @Tariff, @Distributor_ID, @Profile_Class, @Meter_Time_Code, @Line_Loss_Factor, @Unique_Identifier1, @Unique_Identifier2, @Check_Digit, @UnitRate1AnnualUsage, @UnitRate2AnnualUsage, @UnitRate1SubTotal, @UnitRate2SubTotal, @QuoteTotal)
 
RETURN
And this is the code I have in my asp page:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="quote" InsertCommandType="StoredProcedure">
<InsertParameters><asp:ControlParameter ControlID="TextBoxBusinessName" DefaultValue=""
Name="Business_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="DropDownBusinessType" Name="Business_Type"
PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="DropDownListTitle" Name="Contact_Title"
PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="TextBoxFirstName" Name="Contact_First_Name"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLastName" Name="Contact_Last_Name"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine1" Name="Address_Line_1"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine2" Name="Address_Line_2"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine3" Name="City"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxPostcode" Name="Postcode"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxTelNo" Name="Tel_No"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxEmail" Name="E_mail_Address"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxDistributorID" Name="Distributor_ID"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxProfileClass" Name="Profile_Class"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxMeterTimeCode" Name="Meter_Time_Code"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLineLossFactor" Name="Line_Loss_Factor"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxUniqueIdentifier1" Name="Unique_Identifier1"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUniqueIdentifier2" Name="Unique_Identifier2"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxCheckDigit" Name="Check_Digit"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="LabelTariff2" Name="Tariff"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUnitRate1Usage" Name="UnitRate1AnnualUsage"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate2Usage" Name="UnitRate2AnnualUsage"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate1Total" Name="UnitRate1SubTotal"
PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelUnitRate2Total" Name="UnitRate2SubTotal"
PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelQuoteTotal" Name="QuoteTotal"
PropertyName="Text" Type="Decimal" />
</InsertParameters>
</asp:SqlDataSource>
And the following in the C# code:
try
{
SqlDataSource3.Insert();//Insert quote details into the database using a stored procedure
}catch (Exception ex)
{LabelInsertException.Text = "Failed" + ex.Message;
}
Any help would be much appreciated
Thanks, Hayley
 
 

View 6 Replies View Related

Inserting A Record Using Values From Another Stored Procedure

Aug 2, 2006

Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.

View 1 Replies View Related

Stored Procedure For Deleting A Record With Contstraints (m:n)

Oct 9, 2007

I can not get this stored procedure to delete my records...

I have a
contact table
RecordID
FirstName
LastName
etc

and a Address table

RecordID
Street
Zip
Town
Country

And a Relation table

RecordID
ContactID
AddressID
CreateDate


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[bc_Contact_Delete]

@ContactID int

AS

--SET NOCOUNT ON

BEGIN TRY

BEGIN TRANSACTION -- Start the transaction



-- Delete all Adresses

DELETE FROM [Address]

WHERE

RecordId in (SELECT ca.AdressId from [ContactAddress] ca

where

ca.ContactID = @ContactID)

-- Delete all Relations

DELETE FROM [ContactAdress]



WHERE ContactID = @ContactID

--- Delete Kontakt

DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))

COMMIT TRANSACTION

END TRY

BEGIN CATCH

-- Whoops, there was an error

ROLLBACK TRANSACTION

-- Raise an error with the

-- details of the exception

DECLARE @ErrMsg nvarchar(4000),

@ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),

@ErrSeverity = ERROR_SEVERITY()



RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

RETURN


My Errormessage is

The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.

Can someone please post me an advice?

View 5 Replies View Related

SSRS Forms Authentication Error:An Error Occurred While Attempting To Get The ReportServer Url. The RPC Server Is Unavailable. (

Apr 3, 2008

I have managed to get the Forms Authentication sample to work bu there is a hitch. I am running Report Server and Report Manager on the same(test) PC. I can logon to the Report Server without a problem but when I try logon from the UILogon.aspx page for Report Manager I receive the following error:

An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)An error occured while attempting to get the ReportServer Url. The RPC server is unavailable.(Exception from HRESULT: 0x800706BA).

I can register users which suggests that the page can connect to the database.

Any help???

View 3 Replies View Related

BCP -- Insert Record Error (SQL2005-Window 2003)

Aug 21, 2006

Hi All,

When we to inset record into the tables through BCP we are getting following errors.

C:> bcp ACCT.dbo.dtpro out C: estdtpro_TA.dat -T-n
C:>bcp ACCT.dbo.Semiaa out C: estSemiaa_TA.dat -T -n

BCP out works fine only BCP in have problems.

Please can you let me know how to resolved this problem.

===========
Exp - 1
===========

C:> bcp ACCT.dbo.dtpro in C: estdtpro_TA.dat -T-n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

===========
Exp - 2
===========

C:>bcp ACCT.dbo.Semiaa in C: estSemiaa_TA.dat -T -n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

============
Exp-3
============

C:>bcp ACCT.dbo.SemiH in C: estSemiH_TA.dat -T -n

Starting copy...SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
SQLState = 42000, NativeError = 7339
Error = [Microsoft][SQL Native Client][SQL Server]OLE DB provider 'STREAM' for l
inked server '(null)' returned invalid data for column '[!BulkInsert].Created'.


BCP copy in failed




Thanks in Advance






View 12 Replies View Related

DB Engine :: Can Find A Record Of Stored Procedure Being Called?

Jul 15, 2015

I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.

Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005

USE YourDb;

SELECT qt.[text] AS [SP Name],
qs.last_execution_time,
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')

The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?

View 3 Replies View Related

T-SQL (SS2K8) :: Nested Stored Procedure - Multiple Results Of Record Sets Coming

Oct 1, 2014

I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.

I have 2 problems in that

1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.

I have attached the scripts also, the line i described in step1 is

View 9 Replies View Related

T-SQL (SS2K8) :: Error When Attempting To Set Variables Value

May 28, 2014

I am getting the error 'Incorrect syntax near the keyword set' with the following code snippet -

declare @dteTo Date
set @dteTo = CONVERT(date, GETDATE())

My intention is to set the variable @dteTo to the current date (no time component). If I run a SELECT CONVERT(date, GETDATE()) command I do get what I want, it is just assigning the value to the variable that isn't working as intended.

View 8 Replies View Related

Attempting To Create First Database - Error

Feb 17, 2007

I've just installed SQL Server Express and attempted to create my first database and received this error message :-(

Maybe this is a long shot, but can someone tell me what I've done wrong? Do I have a bad install or what?

Essentially I followed the steps below to create the database:

1. Started MS SQL Server Management studio Express
2. Right clicked on the Database folder
3. Selected "New Database..."
4. New Database panel displayed
5. Entered name of database - pip
6. Clicked "OK"
7. Receieved error dialog with followig message:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Create failed for Database 'pip'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

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

Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

View 1 Replies View Related

Error Attempting To Import From Excel

May 3, 2006

Here is the error:

TITLE: SQL Server Import and Export Wizard
------------------------------

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

------------------------------
ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

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

The connection type "EXCEL" specified for connection manager "{55E5636D-F2A9-48D6-9723-867C9F241F5B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({F390DBA3-1B52-43F4-A624-9E71D273B4D7})



I really need to get this data into SQL server... why do I have to go through SQL 2000?



Regards,

View 7 Replies View Related

Error When Attempting To Delete Rows

Sep 19, 2005

I have a log table with no indexes, triggers, or keys.  During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

View 6 Replies View Related

SQL Server 2014 :: Stored Procedure - Add A Record To Local Database Executing In Cloud Environment

May 20, 2015

Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?

View 2 Replies View Related

Error 4064 After Attempting Restore Backup?!

Sep 16, 2006

Hi,

We have a little problem for you:

We have 2 servers both running sqlexpress2005, both have service pack 1 installed, both have the same db (same tables etc.) both have the same users with the same passwords. Everything is identical.

What I want to do is move the db. I am attempting to do this by making a backup of the db and restoring it into the second machine. (I am not sure of a more practical way to do this without stopping the SQL server).

This is the problem, the backup process works fine, without error, but when restoring the copy to the other server ,it loses the dbowner property and all default users when attempting to connect recieve a 4064 error even though all the password for users are there. Very strange.

I tried to restore the copy to to the same server that I made the copy and everything works, this only happens when attempting to restore the copy on another machine.

Do you know if this is a possible bug? I have tried to do further test on 4 different machines now and the same happens.

The objective is to be able to send the data from sqlexpress server to another by making a backup and without the need t stop the sql server. Any suggestion would be appreciated.

Adam

View 5 Replies View Related

Error When Attempting To Configure A Local Distributor

May 26, 2007

i'm getting the following error message when i attempt to configure my server as a local distributor:



TITLE: Connect to Server

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

Cannot connect to 9A6375C3046246B.

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

ADDITIONAL INFORMATION:

Failed to connect to server 9A6375C3046246B. (Microsoft.SqlServer.ConnectionInfo)

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

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)



this error is occuring despite the fact that i have the server configured to allow remote connections. can someone please help me troubleshoot this error? thanks.

View 11 Replies View Related

Error When Attempting To Backup To A Second Disk File

Feb 12, 2007

In SQL Server 2005, via the GUI, I wish to backup a database to an additional disk file (there is already an existing backup disk file for this database), so that I can have more than one backup. I've added the new disk file name, highlighted it, and clicked OK.

I get an immediate error (see below). Note, the 2nd error message is specifying the existing backup disk file, not the new one I'm attempting to create.

"Backup failed for Server 'WCS-DEV-TPA'. (Microsoft.SqlServer.Smo)"

"System.Data.SqlClient.SqlError: The volume on device 'D:Program FilesMicrosoft SQL ServerMSSQLBACKUPWCS_ADV_Longmont.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. (Microsoft.SqlServer.Smo)"

Does anyone know what causes this and how to correct it?

View 11 Replies View Related

Stored Procedure Insert

Nov 7, 2006

Hi,
I am having trouble inserting 2 fields in a row using a stored procedure.
This works fine:
Exec ('Insert Into NumbersPull (Number)'+ @SQL)
but when I try to insert another value into field 2 it errors out:
I try this:
Exec ('Insert Into NumbersPull
(Number,resultID) Select ('+ @SQL
+ '),' + @resultID'
)
and get this error:
ERROR: Line 2: Incorrect syntax near ')'.
Thanks,
Doug

View 3 Replies View Related

INSERT INTO With Stored Procedure

Feb 27, 2007

Having problem do INSERT values to my SQL DB with an StoredProcedure. SELECT works fine.
My StoredProcedure :
CREATE PROCEDURE insert_test
@id  int ,@Rubrik char(25), @Info  char(60) , @Datum datetime
ASINSERT INTO test_news(ID, Rubrik, Info, Datum) VALUES (@id, @Rubrik, @Info, @Datum)GO
The StoredProcedure  works fine in the SQL Query Analyzer
 
My Code;
int num= 1234; string rub = "KLÖKÖLKÖLKÖL"; string ino = "slökdjfkasdkfjsdakf";SqlConnection myConnection = new SqlConnection("server='SOLDANER\DAER'; trusted_connection=true; database='SeWe'");
SqlCommand myCommand = new SqlCommand("insrt_test", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add( "@id",num);myCommand.Parameters.Add( "@Rubrik",rub );myCommand.Parameters.Add( "@Info", ino);myCommand.Parameters.AddWithValue( "@Datum", DateTime.Now );
 Even tried AddWithValues
Dont get any error.....

View 3 Replies View Related

Insert Stored Procedure Help

Oct 31, 2007

OK I have a stored procedure that inserts information into a database table.  Here is what I have so far:
I think I have the proper syntax for inserting everything, but I am having problems with two colums.  I have Active column which has the bit data type and a Notify column which is also a bit datatype.  If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns.  I tried using the set command, but it will give me a xyntax error  implicating the "=" in the Active = 1 How can I set these in the stored procedure?1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:xxxxxxxx
7 -- Create date: 10/31/07
8 -- Description:Insert information into Registration table
9 -- =============================================
10 ALTER PROCEDURE [dbo].[InsertRegistration]
11
12 @Name nvarchar(50),
13 @StreetAddress nchar(20),
14 @City nchar(10),
15 @State nchar(10),
16 @ZipCode tinyint,
17 @PhoneNumber nchar(20),
18 @DateOfBirth smalldatetime,
19 @EmailAddress nchar(20),
20 @Gender nchar(10),
21 @Notify bit
22
23 AS
24 BEGIN
25 -- SET NOCOUNT ON added to prevent extra result sets from
26 -- interfering with SELECT statements.
27 SET NOCOUNT ON;
28
29 INSERT INTO Registration
30
31 (Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify)
32
33 VALUES
34
35 (@Name, @StreetAddress, @City, @State, @ZipCode, @PhoneNumber, @DateOfBirth, @EmailAddress, @Gender, @Notify)
36
37 --SET
38 --Active = 1
39
40 END
41 GO
 

View 8 Replies View Related

Help With Insert Stored Procedure

Nov 17, 2007

I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below
ALTER PROCEDURE [dbo].[AddNewFundAllocation] @Payment_ID Int,@Receipt_ID Int,@Fund_ID Int,@Amount_allocated money,@DateEntered datetime,@EnteredBy nvarchar(50)ASSELECT     (SUM(tblReceiptsFunds.Amount_allocated) + @Amount_allocated) AS total_allocations, Sum(tblReceipts.AmountPaid) as total_paymentsFROM         tblReceiptsFunds INNER JOIN                      tblReceipts ON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@Payment_IDIF (total_allocations<total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)

Values (@Receipt_ID,@Fund_ID,@Amount_allocated,@DateEntered,@EnteredBy)

ELSE BEGIN
PRINT 'You are attempting to allocate more to funds than your total payment.'
END I get the following error when I try and save the stored procedure:
Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26
Invalid column name 'total_allocations'.
Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26
Invalid column name 'total_payments'.

View 6 Replies View Related







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