insert or replace: net

Showing posts with label net. Show all posts

Friday, March 30, 2012

inserting a new record

I want to insert a new record into my db in asp.net. Im programming in vb and using an sql server database. i know this is a begginers question but that's exactly what i am. Thanks in advanceHave you had a look at the ASP.net starter kits?

Inserting 1:M relationship data via One Stored Procedure

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,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

Wednesday, March 28, 2012

insert/update timestamp in a SQL server 2000 db programatically

Hi,

How can i store the record insert/update timestamp in a SQL server 2000 db programacally. ? what are the date/time functions in ASP.NET 2.0 ? I know that this can be done by setting the default valut to getdate() function in SQL, but any other way on ASP page or code-behind page ?

Thanks,

Alex

You can use

string

s =DateTime.Now.ToString("dd/MMM/YYYY");

and then put it into the relevant parameter for your SqlCommand

|||Yes, that is correct if i assume i put that line of code in the code-behind page. But what will the syntax be if i need to use the same in the aspx page

My insert statement is as follows:

InsertCommand="INSERT INTO [StudentRegistration] ([RegDate], [FirstName], [SecondName], [FamilyName], [Photo], [CourseId], [MorningClass], [AfternoonClass], [Block], [Street], [HouseAptNo], [Area], [POBox], [PostalCode], Email , [HomePhone], [Mobile], [WorkPhone], [BirthDate], [Gender], [Nationality], [MaritalStatus], [CivilIdNo], [ExpiryDate], [ContactName], [ContactTel], [ContactMob], [ContactEmail], [MedicalCond], [CompleteHS], [CompYear], [ExpDate], [WhichSchool], [SchoolType], [Other], [QualTitle1], [QualInst1], [QualComp1], [QualTitle2], [QualInst2], [QualComp2], [QualTitle3], [QualInst3], [QualComp3], [QualTitle4], [QualInst4], [QualComp4], [Notes], [DateAdded], [AddedByFK]) VALUES (@.RegDate, @.FirstName, @.SecondName, @.FamilyName, @.Photo, @.CourseId, @.MorningClass, @.AfternoonClass, @.Block, @.Street, @.HouseAptNo, @.Area, @.POBox, @.PostalCode, @.Email, @.HomePhone, @.Mobile, @.WorkPhone, @.BirthDate, @.Gender, @.Nationality, @.MaritalStatus, @.CivilIdNo, @.ExpiryDate, @.ContactName, @.ContactTel, @.ContactMob, @.ContactEmail, @.MedicalCond, @.CompleteHS, @.CompYear, @.ExpDate, @.WhichSchool, @.SchoolType, @.Other, @.QualTitle1, @.QualInst1, @.QualComp1, @.QualTitle2, @.QualInst2, @.QualComp2, @.QualTitle3, @.QualInst3, @.QualComp3, @.QualTitle4, @.QualInst4, @.QualComp4, @.Notes, @.DateAdded , @.AddedByFK)"

<asp:ParameterName="DateAdded"Type="DateTime"/>

in this code, how do i retrieve the current date/time from SQL server while inserting a new record ?

i want to set the DateAdded field to default to the current date/time

Thanks,

Alex

|||I would personally use a stored procedure as then you can pass back the SQL datetime as the return value or an output parametersql

Friday, March 23, 2012

Insert unicode data into the database with Typed DataSet

Hi all,

I am using aStrongly Typed DataSet (ASP.NET 2.0) to insert new data into aSQL Server 2000 database, types of some fields in db arenvarchar.

All thing work fine except I can not insertunicode data(Vietnamese language) into db.

I can't find where to putprefix N.

Please help me!!!

The last collation definition is the MSDN link below is for Vietnamese_CI_AS you have to use that as your database and column collation because 2000 support column level collation. So use NChar, NVarchar and NText as your data types and use the collation below. VS2005 also comes with the option to choose the editor to use in saving your files and the correct encoding so use Vietnamese encoding to save your dataset file, that is covered in the second link. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms143508.aspx

https://www.microsoft.co.ke/middleeast/msdn/arabicsupp.aspx#7

Wednesday, March 21, 2012

Insert trigger calls .NET application

I have an appication that feeds a SQL Server 2005 database with records. I
have another application that should treat the records inserted by this firs
t
application. I know you can achieve this by incorporating .NET code in SQL
Server 2005.
However is there another possibility that SQL Server tiggers my second
application after inserting records from the first application?
thanks.Hello Guy,

> I have an appication that feeds a SQL Server 2005 database with
> records. I
> have another application that should treat the records inserted by
> this first
> application. I know you can achieve this by incorporating .NET code in
> SQL
> Server 2005.
> However is there another possibility that SQL Server tiggers my second
> application after inserting records from the first application?
There's a couple of ways of doing that:
a. Use service broker to do the inserts and send a copy of the data to a
queue that your second program processes
b. Use a SQLDependency in your second application to watch the table in ques
tion,
get the new records and process them.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

Monday, March 19, 2012

Insert TIME only in DateTime field

I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?That is how datetime fields work. You need to address this using formatting on the client side. DateTime has a number of useful ToString() overloads.|||Thanks for your help. It is too bad Access doesn't function in datatypes more similarly to SQL, especially since they are products of the same company. This migration would go alot easier. I am migrating some VBScript applications from Access to SQL, then from VBScript to VB.Net. It has been quite fun so far. Hehehe...

insert stored procedure does not commit to the database

Hi,
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer
AS
SET IMPLICIT_TRANSACTIONS OFF
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SELECT SCOPE_IDENTITY() AS ID
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@.TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@.TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@.UserID", 1)
Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
>
Is there an existing transaction running in the connection? Perhaps a
System.Transactions.TransactionScope? Or some other bit of code running a
BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
>0 there is a transaction.
David
|||On 19 Mar, 21:52, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:

> Is there an existing transaction running in the connection? Perhaps a
> System.Transactions.TransactionScope? Or some other bit of code running a
> BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
> David
Hi David,
Thanks for the reply, I dont think so, I'm running this bit of code as
the first thing my application does in the Form Load Code. I changed
the last line of the SP to
SELECT @.@.trancount AS ID , and the message box returned a blank or
empty string, is this how I was supposed to check?
|||Phil,
With "SET IMPLICIT_TRANSACTIONS OFF" you need to somewhere explicitly commit
the transaction. Your VB code looks like you are closing you connection
without ever issuing a commit.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>
|||General Issues:
1) SET NOCOUNT ON should be first line of almost any sproc.
2) Use an OUTPUT variable for a single row, single value return from a
sproc.
Specific Question Issues:
3) Get rid of SET IMPLICIT_TRANSACTIONS OFF
4) Use BEGIN TRAN..DML..Check for Error..COMMIT/ROLLBACK TRAN methodology in
your sproc.
5) if the above doesn't work, it could be something funky with the MSGBOX
workings. Set a variable equal to the sproc return and then display that
variable in the msgbox.
TheSQLGuru
President
Indicium Resources, Inc.
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>
|||Ok, here is my SProc, and I have changed my VB code to display the
output parameter in the message box, but still no joy, it give me the
next ID number in the table, but when I check the table still nothing
there.
ALTER PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SET @.ID = SCOPE_IDENTITY()
COMMIT TRAN
|||On 20 Mar, 08:50, "Steen Schlter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
>
>
> I know it's a very stupid question, but are you sure that you look for
> the new record in the correct table?
> What if you extend your code to do a SELECT for the record with the
> actual @.ID? If that gives you a record, it should be in the database.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
Hi Steen,
thanks for replying, yes I did consider that, and will try it when I
get home tonight, however I'm sure that I'm on the right database
because I always get the ID no 10, every time I run it, and in the
table that I check the last ID number was 9.
I'm starting to think maybe its some sort of permissions problem, but
I dont really know much about security in SQL 2005. The fact the the
procedure works fine when I try it in the IDE is the bit that is
really confusing me.
|||
> Maybe I'm misunderstanding what you are saying, but if you get the same
> ID every time it looks like it's not inserting anything? If the insert
> is succesfully, the @.ID should be incremented with the IDENTITY
> increment value. If the ID stays the same everytime you run the proc is
> looks like it's not inserting anything.
Hi Steen,
Yes that is the problem, despite my stored procedure returning the
next ID number like the data has been entered, when I check the table
nothing new has actually been added.
Does the proc look like it is correct? If so then I think maybe I will
try a VB.Net newsgroup and see if there is a problem with my code, but
it all looks ok to me.
Phil
|||Do you check for execution errors on return?
Also, just to be safe, I consider it good practice to wrap the entire
SP in a BEGIN/END pair. I don't recall if that's an issue when doing
this stuff thru VB, but it could be.
J.
On 19 Mar 2007 14:41:44 -0700, philhey@.googlemail.com wrote:

>Hi,
>I have a problem where an insert stored procedure does not commit to
>the database from a vb.net program. I can run the stored procedure
>fine through the IDE, but when I use the following vb code the message
>box shows the next ID number but when I check the database no new row
>has been added. Any ideas?
>Phil
>*******STORED PROCEDURE
>CREATE PROCEDURE MYSP_InsertEposTransaction
>@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
>@.TransactionTypeID AS Integer, @.UserID AS Integer,
>@.PaymentTypeID AS Integer
>AS
BEGIN

>SET IMPLICIT_TRANSACTIONS OFF
>INSERT EposTransaction
>(
>TransactionDate,
>CustomerID,
>TransactionTypeID,
>UserID,
>PaymentTypeID
>)
>VALUES
>(
>@.TransactionDate,
>@.CustomerID,
>@.TransactionTypeID,
>@.UserID,
>@.PaymentTypeID
>)
>SELECT SCOPE_IDENTITY() AS ID
END

>
>*VB CODE
>Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
>\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Epos Till.mdf;Integrated
>Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
>procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
>Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
>SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try

insert stored procedure does not commit to the database

Hi,
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer
AS
SET IMPLICIT_TRANSACTIONS OFF
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SELECT SCOPE_IDENTITY() AS ID
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@.TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@.TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@.UserID", 1)
Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
>
Is there an existing transaction running in the connection? Perhaps a
System.Transactions.TransactionScope? Or some other bit of code running a
BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
>0 there is a transaction.
David|||On 19 Mar, 21:52, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> Is there an existing transaction running in the connection? Perhaps a
> System.Transactions.TransactionScope? Or some other bit of code running a
> BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
> >0 there is a transaction.
> David
Hi David,
Thanks for the reply, I dont think so, I'm running this bit of code as
the first thing my application does in the Form Load Code. I changed
the last line of the SP to
SELECT @.@.trancount AS ID , and the message box returned a blank or
empty string, is this how I was supposed to check?|||Phil,
With "SET IMPLICIT_TRANSACTIONS OFF" you need to somewhere explicitly commit
the transaction. Your VB code looks like you are closing you connection
without ever issuing a commit.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||General Issues:
1) SET NOCOUNT ON should be first line of almost any sproc.
2) Use an OUTPUT variable for a single row, single value return from a
sproc.
Specific Question Issues:
3) Get rid of SET IMPLICIT_TRANSACTIONS OFF
4) Use BEGIN TRAN..DML..Check for Error..COMMIT/ROLLBACK TRAN methodology in
your sproc.
5) if the above doesn't work, it could be something funky with the MSGBOX
workings. Set a variable equal to the sproc return and then display that
variable in the msgbox.
--
TheSQLGuru
President
Indicium Resources, Inc.
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||Ok, here is my SProc, and I have changed my VB code to display the
output parameter in the message box, but still no joy, it give me the
next ID number in the table, but when I check the table still nothing
there.
ALTER PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SET @.ID = SCOPE_IDENTITY()
COMMIT TRAN|||philhey@.googlemail.com wrote:
> Ok, here is my SProc, and I have changed my VB code to display the
> output parameter in the message box, but still no joy, it give me the
> next ID number in the table, but when I check the table still nothing
> there.
> ALTER PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
> AS
> SET NOCOUNT ON
> BEGIN TRAN
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SET @.ID = SCOPE_IDENTITY()
> COMMIT TRAN
>
I know it's a very stupid question, but are you sure that you look for
the new record in the correct table?
What if you extend your code to do a SELECT for the record with the
actual @.ID? If that gives you a record, it should be in the database.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||On 20 Mar, 08:50, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
> > Ok, here is my SProc, and I have changed my VB code to display the
> > output parameter in the message box, but still no joy, it give me the
> > next ID number in the table, but when I check the table still nothing
> > there.
> > ALTER PROCEDURE MYSP_InsertEposTransaction
> > @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> > @.TransactionTypeID AS Integer, @.UserID AS Integer,
> > @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
> > AS
> > SET NOCOUNT ON
> > BEGIN TRAN
> > INSERT EposTransaction
> > (
> > TransactionDate,
> > CustomerID,
> > TransactionTypeID,
> > UserID,
> > PaymentTypeID
> > )
> > VALUES
> > (
> > @.TransactionDate,
> > @.CustomerID,
> > @.TransactionTypeID,
> > @.UserID,
> > @.PaymentTypeID
> > )
> > SET @.ID =3D SCOPE_IDENTITY()
> > COMMIT TRAN
> I know it's a very stupid question, but are you sure that you look for
> the new record in the correct table?
> What if you extend your code to do a SELECT for the record with the
> actual @.ID? If that gives you a record, it should be in the database.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
Hi Steen,
thanks for replying, yes I did consider that, and will try it when I
get home tonight, however I'm sure that I'm on the right database
because I always get the ID no 10, every time I run it, and in the
table that I check the last ID number was 9.
I'm starting to think maybe its some sort of permissions problem, but
I dont really know much about security in SQL 2005. The fact the the
procedure works fine when I try it in the IDE is the bit that is
really confusing me.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> phil...@.googlemail.com wrote:
>> Ok, here is my SProc, and I have changed my VB code to display the
>> output parameter in the message box, but still no joy, it give me the
>> next ID number in the table, but when I check the table still nothing
>> there.
>> ALTER PROCEDURE MYSP_InsertEposTransaction
>> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
>> @.TransactionTypeID AS Integer, @.UserID AS Integer,
>> @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
>> AS
>> SET NOCOUNT ON
>> BEGIN TRAN
>> INSERT EposTransaction
>> (
>> TransactionDate,
>> CustomerID,
>> TransactionTypeID,
>> UserID,
>> PaymentTypeID
>> )
>> VALUES
>> (
>> @.TransactionDate,
>> @.CustomerID,
>> @.TransactionTypeID,
>> @.UserID,
>> @.PaymentTypeID
>> )
>> SET @.ID = SCOPE_IDENTITY()
>> COMMIT TRAN
>> I know it's a very stupid question, but are you sure that you look for
>> the new record in the correct table?
>> What if you extend your code to do a SELECT for the record with the
>> actual @.ID? If that gives you a record, it should be in the database.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator- Hide quoted text -
>> - Show quoted text -
> Hi Steen,
> thanks for replying, yes I did consider that, and will try it when I
> get home tonight, however I'm sure that I'm on the right database
> because I always get the ID no 10, every time I run it, and in the
> table that I check the last ID number was 9.
> I'm starting to think maybe its some sort of permissions problem, but
> I dont really know much about security in SQL 2005. The fact the the
> procedure works fine when I try it in the IDE is the bit that is
> really confusing me.
>
>
Maybe I'm misunderstanding what you are saying, but if you get the same
ID every time it looks like it's not inserting anything? If the insert
is succesfully, the @.ID should be incremented with the IDENTITY
increment value. If the ID stays the same everytime you run the proc is
looks like it's not inserting anything.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||> Maybe I'm misunderstanding what you are saying, but if you get the same
> ID every time it looks like it's not inserting anything? If the insert
> is succesfully, the @.ID should be incremented with the IDENTITY
> increment value. If the ID stays the same everytime you run the proc is
> looks like it's not inserting anything.
Hi Steen,
Yes that is the problem, despite my stored procedure returning the
next ID number like the data has been entered, when I check the table
nothing new has actually been added.
Does the proc look like it is correct? If so then I think maybe I will
try a VB.Net newsgroup and see if there is a problem with my code, but
it all looks ok to me.
Phil|||philhey@.googlemail.com wrote:
>> Maybe I'm misunderstanding what you are saying, but if you get the same
>> ID every time it looks like it's not inserting anything? If the insert
>> is succesfully, the @.ID should be incremented with the IDENTITY
>> increment value. If the ID stays the same everytime you run the proc is
>> looks like it's not inserting anything.
> Hi Steen,
> Yes that is the problem, despite my stored procedure returning the
> next ID number like the data has been entered, when I check the table
> nothing new has actually been added.
> Does the proc look like it is correct? If so then I think maybe I will
> try a VB.Net newsgroup and see if there is a problem with my code, but
> it all looks ok to me.
> Phil
>
Hi Phil,
Since you've been able to successfully run the stored proc alone, I
assume that's ok. I'm not a programmer so unfortunately I'm not able to
tell if your VB code looks ok or not. It might be a good idea to let
somebody in a VB group look a the code - unless somebody else in here
can verify it's ok?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Do you check for execution errors on return?
Also, just to be safe, I consider it good practice to wrap the entire
SP in a BEGIN/END pair. I don't recall if that's an issue when doing
this stuff thru VB, but it could be.
J.
On 19 Mar 2007 14:41:44 -0700, philhey@.googlemail.com wrote:
>Hi,
>I have a problem where an insert stored procedure does not commit to
>the database from a vb.net program. I can run the stored procedure
>fine through the IDE, but when I use the following vb code the message
>box shows the next ID number but when I check the database no new row
>has been added. Any ideas?
>Phil
>*******STORED PROCEDURE
>CREATE PROCEDURE MYSP_InsertEposTransaction
>@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
>@.TransactionTypeID AS Integer, @.UserID AS Integer,
>@.PaymentTypeID AS Integer
>AS
BEGIN
>SET IMPLICIT_TRANSACTIONS OFF
>INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
END
>
>*VB CODE
>Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
>\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
>Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
>procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
>Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
>SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try

insert stored procedure does not commit to the database

Hi,
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer
AS
SET IMPLICIT_TRANSACTIONS OFF
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SELECT SCOPE_IDENTITY() AS ID
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\EposTill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@.TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@.TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@.UserID", 1)
Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
>
Is there an existing transaction running in the connection? Perhaps a
System.Transactions.TransactionScope? Or some other bit of code running a
BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
>0 there is a transaction.
David|||On 19 Mar, 21:52, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:

> Is there an existing transaction running in the connection? Perhaps a
> System.Transactions.TransactionScope? Or some other bit of code running a
> BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. I
f
> David
Hi David,
Thanks for the reply, I dont think so, I'm running this bit of code as
the first thing my application does in the Form Load Code. I changed
the last line of the SP to
SELECT @.@.trancount AS ID , and the message box returned a blank or
empty string, is this how I was supposed to check?|||Phil,
With "SET IMPLICIT_TRANSACTIONS OFF" you need to somewhere explicitly commit
the transaction. Your VB code looks like you are closing you connection
without ever issuing a commit.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||General Issues:
1) SET NOCOUNT ON should be first line of almost any sproc.
2) Use an OUTPUT variable for a single row, single value return from a
sproc.
Specific Question Issues:
3) Get rid of SET IMPLICIT_TRANSACTIONS OFF
4) Use BEGIN TRAN..DML..Check for Error..COMMIT/ROLLBACK TRAN methodology in
your sproc.
5) if the above doesn't work, it could be something funky with the MSGBOX
workings. Set a variable equal to the sproc return and then display that
variable in the msgbox.
TheSQLGuru
President
Indicium Resources, Inc.
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||Ok, here is my SProc, and I have changed my VB code to display the
output parameter in the message box, but still no joy, it give me the
next ID number in the table, but when I check the table still nothing
there.
ALTER PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SET @.ID = SCOPE_IDENTITY()
COMMIT TRAN|||philhey@.googlemail.com wrote:
> Ok, here is my SProc, and I have changed my VB code to display the
> output parameter in the message box, but still no joy, it give me the
> next ID number in the table, but when I check the table still nothing
> there.
> ALTER PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
> AS
> SET NOCOUNT ON
> BEGIN TRAN
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SET @.ID = SCOPE_IDENTITY()
> COMMIT TRAN
>
I know it's a very stupid question, but are you sure that you look for
the new record in the correct table?
What if you extend your code to do a SELECT for the record with the
actual @.ID? If that gives you a record, it should be in the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||On 20 Mar, 08:50, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
>
>
>
> I know it's a very stupid question, but are you sure that you look for
> the new record in the correct table?
> What if you extend your code to do a SELECT for the record with the
> actual @.ID? If that gives you a record, it should be in the database.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
Hi Steen,
thanks for replying, yes I did consider that, and will try it when I
get home tonight, however I'm sure that I'm on the right database
because I always get the ID no 10, every time I run it, and in the
table that I check the last ID number was 9.
I'm starting to think maybe its some sort of permissions problem, but
I dont really know much about security in SQL 2005. The fact the the
procedure works fine when I try it in the IDE is the bit that is
really confusing me.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi Steen,
> thanks for replying, yes I did consider that, and will try it when I
> get home tonight, however I'm sure that I'm on the right database
> because I always get the ID no 10, every time I run it, and in the
> table that I check the last ID number was 9.
> I'm starting to think maybe its some sort of permissions problem, but
> I dont really know much about security in SQL 2005. The fact the the
> procedure works fine when I try it in the IDE is the bit that is
> really confusing me.
>
>
Maybe I'm misunderstanding what you are saying, but if you get the same
ID every time it looks like it's not inserting anything? If the insert
is succesfully, the @.ID should be incremented with the IDENTITY
increment value. If the ID stays the same everytime you run the proc is
looks like it's not inserting anything.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||
> Maybe I'm misunderstanding what you are saying, but if you get the same
> ID every time it looks like it's not inserting anything? If the insert
> is succesfully, the @.ID should be incremented with the IDENTITY
> increment value. If the ID stays the same everytime you run the proc is
> looks like it's not inserting anything.
Hi Steen,
Yes that is the problem, despite my stored procedure returning the
next ID number like the data has been entered, when I check the table
nothing new has actually been added.
Does the proc look like it is correct? If so then I think maybe I will
try a VB.Net newsgroup and see if there is a problem with my code, but
it all looks ok to me.
Phil

Monday, March 12, 2012

Insert statement for a datetime field from vb.net code

Hi All,
I have a datetime column in a table on the SQL database. I need to insert
values into the datetime column from vb.net code. Here is my code:
dim nameval, str, qry as string
nameval = "abc"
str = "2005/03/16 14:20"
qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," & "'"
str & "')"
...
...
ocmd.ExecuteNonQuery()
...
...
The error message that I get is as follows:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated. .Net
SqlClient Data Provider"
The problem I think is due to passing a string for a datetime field. My
question is, if I convert the string to datetype using CDate(str), then I
would have to again convert the date to string in order to form the insert
statement. So, the ultimate result will be again passing a string for the
datetime field!
I know that this is a simple syntax problem, which I don't seem to get right!
Would anybody be able to give me insert statement for the above?
Thanks.
kd
Hi
"2005/03/16 14:20" is not a valid date time value. Either use "2005-03-16
14:20:00" or "20050316 14:20:00"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:1FF9F499-8589-4964-9F11-731F22E3D673@.microsoft.com...
> Hi All,
> I have a datetime column in a table on the SQL database. I need to insert
> values into the datetime column from vb.net code. Here is my code:
> dim nameval, str, qry as string
> nameval = "abc"
> str = "2005/03/16 14:20"
> qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," &
"'"
> str & "')"
> ...
> ..
> ocmd.ExecuteNonQuery()
> ...
> ...
> The error message that I get is as follows:
> "The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated. .Net
> SqlClient Data Provider"
> The problem I think is due to passing a string for a datetime field. My
> question is, if I convert the string to datetype using CDate(str), then I
> would have to again convert the date to string in order to form the insert
> statement. So, the ultimate result will be again passing a string for the
> datetime field!
> I know that this is a simple syntax problem, which I don't seem to get
right!
> Would anybody be able to give me insert statement for the above?
> Thanks.
> kd
>

Friday, February 24, 2012

Insert performance/nvarchar

hello,
i wrote a performance test for sequential inserts with ado.net on a P4 2GHz
512 Meg Ram machine
and got the following scores:
insert 10000 ints 1:30 mins
insert 10000 reals 1:20 mins
inserting 10000 nvarchars
first 10000: 1:30
second 10000: 4:11
third 10000: 6:50
fourth 10000: 9:30
fifth 10000: 12:12
sixth 10000: 15:00
seventh 10000 18:20
so the times gets worse and worse.
i would expect, that the convergate but they don't
Is this normal?
If yes we will have problems, because we expect a couple of millions entries
in this
table where this strings are stored.
all tables for the performance test have the same stucture and indices
except of
the datatype which is tested, which is
id
value
Can you give me a hint how to speed this?
thanks mikeDo you have your databases auto-growing during these tests, or did you set
the files to a large enough size before the tests to ensure that they
wouldn't grow?
Do you have the columns indexed? Are the inserts causing page splits?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> hello,
> i wrote a performance test for sequential inserts with ado.net on a P4
2GHz
> 512 Meg Ram machine
> and got the following scores:
> insert 10000 ints 1:30 mins
> insert 10000 reals 1:20 mins
> inserting 10000 nvarchars
> first 10000: 1:30
> second 10000: 4:11
> third 10000: 6:50
> fourth 10000: 9:30
> fifth 10000: 12:12
> sixth 10000: 15:00
> seventh 10000 18:20
> so the times gets worse and worse.
> i would expect, that the convergate but they don't
> Is this normal?
> If yes we will have problems, because we expect a couple of millions
entries
> in this
> table where this strings are stored.
> all tables for the performance test have the same stucture and indices
> except of
> the datatype which is tested, which is
> id
> value
> Can you give me a hint how to speed this?
> thanks mike|||Hello Adam,
yes its auto-growing
yes columns are indexed
most inserts are NOT causing page splits
thanks mike
"Adam Machanic" wrote:
> Do you have your databases auto-growing during these tests, or did you set
> the files to a large enough size before the tests to ensure that they
> wouldn't grow?
> Do you have the columns indexed? Are the inserts causing page splits?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > hello,
> >
> > i wrote a performance test for sequential inserts with ado.net on a P4
> 2GHz
> >
> > 512 Meg Ram machine
> >
> > and got the following scores:
> >
> > insert 10000 ints 1:30 mins
> > insert 10000 reals 1:20 mins
> >
> > inserting 10000 nvarchars
> > first 10000: 1:30
> > second 10000: 4:11
> > third 10000: 6:50
> > fourth 10000: 9:30
> > fifth 10000: 12:12
> > sixth 10000: 15:00
> > seventh 10000 18:20
> >
> > so the times gets worse and worse.
> > i would expect, that the convergate but they don't
> > Is this normal?
> >
> > If yes we will have problems, because we expect a couple of millions
> entries
> > in this
> > table where this strings are stored.
> >
> > all tables for the performance test have the same stucture and indices
> > except of
> > the datatype which is tested, which is
> > id
> > value
> >
> > Can you give me a hint how to speed this?
> >
> > thanks mike
>
>|||You'll get more consistent results if you grow the file first...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> Hello Adam,
> yes its auto-growing
> yes columns are indexed
> most inserts are NOT causing page splits
> thanks mike
>
> "Adam Machanic" wrote:
> > Do you have your databases auto-growing during these tests, or did you
set
> > the files to a large enough size before the tests to ensure that they
> > wouldn't grow?
> >
> > Do you have the columns indexed? Are the inserts causing page splits?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > hello,
> > >
> > > i wrote a performance test for sequential inserts with ado.net on a P4
> > 2GHz
> > >
> > > 512 Meg Ram machine
> > >
> > > and got the following scores:
> > >
> > > insert 10000 ints 1:30 mins
> > > insert 10000 reals 1:20 mins
> > >
> > > inserting 10000 nvarchars
> > > first 10000: 1:30
> > > second 10000: 4:11
> > > third 10000: 6:50
> > > fourth 10000: 9:30
> > > fifth 10000: 12:12
> > > sixth 10000: 15:00
> > > seventh 10000 18:20
> > >
> > > so the times gets worse and worse.
> > > i would expect, that the convergate but they don't
> > > Is this normal?
> > >
> > > If yes we will have problems, because we expect a couple of millions
> > entries
> > > in this
> > > table where this strings are stored.
> > >
> > > all tables for the performance test have the same stucture and indices
> > > except of
> > > the datatype which is tested, which is
> > > id
> > > value
> > >
> > > Can you give me a hint how to speed this?
> > >
> > > thanks mike
> >
> >
> >|||hello adam,
nope,
i dropped the old database, created a new one with fixed size
600 meg (for data and tranlog).
The behaviour is still the same.
The insert times are growing endless.
greetings mike
"Adam Machanic" wrote:
> You'll get more consistent results if you grow the file first...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > Hello Adam,
> >
> > yes its auto-growing
> > yes columns are indexed
> >
> > most inserts are NOT causing page splits
> >
> > thanks mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > Do you have your databases auto-growing during these tests, or did you
> set
> > > the files to a large enough size before the tests to ensure that they
> > > wouldn't grow?
> > >
> > > Do you have the columns indexed? Are the inserts causing page splits?
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > hello,
> > > >
> > > > i wrote a performance test for sequential inserts with ado.net on a P4
> > > 2GHz
> > > >
> > > > 512 Meg Ram machine
> > > >
> > > > and got the following scores:
> > > >
> > > > insert 10000 ints 1:30 mins
> > > > insert 10000 reals 1:20 mins
> > > >
> > > > inserting 10000 nvarchars
> > > > first 10000: 1:30
> > > > second 10000: 4:11
> > > > third 10000: 6:50
> > > > fourth 10000: 9:30
> > > > fifth 10000: 12:12
> > > > sixth 10000: 15:00
> > > > seventh 10000 18:20
> > > >
> > > > so the times gets worse and worse.
> > > > i would expect, that the convergate but they don't
> > > > Is this normal?
> > > >
> > > > If yes we will have problems, because we expect a couple of millions
> > > entries
> > > > in this
> > > > table where this strings are stored.
> > > >
> > > > all tables for the performance test have the same stucture and indices
> > > > except of
> > > > the datatype which is tested, which is
> > > > id
> > > > value
> > > >
> > > > Can you give me a hint how to speed this?
> > > >
> > > > thanks mike
> > >
> > >
> > >
>
>|||Can you post the table definitions, including constraints and indexes?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> hello adam,
> nope,
> i dropped the old database, created a new one with fixed size
> 600 meg (for data and tranlog).
> The behaviour is still the same.
> The insert times are growing endless.
> greetings mike
>
> "Adam Machanic" wrote:
> > You'll get more consistent results if you grow the file first...
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > Hello Adam,
> > >
> > > yes its auto-growing
> > > yes columns are indexed
> > >
> > > most inserts are NOT causing page splits
> > >
> > > thanks mike
> > >
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > Do you have your databases auto-growing during these tests, or did
you
> > set
> > > > the files to a large enough size before the tests to ensure that
they
> > > > wouldn't grow?
> > > >
> > > > Do you have the columns indexed? Are the inserts causing page
splits?
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
message
> > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > hello,
> > > > >
> > > > > i wrote a performance test for sequential inserts with ado.net on
a P4
> > > > 2GHz
> > > > >
> > > > > 512 Meg Ram machine
> > > > >
> > > > > and got the following scores:
> > > > >
> > > > > insert 10000 ints 1:30 mins
> > > > > insert 10000 reals 1:20 mins
> > > > >
> > > > > inserting 10000 nvarchars
> > > > > first 10000: 1:30
> > > > > second 10000: 4:11
> > > > > third 10000: 6:50
> > > > > fourth 10000: 9:30
> > > > > fifth 10000: 12:12
> > > > > sixth 10000: 15:00
> > > > > seventh 10000 18:20
> > > > >
> > > > > so the times gets worse and worse.
> > > > > i would expect, that the convergate but they don't
> > > > > Is this normal?
> > > > >
> > > > > If yes we will have problems, because we expect a couple of
millions
> > > > entries
> > > > > in this
> > > > > table where this strings are stored.
> > > > >
> > > > > all tables for the performance test have the same stucture and
indices
> > > > > except of
> > > > > the datatype which is tested, which is
> > > > > id
> > > > > value
> > > > >
> > > > > Can you give me a hint how to speed this?
> > > > >
> > > > > thanks mike
> > > >
> > > >
> > > >
> >
> >
> >|||Hello Adam,
here comes the table
create table LogStringTable
(
ID int identity (1,1) not null,
stringValue nvarchar(400) not null,
attributeTypeId int not null,
logItemId int not null
constraint FKATIhasStringValues
foreign key ( attributeTypeId )
references LogAttributeType (Id),
constraint FKItemHasStringvalues
foreign key (LogItemId )
references LogItem ( Id )
) on primary
there are four indices
primary key index on Id (clustered)
and an the other columns (not unique)
thank you mike
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hello Adam,
I dropped all indices and tried again
-> nothing principaly changed.
The times are shorter, but they are still growing endless,
with each 10000 insert.
When I have 100.000 entries in that table than the performance is reduce to
about
15 inserts/second compared with 100 inserts/second when starting with a
blank table.
And the performance goes down and down.
Greets mike
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||hello adam,
i know now, that it is not a problem if the sql server.
it has to do with ado.net.
currently i don't know what it is, but now I inserted 10.000 nvarchars with
the
query analyzer and it lasts about 5 seconds
regardless how many records are in the table.
so i have to look into the ado.net stuff.
thank you for your help
I'll let you know what is is, when i know it.
thank you very much
greets michael
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hello Adam,
I got it.
There is an option at the data adapter called
refresh the dataset
This was set to true.
I set it to false and now my world is perpendicular again.
The insert times for 10.000 stings are now about 5-7 seconds.
Unfortunatly, I even didn't use a dataset, so this option is useless even
when set to true.
I think this is worthy a microsoft call.
thank you for your help.
mike.
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Thanks for all of the updates; this is good to know! Can you post an
abbreviated version of the code you were using? How does a dataset option
affect you when you're not using a dataset? And how did you turn it off
without using a dataset?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:EBAADD96-9F27-4A71-AC21-B8487352237C@.microsoft.com...
> Hello Adam,
> I got it.
> There is an option at the data adapter called
> refresh the dataset
> This was set to true.
> I set it to false and now my world is perpendicular again.
> The insert times for 10.000 stings are now about 5-7 seconds.
> Unfortunatly, I even didn't use a dataset, so this option is useless even
> when set to true.
> I think this is worthy a microsoft call.
> thank you for your help.
> mike.
>
> "Adam Machanic" wrote:
> > Can you post the table definitions, including constraints and indexes?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > > hello adam,
> > >
> > > nope,
> > >
> > > i dropped the old database, created a new one with fixed size
> > > 600 meg (for data and tranlog).
> > >
> > > The behaviour is still the same.
> > >
> > > The insert times are growing endless.
> > >
> > > greetings mike
> > >
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > You'll get more consistent results if you grow the file first...
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
message
> > > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > > Hello Adam,
> > > > >
> > > > > yes its auto-growing
> > > > > yes columns are indexed
> > > > >
> > > > > most inserts are NOT causing page splits
> > > > >
> > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > > > > "Adam Machanic" wrote:
> > > > >
> > > > > > Do you have your databases auto-growing during these tests, or
did
> > you
> > > > set
> > > > > > the files to a large enough size before the tests to ensure that
> > they
> > > > > > wouldn't grow?
> > > > > >
> > > > > > Do you have the columns indexed? Are the inserts causing page
> > splits?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Adam Machanic
> > > > > > SQL Server MVP
> > > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > > --
> > > > > >
> > > > > >
> > > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> > message
> > > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > > hello,
> > > > > > >
> > > > > > > i wrote a performance test for sequential inserts with ado.net
on
> > a P4
> > > > > > 2GHz
> > > > > > >
> > > > > > > 512 Meg Ram machine
> > > > > > >
> > > > > > > and got the following scores:
> > > > > > >
> > > > > > > insert 10000 ints 1:30 mins
> > > > > > > insert 10000 reals 1:20 mins
> > > > > > >
> > > > > > > inserting 10000 nvarchars
> > > > > > > first 10000: 1:30
> > > > > > > second 10000: 4:11
> > > > > > > third 10000: 6:50
> > > > > > > fourth 10000: 9:30
> > > > > > > fifth 10000: 12:12
> > > > > > > sixth 10000: 15:00
> > > > > > > seventh 10000 18:20
> > > > > > >
> > > > > > > so the times gets worse and worse.
> > > > > > > i would expect, that the convergate but they don't
> > > > > > > Is this normal?
> > > > > > >
> > > > > > > If yes we will have problems, because we expect a couple of
> > millions
> > > > > > entries
> > > > > > > in this
> > > > > > > table where this strings are stored.
> > > > > > >
> > > > > > > all tables for the performance test have the same stucture and
> > indices
> > > > > > > except of
> > > > > > > the datatype which is tested, which is
> > > > > > > id
> > > > > > > value
> > > > > > >
> > > > > > > Can you give me a hint how to speed this?
> > > > > > >
> > > > > > > thanks mike
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Friday, March 30, 2012

inserting a new record

Inserting 1:M relationship data via One Stored Procedure

Wednesday, March 28, 2012

insert/update timestamp in a SQL server 2000 db programatically

Friday, March 23, 2012

Insert unicode data into the database with Typed DataSet

Wednesday, March 21, 2012

Insert trigger calls .NET application

Monday, March 19, 2012

Insert TIME only in DateTime field

insert stored procedure does not commit to the database

insert stored procedure does not commit to the database

insert stored procedure does not commit to the database

Monday, March 12, 2012

Insert statement for a datetime field from vb.net code

Friday, February 24, 2012

Insert performance/nvarchar

Insert performance/nvarchar

Insert performance/nvarchar

insert or replace

Blog Archive

About Me