InsertCommand.ExecuteNonQuery() And Violation Of PrimaryKey

Jan 15, 2007

Hey,

I have a page that inserts into a customers table in the DataBase a new customer account using this function:

Public Function InsertCustomers(ByRef sessionid, ByVal email, ByVal pass, Optional ByVal fname = "", Optional ByVal lname = "", Optional ByVal company = "", Optional ByVal pobox = "", Optional ByVal add1 = "", Optional ByVal add2 = "", Optional ByVal city = "", Optional ByVal state = "", Optional ByVal postalcode = "", Optional ByVal country = 0, Optional ByVal tel = "")

Dim result As New DataSet

Dim tempid As Integer

Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("Conn"))

Dim Adcust As New SqlDataAdapter

Adcust.InsertCommand = New SqlCommand

Adcust.SelectCommand = New SqlCommand

Adcust.InsertCommand.Connection = conn

Adcust.SelectCommand.Connection = conn

sessionExists(email, sessionid, 1)

conn.Open()

If fname = "" Then

Adcust.InsertCommand.CommandText = "Insert Into neelwafu.customers(email,password,sessionid) Values('" & email & "','" & pass & "','" & sessionid & "')"

Else

Dim strsql As String

strsql = "Insert Into neelwafu.customers"

strsql = strsql & "(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values("

strsql = strsql & "'" & sessionid & "','" & email & "','" & pass & "','" & fname & "','" & lname & "','" & company & "','" & pobox & "','" & add1 & "','" & add2 & "','" & city & "','" & state & "','" & postalcode & "', " & country & ",'" & tel & "')"

Adcust.InsertCommand.CommandText = strsql

End If

Adcust.InsertCommand.ExecuteNonQuery()

Adcust.SelectCommand.CommandText = "Select Max(id) from neelwafu.Customers"

tempid = CInt(Adcust.SelectCommand.ExecuteScalar())

conn.Close()

Return tempid

End Function

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now, I am getting an error:
Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The customers table has as a primary key the 'email'.....

so plz can I know why am I getting this error ????

Thank you in advance

Hiba

View 4 Replies


ADVERTISEMENT

PrimaryKey

May 15, 2008

Hi,

Got a question. I have a script that create a table-- I need Col1, Col2 and Col3 to have PrimaryKey. How do u do. Here is the script

CREATE TABLE [dbo].[PROC_DATA] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Col2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Col3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date1] [datetime] NOT NULL ,
[Recod1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [DRI_CHASE_DATA_GRP1]
GO

Josephine

View 5 Replies View Related

DATETIME AS PrimaryKey?

Dec 1, 2005

DATETIME AS PrimaryKey?
Hi Dears!
declare @dtpk smalldatetime
select @dtpk=getdate()
print @dtpk

in the above, i m using @dtpk as a primary key value in a
table to keep a track of some events that are occuring with
a difference of one second. the above method gives me value
to store in the Primary key Filed (EventTime smalldatetime)
in the form "Dec 1 2005 7:57PM" where my requirement is
to save it in the form "Dec 1 2005 7:57:12.03PM" so as to
make it unique for the primary key field(EventTime
smalldatetime). how to get required datetime format with
the help of above statements to store as primary key in
EventTime field of type smalldatetime.

THNX IN ADAVNCE

View 10 Replies View Related

Using Postcode As Primarykey

Aug 10, 2007

Hello

I've taken over a database where for many tables the postcode field (equivilent to the US Zip Code) is the obvious primary key. [Mapping postcodes to distict or various types of area for statistical analysis]. However the people who set up the database have continually used an auto generated number for the primary key.

There are never any duplicate postcodes in the various tables, so my question is what is the advantage of either using or not using the postcodes as primary keys.

View 2 Replies View Related

Identity -&> PrimaryKey

Oct 29, 2007

I've a question. I've an old db here with no Primary keys nearly but a lot of identity's set, one for each table (nearly). I'm wondering, could something somehow go wrong with some sp's or anything if I make primary keys of these identities? Question why I'm asking is that we're takinging about doing replication but can't since there are primary keys lacking but we dont dare to change ém into primary keys if something might go wrong since we dont have knowledge about that.

Thanks for help.

View 9 Replies View Related

How Does Primary Key (primarykey) Work? Can Only Use Once?

Mar 15, 2007

I want to know if this primary key behavior is unique to Access or if it is THE WAY that all industry-standard primary keys work. Thus, I hope not to get too messed up on my SQL Server 2005 project--or any other SQL project.

In Access, I set up a table 'tbl_Gen_Admin_sto' to store general administration expenses. This is a budget. So, there can be at most one entry per budget_year / cost_center / account_number / month. If there are two or more entries, the data is messed up, something is wrong.

Therefore, I defined a muliple-field primary key for tbl_Gen_Admin_sto. If I make a programming mistake and enter data multiple times, the primary key feature will stop my program from entering the same data multiple times.

As I often do, I copy the data into another table, in this case tbl_Gen_Admin_tmp for manipulation in a form. (I.e., I connect a form to tbl_Gen_Admin_tmp.) The tmp version is a pivoted version of the sto table. So, in sto, I have one amount field for each month. In tmp, I have no month field, but do have one amount field for every month of the year (12 amount fields).

The transfer of data to tmp went fine. The user adds, changes, and deletes records in tmp through the form. When the user is done and wants to save the changes (and not discard changes), my program deletes the selected records from sto, then inserts the records from tmp.

But lo! Access was not accepting the new records because (error message) "key violations". The delete went fine. But the insert would not take.

I deleted the multiple-field primary key index in sto. Then I created a multiple-field regular index with no duplicates allowed. Now, sto accepts the insert.

I thought that when I deleted the records, the primary key field is gone--zzzzpft! But aparently Access is keeping track of all primary keys. Even though I deleted the record, for all eternity I will never ever be able to re-insert with the same primary key.

Here is where I need your help: Am I going to run into this same primary key behavior in SQL Server 2005? If so, is it because I have encountered the fundamental definition of primary key?

(I hope you don't beat me up too much for abusing primary keys.)



View 6 Replies View Related

How To Avoid PrimaryKey Duplication?

Aug 19, 2007

Hi, I having a problem with my query...

I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...

I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...

What i'm doing is like this:

Use osa (Destination Database)
Go

DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)

My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.

How is this possible sir? Anyone care to help? Thanks in advance! More Power!

Best Regards

View 10 Replies View Related

How To Add Record With Same Primarykey That User Deleted

Feb 28, 2007

I have a sql server database that she deleted a record with ID as 2873. I would like to try to add this record manually, but the primary key can't be edit. How can i add this record with the same ID as she deleted?

Many thanks.

View 1 Replies View Related

Abort Insertcommand

Apr 1, 2007

hey. I have a formview - inertitemtemplate. In here I have a button with CommandName="Insert". In code behind under FormView1.ItemInserting I want to be able to abort the inserting in some cases. Is this possible? 

View 1 Replies View Related

Two Inserts In One InsertCommand

Apr 11, 2007

I'm using a SQLDataSource and trying to do two inserts into two different tables with one InsertCommand, but it's not working. Here's the code I'm trying to use. Do you see anything wrong with the syntax? I keep getting an error that says error near ','  but I can't figure out why. Thanks
 
InsertCommand="INSERT INTO [OurProjects] ([Title], [Description], [Location], [Anchors], [Size], [Developer], [DesignBuilder], [Architect], [ImageName], [MapName], [ProjectTypeAbbrev], [Deleted]) VALUES (@Title, @Description, @Location, @Anchors, @Size, @Developer, @DesignBuilder, @Architect, @ImageName, @MapName, @ProjectTypeAbbrev, @Deleted),
INSERT INTO [OurProjectsImages] ([OurProjectsID], [ImageMonthName], [SwfName]) VALUES (@OurProjectsID, @ImageMonthName, @SwfName)"

View 2 Replies View Related

InsertCommand, Add Strings...

Nov 23, 2007

I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table.
 so like
InsertCommand="INSERT INTO customer_mod (phone)  Values (@Telephone_1)
 <asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" />
 I don't think that is gonna work, so can you please help me make that code work?

View 3 Replies View Related

How Can I Add 'InsertCommand' In SqlDataSource?

Dec 17, 2007

I need to add an 'InsertCommand' to my query via sqldatasource, but i cannot see this option, i only have the 'order', 'where' and 'advanced' option, could you please advice?

View 5 Replies View Related

Insertcommand @value Null...

Apr 29, 2008

My problem is when trying to insert it's coming back that "Column 'textmenu' cannot be null" who is right for that fieldCode below: <script language="vb" runat="server">    sub Page_Load(s as Object, e as EventArgs)        if ( Page.IsPostBack ) then            Exit sub        end if    end sub    private sub SubmitBtn_Click(s As Object, e As EventArgs)        SqlDataSource1.Insert()    end sub</script>    <asp:sqldatasource id="SqlDataSource1" runat="server"        ConnectionString="<%$ ConnectionStrings:test %>" ProviderName="<%$ ConnectionStrings:test.ProviderName %>"            insertcommand="INSERT INTO test (textmenu) VALUES (@somevalue)">        <insertparameters>            <asp:ControlParameter PropertyName="text" ControlID="CompanyNameBox" Name="somevalue" Type="String" />        </insertparameters>    </asp:sqldatasource>    <asp:textbox id="CompanyNameBox" runat="server" />    <asp:Button id="submitButton" Text="Envoyer" OnClick="SubmitBtn_Click" runat="server"/></asp:Content>  So I change the constrain to accept "null" value then I check in the table database and there is a record but no valueSo I change the sql command to "INSERT INTO test (textmenu) VALUES ('test')" and it's working I have a record and the value "test" in the tableI don't know what I'm doing wrong up here with the @value. Can anyone help? Thanks  

View 10 Replies View Related

A SqlDataSource Can Execute Two InsertCommand??

May 3, 2007

hi everyone
i have a SqlDataSourceand i want execute two T-Sql (two InsertCommand)but not successHow did i do this?
Thanks
1      Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted2            Dim SDSTemp As SqlDataSource = Nothing3            Try4                SDSTemp = New SqlDataSource(CnStr, "")5                SDSTemp.InsertCommandType = SqlDataSourceCommandType.Text6                SDSTemp.InsertCommand = "INSERT INTO Table1 (id,t1,t2) VALUES (@id,@t1,@t2)"7                Dim id As String = e.Command.Parameters("@id").Value.ToString8                SDSTemp.InsertParameters.Add("id", id)9                SDSTemp.InsertParameters.Add("t1", CType(FormView1.FindControl("TextBox1"), TextBox).Text)10               SDSTemp.InsertParameters.Add("t2", CType(FormView1.FindControl("TextBox2"), TextBox).Text)11               SDSTemp.Insert()12   13              SDSTemp.InsertCommand = "INSERT INTO Table2 (id) VALUES (@id)"14   15              SDSTemp.InsertParameters.Add("id", id)16   17              SDSTemp.Insert()18           Catch ex As Exception19               Message.Text = ex.Message.ToString20           End Try21       End Sub22    

View 1 Replies View Related

InsertCommand Using Data From A Second SqlDataSource - ASP.NET 2.0

Nov 29, 2007

I have a process that inserts a new record using the InsertCommand of a SqlDataSource.  As part of the process, I need to insert data the is available in a different SqlDataSource.  I was trying this with the Insert Parameter:

View 1 Replies View Related

NVarchar And SelectedValue And InsertCommand

Dec 19, 2007

Hi guys,I've got a  problem  inserting data into my db.I've created a NVARCHAR column and I'm using SelectedValue Parameters.I only have a problem in the INSERT mode.The UPDATE and DELETE are working fine.All the fields can be updated or deleted, but I can't insert new data inside my db.I've changed one column to NVARCHAR :   "reference"I use NVARCHAR because I have some Arabic Fields (unicode) into my db.But I've copy-pasted everything about the SqlDataSource.10x a lot anyway !  ASP.NET using MS Visual Studio 2005 :.... <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"        OldValuesParameterFormatString="original_{0}"        OnDeleted="SqlDataSource2_Deleted"        OnUpdated="SqlDataSource2_Updated"        OnInserted="SqlDataSource2_Inserted"        SelectCommand=            "SELECT [reference], [ddf], [description], [quantity], [pru], [supname], [catname]                FROM [Products]                WHERE ([reference] = @reference)"        InsertCommand="INSERT INTO [Products]                ([reference], [ddf], [description], [quantity], [pru], [supname], [catname])                VALUES (@reference, @ddf, @description, @quantity, @pru, @supname, @catname)"        UpdateCommand="UPDATE [Products]                SET [ddf] = @ddf,                    [description] = @description,                    [quantity] = @quantity,                    [pru] = @pru,                    [supname] = @supname,                    [catname] = @catname               WHERE [reference] = @original_reference"               DeleteCommand="DELETE FROM [Products] WHERE [reference] = @original_reference">                <SelectParameters>            <asp:ControlParameter ControlID="GridView1" Name="reference" PropertyName="SelectedValue" Type="String" />              </SelectParameters>                <InsertParameters>            <asp:Parameter Name="reference"                Type="String" />            <asp:Parameter Name="ddf"                Type="DateTime" />             <asp:Parameter Name="description"                Type="String" />            <asp:Parameter Name="quantity"                Type="String" />            <asp:Parameter Name="pru"                Type="Decimal" />              <asp:Parameter Name="supname"                Type="String" />              <asp:Parameter Name="catname"                Type="String" />        </InsertParameters>                <UpdateParameters>            <asp:Parameter Name="ddf"                Type="DateTime" />             <asp:Parameter Name="description"                Type="String" />            <asp:Parameter Name="quantity"                Type="String" />            <asp:Parameter Name="pru"                 Type="Decimal" />              <asp:Parameter Name="supname"                Type="String" />              <asp:Parameter Name="catname"                Type="String" />            <asp:Parameter Name="original_reference"                Type="String" />           </UpdateParameters>                <DeleteParameters>            <asp:Parameter Name="original_reference"                Type="String" />        </DeleteParameters>            </asp:SqlDataSource>SQL SERVER 2005 : Create Database FileUSE masterGOIF EXISTS(SELECT * FROM sysdatabases        WHERE name='Products')    DROP DATABASE ProductsGOCREATE DATABASE ProductsON (    NAME=Product,    FILENAME = 'C:WebAppApp_DataProducts.mdf',    SIZE=10 )GOUSE ProductsGOCREATE TABLE Categories (    catname         VARCHAR(25)     NOT NULL,    PRIMARY KEY (catname) )GOCREATE TABLE Suppliers (    supname        VARCHAR(25)    NOT NULL,    tel            VARCHAR(50)    ,    cell        VARCHAR(50)    ,    fax            VARCHAR(50)    ,    pob            VARCHAR(25)    ,    address        VARCHAR(300)    ,    nearby        VARCHAR(100)    ,    website        VARCHAR(100)    ,    email        VARCHAR(100)    ,    skypephone    VARCHAR(100)    ,    PRIMARY KEY (supname) )GOCREATE TABLE Products (    reference     NVARCHAR(25)     NOT NULL,    ddf         DATETIME        NOT NULL,    description VARCHAR(50)     NOT NULL,    quantity     VARCHAR(10)     NOT NULL,    pru         MONEY             NOT NULL,    supname        VARCHAR(25)    NOT NULL,    catname        VARCHAR(25)        NOT NULL,    pv            MONEY            NOT NULL,    PRIMARY KEY(reference),    FOREIGN KEY(catname) REFERENCES Categories(catname),    FOREIGN KEY(supname) REFERENCES Suppliers(supname) )GO 

View 3 Replies View Related

Sqldatasource InsertCommand && Oracle Sequence.

Jan 10, 2006

I cannot get an Oracle sequence to work in an Sqldatasource InsertCommand:I've tried the following:
InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)And: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (:Testing,:CUSTOMER_ID)<asp:Parameter DefaultValue="whsts_id.nextval" Name="testing" />AndInsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)
This is the classic error I get:ORA-01036: illegal variable name/numberI did review the asp.net forums before posting this.  As well, I've looked through the VWD 2005 documentation and cannot find the answer to this question.  Argg!  Any help is appreciated.   I've also worked to try and find out how to view the SQL that the Sqldatasource is generating, but I can't find the answer to this either.

View 8 Replies View Related

Change The InsertCommand Of A Datasource Before Insert

Jun 8, 2006

Hi,
I am trying to set the InsertCommand of a SqlDataSource prior to a new record being created in DetailsView. It has to be changed because certain fields are not being used in one scenerio and it is causing Null's to be written to the database.
Here is what I have tried:
I set the Insert command on page Load:
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource3.InsertCommand = "Insert into table (name,realName,type,extraValue) VALUES (@name,@realName,@type,@extraValue)";
}
But I don't want to insert the "extraValue if a certain condition is true so I want to change the insert command to SqlDataSource3.InsertCommand = "Insert into table (name,realName,type) VALUES (@name,@realName,@type)
I tried to do that in the following:
protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)
{
SqlDataSource3.InsertCommand = "Insert into table (name,realName,type,extraValue) VALUES (@name,@realName,@type,@extraValue)";
if (e.Exception != null)
{
ErrorMessageLabel.Text = "An error occured while entering this record. Please verify you have entered data in the correct format.";
e.ExceptionHandled = true;
Response.Write(e.Exception);
}
GridView1.DataBind();
GridView1.DataBind();
}
But it doesn't seem to change,
Any Help would be appreciated,
Doug

View 5 Replies View Related

Help On Using SqlDataAdapter InsertCommand Targeting LocalSqlServer Connection

Apr 29, 2008

 I am trying to use a sqldataadapter to log a record to a table with a stored procedure call. I am getting the following error: "System.NullReferenceException: Object reference not set to an instance of an object.
at ContactUs.LogEmail() in c:inetpubwwwrootMDWelcomeContactUs.aspx.vb:line 55"Here is my code: Protected Sub LogEmail()
'Trace.IsEnabled = True
Dim strConnect As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString
Dim strCommandText As String = "dbo.EmailsLogged_Insert"
Dim objConnect As New SqlConnection(strConnect)
Try
objConnect.Open()
Dim objDataAdapter As New SqlDataAdapter(strCommandText, objConnect)
objDataAdapter.InsertCommand.CommandText = strCommandText'this is line 55 in my source where error is thrown
objDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure
With objDataAdapter.InsertCommand.Parameters
.Add("@emailaddress", SqlDbType.VarChar, 255).Value = UsersEmail.Text
.Add("@subject", SqlDbType.VarChar, 255).Value = Subject.Text
.Add("@company", SqlDbType.VarChar, 255).Value = Company.Text
.Add("@phone", SqlDbType.VarChar, 255).Value = Phone.Text
.Add("@emailbody", SqlDbType.VarChar, 255).Value = Body.Text

End With
objDataAdapter.InsertCommand.ExecuteNonQuery()
objConnect.Close() 'then close the connection
Catch SQLError As SqlException
Response.Write("<H1>SQL Exception:</H1>")
Response.Write("<h2>" & SQLError.Procedure & "</h2><br />")
Response.Write("<h2>" & SQLError.Message & "</h2><br />")
Dim lineNum As Integer
Dim errMsgArray() As String = Split(SQLError.StackTrace, vbCrLf, , Microsoft.VisualBasic.CompareMethod.Text)
Do While lineNum <= errMsgArray.Length - 1
If Trim$(errMsgArray(lineNum)) <> "" Then
Response.Write("<br>" & errMsgArray(lineNum))
End If
lineNum = lineNum + 1
Loop
Catch objError As Exception
Dim oMsg As String
'display error details
oMsg = "<b>* Error while accessing data</b>.<br />" _
& objError.Message & "<br />" & objError.Source

Trace.Write(oMsg)
Response.Write(oMsg)
Response.End()
End Try
End Sub
 I think the connection is OK (debugging shows that objConnect.State is open) but I am unclear why this is incorrect code here. 

View 2 Replies View Related

Passing A String Into The InsertCommand Of SqlDataSource At The @color Character

Apr 27, 2007

Ok, so I'm a JSP guy and thing it should be easy to replace "@color" with t_color after I initialized it to red by         String t_color = "red";and then calling the insert         SqlDataSource1.Insert();here is insert command:             InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"  I've tried       InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "+ t_color+")"  Ive tried        InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "<%$ t_color %>" )"   Is there any easy way to do this? or Can I set it like @color = t_color?  Thanks in advance for ANY help JSP turning ASP (Maybe)Dan 

View 4 Replies View Related

ExecuteNonQuery In Asp.net 2.0

Jan 20, 2008

Dear;
       I got a problem executenonquery in asp.net 2.0.  Below as my Code:
1    Dim conn As New SqlConnection(tmpconn)2    Dim cmd1 As New SqlCommand("SP_RPTFABTRANSFER_DYEING_PREV", conn)3    4    cmd1.CommandType = CommandType.StoredProcedure5    cmd1.CommandTimeout = 9006    cmd1.Parameters.Add(New SqlParameter("@aSTDATE", SqlDbType.VarChar, 10))7    cmd1.Parameters.Add(New SqlParameter("@aEDDATE", SqlDbType.VarChar, 10))8    cmd1.Parameters.Add(New SqlParameter("@aBUYERID", SqlDbType.VarChar, 10)) 9    cmd1.Parameters.Add(New SqlParameter("@aFACTORYID", SqlDbType.VarChar, 10))10   cmd1.Parameters.Add(New SqlParameter("@aFabGrpId", SqlDbType.VarChar, 10))11   cmd1.Parameters.Add(New SqlParameter("@aFABSUPPIDFROM", SqlDbType.VarChar, 10))12   cmd1.Parameters.Add(New SqlParameter("@aFABSUPPIDTO", SqlDbType.VarChar, 10))13   cmd1.Parameters.Add(New SqlParameter("@aUSERID", SqlDbType.VarChar, 20))14   cmd1.Parameters.Add(New SqlParameter("@aDelType", SqlDbType.VarChar, 20))15   16   cmd1.Parameters("@aSTDATE").Value = lstartdt17   cmd1.Parameters("@aEDDATE").Value = lenddt18   cmd1.Parameters("@aBUYERID").Value = Trim(dropBuyer.SelectedValue)19   cmd1.Parameters("@aFACTORYID").Value = Trim(dropFactory.SelectedValue)20   cmd1.Parameters("@aFabGrpId").Value = lFabGrp21   cmd1.Parameters("@aFABSUPPIDFROM").Value = fabFrom22   cmd1.Parameters("@aFABSUPPIDTO").Value = fabTo23   cmd1.Parameters("@aUSERID").Value = Session("UID").ToString24   cmd1.Parameters("@aDelType").Value = lDelType25   Try26     conn.Open()27     cmd1.ExecuteNonQuery()28     conn.Close()29   Catch ex As Exception30     lblerr.Visible = True31     lblerr.Text = ex.Message32   Finally33     conn.Close()34   End Try
Web.Config<add name="oldtextileConnectionString" connectionString="Data Source=xx.xx.xx.xx;Initial Catalog=ERP;Integrated Security=TRUE;Connection Lifetime=0;Min Pool Size =0;Max Pool Size=1000;Pooling=true;" providerName="System.Data.SqlClient"/> 
       This Code running on asp only 2min Execute time.  But i try it on Asp.net 2.0 take a long time about 15min or request time out error.  Could any one can give me some tips or hits?  Help much appreciated.  Thanks

View 5 Replies View Related

ExecuteNonQuery For Sql2005

Sep 7, 2006

I hope you would help me in this problem. I use the code below for executenonquery command for mdb DB.But I do not know the changes I should made when Using SQL2005.-------------Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _        "Data Source=C:ASPNET20dataNorthwind.mdb"    Dim dbConnection As New OleDbConnection(connectionString)    dbConnection.Open()    Dim commandString As String = "INSERT INTO Employees(FirstName, LastName) " & _        "Values(@FirstName, @LastName)"    Dim dbCommand As New OleDbCommand(commandString, dbConnection)    Dim firstNameParam As New OleDbParameter("@FirstName", OleDbType.VarChar, 10)    firstNameParam.Value = txtFirstName.Text    dbCommand.Parameters.Add(firstNameParam)    Dim lastNameParam As New OleDbParameter("@LastName", OleDbType.VarChar, 20)    LastNameParam.Value = txtLastName.Text    dbCommand.Parameters.Add(LastNameParam)    dbCommand.ExecuteNonQuery()    dbConnection.Close()--------

View 2 Replies View Related

ExecuteNonQuery Syntax

Sep 11, 2006

Hi, I am trying to execute a nonquery as follows (look for bold):Dim connStringSQL As New SqlConnection("Data Source=...***...Trusted_Connection=False")'// Create the new OLEDB connection to Indexing ServiceDim connInd As New System.Data.OleDb.OleDbConnection(connStringInd)Dim commandInd As New System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)Dim commandSQL As New SqlCommand("GetAssetList2", connStringSQL)commandSQL.CommandType = Data.CommandType.StoredProcedureDim resultDS As New Data.DataSet()Dim resultDA As New SqlDataAdapter()'// Fill the dataset with valuescommandInd.Fill(resultDS)'// Get the XML values of the dataset to send to SQL server and run a new query...'// Return the number of resultsresultCount.Text = source.Count.ToStringresults.DataSource = sourceresults.DataBind()'// Record the searchcommandSQL = New SqlCommand("RecordSearch", connStringSQL)commandSQL.Parameters.Clear()commandSQL.Parameters.Add("@userName", Data.SqlDbType.VarChar, 50).Value = authUser.Text()commandSQL.Parameters.Add("@createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()commandSQL.Parameters.Add("@numRows", Data.SqlDbType.Int, 1000).Value = resultCount.TextcommandSQL.Parameters.Add("@searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.TextconnStringSQL.Open()commandSQL.ExecuteNonQuery()connStringSQL.Close() The stored procedure looks like this:Use GTGAssetsDROP PROC dbo.RecordSearch;--New ProcedureGOCREATE PROC dbo.RecordSearch(@userName varchar(50),@createdDateTime DateTime,@numRows varchar(1000),@searchString varchar(1000))ASBEGINSET NOCOUNT ONINSERT INTO SearchLog (SearchString, CreatedByUser, CreatedDTTM, RowsReturned) VALUES (@searchString, @userName, @createdDateTime, @numRows)ENDGOAny ideas as to why this error is appearing? Incorrect syntax near 'RecordSearch'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'RecordSearch'.Source Error: Line 169: commandSQL.Parameters.Add("@searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.Text
Line 170: connStringSQL.Open()
Line 171: commandSQL.ExecuteNonQuery()
Line 172: connStringSQL.Close()
Line 173: End IfMany thanks!James

View 5 Replies View Related

ExecuteNonQuery Error

Dec 9, 2006

When I try to insert a record with the ExecuteNonQuery command, I get the following error information. Any clues why? Thanks.
SSqlException was unhandled by user code...Message="Incorrect syntax near [output of one of my field names]."...[Item detail:] In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
My code:
Private objCmd As SqlCommandPrivate strConn As New SqlConnection(ConfigurationManager.AppSettings("conn"))...objCmd = New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _"VALUES('" & strUser & "','" & strFName.Text & "','" & strLName.Text & "', '" & strLang.Text & "', '" & strCtry.Text & "', '" & strPhone.Text & "'" _, strConn)strConn.Open()objCmd.ExecuteNonQuery()

View 17 Replies View Related

Error In ExecuteNonQuery()

Dec 12, 2006

Hi,I am developing a small application where in I need to take a few data from the user and put it on the DB. I have stored procs for the same. I am getting an error when I execute the ExecuteNonQuery() command. the error is as follows:
System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Intranet_New.leaveForm.btnSubmit_Click(Object sender, EventArgs e) in c:inetpubwwwrootintranet_newleaveform.aspx.cs:line 138
 Snippet of code:
try
{
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SqlCon"]);
cmd = new SqlCommand();
cmd.Connection = con;
 
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_InsertIntoLeave";
cmd.Parameters.Add("@empid", SqlDbType.Char, 20);
cmd.Parameters["@empid"].Value = txtEmplyId.Text;
cmd.Parameters.Add("@empName", SqlDbType.NVarChar, 50);
cmd.Parameters["@empName"].Value = txtName.Text;
cmd.Parameters.Add("@LeaveFrom", SqlDbType.DateTime);
string str_LeaveFrom = ddlDay.SelectedValue + "/" +ddlMonth.SelectedValue + "/" + ddlYear.SelectedValue;
DateTime LF = new DateTime();
LF = DateTime.Parse(str_LeaveFrom);
string LeaveFrom1 = (LF.ToShortDateString());
cmd.Parameters["@LeaveFrom"].Value = LeaveFrom1;
cmd.Parameters.Add("@LeaveTo", SqlDbType.DateTime);
string str_LeaveTo = ddltoDay.SelectedValue + "/" + ddltoMonth.SelectedValue + "/" + ddltoYear.SelectedValue;
DateTime LT = new DateTime();
LT = DateTime.Parse(str_LeaveTo);
string LeaveTo1 = (LT.ToShortDateString());
cmd.Parameters["@LeaveTo"].Value = LeaveTo1;
cmd.Parameters.Add("@TotalDays", SqlDbType.BigInt);
cmd.Parameters["@TotalDays"].Value = txtNoofDays.Text;
cmd.Parameters.Add("@TypeOfLeave", SqlDbType.NVarChar, 50);
cmd.Parameters["@TypeOfLeave"].Value = rbtnType.SelectedValue;
cmd.Parameters.Add("@ReasonOfLeave", SqlDbType.NVarChar, 1000);
cmd.Parameters["@ReasonOfLeave"].Value = txtReason;
con.Close();
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Stored proc is as follows:
 
ALTER PROCEDURE dbo.SP_InsertIntoLeave
 
(
@empid as char(20), @empName as nvarchar(50), @totalLeave as decimal(9) = 12, @LeaveFrom as datetime,
@LeaveTo as datetime, @TotalDays as bigint, @TypeOfLeave as nvarchar(50), @ReasonOfLeave as nvarchar(1000),
@RemainigLeave as decimal(9)
)
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
 
INSERT INTO Leave_Table
(
emp_id, emp_Name, Total_Leave, Leave_From, Leave_To, Total_no_of_Days, Type_of_Leave, Reason_of_Leave,
Leave_Remaining
)
VALUES
(
@empid, @empName, @totalLeave, @LeaveFrom, @LeaveTo, @TotalDays, @TypeOfLeave, @ReasonOfLeave,
@RemainigLeave
)
RETURN
Thanks in Advance.

View 1 Replies View Related

Problem With ExecuteNonQuery

Jul 3, 2007

I have created a stored procedure that takes several parameters and ultimately does an INSERT on two tables. The sp returns with an integer indicating which is positive if one or more rows were added.
If I execute the SP by hand using the SQL Server Management Studio Express I get the proper results, the records are added to both tables and the return values are proper. One is an output parameter indicating the Identity value of the main record, the return value simply >0 if OK.
However, when I use C#, build my connection, command and its associated parameters making sure they match the SP then I get a malfunction.
The problem is that when I call ExecuteNonQuery the integer value it returns is -1 even though calling it from Mgmt. Studio gives a >0 result. Even though it returns -1 I can confirm that the records were added to BOTH tables and that the output parameter (The identity) given to me is also correct. However the return value is always -1.
I have no idea what is going wrong, Since I have SQL Express 2005 I do cannot do profiling :(. I really don't see why this goes wrong and I think using ExecuteScalar is not the best choice for this type of action.
 

View 3 Replies View Related

Cmd.ExecuteNonQuery() - Max Length

Oct 22, 2007

what the max length that I can run query to sql Server?example: 

View 2 Replies View Related

Problem With ExecuteNonQuery

Feb 14, 2008

HI
I am using ExecuteNonQuery to run an UPDATE statement but i keep getting the following error message: "ExecuteNonQuery: Connection property has not been initialized. "
this is my code can anyone see what is wrong?
 
DBCommand.CommandType = CommandType.TextDBCommand.CommandText = queryCourse
DBConnection.Open()
ExecuteNonQuery = DBCommand.ExecuteNonQuery()
DBCommand.ExecuteNonQuery()
DBConnection.Close()
 help would be appreciated.

View 1 Replies View Related

Cmd.ExecuteNonQuery Question..help Please

Apr 29, 2007

I have a DataAccess that has "cmd.ExecuteNonQuery" and another file SqlTableProfileProvider for a SandBox project(from ASP.NET) for a Custom TableProfileProvider. That also has a cmd.ExecuteNonQuery even thou these are 2 different files i get an error after debug.



Question is are they conflicting ? if so how can i fix this.



Code Snippets for both provided below the 2nd cmd.ExecuteNonQuery that is producing the Error is Commented in Orange...Thxs for the help Rattlerr


Incorrect syntax near ','.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.

Source Error:





Line 454: cmd.CommandType = CommandType.Text;
Line 455:
Line 456: cmd.ExecuteNonQuery();
Line 457:
Line 458: // Need to close reader before we try to update



[SqlException (0x80131904): Incorrect syntax near ','.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
Microsoft.Samples.SqlTableProfileProvider.SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) in d:Programming ProgramsXtremesystemsXtremesystemsxsApp_CodeSqlTableProfileProvider.cs:456
System.Configuration.SettingsBase.SaveCore() +379
System.Configuration.SettingsBase.Save() +77
System.Web.Profile.ProfileBase.SaveWithAssert() +31
System.Web.Profile.ProfileBase.Save() +63
System.Web.Profile.ProfileModule.OnLeave(Object source, EventArgs eventArgs) +2374047
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64


DataAccess.cs::






Code Snippet

public abstract class DataAccess

{

private string _connectionString = "";

protected string ConnectionString

{

get { return _connectionString; }

set { _connectionString = value; }

}

private bool _enableCaching = true;

protected bool EnableCaching

{

get { return _enableCaching; }

set { _enableCaching = value; }

}

private int _cacheDuration = 0;

protected int CacheDuration

{

get { return _cacheDuration; }

set { _cacheDuration = value; }

}

protected Cache Cache

{

get { return HttpContext.Current.Cache; }

}

protected int ExecuteNonQuery(DbCommand cmd)

{

if (HttpContext.Current.User.Identity.Name.ToLower() == "sampleeditor")

{

foreach (DbParameter param in cmd.Parameters)

{

if (param.Direction == ParameterDirection.Output ||

param.Direction == ParameterDirection.ReturnValue)

{

switch (param.DbType)

{

case DbType.AnsiString:

case DbType.AnsiStringFixedLength:

case DbType.String:

case DbType.StringFixedLength:

case DbType.Xml:

param.Value = "";

break;

case DbType.Boolean:

param.Value = false;

break;

case DbType.Byte:

param.Value = byte.MinValue;

break;

case DbType.Date:

case DbType.DateTime:

param.Value = DateTime.MinValue;

break;

case DbType.Currency:

case DbType.Decimal:

param.Value = decimal.MinValue;

break;

case DbType.Guid:

param.Value = Guid.Empty;

break;

case DbType.Double:

case DbType.Int16:

case DbType.Int32:

case DbType.Int64:

param.Value = 0;

break;

default:

param.Value = null;

break;

}

}

}

return 1;

}

else

return cmd.ExecuteNonQuery();

}

protected IDataReader ExecuteReader(DbCommand cmd)

{

return ExecuteReader(cmd, CommandBehavior.Default);

}

protected IDataReader ExecuteReader(DbCommand cmd, CommandBehavior behavior)

{

return cmd.ExecuteReader(behavior);

}

protected object ExecuteScalar(DbCommand cmd)

{

return cmd.ExecuteScalar();

}



SecondFile::








Code Snippet

public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) {

string username = (string)context["UserName"];

bool userIsAuthenticated = (bool)context["IsAuthenticated"];

if (username == null || username.Length < 1 || collection.Count < 1)

return;

SqlConnection conn = null;

SqlDataReader reader = null;

SqlCommand cmd = null;

try {

bool anyItemsToSave = false;

// First make sure we have at least one item to save

foreach (SettingsPropertyValue pp in collection) {

if (pp.IsDirty) {

if (!userIsAuthenticated) {

bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];

if (!allowAnonymous)

continue;

}

anyItemsToSave = true;

break;

}

}

if (!anyItemsToSave)

return;

conn = new SqlConnection(_sqlConnectionString);

conn.Open();

List<ProfileColumnData> columnData = new List<ProfileColumnData>(collection.Count);

foreach (SettingsPropertyValue pp in collection) {

if (!userIsAuthenticated) {

bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];

if (!allowAnonymous)

continue;

}

//Normal logic for original SQL provider

//if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to



//Can eliminate unnecessary updates since we are using a table though

if (!pp.IsDirty)

continue;

string persistenceData = pp.Property.Attributes["CustomProviderData"] as string;

// If we can't find the table/column info we will ignore this data

if (String.IsNullOrEmpty(persistenceData)) {

// REVIEW: Perhaps we should throw instead?

continue;

}

string[] chunk = persistenceData.Split(new char[] { ';' });

if (chunk.Length != 2) {

// REVIEW: Perhaps we should throw instead?

continue;

}

string columnName = chunk[0];

// REVIEW: Should we ignore case?

SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true);

object value = null;

// REVIEW: Is this handling null case correctly?

if (pp.Deserialized && pp.PropertyValue == null) { // is value null?

value = DBNull.Value;

}

else {

value = pp.PropertyValue;

}

// REVIEW: Might be able to ditch datatype

columnData.Add(new ProfileColumnData(columnName, pp, value, datatype));

}

// Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table

Guid userId = Guid.Empty;

cmd = new SqlCommand("SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '" + AppId + "' AND u.UserName = LOWER(@Username)", conn);

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@Username",username);

try {

reader = cmd.ExecuteReader();

if (reader.Read()) {

userId = reader.GetGuid(0);

}

else {

reader.Close();

cmd.Dispose();

reader = null;

cmd = new SqlCommand("dbo.aspnet_Users_CreateUser", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ApplicationId", AppId);

cmd.Parameters.AddWithValue("@UserName", username);

cmd.Parameters.AddWithValue("@IsUserAnonymous", !userIsAuthenticated);

cmd.Parameters.AddWithValue("@LastActivityDate", DateTime.UtcNow);

cmd.Parameters.Add(CreateOutputParam("@UserId", SqlDbType.UniqueIdentifier, 16));

cmd.ExecuteNonQuery();

userId = (Guid)cmd.Parameters["@userid"].Value;

}

}

finally {

if (reader != null) {

reader.Close();

reader = null;

}

cmd.Dispose();

}

// Figure out if the row already exists in the table and use appropriate SELECT/UPDATE

cmd = new SqlCommand(String.Empty, conn);

StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(_table);

sqlCommand.Append(" WHERE UserId = @UserId) ");

cmd.Parameters.AddWithValue("@UserId", userId);

// Build up strings used in the query

StringBuilder columnStr = new StringBuilder();

StringBuilder valueStr = new StringBuilder();

StringBuilder setStr = new StringBuilder();

int count = 0;

foreach (ProfileColumnData data in columnData) {

columnStr.Append(", ");

valueStr.Append(", ");

columnStr.Append(data.ColumnName);

string valueParam = "@Value" + count;

valueStr.Append(valueParam);

cmd.Parameters.AddWithValue(valueParam, data.Value);

// REVIEW: Can't update Timestamps?

if (data.DataType != SqlDbType.Timestamp) {

if (count > 0) {

setStr.Append(",");

}

setStr.Append(data.ColumnName);

setStr.Append("=");

setStr.Append(valueParam);

}

++count;

}

columnStr.Append(",LastUpdatedDate ");

valueStr.Append(",@LastUpdatedDate");

setStr.Append(",LastUpdatedDate=@LastUpdatedDate");

cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);

sqlCommand.Append("BEGIN UPDATE ").Append(_table).Append(" SET ").Append(setStr.ToString());

sqlCommand.Append(" WHERE UserId = '").Append(userId).Append("'");

sqlCommand.Append("END ELSE BEGIN INSERT ").Append(_table).Append(" (UserId").Append(columnStr.ToString());

sqlCommand.Append(") VALUES ('").Append(userId).Append("'").Append(valueStr.ToString()).Append(") END");

cmd.CommandText = sqlCommand.ToString();

cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery(); //THIS cmd.ExecuteNonQuery Produces the Error

// Need to close reader before we try to update

if (reader != null) {

reader.Close();

reader = null;

}

UpdateLastActivityDate(conn, userId);

}

finally {

if (reader != null)

reader.Close();

if (cmd != null)

cmd.Dispose();

if (conn != null)

conn.Close();

}

}

View 17 Replies View Related

ExecuteNonQuery Failing

Apr 21, 2008

I have an interesting error that shows up on Windows Mobile 6.0 devices but does not appear to affect Windows Mobile 5.0 devices. The application runs in .NET CF 2.0 on SQL CE 3.0 using Merge Replication. When the application starts, there is an ExecuteNonQuery prior to calling the first synchronization. It appears that once the application has replicated that you can no longer create a connection to the database and run an ExecuteNonQuery successfully. If you have already created the SqlCeConnection and just open and close it as needed that you are able to run ExecuteNonQuery commands.

In Windows Monile 6.0; however, I eventually run out of memory, like there is a leak someplace. Does anyone have any ideas for how you would actually be able to dispose of the SqlCeConnection and recreate it each time? Does calling .Close() release all the native resources on a SqlCeConnection? I am trying out one more modification, I realized the SqlCeReplication object was not being disposed of and I'm guessing this probably also uses native resources; can anyone confirm this would also potentially cause a problem?

The application works but it is aggravating to have to softboot once in awhile to recover the memory, I'd like to move beyond it. Any ideas would be great!

View 2 Replies View Related

ExecuteNonQuery() And Execute In SqlCommand.

Mar 27, 2008

I try to get the value return from GetRandomPosition StoredProc, and it throws a statement saying incorrect syntax.
Error:

Line 1: Incorrect syntax near 'GetRandomPosition'. 
Mark up:

myPuzzleCmd.Execute is used in Classic ASP and it works just fine.

I tried to subsitute with ExecuteReader / ExecuteScalar / ... etc, none of them fix it.

View 13 Replies View Related

ExecuteNonQuery While DataReader Still Open

Aug 1, 2004

Hi all!

I basically need to get some records from a table and while looping through them i need to insert some records on other table.

I keep getting this error:

There is already an open DataReader associated with this connection which must be closed first.

The piece of code that I have is like this:


...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);

//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
...


Also I would like to have the insertions in the same transaction as the previous sql commands.

Thanks a million!

LAM

View 4 Replies View Related

Sql Error - In The SqlCommand.ExecuteNonQuery

Aug 12, 2004

Hi

I have an asp.net [c#] page that queries a database which worked fine until I entered a new field called VSReferenceNumber.

The error message suggest there is something wrong with the sql string, but for the life of me I can't see why it is wrong.

I believe the problem is somewhere here:

SqlString += "VSReferenceNumber = '" + VSReferenceNumber.Replace("'", "''") + "', ";


Any help would be most appreciated.

Many thanks in advance

Regards

Miles


The Error is::

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'VSReferenceNumber'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.workingdatagrid2_aspx.myDataGrid_Update(Object Sender, DataGridCommandEventArgs e) in C:Inetpubwwwrootuapworkingdatagrid2.aspx:line 64

Here's the code::

<script runat="server">

//public string ConnString = "server=atw_data01;database=UAP;uid=atw_cheryl.theobald;pwd=nudibranch;";
public string ConnString = "Server=(local); user id=sa;password=;initial catalog = UAP;";
void BindData()
{
//-- Using the Try statement, we attempt to connect to our
//-- database, execute a SqlDataAdapter to store our data,
//-- populate a dataset and then bind that dataset
//-- to our DataGrid.
try
{
SqlConnection SqlConn = new SqlConnection(ConnString);
string SqlString = "SELECT [uapID], [DealershipName], [AutoExchangeClientID], [VSReferenceNumber] FROM uapForm";
SqlDataAdapter SqlComm = new SqlDataAdapter(SqlString, SqlConn);
DataSet customerData = new DataSet();
SqlComm.Fill(customerData, "uapForm");

myDataGrid.DataSource = customerData;
myDataGrid.DataBind();

SqlConn.Close();
SqlComm.Dispose();
SqlConn.Dispose();
}

//-- If we are not able to connect, display a friendly error
catch (Exception e)
{
ErrorLabel.Text = "Not able to connect to database. See description below: <P>";
ErrorLabel.Text += e.ToString();
}

}

void myDataGrid_Update (object Sender, DataGridCommandEventArgs e)
{
//-- Take the data from each textbox in our editable item
//-- and assign that text to a string variable
string uapID = Convert.ToString(e.Item.Cells[0].Text);
string DealershipName = ((TextBox) e.Item.Cells[1].Controls[0]).Text;
string AutoExchangeClientID = ((TextBox) e.Item.Cells[2].Controls[0]).Text;
string VSReferenceNumber = ((TextBox) e.Item.Cells[3].Controls[0]).Text;


//-- Again, using the Try statement, attempt to connect to our database
//-- and make an update with the data from our datagrid
SqlConnection SqlConn = new SqlConnection(ConnString);
try
{
SqlConn.Open();
string SqlString = "UPDATE uapForm ";
SqlString += "SET DealershipName = '" + DealershipName.Replace("'", "''") + "', ";
SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "' ";
SqlString += "VSReferenceNumber = '" + VSReferenceNumber.Replace("'", "''") + "', ";
SqlString += " WHERE uapID = '" + uapID + "'";
SqlCommand SqlComm = new SqlCommand(SqlString, SqlConn);
SqlComm.ExecuteNonQuery();
SqlConn.Close();
SqlComm.Dispose();
SqlConn.Dispose();
}

//-- If for some reason we cannot connect, display a friendly error.
catch (Exception exc)
{
ErrorLabel.Text = "Not able to connect to database. <br>Please See description below:<P> <P>";
ErrorLabel.Text += exc.ToString();
}

//-- Remove the edit focus
myDataGrid.EditItemIndex = -1;
//-- Rebind our datagrid
BindData();
}

void myDataGrid_Cancel(object Sender, DataGridCommandEventArgs e)
{
//-- Remove the edit focus
myDataGrid.EditItemIndex = -1;
//-- Rebind our datagrid
BindData();
}

void myDataGrid_Edit(object Sender, DataGridCommandEventArgs e)
{
//-- Set the edit focus to the item that was selected
myDataGrid.EditItemIndex = (int) e.Item.ItemIndex;
//-- Rebind our datagrid
BindData();
}

void Page_Load (object Sender, EventArgs e)
{
//-- If the page is not posting back, bind our datagrid
if (!Page.IsPostBack)
{
BindData();
}
}

</script>

View 4 Replies View Related







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