SQL Select Statement (Textbox Used To Grab Data From Database)
Sep 21, 2007
I have a problem....SOMEONE PLEASE HELP!
Here is the setup.
Text Box: User enters in customer transaction number
Button: User clicks button to display information about the customer
Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far.
Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click
Try
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})})
I want to create a details view page without using the DetailsView control. I would prefer to have separate textboxes or labels for each column. I just don't have a clue how to go about after the Select is done putting the data into the boxes. Below is as far as I've gotten. The purpose of the label is just my way of wanting to display the QueryString value, it serves no other purpose. protected void Page_Load(object sender, EventArgs e) { { string tktid = Request.QueryString["id"].ToString(); Label1.Text = tktid.ToString(); } { SqlCommand cmd = new SqlCommand("Select tkt_creator from Ticket where tkt_id = @tktid", HelpdeskConnectionString); HelpdeskConnectionString.Open(); cmd.ExecuteReader(); Label2.Text = ?; } Any help would be appreciated. So far, I've learned a lot from this forum. Y'all have been great.
i currently have multiple textboxes which input data, i'm wanting the textbox details to be sent to a database, here's how one of the textboxes look: <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' Visible="False" Width="300px"></asp:TextBox> the sqldatasource:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommunityDirConnectionString %>" InsertCommand="INSERT INTO email(Name, Department, Email, Subject, Body) VALUES (@Name, @Department, @Email, @Subject, @Body)" SelectCommand="SELECT email.* FROM email"><InsertParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Department" /><asp:Parameter Name="Email" /><asp:Parameter Name="Subject" /><asp:Parameter Name="Body" /></InsertParameters></asp:SqlDataSource> i'm wanting it to fire once the user selects a button, but i'm not sure what code needs to be placed behind the button?
I have a VERY simple program, it contains a datagrid, textbox and button I want it so when I insert something into the textbox and press the button it puts it into the database that is connected to the datagrid and displays it I have the following code and I cannot get it to work: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) SqlDataSource1.InsertParameters.Add("@Name", TextBox1.Text) End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head> <body><form id="Form1" action="Default.aspx" runat="server"> <table> <tr> <td> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>"DeleteCommand="DELETE FROM [Table] WHERE [ID] = @ID" InsertCommand="INSERT INTO [Table] ([Name]) VALUES (@Name)" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>"SelectCommand="SELECT [ID], [Name] FROM [Table]" UpdateCommand="UPDATE [Table] SET [Name] = @Name WHERE [ID] = @ID"> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> </td> </tr> <tr> <td align="center"> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </td> <td> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" /> </td></tr> </table></form> </body> </html>
I have a chart I am producing in .net and I need to values. The problem is the values (sums) i need are on 2 different servers. Is there any where to combine the query using two databases? Does anyone have any suggestions?
let's say I have a table named "myTable" in a SQL database:
UniqueID FirstName FamilyName
1 Elizabeth Moore
2 Chris Lee
2 Robert McDonald's
I want to create a SQL query should contain a parameter for example: SELECT * FROM myTable WHERE UniqueID = @TextBox OR FirstName = @TextBox OR FamilyName = @TextBox,
and when I type in my TextBox the ' * ' character, it should retrieve the whole table...
hope anybody understood, will be happy to explain more.
Hi, I am very new to the .net framework, but I have a lot of experince in php/mysql applications. So this is this is proboly going to seem like a nwebie question. I am trying to pull a single item out of the database programaticlly, take the value of that item and set it to a varible so I can work with it in other areas. Here is basically what I have this is on the vb page in a sub I have the varible MonthlyPrice Defined earlier in the pageDim connString As String = _ConfigurationManager.ConnectionStrings("ConnectioString").ConnectionString'Create a SqlConnection instanceUsing myConnection As New SqlConnection(connString)'Specify the SQL queryConst sql As String = "SELECT Price FROM Plans Where PlainID='" & PlanId & "'"'Create a SqlCommand instanceDim myCommand As New SqlCommand(sql, myConnection)'Get back a DataSetDim myDataSet As New DataSet'Create a SqlDataAdapter instanceDim ReadData As New SqlDataReader(myCommand) 'Bind the DataSet to the GridViewMonthlyPrice = ReadData("Price")'Close the connectionmyConnection.Close()End Using I just want to get the price of the plan where planID is equal to the value of my varible planID. I want to set that equal to MonthlyPrice which is a decimal so I can add it into some calculations and return a value. I don't really want to bind the data to anything but everytime I search google I can't find anything excpet for binding data to grideview and things like that. I have tried several diffrent approaches that I have found out there but have gotten errors or and things I am not understanding. I originally was trying to access a datasource I already have defined that is on the aspx page.<asp:SqlDataSource ID="SQLSelectPlan" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Plans] WHERE ([PlanID] = @PlanID2)"> <SelectParameters> <asp:FormParameter FormField="grSelectPlan" Name="PlanID2" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> Is there a way for me to grab this datasource which works and is tied to a detailed view, take the value in the price collom and stick it in the MonthlyPrice varible on my vb page? That is the approach that seems the most logical to me. If someone has better way I would really like to know.
Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:
Database name Table name Column name from that table with a clustered index
I have already created the following script which displays all the databases for a given server:
declare @DBname nvarchar(128) declare testCursorForDB cursor for select name from sys.databases with (nolock) where name not in ('master','tempdb','model','msdb') order by name
open testCursorForDB fetch next from testCursorForDB into @DBname
while @@fetch_status = 0 begin print @DBname fetch next from testCursorForDB into @DBname end
close testCursorForDB deallocate testCursorForDB
I also have created the following query which will display all the table and column names which have a clustered index for a given database:
select object_name(i.id) as TableName,
i.name as IndexName from sys.sysindexes as i with (nolock) where i.indid = '1'
However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.
I have a SELECT statement which returns a result, depending on the selection the users make (application written in Visual Basic 2005). I want to export the result of the SELECT statement to a Excel file (.csv, or .txt). How should I proceed?
I'm using sql 2008 and triying to build a dynamic sql script to split the records 50/50.I know using newid() with order by clause selects randomly but how should I build the select statement to split the data 50/50 so i don't need to run the script manually everytime ?
I have this update statement I am trying to use, to update a table. My problem is if there is no data in the select statement, it still adds number to the columns. How can I have this update statement work to put blank value in if there are no counts?
UPDATE T_AXA_BreakDown_Claims SET [Claim Count Conm] = t2.[Claim Count Conm] FROM T_AXA_BreakDown_Claims t1 INNER JOIN (select
I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.
Query is sort of
Select field1,fiel2... from table 1 where id = @id
@id is each ID from lookup
Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.
Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.
is there no a way to determine if a SqlCeDataReader has managed to return data (rows) from an executed SQL SELECT statement? I ask this because I have the following problem where I first need to determine if data (rows) are returned, if so, cycle through them and get the data out. But if I do the test to determine if data is returned (like I have in my code below) then the Reader.Read() is classed as reading a row, so when I do my 'while (Reader.Read())' this will then only work with data in the 2nd row. if (Reader.Read()){ while (Reader.Read()) { //Get data from rows }}
I recall having this issue before as there doesn't seem to be a standard way of testing for data returned from a SELECT statement. I hope you can shed some light on this for me.
I have a select statement running on the client machine linking to different tables in 1 database. All with the same schema. When I ran it, i had this error. I had trial and error, removing 1 table at a time until i hit the one which is causing it. when i removed it, everything's ok. i just wonder if all the tables were using dbo schema what is causing this particular table to throw this error?
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value. ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid This is all done in Page_Load event backend code. string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
I am currently developing a website for a friend of mine. Is there a way using sqldatasource or accessdatasource etc to pull data from a access database to 3 textboxes? Name / Surname / Email Address Textboxes Textbox1 = name / Textbox2 = surname / Textbox3 = email Database Name / Surname / Email I have searched on the web however can only find help on how to insert into a access database from a textbox.
(VB 2005 Express) I have a textbox linked to a DataTable (database). I want to save its text into the database as a new row. What is the VB code? The following is not working.
Hi I have a situation where i have a drop down list. The user selects a value from it and then a query behind the scenes is generated based on the value from the list. I want the result of the query to be diplayed in the textbox.I've checked a few examples but i don't really have a good understanding of how to generate this query and get the results to the textbox. I think you use the SQLDATASOURCE but still need some help...Can anyone help? Thanks.
Hi, I'm a newbie to to asp.net. I have been trying to get this done but have no idea how to go about to do it. I am using Visual Studio Express 2005 and SQL Server Express 2005. I have a database "RECORDS" and a table "NAME" with columns "NAMEID - int" and "NAMERECORDS - varchar(50)" I opened new website asp.net using vb. In the default I have one textbox (textbox1) and button (button1). I wish to insert a new NAME to the database whenever I click the Button1. I have created the INSERT command but unsure how to code it. I appreciate anyone can show me how to insert to the database whatever is being typed in the textbox whenever I click the button1. Thanks in advance.
hi there...i want to know how to get a specific data from a database and put it in a label? Like an example i have created a database that have "username", "account number"..example i want to put the account number of the username "kevin" into a text box (vs 2005)..
Hi All, I am working on a report, in this report a textbox is present, which is expected to show string. This string length is larger than what textbox can accommodate in single line. I want this textbox to show only right part of the string in case it can accommodate full string. After using the right alignment I assumed it would do show, however it does not work like that. It always shows the left part of string in case, string is bigger than width of textbox. Restriction from design: 1. Cannot increase the width of textbox. 2. Cannot use the Can Grow = true to show string in multiple lines.
Below is the example of my issue: Say there is a string: "Microsoft SQL Server Reporting Services". because the textbox width is not sufficient to see full string, I want to "Reporting Service". Which is of same size which column can accommodate. So I used Right alignment in textbox. however text box shows "Microsoft SQL Server". So, is there a solution to see required text in the textbox.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
