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.
Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db. The db field i'm setting has type set to "bit", is this correct? And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category If chkAspnet.Checked = True Then sqlCommand.Parameters.Add("@aspnet","1") Else sqlCommand.Parameters.Add("@aspnet","0") End If Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try Return rowsAffected End Function Sub SubmitBtn_Click(sender As Object, e As EventArgs) If Page.IsValid then InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value) Response.Redirect("ListAllProducts.aspx") End If End SubAny help would be appreciated or links to tutorials.ThanksBen
I am trying to insert into a database Checkbox list items, I get good values up till the checkBox List and then it gives me all O's, My fields in my database are bit columns so I need the checkbox list itmes to be converted to this format. This is what i have so far. One thing is I don;t think I am inserting into the correct database columns. bitLOD, bitInjury, bitIllness, bitreferral are the database fileds.Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 'Check for the page ID to make sure not an update page then either insert new data or update existing data. Dim id As String Dim LOD As Byte Dim Injury As Byte Dim Illness As Byte Dim Referral As Byte id = Trim(Request.QueryString("ID"))For Each LItems As ListItem In CheckBoxList1.Items If LItems.Selected = True ThenSelect Case LItems.ValueCase "1" LOD = 1Case "2" Injury = 1Case "3" Illness = 1Case "4" Referral = 1 End Select End If Next 'Put data into the Database If id = "" Then 'save data into the database sql = "INSERT tblSADHealth (intTaskForceID, intUICID, strSSN, dtInjury, strNotes, LOD, Injury, Illness, Referral,) " _ & "VALUES (" & ddlTaskForce.SelectedValue & ", " & DDLUIC.SelectedValue & ", '" & txtSSN.Text & "', '" & txtStatus.Text & "', " _ & "'" & txtDate.Text & "','" & txtNotes.Text & "', " & LOD & ", " & Injury & ", " & Illness & ", " & Referral & ")" Response.Write(sql) Response.End()
Hi AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG
I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance
the screenshot links: http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG
I have <asp:SqlDataSource> in aspx page that is being used by an updateable GridView. However, there are a couple fields in the dataset that contain filenames that I want to access directly in code and place them into some image tags on the page. This line should give me access to the dataset but how to I access the fields? Dim myDataSource As DataView = DirectCast(SqlDataSourceGridView.[Select](DataSourceSelectArguments.Empty), DataView) When the SqlDataSource is a DataReader I would access it with the code below but since this SqlDataSource is a DataSet I can't access it with this code.If myDataSource.Read Then If Convert.IsDBNull(myDataSource("CusAgentPhoto")) Then ImageAgent.ImageUrl = "/photos/nophoto.gif" Else ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString End If If Convert.IsDBNull(myDataSource("CusCompanyLogo")) Then ImageCompany.ImageUrl = "/photos/nophoto.gif" Else ImageCompany.ImageUrl = OfficeImagePath & myDataSource("CusCompanyLogo").ToString End IfEnd If What would be the correct way to get at the DataSet fields that contain the filenames?
Whn I use a UniqueIdentifier field as a parameter for SQLDataSource the parameter type defaults as an object. I just want to use the string value of the field.How do I do this?Thanks.
Hello, I'm new to the forum and new to SQL, ASP.NET, etc. I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve. I have tried Googling it and came up empty. I have a database in SQL Express 2005 and my website will be accessing several tables within the database. I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set. The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine. The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
Hi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so this tblFriendUser has a column called UserName and another column called FriendUserName. I am trying to get a list of friends for that particular user. Note that if User1 initiated the friend request, he will be listed as UserName and his friend as FriendUserName, but if his friend initiated the friend request, it will be vice versa: him being the FriendUserName and his friend the UserName. So I want the following 2 queries run and merged into one query in order to return 2 columns only: UserFriendID & UserName, is that possible? Is my design bad? Any suggestions/advice would help! Thanks a lot!
SELECT UserFriendID, UserName FROM tblUserFriends WHERE (UserName = @UserName);
SELECT UserFriendID, FriendUserName AS UserName FROM tblUserFriends WHERE (FriendUserName= @UserName);
Hi,I am trying to insert data using data from a datalist on the page. To do this, I use the following coding: command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@sendername,@recievername,@message,@date,@subject)"; command.Parameters.AddWithValue("@sendername", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.AddWithValue("@recievername", SqlDataSource2 ); command.Parameters.AddWithValue("@message", TextBox2.Text); command.Parameters.AddWithValue("@subject", TextBox3.Text); command.Parameters.AddWithValue("@date", DateTime.Now.ToString()); command.ExecuteNonQuery(); SqlDataSource is the datasource for the other datalist- will this work?Thanks,Jon
Hi. I'm testing GridView and SqlDataSource controls and I'm using this http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspxI have problems with inserting new items into the db. As I understood from the code in that example, I need to create InsertParamaters and name it just like the one in the insert query. <asp:SqlDataSource ID="Sql1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Clients]" DeleteCommand="DELETE FROM [Clients] WHERE [ID] = @ID" InsertCommand="INSERT INTO [Clients] ([Name]) VALUES (@Name)" UpdateCommand="UPDATE [Clients] SET [Name] = @Name WHERE [ID] = @ID"> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <InsertParameters> <asp:ControlParameter Name="Name" ControlID="newClientTB" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> </InsertParameters> </asp:SqlDataSource> and I get the following error when run:The variable name '@Name' has already been declared. Variable names must be unique within a query batch or stored procedure. Any help?
Coming from an asp world, I am totally lost on this one. I have two tables. tblUsers and tblUserInfo I have a gridview that gives me the list of users and when selected, it gives me the detailsview of the the record. However, When I need to add a new user, I am trying to add that user into tblUsers (which happens with no problems), and then add a record, using the same user number (UserID) has the key or ID for the tblUserInfo. So basicly, when I create a new user in tblUsers I want to create a record in tblUserInfo with the same userid. In ASP, this takes me minutes... I have been trying to figure this out for weeks. vs2005, sql express. Thanks.
Hello there, I made a page to add these fields (name, description, price, end_date) into a SQL database. I'm using ASP.NET 2.0 with C#, my database is SQL 2005. I did it without DetailsView or FormView, it consists of TextBoxes and a Calendar. Anyways, i wrote this code for Button1, which is the button that adds the data. protected void Button1_Click(object sender, EventArgs e) { SqlDataSource sql = LoginView1.FindControl("SqlDataSource1") as SqlDataSource; sql.Insert(); Response.Redirect("home.aspx"); } The parameters and insert statments and commands are written from the SqlDataSource Insert Query wizard. But I keep getting this error when trying to click Button1. Object must implement IConvertible. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Object must implement IConvertible.Source Error:
Line 31: { Line 32: SqlDataSource sql = LoginView1.FindControl("SqlDataSource1") as SqlDataSource; Line 33: sql.Insert(); Line 34: Response.Redirect("home.aspx"); Line 35: } Source File: c:Documents and SettingsDoublethink..!!My DocumentsVisual Studio 2005WebSitesSunnDarkProjectsell.aspx.cs Line: 33
Hi, I have some problem with inserting data into two separate table which are connected via foreign key like in this example:Students (id_s, name, id_subject)Subjects (id_subject, name)So the point is to add a new student and subject for that chosen student. You have to make two inserts right? but how to pass the primary key to the table with foreign key? The form consists of three texbox: name, name_of_subject. Both id_s and id_subject are increment automatically. Any idea how to do it? I would be very grateful for the working code.Thanks in advance and best regards,N.
Hey forum, the .NET form controls make it extremely easy to manage database data, control + SQLdatasource et voilĂ , its there. i have a web form, and a SQLdatasource with an insert query with parameters (works perfectly, only the Date picker Lite textbox ID as parameter wont register, but i have a thread runnin on their forums for that). when i open the Query builder and execute the query it works! woot! but now, how can i make it execute on a button press? i tried with several commands, like SQLdatasource1.execute, and several other commands i searched (can post later, not behind my test environment with my tools/webpage at the moment). so what is the command to let the SQLdatasource execute it's query? it's all i need to know to make several forms, please ghelp me! :) gr. michael
Hi all, I have few insertions statements in one datasource insert command, which i use for my user creation process adding new billing info shipping info and so on. I've put this code in createuserwizard usercreated step with a try catch code. On the insertion if it doesnt work it deletes the user account. Well lets say the first insertion went trough with out any problems, but the second one got blocked or didnt work length or any wierd on controled issue. I was thinking of putting begin transaction end transaction statements in front and end of the statements but this can prevent the insert not to return error, which will prevent try catch to fire? Basicly the idea is to do all insertions with the user creation, and if one goes down all the others gets reversed. (if possible single sqldatasource, just to make the code look clean)
Hello all, I am having problem to insert the record into database from sqldatasource control. my code is listed below, and i can't find anything why it cause the exception... <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>" SelectCommand="SELECT * FROM [ServiceAgents]" InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)"> <InsertParameters> <asp:FormParameter Name="Ser_STATE" FormField="TextBox_state"/> <asp:FormParameter Name="Ser_CITY" FormField="TextBox_city"/> <asp:FormParameter Name="Ser_AGENT" FormField="TextBox_agent"/> <asp:FormParameter Name="Ser_PHONE" FormField="TextBox_phone"/> <asp:FormParameter Name="Ser_EQUIPMENT" FormField="TextBox_equip" /> </InsertParameters> </asp:SqlDataSource> The table has got the fields that needed for insert command, for example: <table> <tr> <td>State:</td> <td> <asp:TextBox ID="TextBox_state" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox_state" Display="Static" ErrorMessage="Please enter a state." /> </td> </tr> <tr> <td>City:</td> <td> <asp:TextBox ID="TextBox_city" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox_city" Display="Static" ErrorMessage="Please enter a city." /> </td> </tr> <tr> <td>Agent:</td> <td> <asp:TextBox ID="TextBox_agent" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator3" runat="server" ControlToValidate="TextBox_agent" Display="Static" ErrorMessage="Please enter a agent." /> </td> </tr> <tr> <td>Phone:</td> <td> <asp:TextBox ID="TextBox_phone" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox_phone" Display="Static" ErrorMessage="Please enter a phone No." /> </td> </tr> <tr> <td>Equipment:</td> <td> <asp:TextBox ID="TextBox_equip" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator5" runat="server" ControlToValidate="TextBox_equip" Display="Static" ErrorMessage="Please enter a equipment." /> </td> </tr> <tr> <td></td> <td> <asp:Button ID="Button2" runat="server" Text="Add" OnClick="addEntry" BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="1.0em" ForeColor="#284775"/> </td> </tr> </table> And in the code i just called: SqlDataSource1.Insert(); Then the page gives me the exception like: Cannot insert the value NULL into column 'STATE', table 'WebsiteData.dbo.ServiceAgents'; column does not allow nulls. INSERT fails. The statement has been terminated. But i actually input all the required text in the textbox.... Any idea guys? Thanks
SqlDataSource UpdateCommand plus Insert I have a SqlDataSource with an UpdateCommand but besides that i need also an Insert command triggered on the same update command. That´s because i need to update a record and at the same time include a log of that update in another database. Thanks
I have a sqldatasource (code listed below) whose insert Paramaters are control parameters. My aspx page has a textbox and a submit button. the button onclick runs the sqdatasource1.insert. What I get is every other insert inserts the text in textbox2 and every other insert enters nothing for the namecust value. I have a required field validator which correctly prevents submission if textbox2 is empty. How do I fix this? :<code> <asp:Panel ID="Panel1" runat="server" Height="50px" Width="548px"> <asp:Button ID="Button1" runat="server" Text="New Prospect" ValidationGroup="insertCust" /> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox2" ErrorMessage="Prospect Name can not be blank" ValidationGroup="insertCust"></asp:RequiredFieldValidator> <asp:TextBox ID="TextBox2" runat="server" Width="330px" ValidationGroup="insertCust"></asp:TextBox></asp:Panel> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>" SelectCommand="SELECT DISTINCT CODETERR FROM dbo.F_arcus() AS F_arcus_1 WHERE (DATEINAC = 0) AND (rtrim(CODETERR) <>'')"> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>" InsertCommand="INSERT INTO dbo.BudgetProspects(NameCust, CodeTerr) VALUES (@Namecust, @codeterr)" SelectCommand="SELECT CustomerID, NameCust FROM dbo.BudgetProspects WHERE (CodeTerr = @codeterr)" UpdateCommand="UPDATE dbo.BudgetProspects SET NameCust = @namecust"> <UpdateParameters> <asp:Parameter Name="namecust" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="RadioButtonList1" Name="codeterr" PropertyName="SelectedValue" /> </SelectParameters> <InsertParameters> <asp:ControlParameter ControlID="textbox2" Name="Namecust" PropertyName="text" /> <asp:ControlParameter ControlID="RadioButtonList1" Name="codeterr" PropertyName="SelectedValue" /> </InsertParameters> </asp:SqlDataSource> </code> codebehind button_click: <code> Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click If Not TextBox2.Text Is Nothing Then SqlDataSource1.Insert() TextBox2.Text = "" End If End Sub </code>
I have a table with 2 columns - "memberid" is a guid and "Interest" is a string. In an aspx page I want to read in all values of "Interest" for a given "memberid" and display approprite checkboxes as checked. The user can then change which boxes are checked and I want to record the new list of selected items in the table. I created a SQLDataSource (see below) but when I run it, I get this error: ========== Error =========== Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'DB_136571.dbo.gs_MemberInterests', column 'memberid'. Use the CONVERT function to run this query. ============================ The only use of this SQLDataSource is to delete all entries for a given user and then insert an entry for each checked checkbox. The code to set the session variables is as follows: ======== Code ============ Dim guidMemberid As Guid = CType(user.ProviderUserKey, Guid) Session("currmember") = guidMemberid ..... Session("currinterest") = CType(item.FindControl("CheckBox1"), CheckBox).Text ========================== Any ideas of what I am doing wrong here?
Hello, I have created a web page with a FormView that allows me to add and edit data in my database. I configured the SqlDataSource control and it populated the SELECT, INSERT, UPDATE and DELETE statements. I've created a stored procedure to do the INSERT and return to new identity value. My question is this: Can I configure the control to use this stored procedure? If so, how? Or do I have to write some code for one of the event handlers (inserting, updating???) Any help would be appreciated. -brian
hi i have an sqldatasource which has an insert command - a stored procedue is used.I have a text box with a button next to it . it is not in a datagrid.on the onclick event I would like to pass the value of the text box to the sqldatasource insert parameter ( it only expects this one parameter , and use the sqldatasource to do the insert basically doing a manual insert using the sqldatasource.does anyone know if this is possible thanks
I m trying to INSERT a record to my Consultants table using sqldatasource and formview: here is my sqldatasource code: <asp:SqlDataSource ID="sqlDS1" runat="server" ConnectionString="<%$ ConnectionStrings:myDB %>"InsertCommand="INSERT INTO [Consultants] ([firstName],[lastName],[skillCategoryID],[resourceManagerID],[AMgroupID],[skillSet],[statusID],[location],[comments],[profile],[isAvailable],[dateModified],[focusID]) VALUES (@FNAME, @LASTNAME, 1, @RESOURCEMANAGERID, @AMGROUPID, @SKILLSET, @STATUSID, @LOCATION,@COMMENTS,@PROFILE,@ISAVAILABLE,getdate(),1)">
<InsertParameters> <asp:FormParameter FormField="txtFName" Name="FNAME" /> <%--other parameters--%></InsertParameters></asp:SqlDataSource> now in my formview i have this:<asp:FormView DefaultMode="Insert" ID="FormView1" runat="server" DataSourceID="sqlDS1" DataKeyNames="id"> <table border="0" cellspacing="5" cellpadding="0"><tr> <td class="blacktextbold">First Name:</td> <td class="blacktext"><asp:TextBox ID="txtFName" CssClass="txtfield" Width="200" runat="server" Text="<% #Bind('firstName')%>" /> <asp:RequiredFieldValidator ControlToValidate="txtFName" runat="server" ValidationGroup="gpInsert" ErrorMessage="Required Field" Display="Dynamic" CssClass="redtextsmallbold" /> </td></tr> </table> THE PROBLEM:no matter what i put the txtFName field its ALWAYS null and i get INSERT error as column firstName cant accept NULLany ideas? ur help would be appreciated
Ok, when i use a sqldatasource control and build a INSERT command like below. My code always gives me a already have @UserId parameter error when I try to insert. What am I doing wrong here? SqlDataSource INSERT COMMAND:INSERT INTO Ranger_Profile(UserId, FirstName, LastName, Address, City, State, Zip, HomePhone, CellPhone, Email) VALUES (@UserId, @FirstName, @LastName, @Address, @City, @State, @Zip, @HomePhone, @CellPhone, @Email)Page Code:Dim RangerProfileDataSource As SqlDataSource = SqlDataSource1RangerProfileDataSource.InsertParameters.Add("UserId", CreateNewUser.UserName.ToString)RangerProfileDataSource.InsertParameters.Add("FirstName", txtFirstName.Text.ToString)RangerProfileDataSource.InsertParameters.Add("LastName", txtLastName.Text.ToString)RangerProfileDataSource.InsertParameters.Add("Address", txtAddress.Text.ToString)RangerProfileDataSource.InsertParameters.Add("City", txtCity.Text.ToString)RangerProfileDataSource.InsertParameters.Add("State", txtState.Text.ToString)RangerProfileDataSource.InsertParameters.Add("Zip", txtZip.Text.ToString)RangerProfileDataSource.InsertParameters.Add("HomePhone", txtHomePhone.Text.ToString)RangerProfileDataSource.InsertParameters.Add("CellPhone", txtCellPhone.Text.ToString)RangerProfileDataSource.InsertParameters.Add("Email", CreateNewUser.Email.ToString)Dim rowsaffected As Integer = SqlDataSource1.InsertIf rowsaffected = 0 Then'End If
Hi,because I have had problems with transporting of data from TextBoxes to SQL database in my application using SqlDataSource.Insert(), I tried to analyze it using Microsoft's sample code located here: http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspxEverything worked well untill I have used masterpage. Since this moment my database table started receive only NULL values - the same problem, which I have in my application. Despite all data values were correct before calling SqlDataSource.Insert() method. Is it some bug or something else (my bug ) and how can I resolve it? I'd like to use all advantages of masterpages and datasources together.Here are non-working codes of spoken sample:shelter3.aspx<%@ Page Language="VB" MasterPageFile="~/MasterPage3.master" AutoEventWireup="false" CodeFile="shelter3.aspx.vb" Inherits="shelter3" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="Obsah" runat="Server"><asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"DataTextField="nazev" DataValueField="code" /><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:intranet %>"SelectCommand="SELECT nazev,code FROM smenky" InsertCommand="INSERT INTO smenky (nazev,code) VALUES (@nazev,@code)"><InsertParameters><asp:FormParameter Name="nazev" FormField="NazevBox" /><asp:FormParameter Name="code" FormField="CodeBox" /></InsertParameters></asp:SqlDataSource><p><asp:TextBox ID="NazevBox" runat="server" /><asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="NazevBox"Display="Static" ErrorMessage="Please enter a company name." /><p><asp:TextBox ID="CodeBox" runat="server" /><asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="CodeBox"Display="Static" ErrorMessage="Please enter a phone number." /><p><asp:Button ID="Button1" runat="server" Text="Insert New Shipper" /></asp:Content> shelter3.aspx.vbPartial Class shelter3Inherits System.Web.UI.PageProtected Sub InsertShipper(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ClickSqlDataSource1.Insert()End SubEnd Classmasterpage3.master<%@ Master Language="VB" CodeFile="MasterPage3.master.vb" Inherits="MasterPage3" %><!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>Untitled Page</title></head><body><form id="form1" runat="server"><div><asp:contentplaceholder id="Obsah" runat="server"></asp:contentplaceholder></div></form></body></html>masterpage3.master.vbPartial Class MasterPage3Inherits System.Web.UI.MasterPageEnd ClassThanks for any idea
Hi all, I really can't figure out what i'm doing wrong here, and I'd appreciate some help if possible please! SqlDataSource5.InsertParameters("ntuser").DefaultValue = ntuser SqlDataSource5.InsertParameters("datetime").DefaultValue = DateTime.Now SqlDataSource5.Insert()Above is my code to insert into my SQL database a varchar NT user name, and the current date. The Parameters are defined in the HTML as : <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:Test EnvironmentConnectionString %>" InsertCommand="usp_insert_hit" InsertCommandType="StoredProcedure" SelectCommand="usp_select_hits" SelectCommandType="StoredProcedure" UpdateCommand="usp_insert_hit" UpdateCommandType="StoredProcedure"> <InsertParameters> <asp:Parameter Name="ntuser" Type="String" /> <asp:Parameter Name="datetime" Type="DateTime" /> </InsertParameters></asp:SqlDataSource> My SQL Database is set up to have a table called "Hits" which has three colums, a hit_id (Autocounter, primary key), ntuser (varchar), datetime (datetime) When the application runs it stops at runtime and highlights the SQLDataSource5.Insert() line, and I can't figure out why - can anyone else tell why please? thanks.
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(); }
Hi frdz, I m the new user of ASP.NET WEB APPLICATIONS WITH C# LANGUAGE. I m using SQL SERVER 2005 and SQLDATASOURCE to get or retrieve the data from the database. I have created the stored procedure for insert and update. The stored procedure is executing fine when i m running it from sqlserver2005. My problem is with the web-application page. I m not able to insert or update data thru that... Pls check the code and tell me what's missing out ..........SQLDATASOURCE<asp:SqlDataSource ID="srcemp" runat="server" ConnectionString="<%$ ConnectionStrings:empmaster %>" InsertCommand="empStoredProcedure" InsertCommandType="StoredProcedure" UpdateCommand="empStoredProcedure" UpdateCommandType="StoredProcedure" DeleteCommand="DELETE FROM empmaster WHERE empid = @empid" DeleteCommandType="Text" SelectCommand="select * from empmaster " SelectCommandType="Text"> <InsertParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city" /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></InsertParameters> <UpdateParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city" /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></UpdateParameters> </asp:SqlDataSource>GRIDVIEW <asp:GridView ID="empGridView" runat="server" AutoGenerateColumns="False" DataKeyNames="empid" DataSourceID="srcemp" Width="56px" > <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="btnDelete" runat="server" CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete this row ?');">Delete</asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:boundfield datafield="empname" headertext="emp Name"/> <asp:boundfield datafield="address" headertext="Address"/> <asp:boundfield datafield="city" headertext="City"/> <asp:boundfield datafield="state" headertext="State"/> <asp:CheckBoxField DataField="deleted" HeaderText="In Existance" /> </Columns> </asp:GridView>C# code protected void cmdsubmit_Click(object sender, EventArgs e) { srcemp.InsertParameters["empname"].DefaultValue = tbcompanyname.Text; srcemp.InsertParameters["address"].DefaultValue = tbaddress.Text; srcemp.InsertParameters["city"].DefaultValue = tbcity.Text; srcemp.InsertParameters["pincode"].DefaultValue = tbpincode.Text; srcemp.InsertParameters["state"].DefaultValue = cmbstate.SelectedItem.ToString(); srcemp.Insert();}Note :I m not getting a single error msg for the above code in web-page or stored procedure but it does not insert,update or delete the record from the database....Thanxs in adv...pls reply at earliest if possible...What's missing ??can anyone check out..what's wrong ??
Hello! I have a SqlDataSource that inserts some data in a database. The field "id" is auto-increment. Is it possible to use the "id", this data-row got from the database automatically, directly after the SqlDataSource.Insert() command in my CodeBehind file? Thank you!
