Need To Find Where In Db Record Is Stored
Feb 8, 2005
I have a mystery database on my hands...it is part of an application that my company bought, and it uses SQL server for its backend. The reporting features built in are not good enough, so I need to write some queries by hand...trouble is I am having a hard time figuring out how the schema works...using the front end they gave us I put a value of "12345" for a field I need to get to, but I can not locate where in the db it gets stored....can anyone tell me a way to query that will look at every single record and every single field in the db to find the value "12345"??
View 14 Replies
ADVERTISEMENT
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
Nov 27, 2006
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies
View Related
Mar 24, 2007
I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.[CODE] public void UpdateMaleHistLbl() { SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True"); SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn); comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { string usr = reader["username"].ToString(); usr = usr.TrimEnd(); string pss = reader["password"].ToString(); pss = pss.TrimEnd(); if (usrnmeLbl.Text == usr) { if (hiddenpassLbl.Text == pss) { maleHistLbl.Text = reader["GG_Grandfather"] + " > "; maleHistLbl.Text += reader["G_Grandfather"] + " > "; maleHistLbl.Text += reader["Grandfather"] + " > "; maleHistLbl.Text += reader["Father"] + " > "; maleHistLbl.Text += reader["Son"] + " > "; maleHistLbl.Text += reader["Grandson"] + " > "; maleHistLbl.Text += reader["G_Grandson"] + " > "; maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />"; } } break; //exit out of the loop since user found } reader.Close(); conn.Close(); }}[/CODE]Thanks in advance
View 1 Replies
View Related
Mar 22, 2007
Code:
"SELECT " & _
"Asset_Number, Inv_ID " & _
"FROM " & _
"tbl_Asset "
OKay looking at this highly simplified query here is my problem.
We have a ton of Asset numbers that are the same and for each asset number the inv_id should also be the same.....
However, since this data gets entered by humans - sometimes the inv_id is wrong....
I need to figure out a query that fleshes out the rows where asset_nums are the same but inv_id is different than other rows...?
Of course this may be impossible.....
I mean what if there was only two records by the same asset_num and each inv_id was different?
How could the query know which was the right one......
I need to run a report that finds the invalid inv_id's so that they can corrected.....
any thoughts on how this is done?
My thoughts are - using an inner select...
something to the effect of :
SELECT ....
FROM
(SELECT * WHERE (inv_id = inv_id) > 1)
GROUP BY asset_num.........
I just dont know exactly what the correct syntax is....
muchas gracias....
(btw this is actually ms-sql but mysql forum is so much more popular and the syntaxes are similar enough....thank you...)
View 2 Replies
View Related
Apr 14, 2008
sno Empid Address Date
------------------------------------------------
1100bhopal2008-01-02 00:00:00.000
2100indore2008-02-02 00:00:00.000
3100gwalior2008-03-03 00:00:00.000
4101gwalior2008-01-02 00:00:00.000
5101Indore2008-02-02 00:00:00.000
6102bhopal2008-01-01 00:00:00.000
------------------------------------------------
I want last update Address of employee from this table like
100 Gwalior
101 Indore
102 Bhopal
pls help me out ...............
Yaman
View 2 Replies
View Related
Mar 20, 2007
hi all,
if we have 100 same records, how can we select any 50 of it? thanks
~~~Focus on problem, not solution~~~
View 20 Replies
View Related
Aug 14, 2001
Is there any method so that I can find that the particular user has changed the record in the particualr table..?
View 1 Replies
View Related
Oct 15, 2013
I have a table called Register that has the following in it
Policy_number, Policy_date_time, Portfolio_set, Status..The rule for the table is that the last record for each portfolio_set for a policy the status needs to be 'A' but there have been instances that the last record status is 'I'
I need to identify the record that is out of place..In the example below record number 2.
example
policy_number Policy_date_time Portfolio_set, status
12345 1/1/2011 1 I
12345 1/2/2011 1 A
12345 1/3/2011 1 I
12345 1/4/2011 1 I
I need to identify that the second record is in the wrong place...
View 4 Replies
View Related
Sep 23, 2007
Hello
I need an alarm or raise an event from SQL Server after updating to get it in .net.
I use SQL Server 2005 Express Edition and vb.net2 for my programming language.
Suppose that in a windows form I have a grid and I'm working with this page
another client is working with this same page .He is editing the information of
a record of a grid and save it in database so I want to give an alarm or an event
that raise from SQL Server (for example in a trigger) , what can I do?
Thanks.
View 3 Replies
View Related
Jul 23, 2005
Is there a way to tell who or what process deleted a record in a SQL table.I know you can setup triggers or Profiler - but is there a way to see how arecord was deleted if you DO NOT have a trigger or Profiler already running?This is SQL 2000 Standard, sp3a with default options and configuration.Thanks
View 2 Replies
View Related
Dec 11, 2007
I have a select statment that gives me an error, cannot go more than rowsize 8094.
I am trying to find the particualr record/acctno, where the rowsize is excedding the max limi of 8094 chars. Once I have the acctno, I will correct it.
But how do I find which acct has this issue? Bascailly how can I find which record size is more than 8094 chars?
View 1 Replies
View Related
Oct 29, 2014
We have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.
The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).
select id, worknumber, date, notes from worksordernotes
id worknumber date
----------- ------------ ----------------------- --------------------
1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK
2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE
4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG
5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES
6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS
7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS
View 3 Replies
View Related
Apr 2, 2015
I need to find all items in an inventory table where a field has been unticked in the last 24 hours (there is no audit trail), as well as the contract number of the contract it has been added to (it will not have existed in that table before).
These are the two table querys in their basic form:
select item (nvarchar(20)), inactive (bit) from inventory
select item (nvarchar(20) , contract (nvarchar(20)) , original_start_rent (datetime) from deltickitem
I would like to see just the item number and the contract number it has been added to.
View 11 Replies
View Related
Oct 12, 2007
I have a table with member information. Each member has multiple effective dates. I would like to query to find the record for each member that has the maximum effective date. A sample table is below:
MEMBERID EFECTIVEDATE
------------ ------------------
699361401 20070101
699361401 20070501
732612701 20070101
732612701 20070501
575424301 20070101
575424301 20070501
192939801 20070101
192939801 20070501
458645001 20070101
458645001 20070501
View 4 Replies
View Related
Sep 18, 2015
I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.
I know that i can build this with a correlated subquery, but i´am curious about using CTE.
I post sample create Script:
create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union
[code]....
View 5 Replies
View Related
Jul 15, 2015
I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this
Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.
And this query produces wrong results, its giving me match and also non match records.
how to write a query for my non match condition?
View 5 Replies
View Related
Oct 1, 2007
Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
View 16 Replies
View Related
Oct 13, 2012
Table :
ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal
Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.
SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date
The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.
View 1 Replies
View Related
Sep 19, 2015
The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.
Code:
SELECT
tblinmate.statusid,
tblinmate.activedate,
Last(tblclassificationhistory.classificationid) AS LastOfclassificationID,
Last(tblsquadhistory.squadid) AS LastOfsquadID,
tblperson.firstname,
tblperson.middlename,
tblperson.lastname,
[Code] ....
The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function
Code:
SELECT a.inmateID,
a.classificationID,
b.max_date
FROM (
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,
[Code] .....
View 1 Replies
View Related
Mar 3, 2015
I have the following tables:
--acts as a transaction table
CREATE TABLE #TestData (
id int not null identity(1,1) primary key,
account varchar(10) not null,
deposit int not null
[Code] ....
--desired results
-- within each account group, when a individual record causes the groups running total to exceed the group's budget, show the difference that causes the groups running total to exceed the budget as unbudgeted. The amount of that transaction upto the budget amount show as renewal. For any succeeding individual records in the group, the amount of that transaction is Unbudgeted.
Here's the SQL I have thus far...
SELECT a.id
,a.account
,a.deposit
,a.total
,a.budget
,case when a.total <= a.budget
[Code] ....
View 4 Replies
View Related
Feb 23, 2015
I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.
I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:
FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123
From this table, I would like a query that would give me this result:
01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123
There will likely be more than just 2 consecutive records per contract number.
View 4 Replies
View Related
Nov 6, 2015
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
View 6 Replies
View Related
Apr 2, 2015
So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
[Code] ....
View 4 Replies
View Related
Mar 11, 2005
I developed a ASP.net web application with a MSDE database backend on my laptop(vs.net 2003 XP Pro), then I transferred the website onto a server(Windows Server 2003) and generated a SQL Server 2000 database from the scripts I exported from MSDE(web administrator). The problem I am having is that it can't find any stored procedures. I keep getting errors when logging on, 'Could not find stored procedure "_myProc" '.
Any one with any clues what might be the problem?
Yes I have changed the connection strings.
Thanks in advance
P
View 2 Replies
View Related
Jan 5, 2006
I use the following code in ASP.NET 2.0 to update the database:
Dim myConnection As New SqlClient.SqlConnection("server=local);uid=sa;pwd=xxx;database=Northwind")Dim myCommand As New SqlClient.SqlCommand("dbo.spTralen_customer_save 'CACTU'", myConnection)myCommand.CommandType = CommandType.StoredProceduremyConnection.Open()myCommand.ExecuteReader(CommandBehavior.CloseConnection)
I get the following error message: "Could not find stored procedure..."
The sp is in the database and dbo is the owner of the sp and I'm logged in as sa as you can see above. It doesn't matter if I remove the "dbo." from the sql command, it still doesn't work. If I remove the parameter value 'CACTU' above I get an error message saying that the sp expects the parameter so the sp is obviously in the database.
Can someone please help me as soon as possible!// Gato Gris
View 2 Replies
View Related
Apr 29, 2008
Not sure if this question belongs here or in a .NET forum.
But Im going to give it a shoot. The problem is that Im getting the following error: "Could not find stored procedure 'xxx'".
Ive never used stored procedures before, so what I am wondering is there anything basic that Im forgetting?
I have this simple stored Procedure:
ALTER PROCEDURE Person_info
@FirstN varchar(128),
@LastN varchar(128)
AS
SELECT FName, LName
FROM Person
WHERE (FName = @FirstN) AND (LName = @LastN)
and each time I call this procedure I get the prior stated error.
returnValue = sqlcmd.ExecuteReader(); //crashes when this line executes.
Ive found some people talking about this and it might be caused due to the "initial catalog=<database name>" in the connection string is missing. I tried that but didnt work.
View 18 Replies
View Related
Sep 13, 2005
Hello,Our SQL machine is getting bogged down by some sort of stored procedureand I am trying to find which one. My SQLdiagnostic software (by Idera)that monitors our SQL server, says that these commands are executingand taking upwards of 30 minutes to run. This is new and unexpected.The commands are:exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =25, @Pm2 = 2, @Pm3 = 1exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =105, @Pm2 = 2, @Pm3 = 1exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =57, @Pm2 = 2, @Pm3 = 1I am getting pages of these and yesterday the are taking upto 30minutes to run (currently they are taking 1-2 minutes to complete w/opeople on the machine).We are not getting much help from our software vendor (of ouradmissions software, not Idera) on this matter. I have sa access to theSQL machine and I can see the pages and pages of stored procedures, butI don't know what the above is running. I want to find the storedprocedure that keeps getting executed. Is the @Pm0 = an encryptedentry?Any advice I would appreciate.ThanksRob Camarda
View 2 Replies
View Related
Jul 20, 2005
I have an Access 2000 database connected to a SQL Server and am tryingto execute my first stored procedure. I created the stored procedureand verified that it works, but when I try to execute it from Access:cnn.Execute("sp_IPT")it says: 'Could not find stored procedure 'sp_IPT'Any ideas?Norman B. ScheininF-22 Applications DevelopmentM/S 4E-09(206) 655-7236Join Bytes!
View 1 Replies
View Related
Aug 22, 2007
I have this error (Merge Replication):
The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.
How can I solve this problem?
Thanks.
View 11 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
