Am I Doin This Right? SELECT / ExecuteNonQuery Statement

Jun 26, 2007

I am working on a web service method that will return weather or not a page url is stored in the database but the  ExecuteNonQuery keeps returning -1. I was just wondering if i was doing anything wrong or why the ExecuteNonQuery method does not return a value of 1 or more indicating that the pageurl exists in the database? I have tried using the SQLDataReader as well to no effect and I have verified that SELECT statement refers to valid table and field names. Any help or pointers would be appreciated. I'm still kind of a newb when it comes to db programming.

 1 <WebMethod()> _
2 Public Function IsPageStored(ByVal pageurl As String) As Boolean
3 If String.IsNullOrEmpty(pageurl) Then Return False
4
5 Dim conn As New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
7
8 Dim cmd As String
9 cmd = "SELECT [" & Constants.SourceFieldName & "] "
10 cmd &= "FROM [" & Constants.StoredCopyTableName & "] "
11 cmd &= "WHERE ([" & Constants.SourceFieldName & "] = '@Source')"
12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase
15
16 conn.Open()
17
18 Dim exists As Boolean = False
19 exists = (C.ExecuteNonQuery > 0)
20
21 conn.Close()
22 C.Dispose()
23 C = Nothing
24 conn.Dispose()
25 conn = Nothing
26
27 Return exists
28 End Function
29
  

View 4 Replies


ADVERTISEMENT

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

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

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

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

22810
8461760

And what I need is (without showing any other field):

Field02

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

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View 1 Replies View Related

Select Statement Problem - Group By Maybe Nested Select?

Sep 17, 2007

Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt 

View 2 Replies View Related

SQL Select Statement To Select The Last Ten Records Posted

Aug 6, 2007

SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View 2 Replies View Related

Using Select Statement Result In If Statement Please Help

Jul 11, 2007

Hello
How can i say this I would like my if statement to say:  if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated

View 2 Replies View Related

If STATEMENT Within Select Statement Syntax

May 15, 2008

Hi,

I am a newbie to this site and hope someone can help....

I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:

if(TL_flag= '1', "yes") as [Trial Leave]

it is coming up with an error.... I can use Select case but I should not need to as this should work?

Any ideas?

View 2 Replies View Related

Can I Use SELECT Statement To Select First 100 Record????

Apr 21, 1999

I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?

Thanks,

Sam

View 1 Replies View Related

Select Statement With Count Within Another Select

Aug 23, 2013

I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).

Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.

SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2

[code]....

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

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

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

SQLCommand.ExecuteNonQuery() Is Always Returning -1

Apr 27, 2006

Hi,
I'm using .net 2.0 and I have created a stored procedure (sql server DB) which has one insert statement with 2 input parameters. When I execute this stored procedure using ExecuteNonQuery(), it always returning -1. I want to know how many records are effected. I want to know if there is any error. Help appreciated.
 
Thanks,
Brett
code looks like this:
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = "myString...";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "usp_InsertApplicationInfo";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = cmd.Parameters.Add("@vchrApplicationName", SqlDbType.VarChar);
param1.Direction = ParameterDirection.Input;
SqlParameter param2 = cmd.Parameters.Add("@vchrApplicationDescription", SqlDbType.VarChar);
param2.Direction = ParameterDirection.Input;
param1.Value = "prama1";
param2.Value = "test";
cmd.Connection = sqlConn;
sqlConn.Open();
int rows = cmd.ExecuteNonQuery();

View 2 Replies View Related

ExecuteNonQuery Fails Catastrophically

Dec 13, 2006

I was trying an RDA Demo project. However, everytime I pull the data from Sql Server 2005 and then move on to add a row to the table using SqlCeCommand.ExecuteNonQuery(), the application fails catastrophically without giving any exception and simply exits!

When I have pulled the data once and this application has shut down (exited) I can open it again and then continue normally but I can't expect the application to PULL the data and then even add or update a row to that table. I can do select queries fine. Why would this happen?

I am disposing all the objects that I use in both the scenarios, or atleast I believe so.
Does any one have any idea?

View 3 Replies View Related

Dataadapter.Update() OR ExecuteNonQuery()

May 18, 2007

Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!

View 5 Replies View Related







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