Search Results

Search found 484 results on 20 pages for 'sqlcommand'.

Page 9/20 | < Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >

• My SqlComand on SSIS - DataFlow OLE DB Command seems not works

  - by Angel Escobedo

  Hello Im using OLE DB Source for get rows from a dBase IV file and it works, then I split the data and perform a group by with aggregate component. So I obtain a row with two columns with "null" value : CompanyID | CompanyName | SubTotal | Tax | TotalRevenue Null Null 145487 27642.53 173129.53 this success because all rows have been grouped with out taking care about the firsts columns and just Summing the valuable columns, so I need to change that null for default values as CompanyID = "100000000" and CompanyName = "Others". I try use SqlCommand on a OLE DB Command Component : SELECT "10000000" AS RUCCLI , "Otros - Varios" AS RAZCLI FROM RGVCAFAC <property id="1505" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">SELECT "10000000" AS RUCCLI , "Otros - Varios" AS RAZCLI FROM RGVCAFAC</property> but nothings happens, why? and finally the task finish when the data is inserted on a SQL Server Table. Im using the same connection manager on extracting data and transform. (View Code) <DTS:Property DTS:Name="ConnectionString">Data Source=C:\CONTA\Resocen\Agosto\;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Extended Properties=dBASE IV;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager> all work is on memory, Im not using cache manager connections

  Read the article

• Invoking ADO.NET from MATLAB

  - by Andrew Shepherd

  It's possible to call .NET from MATLAB, so I thought I would try to use ADO.NET to connect to a database. I seem to have hit a blocking problem - anytime you try and create a Command object, it throws an error. You can try this yourself: >> NET.addAssembly('System.Data'); >> sqlconn = System.Data.SqlClient.SqlConnection(); >> sqlconn.State ans = Closed >> % So far, so good >> sqlcmd = System.Data.SqlClient.SqlCommand(); ??? Error using ==> System.Data.SqlClient.SqlCommand 'Connection' is already defined as a property. >> Does anyone have some insight into this? It seems like a pure and simple bug on MATLAB's part - maybe it happens with every .NET class that happens to have a property called "Connection". Should I just throw in the towel and give up on using MATLAB to talk to a database using .NET?

  Read the article

• Adapt beginner C# SQL Server INSERT example to work with my database

  - by Mike Bertelsen

  I have read TONS of tutorials, articles and whatever regarding my issue and honestly, due to my lack of experience I can't twist my fingers around this one so I hope some of you guys can help me out :) I am working on a project (simply to learn how to program so it's probably very basic), but I have this "News" page where I can update and delete data using a GridView. Now I would like to INSERT something into my database using 3 textboxes and 1 submit button. I have 3 rows that has to be inserted: Headline Date Content/the news itself. Which are stored under NyhedTB from the connectionstring: BoligStjernenConnectionString My query looks like this: INSERT INTO [NyhedTB] ([NyhedDato], [NyhedTitel], [NyhedTekst]) VALUES (@NyhedDato, @NyhedTitel, @NyhedTekst) I read on the internet that this code should do the magic for me (I will have to insert my own values ofc.): static void Insert() { try { string connectionString = "server=.;" + "initial catalog=employee;" + "user id=sa;" + "password=sa123"; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("INSERT INTO EmployeeDetails VALUES(" + "@Id, @Name, @Address)", conn)) { cmd.Parameters.AddWithValue("@Id", 1); cmd.Parameters.AddWithValue("@Name", "Amal Hashim"); cmd.Parameters.AddWithValue("@Address", "Bangalore"); int rows = cmd.ExecuteNonQuery(); //rows number of record got inserted } } } catch (SqlException ex) { //Log exception //Display Error message } } I looked at this code and thought it should be easy enough but really, I can't figure it out.

  Read the article

• ASP.Net GridView UpdatePanel Paging Gives Error On Second Click

  - by joe

  I'm trying to implement a GridView with paging inside a UpdatePanel. Everything works great when I do my first click. The paging kicks in and the next set of data is loaded quickly. However, when I then try to click a link for another page of data, I get the following error: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 12030 aspx code <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <contenttemplate> <asp:GridView ID="GridView1" runat="server" CellPadding="2" AllowPaging="true" AllowSorting="true" PageSize="20" OnPageIndexChanging="GridView1_PageIndexChanging" OnSorting="GridView1_PageSorting" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="ActivityLogID" HeaderText="Activity Log ID" SortExpression="ActivityLogID" /> <asp:BoundField DataField="ActivityDate" HeaderText="Activity Date" SortExpression="ActivityDate" /> <asp:BoundField DataField="ntUserID" HeaderText="NTUserID" SortExpression="ntUserID" /> <asp:BoundField DataField="ActivityStatus" HeaderText="Activity Status" SortExpression="ActivityStatus" /> </Columns> </asp:GridView> </contenttemplate> </asp:UpdatePanel> code behind private void bindGridView(string sortExp, string sortDir) { SqlCommand mySqlCommand = new SqlCommand(sSQL, mySQLconnection); SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand); mySqlAdapter.Fill(dtDataTable); DataView myDataView = new DataView(); myDataView = dt.DefaultView; if (sortExp != string.Empty) { myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir); } GridView1.DataSource = myDataView; GridView1.DataBind(); if (mySQLconnection.State == ConnectionState.Open) { mySQLconnection.Close(); } } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; bindGridView(); } protected void GridView1_PageSorting(object sender, GridViewSortEventArgs e) { bindGridView(e.SortExpression, sortOrder); } any clues on what is causing the error on the second click?

  Read the article

• What purpose does “using” serve when used the following way

  - by user287745

  What purpose does “using” serve when used the following way:- ONE EXAMPLE IS THIS, (AN ANSWERER- @richj - USED THIS CODE TO SOLVE A PROBLEM THANKS) private Method(SqlConnection connection) { using (SqlTransaction transaction = connection.BeginTransaction()) { try { // Use the connection here .... transaction.Commit(); } catch { transaction.Rollback(); throw; } } } OTHER EXAMPLE I FOUND WHILE READING ON MICROSOFT SUPPORT SITE public static void ShowSqlException(string connectionString) { string queryString = "EXECUTE NonExistantStoredProcedure"; StringBuilder errorMessages = new StringBuilder(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); try { command.Connection.Open(); command.ExecuteNonQuery(); } catch (SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { errorMessages.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "LineNumber: " + ex.Errors[i].LineNumber + "\n" + "Source: " + ex.Errors[i].Source + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } Console.WriteLine(errorMessages.ToString()); } } } I AM doing at top of page as using system.data.sqlclient etc so why this using thing in middle of code, What if I omit it (I know the code will work) but what functionality will I be losing

  Read the article

• Calling a Stored Procedure using C# with XML Datatype

  - by Lakeshore

  I am simply trying to call a store procedure (SQL Server 2008) using C# and passing XMLDocument to a store procedure parameter that takes a SqlDbType.Xml data type. I am getting error: Failed to convert parameter value from a XmlDocument to a String. Below is code sample. How do you pass an XML Document to a store procedure that is expecting an XML datatype? Thanks. XmlDocument doc = new XmlDocument(); //Load the the document with the last book node. XmlTextReader reader = new XmlTextReader(@"C:\temp\" + uploadFileName); reader.Read(); // load reader doc.Load(reader); connection.Open(); SqlCommand cmd = new SqlCommand("UploadXMLDoc", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Year", SqlDbType.Int); cmd.Parameters["@Year"].Value = iYear; cmd.Parameters.Add("@Quarter", SqlDbType.Int); cmd.Parameters["@Quarter"].Value = iQuarter; cmd.Parameters.Add("@CompanyID", SqlDbType.Int); cmd.Parameters["@CompanyID"].Value = iOrganizationID; cmd.Parameters.Add("@FileType", SqlDbType.VarChar); cmd.Parameters["@FileType"].Value = "Replace"; cmd.Parameters.Add("@FileContent", SqlDbType.Xml); cmd.Parameters["@FileContent"].Value = doc; cmd.Parameters.Add("@FileName", SqlDbType.VarChar); cmd.Parameters["@FileName"].Value = uploadFileName; cmd.Parameters.Add("@Description", SqlDbType.VarChar); cmd.Parameters["@Description"].Value = lblDocDesc.Text; cmd.Parameters.Add("@Success", SqlDbType.Bit); cmd.Parameters["@Success"].Value = false; cmd.Parameters.Add("@AddBy", SqlDbType.VarChar); cmd.Parameters["@AddBy"].Value = Page.User.Identity.Name; cmd.ExecuteNonQuery(); connection.Close();

  Read the article

• Parameterized Queries /Without/ using queries.

  - by Aren B

  I've got a bit of a poor situation here. I'm stuck working with commerce server, which doesn't do a whole lot of sanitization/parameterization. I'm trying to build up my queries to prevent SQL Injection, however some things like the searches / where clause on the search object need to be built up, and there's no parameterized interface. Basically, I cannot parameterize, however I was hoping to be able to use the same engine to BUILD my query text if possible. Is there a way to do this, aside from writing my own parameterizing engine which will probably still not be as good as parameterized queries? Update: Example The where clause has to be built up as a sql query where clause essentially: CatalogSearch search = /// Create Search object from commerce server search.WhereClause = string.Format("[cy_list_price] > {0} AND [Hide] is not NULL AND [DateOfIntroduction] BETWEEN '{1}' AND '{2}'", 12.99m, DateTime.Now.AddDays(-2), DateTime.Now); *Above Example is how you refine the search, however we've done some testing, this string is NOT SANITIZED. This is where my problem lies, because any of those inputs in the .Format could be user input, and while i can clean up my input from text-boxes easily, I'm going to miss edge cases, it's just the nature of things. I do not have the option here to use a parameterized query because Commerce Server has some insane backwards logic in how it handles the extensible set of fields (schema) & the free-text search words are pre-compiled somewhere. This means I cannot go directly to the sql tables What i'd /love/ to see is something along the lines of: SqlCommand cmd = new SqlCommand("[cy_list_price] > @MinPrice AND [DateOfIntroduction] BETWEEN @StartDate AND @EndDate"); cmd.Parameters.AddWithValue("@MinPrice", 12.99m); cmd.Parameters.AddWithValue("@StartDate", DateTime.Now.AddDays(-2)); cmd.Parameters.AddWithValue("@EndDate", DateTime.Now); CatalogSearch search = /// constructor search.WhereClause = cmd.ToSqlString();

  Read the article

• Numeric Order By In Transact SQL (Ordering As String Instead Of Int)

  - by Pyronaut

  I have an issue where I am trying to order a result set by what I believe to be a numberic column in my database. However when I get the result set, It has sorted the column as if it was a string (So alphabetically), instead of sorting it as an int. As an example. I have these numbers, 1 , 2, 3, 4, 5, 10, 11 When I order by in Transact SQL, I get back : 1, 10, 11, 2, 3, 4, 5 I had the same issue with Datagridview's a while back, And the issue was because of the sorting being done as if it was a string. I assume the same thing is happening here. My full SQL code is : SELECT TOP (12) DATEPART(YEAR, [OrderDate]) AS 'Year', DATEPART(MONTH, [OrderDate]) AS 'Month' , COUNT(OrderRef) AS 'OrderCount' FROM [Order] WHERE [Status] LIKE('PaymentReceived') OR [Status] LIKE ('Shipped') GROUP BY DATEPART(MONTH, [OrderDate]), DATEPART(YEAR, [OrderDate]) ORDER BY DATEPART(YEAR, OrderDate) DESC, DATEPART(MONTH, OrderDate) desc DO NOTE The wrong sorting only happens when I cam calling the function from Visual Studio. As in my code is : using (SqlConnection conn = GetConnection()) { string query = @"SELECT TOP (12) DATEPART(YEAR, [OrderDate]) AS 'Year', DATEPART(MONTH, [OrderDate]) AS 'Month' , COUNT(OrderRef) AS 'OrderCount' FROM [Order] WHERE [Status] LIKE('PaymentReceived') OR [Status] LIKE ('Shipped') GROUP BY DATEPART(MONTH, [OrderDate]), DATEPART(YEAR, [OrderDate]) ORDER BY DATEPART(YEAR, OrderDate) DESC, DATEPART(MONTH, OrderDate) desc"; SqlCommand command = new SqlCommand(query, conn); command.CommandType = CommandType.Text; using (SqlDataReader reader = command.ExecuteReader()) etc. When I run the statement in MSSQL server, there is no issues. I am currently using MSSQL 2005 express edition, And Visual Studio 2005. I have tried numerous things that are strewn across the web. Including using Convert() and ABS() to no avail. Any help would be much appreciated.

  Read the article

• Autocomplet extender control in ajax (asp.net)?

  - by Surya sasidhar

  hi, i am using autocomplete extender in my application but it is not working. this is my code.. <form id="form1" runat="server"> <asp:scriptmanager ID="Scriptmanager1" runat="server"></asp:scriptmanager> <br /> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <div> <cc1:AutoCompleteExtender TargetControlID="TextBox1" MinimumPrefixLength="1" ServiceMethod="GetVideoTitles" CompletionSetCount="10" ServicePath="Myservices.asmx" ID="AutoCompleteExtender1" runat="server"> </cc1:AutoCompleteExtender> </div> </form> this is webservice method public string[] GetVideoTitles(string prefixText) { SqlConnection con = new SqlConnection(@"Data Source=SERVER5\SQLserver2005;Initial Catalog=tpvnew;User ID=xx;Password=525"); con.Open(); SqlCommand cmd = new SqlCommand("video_videotitles", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@prefixText", SqlDbType.VarChar, 50); cmd.Parameters["@prefixText"].Value = prefixText; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); string[] items = new string[dt.Rows.Count]; int i = 0; foreach (DataRow dr in dt.Rows) { items.SetValue(dr["videotitle"].ToString(), i); i++; } return items; }

  Read the article

• Is SQLDataReader slower than using the command line utility sqlcmd?

  - by Andrew

  I was recently advocating to a colleague that we replace some C# code that uses the sqlcmd command line utility with a SqlDataReader. The old code uses: System.Diagnostics.ProcessStartInfo procStartInfo = new System.Diagnostics.ProcessStartInfo("cmd", "/c " + sqlCmd); wher sqlCmd is something like "sqlcmd -S " + serverName + " -y 0 -h-1 -Q " + "\"" + "USE [" + database + "]" + ";+ txtQuery.Text +"\"";\ The results are then parsed using regular expressions. I argued that using a SQLDataReader woud be more in line with industry practices, easier to debug and maintain and probably faster. However, the SQLDataReader approach is at least the same speed and quite possibly slower. I believe I'm doing everything correctly with SQLDataReader. The code is: using (SqlConnection connection = new SqlConnection()) { try { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString); connection.ConnectionString = builder.ToString(); ; SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); // do stuff w/ reader reader.Close(); } catch (Exception ex) { outputMessage += (ex.Message); } } I've used System.Diagnostics.Stopwatch to time both approaches and the command line utility (called from C# code) does seem faster (20-40%?). The SqlDataReader has the neat feature that when the same code is called again, it's lightening fast, but for this application we don't anticipate that. I have already done some research on this problem. I note that the command line utility sqlcmd uses OLE DB technology to hit the database. Is that faster than ADO.NET? I'm really suprised, especially since the command line utility approach involves starting up a process. I really thought it would be slower. Any thoughts? Thanks, Dave

  Read the article

• How update DB table with DataSet

  - by Paul

  I am begginer with ADO.NET , I try update table with DataSet. O client side I have dataset with one table. I send this dataset on service side (it is ASP.NET Web Service). On Service side I try update table in database, but it dont 't work. public bool Update(DataSet ds) { SqlConnection conn = null; SqlDataAdapter da = null; SqlCommand cmd = null; try { string sql = "UPDATE * FROM Tab1"; string connStr = WebConfigurationManager.ConnectionStrings["Employees"].ConnectionString; conn = new SqlConnection(connStr); conn.Open(); cmd=new SqlCommand(sql,conn); da = new SqlDataAdapter(sql, conn); da.UpdateCommand = cmd; da.Update(ds.Tables[0]); return true; } catch (Exception ex) { throw ex; } finally { if (conn != null) conn.Close(); if (da != null) da.Dispose(); } } Where can be problem?

  Read the article

• error in the syntax of cmd parameters

  - by KATy

  hi guyz in this method i m just adding the values to the db. temp is a object. the field value and variables in the object re havin the same name.. dono y this error s comin plz help me out... public virtual void Save_input_parameter_details(Test_Unit_BLL temp ) { SqlConnection con; con = new SqlConnection("Data Source=VV;Initial Catalog=testingtool;User ID=sa;Password=sa;"); con.Open(); SqlCommand cmd, cmd2, cmd3; //try //{ for (int i = 0; i < temp.No_Input_parameters; i++) { cmd2 = new SqlCommand("insert into Input_parameter_details values(@Input_Parameter_name,@Input_Parameter_datatype,@noparams,@class_code", con); cmd2.Parameters.AddWithValue("@Input_Parameter_datatype", temp.Input_Parameter_datatype[i]); cmd2.Parameters.AddWithValue("@Input_Parameter_name", temp.Input_Parameter_name[i]); cmd2.Parameters.AddWithValue("@noparams", temp.No_Input_parameters); cmd2.Parameters.AddWithValue("@class_code",temp.class_code); cmd2.ExecuteNonQuery(); } //} //catch (Exception ex) // { // MessageBox.Show("error"+ex); // } }

  Read the article

• asp.net, how to change text color to red in child nodes in VB codes?

  - by StudentIT

  I got everything worked now by list of server Name but I want to add a IF statement by checking a column from SQL Server called Compliance by either True or False value listed. If it False, the Name will change text color to Red. If it True, the Name won't change text color. I am not sure how add that in VB codes side. I am pretty sure that I would need to put IF statement inside While dr.Read(). I am pretty new to VB.Net and not sure which VB code that change text color. Here is my VB codes, Sub loadData() 'clear treeview control TreeViewGroups.Nodes.Clear() 'fetch owner data and save to in memory table Dim sqlConn As New System.Data.SqlClient.SqlConnection((ConfigurationManager.ConnectionStrings("SOCT").ConnectionString)) Dim strSqlSecondary As String = "SELECT [Name] FROM [dbo].[ServerOwners] where SecondaryOwner like @uid order by [name]" 'Getting a list of True or False from Compliance column Dim strSqlCompliance As String = "SELECT [Compliance] FROM [dbo].[ServerOwners] where SecondaryOwner like @uid order by [name]" Dim cmdSecondary As New System.Data.SqlClient.SqlCommand(strSqlSecondary, sqlConn) Dim cmdCompliance As New System.Data.SqlClient.SqlCommand(strSqlCompliance, sqlConn) cmdSecondary.Parameters.AddWithValue("@uid", TNN.NEAt.GetUserID()) cmdCompliance.Parameters.AddWithValue("@uid", TNN.NEAt.GetUserID()) Dim dr As System.Data.SqlClient.SqlDataReader Try sqlConn.Open() Dim root As TreeNode Dim rootNode As TreeNode Dim firstNode As Integer = 0 'Load Primary Owner Node 'Create RootTreeNode dr = cmdSecondary.ExecuteReader() If dr.HasRows Then 'Load Secondary Owner Node 'Create RootTreeNode root = New TreeNode("Secondary Owner", "Secondary Owner") TreeViewGroups.Nodes.Add(root) root.SelectAction = TreeNodeSelectAction.None rootNode = TreeViewGroups.Nodes(firstNode) 'populate the child nodes While dr.Read() Dim child As TreeNode = New TreeNode(dr("Name"), dr("Name")) rootNode.ChildNodes.Add(child) child.SelectAction = TreeNodeSelectAction.None End While dr.Close() cmdSecondary.Dispose() End If 'check if treeview has nodes If TreeViewGroups.Nodes.Count = 0 Then noServers() End If Catch ex As Exception hide() PanelError.Visible = True LabelError.Text = ex.ToString() Finally sqlConn.Dispose() End Try End Sub

  Read the article

• Please help debug this ASP.Net [VB] code. Trying to write to text file from SQL Server DB.

  - by NJTechGuy

  I am a PHP programmer. I have no .Net coding experience (last seen it 4 years ago). Not interested in code-behind model since this is a quick temporary hack. What I am trying to do is generate an output.txt file whenever the user submits new data. So an output.txt file if exists should be replaced with the new one. I want to write data in this format : 123|Java Programmer|2010-01-01|2010-02-03 124|VB Programmer|2010-01-01|2010-02-03 125|.Net Programmer|2010-01-01|2010-02-03 I don't know VB, so not sure about string manipulations. Hope a kind soul can help me with this. I will be grateful to you. Thank you :) <%@ Import Namespace="System.IO" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script language="vb" runat="server"> sub Page_Load(sender as Object, e as EventArgs) Dim sqlConn As New SqlConnection("Data Source=winsqlus04.1and1.com;Initial Catalog=db28765269;User Id=dbo2765469;Password=ByhgstfH;") Dim myCommand As SqlCommand Dim dr As SqlDataReader Dim FILENAME as String = Server.MapPath("Output4.txt") Dim objStreamWriter as StreamWriter ' If Len(Dir$(FILENAME)) > 0 Then Kill(FILENAME) objStreamWriter = File.AppendText(FILENAME) Try sqlConn.Open() 'opening the connection myCommand = New SqlCommand("SELECT id, title, CONVERT(varchar(10), expirydate, 120) AS [expirydate],CONVERT(varchar(10), creationdate, 120) AS [createdate] from tblContact where flag = 0 AND ACTIVE = 1", sqlConn) 'executing the command and assigning it to connection dr = myCommand.ExecuteReader() While dr.Read() objStreamWriter.WriteLine("JobID: " & dr(0).ToString()) objStreamWriter.WriteLine("JobID: " & dr(2).ToString()) objStreamWriter.WriteLine("JobID: " & dr(3).ToString()) End While dr.Close() sqlConn.Close() Catch x As Exception End Try objStreamWriter.Close() Dim objStreamReader as StreamReader objStreamReader = File.OpenText(FILENAME) Dim contents as String = objStreamReader.ReadToEnd() lblNicerOutput.Text = contents.Replace(vbCrLf, "<br>") objStreamReader.Close() end sub </script> <asp:label runat="server" id="lblNicerOutput" Font-Name="Verdana" />

  Read the article

• Rolling Back a Transaction with MySQL Connector in VB.net

  - by Jonathan

  Hey all- I have one multi-row INSERT statement (300 or so sets of values) that I would like to commit to the MySQL database in an all-or-nothing fashion. insert into table VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); In some cases, a set of values in the command will not meet the criteria of the table (duplicate key, for example). When that happens I do not want any of the previous sets added to the database. I've implemented this with the following code, however, my rollback command doesn't appear to be making a difference. I've used this documentation: http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqltransaction.html Dim transaction As MySqlTransaction = sqlConnection.BeginTransaction() sqlCommand = New MySqlCommand(insertStr, sqlConnection, transaction) Try sqlCommand.ExecuteNonQuery() Catch ex As Exception writeToLog("EXCEPTION: " & ex.Message & vbNewLine) writeToLog("Could not execute " & sqlCmd & vbNewLine) Try transaction.Rollback() writeToLog("All statements were rolled back." & vbNewLine) Return False Catch rollbackEx As Exception writeToLog("EXCEPTION: " & rollbackEx.Message & vbNewLine) writeToLog("All statements were not rolled back." & vbNewLine) Return False End Try End Try transaction.commit() I get the DUPLICATE KEY exception thrown, no Rollback Exception thrown, and every set of values up to duplicate key committed to the database. What am I doing wrong? Thanks- Jonathan

  Read the article

• CascadingDropDown taking two parameters...

  - by WeeShian

  I have a page with 3 dropdownlist, 2nd and 3rd dropdownlist are added with CascadingDropDown. 3rd dropdownlist will take parameters from 1st and 2nd dropdownlist. So, in current example for CascadingDropDown i have found from google, they are only passing one parameter to the WebService method. How can pass two parameters to the service method, so that my 3rd dropdownlist will based on the SelectedValue of 1st and 2nd dropdownlist? <WebMethod()> _ Public Function GetTeams(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue() Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString Dim sqlConn As SqlConnection = New SqlConnection(strConnection) Dim strTeamQuery As String = "SELECT * FROM TEAM WHERE conf_id = @confid" Dim cmdFetchTeam As SqlCommand = New SqlCommand(strTeamQuery, sqlConn) Dim dtrTeam As SqlDataReader Dim kvTeam As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues) Dim intConfId As Integer If Not kvTeam.ContainsKey("Conference") Or Not Int32.TryParse(kvTeam("Conference"), intConfId) Then Return Nothing End If cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId) Dim myTeams As New List(Of CascadingDropDownNameValue) sqlConn.Open() dtrTeam = cmdFetchTeam.ExecuteReader While dtrTeam.Read() Dim strTeamName As String = dtrTeam("team_name").ToString Dim strTeamId As String = dtrTeam("team_id").ToString myTeams.Add(New CascadingDropDownNameValue(strTeamName, strTeamId)) End While Return myTeams.ToArray End Function This is the sample code i found! As you can see in the code, '@confid' will be passed from 2nd dropdownlist! So, hw do i modify this code to get the selected value from 1st dropdownlist as well??

  Read the article

• sorting filtered data in asp.net listview

  - by user791345

  I've created a listview that's filled up with a list of guitars from the database on page load like so: protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["GuitarsLTDBConnectionString"].ToString()); SqlCommand cmd = new SqlCommand("SELECT * FROM Guitars", con); SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText, con); DataTable dt = new DataTable(); da.Fill(dt); lvGuitars.DataSource = dt; lvGuitars.DataBind(); } The following code filters that list of guitars by a certain Make when the user checks the checkbox corresponding to that make protected void chkOrgs_SelectedIndexChanged(object sender, EventArgs e) { DataTable dt = (DataTable)lvGuitars.DataSource; DataView dv = new DataView(dt); if (chkOrgs.SelectedValue == "Gibson") { dv.RowFilter = "Make = 'Gibson' OR Make='Fender'"; } lvGuitars.DataSource = dv.ToTable(); lvGuitars.DataBind(); } Now, what I want to do is be able to sort the latest data that is present within the listview. Meaning, if sort is clicked before filtering, the it should sort all data. If sort is clicked after filtering, it should sort the filtered data. I'm using the following code, which is triggered upon a LinkButton click protected void lnkSortResults_Click(object sender, EventArgs e) { DataTable dt = (DataTable)lvGuitars.DataSource; DataView dv = new DataView(dt); dv.Sort = "Make ASC"; lvGuitars.DataSource = dv.ToTable(); lvGuitars.DataBind(); } The problem is that all the data that the listview was loaded with before any filtering is sorted, and not the latest filtered data. How can I change this code so that the latest data available in the listview is the one that's sorted? Thanks

  Read the article

• Optimizing C# code in MVC controller

  - by cc0

  I am making a number of distinct controllers, one relating to each stored procedure in a database. These are only used to read data and making them available in JSON format for javascripts. My code so far looks like this, and I'm wondering if I have missed any opportunities to re-use code, maybe make some help classes. I have way too little experience doing OOP, so any help and suggestions here would be really appreciated. Here is my generalized code so far (tested and works); using System; using System.Configuration; using System.Web.Mvc; using System.Data; using System.Text; using System.Data.SqlClient; using Prototype.Models; namespace Prototype.Controllers { public class NameOfStoredProcedureController : Controller { char[] lastComma = { ',' }; String oldChar = "\""; String newChar = "&quot;"; StringBuilder json = new StringBuilder(); private String strCon = ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString; private SqlConnection con; public StoredProcedureController() { con = new SqlConnection(strCon); } public string do_NameOfStoredProcedure(int parameter) { con.Open(); using (SqlCommand cmd = new SqlCommand("NameOfStoredProcedure", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@parameter", parameter); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { json.AppendFormat("[{0},\"{1}\"],", reader["column1"], reader["column2"]); } } con.Close(); } if (json.Length.ToString().Equals("0")) { return "[]"; } else { return "[" + json.ToString().TrimEnd(lastComma) + "]"; } } //http://host.com/NameOfStoredProcedure?parameter=value public ActionResult Index(int parameter) { return new ContentResult { ContentType = "application/json", Content = do_NameOfStoredProcedure(parameter) }; } } }

  Read the article

• Violation of PRIMARY KEY constraint 'PK_

  - by abhi

  hi am trying to write a code in which i need to perform a update but on primary keys how do i achieve it i have written the following code: kindly look at it let me know where m wrong Protected Sub rgKMSLoc_UpdateCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs) Handles rgKMSLoc.UpdateCommand Try KAYAReqConn.Open() If TypeOf e.Item Is GridEditableItem Then Dim strItemID As String = CType(e.Item.FindControl("hdnID"), HiddenField).Value Dim strrcmbLocation As String = CType(e.Item.FindControl("rcmbLocation"), RadComboBox).SelectedValue Dim strKubeLocation As String = CType(e.Item.FindControl("txtKubeLocation"), TextBox).Text Dim strCSVCode As String = CType(e.Item.FindControl("txtCSVCode"), TextBox).Text SQLCmd = New SqlCommand("SELECT * FROM MstKMSLocKubeLocMapping WHERE LocationID= '" & rcmbLocation.SelectedValue & "'", KAYAReqConn) Dim dr As SqlDataReader dr = SQLCmd.ExecuteReader If dr.HasRows Then lblMsgWarning.Text = "<font color=red>""User ID Already Exists" Exit Sub End If dr.Close() SQLCmd = New SqlCommand("UPDATE MstKMSLocKubeLocMapping SET LocationID=@Location,KubeLocation=@KubeLocation,CSVCode=@CSVCode WHERE LocationID = '" & strItemID & "'", KAYAReqConn) SQLCmd.Parameters.AddWithValue("@Location", Replace(strrcmbLocation, "'", "''")) SQLCmd.Parameters.AddWithValue("@KubeLocation", Replace(strKubeLocation, "'", "''")) SQLCmd.Parameters.AddWithValue("@CSVCode", Replace(strCSVCode, "'", "''")) SQLCmd.Parameters.AddWithValue("@Status", "A") SQLCmd.ExecuteNonQuery() lblMessageUpdate.Text = "<font color=blue>""Record Updated SuccessFully" SQLCmd.Dispose() rgKMSLoc.Rebind() End If Catch ex As Exception Response.Write(ex.ToString) Finally KAYAReqConn.Close() End Try End Sub this is my designer page' Location: ' / ' DataSourceID="dsrcmbLocation" DataTextField="Location" DataValueField="LocationID" Height="150px" Kube Location: ' Class="forTextBox" MaxLength="4" onkeypress="return filterInput(2,event);" CSV Code: ' Class="forTextBox" MaxLength="4" onkeypress="return filterInput(2,event);" <tr class="tableRow"> <td colspan="2" align="center" class="tableCell"> <asp:ImageButton ID="btnUpdate" runat="server" CommandName="Update" CausesValidation="true" ValidationGroup="Update" ImageUrl="~/Images/update.gif"></asp:ImageButton> <asp:ImageButton ID="btnCancel" runat="server" CausesValidation="false" CommandName="Cancel" ImageUrl="~/Images/No.gif"></asp:ImageButton> </td> </tr> </table> </FormTemplate> </EditFormSettings> Locationid is my primary key

  Read the article

• Insert records using ExecuteNonQuery, showing exception that invalid column name

  - by tina

  Hi all, I am using SQL Server 2008. I want to insert records into a table using ExecuteNonQuery, for that I have written: customUtility.ExecuteNonQuery("insert into furniture_ProductAccessories(Product_id, Accessories_id, SkuNo, Description1, Price, Discount) values(" + prodid + "," + strAcc + "," + txtSKUNo.Text + "," + txtAccDesc.Text + "," + txtAccPrices.Text + "," + txtAccDiscount.Text + ")"); & following is ExecuteNonQuery function: public static bool ExecuteNonQuery(string SQL) { bool retVal = false; using (SqlConnection con = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["dbConnect"].ToString())) { con.Open(); SqlTransaction trans = con.BeginTransaction(); SqlCommand command = new SqlCommand(SQL, con, trans); try { command.ExecuteNonQuery(); trans.Commit(); retVal = true; } catch(Exception ex) { //HttpContext.Current.Response.Write(SQL + "<br>" + ex.Message); //HttpContext.Current.Response.End(); } finally { // Always call Close when done reading. con.Close(); } return retVal; } } but it showing exception that invalid column name to Description1 and even it's value which coming from txtAccDesc.Text. I have tried by removing Description1 column, other records are getting inserted successfully. Could you please help me? Thanks.

  Read the article

• Binding search results to data grid

  - by Abid

  I want to add search functionality to my program. There's a class which has this function: public DataTable Search() { string SQL = "Select * from Customer where " + mField + " like '%" + mValue + "%'"; DataTable dt = new DataTable(); dt = dm.GetData(SQL); return (dt); } There are setter and getter properties for mField and mValue. DM is the object of class DataManagement, which has a method GetData: public DataTable GetData(string SQL) { SqlCommand command = new SqlCommand(); SqlDataAdapter dbAdapter = new SqlDataAdapter(); DataTable DataTable = new DataTable(); command.Connection = clsConnection.GetConnection(); command.CommandText = SQL; dbAdapter.SelectCommand = command; dbAdapter.Fill(DataTable); return (DataTable); } The search functionality is currently implemented like this: private void btnfind_Click(object sender, EventArgs e) { //cust is the object of class customer// if (tbCustName.Text != "") { cust.Field="CustName"; cust.Value = tbCustName.Text; } else if (tbAddress.Text != "") { cust.Value = tbAddress.Text; cust.Field="Address"; } else if (tbEmail.Text != "") { cust.Value = tbEmail.Text; cust.Field="Email"; } else if (tbCell.Text != "") { cust.Value = tbCell.Text; cust.Field = "Cell"; } DataTable dt = new DataTable(); dt = cust.Search(); dgCustomer.DataSource = dt; RefreshGrid(); } private void RefreshGrid() { DataTable dt = new DataTable(); dt = cust.GetCustomers(); dgCustomer.DataSource = dt; } This is not working. I don't know why. Please help.

  Read the article

• Getting table schema from a query

  - by Appu

  As per MSDN, SqlDataReader.GetSchemaTable returns column metadata for the query executed. I am wondering is there a similar method that will give table metadata for the given query? I mean what tables are involved and what aliases it has got. In my application, I get the query and I need to append the where clause programically. Using GetSchemaTable(), I can get the column metadata and the table it belongs to. But even though table has aliases, it still return the real table name. Is there a way to get the aliase name for that table? Following code shows getting the column metadata. const string connectionString = "your_connection_string"; string sql = "select c.id as s,c.firstname from contact as c"; using(SqlConnection connection = new SqlConnection(connectionString)) using(SqlCommand command = new SqlCommand(sql, connection)) { connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); DataTable schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { foreach (DataColumn column in schema.Columns) { Console.WriteLine(column.ColumnName + " = " + row[column]); } Console.WriteLine("----------------------------------------"); } Console.Read(); } This will give me details of columns correctly. But when I see BaseTableName for column Id, it is giving contact rather than the alias name c. Is there any way to get the table schema and aliases from a query like the above? Any help would be great!

  Read the article

• ASP.NET SqlDataReader throwing error: Invalid attempt to call Read when reader is closed.

  - by Bugget

  This one has me stumped. Here are the relative bits of code: public AgencyDetails(Guid AgencyId) { try { evgStoredProcedure Procedure = new evgStoredProcedure(); Hashtable commandParameters = new Hashtable(); commandParameters.Add("@AgencyId", AgencyId); SqlDataReader AppReader = Procedure.ExecuteReaderProcedure("evg_getAgencyDetails", commandParameters); commandParameters.Clear(); //The following line is where the error is thrown. Errormessage: Invalid attempt to call Read when reader is closed. while (AppReader.Read()) { AgencyName = AppReader.GetOrdinal("AgencyName").ToString(); AgencyAddress = AppReader.GetOrdinal("AgencyAddress").ToString(); AgencyCity = AppReader.GetOrdinal("AgencyCity").ToString(); AgencyState = AppReader.GetOrdinal("AgencyState").ToString(); AgencyZip = AppReader.GetOrdinal("AgencyZip").ToString(); AgencyPhone = AppReader.GetOrdinal("AgencyPhone").ToString(); AgencyFax = AppReader.GetOrdinal("AgencyFax").ToString(); } AppReader.Close(); AppReader.Dispose(); } catch (Exception ex) { throw new Exception("AgencyDetails Constructor: " + ex.Message.ToString()); } } And the implementation of ExecuteReaderProcedure: public SqlDataReader ExecuteReaderProcedure(string ProcedureName, Hashtable Parameters) { SqlDataReader returnReader; using (SqlConnection conn = new SqlConnection(connectionString)) { try { SqlCommand cmd = new SqlCommand(ProcedureName, conn); SqlParameter param = new SqlParameter(); cmd.CommandType = System.Data.CommandType.StoredProcedure; foreach (DictionaryEntry keyValue in Parameters) { cmd.Parameters.AddWithValue(keyValue.Key.ToString(), keyValue.Value); } conn.Open(); returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (SqlException e) { throw new Exception(e.Message.ToString()); } } return returnReader; } The connection string is working as other stored procedures in the same class run fine. The only problem seems to be when returning SqlDataReaders from this method! They throw the error message in the title. Any ideas are greatly appreciated! Thanks in advance!

  Read the article

• How can I synchronise two datatables and update the target in the database?

  - by Craig

  I am trying to synchronise two tables between two databases. I thought that the best way to do this would be to use the DataTable.Merge method. This seems to pick up the changes, but nothing ever gets committed to the database. So far I have: string tableName = "[Table_1]"; string sql = "select * from " + tableName; using (SqlConnection sourceConn = new SqlConnection(ConfigurationManager.ConnectionStrings["source"].ConnectionString)) { SqlDataAdapter sourceAdapter = new SqlDataAdapter(); sourceAdapter.SelectCommand = new SqlCommand(sql, sourceConn); sourceConn.Open(); DataSet sourceDs = new DataSet(); sourceAdapter.Fill(sourceDs); using (SqlConnection targetConn = new SqlConnection(ConfigurationManager.ConnectionStrings["target"].ConnectionString)) { SqlDataAdapter targetAdapter = new SqlDataAdapter(); targetAdapter.SelectCommand = new SqlCommand(sql, targetConn); SqlCommandBuilder builder = new SqlCommandBuilder(targetAdapter); targetAdapter.InsertCommand = builder.GetInsertCommand(); targetAdapter.UpdateCommand = builder.GetUpdateCommand(); targetAdapter.DeleteCommand = builder.GetDeleteCommand(); targetConn.Open(); DataSet targetDs = new DataSet(); targetAdapter.Fill(targetDs); targetDs.Tables[0].TableName = tableName; sourceDs.Tables[0].TableName = tableName; targetDs.Tables[0].Merge(sourceDs.Tables[0]); targetAdapter.Update(targetDs.Tables[0]); } } At the present time, there is one row in the source that is not in the target. This row is never transferred. I have also tried it with an empty target, and nothing is transferred.

  Read the article

• Filter of Data in Gridview logical error please using asp.net

  - by RajuBabli Abbasi

  I wanted to filter the Data in asp.net but my Data is not filtering i have some logical error So please help me for this case i will be very thanks full to those who will help me please consider my code and replay me with code if you can so please i am waiting for your replay thanks again my asp.cs file is protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DisplayStudentInformation(); } } private void DisplayStudentInformation() { string filter = "%" + filterTextBox.Text + "%"; if (filter == String.Empty) filter = "%"; try { using (SqlDataReader reader = DAC.GetCompanyInformation(filter)) {//reader.Read(); StudentGridView.DataSource = reader; StudentGridView.DataBind(); } } catch (SqlException ex) { StatusLabel.Text = ex.Message; } } my .aspx file is asp:Table ID="Tabel" runat ="server" asp:TableRow asp:TableCell asp:Label ID="filterLabel" runat ="server" Text ="Company Name Filter:" AssociatedControlID="filterTextBox" /asp:TableCell asp:TableCell asp:TextBox ID="filterTextBox" runat="server" MaxLength ="50" /asp:TableCell asp:TableCell asp:Button ID="refreshButton" runat ="server" Text ="Filter" CausesValidation="false" / /asp:TableCell /asp:TableRow /asp:Table My DAC file is public static SqlDataReader GetCompanyInformation(string filter) { SqlDataReader reader; string sql = "SELECT * FROM Student WHERE LastName LIKE @prmLastName "; using(SqlCommand command = new SqlCommand (sql,ConnectionManager.GetConnection())) {//In ExecuteReader we pass the CommandBehavior as singleResult because we need the Single result and also passing the close connection when Datais retriev // command.Parameters.Add("@prmLastName", SqlDbType.VarChar, 25).Value = filter; command.Parameters.AddWithValue("@prmLastName", filter); reader = command.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.CloseConnection); } return reader; } Note:- When i don't use the If(!Ispostback) Condition and simply pass the DisplayStudentInformation(); method in my page load then Data can be filter but with If(!IspostBack ) condition which is also important for updating the data and for other purpose . Data can be filter . Se aim of the expert is that Filter the Data in a gridview using condition of IF(!IspostBack ) means without the removing is post back condition Filter the Data . I have been ask other about this question but no body solve this so please help me i will be very thanks full to you all ok

  Read the article

< Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >