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 that
search to insert another record in table Y and then exec another stored
procedure and use the outcome of that stored procedure to update the
record in table Y.

I have this stored procedure (stA)

CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)
AS
SET NOCOUNT ON

SELECT OType, Status, SSN, FName, LName
FROM Customers
WHERE (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 it
is:

CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)
AS
SET NOCOUNT ON

-- select the record
SELECT OrderID, OrderDate, SSN
FROM Orders
GROUP BY OrderID, OrderDate, SSN
HAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)
GO.

After running this, I need to update the record I created a moment ago
in table Y with the OrderDate and OrderID from the second stored
procedure.


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 insert
statement for the new record, and then placing something like exec
procstB 'SSN value'? to run stored procedure B and then having a
update statement to update that new record?

Thanks for all your help.

View 1 Replies


ADVERTISEMENT

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

Comparing Values And Inserting A Record

Apr 25, 2007

SELECTIndustry,
100.0 * SUM(CASE when ceoischairman = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [YesPercent],
100.0 * SUM(CASE when ceoischairman = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [NoPercent]
FROMTCompanies
GROUP BYIndustry
ORDER BYIndustry

This code above is working as I need it but I need to insert some additional functionality. Thanks

I need to add something like this:

IF YesPercent > NoPercent
UPDATE tableX SET CEOIsChairman='Yes' WHERE Industry='<the industry value being evaluated>'
Else If NoPercent > YesPercent
UPDATE tableX SET CEOIsChairman='No' WHERE Industry='<the industry value being evaluated>'
Else
UPDATE tableX SET CEOIsChairman='Equal' WHERE Industry='<the industry value being evaluated>'
End

View 1 Replies View Related

Problem In Inserting A Record Whose Values Are Of Date And Time Format.

Jan 11, 2007

hello,

I am trying to insert date and time into my table.

insert into <table_name> values('12/12/2006','12:23:04');

but it displays error at " ; "

can anyone help me to figure out the problem



Thanks a lot in advance.

Regards,

Sweety

View 3 Replies View Related

About Inserting Some Parameters To A Stored Procedure.

Feb 4, 2007

Hello everyone,
I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance
Errors
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType' 
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand' 
 protected void Button1_Click(object sender, EventArgs e)
{
/* These two variables get the values of the textbox (i.e user input) and assign two local
* variables, This is also a good strategy against any Sql Injection Attacks.
*
*/
string Interview1 = TextBox1.Text;
string Interview2 = TextBox2.Text;

string Interview3 = TextBox3.Text;
string ProdMentioned = TextBox4.Text;

string ProdSeen = TextBox5.Text;
string Summary = TextBox6.Text;

string Compere = TextBox7.Text;
string Duration = TextBox8.Text;


//Create Sql connection variable that call the connection string
SqlConnection SqlConnection = new SqlConnection(GetConnectionString());

//Create a sql command to excute SQL statement against SQL server
SqlCommand Command = new SqlCommand();

// Set the command type as one that calls a Stored Procedure.
Command.InsertCommandType = CommandType.StoredProcedure;

//Call the stored procedure so we can pass it on the user input to retrieve user details
Command.InsertCommand = "Summaries";

//open the command connection with the connection string
Command.Connection = SqlConnection;

// Pass the user input to the Stored Procedure to check if user exists in our system.
Command.InsertParameters.Add("interview1", interview1);
Command.InsertParameters.Add("interview2", interview2);
Command.InsertParameters.Add("interview3", interview3);

Command.InsertParameters.Add("ProdMentioned", ProdMentioned);
Command.InsertParameters.Add("ProdSeen", ProdSeen);

Command.InsertParameters.Add("Compere", Compere);
Command.InsertParameters.Add("Duration", Duration);



int rowsAffected = 0;

try
{
rowsAffected = Command.Insert();

}
catch (Exception ex)
{
Resonse.Redirect("InsertSuccessfull.aspx");

}



// open the connection with the command
//Command.Connection.Open();

}

private static string GetConnectionString()
{

return ConfigurationManager.ConnectionStrings["BroadcastTestConnectionString1"].ConnectionString;

}
}  
 

View 1 Replies View Related

Passing Xml To Stored Procedure For Inserting

Feb 16, 2007

Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?

View 1 Replies View Related

Inserting Into Tables Using Stored Procedure

Dec 7, 2007

I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
 
 

View 4 Replies View Related

Inserting Records Via Stored Procedure

Mar 23, 2006

I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
 
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
 
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
 
Please help, I am a newb and lost...as you can tell from my code...

View 4 Replies View Related

Inserting BLOB Using Stored Procedure

Sep 14, 2007

Hi,

Can we insert a blob in the database(eg: doc, jpeg, pdf, etc) from the sqlcmd prompt. I want to insert few files into my table having varbinary(max) column and i dont want to use any front end tool for making such insertions. What i am thinking of is providing a file system path for a particular file(eg: doc, jpeg, pdf, etc) to a stored procedure so that it can be inserted into the database, something that we can do via Oracle's sqlldr tool.

Regards
Salil

View 1 Replies View Related

Inserting Simultaneous Tables Using Stored Procedure

Feb 8, 2007

I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReasonâ€? and “@WIPâ€?.â€? I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.

I made up the two tables just for testing, they are:
tbltest


ECID – int (PK)

View 2 Replies View Related

Can Anybody Tell Me Why The Following Stored Procedure Is Not Inserting Into My Database Table?....

Sep 7, 2007

ALTER PROCEDURE AddListAndReturnNewIDValue (
@EditorId int,@CategoryID int,
@ListTitle nvarchar(50),@Blurb nvarchar(250),
@FileName nvarchar(50),@ByLine nvarchar(50),
@HTMLCopy nvarchar(MAX),@MainStory bit,
@MainStoryImageFile nvarchar(50),@Publish bit,
@PublishDate smalldatetime,
@ListId int OUTPUT
)
AS
-- Insert the record into the database
INSERT INTO shortlist (EditorId,CategoryID,ListTitle,Blurb,FileName,ByLine,HTMLCopy,MainStory,MainStoryImageFile,Publish,PublishDate)
VALUES (@EditorID,@CategoryID,@ListTitle,@Blurb, @FileName, @ByLine, @HTMLCopy, @MainStory, @MainStoryImageFile,@Publish,@PublishDate)
-- Read the just-inserted ProductID into @NewProductID
SET @ListId = SCOPE_IDENTITY()
 
here is the sqlDataSource
<asp:SqlDataSource
id="srcShortList"
ConnectionString="<%$ ConnectionStrings:ShortList %>"
SelectCommand="SELECT Id,EditorId,CategoryID,ListTitle,Blurb,FileName, ByLine, HTMLCopy, MainStory, MainStoryImageFile, Publish,PublishDate,Date,Deleted FROM shortlist"
InsertCommand="AddProductAndReturnNewProductIDValue"SelectCommandType="StoredProcedure"
UpdateCommand="UPDATE shortlist SET CategoryID=@CategoryID,ListTitle=@ListTitle,Blurb=@Blurb,ByLine=@ByLine,HTMLCopy=@HTMLCopy,MainStory=@MainStory,Publish=@Publish,PublishDate=@PublishDate WHERE Id=@Id"
Runat="server" >
<SelectParameters>
<asp:QueryStringParameter
Name="Id"
QueryStringField="Id" />
</SelectParameters>
 
</asp:SqlDataSource>

View 2 Replies View Related

Regarding Stored Procedure For Selecting A Value From One Table And Inserting It To Another

Sep 19, 2007

Hi iam Prameela,
I want to select some dynamic values from a table and store them to another table.
Let me give u an example,its like:
I have UID,QID,Option1,Option2,Survey Name in one table called Survey Answers and i must select these values and insert them into Surevy Count table which contains some fields as QID,Opt1Cnt,Opt2Cnt,Survey Name. this is an online survey and when ever an user participate in the survey then values will be changed in Survey Answers like:
Surevy Answers Table:
UID   QID        Option1             Option2          Survey Name---------These are the fields
1         1             1                       0                    Articles
1          2            0                       1                    Articles
2           1            1                        0                      Articles
2           2             0                       1                       articles
I need to add all these Options of particular QID and store them in Survey Count table,like
QID          Opt1Cnt          Opt2Cnt          Survey Name
1                 2                     0                     Articles
2                  0                      2                   Articles
When ever the user participate in survey then there will be change in Survey answers table i.e the option count will be increased
So this count should be modified in Survey Count Table,like:
If another user participated in survey and if he voted for Option1 of QID1,Option1 of QID2 then the survey count table should be modified as:
QID           Opt1Cnt                Opt2Cnt             Survey Name
1                   3                         0                            Articles
2                   1                          2                             Articles
I need a Stored Procedure for this.
Please help me with this query.
 

View 7 Replies View Related

Question About Inserting A Row Before The First Row Of The Table ----Stored Procedure

Jan 31, 2008

Hi, I have a stored procedure. In the stored procedure, I have a table called "#temp", this table contains 50 rows that I select from other tables. Now I want to insert a row before the first row. How can I do it in SQL Server 2005? Thanks in advance.

View 2 Replies View Related

Inserting Request.Querystring Into The Stored Procedure

Feb 2, 2008

Hi i am trying to insert the value of my Request.Querystring into my stored procedure, but i am having trouble with it, how would i insert the id as a parameter which is expected from the stored procedure this is what i have doen so far;string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand comm = new SqlCommand("stream_PersonnelDetails", conn);comm.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
 
Thank you

View 2 Replies View Related

Inserting 1:M Relationship Data Via One Stored Procedure

Sep 2, 2006

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

View 3 Replies View Related

Stored Procedure Format Incorrect - Inserting To Two Tables

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 1 Replies View Related

Stored Procedure Inserting Duplicate Records Randomly

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

Restrict Inserting Record If Record Already Exist In Table

Apr 17, 2014

Is that possible to restrict inserting the record if record already exist in the table.

Scenario: query should be

We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.

View 2 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

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

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

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

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 9 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 12 Replies View Related

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 Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure
Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.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 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

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

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







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