SqlCommand.StatementCompleted Event - How To Use In ASP

Mar 24, 2008

Hi, I've come from desktop to web (say no more?)

I'd like to fire a sqlCommand.StatementCompleted event in ASP.NET & use the sender argument to retrieve the output of a stored proc.

The command is in a button event. The event handler += is also set up in the same button event. Naturally the event handler proc is separate as its own procedure

In the event handling procedure, I cast the sender arg. to an SQLCommand object & try to extract the value of the output parameter.

When I try to do this I get a "No reference set to object" error raised in . Is this because there was a post back after the command completed & the Command object lost its state? If so, what would be the best way to retain the parameters value? Using session state or Profile?

Any help would be greatly appreciated

below is cut down code as to what I'm doing:protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.StatementCompleted += new StatementCompletedEventHandler(c_StatementCompleted); cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
void c_StatementCompleted(object sender, StatementCompletedEventArgs e)
{
// get an error here
string result = ((SqlCommand)sender).Parameters["returnPrm"].Value.ToString();
}

View 3 Replies


ADVERTISEMENT

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

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

Recovery :: Configure Extended Event To Trigger A Mail Whenever Any Event Occurs

Jun 2, 2015

Recently we migrated our environment to 2012.

We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.

Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.

I have gone through so many websites but i never find.

View 13 Replies View Related

DB Engine :: Event Tracing For Windows Failed To Send Event

Oct 25, 2011

My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-

Date  10/25/2011 10:55:46 AM
Log  SQL Server (Current - 10/25/2011 10:55:00 AM)
Source  spid
Message
Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
 
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.

View 2 Replies View Related

WMI Event Watcher Task Continual Firing Event When Not Triggered

Apr 8, 2008

I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file.
The WQL is thus:

SELECT * FROM __InstanceModificationEvent within 30
WHERE targetinstance isa 'CIM_DataFile'
AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'

This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).

My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message.
However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.

Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?

View 2 Replies View Related

Help - Security Event Log Posts Error Event ID 560 Every Few Seconds

May 31, 2007

Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup)
We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers.
We were having

Event Type: Failure Audit
Event Source: Security
Event Category: Object AccessEvent ID: 560
Date: 5/23/2007
Time: 6:27:15 AM
User: domainuser
Computer: MACHINENAME
Description:
Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,1623975729}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: Domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: User
Client Domain: Domain
Client Logon ID: (0x0,0x6097C608)
Accesses: READ_CONTROL
Connect to service controller
Enumerate services
Query service database lock state

Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015



Applied the following fix

http://support.microsoft.com/kb/907460/


Now we are getting



Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: user
Client Domain: domain
Client Logon ID: (0x0,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D

View 4 Replies View Related

Query Or Script To Get The Event Viewer Event Properties?

Nov 2, 2007



Hi all,


Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?

Scenario:

>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application

click on one event.We can get the log info which is the manual procudure.

But now i want to get the event properties through the Query analyzer...

Any help would be great?


Thanks,

View 4 Replies View Related

EVENT ID 18456 STATE: 16 Login Failed For User 'DOMAIN/user'. [CLIENT: &&<local Machine&&>] Every Minute In Event Log.

Oct 22, 2007

We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:

EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]

This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.

Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.

We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!

David

View 5 Replies View Related

Help With Sqlcommand...

Jan 8, 2007

Hi guys. I'm having trouble declaring an sqlcommand. What I want to do is declare a global sqlcommand and I would want this sqlcommand to vary depending on the conditions on my page_load.
Here's the code....
  
 Dim p_s_syounin2 As New SqlCommand 
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
 p_s_syounin2 = ("INSERT INTO (p_s_syounin2) SELECT syain_hnm FROM TR_syainID WHERE TR_syainID.syozokubu_id=20 AND TR_syainID.syozokuka_id=21 AND TR_syainID.kaikyuu_id=23, cnn")   '''' THIS DOES NOT WORK!
End If
End Sub
 p_s_syounin2 .ExecuteNonQuery()
 
What is the correct way of declaring  p_s_syounin2?
Thanks.
 
Best Regards,
Audrey

View 5 Replies View Related

Re-use SqlCommand Object

Oct 19, 2006

Is it ok to re-use a SqlCommand object?  In a method, I am executing 2 separate parameterized sql statements.  Before I run the second, I will clear the command objects parameters.(command.parameters.clear())  I'm just checking to see if it is good coding practice or not. thanks,SC

View 2 Replies View Related

XML Parameter In Sqlcommand

Mar 6, 2007

I created an xmldocument which I would like to insert in a db field with the data type XML.The following code is giving me the error:System.Data.SqlClient.SqlException: XML parsing: line 1, character 38, unable to
switch the encoding             SqlCommand cmdUpdate = new SqlCommand("sp_AddHistory", sqlConnection);            cmdUpdate.CommandType = CommandType.StoredProcedure;            cmdUpdate.Parameters["@FieldsChanged"].Value = xmlDoc.innerXML; // don't know whether this is good             cmdUpdate.ExecuteNonQuery(); innerXML:<?xml version="1.0" encoding="utf-8"?><Fields>    <Field>        <FieldName>comp_Area</FieldName>        <OldValue>Area 52</OldValue>        <NewValue>Area 51</NewValue>    </Field></Fields> The XML seems fine.. any ideas? 

View 3 Replies View Related

How To Use A Variable In A SQLCommand?

May 10, 2007

Hi! I need to know what can I do to use a variable in the WHERE condition of the sqlcommand as I show you:  current_user = User.Identity.Name

Dim cmd As New SqlCommand("SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = current_user", cn) Obviously it doesn't work and I need your help. Thanks.   

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

Re-use Of SqlConnection And SqlCommand ?

Oct 26, 2007

Hi,When using the following controls....System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlCommandIf I want to change my SQL command and execute the query once again what cleanup do I need to do first?Do I need close and dispose the SqlConnection?Do I need to dispose the SqlCommand?Can I use the SqlConnection for more than one SqlCommand?Thanks,Scott   

View 5 Replies View Related

SqlCommand Problem

Nov 23, 2007

Hello all, when I write my CommandText, It displays it as insert into videos values (127.0.0.1, 4000, 434), which the ip address is sent int as a string, put i keep getting an error, and I think it is because it is wanting an string, but it is sending in these "." or something, anyways heres my method, I dont know how I can add " " around the ip when i pass it in, since it is a string. thanks.
  1 [WebMethod]
2 public void Register(string ip, int port, int[] handles)
3 {
4 SqlConnection dbConn = new SqlConnection(connStr);
5 SqlCommand comm = new SqlCommand();
6 comm.Connection = dbConn;
7 dbConn.Open();
8 for (int i = 0; i < handles.Length; i++)
9 {
10 comm.CommandText = "insert into videos values (" + ip + ", " + port + ", " + handles[i] + ")";
11 comm.ExecuteNonQuery();
12 }
13 dbConn.Close();
14 }
 

View 1 Replies View Related

SqlCommand Parameters.Add

Mar 12, 2008

Using SqlCommand, this is how I am updating a database table:


Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs)    strSQL = "UPDATE Basket SET Quantity = Qty, Total = TotAmt WHERE BasketID = BID AND ProductID = PID"    sqlCmd = New SqlCommand(strSQL, sqlConn)    With sqlCmd        .Parameters.Add("Qty", SqlDbType.Int).Value = CInt(iQty)        .Parameters.Add("TotAmt", SqlDbType.Money).Value = CInt(iQty) * CType(ea.Item.FindControl("lblPrice"), Label).Text        .Parameters.Add("BID", SqlDbType.VarChar, 50).Value = strBasketID        .Parameters.Add("PID", SqlDbType.VarChar, 50).Value = CType(ea.Item.FindControl("lblID"), Label).Text    End With      sqlConn.Open()    sqlCmd.ExecuteNonQuery()    sqlConn.Close()End Sub


But the above code generates the following error pointing to the red colored line in the above code:
Invalid column name 'BID'.Invalid column name 'PID'.
BID & PID are not the column names in the actual database table but can't it be done in the way I have done above? In fact, Qty & TotAmt are not the column names in the actual database table as well; so why isn't the error pointing to Qty & TotAmt as they will be evaluated before BID & PID, if I am not mistaken?

View 8 Replies View Related

SqlCommand Check

Apr 15, 2008

How can I check if the ( SqlCommand ) return empty values
Can some one write code for this, I want know it is return Null values or not
thanx ....
 

View 4 Replies View Related

SqlCommand Array Help

Feb 19, 2006

I want to do something like the following but I get an error: Object reference not set to an instance of an object.       SqlConnection sqlConnection = new SqlConnection("server=xxxxx");        SqlCommand [] cmd = new SqlCommand[3];        Object returnValue;          cmd[0].CommandText = "DO QUERY";          cmd[1].CommandText = "DO QUERY";          cmd[2].CommandText = "DO QUERY";          cmd[3].CommandText = "DO QUERY";        }        sqlConnection.Open();int i = 0;while(i<4){       cmd[i].CommandType = CommandType.Text;        cmd[i].Connection = sqlConnection;        cmd[i].ExecuteNonQuery();        returnValue[i] = cmd[i].ExecuteScalar();i++}        sqlConnection.Close();How should I do the followingThanks

View 9 Replies View Related

How To Debug A SqlCommand?

Mar 7, 2006

How to test @au_lname's value sends to the following following sql command?
Dim MyCommand As New SqlCommand("UPDATE [authors] SET [au_lname] = @au_lname",  MyConnection)MyCommand.Parameters.Add(New SqlParameter("@au_lname", SqlDbType.NVarChar)).Value = me.au_lname.text
I tried to print the "MyCommand.CommandText.ToString" but only get UPDATE [authors] SET [au_lname] = @au_lname with no value in the command text.
Thanks!
 

View 5 Replies View Related

SqlCommand.ExecuteScalar()

May 4, 2006

Is there documentation on what ExecuteScalar() will return if the SQL statement is returning an image?

View 1 Replies View Related

SqlCommand Parameters

May 17, 2006

Hi:
I am using sqlcommand.parameters.add() and up to 74 parameters for one sqlcommand, it gets the error "too many parameters for the sqlcommand", I wonder if someone know is there limitation of the paramters that I can pass to sqlcommand? If so, how many parameters I can pass to the sqlcommand at one time?
thank u in adv.

View 4 Replies View Related

New Sqlcommand Question???

May 30, 2006

Hello,I was trying to do the following: Dim cmd As SqlCommand Dim objConnection As SqlConnection objConnection = New SqlConnection = Web.configwhere the We.config is where my connection string is set.  but I get a sintax error in the Web.config line.is it possible to asign the value of the web.config content to the new sql connection?thanks for any suggestions.

View 1 Replies View Related

SqlCommand And Nullable Parameters

Aug 1, 2006

I am trying to add a DateTime? parameter to SqlCommand. It works when the variable has a value, but when its null, an exception gets thrown saying that parameter was not supplied.What is causing this error?

View 5 Replies View Related

Using Both Sqlcommand And Sqldataadpter Objects

Jan 29, 2007

Hi
Just a doubt: s it possible to write an ado.net code that uses a sqldataadpter object and a sqlcommand object ( BOTH OBJECTS, IS IT POSSIBLE?) to retrieve data from the database by calling a stored procedure.
 Thanks a lot

View 3 Replies View Related

Passing Variable To SqlCommand

May 18, 2007

 Can't seem to pass a variable to the sql statement. I'd appreciate any help. I'm trying to pass pColName to  CommandText = "ALTER TABLE tb_roomInfo ADD @rColName  varchar(50);";Doesn't seem to work though. CODE:  [WebMethod]    public string addCol(string pColName)    {                    SqlConnection cnn = new SqlConnection(connString);        try        {                        cnn.Open();            SqlCommand cmd = new SqlCommand();            cmd.Connection = cnn;                       cmd.CommandText = "ALTER TABLE tb_roomInfo ADD @rColName  varchar(50);";            SqlParameter rColName = new SqlParameter("@rColName", pColName);            cmd.Parameters.Add(rColName);            int i = cmd.ExecuteNonQuery();            cnn.Close();            return "Insert Successful";        }        catch        {                        return "Insert Unsuccessful";        }    }

View 3 Replies View Related

How To Set SQLCommand Timeout For SqlDataSource For ASP.NET 2.0?

Oct 16, 2007

With VS2005, there is a new component SqlDataSource, <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource> Then you can assign SP and bind datasource to a get data for this component in .NET code:SqlDataSource1.SelectCommand = "spName"SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure SqlDataSource1.ConnectionString = Comm.connString There is no way to set sqlcommand timeout for this stored procedure like SqlClient.SqlCommand. How can I do this? 

View 4 Replies View Related

SqlCommand Get The Inserted Primary Key

Jan 23, 2008

Hi,
I would like to know how I can retrieve the ID (Primary key) of the row I just inserted with a sqlcommand(text not stored procedure).
Thx

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

SqlCommand.CommandTimeout Not Support.

Mar 31, 2008

 Hi Dear Sir,I have some problem:SqlCommand.CommandTimeout not support.My SQL Query: public static List<Subject> GetAllSpecificUrlSubject(int memberId)    {       
string sql = string.Concat("SELECT distinct S.subject_id as 'id',
cast(S.subject_raw as varchar(8000)) as 'subject' FROM STATEMENT
S,Custom_Blogs B INNER JOIN onsurvey.member_custom_blogs M ON
B.Blogs_ID=M.Blogs_Id where M.MemberId="+ memberId +" and
cast(S.blog_url as varchar(8000)) like '%'+ cast(B.Url as
varchar(8000)) COLLATE SQL_Latin1_General_CP1_CI_AS +'%' group by
S.Subject_Id,cast(S.Subject_Raw as varchar(8000))");        List<Subject> list;        using (SqlConnection conn = OLSUtils.DBUtils.SqlConnectionUtils.GetNewConnection())        {            using (SqlCommand cmd = conn.CreateCommand())            {                cmd.CommandText = sql;                cmd.CommandType = CommandType.Text;               //cmd.CommandTimeout = 300;                conn.Open();
cmd.CommandTimeout = 300;

View 1 Replies View Related

What Property Should I Assign To My Sqlcommand ?

Apr 2, 2008

what property should i assign to myPuzzleCmd2 ? 
 
myConnection.Open()
 Dim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection)
 
myPuzzleCmd2.CommandType = CommandType.StoredProcedure
 Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, 6)
retLengthParam.Direction = ParameterDirection.Input
 
myPuzzleCmd2.Parameters.Add(retLengthParam)
 Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30)
 
retRandomCode.Direction = ParameterDirection.Output
 
myPuzzleCmd2.Parameters.Add(retRandomCode)
 
Try
 Dim reader As SqlDataReader = myPuzzleCmd2.ExecuteReader()
myPuzzleCmd.ExecuteNonQuery()Catch ex As Exception
 
myPuzzleCmd2 = Nothing
 Session.Remove("RandomCode")
 HttpContext.Current.Session("RandomCode") = myPuzzleCmd2("@RandomCode")        <  ---------       Over here
 Finally
myConnection.Close()
End Try

View 7 Replies View Related

Problem With Executing An Sqlcommand

Jun 21, 2004

when i push a button my datagrid doesn't show up. please help this is my code. I only try to execute one of the sql command and when i push the button it just redirects me to my homepage.


Private Sub btnbid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbid.Click

Dim conn As New SqlConnection((Application("SQL_Connection_String")))

conn.Open()

Dim cmdbid As New SqlCommand("insert into Bids (CustID,ItemCode,BidAmount) values (@CustID,@ItemCode,@BidAmount)", conn)

cmdbid.Parameters.Add("@CustID", SqlDbType.Int, 4)
cmdbid.Parameters("@CustID").Value = Session("CustID")
cmdbid.Parameters.Add("@ItemCode", SqlDbType.Int, 4)
cmdbid.Parameters("@ItemCode").Value = CInt(Request.QueryString("Id"))
cmdbid.Parameters.Add("@BidAmount", SqlDbType.Decimal, 9)
cmdbid.Parameters("@BidAmount").Value = CDec(txtbidamount.Text)


Dim cmdhighbid As New SqlCommand("update items set Highestbid=@BidAmount,HighestBidder=@bidder where ItemCode=@ItemCode and Highestbid<@BidAmount", conn)

cmdhighbid.Parameters.Add("@BidAmount", SqlDbType.Decimal, 9)
cmdhighbid.Parameters("@BidAmount").Value = CDec(txtbidamount.Text)
cmdhighbid.Parameters.Add("@bidder", SqlDbType.Int, 4)
cmdhighbid.Parameters("@bidder").Value = Session("CustID")
cmdhighbid.Parameters.Add("@ItemCode", SqlDbType.Int, 4)
cmdhighbid.Parameters("@ItemCode").Value = Request.QueryString("Id")


Try
cmdbid.ExecuteNonQuery()

lblbidstatus.Text = "Bid Inserted Successfully!! Good Luck!!!"
Catch ex As Exception


lblbidstatus.Text = ex.Message
End Try

btnbid.Enabled = False
conn.Close()
End Sub

View 5 Replies View Related

Sql Error - In The SqlCommand.ExecuteNonQuery

Aug 12, 2004

Hi

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

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

I believe the problem is somewhere here:

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


Any help would be most appreciated.

Many thanks in advance

Regards

Miles


The Error is::

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

Here's the code::

<script runat="server">

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

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

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

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

}

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


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

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

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

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

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

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

</script>

View 4 Replies View Related







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