Trouble With An ASync Query To Insert A Record And Return The Auto Number Field.

Aug 31, 2007

I get this error when I look at the state of my SQLresults object. Have I coded something wrong?

Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
  conn.Open()
Dim strSql As String

strSql = "INSERT INTO contacts (companyId, sourceId, firstName, lastName, middleName, birthday, dateCreated)" _
& "VALUES ('" & companyId & "', '" & sourceId & "', '" & firstName & "', '" & lastName & "', '" & middleName & "', '" & birthday & "', '" & now & "') SELECT @@IDENTITY AS 'contactId'"

Dim objCmd As SqlCommand
objCmd = New SqlCommand(strSql, conn)

Dim aSyncResult As IAsyncResult = objCmd.BeginExecuteReader()

If aSyncResult.AsyncWaitHandle.WaitOne() = True Then


Dim sqlResults As SqlClient.SqlDataReader

sqlResults = objCmd.EndExecuteReader(aSyncResult)

Dim cid As Integer



cid = sqlResults.Item("contactId")
Me.id = cid
conn.Close()
Return cid
Else
Return "failed"


End If  

View 3 Replies


ADVERTISEMENT

In Trouble With Async Replication..

Nov 21, 2006

Hello Everybody,

In the past few days I try to work with SQL Mobil and Replication.

And now I have a big problem.

When the replication ist running in my little application the pda-user wants not stop there working. So I try to implement some routines of code that I find in this onlinearticle: http://msdn2.microsoft.com/en-us/library/2ysxae29.aspx

The codes workes fine as long I don't start another SQL Task. When I start either (Select, Insert, Update or Delete) Statement the Database crash with errormessage: "The database file may be corrupted. Run the repair utility...



Can anyone give me a tip what I must do to fix this problem.



Many Thanks

Markus



View 1 Replies View Related

Creating A Record Number That Auto Generates In SSIS

Jan 8, 2008

Can anyone help me figure out how to set up a new table using SSIS with a record field that will generate a unique number? The record number is also the primary key in the table. I did this about a year ago with a bit of help, but once you get the language right and initiate the creation, the text for defining the table is no longer available. I have seen a lot of references for MySQL and MS SQL 2000 and an AUTO_Generate or a UNIQUE ID. None of this works within MS SQL 2005 in SSIS.

Thanks!
Jim

View 8 Replies View Related

Auto Insert A Variable Number Of Records?

Sep 20, 2006

I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC  combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam

View 3 Replies View Related

Auto Number Filed Insert Problem

Jun 7, 2006

i am trying to insert an auot number field in my table which has got about million rows but sql 2005 is giving me na error "cant insert"
i need to index my table so that the query runs faster when i perform joins on two of such huge tables..
i tryid inserting the identity key the way it was mentioned in the forum but sql doesnt let me do that??

pls help

View 9 Replies View Related

Code That Return Correct Number Of Record When Run Without Aggregate Function

Nov 26, 2013

I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items

Group 1

Group 2

Group 3

The other group sum up correctly while others don't. What can explain this? Here is the code.

Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey

[code]....

View 3 Replies View Related

Mirroring :: Async Mirror Setup For High Number Of Databases

Oct 27, 2015

I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances.

What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.

What all needs to be checked for looking into the feasibility of going ahead with a async mirror setup as mentioned above.

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

Attempt To Return Record Set In INSERT...EXEC Statement From ODBC Source(non MSSQL)

Jan 17, 2007

Greeting.

I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.

I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.

Have this problem solution? May be, I must use another method to get my data?



P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.

P.S.S. Sorry for bad english.









View 1 Replies View Related

Query Help: Need To Return 2nd From Top Record

Jul 20, 2005

i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/

View 6 Replies View Related

Query To Return Record From Hierarchy?

Oct 21, 2013

I am wanting to run a SQL statement whereby i return the ID of any employee's Director.

The database for employees has a reports to field which enables me to see the hierarchy of managers above any employee.

There is also a IsDirector flag that indicates a director.

So essentially i want to run sql that would return the first instance of a director in the hierarchy above any employee.

eg if A reports to B and B reports to C (who is a director) then it returns C.

I basically want the script to run until a director is found.

how would i do this?

View 5 Replies View Related

Insert Number With Comma Into SQL Database 'int' Field

Feb 8, 2006

How can I allow users to input numbers with commas into a database field with an 'int' datatype without getting this error, 'Input string was not in a correct format'?

View 3 Replies View Related

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

Hi!

I have a table called DB1 that contains this:

MID
IIN
NUM_EVENTS
DATE

MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?

View 6 Replies View Related

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

Hi!

I have a table called DB1 that contains this:

MID
IIN
NUM_EVENTS
DATE

MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?



$3.99/yr .COM!
http://www.greatdomains4less.com

View 3 Replies View Related

Insert Previous Record In A Blank Or A Null Field

Nov 29, 2000

Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:

ONETWO
a1
2
3
b1
2
3

Would want:

ONETWO
a1
a2
a3
b1
b2
b3

View 5 Replies View Related

How Do You Write A Query To Return All Record With Parameters?

Jan 24, 2006

For example:

Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location

I know MS Access you can use a "*" to retrieve all records when there is a parameter. What does SQL Sever query use to retrieve all record?

View 14 Replies View Related

Return Last Valid Record When Query Is Outside The Range?

Dec 18, 2012

Using MSSQL 2008 R2

Given the following table

Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}

If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?

For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3

This is a query that I am trying to work on:

Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass

Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.

View 4 Replies View Related

Trying To Return Total Record Count With Query

Feb 26, 2007

I'm trying to return the total records with my query, but I'm getting the following error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Here's my query:


set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")

<%response.write rsfind("TotalCount")%>


Thanks for any help!
Dale

View 10 Replies View Related

Query Using The Record Number

May 7, 2004

happy friday...
my table has 200,000 + records and I want to see the particular record which I think it is causing the problem.
How could i query 195,203rd record?
thank you, yanoroo

View 8 Replies View Related

Transact SQL :: How To Return Number Of Records In Query As If TOP Was Not Used

Jul 21, 2015

What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100  and therefore the TSQL would look like

SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc

Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.

View 6 Replies View Related

Return Number Of Columns - Pivot Query Into Report Builder

Oct 31, 2014

I have a query which returns number of columns using pivot ( rows into columns -- dynamic sql pivot columns). Since it is dynamic pivot, how can I bind this returned values into report builder matrix reports.

Look at this example :

First time query returns
StudentId | Col1 | Col2 | Col3

Second time query returns
StudentId | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 ...

How to bind this query into report builder 3.0 reports?

View 13 Replies View Related

How To Return A Numeric Value Based Upon If A Record Is Returned From My Query/Stored Proc.

Oct 2, 2007

 
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT     Name.Active FROM       Name INNER JOIN               NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND               Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
 

View 3 Replies View Related

How To Write A Query To Return Null For Non-exist Record In An Outer Join.

Jun 2, 2004

I have two tables:

1. tblProducts
columns: ProductID, ProductName

2. tblProductCodes
columns: ProductID, CustomerID, ProductCode

The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.

I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.

I tried:

SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2

But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.

Any help is highly appreciated.

View 4 Replies View Related

Auto Increment Auto Non-identity Field

Jan 23, 2004

I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?

Thanks, Justin.

View 3 Replies View Related

SQL 2012 :: Query To Return Values From XML Field

Jun 5, 2014

Query to return values from XML field

create table #temp
(
id int identity (1,1)
,FieldSet XML
)

INSERT INTO #temp
VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')
,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')

SELECT * FROM #temp

Expected Output :

Id Column1Column2Column3Column4
1value1value2nullnull
2nullnullvalue3value4

View 4 Replies View Related

Conditional Return Of A Value From A Set Of Records As A Field Value In A Query

Dec 20, 2007

Hi,

I need to implement some thing like this.

I have a query like

SELECT table1.col1
,€™n/a€™ _response
FROM table1
INNER JOIN table2

This is the query that get the report data for my report. Now I need to replace the second column with an actual response like €˜accepted€™ and €˜rejected€™. And these values should be a result of evaluation of this form

Statusarray[] = ResponseStoredProcedure(table1.col1)

If(Statusarray.count < 1)
Set _response = €˜accepted€™
Else
Get Statusarray[1]
Compare this to Statusarray[0]
Set _response = some result based on comparision.

The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.

How can this be done in sql(using SQL Server 2005)

PLZZZ HELP!!!!!!!!!

Thanks,
Hari

View 2 Replies View Related

Query To See If An Int Field Starts With A Certain Number

Nov 3, 2006

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!

View 3 Replies View Related

Return A Value After Insert The Query

Oct 8, 2007

Hi!   create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value.          Dim objConn As SqlConnection        Dim SQLCmd As SqlClient.SqlCommand        Dim ds As New DataSet        Dim strsql As String        Try            objConn = New SqlConnection            objConn.ConnectionString = _                "Network Library=DBMSSOCN;" & _                "Data Source=localhost;" & _                "Initial Catalog=mydb;" & _                "User ID=userid;" & _                "Password=pass"            objConn.Open()            strsql = "insert into testReturn values ('a')"            SQLCmd = New SqlClient.SqlCommand(strsql, objConn)            Dim rowsAffected As Integer = 0            rowsAffected = SQLCmd.ExecuteNonQuery            Dim rv As String            rv = SQLCmd.Parameters(0).Value.ToString()            Response.Write(rv)                    Catch ex As Exception            Response.Write(ex.ToString)        End Try  

View 5 Replies View Related

Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table

Nov 1, 2007

I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
 
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' 
 
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return.  Same goes for the last_name field.   Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..

View 7 Replies View Related

Insert Query And Number Of Fields

Apr 18, 2008

I've found out how to to the Insert into my table (col1, col2) Select (col1, col2...) from othertable where regId= @regId in my earlier question but do i have to name every column as i have about 80 in my table. Can't I use an asterisk or something....

View 4 Replies View Related

Transact SQL :: Query Is Trying To Insert Duplicate Record?

May 18, 2015

My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.

-- Inserting new preferred supplier into R5CATALOGUE

DECLARE @DATEPROCESS DATETIME;
SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE)
INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG,
CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)

[code]....

View 5 Replies View Related

Insert Record In Table - Select Output Of Query

Oct 14, 2013

I had a query and i need to insert record in table and also want to select output of query.

Actually I need to insert and show data in same query.

;with cte as (
select id , status
from tbl_main
where id = 15555
)
insert into testinsert (id , status)
select * from cte

View 3 Replies View Related

Transact SQL :: Query To Insert Over 1000,0000 Record

May 8, 2015

How to write a query that can insert over 1000,0000 dummy records in the fastest way? My current query is

  DECLARE @d DateTIme = GETDATE()
  DECLARE @c INT = 1
WHILE @c <= 5 BEGIN
INSERT INTO MyTable VALUES (NEWID(),'PREFIX' + RIGHT('0000000000'+ (CAST(@c AS VARCHAR)), 10), DATEADD(MINUTE,@c,@d), FLOOR(RAND()*3), FLOOR(RAND()*2), 'INFO')
    SET @c = @c + 1
END

View 7 Replies View Related







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