SqlCommand Array Help
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 following
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
Can I Add Integer Array To SQLCommand As Parameter?
I'm trying to build an SQL string that should look like this when executed: UPDATE [Table] SET Active = 'False' WHERE ID IN (3, 4, 5, 6, 7, etc.) I'm using the convention (in code behind):SqlCommand cmd = new SqlCommand("UPDATE [Table] SET Active = 'False' WHERE ID IN (@TheIDs)", connection);cmd.Parameters.Add("TheIDs", SqlDbType.Text).Value = theIDsAsAnArrayList; But logically enough I cannot insert them as a text string as they have to be integers seperated by commas. Question: How can I convert an Array of integers into ... well, a string without the quotes, if you know what I mean? As it cannot end up like this: UPDATE [Table] SET Active = 'False' WHERE ID IN ("3, 4, 5, 6, 7, etc.") Note the quotes around the integers. Any hints on doing this with security in mind are welcome. I know I can concatenate the whole lot as strings, but this is unsecure, so I'm not going for that approach.
View Replies !
The Best Overloaded Method Match For 'System.Data.SqlClient.SqlCommand.SqlCommand Error
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 Replies !
How Would I Send A String Array As A Integer Array?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
View Replies !
Array Of Array - IRR Function
Hi, I am using the IRR function in a report. I have created the following code so it creates an array: Public GroupIRRArray(-1) As Double Public Function addToIRRArray(ByVal BMV As Decimal, ByVal BAB As Decimal, ByVal EMV As Decimal, ByVal EAB As Decimal, ByVal CFB As Decimal) Dim g As Integer g = uBound(GroupIRRArray) + 1 ReDim Preserve GroupIRRArray(g) if g=0 then GroupIRRArray(g) = (CFB+EAB-BAB+BMV)*-1 else if g=1 then GroupIRRArray(g) = (BAB-CFB-EAB+EMV) else GroupIRRArray(g-1)= GroupIRRArray(g-1)-(BMV) GroupIRRArray(g) = (BAB-CFB-EAB+EMV) end if End Function It works fine but now I want to create multiple groups within my report. How can I change the code so it loops on another parameter? What I had in mind was to create an initial array with the parameter value that I want to use for grouping and a dynamic array based on the name of each group. So I would end up with one array containing the group name plus x number of arrays with the raw data. Alternatively, is there a way to use the IRR function without creating a custom code? Like a conversion parameter that would make my floating field a one dimensional array? Thanks, Jam
View Replies !
Help With Sqlcommand...
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 Replies !
SqlCommand Check
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 Replies !
SqlCommand Parameters.Add
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 Replies !
SqlCommand Problem
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 Replies !
Re-use Of SqlConnection And SqlCommand ?
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 Replies !
SqlCommand Error
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 Replies !
How To Use A Variable In A SQLCommand?
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 Replies !
XML Parameter In Sqlcommand
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 Replies !
Re-use SqlCommand Object
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 Replies !
SqlCommand Parameters
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 Replies !
New Sqlcommand Question???
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 Replies !
How To Debug A SqlCommand?
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 Replies !
What Property Should I Assign To My Sqlcommand ?
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 Replies !
SqlCommand.CommandTimeout Not Support.
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 Replies !
ExecuteNonQuery() And Execute In SqlCommand.
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 Replies !
SqlCommand.StatementCompleted Event - How To Use In ASP
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 Replies !
How To Set SQLCommand Timeout For SqlDataSource For ASP.NET 2.0?
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 Replies !
Passing Variable To SqlCommand
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 Replies !
SqlCommand And Nullable Parameters
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 Replies !
SQLCommand.ExecuteNonQuery() Is Always Returning -1
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 Replies !
SqlCommand Parameters Best Practices
I was writing a simple sign up class the other day and coded a Register method with a SqlCommand and Parameters like this: SqlCommand sqlCmd = new SqlCommand("Regester", sqlConn); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 55); sqlCmd.Parameters["@UserName"].Value = userName; sqlCmd.Parameters.Add("@Firstname", SqlDbType.VarChar, 55); sqlCmd.Parameters["@Firstname"].Value = fName; etc... Seems pretty straight forward right? A senior coder writes his params like this SqlParameter pUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 55); pUserName .Value = address2; sqlCmd.Parameters.Add(pUserName ); SqlParameter pFName = new SqlParameter("@FName", SqlDbType.VarChar, 55); pFName .Value = city; sqlCmd.Parameters.Add(pFName ); etc... What is the benefit, if any, of creating a new instance of SqlParameter for each value passed to the sp? How could I easily test the code for speed and resource use besides tracing?
View Replies !
Working With SqlCommand And Parameters
i am inserting into a table using the sqlCommand parameters property.for example i do :sqlCmd.Parameters.Add("@date_birth_hebrew", Request("date_birth_hebrew"))the date_birth_hebrew is not a must and has allow null value in the db.when the user submits the form and leaves the textbox of date_birth_hebrewnot field out and i try to add this value to parameters as the above code i recive :Prepared statement '(@date1 datetime,@first_name nvarchar(4000),@last_na' expects parameter @date_birth_hebrew, which was not supplied. as i understand beacuse the Request("date_birth_hebrew") is empty the sqlCommand.Parameters acts as no value entered.i hae solved this problem by on each value checking thatif Parameters Request("date_birth_hebrew")="" thensqlCmd.Parameters.Add("@date_birth_hebrew", "")but isnt there any another way?thnaks in advancepeleg
View Replies !
Sql Error - In The SqlCommand.ExecuteNonQuery
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 Replies !
Problem With Executing An Sqlcommand
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 Replies !
Cannot Perform Delete In SqlCommand
Hi everyone! I am building a database in VC++2005 Express, connecting to SQL Server. I planed to add Delete function to a button, and when clicked, the data with the specific ID according to the IDTextBox->Text would be deleted from the table. However, nothing happened. I don't know what's wrong with my program. I have attached the button_Click here. Thank you! private: System:: Void button8_Click(System:: Object^ sender, System:: EventArgs ^ e) { System:: Windows:: Forms:: DialogResult^ MyDlg=MessageBox:: Show("Are you sure to delete?","Warning",MessageBoxButtons:: YesNo,MessageBoxIcon:: Question); if (MyDlg==System:: Windows:: Forms:: DialogResult:: Yes) { if (this->sqlConnection1->State==ConnectionState:: Open) { this->sqlConnection1->Close(); this->sqlConnection1->ConnectionString="Data Source=(local);Integrated S ecurity=SSPI;database=Pnemoconises"; this->sqlConnection1->Open(); SqlTransaction^ MyTransaction=this->sqlConnection1->BeginTransaction(); try { this->sqlCommand1=this->sqlConnection1->CreateCommand(); this->sqlCommand1->CommandType=CommandType:: Text; this->sqlCommand1->CommandText="DELETE FROM [Basic_Info] WHERE Patient I D='"+Convert:: ToString(this->Patient_IDTextBox->Text)+"'"; this->sqlCommand1->Transaction=MyTransaction; this->sqlCommand1->ExecuteNonQuery(); MyTransaction->Commit(); } catch (SqlException^ ex) { MyTransaction->Rollback(); } catch (Exception^ ex) { MessageBox:: Show(ex->Message,"Information",MessageBoxButtons:: OK,Message BoxIcon:: Information); } finally { this->sqlConnection1->Close(); } } } }
View Replies !
SqlCommand.BeginExecuteReader Vs SynchronousDataAccessMethodDelegate.BeginInvoke
Is there a functional or perfomance difference between having a data access method that perfoms an asynchrounous data access call using SqlClient.SqlCommand.BeginExecuteReader/EndExecuteReader and having a method that performs the same data access synchrounously using ExecuteReader, but is called by the client using BeginInvoke and EndInvoke on a delegate of the synchrounous method?
View Replies !
Count Records In Sqlcommand Or Sqladapter?
Hi everyone? I have a small problem here, I want to count the records found under the following method: Public Function ValidateAssembly(ByVal assyno As String) As DataSet Dim SQLConn As SqlConnection = New SqlConnection(Connstr) Dim adapter As SqlDataAdapter = New SqlDataAdapter Dim dsVendorInfo As DataSet = New DataSet("AssemblyHeader") Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand Dim BMItemno As New SqlParameter("@v_assyno", SqlDbType.VarChar, 10) BMItemno.Value = GetBomAssyNo(assyno) SQLConn.Open() Try With cmd .CommandText = "SELECT * FROM dbo.cfn_bom_get_assy(@v_assyno)" .Parameters.Add(BMItemno) .Connection = SQLConn .CommandType = CommandType.Text End With adapter.SelectCommand = cmd adapter.Fill(dsVendorInfo) Catch x As Exception End Try SQLConn.Close() Return dsVendorInfo End Function I want to see if this can be done and passed to the actual dataset? Is this possible? Or do I need to pass the found results from my query to a sqlreader? Also, Im I forced to put my colums together in a DataTable before actually binding to the DataSet? Thanks everyone!
View Replies !
Summary Report Using Nested Sqlcommand
Let me try to be as clear as possible. I am using VWD c# code behind asp.net 2.0 A company has to track the types of calls multiple extensions receive. Each extension receives hundreds of calls each day stored in a table. I need to generate a Report that produces one row for each extension and it counts the types of calls that extension receives. (I would like to use some type of Data control to do this) I can display the extensions: SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString); connection.Open(); string sql = "Select extension from View1 where DATEADD(d,0,DATEDIFF(d,0,insertDate))='5/01/06' group by extension"; SqlCommand command = new SqlCommand(sql, connection); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { string ext= (dr["extension"].ToString()); Response.Write ( ext + "<br>"); This is where I need do my counts.. I tried to create and run a count(*) queary while in the readerbut it craps out.......... int total; string countcmd = "select count(id) as total from leads where ext='" + ext + "' and dateadd(d,0,datediff(d,0,insertdate))='5/01/06'"; sqlcommand cmd = new sqlcommand(countcmd, connection); total = (int)cmd.executescalar(); Response.Write(total + "<br>"); gives me Error 3 The type or namespace name 'sqlcommand' could not be found (are you missing a using directive or an assembly reference?) Any help would be greatly appreciated, Doug
View Replies !
Using Variable In DataReader's SqlCommand Text
I'm attempting to configure a DataReader's SqlCommand text to use a package-level variable and I'm having trouble. I've read about using the "sql command from variable" option but although I see that during the configuration of Ole DB source, I don't see the same option for DataReader source. Viewing the properties of the DataReader and selecting the SqlCommand property does have button to configure the value but only displays a string value editor window. (I was hoping for the expression editor.) The configured connection to the DataReader uses the odbc Data Provider (under .Net providers). I'm connecting to mySql, if that matters. What I'm looking to do is to use a variable in the where clause of the DataReader's SqlCommand. Any hints how to accomplish this or other ideas would be greatly appreciated. Thanks
View Replies !
Problem With Variable In DataReader SQLCommand
Hello, I'm trying to set the SQLCommand of a DataReader with a string variable (at the DataFlow Task level), and I'm having some problems. The source is a DB2 table. The destination is an MS Excel file. Here is what I have: 1. A variable named SQLStatementDataFlow of type String. EvaluateAsExpression is set to True, and the SQL statement in the expression evaluates without a problem. 2. In the Expression property of the DataFlow Task, I have the [SqlCommand] property configured with the variable. First, in the component properties of the DataReader, what kind of SQLCommand do I set up here if the DataFlow SQLCommand is configured to use a variable? Thank you for your help! cdun2
View Replies !
SqlDependency And Selection Of SqlCommand Columns
Is the following behaviour by design? I created a SqlDependency object that uses a SqlCommand object that selects a single column: Code Snippet SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select OrderID from Orders"; // Only one column! cmd.CommandType = CommandType.Text; SqlDependency d = new SqlDependency( cmd ); d.OnChange += new OnChangeEventHandler(dependency_OnChange); Now when I change the value of any field in table Orders, my query notification handler fires. Can I change this behaviour such that SqlDependency handler fires only if the specified column (OrderID) is changed?
View Replies !
The Insides Of The SqlConnection, SqlCommand, And SqlDataReader, Objects
Im just curious, can anyone explain what exactly the methods do within the objects (sqlconnection, sqlCommand, SqlDataReader) . and why it takes three objects to carry out a connection. Php only used one object I believe. But most of all i would like to know how they work. like sqlCommand carrys the connection (sqlcommand.connection) while sql connection retrieves the address connection (sqlconnection(connectionstring)). what does Sqlconnection do once it gets this address. and what does it give sqlcommand when its ready to take out the query and put it in the reader once the sqlconnection.open() to make it simple. let me put it this way sqlConnection con = new SqlConnection(_connectionstring); // server address comes through here. along with security and database name what exactly does the object con do ? it runs through the constructor and does what ? SqlCommand cmd = new SqlCommand(); cmd.Connection = con; //So now what ever the SqlConnection did with the _connectionstring. it now passes it through the connection property. the connection property does something with value of con. what is it ?? cmd.CommandText = 'select, ID, Name, Price.....' // I think i know what that does con.Open() // I know that the connection opens here. but what exactly does this method do to make it open ???????? sqlDataReader reader = cmd.ExecuteReader(); im some what confused with this one. sqldataReader reader was never declared and its not even a static method. What exactly is it ????? cmd.ExecuteReader() now takes the property connection which did something with the con value. then it used the commandtext property to request the query. it takes con.open does something and now its execute reader. Im a lil confused last but not least, con.close() what does that do ?? its alot but can anyone fill me in. or should i say fill(me); in lol, get it. never mind.
View Replies !
Change SqlCommand Based On Combo Box Selection
I have a textbox, combo box, and a button on a form. I would like to perform a different query depending on the combo box selection. I thought I could do something such as: if (cboSearch.Text == "Selection1") { scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection1"; } else if (cboSearch.Text == "Selection2") { scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection2"; } else { scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection3"; } However, this obviously does not operate as I would need it to. What is the proper method for conditional SqlCommand statements like this?
View Replies !
SQLCommand's Transaction Property Goes Null On Failure
I've written some provider independent code that dynamically generates and loads DbCommands with data via Parameters and executes them within a transaction. Everything works fine until an exception occurs and then the DbCommand.Transaction property that was set goes to null, effectively killing any chance of rolling back the transaction. I've noticed this only happens when the executed command is of type SqlCommand. The OracleCommand mainitains its link to the set transaction of failure. Upon further investigation, I found that this only occurs when parameters are used but not if I dynamically generate the insert statement as a string.. For instance:SqlConnection conn = new SqlConnection(connectionString);conn.Open();SqlTransaction transaction = conn.BeginTransaction();SqlCommand comm = new SqlCommand();comm.Connection = conn;comm.Transaction = transaction; command.commandText = "INSERT INTO Table1 Values (1, 2, 3)";command.ExecuteNonQuery(); If I execute that and it fails the command.Transaction property will remain set to transaction, but in this scenario: SqlConnection conn = new SqlConnection(connectionString);conn.Open();SqlTransaction transaction = conn.BeginTransaction();SqlCommand comm = new SqlCommand();comm.Connection = conn;comm.Transaction = transaction; comm.commandText = "INSERT INTO Table1 Values (@Col1,@Col2, @Col3)";comm.Parameters.add(new SqlParameter("@Col1", 1); comm.Parameters.add(new SqlParameter("@Col2", 2);comm.Parameters.add(new SqlParameter("@Col3", 3); comm.ExecuteNonQuery(); Upon failing the comm.Transaction property gets somehow goes null and the Connection property of the transaction object also goes null.Any ideas on why this only happens with a SqlCommand but no other DbCommand?
View Replies !
New In .Net But Learning SqlConnection, SqlCommand And SqlDataReader How In Code_behind
Hi. I want to now a little about CodeBehind how to use it with SqlConnection, SqlCommand and SqlDataReader. How i use:Web.Config <connectionStrings><add name="ConnectionStringTest" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|DSNbase.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/></connectionStrings> Default.aspx<asp:Repeater ID="test" runat="server" DataSourceID="SqlDataSourcetest">.....<asp:SqlDataSource ID="SqlDataSourcetest" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"ProviderName="<%$ ConnectionStrings:ConnectionStringTest.ProviderName %>" SelectCommand="SELECT [ID], [Name], [Sex], [Born], [Color], [Dog], [Images] FROM [Dogs] WHERE ([Dog] = 'YES') ORDER BY [ID]"></asp:SqlDataSource> How can i take my asp:SqlDataSource and put it in my CodeBehind, so it use the ConnectionString from my Web.Config and so i can use my Repeater on my default.aspx site.. !?Maybe if u got a great link that show "How to do" maybe om MSDN/MSDN2.
View Replies !
Different Result Running SQL Query Directly Or Using SqlCommand
Hi, when running the following stored procedure: ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]@LeidinggevendeID int,@Start int = 1,@Limit int = 25,@Sofinummer int = NULL,@Achternaam nvarchar(128) = NULL,@Functie nvarchar(64) = NULL ASWITH Ordered AS ( SELECT ROW_NUMBER() OVER (ORDER BY Achternaam) AS RowNumber,Persoon.*FROM PersoonINNER JOIN DienstverbandON Persoon.ID = Dienstverband.PersoonIDINNER JOIN BedrijfsonderdeelON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelIDINNER JOIN LeidinggevendeON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelIDWHERE Leidinggevende.Begindatum <= getdate()AND (Leidinggevende.Einddatum > getdate()OR Leidinggevende.Einddatum IS NULL ) AND Leidinggevende.PersoonID = @LeidinggevendeIDAND ( Sofinummer = @Sofinummer OR @Sofinummer IS NULL ) AND ( Achternaam LIKE @AchternaamOR AchternaamPartner LIKE @AchternaamOR @Achternaam IS NULL ) ) SELECT *FROM OrderedWHERE RowNumber between @Start and (@Start + @Limit - 1) When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code: [DataObject(true)] public class PersoonFactory { [DataObjectMethod(DataObjectMethodType.Select, false)] public static IList WerknemersBijLeidinggevende(int ldgID, int start, int max) { IList list = new List(); SqlDataReader rdr = null; SqlConnection connection = DatabaseProvider.Connection; SqlCommand command = new SqlCommand("GetWerknemersBijLeidinggevende", connection); command.Parameters.AddWithValue("LeidinggevendeID", ldgID); command.CommandType = CommandType.StoredProcedure; try { connection.Open(); rdr = command.ExecuteReader(CommandBehavior.CloseConnection); while (rdr.Read()) { Persoon pers = new Persoon(); pers.ID = rdr["ID"] as int?; pers.Achternaam = rdr["Achternaam"] as string; pers.AchternaamPartner = rdr["AchternaamPartner"] as string; pers.Achtertitels = rdr["Achtertitels"] as string; pers.DatumOverlijden = rdr["DatumOverlijden"] as DateTime?; pers.Geboortedatum = rdr["Geboortedatum"] as DateTime?; pers.Geslacht = rdr["Geslacht"] as string; pers.Middentitels = rdr["Middentitels"] as string; pers.Naamgebruik = (int)rdr["Naamgebruik"]; pers.Sofinummer = rdr["Sofinummer"] as string; pers.Voorletters = rdr["Voorletters"] as string; pers.Voortitels = rdr["Voortitels"] as string; pers.Voorvoegsel = rdr["Voorvoegsel"] as string; pers.VoorvoegselPartner = rdr["VoorvoegselPartner"] as string; list.Add(pers); } } catch { throw; } finally { if (rdr != null) rdr.Close(); else connection.Close(); } return list; } I get 0 rows all of a sudden. Any idea why?
View Replies !
Writing A Conditional Save Method Using SqlCommand
Hi,I'm trying to write a Save method which is conditional based on the form fields which the user has completed. I'm using the SqlCommand to write the Save method. I'd like to be able to only save the form fields which the user have completed, but I'm not sure what is the better option. Whether to check which fields have been filled in and have different methods for each form field. Is there a better way to write a conditional save method?Stephen
View Replies !
Can The Result From An EXEC(@sqlcommand) Be Returned To A Variable
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable? As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible? For Example (I know this doesn't work, but it is effectively what I am trying to achieve): select @result = EXEC(@sqlcommand) I could then use the @result variable in an insert statement to update a table with the results from the EXEC command. Any assistance would be greatly appreciated... Regards, Wayne
View Replies !
Passing Parameter From SQL Task To DataReader SQLCommand
Hello, Newbee here 64 bit SQL 2005 running on Windows Server 2003 X64 I have an exececute SQL task (in the control flow obviously) SELECT MAX(last_update) AS OrdersLastUpdateFROM orders This task executes successfully and I can see that my user variable called "User:tmOrdersLastUpdate" populates correctly in the "variables" pane.. ALL GOOD. The next step of the Control flow is a dataflow task Details DataFlow Source = DataReader Source (MySQL .NET connector) DataFlow Dest = local SQL Server OLE DB. In the DataFlow Source the DataReader SQLCommand property is Select * from orders where last_update >= @User:tmOrdersLastUpdate I've tried every conceivable permutation and I can't get SSIS to itnerpret the variable as such...it always gets passed to the server as a literal. How do I pass a user-defined global variable to the WHERE clause in a DataRader object? Thanks
View Replies !
Correct Syntax Using Variables Inside SQL Queries (SqlCommand)
I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number). I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance. The database table is called "friday" and the one and only column is called "Tickets". Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e) {SqlConnection conn; conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***"); conn.Open();int count = -1; SqlCommand cmd = new SqlCommand("select Tickets from friday", conn); count = (int)cmd.ExecuteScalar();if (count > 0) { string updateString = @" update friday set Tickets = 500" ; <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString); cmd2.Connection = conn; cmd2.ExecuteNonQuery(); } else { } conn.Close(); }
View Replies !
Multiple OCmd.ExecuteNonQueries - The SqlCommand Is Currently Busy Open
Hi there, Just starting out so bear with me thanks. I am developing a ASP.NET web application with supporting ASP.NET Web services. The app sends a webmethod a bunch of values and the web service takes those values and through several stored procedures, updates a SQL database. I initialise the DB connection right at the top of the web method following the method attributes : .... ByVal strState As String, _ ByRef nominalExists As Boolean _ ) As String Dim oConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("DBconnString")) Dim oCmd As SqlCommand = New SqlCommand Dim workParam As SqlParameter Dim myTrans As SqlTransaction Dim isError As Boolean Dim returnURL As String .... The function continues into a SELECT CASE routine... Right... So when the desired CASE fires I enter into the following type of code. Purpose being to fire off several different Stored procedures under the same transaction and the same database connection. Dim oCheckDBReader As SqlDataReader Try oConn.Open() oCmd.Connection = oConn oCmd.CommandType = CommandType.Text oCmd.CommandText = "SOME SELECT STATEMENT" oCheckDBReader = oCmd.ExecuteReader If oCheckDBReader.HasRows Then nominalExists = True oCmd.Connection = Nothing oCheckDBReader.Close() oCmd.Dispose() myTrans = oConn.BeginTransaction() oCmd.Connection = oConn oCmd.Transaction = myTrans oCmd.CommandText = "sp_mystoredprocedure" oCmd.CommandType = CommandType.StoredProcedure ... oCmd parameter stuff oCmd.ExecuteNonQuery oCmd.Dispose() oCmd = New SQLCommand oCmd.Connection = oConn oCmd.Transaction = myTrans oCmd.CommandText = "sp_mystoredprocedure" oCmd.CommandType = CommandType.StoredProcedure ... oCmd parameter stuff oCmd.ExecuteNonQuery oCmd.Dispose() .... Catch myTrans.Rollback() .... Finally oCmd.Dispose() oConn.Dispose() .... End Try Can someone please spare some time to tell me: a) Whether I am going about all of this the right way b) Explain why I am getting the following error System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: The SqlCommand is currently busy Open, Fetching. at System.Data.SqlClient.SqlCommand.set_Transaction(SqlTransaction value) at System.Data.SqlClient.SqlTransaction.GetServerTransactionLevel() at System.Data.SqlClient.SqlTransaction.Rollback() at Thanks a million to all that assist... Hope to hear from you soon....
View Replies !
Which Party Manages The Timeout Of Class SqlCommand? .Net Or SQL Server?
Dear all, Our system is using .Net 1.1 and SQL Server 2000. In our system, the timeout of SqlCommand is set to 300 seconds (~5 mins). However, there was a case that exception 'ThreadAbortException' was thrown in .Net (IIS Server), with its corresponding query was blocked over 11 minutes. The case raised a question: 'Which party manages the timeout after the sql command is sent to DB? .Net, IIS Server or SQL Server?' If the timeout is managed by .Net or IIS, is there any setting making SQL Server 2000 possible to kill the aborted process earlier? Thanks for helps. Gabriel
View Replies !
|
TRACKER
