Trouble With A Where Clause
May 5, 2008
I've tried to post this somewhere else, but I haven't gotten it figured out yet:
I'm passing variables through a URL, and the page that accepts the variables has a where clause like this:
"WHERE (name1 LIKE @name1 + '%') and (property_add like @property_add + '%') and (ctlmap = @ctlmap )"
On the search page where the variables are entered, the user may not know all the information that goes into each input field. (If the user just knew the name, they would search by name. If the user just knew the address, they would search by address...etc.) Everything works great before I add the ctlmap variable. When the ctlmap variable is added, no search results will turn up unless there is an entry made to the ctlmap field. I know it has to have something to do with the ctlmap being set to equals rather than like, but I can't find a way to make the search work with a null value for ctlmap. Thanks, everyone.
Additional Info:
<SelectParameters>
<asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="name1"
QueryStringField="name" Type="String" />
<asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="property_add"
QueryStringField="address" />
<asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="ctlmap"
QueryStringField="ctlmap" />
</SelectParameters>
Here's another type of where clause that I tried but had no luck with:
WHERE (name1 LIKE @name1 + '%') and (property_add like @property_add + '%') and (@ctlmap IS NULL or @ctlmap = ctlmap)
This is how I would try to do it in php/mysql, but I don't know how to make it work in asp.net/sql:
if ($ctlmap != "")
{
$whereClause = $whereClause." and ctlmp = '".strtoupper($ctlmap)."' ";
}
if ($whereClause != "")
{
$whereClause = " where ".$whereClause;
}
Here's the MYSQL counterpart:
$sqlString = "Select distinct district, map, bill_group, ctlmap, bill_parcel, propertyid, special, property_add, name1 from MUR_bill_master".$whereClause";
Can anyone help?
View 11 Replies
ADVERTISEMENT
Jun 6, 2008
I am currently working with the following query:
SELECT w.styleId, w.modelyear, w.vehdesc,
ISNULL(CONVERT(varchar,wp.algfmv-isnull(ROUND(vp.value,0),0)),'') AS previousPeriodBeforeAlgAnalystFactor,
CONVERT(varchar,ISNULL(ROUND(vp.value,0),'')) as previousPeriodAlgAnalystFactor,
CONVERT(varchar,ISNULL(wp.algFmv,'')) AS previousPeriodAlgFmvAfterAnalystFactor,
CONVERT(varchar,ISNULL(vpaf.value,'')) As previousPeriodIntAnalystFactor,
CONVERT(varchar,ISNULL(wp.intFinalFmv,'')) AS previousPeriodFinalIntFmv,
CONVERT(varchar,ISNULL(w.algFmv,'')) AS currentPeriodAlgFmv,
'' AS currentPeriodAlgAnalystFactor
FROM RPM_CHR.dbo.Workspace w
LEFT OUTER JOIN RPM_CHR.dbo.Workspace wp ON wp.periodId = w.periodId - 1 AND wp.styleId = w.styleId
LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vp ON vp.periodId = wp.periodId AND vp.styleId = wp.styleId AND vp.valtype=45
LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vpaf ON vpaf.periodId = wp.periodId AND vpaf.styleId = wp.styleId AND vpaf.valtype = 20
WHERE w.periodId = 194 and (wp.algfmv > 0 or w.algfmv>0)
order by w.vehdesc, w.modelyear desc
This works well but instead of having to say "w.periodid=194" I would like w.periodid = max(w.perioidid). When I try to accomplish this using a having clause by replacing
"WHERE w.periodId = 194 and (wp.algfmv > 0 or w.algfmv>0)
order by w.vehdesc, w.modelyear desc
"
with
"WHERE (wp.algfmv > 0 or w.algfmv>0)
group by w.periodid, w.styleId, w.modelyear, w.vehdesc, w.algFmv,wp.intFinalFmv,vpaf.value,wp.algFmv,
vp.value
Having max(w.periodid)= w.periodid
order by w.vehdesc, w.modelyear desc"
it returns all the records in my table. Could anyone suggest a better way of doing this, or point out where I am erring?
Any help would be greatly appreciated.
I think I should add, the whole point of doing this is that there is a new perioidid each week and I want to only look at records with the current periodid.
Thanks,
Tasha
View 2 Replies
View Related
Apr 13, 2006
Please help.
Has anyone seen a problem querying Excel or Access database when the "LIKE" clause is used with the "*" wildcard? The problem I'm seeing is that the query fails to return a dataset whenever "*" wildcard is used.
For Example,
sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" 'query WORKS
sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA*'" 'query does NOT WORK
Assume that Table name is "EmployeeData", with "ID", "Name" and "Birthdate" as Fields.
Data:
ID Name Birthdate
AAA
Aaron
5/4/1975
CCC
Charlie
10/14/1948
DDD
Deloris
7/19/1998
The code I use is listed as follows:
Imports System.Data.OleDb
Imports System.Data
Public Class Form1
Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:ExcelData1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Use a DataSet to Query data from the EmployeeData table.
Dim QryConn As New OleDbConnection(m_sConn1)
'Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" '- works
Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AA*'" ' does not work
Dim da As New OleDbDataAdapter(strQry, QryConn)
Dim ds As DataSet = New DataSet
da.Fill(ds)
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows 'Show results in output window
Debug.WriteLine(dr("Id") & ", " & dr("Name") & ", " & dr("Birthdate"))
'Expected output:
'AAA
Aaron
5/4/1975
Next
QryConn.Close()
End Sub
End Class
View 1 Replies
View Related
Nov 4, 2015
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
View 13 Replies
View Related
Jul 23, 2005
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View 3 Replies
View Related
Oct 25, 2007
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.
When on SQL Server 2000 the following statement ran without issue:
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
GROUP BY TrackID
HAVING MAX(LegNum) = 1 AND
TrackID + 'x1' IN
(
SELECT
dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
WHERE TrackID + 'x1' IN
(
SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
GROUP BY TrackID
HAVING MAX(LegNum) = 1
)
View 3 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
Nov 20, 2004
Hi,
What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?
eg :
SELECT SUM(col1) from test HAVING col2 < 5
SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5
I want the equivalent query in MSSQLServer for the above Oracle query.
Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.
Thanks,
Gopi.
View 3 Replies
View Related
Apr 3, 2008
How Can I use Top Clause with GROUP BY clause?
Here is my simple problem.
I have two tables
Categories
Products
I want to know Top 5 Products in CategoryID 1,2,3,4,5
Resultset should contain 25 Rows ( 5 top products from each category )
I hope someone will help me soon.
Its urngent
thanks in advance
regards
Waqas
View 10 Replies
View Related
Apr 4, 2007
hi..
i have basic question like
what is differance between conditions put in ON clause and in WHERE clause in JOINS????
see conditions that shown in brown color
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)
another thing...
if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????
View 6 Replies
View Related
Jan 21, 2008
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Thanks
View 6 Replies
View Related
Feb 16, 2004
I just got finished developing the company intranet site and thinking that everything was working I boasted about how good it was by getting my boss to login and submit a new job to the db (new job, its a work management app) while i did the same, the pland was to hit the submit button at the same time. He would send one to be read by me and I would send one to be read by him. We both hit submit and the following happened.
The db has somehow fused the two into one. I thought maybe we were to accurate in hitting the submit button together. But I even gave a five second delay between and for some reason the job is being overriden by one user tor the other. In other words we are both sharing the same jobid. I thought this could never happen with sql server supposing that it would lock one request until another was completed and vice versa. But I'm so new to this that I'm just so naieve to think that the db would do this for you. Problem is I'm about to move on and I can't leave the app in this state. Can anyone point to some articles or give some suggestions has to my situation. Most desperately in need!!
Thanks in advance
View 11 Replies
View Related
May 20, 2006
hello
i'm having touble getting my sp done. the problem is as follow..i've found an sp tokenize (which works fine) with the following signature:
CREATE PROCEDURE TOKENIZE (
S VARCHAR(10000),
DELIM CHAR(1))
RETURNS (
ID INTEGER,
TKN VARCHAR(10000))
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE LEN INTEGER;
DECLARE VARIABLE FIRSTCHAR CHAR(1);
DECLARE VARIABLE S2 VARCHAR(10000);
begin
...
SUSPEND;
end^
then i build myself another sp (which also works fine) with this signature:
CREATE PROCEDURE GET_DICENTRIES_BY_ASDSKRPT (
ASDSKRPTINPUT VARCHAR(15))
RETURNS (
ID BIGINT)
AS
begin
...
suspend;
end^
now, what i'm trying to do is write another sp (get_dicentries_by_all_asdskrpts) that accepts a '.'-tokenized string as a parameter (e.g. 'bla.bli.blo.blu'; number of tokens NOT fixed at 4!) and returns the intersection of
GET_DICENTRIES_BY_ASDSKRPT('bla'), GET_DICENTRIES_BY_ASDSKRPT('bli'), GET_DICENTRIES_BY_ASDSKRPT('blo') AND GET_DICENTRIES_BY_ASDSKRPT('blu')
does any of you have an idea how to go about?
thanx,
martin
View 1 Replies
View Related
Jul 23, 2005
Could someone help me get the following SQL statement working?SELECTstandardgame.gamename,standardgame.rowteamname,standardgame.colteamname,standardgame.dollarvalue,standardgame.gameid,standardgame.cutoffdatetime,standardgame.gametype,standardgame.gameowner,(100-COUNT(purchasedsquares.gameid)) AS squaresremainingFROM standardgameLEFT OUTER JOINpurchasedsquares ON standardgame.gameid = purchasedsquares.gameidwhere gametype='$gametype' and dollarvalue = '$dollarvalue' andgameowner = 'GROUP BY standardgame.gameidorder byCASE squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,squaresremaining ASCThe problem is... MySQL doesn't seem to want to let me usesquaresremaining in that case statement since it's not an officialcolumn name. Any idea how I can reference squaresremaining in the casestatement?
View 2 Replies
View Related
Mar 13, 2007
I have a smalldatetime field in SQL.
For the query of my report, I need any transaction that is like 09/2006 (matching the month and year).
So I wrote something like this:
AND (DATE LIKE '%2006%')
That correctly returns all of the 2006 transactions.
Now why won't this work:
AND (DATE LIKE '%2006-09%')
Or how about this:
AND (DATE LIKE '%09%2006%')
What is the correct syntax??
View 3 Replies
View Related
Nov 2, 2007
am an ASP.net developer and i've stucked in a C# windows application ... and i am the linking part ... between the Database and my application ... in ASP i have a wizard that handles getting the data from the controls ... but is it the same in C# windows application ? am using VS2005 TeamSystem... and widows Vista(framework 3.0) .... and this query is getting executed and return 1 ... and the intellecence is telling me to insert 4 strings
int rowseffected = loginNamesTableAdapter.InsertQuery(textBox1.Text.ToString(), textBox2.Text.ToString(), textBox3.Text.ToString(), textBox4.Text.ToString());
MessageBox.Show(rowseffected.ToString());
i don't know how to link the parameters with the controls ... please show me how ... thx... please help with images ... as i previously mentioned... this is a new experment in C# thx.
__________________
Ahmed Reda
View 1 Replies
View Related
Jul 20, 2005
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies
View Related
Jul 24, 2006
Using Vs2005 sqlServer 2005 When i try to connect i get this error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Connection string: <connectionStrings> <add name="pubsConnectionString1" connectionString="Data Source=.;Initial Catalog=pubs;User=mk;pw=x" providerName="System.Data.SqlClient" /> </connectionStrings> Calling code : try { using (SqlConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings[0].ConnectionString)) { cn.Open(); } } catch (Exception ex) { Response.Write(ex.Message); }
View 1 Replies
View Related
Mar 2, 2007
I have a table adapter query with a like clause that I can't get to work. The field is "Type", so I have "LIKE '%@Type%'". When I click the Execute Query button to test, not only does nothing get returned, I don't get the chance to enter the parameter. If I change LIKE '%@Type%' to say, LIKE '%book%', the appropriate records are returned. I actually need to check two parameters. If I ad the second parameter, the where clause becomes(Type LIKE '%@Type%') AND (SendState = @SendState)When I test the query, a screen pops up to let me enter the state, but not the Type. I can't see anything wrong with the query, but something must be. Diane
View 4 Replies
View Related
Jun 6, 2007
I am having a most frustrating time getting this working. I have two machines, one named 'REMOTE' and it contains my SQL Server (2005 developer edition). Then I have my main PC, named 'DOUGAL' The server instance on the REMOTE computer is called 'SQLSERVER' and there is only one instance. The server is showing as running etc and I can log in locally on the REMOTE computer easily via the Microsoft SQL Server Management Studio etc. I have two logins that are working fine, sa and a login called 'db_user'.Now I am trying to connect in the server explorer in visual studio. When I try and connection i get the following error; An Error has occured while establishing a connection to the server. When connectiong to SQL Server 2005 this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provder: Named Pipes Provider, error: 40 - Count not open connection to SQL Server). I have tried connection to 'REMOTE/SQLSERVER' 'REMOTE' and with its IP address to - without any luck. So, to fix this problem I have checked that remote connections are allowed (and restarted the system). I have also checked that named pipes and tcp/ip connections are enabled. I have searched around with the aid of my best friend, Google. However, I've only found suggestions that involve trying the fixes I already tried.
View 4 Replies
View Related
Nov 6, 2007
Ok, what I am tryin to do is quite simple i do believe. Basically I have an aspx page with a textbox, a button and a datagrid linked to my sql db. What I am wanting is for a user to enter in their Social Security Number in the text box and click the button. If the SSN matches a record in the DB then it binds to the datagrid and shows it, IF no match is found then it displays a link to click on to continue onto the next page. This is to stop duplicate entries. I can get one to work by itself but not both of them at the same time. Here is my code.<%@ Page Language="VB" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><script language="VB" runat="server"> Sub btnSearch_OnClick(sender as Object, e as EventArgs) Dim objConnection As SqlConnection Dim objCommand As SqlCommand Dim objAdapter As SqlDataAdapter Dim objDataSet As DataSet Dim strSearch As String Dim strSQLQuery As String ' Get Search strSearch = txtSearch.Text ' If there's nothing to search for then don't search ' o/w build our SQL Query and execute it. If Len(Trim(strSearch)) > 0 Then OK, Here is my first IF, this works. ' Set up our connection. objConnection = New SqlConnection("Data Source=BRADBLACKBUR-PCMSSMLBIZ;" _ & "Initial Catalog=Victorypoint;Integrated Security=True;") ' Set up our SQL query text. strSQLQuery = "SELECT FirstName, LastName, SSN " _ & "FROM Applicants " _ & "WHERE SSN LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "ORDER BY SSN;" ' Create new command object passing it our SQL query ' and telling it which connection to use. objCommand = New SqlCommand(strSQLQuery, objConnection) ' Get a DataSet to bind the DataGrid to objAdapter = New SqlDataAdapter(objCommand) objDataSet = New DataSet() objAdapter.Fill(objDataSet) If objDataSet.Tables.Count < 1 Then Right here is where I am having the problem, I need to count the Rows not the Tables, but I dont know what code i should use here. Button1.Visible = True Else ' DataBind DG to DS dgSearch.DataSource = objDataSet dgSearch.DataBind() objConnection.Close() End If End If End Sub
View 2 Replies
View Related
Jun 9, 2004
Greetings,
I am having difficulty connecting to a SQL DB in my ASP page. Each time I run my application I receive the following error, "Login failed for user 'CX259ASPNET'". This is obviously something to do with permissions. I am all out of ideas but I will list the things I have already attempted to see if I either missed something or somebody can add to what I have already done.
1. In SQL Server I right-clicked the db -> properties -> security tab -> selected the option button to "SQL Server and Windows" instead of "Windows Only".
2. I installed SP3 and changed the sa password.
3. I created a new user under my db using the ASPNET account listed. I then changed the permissions to allow selecting, update, insert and delete access.
4. I also created a new user under my db using the IUSR account listed. I changed the permissions to the same settings as above.
I did a search on google which provided with all of the information above, but I am still stuck and hoping that this is a common problem and will be easy to fix.
Below is the code which I am attempting to use.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim strConnString As String = _
"Data Source=(local);" & _
"Initial Catalog=Names;" & _
"Integrated Security=SSPI;"
Dim objConn As New SqlConnection(strConnString)
Dim strSQL As String = "SELECT * FROM [Names]"
Dim objCmd As New SqlCommand(strSQL, objConn)
objConn.Open()
MyDataGrid.DataSource = objCmd.ExecuteReader
MyDataGrid.DataBind()
objConn.Close()
End If
End Sub
Regards,
Corey
View 11 Replies
View Related
Dec 24, 2004
Hi All,
What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also).
=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"
if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"
DECLARE curs1 CURSOR SCROLL
FOR SELECT
*
FROM
school
WHERE
school ='ABC' + @where
=============================================
It gives me all the Records inside the SCHOOL='ABC' ...
Please check my SQL Above and Could somebody tell me , how can I attach the VARIABLE with CURSOR sql statement ?
Please advice me..(:
Thanks !
View 4 Replies
View Related
Feb 22, 2005
I'm trying to get just the day part of the date - 2/22/2005 (getdate()) but instead of returning '22', it's returning '2'. Can someone please tell me what I'm doing wrong?
Thanks!
Lynnette
Here's the code
declare @thisDay varchar
set @thisDay = Convert(varchar, Datepart(day, getdate()))
View 3 Replies
View Related
Jan 29, 2006
hi..i am kind of new to asp.net n having trouble with the SqlException error message.. n this code works as 1st page pass the id to second page and the second page took the id to execute the query..i dun know the wer the error occurs..can give a help..Thanks.
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection connection = null;
SqlCommand command = null;
string sqlConnection = null;
string sql = null;
string ab = null;
sqlConnection = ConfigurationSettings.AppSettings["MSQLConnectionString"];
connection = new SqlConnection(sqlConnection);
if (!Page.IsPostBack)
{
try
{
if (Request.QueryString["categoryID"] == null)
{
}
else
{
ab= Request.QueryString["categoryID"].ToString(); //getting the id from page that pass this values
sql = "Select groupname, categoryid, description from groups where groups.categoryid=?"; // can this query execute?
command = new SqlCommand(sql, connection);
connection.Open();
command.Parameters.Add(new SqlParameter("categoryid", ab));
reader = command.ExecuteReader(); // error on here "SqlException"
while (reader.Read())
{
group.InnerText = reader["groupname"].ToString();
desc.InnerText = reader["description"].ToString();
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
View 1 Replies
View Related
Jun 21, 2001
Hi everybody!
I´m trying to start SQL Mail in SQL7. I configure the exchange client, test the Mailbox, but when i try to start Mail, Console show me the following error:
Error 22030 - A MAPI Error - MapiLogonEx Failed due to MAPI Error 273
NOTE: The Exchange server is in another domain.
I think that i'm forgetting something (i´m new on this)...
Thanks in advance
View 2 Replies
View Related
Nov 17, 2000
Hello,
I've got some trouble when using xp_readmail.I check the email count and saved the attached files in c:winnt.
The problem is when the attached files are saved cause the file name is cut in dos format (8 characters + . + 3 characters) but my file's name is like toto.tutu.titi_tata.txt so is there a way to keep the name unchanged when saving attached files ?
Thanks for your answers
View 1 Replies
View Related
Jan 13, 2000
I've been having this huge problem with date and SQL 7, like lot of people. I use asp with the sql and trying to do this query where startdate should equal or smaller the date today, here are to queries that worked, but not anymore.
SELECT * FROM tbl_date WHERE convert(varchar(10), startdate, 101) = convert(varchar(10), getdate(), 101)
this query worked until the year 2000, the normal y2k bug
then this one, wich i saw here on messageboard:
select * from tbl_date where startdate =< '" & date & "'"
well this worked until today, when it just stop working
so does anybody know what to do?
hope to get answer on this
View 1 Replies
View Related
Sep 20, 2000
Replication problem:
Server A is Publisher/Distributor SQL7
Server B is Subscriber SQL7
Successfully set up a publication for table on Server A
Synchronisation fails with error that 'The network name cannot be found' and the following file could not be created:
The last action is 'Server AC$MSSQLRepldataunc<pub name><date string>ablename.sch'
... which is odd because the file IS created and contains a correct looking table definition.
So, manually sync data on this one table and try again, now it can't connect with my server, which is weird cos I
can connect through EM.
Any thoughts???
G.
View 2 Replies
View Related
Apr 17, 2000
Take a look at this code:
CREATE PROCEDURE sp_addCustomer
@fnamechar(25),
@lnamechar(25),
@companyNamechar(30),
@addressvarchar(50),
@zipvarchar(10),
@citychar(20),
@countrychar(20),
@emailchar(30)
AS
IF @email = (SELECT email FROM customers WHERE @email = email)
PRINT 'This customer is already in the database'
ELSE
PRINT 'New Customer Added'
INSERT INTO customers
VALUES (@fname,@lname, @companyName, @address, @zip, @city, @country, @email)
Looks pretty straightfoward, however when I add a duplicate customer, I get the 'This customer is already in the database' message and then the record is added. I don't know why this is happening. Can anyone help?
Thank you,
Nathan
View 2 Replies
View Related
May 6, 2003
Hello All,
I am attempting to port a database from one server to another, I am using the DTS wizard to so this, the Db copies over to the other server but it drops the views, usernames, stored procs etc. Is there another way to do this? possibly with T-SQL? Any suggestions would be great.
Thanks
Tony
View 1 Replies
View Related
