|
Hi.
I'm running SQL Server 2000.
I have a little problem, and I do not know how to solve it. I have a News tables, a Category table, and a Publisher table. When inserting a new news article, the user can select the news category and the news publisher from drop downlists. A category is required, but not a publisher. So if no publisher is selected, then I make this foreign key in the News table NULL.
My problem comes in when I want to retrieve a news article that has a publisher ID of NULL. The reason for this is because it goes and looks for an ID of NULL in the publishers table and finds none. But what I actually want is for it to bring back the complete news article, and make put and empty string in publisher name, and 0 in publisher ID, or something like this. My SQL statement in my stored procedure looks like this:
SELECT
PKNewsID,
FKNewsCategoryID,
LinkTitle,
NewsTitle,
NewsBodyCopy,
NewsDatePublished,
Publish,
FKNewsPublisherID,
NewsPublisher,
NewsCategory
FROM News
INNER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID
INNER JOIN NewsCategory ON News.FKNewsCategoryID = NewsCategory.PKNewsCategoryID
WHERE PKNewsID = @NewsID;
Please can someone help me with this.
Regards,
ma se 
|
|
|
|
|
Then an INNER JOIN isn't going to work as it requires that all rows exist.
Change this line:
INNER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID to
LEFT OUTER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID
You will get nulls in the columns that came from NewsPublisher when there is no join.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
how do i connect to a database and pick elements from it at random to fill and array..
first i need to know how to connect to it in the first place and be able to read from it.. please your quick help is needed..
thanks in advance
Nab
|
|
|
|
|
This thing is very strange.I have been using the following connection string all these days
data source=server;initial catalog=NIA;password=tpassword;persist security info=True;user id=sa
But now all of my .net application is not getting connected to sqlserver
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
NewNia.WebForm2.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\NewNia\WebForm2.aspx.vb:40
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Though i can acces my server through query analyzer.Please help
|
|
|
|
|
since your datasource is "server" I assume it's a different machine?
if that's the case, have you recently installed SP2 or SP1 (in Server 2003)?
They change the security model and as a result you won't be able to connect to sql server over a network connection unless you tweak some settings. Search for SP2 in Microsoft and then SQL Server and you will find a work around.
If it's not SP 2, then there is some problem with your connection, or SQL server is not running, or the database NIA is not started, there are a number of reasons why this can happen.
|
|
|
|
|
Hello All,
Whenever I try to execute a Query in Analyzer, I get the below error.
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.
I went Microsoft support site where they have mentioned that the above issue is fixed in service pack 4. Even after downloading the service pack 4, I receive the same error. I have been held up since a week because of this issue.
Please someone get me out of this situation.
For your information, I am using SQL Server 2000 with SP4.
Thanks a lot in advance
Looney
|
|
|
|
|
i want to know the differences betwween ado and ado.net
|
|
|
|
|
i think (but i'm not sure) that ADO.NET is designed for .net languages such as C#, VB.NET, MC++, and so...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
hi guys..
can anyone tell me what might be the error at the following code ?
public void getCounterValue(string countername,string tablename,string email)
{
SqlConnection conn = null;
SqlDataReader reader = null;
int result = 0;
try
{
conn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis");
conn.Open();
SqlCommand cmd = new SqlCommand("select @ctr from @table where @mail = @mail", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@ctr";
param.Value = countername;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@table";
param2.Value = tablename;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@mail";
param3.Value = email;
cmd.Parameters.Add(param);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);
reader = cmd.ExecuteReader();
while(reader.Read())
{
result = Convert.ToInt32(reader[0]);
}
}
catch(Exception x)
{
MessageBox.Show(x.ToString());
}
finally
{
reader.Close();
conn.Close();
}
}
it throws the exception @table doesn`t have a value,
but it has the value tablename from the method construction..
any thougts ?
thx !
good coding !
|
|
|
|
|
You cannot use a parameter in place of a table name (or any object name, eg. Stored procedure, function, etc.)
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
How can you pass the table name to a stored procedure?
|
|
|
|
|
TheJudeDude wrote: How can you pass the table name to a stored procedure?
Previously Colin Angus Mackay wrote: You cannot use a parameter in place of a table name (or any object name, eg. Stored procedure, function, etc.)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Gotcha...use command text w/ defined function with the name passed as a parameter.
Could you answer ( I sure you can)..will you answer this??
I am storing a variable to smalldatetime in a table. The parameter is defined as:
cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now.ToShortTimeString();
When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that?
Thanx in advance!
|
|
|
|
|
TheJudeDude wrote: When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that?
Because it must. Actually the order of the elements (month, day, hours, minutes, etc.) are not important, that is just the user interface. Internally it is stored the same way regardless of the culture.
The type is designed for storing a date with a time to a resolution of one minute. Therefore the date part must be filled with something. If I remember correctly it will be the 1st January 1900.
There is no specific time only type in SQL Server, so you will need to work around the addition of the date.
Finally, you may find that posting a new thread when you have a new question will be seen by more people and you will get a quicker response if I'm not around. Posting on a thread that started last November will only be seen by the person whose post you responded to (assuming they have email notification turned on). Hopefully you'll find that helpful in getting a good response sooner.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanx for the info! If you get this, I have another delima 
I am storing a decimal value in the table that is defined as a decimal, but it is only storing the integer part. In the debugger I would have something like 8.234534, but it stores 8.The definition for the column is decimal 5 allow nulls pricision 4.
The code to store the value is:
cmdSetHours.CommandText = "UPDATE " + strName + " SET Hours = " + decTotalHours +
" WHERE EmpID = " + m_intEmployeeID;
Thanx again!
|
|
|
|
|
I have a table which has 1 Cr records .Now i have a query which has joins on 3 tables includung the one that has the large number of records.The select fields remain the same but the fields in where condition changes as per user selection.I have created a view when there is no where condition and this works fast.But now what do i do when there is a where condition.This query takes 9 min.I have already applied indexes
|
|
|
|
|
Hello,
CREATE PROCEDURE dbo.spFindProduct
@searchText varchar(50)
AS
SELECT * FROM Product
WHERE productName LIKE '@searchText%'
GO
When the user enters a letter in the textbox the stored procedure will find all the words that begin with that letter. When they type a second letter the stored procedure will find all the words that begin with those 2 letters etc. All rows that are matched are displayed in datagrid. This is the same as one of those applications that find the closest word for what you are looking for.
My problem is that the above stored procedure does not return anything, even if the right letter has been entered.
The parameter is being passed correctly: WHERE productName = @searchText
That works and fine if l am looking for the exact word.
WHERE productName = 'F%' that works ok and finds all the rows where product name begins with a F.
But the above stored procedure does not work. Can anyone tell me how to correc this.
Many thanks in advance,
Steve
|
|
|
|
|
That is because you have asked it to search for the literal string '@searchText%' so it will only match product names that start with @searchText because you enclosed it in quotes.
What you need to do is something like this
DECLARE @search varchar(51)
SET @search = @searchText + '%'
SELECT * FROM Product
WHERE productName LIKE @search
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
If Not (Session("searchstring") Is Nothing) Then
Session.Remove("searchstring")
End If
str1 = "select uid, firstname+' '+middlename+' '+lastname as name,login,email,Case When Status = 1 Then '../admin/img/img_status_active.gif' When status=0 Then '../admin/img/img_status_inactive.gif' End status from tb_users where 1=1"
If TextBox1.Text <> "" Then
str1 = str1 & " and last_name like '" & TextBox1.Text & "%'"
End If
|
|
|
|
|
Breddy wrote: str1 = "select uid, firstname+' '+middlename+' '+lastname as name,login,email,Case When Status = 1 Then '../admin/img/img_status_active.gif' When status=0 Then '../admin/img/img_status_inactive.gif' End status from tb_users where 1=1"
If TextBox1.Text <> "" Then
str1 = str1 & " and last_name like '" & TextBox1.Text & "%'"
End If
I'm not sure what this post is supposed to demonstrate. It does not appear to have much to do with the original poster's query. It looks like it is some code you are using some place else that is vaguely applicable, but you have not provided an explanation so it is difficult to see where it is supposed to be helpful.
More importantly, your solution contains some pretty horrible security holes that you should patch. You may want to read the article SQL Injection Attacks and Tips on how to prevent them[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
hi guys..
my problem is like this..
i have a query which is :
string query = "select count(*) AS [username] from [" + tablename + "] WHERE username = '"+username+"'";
and my method is :
public void checkIfUserExists(string username,string tablename)
as u can see, i want to check how many user exists with this username in the database, i want to execute the query, and take the result inside an int
( e.g. int result )
if the result is > 0 , then i won`t allow user to choose that username
else user can register..
how can i do this ? it was easy doing that at php but i cant do this in c# because i can`t understand ado.net very well ( datasets, datagrids,etc..)
any help would be great!
thx!
good coding !
|
|
|
|
|
The way you build your SQL string is susceptable to a SQL Injection attack you may wish to read SQL Injection attacks and tips on how to prevent them[^]
You don't say what database you are using so I'll assume SQL Server 2000
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
int result = (int)cmd.ExecuteScalar();
conn.Close();
This is a very basic example and does not take into account error conditions. You have to provide the connection string (as I know nothing about your database or the security you've set up) and the query (which you have above - although I do recommend securing it as shown in the article I've linked to)
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I'm sure this has been asked 1,000 times already so sorry for this but...
Can 2005 & 2000 co-habitate on a server? If it can then is it a good idea?
E=mc2 -> BOOM
|
|
|
|
|
Albert Einstien wrote: Can 2005 & 2000 co-habitate on a server?
Yes. I am have both happilly running right now.
Albert Einstien wrote: If it can then is it a good idea?
Since they both play nice together I don't see why it would be a bad idea. Some things to be aware of:
* Enterprise Manager cannot see SQL Server 2005, but the new SQL Server Management Studio can see SQL Server 2000.
* Query Analyser from SQL Server 2000 can connect to SQL Server 2005 instances.
* Having both SQL Server 2000 and 2005 on one machine is just like having multiple instances of a SQL Server running. Each instance will launch its own service and run as a separate process.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
We discovered that you can't edit a multi-statement table-valued function in SQL Server 2000 from SQL Server 2005 Management Studio. My bug report[^] (I've just found two duplicates submitted before mine which didn't show up when I searched originally).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
