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 ON
INSERT 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
ADVERTISEMENT
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
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
Nov 14, 2007
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items
View 2 Replies
View Related
Jul 5, 2007
1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
2 Dim sqlStr As String
3 Dim sqlStr2 As String
4 Dim myConnection As MySqlConnection = Nothing
5 Dim myCommand As MySqlCommand = Nothing
6 Dim myConnection2 As MySqlConnection = Nothing
7 Dim myCommand2 As MySqlCommand = Nothing
8 Dim myReader As MySqlDataReader = Nothing
9 Dim IC As String
10
11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text
12
13
14 Try
15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'"
16
17 ' Connection
18 myConnection2 = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
19 myConnection2.Open()
20 ' Command
21 myCommand2 = New MySqlCommand(sqlStr2, myConnection2)
22 ' Reader
23 myReader = myCommand2.ExecuteReader()
24 Catch ex As Exception
25 ' Exception Error Here
26 Response.Write(Err.Number & " - " & Err.Description)
27
28 End Try
29 ' Checking
30 If myReader.Read() Then
31
32
33 Label2.Text = "Username already exist. Please choose another username"
34 Label3.Text = "*"
35
36 Else
37
38 Try
39
40
41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )"
42
43
44
45 ' Connection
46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
47 myConnection.Open()
48 'Command
49 myCommand = New MySqlCommand(sqlStr, myConnection)
50
51 myCommand.Parameters.AddWithValue("?uName", txtName.Text)
52 myCommand.Parameters.AddWithValue("?uIC", IC)
53
54
55 myCommand.ExecuteNonQuery()
56 myConnection.Close()
57 Response.Redirect("Register.aspx", False)
58
59 Catch ex As Exception
60 ' Exception Error Here
61 Response.Write(Err.Number & " - " & Err.Description)
62 Finally
63 ' Clean Up
64 If Not IsNothing(myCommand) Then
65 myCommand.Dispose()
66 End If
67 '
68 If Not IsNothing(myConnection) Then
69 If myConnection.State = Data.ConnectionState.Open Then myConnection.Close()
70 myConnection.Dispose()
71 End If
72 End Try
73
74
75 End If
76
77 End Sub
78
79
above is my code for the user registration page.the code that i bold,which with number 55,56 and 57,are where the problem occur.
when it run,it run 55, then 57,then back to 55, then 57 again
means that my db hav duplicate record being insert
anyone know how to solve this problem?
View 2 Replies
View Related
Jun 5, 2006
is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies
View Related
Feb 14, 2008
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure.
for example:
alter procedure [dbo].[AddDetails]
(
@IndustryName nvarchar(50),
@CompanyName nvarchar(50),
@PlantName nvarchar(50),
@Address nvarchar(100),
@Createdby int,
@CreatedOn datetime
)
as
begin
insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn)
insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn)
insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp)
end
Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this.
Please help me, i need the solution soon.
View 11 Replies
View Related
Jul 11, 2006
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
View 3 Replies
View Related
Sep 8, 2015
I am run a stored procedure using Execute SQL task in, I want to log information of number of record inserted update in my table. I want to enable SSIS logging after from where I get information number of record inserted update.
I am using SQL server 2008R2 standard edition.
View 4 Replies
View Related
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Apr 17, 2008
Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance
Pmillio Jones
Pmillio Jones
Pmillio Jones
Robert Walsh
Here is my stored procedure;
ALTER PROCEDURE [dbo].[stream_UserFind]
-- Add the parameters for the stored procedure here
@userName varchar(100),
@subCategoryID INT,@regionID INT
AS
SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON
Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR
SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);
View 11 Replies
View Related
Feb 11, 2004
Hi,
I have written the following stored procedure:
alter proc GetProducts
@prodcatint=null
as
select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name]
,p.designer,p.weight,p.price
from productdescription pd inner join products p on pd.productcategory=p.productcategory
where @prodcat=p.productcategory
order by p.productid
return
My Results are:
ProductCategory ProductID (Rest of the columns)
22 47
22 47
22 58
22 58
In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.
Does anybody have any suggestions.
Thank you in advance,
poc1010
View 4 Replies
View Related
Oct 22, 2007
hi every body
can any onehelp me in making a stored procedure to delete a duplicate rows in tables
thanks in advance
View 9 Replies
View Related
Feb 1, 2005
I have a web app that calculates tax filing status and then stores data about the person.
Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.
Any help would be greatly appreciated.
There are many sqlcmdInsert.Parameters("@item").Value =
cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()
And that is it.
View 6 Replies
View Related
Jul 20, 2005
This is like the bug from hell. It is kind of hard to explain, soplease bear with me.Background Info: SQL Server 7.0, on an NT box, Active Server pageswith Javascript, using ADO objects.I'm inserting simple records into a table. But one insert command isplacing 2 or 3 records into the table. The 'extra' records, have thesame data as the previous insert incident, (except for the timestamp).Here is an example. Follow the values of the 'Search String' field:I inserted one record at a time, in the following order (And only oneinsert per item):airplanejetdogcatmousetigerAfter this, I should have had 6 records in the table. But, I endedup with 11!Here is what was recorded in the database:Vid DateTime Type ProductName SearchString NumResultscgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64Look at the timestamps, and notice which ones are the same.I did one insert for 'dog' , but notice how 2 'jet' records wereinsertedat the same time. Then, when I inserted the 'cat' record, another'dog' record was inserted. I waited awhile, and inserted mouse, andonly the mouse was inserted. But soon after, I inserted 'tiger', and 2more mouse records were inserted.If I wait awhile between inserts, then no extra records are inserted.( Notice 'airplane', and the first 'mouse' entries. ) But if I insertrecords right after one another, then the second record insertion alsoinserts a record with data from the 1st insertion.Here is the complete function, in Javascript (The main code ofinterestmay start at the Query = "INSERT ... statement):----------------------------------------------------------------------//Write SearchTrack Record ------------------------------------Search.prototype.writeSearchTrackRec = function(){Response.Write ("<br>Calling function writeSearchTrack "); // fordebugvar Query;var vid;var type = "i"; // Type is imagevar Q = "', '";var datetime = "GETDATE()";//Get the Vid// First - try to get from the outVid var of Cookieinctry{vid = outVid;}catch(e){vid = Request.Cookies("CGIVid"); // Gets cookie id valuevid = ""+vid;if (vid == 'undefined' || vid == ""){vid = "ImageSearchNoVid";}}try{Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,NumResults) ";Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',"+datetime+","+this.numResults+ ")";this.cmd.CommandText = Query;this.cmd.Execute();}catch(e){writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"- SearchString:: "+this.searchString+" - NumResults: "+this.numResults, e.description);}}//end-----------------------------------------------------------------I also wrote a non-object oriented function, and created the commandobject inside the function. But I had the same results.I know that the function is not getting called multiple timesbecause I print out a message each time it is called.This really stumps me. I'll really appreciate any help you canoffer.Thanks,George
View 2 Replies
View Related
Dec 22, 2007
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation.
I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work.
How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#.
As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
View 10 Replies
View Related
Jul 20, 2005
Hello,I would like do an insert into a table. The table has an autoincrimenting unique int id. After I do the insert how do i get theunique int id of the record that I just inserted? Is there a straightforward way of accomplishing this?Thanks,Billy
View 6 Replies
View Related
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
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
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
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
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
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
Oct 1, 2007
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement INSERTINTO products ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo FROM productsWHERE product_ID = @productID
View 2 Replies
View Related
Jan 18, 2005
I am using VS.net (2003) SQLcommand TEXT. with input params.
SQL server 2000
Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.
My code so far that works... but Where do I put @@IDENTITY ?
How do I call or assign the @@IDENTITY value to a value in my aspx.vb code page?
Question: how do I get the Identity value from the ID column.
Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help...
---------------------
INSERT INTO App
(AppName, Acronym, Description,bla bla bla bla........)
VALUES (@AppName, @Acronym, @Description, bla bla bla bla........)
-----------------------------
Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click
With cmdAddApp
'.Parameters("@app_id").Value = Session("App_ID")
.Parameters("@AppName").Value = txtAppName.Text
.Parameters( bla bla bla.............
.Parameters( bla bla bla.............
.Parameters( bla bla bla.............
End With
Try
cnAppKBdata.Open()
cmdAddApp.ExecuteNonQuery()
cnAppKBdata.Close()
''''''''''''''Session("App_ID") = whatever the @@IDENTITY is....'''''''''''''''''''''''''''??
Response.Redirect("AppUpdate.asp")
Catch ex As Exception
End Try
End Sub
Anyone have the lines of code that does this?
Any advise or examples :) thanks you.
View 1 Replies
View Related
Feb 21, 2006
I have a table named invoice that contains the following columns-invoiceno - Primary key and is set to autonumber-customerno-incoicedateand on my VB code i did the following InsertCommandSqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "SqlDataSource1.Insert()My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library
View 1 Replies
View Related
Apr 23, 2006
I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me.
Here is my VB Code
Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@UseriD, @Company)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_useriD.ParameterName = "@UseriD" dbParam_useriD.Value = useriD dbParam_useriD.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_useriD) Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_company.ParameterName = "@Company" dbParam_company.Value = company dbParam_company.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_company)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
Return rowsAffected End Function
View 4 Replies
View Related
Mar 5, 2001
Is it possible to retrieve the user name or host name of the user that inserted or updated a particular record? Is this information stored in the database's log file, in hidden fields, or anywhere else? I know I can easily add a user_name field and use triggers to add this information for new records, but I need this information for records already existing in the table. Thanks.
Jason
View 5 Replies
View Related
Dec 1, 2005
Hi All,
i have a database, and every time when any row enter , it capture the date and time of insertation,
then does any function or query available, which help to investigate me, today inserted record,yesterday inserted record,last 7 days inserted record,last 30 days inserted record and last year inserted record..
i am using asp.net and MS Access...
Any idea
thanx in advance...
sajjad
View 2 Replies
View Related
Feb 9, 2007
hi,i need to select last inserted record in my table,can any one show some example query for that please
View 6 Replies
View Related
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
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
Jul 29, 2006
There are loads of postings on the net about this problem but none I have found explain the cause.
Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.
Has anyone come across the solution for this issue?
Thanks
View 6 Replies
View Related