Select SCOPE_IDENTITY() From TableAdapter

Oct 8, 2007

I like to run a query from my tableadapter, but if i insert a row i like to know the id of it.
So i was looking on asp.net and found a tutorial for it. But it doesnt work!

My inset query:

"INSERT INTO Klanten
                      (BedrijfsNaam, Contactpersoon, Adres, Postcode, Woonplaats, Telefoon, Mobiel, [E-mail], Website, Kvk, BTWnr)
VALUES     (?,?,?,?,?,?,?,?,?,?,?);

Select SCOPE_IDENTITY()"

As database i user MS SQL Express.

Also i tried it with @@Identity, but that works eighter.
If i try the insert code with the scope identity above, and i put it in the Query builder from MS Visual Studio, it returns an error.. :

"The following errors were encountered while parsing the contents of the SQL pane:

Unable to parse query text."

This error is only there if i add scope_idititiy or @@identity to it.
Also if i run it in the SQL part of SQL Studio Express i get the same error.

So should i change this?
Or how can i get the id of it ?

Of course i can read the database after the insert, but its not save enough i think.


 

View 4 Replies


ADVERTISEMENT

Problem With SELECT SCOPE_IDENTITY()

Nov 18, 2006

I am following the tutorial from here : http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63Using Visual Studio Express Edition, it seems like so many SQL statements are not compatible with that tutorial so I have to figure the difference myself.For example for this :  SELECT     ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued
FROM         Products
WHERE     CategoryID = @CategoryIDI have to use this : SELECT     ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued
FROM         Products
WHERE     (CategoryID = ?)As you can see the difference is the ? character.Now I have problem with SELECT SCOPE_IDENTITY(), when I use it like tutorial: INSERT INTO [Products]                      ([SupplierID], [ProductName], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])VALUES     (?, ?, ?, ?, ?, ?, ?, ?, ?);SELECT     SCOPE_IDENTITY() it gives me this error:   Unable to parse query text. I really need to use SCOPE_IDENTITY() to return the inserted record ID !    Do you know what is wrong or what changes I have to make ?Thanks in advance.     

View 11 Replies View Related

Using SELECT SCOPE_IDENTITY In An Ad-hoc Query

Jul 24, 2007

I've got an insert command in an SqlDataSource control that looks like this:
InsertCommand="INSERT INTO TAG2(TagText) VALUES (@TagText); SELECT SCOPE_IDENTITY()"Is this correct? How do I get at the value of the identity so I can use it in another insert command?

View 7 Replies View Related

Select Scope_identity Question

Feb 21, 2008

I am inserting some values to database and I have this code
 Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
Dim name, address, nice As String
Dim sConnString, sSQL As StringDim u As MembershipUser
' Dim aid As String
'Receiving values from Form, assign the values entered to variables
'name = Request.Form("name")
'address = Request.Form("address")name = Me.name.Textaddress = Me.address.Text
 
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
'comments = Request.Form("comments")
'declare SQL statement that will query the database sSQL = "INSERT into test ([Address],[Name], [username]) values ('" & _
address & "', '" & _name & "', '" & _
nice & "')"
'aid = "Select Scope_Identity()"
' MsgBox(aid)
MsgBox(sSQL)
'define the connection string, specify database
'driver and the location of databasesConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString()
'create an ADO connection object Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection)
MyConnection.Open()
MyCmd.ExecuteNonQuery()
MyConnection.Close()
End UsingResponse.Redirect("transferred.aspx?value=2")
End Sub
End Class
 
I want to get the last record id that was inserted using scope_identity. I am new to asp.net , vb.net and mssql so I was hoping if someone can guide me to the right path
 
Thanks

View 4 Replies View Related

SQL Login With TableAdapter

Jul 31, 2006

Ok, just made a TableAdapter and the SELECT statement works fine and pulls data. But doing an INSERT is something different, it craps out wanting a login.
No neat Wizard (or so I haven't found yet) that lets me give the TableAdapter the login info for the SQL Server, which I have. 
Do I need to write code with the login and password for the SQL connection instead?
-Ed

View 1 Replies View Related

Problem With T-SQL In TableAdapter

Jul 11, 2007

Hi,
I have a TableAdapter created in a Dataset. I'm creating a search function for a table, and here's how my code looks like in the "Add Query" wizard:
Select * from Event where eventname like '%@EventName%'
But when I click on preview data, I do not get the prompt to enter a value for the @EventName parameter, where went wrong?

View 4 Replies View Related

I/O Blob Through TableAdapter

May 26, 2006

What's a good way to work with Blobs and TableAdapters, in terms of declaring compatible column types in SQL Server and DataTable fields?

View 2 Replies View Related

TransactionScope And TableAdapter

Jul 30, 2007

This thread has also been posted under '.NET Data Access and Storage'. However, I have realized that the same code contruction using SQL Server connection- and tableadapter objects work fine so I am trying to get answers here also.

I am attempting to do transactional updates to SQL Compact Edition database using TableAdapter and TransactionScope like this:


using (TransactionScope ts = new TransactionScope())

{


SqlCeConnection sqlConn = new SqlCeConnection(connectionString);

myTableTableAdapter ta = new myTableTableAdapter();

ta.Connection = sqlConn;

ta.Update(dsmyTable.myTable);
ts.Complete();

}


dsmyTable is a strongly typed dataset created through the Dataset Designer and populated with data from the database prior to the code sample above.

This all works fine. However, when removing the call to 'ts.Complete()' to simulate the transaction rolling back, data is still stored into the database.

Am I missing something here or does TransactionScope not support SQL Compact Edition? Any help is appreciated!

Regards.

View 3 Replies View Related

TableAdapter And Variable

Sep 5, 2006

Hello,



I want to do something like this in a TableAdapter's SQL Statement:



SELECT Type.*

FROM Controllers INNER JOIN

@Type AS Type ON Type.ConfigurationId = Controllers.ActiveConfig INNER JOIN

Tank ON Controllers.ControllerId = Tank.ControllerId

WHERE (Tank.TankId = @TankId)



But I get an error because of the @Type variable. Is there any other way to choose a table in a TableAdapter with variables?



Thanks for your help!

View 3 Replies View Related

Passing Parameters In TableAdapter

Jul 28, 2006

A little new to ASP.NET pages, and I'm trying to pass some parameters to a SQL 2000 Server using a TableAdapter, code is as follows:
------
TestTableAdapters.test_ModemsTableAdapter modemsAdapter = new TestTableAdapters.test_ModemsTableAdapter();
// Add a new modem
modemsAdapter.InsertModem(frmRDate, frmModem_ID, frmProvisioning, strDecESN );
--------
And the error I get when loading the ASP.NET page is as follows:
Compiler Error Message: CS1502: The best overloaded method match for 'TestTableAdapters.test_ModemsTableAdapter.InsertModem(System.DateTime?, string, string, string)' has some invalid arguments
---------------
Now I realized the four strings that I am trying to pass to the server refer to ID's on Textboxes on the web page. Not sure if that might be the problem for databinding... ? Or is it my statement for the adapter?
-Ed

View 4 Replies View Related

TableAdapter Class Not Generated

Aug 2, 2006

I created a new DataSet object using the
wizard and had no probs, it's very straightforward.  I created a
GetProducts() method and also added a GetProductCount() method.  I
read somewhere that when the DataSet is saved, it will generate the
TableAdapter classes and store them in the project nested under the
DataSet object.  This isn't happening, the files aren't there and
yes I'm displaying all files.  There *is* however the TableAdapter
class but it's in not in the project, it's a temporary file w/ the
following path:
   
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET
Fileswebsitef38f8b5500014aeSources_App_Codepw_products.xsd.72cecc2a.cs.

I saved the DataSet several times, compiled the entire solution and
even closed the solution yet this temporary file isn't getting saved
where it should be.  Does anyone have any ideas abt this? 
I'm eager to start developing my DAL and def want to use these
TableAdapter classes.

Thx in advance,
-Pete

View 2 Replies View Related

Tableadapter Query Question

Nov 20, 2006

hii am using the nothwind database for a current exercise, i am wanting to user to be able to search for products based on two input methods (product name, category).here is the code i currently have: SELECT    DISTINCT  ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, ProductName,                       ProductID, CategoryIDFROM         ProductsWHERE       (CategoryID = @categoryID) OR                       (ProductName LIKE '%' + @ProductName + '%')i'm wanting to have it set so the user can use both input methods, or either, when one input method is used by the user it works fine, but when both are used it generates results for 2 seporate queriesi'm wanting to have it set so the user can search by product name in certain categories (when both search input methods are used)how would i go about doing this? is there some kind of equivalent to an AND/OR statement?eg.  WHERE       (CategoryID = @categoryID) AND OR
                      (ProductName LIKE '%' + @ProductName + '%') thanks in advance! 

View 2 Replies View Related

Going From Sqlconnection And Sqlreader To Using A Tableadapter And ????????

Apr 13, 2007

Below is my code right now Im using a direct sqlconnection for each request. I would like to use Tableadapters and/or my BLL code like the rest of my sites pages. I cant find how to do this progamatically and using the xmltextwriter. (Also i need to be able to name the xml output nodes like below)Thanks for the help.NeilPrivate Sub GetAllEvents(ByVal SqlString)    Response.Clear()    Response.ContentType = "text/xml"    Dim objX As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)    objX.WriteStartDocument()    objX.WriteStartElement("Events")    Dim objConnection As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|*********.mdf;Integrated Security=True;User Instance=True")    objConnection.Open()    Dim sql As String = SqlString    Dim objCommand As New SqlCommand(sql, objConnection)    Dim objReader As SqlDataReader = objCommand.ExecuteReader()    While objReader.Read()      objX.WriteStartElement("Event")      objX.WriteAttributeString("EventId", objReader.GetInt32(0))      objX.WriteAttributeString("EventName", objReader.GetString(1))      objX.WriteAttributeString("EventDescription", objReader.GetString(2))      objX.WriteAttributeString("EventDate", objReader.GetDateTime(3))      objX.WriteAttributeString("CurrentDate", Date.Now.ToString)      If Not objReader.IsDBNull(12) Then        objX.WriteAttributeString("EventImage", objReader.GetString(12))      End If      objX.WriteEndElement()    End While    objReader.Close()    objConnection.Close()    objX.WriteEndElement()    objX.WriteEndDocument()    objX.Flush()    objX.Close()    Response.End()  End Sub

View 2 Replies View Related

If/then Parameterized Queries Using Tableadapter

Jun 29, 2007

Hey fellas.  Here's my situation.  I have two textboxes where the user enters a "start" date and an "end" date.  I want to search a table to find records who's "expired" column date is between those two dates provided by the user.  The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond.  Essentially, I think I'm looking for a SQL statement along the lines of:
  SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate,
Request.ExpirationDate
FROM Request INNER JOIN
RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN
ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN
RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN
Role ON RoleActionProvider.RoleID = Role.RoleID
WHERE

CASE WHEN @BeginDate is not null AND @BeginDate <> ''
THEN Request.ExpirationDate > @BeginDate
END

AND

CASE WHEN @EndDate is not null AND @EndDate <> ''
THEN Request.ExpirationDate > @EndDate
END

AND (Role.Description = 'Requestor')

 
I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around.  Anyway, how do I do this?  Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL?  If I can tackle it with SQL, where have I gone astray?  I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text."
 Thanks for the help everyone!

View 3 Replies View Related

TableAdapter And Connection Strings

Sep 19, 2007

Hello,
I'm trying to setup a typed dataset with a table adapter in .NET 2.0, and the problem I am having is that I cannot get the table adapter to use existing connection strings setup in the web.config file.  How can I get it to do so?  It doesn't see the connection strings, and so it wants me to create a new connection, which I don't want to do.

View 2 Replies View Related

Tableadapter.update Problem

Feb 26, 2008

If this is the wrong forum, please let me know.

Using .NET Compact Framework, VB.NET, SQL Server CE 3.0

I use tableadapter.update in many places throughout my application. The user can save a record by click on a menu item called Save.

I call this code:

Private Sub Save

bindingsource.endedit
tableadapter.update(me.dataset.datatable)

End Sub

In most places the code works fine. It saves updates to the table. However, in two tables it is not working. It does not throw any exceptions, but does not save the record.

The subs that work and the ones that dont are coded exactly the same.

Thanks for any help

Ryan

View 1 Replies View Related

TableAdapter Configuration Wizard Error

Jan 31, 2007

Hi,I always use the following sql statement to check whether an email address exists in a database. With my latest project I tried to do this within the TableAdapter Configuration Wizard and it gave me an error:  IF NOT EXISTS (SELECT Email FROM tblEmailList WHERE Email=@Email) BEGIN INSERT INTO tblEmailList (Email, DateRegistered) VALUES (@Email, @DateReg) SELECT 1 AS RESULT END ELSE BEGIN SELECT 0 AS RESULT END first it gave an error saying I must declare the variables @Email and @DateReg then when I removed the part of insert and just wanted to check the email address existence it gave me this error:The Compound statement SQL construct or statement is not supportedAny idea anyone? 

View 5 Replies View Related

Passing Integer List To SQL Using TableAdapter?

Apr 15, 2007

I want to select rows that have an integer value IN a parameter list value
I want to pass an integer list to my tableadapter's select 
as in  Select  * from sometable
Where myIntValue IN (@intParamList )
 
I cannot figure out how to get my list into @intParamList
It works fine as a single value  - eg 3, but how do I set it to 1,2,3 ?
Thanks
 Bill

View 2 Replies View Related

~TableAdapter.Update Not Updating The Database

Apr 28, 2006

I am designing my first database (Visual Basic & SQL Server Express 2005) and it appears as if the database isn't updating. I am running it by hitting F5, closing by hitting the "X" and then hitting F5 again to check if the changes have stuck. They don't. These are the instructions given in the tutorial.

Any ideas? Thanks.

In particular I have tried two step-by-step tutorials distributed by Microsoft: 1. Absolute Beginner's Video Series, Lesson09; and 2. the Help tutorial: Managing Your Records (ms-help://MS.VSExpressCC.v80/MS.NETFramework.v20.en/dv_vbcnexpress/html/1ccdb8f1-4162-4a54-af17-231007eb529b.htm)

The code for the form is:

Public Class Form1


Private Sub AddressesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddressesBindingNavigatorSaveItem.Click


Me.Validate()

Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing


Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


'TODO: This line of code loads data into the 'FirstDatabaseDataSet.Addresses' table. You can move, or remove it, as needed.

Me.AddressesTableAdapter.Fill(Me.FirstDatabaseDataSet.Addresses)

End Sub
End Class

View 3 Replies View Related

TableAdapter.Insert Method At VS .Net 2008.

Mar 28, 2008

Hi there,

i have a problem with my smart device application that i develop.

To simplify:

1) C# smart device appliccation
2)a database file *sdf
3)a typed dataset using the wizard to do so
4)TableAdapter with connection string with no problems(cool)
5)use of Insert method that returns 1 ( also cool) [--> MyTableAdapter.Insert("blabla","blablabla"); <------]
6)no saved records in the DB file(baaaaaad)

Plz any help would be appreciated.

Dimoklis
Software Developer

View 9 Replies View Related

TableAdapter Wizard Not Writing Stored Procedures?

Nov 9, 2006

My TableAdapter wizard will not write the update and delete stored procedures and I do not know why.  Any thoughts?

View 1 Replies View Related

How To Install A Null Value Into A DateTime Column Via A TableAdapter?

Feb 8, 2007

Hi,I have a table adapter that I am using to call a stored procedure on a SQL server database to insert a record.Everything works ok, except I cannot figure out how to pass a null value to a DateTime field.  In my SQL server database, the column allow nulls.  In my typed dataset, the column is set to allow nulls.When I try to run the insert procedure with a null value: ListTableAdapter da = new ListTableAdapter();
da.InsertList(System.Data.SqlTypes.SqlDateTime.Null, Name); I get this error: Error    14    Argument '1': cannot convert from 'System.Data.SqlTypes.SqlDateTime' to 'System.DateTime?I've also tried passing it DBNull.Value, but I get an exception with that too.Can anyone steer me in the right direction with this?

View 11 Replies View Related

TableAdapter/DataTable Query Returning No Results

Sep 6, 2007

Hello,
I have a query that works in query analyzer; it looks that a certain date is between the start and end date of a certain value.  I also have a status field, which can be null, but if provided, provides the appropriate status to filter by.
Again, the query works in QA, but not in the application.  I test in SQL by using start date = '1/1/1900', end date = '12/31/9999', and status = null.  Results are returned.  But, not when the results are done through code.  In code, I set the begin date to new DateTime(1900, 1, 1), the end date to DateTime.MaxValue, and the status to a null string.  But, no results are returning.  Why isn't that mapping over correctly?  In the function, it has the two dates as Nullable(Of DateTime), which I provide a date, and the string is getting passed Nothing.
Any ideas?  Can't post any code on this one...
Thanks.

View 5 Replies View Related

TableAdapter Reversing Values Sent By A Stored Procedure

Nov 5, 2007

I have a  stored procedure which returns 3 different kind of values. I am checking whether a certain value entered by user is present in one of the columns of database table. Accordingly the SP returns 1 if present, -1 if not present and third value is SQL server 2005  error.But the problem is that I am only getting  -1  everytime even if the value is present.I executed  the SP alone to find out if it is the one which is returning the INCORRECT value. I found that that SP is returning the correct value.Therefore I came to the conclusion that it is the Table ADapter which got corrupted.I deleted the TableAdapter and created it again, but then it didn't solve the problem.I have now run out of ideas.
The code of the SP is:ALTER PROCEDURE spcheck_ServerName
(@Server_Name nvarchar(50)
 
)
ASDECLARE @Result int
IF EXISTS
(
SELECT
NULL
FROMServerDetails WITH (UPDLOCK)
WHERE
 
[SERVER NAME] = @Server_Name
)
BEGINSELECT @Result = 1
END
ELSE
BEGIN
 SELECT @Result = -1
END
 
IF @@ERROR <> NULL
BEGIN
 SELECT @Result = @@ERROR
 END
RETURN @Result
And I am calling the tableAdapter method in the code behind file of the web form in the following manner:private int chkServerName(string sname1)
{
try
{Serverlist1TableAdapters.SERVERDETAILSTableAdapter nwAdapter = new Serverlist1TableAdapters.SERVERDETAILSTableAdapter();
int snval = (int)nwAdapter.spcheck_SName(sname1);return snval;
}catch (Exception ex)
{return ex.GetHashCode();
}
}
 
Any help will be greatly appreciated.

View 5 Replies View Related

Reuse Existing Connection String For TableAdapter?

Jan 25, 2008

I've added a typed DataSet and dragged a table across from the server explorer. When I click configure on the table adapter, then click previous back to the "Choose Your Data Connection" dialog. The only option is the new connection that was just created when I added the sql server to the server explorer. Is there anyway to reuse my existing web.config connection string? My goal is to have a single connection string in my web.config.
 Thanks.
 -David

View 3 Replies View Related

How Do I Insert A Record With Possible NULL Values Using A Tableadapter.

Feb 26, 2008

I'm sorry, this question has a pretty long preamble, but bear with me as this will fully explain my problem, and save a number of clarification questions.I am using Visual Web Developer 2005 Express, with SQL Server 2005. After reading a Microsoft article saying that it was much better to use a three tier architecture with data adapters than to write explicit queries, I have used this approach extensively, and generally it works very well.  Typical code patterns are: -        Dim taPassenger As New ShippingTableAdapters.VPassengerTableAdapter        Dim tbpassenger As Shipping.VPassengerDataTable        Dim trpassenger As Shipping.VPassengerRow        .....        '   to process all passengers from a voyage: -        tbpassenger = taPassenger.GetDataByVOYid(Voyageid)        For Nbr As Integer = 1 To tbpassenger.Count            trpassenger = tbpassenger(Nbr - 1)             '    I now have the row available in properly-typed fields, eg trpassenger.vpxName             ...        Next        '   to read and process a single passenger        tbpassenger = tapassenger.GetDataByVPXid(VPXid)        trpassenger = tbpassenger(0)The table adapter has been created with Insert,Update, Delete methods.   Thus you can assign values to row fields, and then update the row: -        trpassenger.VPXName = "New Name"        ....        tapassenger.update(trpassenger)There are actually several overloads of the update method:  you could have written a field list, like this, where the names (VPXid etc) would be defined in my program as variables of the appropriate type for the corresponding column.        tapassenger.update(VPXid, VPXName, VPXAge, ....HOWEVER,  this doesn't work properly if you have non-string data that could be null.   Thus if the fourth field is a foreign key that could be null, if you write        Dim VPXVoyId as Nullable(of GUID)        VPXVoyid = nothing        tapassenger.update(VPXid, VPXname, VPXAge, VPXVoyid, ...or         Trpassenger.VPXVoyid = Nothing        tapassenger.update(trpassenger) you do NOT put a null value into the database.  Instead you store a value of "00000000-0000-0000-0000-000000000000".   You can solve this annoying problem in two ways: -A.   If you will ALWAYS be storing a null value at this point of your program, then simply write the update statement like this: -        tapassenger.update(VPXid, VPXname, VPXAge, Nothing, ...Here "Nothing" actually means "DBNull" even though it doesn't mean this when used in a normal VB assignment (and neither does DBNull).B   You can use the SetxxxxNULL method: -        If trpassenger.VPXVoyid = nothing then             trpassenger.SetVPXVoyidNull        End If        tapassenger.update(trpassenger) Thus when the program logic is complex and there are many paths to the update statement I will often put statements such as this in front of the update statement, using SetxxxxxNull for all non-string values that may be null.  You don't need to worry about string values because the Nullvalue property can be used to simply say that Null values are empty, and it all works properly, but for reasons unknown Microsoft have prohibited this for other data types. Now at last we get to my question, which is "How do I do the equivalent for an Insert?"   tapassenger.insert does not have any overloads, and the only form is  tapassenger.insert(field list).   But this creates the problem above:  if VPXVoyid = nothing then I insert the not-null value of "00000000-0000-0000-0000-000000000000".   If I COULD use the form tapassenger.insert(trpassenger), then I'd have another problem: if you haven't set trpassenger (for example with trpassenger = tbpassenger(0)) then it will not exist, so you will get an exception when you try to reference it.  Yet you can't write    If isnothing(trpassenger) then       trpassenger = new shipping.vpassengerrow)         (I've tries this both as an assignment, and as a Dim statement)    end ifSo far I can think of only two solutions, both awful: -a/    Use multi-choice logic to choose an appropriate insert statement.  If there is only one or two possibly-null fields, I could write:-        If field4 = nothing then            trpassenger.insert (VPXid, VPXname, VPXAge, Nothing, ..        However if there is more than one possibly-null field, you need (2 to the power of  nbr-of-possibly-null-fields) insert statements, with of course a lot of testing to choose which oneb/    I could insert the record with nulls, then read it back and update it.  Surely there is a better way!   Help! Thank you, Robert Barnes.    

View 2 Replies View Related

How To Get The Return Value When Using A TableAdapter Access A Stored Procedure

Apr 25, 2006

I have a Stored Procedure

CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END

I can using the unbox get the return value

but if i direct return a value form a Stored Procedure like this

CREATE PROCEDURE test
AS
BEGIN
return 100
END

I can not get the VALUE
I do not know how to
Please Help Me
thx

View 1 Replies View Related

How To Get The Return Value When Using A TableAdapter Access A Stored Procedure

Apr 25, 2006

I have a Stored Procedure

CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END

I can using the unbox get the return value

but if i direct return a value form a Stored Procedure like this

CREATE PROCEDURE test
AS
BEGIN
return 100
END

I can not get the VALUE
I do not know how to
Please Help Me
thx

View 1 Replies View Related

TableAdapter.Update (Data Not Being Writen To The Database)

May 30, 2006

Problem
I downloaded the VB Lesson 9 Visual Basic Project from Microsoft Learning Resources. With out making any changes to the project I compiled and ran it, making changes to the data and saving the changes. Every thing look great until you run it a second time and see that the changes to the data are all lost.
 
Observations:
1. Changes made to the data from within Visual Studio (Show Table Data) do take effect.
 
2. The in-memory data (DataSet) is changed as you move from row to row.
 
3. The TableAdapter.Update call returns 1, ostensibly indicating the number of rows updated.
 
4. The data in the database is not changed by the TableAdapter.Update call.
 
Operating System: Windows XP Pro Service Pack 2
Other Software installed on this system
20SQL Server 2005 Express
Visual Web Developer 2005 Express
Visual Studio 6.0 Enterprise
Visual Studio .net Enterprise
Microsoft .NET Framework SDK
Microsoft Office 2003
 
 
If anyone can help me understand the cause of this problem I would greatly appreciate it.
 
Thanks
Bob

View 1 Replies View Related

Access To TableAdapter Object In WebPage Class.

Feb 2, 2007

Hi, I'm using Reporting Services on my website and for some reports, i get the timeout error after 30 secs.

I declared my object using the Reporting tools and I am using ObjectDataSource.

My report is declared in my aspx page:

<rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt" Width="100%" Height="600px">

My ObjectDataSource is declared in my aspx page under my report tag:

<asp:ObjectDataSource ID="ObjectDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="ReportsTableAdapters.ViewingTotalsTableAdapter">

My TableAdapter is declare in my Reports.xsd file:

<TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="ViewingTotalsTableAdapter" GeneratorDataComponentClassName="ViewingTotalsTableAdapter" Name="ViewingTotals" UserDataComponentName="ViewingTotalsTableAdapter">

I am trying to access the TableAdapter object from my WebPage Class on the Page_Load event , so I can modify the Command.CommandTimeout. Unfortunately, I cannot find a way to have access to the TableAdater object.

Anyone has any suggestion ?

Thanks,

Richard



View 1 Replies View Related

Using Scope_identity

Oct 20, 2007

Using scope_identity
I am using SQL2005 and I need to insert a record and return ID. I am using scope_identity() in the stored procedure to return the ID for the record just inserted.
Do you see any problem with this when it comes to multi-user and multi-threaded environment.
 

View 6 Replies View Related

Help On Scope_identity

Jan 3, 2008

Hi, i  need the DiagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement. I was told to use scope_identity(), but i'm not sure how to implement it. Below is my code behind in vb.net. pls help. Dim cmd1 As New SqlCommand("insert into Diagnosis(TypeID, SeverityID, UniBilateral, PatientID, StaffID) values ('" & typevalue & "','" & severityvalue & "','" & unibivalue & "','" & Session("PatientID") & "','" & Session("StaffID") & "')", conn)        cmd1.ExecuteNonQuery()        Dim i As Integer        For i = 0 To hearingarray.Count - 1            Dim li As New ListItem            li = hearingarray(i)            Dim cmd As New SqlCommand("insert into DiagnosisManagement(ManagementID) values ('" & li.Value & "')", conn)        //i need the DIagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement here            cmd.ExecuteNonQuery()        Next

View 1 Replies View Related

Using SCOPE_IDENTITY()

May 19, 2008

Hi All,
I'm trying to return the last id entered via the following code, and I'm only getting '0' back.
 1 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))
2 {
3 connection.Open();
4 using (SqlCommand command = new SqlCommand("REG_S_CustomerIDFromPhoneNumber", connection))
5 {
6 command.CommandType = CommandType.StoredProcedure;
7 command.Parameters.AddWithValue("@Mobile_Telephone", MobileNumber);
8
9 int test = Convert.ToInt32(command.ExecuteScalar());
10
11 Response.Write(test.ToString());
12
13
14 }
15 } My SP is as follows (I'm trying to use one that's already been written for me) 1 SET QUOTED_IDENTIFIER ON
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7
8 ALTER PROCEDURE [dbo].[REG_I_CreateBlankCustomer]
9 @Mobile_Telephone varchar(255),
10 @CustomerID int OUTPUT
11
12 AS
13
14 INSERT INTO Customer (Mobile_Telephone)
15 VALUES (@Mobile_Telephone)
16
17 --SET @CustomerID = @@Identity
18 SELECT SCOPE_IDENTITY();
19
20
21 GO
22 SET QUOTED_IDENTIFIER OFF
23 GO
24 SET ANSI_NULLS ON
25 GO
26
27
28
 

 when I'm running this via Query Analyser, I get the ID returned correctly, however as mentioned when ran via that code above - I get a 0 outputted to me.  What am I doing wrong?
 
Thanks!

View 9 Replies View Related







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