I have searched for what I think is a simple solution.
In my aspx.vb code page, I need to make a connection to SQL Server (my connection strings are in the web.config file) and then make a simple SQL Select to return one or 2 values.
For example, the user will select a customer on the ASP form, then I need to read a couple of default values from that user's record and display them back onto the form.
Can someone provide the sample code or direct me to this? I'm thinking that this is rather simple, but I can't seem to make it work. If I see a sample of exactly what I need to do, such as reading a value using the Northwind sample DB, then I can modify it to fit my table structure, etc.
Hi,I have a form page with an insert like this:<asp:SqlDataSource ID="addProductDataSource" runat="server" InsertCommand="INSERT INTO test( title1, firstName1, lastName1, dateOfBirth ) VALUES ( @title1, @firstName1, @lastName1 @dateOfBirth )" ConnectionString="<%$ ConnectionStrings:yourQuoteCentreConnectionString %>"> <InsertParameters> <asp:ControlParameter ControlID="dateOfBirth" Name="dateOfBirth" Type="DateTime" /> <asp:ControlParameter ControlID="title1" Name="title1" PropertyName="Text" /> <asp:ControlParameter ControlID="firstName1" Name="firstName1" PropertyName="Text" /> <asp:ControlParameter ControlID="lastName1" Name="lastName1" PropertyName="Text" /> </InsertParameters></asp:SqlDataSource> In default.aspx.vb I have:Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick 'CREATE DATETIME FROM USERS BIRTH DATE Dim dateOfBirth As New System.DateTime(applicantYear.SelectedValue, applicantMonth.SelectedValue, applicantMonth.SelectedValue, 0, 0, 0) 'INSERT TO DATABASE addProductDataSource.Insert()End SubI would like to reference the variable dateOfBirth created in default.aspx.vb in the insert query on the page, or ideally move the insert query into the aspx.vb page. Would this make more sense to do?Thanks,Paul
Anyone support a config where you have an unix/linux (AIX here) ODBCclient connection to SQL Server database? I am looking for a simple,supportable configuration that does not require me to learn perl orinstal a bunch of crap gnu shareware on enterprise class machines.Please help!
I am trying to select a particular record from a table and for some reason it only returns 1 record when I know there are at least 6. I've tried Rtrim and left to make sure that I'm getting the exact string. The "MyID" field is varchar(32) and the "ExpireDate field is datetime and I have tried the below. Any ideas what could be wrong?
Select DISTINCT K.MyID, KP.[EXPIREDATE] --, K.ACTIVATIONKEY From [KEY] K INNER JOIN KEY_PRODUCT KP ON K.MyKey = KP.MyKey Where K.MyID = '013BEB73C2CF11D39F3600105A05264C'
I have a simple select statement that I am having a complex with. I am a C# developer trying to expand and gain a little more knowledge of SQL. I want to send a string to a SQLCommand catagory = 'news,alerts,events' and return a result set if it matches any term.
This is for a blog I am creating for a church and the catagory is like the tags you normally see. I could use something like:
SELECT GUID,CATAGORY,DESCRIPTION,TITLE,ETC FROM RSS_FEEDS WHERE CATAGORY LIKE 'news,alerts,events'
I can seperate the keywords with any character if needed.
WHERE (ITEM_CATEGORY LIKE '%news,events%')
This only returns ones that have all like what is there not the ones having news only
Hi I Have the following table SequenceNumber___TypeID8_________________IMG7_________________IMG6_________________IMG5_________________IMG4_________________IMG3_________________IMG2_________________FLP2_________________IMG I want to pull the data out in the following format, SequenceNumber___TypeID8_________________IMG2_________________FLP This basically shows the highest SequenceNumber of each TypeID, I've tried many different SQL queries but I can't seem to get it! Any ideas?
I am new to ASP, I come from a PowerBuilder background. I like the sqldatasource model, but I have a question. In PowerBuilder I could just write straight 'embeded' sql in the code.such as Select XFROM YWhere XYZ; So my question is this: I want to get the value out of the database and set a variable to it where I know a key field, what's the quickest way to do it? Thanks Dan
Ok, I do know SQL and have been using it for quite soem time. For some reason, it is giving me an error and I was wondering if someone could help.
Here is the few lines of interest
System.Data.SqlClient.SqlCommand command; command = new System.Data.SqlClient.SqlCommand(@"SELECT Password FROM User WHERE Username='" + user + "'", this.sqlConn);
dataReader = command.ExecuteReader();
This is the error I am coming up with.
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.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.
I have tried enclosing the Table name in quotes and removing the @. What am I doing wrong? Thanks!
This is the query I wrote, but it only selects the personel who have Workgroups and Access Groups assigned to them but I want to show all the employies and if they don't have Workgroups and Access Groups assigned to them then show empty cells on DBGrid... and I know the red part is the problem but I could not build up the logic to select the matching records and empty records at the same time... Thanks in advance
There is a stored procedure that updates a "sales" table with the current "sales representative" taken from the "customers" table. I'm changing this mess of cursors into a simple update, my first approach was just to do a select (instead of update) just to verify that the rows selected were the ones that really needed to be changed. The select is very simple but is returning a cartesian product instead of just the 200+ (aprox) rows. I would appreciate if someone took a quick look at this select and see what I'm missing :) (I'm including the code for the cursor as well as the code for the select)
CURSOR:
CREATE PROCEDURE [ReCast_Salesman] AS -- Recast salesman
Set @i = 0 Set @cnt = 0 Set @ucnt = 0 Set @icnt = 0 Set @UpdFlag = 'N'
Declare Customer_cur Cursor for Select site_code, cust_no, cust_sffx, sales_rep From Customer where Active_Flag = 'A' and sales_rep <> 'XXXX' Order by site_code, cust_no, cust_sffx For Read only
Open Customer_Cur
Fetch Next from Customer_Cur into @vsite_code, @vcustno, @vcustfx, @vsalrep
While @@FETCH_STATUS = 0 Begin
Declare sales_cur Cursor for Select site_code, cust_no, cust_sffx, salesrep From Sales Where site_code = @vsite_code and cust_no = @vcustno and cust_sffx = @vcustfx and salesrep <> @vsalrep Order by Site_code, cust_no, cust_sffx For Update
Open Sales_cur
Begin
Fetch Next from sales_cur into @ssite_code, @scustno, @scustfx, @Ssalrep
While (@@fetch_status = 0) Begin
--If @vsalrep <> @Ssalrep
-- Begin Update Sales set salesrep = @vsalrep Where current of Sales_cur
set @ucnt = @ucnt + 1 -- End
Fetch Next from sales_cur into @ssite_code, @scustno, @scustfx, @Ssalrep
End
FetchNext: deallocate sales_cur
Fetch Next from Customer_Cur into @vsite_code, @vcustno, @vcustfx, @vsalrep
End
End
set @vprodate = getdate() set @vpro = 'Recast Salesman ' set @vmesg = Str(@ucnt) + ' Records Changed'
use salesdatamart; Select A.site_code, A.cust_no, A.cust_sffx, A.salesrep, From Sales As A Inner Join Customer As B OnA.site_code = B.site_code AND A.cust_no = B.cust_no AND A.cust_sffx = B.cust_sffx AND A.salesrep != B.sales_rep AND B.sales_rep != 'XXXX' AND B.active_flag = 'A'
I have a select statement that is not finding a recordset that I know is there.
SQL_1 = "SELECT cust_id, firstname, lastname, email FROM tbl_customers WHERE email = '" & email & "'"
Works fine on most email addresses, but on email addresses where there is a period in the name it does not find the record. The email string is being submitted via a form. Before the SQL statement above I have the request from the form.
email = Request("email")
Like I said, this select works fine on most email addresses. Im sure this is a formatting issue.
If I manually type the select statement into SQL Query Analyzer it works also, but of couse I am manually typing the email address in there that contains the period in the email address.
I have verified that the request is pulling the correct email address with the period.
I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type). All I want to do is pull out one recordset (if it's in that table) from each of the tables.
This is what I have: "SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"
Hi,I feel so stupid to ask this question, but here it goesWhen I select a column from a table, if the column has a null value Iwant the select to return me a blank. I have done this before but Iforgot, some one help?select name, age from peoplein the above query the name can be null. I do not want to doselect @name=name....Thanks.
Hi,I cant figure out how to do this....for example:Select name from mytab order by col1could returnMikeDaveSueSimonPaulFredI would like to show the row number, like in the grid in query analyser. sothe orginal sort order is preservedi.e1 Mike2 Dave3 Sue4 Simon5 Paul6 FredAny ideas?
I want to select from a table based of a variable, the variable is either 1 or 2, however is it possible to ask for BOTH 1 and 2 when calling from a variable?I tried this, but it seems to only look for values matching 1 and then stop... SET @FuelType = '1 OR FuelType = 2'SELECT * FROM Engine WHERE FuelType = @FuelType
This might be too simple of a question to post here. But anyway.... I would appreciate if anyone could answer this: I have two tables, Visitors and Registrants. The relationship between two tables is that every Registrant is a (web site) Visitor. The primary key of Visitor table (v_id) is foreign key in Registrant table, hence enforcing the 1-to-many relationship between tables. Registrant table has its own PK as reg_id. Essentially, Registrant table contains log of those visitors who in fact registered. How can I retrieve the rows from Visitors table for users who did NOT register? In other words, just opposite of what I would getting if I JOIN the two tables. Any ideas? Thanks, W.
I know there must be something i'm missing. I'm logged on as sa on the Query Analyzer, and run a simple select statement, or any statement for that matter, and i get an 'INVALID OBJECT NAME' error.
I have several records in a table of "links", which has the fields ID, UserID, Description and URL. I'm trying to select all the records in this table for a specific user. The stored prodecure I'm using is:
ALTER PROCEDURE dbo.sprocLinkSelect (@UserID uniqueidentifier)
AS
SELECT ID, UserID, description, URL FROM links WHERE UserID = @UserID
When I execute the procedure without "UserID" in the SELECT part of the statement it works and returns the correct rows for the UserID entered as a parameter. However, when UserID is included in the SELECT part of the statement (as above), it says:
"No rows affected. (1 row(s) returned)"
and just shows the column headings, but with no record details beneath. (When the example works as intended, it returns two rows.)
I've tried changing the procedure to take @description as its parameter and return all records with one specific description, and it also only works when UserID isn't included in the SELECT line. I've also tried using SELECT *, but that doesn't seem to work either.
Any help would be greatly appreciated, I'm using SQL Express.
Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.
IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.
select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;
But now if I want to grab them in alphabetical order from another table I have problems.
this is the code I am trying to use maybe I am just thinking it through wrong. select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;
any help is appreciated. Is there a way to grab each row in alphabetical order?
I'm trying to return all of rows by applying a simple query to my database, however not all of the rows are being returned. The simple SQL query is:
SELECT `id` FROM `tags` WHERE `tagname`='baseball'
The rows that are left out are those in which the variable being searched for is not the first record listed in the table for a corresponding record. For example, the query of the "tags" table (below) for 'baseball' above returns only ids 10 and 12. id 11 also has a tag "baseball" but it is not being returned. What do I need to add to my query in order to return all of the ids that correspond, and not just the "first" ones? Thanks in advance!
I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?
SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate FROM Employees FULL OUTER JOIN People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007') ORDER BY People.BirthDate
select pname, cname from parent table a, child_table b where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1 Ben ben_Child2 Ben ben_Child3 ...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?
I've just inherited a system and have some concerns about the speed ofconnections to a remote server (SQL2000). If I do a simple selectstatement on the table below, it takes 14 minutes to retrive 6 millionrows across a 2Mb line. Obviously it's a reasonable amount of data toretrieve, but I would have thought this would be quicker if I'm honest.Run locally, this is 50 seconds.My thoughts are that there may be some issues with our connection (weget general network errors sporadically, which are being looked at),but wanted some thoughts if the performance is acceptable for what itis doing with what is available. I don't think there is a SQL issue,but want to check if this sounds about right.It's early days, so I'm after a general impression of the speed ofretrieval for the amount of data on the available bandwidth. Assuming abest performance scenario, what is the minimum time it should take as abest guess ?ThanksRyanCREATE TABLE [FIELD_VALUES] ([DEALER_DATA_ID] [int] NOT NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,[CHANGED_TYPE] [int] NULL ,CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED([DEALER_DATA_ID],[FIELD_CODE]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO
I have 2 SQL 2000 servers. One has been added as a linked server. They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain. Thanks
