I need to add an 'InsertCommand' to my query via sqldatasource, but i cannot see this option, i only have the 'order', 'where' and 'advanced' option, could you please advice?
I have a process that inserts a new record using the InsertCommand of a SqlDataSource. As part of the process, I need to insert data the is available in a different SqlDataSource. I was trying this with the Insert Parameter:
I cannot get an Oracle sequence to work in an Sqldatasource InsertCommand:I've tried the following: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)And: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (:Testing,:CUSTOMER_ID)<asp:Parameter DefaultValue="whsts_id.nextval" Name="testing" />AndInsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID) This is the classic error I get:ORA-01036: illegal variable name/numberI did review the asp.net forums before posting this. As well, I've looked through the VWD 2005 documentation and cannot find the answer to this question. Argg! Any help is appreciated. I've also worked to try and find out how to view the SQL that the Sqldatasource is generating, but I can't find the answer to this either.
Ok, so I'm a JSP guy and thing it should be easy to replace "@color" with t_color after I initialized it to red by String t_color = "red";and then calling the insert SqlDataSource1.Insert();here is insert command: InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" I've tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "+ t_color+")" Ive tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "<%$ t_color %>" )" Is there any easy way to do this? or Can I set it like @color = t_color? Thanks in advance for ANY help JSP turning ASP (Maybe)Dan
Hi, I have a SQLDataSource, button, textbox, and label. I take the text from the textbox, count it's occurance in the database, then assign the number to the label. The code works but I would like to provide a button that will execute/invoke the SQLDataSource. I have this in the click event for the button: Me.label1.Text = SqlDataSource1.Select(DataSourceSelectArguments.Empty) How do I execute a SQLDataSource from the code-behind for the button click event? Thanks.
Hi,I've never used an SQLDataSource programatically before.Its not even executing the select command because I've got a trace on the database and theres no executing of the Sproc.Here's my code behind: Dim sql As New SqlDataSource sql.ConnectionString = ConfigurationManager.ConnectionStrings("My connection").ConnectionString sql.SelectCommand = "Select_All" sql.SelectCommandType = SqlDataSourceCommandType.StoredProcedure sql.SelectParameters.Add("@Param1", txtParam1.Text) sql.SelectParameters.Add("@Param2", txtParam2.Text) sql.DataSourceMode = SqlDataSourceMode.DataReader gvOrderItemsReport.DataSource = sql gvOrderItemsReport.DataBind() Any ideas? I must be missing something.
Hello I have a piece of VB.NET code that generates an email on the SqlDataSource Inserted event. It appears to be executing twice because it is sending two emails. If I place the code on any other event, it just sends the one email. Does any have a suggestion on how to handle this? Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted Dim MailServerName As String = "alvexch01"Dim Message As MailMessage = New MailMessage Message.From = New MailAddress("sender@email.com")Message.To.Add("receiver@email.com") Message.Subject = "Near Miss" Message.Body = "Test" Message.IsBodyHtml = True Message.Priority = MailPriority.NormalDim MailClient As SmtpClient = New SmtpClient MailClient.Host = MailServerName MailClient.Send(Message) Message.IsBodyHtml = True Message.Dispose() End Sub
Hi, I'm trying to do a straight forward call to an Oracle Stored Procedure from a GridView Web Control using UpdateQuery. I created a simple procedure (then tried a package) with no parameters (then with a parameter) and tried to call it from UpdateQuery. I either get a Internal .Net Framework Data Provider error 30 with a parameter or Encountered the symbol "UARF_FUNCTIONAL_UPDATE" when expecting one of the following: := . ( @ % ; without using a paremeter. My updatequery on the sqldatasource control is: exec uarf_functional_update; I'm sure I'm missing something very simple (syntax or something), but I can't find the right direction in my help material. Any assistance would be greatly appreciated. Thanks, E.
Hi you all, In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource. In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!
hey. I have a formview - inertitemtemplate. In here I have a button with CommandName="Insert". In code behind under FormView1.ItemInserting I want to be able to abort the inserting in some cases. Is this possible?
I'm using a SQLDataSource and trying to do two inserts into two different tables with one InsertCommand, but it's not working. Here's the code I'm trying to use. Do you see anything wrong with the syntax? I keep getting an error that says error near ',' but I can't figure out why. Thanks
I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table. so like InsertCommand="INSERT INTO customer_mod (phone) Values (@Telephone_1) <asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" /> I don't think that is gonna work, so can you please help me make that code work?
My problem is when trying to insert it's coming back that "Column 'textmenu' cannot be null" who is right for that fieldCode below: <script language="vb" runat="server"> sub Page_Load(s as Object, e as EventArgs) if ( Page.IsPostBack ) then Exit sub end if end sub private sub SubmitBtn_Click(s As Object, e As EventArgs) SqlDataSource1.Insert() end sub</script> <asp:sqldatasource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test %>" ProviderName="<%$ ConnectionStrings:test.ProviderName %>" insertcommand="INSERT INTO test (textmenu) VALUES (@somevalue)"> <insertparameters> <asp:ControlParameter PropertyName="text" ControlID="CompanyNameBox" Name="somevalue" Type="String" /> </insertparameters> </asp:sqldatasource> <asp:textbox id="CompanyNameBox" runat="server" /> <asp:Button id="submitButton" Text="Envoyer" OnClick="SubmitBtn_Click" runat="server"/></asp:Content> So I change the constrain to accept "null" value then I check in the table database and there is a record but no valueSo I change the sql command to "INSERT INTO test (textmenu) VALUES ('test')" and it's working I have a record and the value "test" in the tableI don't know what I'm doing wrong up here with the @value. Can anyone help? Thanks
Hey, I have a page that inserts into a customers table in the DataBase a new customer account using this function: Public Function InsertCustomers(ByRef sessionid, ByVal email, ByVal pass, Optional ByVal fname = "", Optional ByVal lname = "", Optional ByVal company = "", Optional ByVal pobox = "", Optional ByVal add1 = "", Optional ByVal add2 = "", Optional ByVal city = "", Optional ByVal state = "", Optional ByVal postalcode = "", Optional ByVal country = 0, Optional ByVal tel = "") Dim result As New DataSet Dim tempid As Integer Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("Conn")) Dim Adcust As New SqlDataAdapter Adcust.InsertCommand = New SqlCommand Adcust.SelectCommand = New SqlCommand Adcust.InsertCommand.Connection = conn Adcust.SelectCommand.Connection = conn sessionExists(email, sessionid, 1) conn.Open() If fname = "" Then Adcust.InsertCommand.CommandText = "Insert Into neelwafu.customers(email,password,sessionid) Values('" & email & "','" & pass & "','" & sessionid & "')" Else Dim strsql As String strsql = "Insert Into neelwafu.customers" strsql = strsql & "(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values(" strsql = strsql & "'" & sessionid & "','" & email & "','" & pass & "','" & fname & "','" & lname & "','" & company & "','" & pobox & "','" & add1 & "','" & add2 & "','" & city & "','" & state & "','" & postalcode & "', " & country & ",'" & tel & "')" Adcust.InsertCommand.CommandText = strsql End If Adcust.InsertCommand.ExecuteNonQuery() Adcust.SelectCommand.CommandText = "Select Max(id) from neelwafu.Customers" tempid = CInt(Adcust.SelectCommand.ExecuteScalar()) conn.Close() Return tempid End Function -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Now, I am getting an error: Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The customers table has as a primary key the 'email'..... so plz can I know why am I getting this error ???? Thank you in advance Hiba
Hi, I am trying to set the InsertCommand of a SqlDataSource prior to a new record being created in DetailsView. It has to be changed because certain fields are not being used in one scenerio and it is causing Null's to be written to the database. Here is what I have tried: I set the Insert command on page Load: protected void Page_Load(object sender, EventArgs e) { SqlDataSource3.InsertCommand = "Insert into table (name,realName,type,extraValue) VALUES (@name,@realName,@type,@extraValue)"; } But I don't want to insert the "extraValue if a certain condition is true so I want to change the insert command to SqlDataSource3.InsertCommand = "Insert into table (name,realName,type) VALUES (@name,@realName,@type) I tried to do that in the following: protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e) { SqlDataSource3.InsertCommand = "Insert into table (name,realName,type,extraValue) VALUES (@name,@realName,@type,@extraValue)"; if (e.Exception != null) { ErrorMessageLabel.Text = "An error occured while entering this record. Please verify you have entered data in the correct format."; e.ExceptionHandled = true; Response.Write(e.Exception); } GridView1.DataBind(); GridView1.DataBind(); } But it doesn't seem to change, Any Help would be appreciated, Doug
I am trying to use a sqldataadapter to log a record to a table with a stored procedure call. I am getting the following error: "System.NullReferenceException: Object reference not set to an instance of an object. at ContactUs.LogEmail() in c:inetpubwwwrootMDWelcomeContactUs.aspx.vb:line 55"Here is my code: Protected Sub LogEmail() 'Trace.IsEnabled = True Dim strConnect As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString Dim strCommandText As String = "dbo.EmailsLogged_Insert" Dim objConnect As New SqlConnection(strConnect) Try objConnect.Open() Dim objDataAdapter As New SqlDataAdapter(strCommandText, objConnect) objDataAdapter.InsertCommand.CommandText = strCommandText'this is line 55 in my source where error is thrown objDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure With objDataAdapter.InsertCommand.Parameters .Add("@emailaddress", SqlDbType.VarChar, 255).Value = UsersEmail.Text .Add("@subject", SqlDbType.VarChar, 255).Value = Subject.Text .Add("@company", SqlDbType.VarChar, 255).Value = Company.Text .Add("@phone", SqlDbType.VarChar, 255).Value = Phone.Text .Add("@emailbody", SqlDbType.VarChar, 255).Value = Body.Text
End With objDataAdapter.InsertCommand.ExecuteNonQuery() objConnect.Close() 'then close the connection Catch SQLError As SqlException Response.Write("<H1>SQL Exception:</H1>") Response.Write("<h2>" & SQLError.Procedure & "</h2><br />") Response.Write("<h2>" & SQLError.Message & "</h2><br />") Dim lineNum As Integer Dim errMsgArray() As String = Split(SQLError.StackTrace, vbCrLf, , Microsoft.VisualBasic.CompareMethod.Text) Do While lineNum <= errMsgArray.Length - 1 If Trim$(errMsgArray(lineNum)) <> "" Then Response.Write("<br>" & errMsgArray(lineNum)) End If lineNum = lineNum + 1 Loop Catch objError As Exception Dim oMsg As String 'display error details oMsg = "<b>* Error while accessing data</b>.<br />" _ & objError.Message & "<br />" & objError.Source
Trace.Write(oMsg) Response.Write(oMsg) Response.End() End Try End Sub I think the connection is OK (debugging shows that objConnect.State is open) but I am unclear why this is incorrect code here.
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
i am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
I'm looking for a way to refer to a package variable within any Transact-SQL code included in either an Execute SQL or Execute T-SQL task. If this can be done, I need to know the technique to use - whether it's something similar to a parameter placeholder question mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements within the Execute SQL task, so I don't think the Execute T-SQL task is even necessary for this purpose.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
I am trying to execute a SP in the execute SQL task in SSIS 2005..
but I keep getting an error:
SSIS package "Package.dtsx" starting. Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Load_Gs_Modifier_1 SSIS package "Package.dtsx" finis
I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????
I have an asp:sqldatasource which is bound to a gridviewIn addition to this I would like it to a) see if there is a specif row/ item in it (ie item_id = 10 for any of the rows it has received) as I conditionally want to show another item outside of the gridview subject to if it is in the gridview or notb) show the sum of all the values within a certain column of returned rowsMany thanks
How do I get the result of this select into a variableDim sqldsFindUserId As SqlDataSource = New SqlDataSource sqldsFindUserId.ConnectionString = ConfigurationManager.ConnectionStrings("bluConnectionString2").ToString sqldsFindUserId.SelectCommandType = SqlDataSourceCommandType.Text Dim myUserIdCmd As String = "select pkUser from tblUsers where strDisplayName='" + myDisplayname + "" sqldsFindUserId.SelectCommand = myUserIdCmd ' The result of this select statement is to be stored in a variable, how do I do it?
hi all, in all my 2.0 learnings and books i keep coming across the page element <asp:sqldatasource>. I have always (in 1.1) used server side connections and adapters to bind my Sql datasets to any control needed. Now that im learning 2.0 im finding it difficult to understand using control on the page to bind my data. Can someone explain the benefits of using this data source? Ideally i would like to keep my data access layer separate from my presentation layer but i'd really like to understand why this method seems so popular. thanks in advance, mcm
Hi, I am new to ASP.NET 2.0 and I am trying to use VWB to bind my web site to a SQL Express edition. I used SQLDataSource to specify the .mdf file so I can connect to my tables but when I click on the advanced button to generate the Insert, Update, Delete SQL I find it grayed out and it cannot be clicked. I looked into several tutorials online and I couldn't find the problem, can anyone explain what I am missing or doing wrong? Any suggestion is very appreciated. Thanks
SELECT * FROM [CONTACTS] WHERE @ddl_value LIKE '%@txt_value%'
Why doesn't this not working I am using SQLdatasource control to bind a gridview If my query is wrong then what might be the correct one to work with like operator in the sqldatasource Can any ony help me!
Hello All, I have quick question .. In my aspx page i have gridview and Sql DataSource object as you can see <asp:sqldatasource id="SqlDataSource1" runat="server" ></asp:sqldatasource>
<asp:gridview id="GridView1" runat="server" allowpaging="True" allowsorting="True" autogeneratecolumns="False" datasourceid="SqlDataSource1"> <columns> <asp:boundfield datafield="breakdownid" headertext="breakdownid" insertvisible="False" readonly="True" sortexpression="breakdownid" /> <asp:boundfield datafield="ticketno" headertext="ticketno" sortexpression="ticketno" /> <asp:boundfield datafield="systemtype" headertext="systemtype" sortexpression="systemtype" /> <asp:boundfield datafield="break_date" headertext="break_date" readonly="True" sortexpression="break_date" /> <asp:boundfield datafield="subject" headertext="subject" sortexpression="subject" /> <asp:boundfield datafield="status" headertext="status" sortexpression="status" /> <asp:boundfield datafield="prioritylevel" headertext="prioritylevel" sortexpression="prioritylevel" /> <asp:boundfield datafield="mobiletype" headertext="mobiletype" sortexpression="mobiletype" /> </columns> </asp:gridview> In codebehind file i call a function to get data from database. what i want is to bind the result to the sqlDataSourse not the gridview. I need to have the SqlDataSourse thier.. Any help please
Sub Page_load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not Page.IsPostBack Then Dim objReader As New Dynamic.Reportsdemo SqlDataSource1 = .objReader.Get_Tickets(1, 0, "all")