We got this little issue of passing around (updated and inserting) small dataSets (20-500 or so records) from fat clients to a remote server running .Net / SQL 2005.
Instead of trying to fudge the data and make updates in .Net, we just decided it would be a lot less nonsense if we just wrap up the dataSets in an XML string, have .Net just pass it thru as a parameter in a SP and let SQL parse it out using openXML. The data is small and server is low use, so I'm not worried about overhead, but I would like to know the best methods and DO's & Don'ts to parse the XML and make the updates/inserts....and maybe a few good examples. The few examples I've come across are kind of sketchy. Seems it's not a real popular method of handling updates.
Hello, Is there any way to run a parameterized statement against SQL when you have an unknown ammount of inputs. Instead of doing the following: FOR i as integer = 0 to topValuestrWhere &= " OR myColName='"& myArr(i) &"'" NEXT wash up the string and submit this to SQL, I want to do it with parameters. I'm calling a sproc in SQL and I would prefer just to send in a string like "1,4,42',45" in a parameter split that in SQL and run my query.The query would be something like "...WHERE myColname=1 OR myColname=2 OR myColName=3" I only want to declare one parameter and not use string concatenation as described above.It doesn't matter if there's a split function in SQL or not, as long as it solves my problem in an efficient manner. Cheers!/Eskil
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
<p>Hi , I would like to know anyway to insert two strings into a row with 2 lines created?My codes are as below If NodeName = "subProductPrice" Then If xmlrder.NodeType = Xml.XmlNodeType.Text Then SubPrPriceList.Add(xmlrder.Value) For i = 0 To SubPrPriceList.Count - 1 SubPrPrice = CStr(SubPrPriceList.Item(i)) PriceBrkDownStr &= SubPrPrice Next PricePerDay = SvcDate & PriceBrkDownStr dr("dailyPrice") = PricePerDay End If End If Although both the SvcDate and PriceBrkDownStr are inserted into the same row but they are displayed into one line such as below: <u>dailyPrice </u>02/03/2007 03/03/2007 120 230 Any idea how to make the date and price separate into two rows in the same table grid row? thanks =)</p>
Here is the situation, please let me know if you have any tips:..TXT files in a share at \fooSPROCS run daily parses of many things, including data on that share. Theother day, we encountered rows in the TXT files which looked like:column1Row1data,column2Row1datacolumn1Row2data,column2Row2data...etc..However, column2 was about 6000 bytes of unicode. We are bulk insertinginto a table specifying nvarchar(4000). When it encounters high unicoderows, it throws a truncation error (16).We really need information contained in the first 200 bytes of the string incolumn2. However, the errors are causing the calling SPROC to abort.Please let me know if you have any suggestions on workarounds for thissituation. Ideally, we would only Bulk Insert a sub-section of column2 ifpossible.Thanks!/Ty
Hi,A person from my company's IT department approached me a few days ago saying that they followed some instructions on a recent security vulnerability in IIS whereby they where told to turn impersonation off in IIS. I'm afraid I don't know the exact procedure, but I do know that it took down all of my applications with the following error:"Failed to generate a user instance of SQL Server"I do NOT have impersonation enabled in my web.config, however all of my connection strings look like the following:<add name="myConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>From what I've read, this type of connection string, which VWD 2005 generates by default, is not the correct way to go about connecting to a database. Could this be my problem??Thanks!
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
My problem is that I can' t update my gridview. I am stuck at this problem for several days now and have no clue what the sollution could be. Hope somebody can give me some ideas of what I am doing wrong. <asp:GridView ID="GridView_phl_berichten" runat="server" AutoGenerateColumns="False" BorderColor="DimGray" BorderStyle="Solid" BorderWidth="1px" DataKeyNames="phlb_id" DataSourceID="SqlDataSource_phl_berichten" GridLines="Horizontal" Width="716px"> <Columns> <asp:TemplateField HeaderText="Van datum" SortExpression="phlb_van"> <EditItemTemplate> <asp:Calendar ID="phlb_van" runat="server" SelectedDate='<%# eval("phlb_van") %>' VisibleDate='<%# eval("phlb_van") %>'></asp:Calendar> </EditItemTemplate> <ItemStyle VerticalAlign="Top" Width="5cm" /> <ItemTemplate> <asp:Label ID="Label_datum_van" runat="server" Text='<%# eval("phlb_van", "{0:d}") %>'></asp:Label> <br /> <br /> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton> <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" OnClientClick=" return confirm('Bent u zeker dat u dit bericht wil verwijderen ?') "></asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Tot datum" SortExpression="phlb_tot"> <EditItemTemplate> <asp:Calendar ID="phlb_tot" runat="server" SelectedDate='<%# eval("phlb_tot") %>' VisibleDate='<%# eval("phlb_tot") %>'></asp:Calendar> </EditItemTemplate> <ItemStyle VerticalAlign="Top" Width="4cm" /> <ItemTemplate> <asp:Label ID="Label_datum_tot" runat="server" Text='<%# eval("phlb_tot", "{0:d}") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Bericht" SortExpression="phlb_bericht"> <EditItemTemplate> <asp:TextBox ID="phlb_bericht" runat="server" Height="98px" Text='<%# eval("phlb_bericht") %>' TextMode="MultiLine" Width="527px"></asp:TextBox><br /> <br /> <asp:LinkButton ID="LinkButtonUpdate" runat="server" CausesValidation="False" CommandName="Update" Font-Size="Small" OnClientClick="return confirm('Bent u zeker dat u dit bericht wil opslaan ?')" Text="Update"></asp:LinkButton> <asp:LinkButton ID="LinkButtonCancel" runat="server" CausesValidation="False" CommandName="Cancel" Font-Size="Small" Text="Cancel"></asp:LinkButton> </EditItemTemplate> <ItemTemplate> <asp:TextBox ID="TextBox_bericht" runat="server" Height="98px" ReadOnly="True" Text='<%# eval("phlb_bericht") %>' TextMode="MultiLine" Width="527px"></asp:TextBox> </ItemTemplate> </asp:TemplateField>
<asp:TemplateField HeaderText="phlb_id" SortExpression="phlb_id" Visible="False"> <EditItemTemplate> <asp:Label ID="phlb_id" runat="server" Text='<%# Bind("phlb_id") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label_bericht_id" runat="server" Text='<%# Bind("phlb_id") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <HeaderStyle BackColor="#F3F3F3" /> </asp:GridView> and here is my sqldatasource-object : <asp:SqlDataSource ID="SqlDataSource_phl_berichten" runat="server" ConnectionString="<%$ ConnectionStrings:ConEP %>" SelectCommand="sp_berichten_PHL_alle" SelectCommandType="StoredProcedure" UpdateCommand="UPDATE T_bericht_PHL set phlb_bericht = @phlb_bericht where phlb_id = @phlb_id"> <UpdateParameters> <asp:Parameter Name="phlb_bericht" Type="String" /> <asp:Parameter Name="phlb_id" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> It always stay giving the message that you can ' t insert null values in the column " bericht " ... ; All my variables have the same names as the columns in my table. So it is finding the id , but it doesn' t seem to find the value of my edititemtemplatefield "bericht". Hope somebody can give some suggestions on what I can try to do else.
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
Im looking for example code to make a sql update... I want to use command.Parameters and variables from text boxes and i'm unsure how to do this... Please help. This code below doesn't work but it is an example of what i've been working with.. <code> { string conn = string.Empty; ConnectionStringsSection connectionStringsSection = WebConfigurationManager.GetSection("connectionStrings") as ConnectionStringsSection; if (connectionStringsSection != null) { ConnectionStringSettingsCollection connectStrings = connectionStringsSection.ConnectionStrings; ConnectionStringSettings connString = connectStrings["whowantsConnectionString"]; conn = connString.ConnectionString; using (SqlConnection connection = new SqlConnection(conn)) using (SqlCommand command = new SqlCommand("UPDATE users SET currentscore=5)", connection)) { updateCommand.Parameters.Add("@currentscore", SQLServerDbType.numeric, 18, "currentscore"); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } }</code>
I have a web form that is populated from the DB values. User can update teh record by keying in new values in the form fields. In my .vb class file I have an update statement - strSql = "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" I have the parameters defined as cmd.Parameters.Add(New SqlParameter("@firstname", _FirstName)) cmd.Parameters.Add(New SqlParameter("@id", _Id)) _FirstName & _Id are the private variables whose values a set thru set and get methods. I expect _FirstName to have the new value I am keying in the form. Can any body help me with what's wrong here? This is not updating the database. When I do trace.write it shows me the Update statement as "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" instead of @firstname and @id being replace by actual values. Please help. Thanks,Shaly
Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating Dim quantity As Integer Dim weight As Integer Dim packed As Integer quantity = CInt(bagsOnPallet.Text) weight = CInt(lbsInBags.SelectedValue) packed = weight * quantity e.Command.Parameters("@packedLbs").Value += packed e.Command.Parameters("@AvailableLbs").Value -= packed End Sub
hi, please someone can help me. I get error 0x80040E14L // The command contained one or more errors. I think that the error is in the sql update command. this is my code:
I have a problem here where I am trying to use SQL Parameters to update a column in the database, but the problem is that I need to concatenate the same column to the SQL parameter. The code I have is below, but it throws a Format Exception...
UPDATE tbl_NSP_Inspection SET Description = Description + @InspectionDescription
...It is because I am trying to Append teh data in the description column to the SQL Parameter ( @InspectionDescription). How do I actually do this using SQL Parameters?
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hello, this is my code: SqlCommand cmd = new SqlCommand("INSERT INTO Users (Username,Password) " + "VALUES ('@username','@password' ", new SqlConnection(my_ConnectionString));
cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); But in the database, the row inserted is exactly this:"@username" "@password"I mean, the parameters are not inserted :SPlease, tell me the error in the code...Thank you so much,Carlos.
I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening. SqlConnection conn = new SqlConnection(@"Server=.SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open(); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;
// delete all rows cmd.CommandText = "DELETE FROM Person"; cmd.ExecuteNonQuery();
// parameter insert cmd.CommandText = "SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@PersonID, @PersonName, @PersonAge)"; p = new SqlParameter("@PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p = new SqlParameter("@PersonName", "Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p = new SqlParameter("@PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText = "SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText = "SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();
Response.Write("<br>end.");
} finally { conn.Close(); } I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing... Thank you.
I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast. And then firing off the update command that contains that parameter from a button. But it's not changing my data on my server when I do so. I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes. All this works fine, just not sp that should update my data when I hit the submit button. It's supposed to update one of my tables to whatever the selected value is from my drop down list. But it doesn't even change anything. It just refreshes the page and goes back to the original value for my drop down list. Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc. It just won't change the data from the page when I try to.
This is what the stored procedure looks like: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS BEGIN UPDATE [Current_tbl] SET ID = @SelectedID WHERE PrimID = '1' END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ And here's my aspx page: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Data Verification Editor</title> </head> <body> <form id="form1" runat="server"> <asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>" SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet"> </asp:SqlDataSource> <asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>" SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader" UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure"> <UpdateParameters> <asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" /> </UpdateParameters> </asp:SqlDataSource> <table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;"> <tr> <td colspan="2" style="font-family:Tahoma; font-size:10pt;"> Please select one of the following:<br /> </td> </tr> <tr> <td colspan="2"> <asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title" DataValueField="ID" style="width: 100%; height: 24px; background: gold"> </asp:DropDownList> </td> </tr> <tr> <td style="width:50%;"> <asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True" Font-Size="8pt" Width="100%" /> </td> <td style="width:50%;"> <asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True" Font-Size="8pt" Width="100%" /> </td> </tr> <tr> <td colspan="2"> <asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label> </td> </tr> </table> </form> </body> </html> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ And here's my code behind: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Imports System.Data Imports System.Data.SqlClient Partial Class Editor Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Saved_lbl.Text = "" Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;") Dim View1 As New DataView Dim args As New DataSourceSelectArguments View1 = SQLDS_Fill.Select(args) Dim Row As DataRow For Each Row In View1.Table.Rows Ver_ddl.SelectedValue = Row("ID") Next Row SQLDS_Fill.Dispose() End Sub Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click SQLDS_Update.Update() Saved_lbl.Text = "Thank you. Your changes have been saved." SQLDS_Update.Dispose() End Sub End Class ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hi,i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.This happens in event DetailsView1_ItemUpdating with my own code.It works without parameters (i know, bad way) like this:SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"But when using parameters like here below, i get the error:"Input string was not in a correct format"Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdatingSqlDataSource1.UpdateCommand = "UPDATE mytable set name= @myname"SqlDataSource1.UpdateParameters.Add("@myname", SqlDbType.NVarChar, na)SqlDataSource1.Update()End SubI don't see what's wrong here.Thanks for helpTartuffe
I have a sqldatasource in a wizard. When I click the finishbutton in the wizard I want to insert some values (from textboxes, dropdownlists in my wizard) into a mysql-database. For this I use parameters, I add some values in the design mode and some in codebehind. The parameters in codebehind are added perfectly but the other parameters in designmode (name, email) just inserts null. Why? My datasource: <asp:SqlDataSource ID="dsInsert" InsertCommand="INSERT INTO tbltest (name, email, city) VALUES (?name, ?email, ?city)" runat="server" ConnectionString="<%$ ConnectionStrings:conn %>" ProviderName="MySql.Data.MySqlClient"> <InsertParameters> <asp:FormParameter Name="?name" ConvertEmptyStringToNull="true" Type="string" FormField="name" /> <asp:FormParameter Name="?email" ConvertEmptyStringToNull="true" Type="string" FormField="email" /> </InsertParameters> </asp:SqlDataSource> The codebehind: protected void finishButtonClick(object sender, EventArgs e) { dsInsert.InsertParameters.Add("?city", TypeCode.Int32, city.SelectedValue); dsInsert.Insert(); }
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
Hi there, I am trying to use the ADO technology within MS Access 2000. Basically I'd like to use parameters in a command object to insert a new record and get its newly inserted ID. But instead of it it returns error:
Run-time error '-2147217900 (80040e14) Must declare the scalar variable "@ii_file"
Isn't this varaible (and all the rest of variables) declared by setting a parameter ".Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii) "? I'd like to avoid using stored procedures in order to create the whole SQL statement from the client side. Thanks!
Darek
Public Sub save_import() Dim rs As ADODB.Recordset, cmd As ADODB.Command, rec_affected As Long
I have an update command with 7 parameters, but at run time the order to the parameters gets mixed up. I'm using a stored procedure. At first I have the command type set to text, and was calling it using EXEC spName ?,?,?,?,?,?,? I then named each of the parameters and set their sources. The parameters are like this (samepl name, then source, then type): A : QueryString - intB: Control - intC: Control - intD: None - intE: None - decimalF: Control - datetimeG: Control - datetime At run time I was getting an error that an integer couldn't be converted to date time. So I put a breakpoint in the Updating event and then looked at the parameters prior to update. This is how they looked (Parameter index, paramter name): [0] A[1] B[2] D[3] E[4] F[5] G[6] C It didn't maek any sense. Do, I deleted all of the paramters and readded them. That didnt' work. Then I changed the command to StoredProcedure and refreshed the parameters from the stored proc and it brought them in the right order, but the problem remains the same. I looked at the page source, and there are no indexes in the page source, but the parameters are listed in the proper creation order, as follows:<UpdateParameters><asp:QueryStringParameter Type="Int32" Name="PROJ_ID" QueryStringField="pid"></asp:QueryStringParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="TASK_UID" ControlID="fvTask"></asp:ControlParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="ASSN_UID" ControlID="gvResources"></asp:ControlParameter><asp:Parameter Type="Int32" Name="RES_UID"></asp:Parameter><asp:Parameter Type="Double" Name="Work"></asp:Parameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Start" ControlID="TASK_START_DATETextBox"></asp:ControlParameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Finish" ControlID="TASK_FINISH_DATETextBox"></asp:ControlParameter></UpdateParameters> No mater what I do, at run time ASSN_UID is always the last parameter. I've also run a SQL trace to see how it is actually being executed, and sure enough, its passing the value for ASSN_UID as the last parameter, which obviously doesn't work. Any ideas as to why this would happen or how to fix it? (I guess I can reorder the patameters in the stored proc to match how they are being passed, but still, that wouldn't be a very comfortable solution, since it could perhaps revert at some point or something)
I have a stored procedure that updates about a dozen rows.
I have some overloaded functions that I should update different combinations of the rows - 1 function might update 3 rows, another 7 rows.
Do I have to write a stored procedure for each function or I can I handle it in the Stored Procedure. I realise I can have default values but I the default values could overwrite actual data if the values are not supplied but have been previously written.
Set prm = New Parameter prm.Type = adInteger prm.Value = gsPlcCode cmd.Parameters.Append prm
Set ttt = New Parameter ttt.Type = adVarChar ttt.Value = "TEST" cmd.Parameters.Append ttt
'cmd(1) = gsPlcCode 'cmd(2) = "Test"
Set rs = cmd.Execute
It works when passing one but NOT two ?? Can anyone shed any light ?? I've also tried using the Create Parameter method but again one is fine but passing two parameters doesn't work ?
The stored procedure just performs an update on a table.
Basically I want to be able to update any fields in a table (specific record) when they have been changed by the user. I have placed boolean variables in the change events to detect any user edits. If any field(s) have been edited I then want to send each field within the current record as parameters to the update stored procedure.
Any clues as to how to get an update stored procedure to except multiple variables.
I have trawled through loads of documentation to no avail.
