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


ADVERTISEMENT

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

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

The Best Overloaded Method Match For 'System.Data.SqlClient.SqlCommand.SqlCommand Error

Sep 21, 2006

Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table.  But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: {
Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
Line 26: mySqlDataAdapter.Fill(myDataSet);
Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs    Line: 25 This is my code:using System;
using System.Data;
using System.Data.SqlClient ;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;

public partial class createGeoRSSFile : System.Web.UI.Page
{
protected void Page_Load(object sender, DataSet myDataSet, EventArgs e)
{
string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password";
string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes";

using (SqlConnection mySqlConnection = new SqlConnection(connString))
{
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
mySqlDataAdapter.Fill(myDataSet);
return myDataSet;
}

// Create a new XmlTextWriter instance
XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode);

// Start writing!
writer.WriteStartDocument();
writer.WriteStartElement("item");

// Creating the <town> element
writer.WriteStartElement("town");
writer.WriteElementString("PostCode",myDataSet .Tables[1].Columns("PostCode"));
writer.WriteElementString("geo:lat",myDataSet.Tables[1].Columns("Latitude"));
writer.WriteElementString("geo:lon", myDataSet.Tables[1].Columns("Longitude"));
writer.WriteEndElement();

writer.WriteEndElement();
writer.WriteEndDocument();
writer.Flush();
writer.Close();

}
}What seems to be causing this error?Thanks. 

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

Command.ExecuteNonQuery() Error Occurring NULL Value

Apr 2, 2008

Here is the table that I am wanting to insert the information into (BTW I am wanting the UserID, EntryDate, Note) On my page i do have a text box for the UserID as well as a Note textbox. When I hite the submit button on my page I am already sending the UserID textbox information to be sent to another table (called RequestTable). However, I am wanting to take that same UserID and insert it into the RequestNote table as well. Let me know if you have any questions for me to further explain anything.
**RequestNote**RequestNoteKey (PK)  (has identity set to yes)RequestKey (allows nulls)NoteEntryDateEntryUserID  (allows Nulls)****This is my stored procedure that I am calling called "NoteInsert"***@Note nvarchar(1000),@EntryUserID nvarchar(50)AS INSERT INTO RequestNote (Note, EntryDate,EntryUserID)
VALUES (@Note,GetDate(), @EntryUserID)
RETURNGO
****THIS IS THE PAGE THAT CONNECTS THE USER INTERFACE AND STORED PROCEDURE***public static void AddRequestNote(string requestNote, string userID){using (SqlConnection connection = new SqlConnection(connRequestNote)){using (SqlCommand command = new SqlCommand("NoteInsert", connection)){command.CommandType = CommandType.StoredProcedure;command.Parameters.Add(new SqlParameter("@Note", requestNote));command.Parameters.Add(new SqlParameter("@EntryUserID", userID));connection.Open();command.ExecuteNonQuery(); <--THIS IS WHERE I GET AN ERROR THAT SAYS Cannot insert the value NULL into column 'RequestNoteKey', table 'RequestNote'; column does not allow nulls. INSERT fails}

View 5 Replies View Related

ExecuteNonQuery() Throws An Incorrect Syntax Error

Apr 7, 2008

I try to update from datalist by using sqlcommand, it throws an error on the date field:
Mark up:

10/11/2008 12:00:00 AM        <--- the format in the SQL database
Error: 

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'DOB'.Dim strSQL As String = _
"UPDATE [Customers] SET [country] = @country, " & _"[nickName] = @nickname, [identityid] = @identityid " & _
"[DOB] = @DOB, [Hpno] = @Hpno " & _"[address] = @address, = @email " & _
"WHERE [CustomerID] = @CustomerID"
...Dim parameterdob As SqlParameter = _
New SqlParameter("@DOB", SqlDbType.DateTime)
parameterdob.Value = Request.Form("txtdob") & " 00:00:00"
myCommand.Parameters.Add(parameterdob)
myCommand.ExecuteNonQuery()                                     <---  error here
...
<form id="form1" runat="server">
<asp:DataList ID="ProfileDataList" runat="server">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtdob" Text='<%#DataBinder.Eval(Container.DataItem, "DOB", "{0:d}")%>' />
</ItemTemplate>
</asp:DataList>

View 5 Replies View Related

SqlCommand Error

Oct 17, 2007

Hello,
I am trying to insert a value into a specific row in a table. The error comes from the myCommand2 statement but i don't know how to solve it. Please help!
My code is as follows:SqlCommand myCommand = new SqlCommand("select count(*) from updatetable", myConnection);
 
myReader = myCommand.ExecuteReader();
 while (myReader.Read())
int count = myReader.GetInt32(0);
SqlCommand myCommand2 = new SqlCommand("insert into interface (Total) where description = 'Graphicads'  value ( " + count + ")", myConnection2);myCommand2.CommandType = CommandType.Text;
myCommand2.ExecuteNonQuery();
 
 

View 4 Replies View Related

Passing An SQLcommand To A Asp.net Web Service As Sqlcommand

Feb 10, 2008

Hi

Is it possible To pass an SQL command to a ASp.net web service as system.data.SQLclient.sqlcommand?


That means is ispossible to pass the actuall sql command instead of just the string?

If yes how can you do that??

Cheers

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

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

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

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

Updating A Record: ExecuteNonQuery

Apr 10, 2008

I'm using VB.Net 2008 with SQL Compact 3.5. After trying forever, I finally got my app to open a retrieve a record from a table. Now, when I try to update the record, I get an error on the ExecuteNonQuery statement. The error says I am "attempting to read or write protected memory". The code works perfectly with an Access database.

Here is the code I am using:

Dim objConnection As Data.OleDb.OleDbConnection

Dim objCommand As Data.OleDb.OleDbCommand

Dim sConnection As String

Dim sTableName As String

Try

'Set Table Name

sTableName = "Settings"

'Build SQL and Connection strings

'sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mstrDataPath

sConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & mstrDataPath

objConnection = New Data.OleDb.OleDbConnection(sConnection)

'Create the UpdateCommand

objCommand = New Data.OleDb.OleDbCommand("UPDATE " & sTableName & " SET " & _

"OwnerName = ?, " & _

"Address1 = ?, " & _

"Address2 = ?, " & _

"City = ?, " & _

"State = ?, " & _

"ZipCode = ?, " & _

"Phone = ?, " & _

"EmailAddress = ? ", objConnection)

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(0).Value = mstrOwnerName

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(1).Value = mstrAddress1

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(2).Value = mstrAddress2

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(3).Value = mstrCity

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(4).Value = mstrState

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(5).Value = mstrZipCode

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(6).Value = mstrPhone

objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)

objCommand.Parameters(7).Value = mstrEmailAddress


objConnection.Open()

objCommand.ExecuteNonQuery()

objConnection.Close()

objCommand.Dispose()

UpdateRecord = True

Catch objException As Exception

UpdateRecord = False

MessageBox.Show(objException.ToString, "Error")

End Try

View 21 Replies View Related

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

ExecuteNonQuery: CommandText Property Has Not Been Initialized

May 21, 2007

Hi ,
     Iam new to vs2005. Iam trying to integrate  Authorize.net for transactions in my site. When i tested it worked fine .But when i put it in live for Amex cards it is giving me sqlerror.
Here is my code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Net;
using System.IO;
public partial class Paymentprocessing : System.Web.UI.Page
{SqlConnection objConn = new SqlConnection(ConfigurationManager.AppSettings["strConn"]);
string permLevel = "";protected void Page_Load(object sender, EventArgs e)
{if (Session["displayname"] == null || Session["franchiseid"] == null || Session["username"] == null)
{Response.Redirect("Default.aspx");
}
else
{lblusrname.Text = Session["displayname"].ToString();
}string strSelectquery = "";
strSelectquery = "select userPermissionLevel,Franchise_ID from tblUsers where User_Name='" + Session["username"].ToString()+"'";SqlCommand objCmd = new SqlCommand(strSelectquery, objConn);SqlDataReader objDr;
objConn.Open();
objDr = objCmd.ExecuteReader();
 if (objDr.Read())
{
permLevel = objDr[0].ToString();
}
objDr.Close();
objConn.Close();if (int.Parse(permLevel) == 99)
{pnlRefundCC.Visible = true;
pnlRefundCA.Visible = true;pnlTransact.Visible = true;
pnlPaymentInfo.Visible = true;pnlCardifo.Visible = true;
}
else
{pnlPaymentInfo.Visible = true;
pnlCardifo.Visible = true;pnlRefundCC.Visible = false;
pnlRefundCA.Visible = false;pnlTransact.Visible = false;
}
}protected void RadioButton3_CheckedChanged(object sender, EventArgs e)
{pnlTransact.Visible = false;
pnlPaymentCCA.Visible = false;pnlOrgTransID.Visible = true;pnlCardifo.Visible = true;
 
}protected void RadioButton1_CheckedChanged(object sender, EventArgs e)
{pnlOrgTransID.Visible = false;pnlPaymentCCA.Visible = false;
}protected void RadioButton2_CheckedChanged(object sender, EventArgs e)
{
pnlPaymentInfo.Visible = false;pnlTransact.Visible = false;
pnlPaymentCCA.Visible = true;pnlDriversCCA.Visible = true;
}protected void RadioButton4_CheckedChanged(object sender, EventArgs e)
{pnlPaymentInfo.Visible = true;
pnlTransact.Visible = false;pnlOrgTransID.Visible = true;
pnlCardifo.Visible = false;pnlPaymentCCA.Visible = true;pnlDriversCCA.Visible = false;
}protected void btnSubmit_Click(object sender, EventArgs e)
{
string strSelect = "";strSelect = "select franchiseAuthNetID,franchiseAuthNetKey from franchises where franchiseid=" + Session["franchiseid"];
SqlCommand objCmd = new SqlCommand(strSelect, objConn);SqlDataReader objDr;
objConn.Open();
objDr = objCmd.ExecuteReader();String x_login = "";
String x_tran_key = "";if (objDr.Read())
{
x_login = objDr[0].ToString();
x_tran_key = objDr[1].ToString();
}
objDr.Close();
objConn.Close();
 /***************************************************************
VARIABLES USED THROUGHOUT THIS SCRIPT
**************************************************************/String x_version = "3.1";
String x_test_request = "false"; // needs to be set to false when in productionString x_delim_data = "true";
String x_delim_char = "|";String x_relay_response = "false";
String x_first_name = txtFirstname.Text;String x_last_name = txtLastname.Text;
String x_company = txtCompany.Text;String x_address = txtAddress.Text;
String x_city = txtCity.Text;String x_state = txtState.Text;
String x_zip = txtZip.Text;String x_country = txtCountry.Text;
String x_phone = txtPhone.Text;String x_fax = txtFax.Text;
String x_cust_id = "";String x_customer_ip = Request.ServerVariables["REMOTE_ADDR"];
String x_customer_tax_id = txtTaxid.Text;String x_email = txtEmail.Text;
String x_email_customer = "true";String x_merchant_email = "help@XYZ.com";
String x_invoice_num = txtInvoiceno.Text;String x_description = txtInvoicedesc.Text;
String x_ship_to_first_name = "";String x_ship_to_last_name = "";
String x_ship_to_company = "";String x_ship_to_address = "";
String x_ship_to_city = "";String x_ship_to_state = "";
String x_ship_to_zip = "";String x_ship_to_country = "";if (CheckBox1.Checked)
{
x_ship_to_first_name = txtFirstname.Text;
x_ship_to_last_name = txtLastname.Text;
x_ship_to_company = txtCompany.Text;
x_ship_to_address = txtAddress.Text;
x_ship_to_city = txtCity.Text;
x_ship_to_state = txtState.Text;
x_ship_to_zip = txtZip.Text;
x_ship_to_country = txtCountry.Text;
}
else
{
x_ship_to_first_name = txtShippingfirstname.Text;
x_ship_to_last_name = txtShippinglastname.Text;
x_ship_to_company = txtShippingcompany.Text;
x_ship_to_address = txtShippingaddress.Text;
x_ship_to_city = txtShippingcity.Text;
x_ship_to_state = txtShippingstate.Text;
x_ship_to_zip = txtShippingzip.Text;
x_ship_to_country = txtShippingcountry.Text;
}String x_amount = "";
String x_method = "";if (RadioButton1.Checked == true || RadioButton3.Checked == true)
{
x_amount = txtAmount.Text;x_method = "CC";
}else if (RadioButton2.Checked == true || RadioButton4.Checked == true)
{
x_amount = txtCCAamount.Text;x_method = "ECHECK";
}String x_currency_code = "USD";
String x_type = "";if (int.Parse(permLevel) == 99)
{if (RadioButton5.Checked == true)
{x_type = "AUTH_CAPTURE";
}if (RadioButton6.Checked == true)
{x_type = "AUTH_ONLY";
}if (RadioButton7.Checked == true)
{x_type = "CAPTURE_ONLY";
}
}
else
{x_type = "AUTH_CAPTURE";
}String x_recurring_billing = "NO";
String x_bank_aba_code = txtRoutingno.Text;String x_bank_acct_num = txtAccno.Text;
String x_bank_acct_type = DropDownList1.SelectedValue;String x_bank_name = txtBankname.Text;
String x_bank_acct_name = txtNameonbankacc.Text;String x_echeck_type = "";
String x_customer_organization_type = "";if (DropDownList1.SelectedValue == "BUSINESSCHECKING")
{x_echeck_type = "CCD";
x_customer_organization_type = "B"; // business
}
else
{x_echeck_type = "WEB";
x_customer_organization_type = "I"; //individual
}
 String x_card_num = txtCardno.Text;
String x_exp_date = txtExpdate.Text;String x_card_code = "";
String x_trans_id = txtorgtransid.Text;String x_auth_code = "";
String x_authentication_indicator = "";String x_cardholder_authentication_value = "";
String x_drivers_license_num = txtDriverlicenseno.Text;String x_drivers_license_state = txtDriverlicensestate.Text;
String x_drivers_license_dob = txtDriverlicenseDOB.Text;/*************************************************************
Level 2 Data
*************************************************************/String x_po_num = "";
String x_tax = "";String x_tax_exempt = "";
String x_freight = "";String x_duty = "";
//*************************************************************/**************************************************************
Optional: You can also supply merchant-defined values.
**************************************************************/String my_own_variable_name = "";
String another_field_name = "";string strInsert = "";
string cashpay = "";string nocharge = "";string billcustomer = "";
 
 if (!CheckBox2.Checked)
{
 /**************************************************************
REQUEST STRING THAT WILL BE SUBMITTED BY WAY OF
THE HTTPS POST OPERATION
**************************************************************/String strPost = "x_login=" + x_login + "&x_tran_key=" + x_tran_key + "&x_version=" + x_version + "&x_method=" + x_method;
strPost = strPost + "&x_test_request=" + x_test_request + "&x_delim_data=" + x_delim_data + "&x_delim_char=" + x_delim_char;strPost = strPost + "&x_relay_response=" + x_relay_response + "&x_first_name=" + x_first_name + "&x_last_name=" + x_last_name + "&x_company=" + x_company + "&x_address=" + x_address;
strPost = strPost + "&x_city=" + x_city + "&x_state=" + x_state + "&x_zip=" + x_zip + "&x_country=" + x_country + "&x_phone=" + x_phone + "&x_fax=" + x_fax;strPost = strPost + "&x_cust_id=" + x_cust_id + "&x_customer_ip=" + x_customer_ip + "&x_customer_tax_id=" + x_customer_tax_id + "&x_email=" + x_email;
strPost = strPost + "&x_email_customer=" + x_email_customer + "&x_merchant_email=" + x_merchant_email + "&x_invoice_num=" + x_invoice_num + "&x_description=" + x_description;strPost = strPost + "&x_ship_to_first_name=" + x_ship_to_first_name + "&x_ship_to_last_name=" + x_ship_to_last_name + "&x_ship_to_company=" + x_ship_to_company;
strPost = strPost + "&x_ship_to_address=" + x_ship_to_address + "&x_ship_to_city=" + x_ship_to_city + "&x_ship_to_state=" + x_ship_to_state;strPost = strPost + "&x_ship_to_zip=" + x_ship_to_zip + "&x_ship_to_country=" + x_ship_to_country + "&x_amount=" + x_amount;
strPost = strPost + "&x_currency_code=" + x_currency_code + "&x_method=" + x_method + "&x_type=" + x_type + "&x_recurring_billing=" + x_recurring_billing;strPost = strPost + "&x_bank_aba_code=" + x_bank_aba_code + "&x_bank_acct_num=" + x_bank_acct_num + "&x_bank_acct_type=" + x_bank_acct_type;
strPost = strPost + "&x_bank_name=" + x_bank_name + "&x_bank_acct_name=" + x_bank_acct_name + "&x_echeck_type=" + x_echeck_type + "&x_card_num=" + x_card_num;strPost = strPost + "&x_exp_date=" + x_exp_date + "&x_card_code=" + x_card_code + "&x_trans_id=" + x_trans_id + "&x_auth_code=" + x_auth_code;
strPost = strPost + "&x_authentication_indicator=" + x_authentication_indicator + "&x_cardholder_authentication_value=" + x_cardholder_authentication_value;strPost = strPost + "&x_customer_organization_type=" + x_customer_organization_type + "&x_drivers_license_num=" + x_drivers_license_num;
strPost = strPost + "&x_drivers_license_state=" + x_drivers_license_state + "&x_drivers_license_dob=" + x_drivers_license_dob + "&my_own_variable_name=" + my_own_variable_name;strPost = strPost + "&another_field_name=" + another_field_name + "&x_po_num=" + x_po_num + "&x_tax=" + x_tax + "&x_tax_exempt=" + x_tax_exempt; strPost = strPost + "&x_freight=" + x_freight + "&x_duty=" + x_duty + "&x_customer_organization_type=" + x_customer_organization_type;
//Response.Write(strPost);
//Response.End();String result = ""; StreamWriter myWriter = null;
// HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create("https://test.authorize.net/gateway/transact.dll");HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create("https://secure.authorize.net/gateway/transact.dll");objRequest.Method = "POST";
objRequest.ContentLength = strPost.Length;objRequest.ContentType = "application/x-www-form-urlencoded";
try
{myWriter = new StreamWriter(objRequest.GetRequestStream());
myWriter.Write(strPost);
}catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
myWriter.Close();
}HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse();using (StreamReader sr = new StreamReader(objResponse.GetResponseStream()))
{
result = sr.ReadToEnd();
// Close and clean up the StreamReader
sr.Close();
}string[] strname = new string[1000];
strname = result.Split("|".ToCharArray());if (strname[0].ToString() == "2")
{Response.Write("Your Transaction Was Denied!" + "<br>");
}if (strname[0].ToString() == "3")
{Response.Write("Error Submitting Transaction" + "<br>");
}if (strname[0].ToString() == "1")
{
// Response.Write("Your Transaction Was Approved!" + "<br>");string cardno = "";
string accno = "";if (x_card_num != "")
{int intcardLen = x_card_num.Length;
cardno = x_card_num.Substring(intcardLen - 4, 4);
}
else
{cardno = "";
}if (x_bank_acct_num != "")
{int intLen = x_bank_acct_num.Length;
accno = x_bank_acct_num.Substring(intLen - 4, 4);
}
else
{accno = "";
}
 if (CheckBox2.Checked)
{cashpay = "1";
}
else
{cashpay = "0";
}
 if (CheckBox3.Checked)
{nocharge = "1";
}
else
{nocharge = "0";
}
 if (CheckBox4.Checked)
{billcustomer = "1";
}
else
{billcustomer = "0";
}strInsert = "Insert into transactions(franchiseid,transdate,transowner,transamount,transPaymentMethod,transCardNumber,transExpirationDate,transRoutingNumber,transAccountNumber,transBankName,transNameOnBankAccount,transBankAccountType,transDispatchNumber,transDescription,transBillingFirstName,transBillingLastName,";
strInsert = strInsert + "transBillingCompany,transBillingAddress,transBillingCity,transBillingState,transBillingZip,transBillingCountry,transBillingPhone,transBillingFax,transBillingEmail,transShippingFirstName,transShippingLastName,transShippingAddress,transShippingCity,transShippingState,transShippingZip,transShippingCountry,transCashpayment,transNocharge,transBillcustomer,transtimeinn,transtimeout)";strInsert = strInsert + "values (" + Session["franchiseid"] + ",'" + DateTime.Now.ToString() + "','" + Session["username"].ToString().Replace("'", "''") + "','" + strname[9].ToString() + "','" + x_method + "','" + "************" + cardno + "','" + x_exp_date + "','" + x_bank_aba_code + "','" + "*****" + x_bank_acct_num + "','" + x_bank_name.Replace("'", "''") + "','" + x_bank_acct_name.Replace("'", "''") + "','" + x_bank_acct_type + "','" + x_invoice_num + "','" + x_description.Replace("'", "''") + "','" + x_first_name.Replace("'", "''") + "','" + x_last_name.Replace("'", "''") + "','" + x_company.Replace("'", "''") + "','" + x_address.Replace("'", "''") + "','" + x_city.Replace("'", "''") + "','" + x_state + "','" + x_zip + "','" + x_country + "','" + x_phone + "','" + x_fax + "','" + x_email + "','" + x_ship_to_first_name.Replace("'", "''") + "','" + x_ship_to_last_name.Replace("'", "''") + "','" + x_ship_to_address.Replace("'", "''") + "','" + x_ship_to_city.Replace("'", "''") + "','" + x_ship_to_state + "','" + x_ship_to_zip + "','" + x_ship_to_country + "'," + cashpay + "," + nocharge + "," + billcustomer + ",'" + ddlTimein.SelectedValue + "','" + ddlTimeout.SelectedValue + "')";
 
}
}
else
{if (CheckBox2.Checked)
{cashpay = "1";
}
else
{cashpay = "0";
}
 if (CheckBox3.Checked)
{nocharge = "1";
}
else
{nocharge = "0";
}
 if (CheckBox4.Checked)
{billcustomer = "1";
}
else
{billcustomer = "0";
}strInsert = "Insert into transactions(franchiseid,transdate,transowner,transamount,transPaymentMethod,transCardNumber,transExpirationDate,transRoutingNumber,transAccountNumber,transBankName,transNameOnBankAccount,transBankAccountType,transDispatchNumber,transDescription,transBillingFirstName,transBillingLastName,";
strInsert = strInsert + "transBillingCompany,transBillingAddress,transBillingCity,transBillingState,transBillingZip,transBillingCountry,transBillingPhone,transBillingFax,transBillingEmail,transShippingFirstName,transShippingLastName,transShippingAddress,transShippingCity,transShippingState,transShippingZip,transShippingCountry,transCashpayment,transNocharge,transBillcustomer,transtimeinn,transtimeout)";strInsert = strInsert + "values (" + Session["franchiseid"] + ",'" + DateTime.Now.ToString() + "','" + Session["username"].ToString().Replace("'", "''") + "','" + amtVal.Text + "','','','','','','','','','" + x_invoice_num + "','" + x_description.Replace("'", "''") + "','" + x_first_name.Replace("'", "''") + "','" + x_last_name.Replace("'", "''") + "','" + x_company.Replace("'", "''") + "','" + x_address.Replace("'", "''") + "','" + x_city.Replace("'", "''") + "','" + x_state + "','" + x_zip + "','" + x_country + "','" + x_phone + "','" + x_fax + "','" + x_email + "','" + x_ship_to_first_name.Replace("'", "''") + "','" + x_ship_to_last_name.Replace("'", "''") + "','" + x_ship_to_address.Replace("'", "''") + "','" + x_ship_to_city.Replace("'", "''") + "','" + x_ship_to_state + "','" + x_ship_to_zip + "','" + x_ship_to_country + "'," + cashpay + "," + nocharge + "," + billcustomer + ",'" + ddlTimein.SelectedValue + "','" + ddlTimeout.SelectedValue + "')";
//Response.Write("<br>autho" + strInsert);
}SqlCommand cmd1 = new SqlCommand(strInsert, objConn);
objConn.Open();
cmd1.ExecuteNonQuery();
objConn.Close();Response.Redirect("Printpaymentdetails.aspx");
 
}protected void Button1_Click(object sender, EventArgs e)
{Response.Redirect("ABCD.aspx");
}protected void CheckBox2_CheckedChanged(object sender, EventArgs e)
{if (CheckBox2.Checked)
{pnlPaaymentMethod.Visible = false;
pnlTransact.Visible = false;pnlPaymentCCA.Visible = false;
pnlPaymentInfo.Visible = false;amount.Visible = true;
}
else
{pnlPaaymentMethod.Visible = true;
pnlTransact.Visible = true;pnlPaymentInfo.Visible = true;
pnlCardifo.Visible = true;amount.Visible = false;
}
}
}
 
Here is the error iam getting
server Error in '/' Application. ----------------ExecuteNonQuery: CommandText property has not been initialized 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.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initializedSource Error:  Line 431:            SqlCommand cmd1 = new SqlCommand(strInsert,objConn);Line 432:            objConn.Open();Line 433:            cmd1.ExecuteNonQuery();Line 434:            objConn.Close();Line 435: Source File: d:Websitesserviceinfo.comsecurePaymentprocessing.aspx.cs    Line: 433 Stack Trace:  [InvalidOperationException: ExecuteNonQuery: CommandText property hasnot been initialized]   System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +873524   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +72   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135   Paymentprocessing.btnSubmit_Click(Object sender, EventArgs e) in d:Websitesxyassss.aspx.cs:433   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102   ----------------Version Information:  Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

View 2 Replies View Related

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 Boolean3 If String.IsNullOrEmpty(pageurl) Then Return False4
5 Dim conn As New SqlConnection()6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString7
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.IgnoreCase15
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 = Nothing26
27 Return exists28 End Function
29
  

View 4 Replies View Related

ExecuteNonQuery To Count Number Of Rows??

Sep 5, 2007

My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned.
Does this also work when calling stored procedures and passing parameters?
I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1.
What should I do?Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))
myCommand.Connection = con
con.Open()
Dim rowCount As Integer = myCommand.ExecuteNonQuery()
numberParts.Text = rowCount.ToString
con.Close() Thank you.

View 6 Replies View Related

Unable To Force A Return Value With ExecuteNonQuery???

Sep 13, 2007

Hi all!  I've been banging my head on this for way to long and decided it's time to reach out for help.  I'll try to be complete and concise with this, but this will end up being lengthy most likely.
The crux of the issue is that I apparently do not have any control over what is returned from a stored proc via the SqlCommand.ExecuteNonQuery method.  I want the procedure to explicitly return 0 if the proc was successful, otherwise it will return the error code generated.  A value of -1 is consistently being returned when SET NOCOUNT ON is in place, and when not, a 1 is returned.  This seems to be ignoring the fact that I have RETURN 0 at the end of the proc.  Perhaps I'm missing something, but if I RETURN 0, I should get 0...here is the procedure:1 ALTER PROCEDURE [dbo].[epsp_EditCities]
2 @CityId int = null,
3 @City varchar(50),
4 @CreatedBy int,
5 @UpdatedBy int,
6 @ActionType varchar(1) = 'X',
7 @Identity int = 0 OUT,
8 @RowCount int = 0 OUT
9
10 AS
11 DECLARE @ERROR int -- Local @@ERROR
12
13
14 SET NOCOUNT ON
15 BEGIN TRAN
16
17 IF @ActionType = 'I' /* --- INSERT --- */
18 BEGIN
19
20 INSERT INTO [EnrollmentPrograms].[dbo].[Cities]
21 ([City]
22 ,[CreatedBy]
23 ,[UpdatedBy])
24 VALUES
25 (@City
26 ,@CreatedBy
27 ,@UpdatedBy)
28 SET @Identity = SCOPE_IDENTITY()
29
30 END
31
32 IF @ActionType = 'U' /* --- UPDATE --- */
33 BEGIN
34
35 UPDATE [EnrollmentPrograms].[dbo].[Cities]
36 SET [City] = @City
37 ,[UpdatedBy] = @UpdatedBy
38 WHERE CityId = @CityId
39 END
40
41 IF @ActionType = 'D' /* --- DELETE --- */
42 BEGIN
43
44 DELETE FROM [EnrollmentPrograms].[dbo].[Cities]
45 WHERE CityId = @CityId
46 END
47
48
49 -- Error checking (place this after every statement) --
50 SET @ERROR = @@ERROR
51 SET @RowCount = @@ROWCOUNT
52
53 IF @ERROR != 0 GOTO HANDLE_ERROR
54
55 COMMIT TRAN -- No Errors, so go ahead
56
57 RETURN 0
58
59 HANDLE_ERROR:
60 ROLLBACK TRAN
61 RETURN @ERRORAnd a fascinating tidbit is the results from this which shows the return value as 0:1 DECLARE@return_value int,
2 @Identity int--,
3 --@RowCount int
4
5 EXEC@return_value = [dbo].[epsp_EditCities]
6 @CityId = 1,
7 @City = N'Agoura Hills',
8 @CreatedBy = 1,
9 @UpdatedBy = 1,
10 @ActionType = N'U',
11 @Identity = @Identity OUTPUT--,
12 --@RowCount = @RowCount OUTPUT
13
14 SELECT@Identity as N'@Identity'--,
15 --@RowCount as N'@RowCount'
16
17 SELECTReturnValue = @return_value
 
And finally, here is the C# call that always returns -1:
retVal = cmd.ExecuteNonQuery();
Now, I've tried almost all variations and unit tests on the procedure that I could dream up.  Commenting this out and putting this in etc....I still remain unable to fix the return code to 0 and get that result into my retVal var.
Any love?  Much thanks in advance for spending time on my issue!
Cheers!
Wayne

View 5 Replies View Related

ExecuteNonQuery: Connection Property Has Not Been Initialized

Feb 9, 2005

I am trying to create a web form that will be used to create new users. The
first step that I am taking is creating a web form that can check the
username against a database to see if it already exists. I would it to do
this on the fly, if possible. When I execute my current code, I get the
following error:

ExecuteNonQuery: Connection property has not been initialized

Below is the code from the page itself:
-----
<!-- #INCLUDE FILE="../include/context.inc" -->
<!-- #INCLUDE FILE="../include/db_access.inc" -->

<script language="VB" runat="server">

Sub CheckButton_Click(Sender as Object, e as EventArgs)

Dim result As Int32
Dim cmd As OdbcCommand

cmd = new OdbcCommand( "(? = CALL CheckUserExists(?))", db_conn )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "result", OdbcType.Int ).Direction =
ParameterDirection.ReturnValue

cmd.Parameters.Add( "@userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")

cmd.ExecuteNonQuery()
result = cmd.Parameters("result").Value

If result <> 1 Then
CheckResults.Text="<font color=""#ff0000"">Username already
exists!</font>"
Else
CheckResults.Text="<font color=""#009900"">Username is
available.</font>"
End If

end Sub

</script>

<html><body>
<form runat="server">
<asp:TextBox id=userName runat="server" />
<asp:Button id=CheckButton runat="server" Text="Check Username"
onClick="CheckButton_Click" />

<p>
<asp:Label id=CheckResults runat=server />
</form>
</body></html>
-----

Can anyone see why I might get this error? Here are some more details of
the error:

Line 15: cmd.Parameters.Add( "@userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")
Line 16:
*Line 17: cmd.ExecuteNonQuery()
Line 18: result = cmd.Parameters("result").Value

Thank You,
Jason Williard

View 7 Replies View Related

ExecuteNonQuery Not Returning Rows Affected

Mar 13, 2006

I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...
SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write.  By hiding the count of records touched by your query, you also disable the results to be consumed by your application.
So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.

View 2 Replies View Related

VB.Net CF App Falls Over On SqlServerCe ExecuteNonQuery Under WM5 But Not PC2003

Jun 1, 2006

I have a VB.net 2005 application that uses both System.Data.SqlClient and System.Data.SqlServerCe which has worked on PC2003 handhelds but falls over in Windows Mobile 5. It seems to be when I have used a SqlClient connection and then use ExecuteNonQuery on the local sdf file - the application just bombs out - no error message, nothing.

Weirdly, if I am connected to a CE database through Query Analyzer 3.0 at the same time as running this application, everything works fine. Do I need to 'initialize' something that Query Analyzer seems to be doing in order to make this work?

Most grateful for any ideas.

Thanks

Chris

View 5 Replies View Related

Use Of Variant Type Parameter In ExecuteNonQuery()

Jul 19, 2007



In the following code snippet, the stored procedure executes up to the


iRet = vciSqlCommand.ExecuteNonQuery();

command and fails with error:


System.Data.SqlClient.SqlException: Incorrect syntax near 'value'.

That would point to the


" = @data_Param where

section.



So what is the proper syntax?






Code Snippet

SqlParameter data_Param = new SqlParameter("@data_Param", SqlDbType.Variant);

SqlCommand vciSqlCommand = new SqlCommand();

switch ((vcidatatype)FieldDef.ivcidatatype)

{ // read in the binary data!!!!!!


case vcidatatype.eENGUNITS: data_Param.Value = Encoding.ASCII.GetString(r.ReadBytes(FieldDef.iLen)); break;

case vcidatatype.eLANADR: data_Param.Value = r.ReadInt16(); break;

case vcidatatype.eBYTESIZE: data_Param.Value = r.ReadByte(); break;

case vcidatatype.eFLOATSIZE: data_Param.Value = r.ReadSingle(); break;

case vcidatatype.eINT32SIZE: data_Param.Value = r.ReadUInt32(); break;

case vcidatatype.eFILLERBYTES: r.ReadBytes(FieldDef.iLen); break; // eat up empty bytes

default: FieldDef.ivcidatatype = (Int32)vcidatatype.eFILLERBYTES; break;

}

if (FieldDef.ivcidatatype != (Int32)vcidatatype.eFILLERBYTES)

{ // all but filler


vciSqlCommand.CommandText =


"update " + acPointType + "set " + FieldDef.sFldName +

" = @data_Param where VEC = " + acVECName +

" and name = " + acPointName;

vciSqlCommand.Connection = conn;

conn.Open();

iRet = vciSqlCommand.ExecuteNonQuery();

conn.Close();














































View 1 Replies View Related

Problem With The SQL Insert With .CommandText() And .ExecuteNonQuery()

Jan 18, 2007

Hi

I have done a SQL statement INSERT for my coding. The program compiled and run the insertion with no error occurred. But after the compilation I found out that my database is not populated at all.

I tried the data insertion using the manual query, and I work perfectly.

So the problem now is that I do not know why my codes did not insert the data into my database. Will like to get some advise. Thanks

My codes are pasted in http://www.pastebin.ca/320102
The Debug.Writeline in the code is in http://www.pastebin.ca/320108


Thanks again.

View 4 Replies View Related

ExecuteNonQuery() Not Giving Correct Affected Rows

Jul 23, 2007

 When I use ExecuteNonQuery() with the stored procedure below it returns -1. However, when i tried to get rid of the if/else statements and just leave one insert statement for testing purposes, ExecuteNonQuery() returns the correct affected rows which is 1. So it seems like ExecuteNonQuery() doesn't work when the INSERT statement is inside the IF..ELSE. Can anybody help me with this problem? I haven't tried using @@RowCount because I really want to use ExecuteNonQuery() to do this because I don't want to rewrite my DAL. Thanks in advance -- With if/else ExecuteNonQuery returns -1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]  @ObjectId int,  @FanId int,  @DateContributed DateTime,  @Notes nvarchar(512),  @ObjectType intASBEGIN  BEGIN TRAN    IF @ObjectType = 2    BEGIN      INSERT INTO FighterContributions        (FighterId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 3    BEGIN      INSERT INTO FighterPhotoContributions        (FighterPhotoId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 4    BEGIN      INSERT INTO OrganizationContributions        (OrganizationId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 5    BEGIN      INSERT INTO EventContributions        (EventId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 6    BEGIN      INSERT INTO FightContributions        (FightId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 7    BEGIN      INSERT INTO FightPhotoContributions        (FightPhotoId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    IF @@ERROR <> 0    BEGIN      ROLLBACK RETURN    END  COMMIT TRANEND -- Without if/else ExecuteNonQuery returns 1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
  @ObjectId int,
  @FanId int,
  @DateContributed DateTime,
  @Notes nvarchar(512),
  @ObjectType int
AS

BEGIN

  BEGIN TRAN
      INSERT INTO FighterContributions
        (FighterId, FanId, DateContributed, Notes) VALUES
        (@ObjectId, @FanId, @DateContributed, @Notes)
    IF @@ERROR <> 0
    BEGIN
      ROLLBACK RETURN
    END

  COMMIT TRAN

END 

View 7 Replies View Related







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