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
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.
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?
I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below. However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help? Thanks, Andy.private bool searchProducts(string keywords) { ....... command.CommandText = "SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@keywords%' "; command.Parameters.Add("@keywords", SqlDbType.VarChar, 100); command.Parameters["@keywords"].Value = keywords; ............. }
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);
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.
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
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.
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();
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
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 }
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?
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
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!
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.
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.
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?
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
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?
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(); }
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;
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)
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
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)
Dim cmdhighbid As New SqlCommand("update items set Highestbid=@BidAmount,HighestBidder=@bidder where ItemCode=@ItemCode and Highestbid<@BidAmount", conn)
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");
//-- 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(); }
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(); } }
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
