|
In VB i am using this string:
sqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=secret;database=EventMngt")
and works fine for both SQL2000 and 2005.
|
|
|
|
|
Is this sql server express or the full version. I would guess that the full version does allow remote connections by default ut i know that express does not. If it is express its an easy fix anyway.
Jonathan
|
|
|
|
|
This is because the default installation for SQL 2005 only lets you connect on the same computer (Shared memory protocol). To connect remotely with TCP, Named Pipes or whatever, you have to set up the protocols you want to use.
Go to Start -> Programs -> Microsoft SQL 2005 -> Configuration tools -> SQL Server Configuration Manager
Then open the treenode called SQL Server 2005 Network Configuration and click on Protocols for MSSQLSERVER.
Should be able to figure the rest out.
Regards,
Chad
|
|
|
|
|
Chad is correct. I had the same problem when trying to access SQL 2005 Express from a different computer. Did the steps he provided and all is well. Just my two cents worth 
Paul
|
|
|
|
|
Its the SQL 2005 Express edition, and the thing is that all that i'm trying to do is access my own pc, i mean (local), and it keeps sending me the same error even if i use: "server=localhost" or "server=(local)", i even tried the examples that other guys told me (i mean the ones in the forum), but it doesn't work... what i'm i doing wrong?
|
|
|
|
|
OK, the express edition is quite different to SQL server 2005. The comment above was quite correct but perhaps not explained completely.
The link below will give you full details on how to do this so there is no point me repeating any of it. Read it completely as its a good overview of how the express editions work.
Pay particular attention to the sections "Configure for network connectivity" and "Programmatically opening a database from a remote computer". These explain exactly how to do what you want to do.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvsdev05/html/vs05b1.asp[^]
Jonathan
|
|
|
|
|
Ok Here is thew error:
An OleDBParameter with ParameterName 'TableNumber' is not contained by this OleDbParameterCollection.
Here is the code:
private void UpdateTable(string strTable, decimal decSubtotal)
{
try
{
objUpdateSubtotal.Parameters["Subtotal"].Value = decSubtotal;
objUpdateSubtotal.Parameters["TableNumber"].Value = strTable;
oleDbConnection1.Open();
objUpdateSubtotal.ExecuteNonQuery();
oleDbConnection1.Close();
}
catch(System.Exception caught)
{
MessageBox.Show(caught.Message);
}
}
And here is the Sql statementr:
UPDATE tables
SET Subtotal = ?
WHERE (TableNumber = ?)
What am I missing?? (I built the statement in VS query builder in the same project and verified it)
Thanx in Advance !
Jude 
|
|
|
|
|
It means that your objUpdateSubtotal object doesn't have a parameter in its collection, called "TableNumber". We can't tell you exactly what's wrong, because you haven't supplied the code that builds your objUpdateSubtotal object.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Ok, here is the statement that VS created when I dragged and dropped an OleDbCommand on the form:
private System.Data.OleDb.OleDbCommand objUpdateSubtotal;
I then changed the CommandText field to :
UPDATE tables SET Subtotal = ? WHERE (TableNumber = ?)
through the query builder.
Thank you!
|
|
|
|
|
TheJudeDude wrote: when I dragged and dropped an OleDbCommand on the form:
Mistake number one. It's always much better to write your own DB code than to rely on the designer to do it for you. You have MUCH greater control, easier to debug, and you won't end up making mistake number 2.
Mistake number 2. Just changing the SQL statement will not add the Parameter objects to the Parameters collection. OleDb doesn't use named parameters so you have to make sure that the parameter objects you add are done in the correct order as your SQL statement expects them.
You're missing something like this:
OleDbCommand cmd = new OleDbCommand("UPDATE tables SET Subtotal = ? WHERE (TableNumber = ?)", connectionString);
cmd.Parameters.Add("@Subtotal", OleDbType.Single, 4).Value = decSubtotal;
cmd.Parameters.Add("@TableNumber", OleDbType.Char, 10).Value = strTable;
This code is, of course, not guaranteed to work because there is so much more we don't know about your code, database field types and widths, ...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Gotcha...just received the book "Mastering C# Database Programming" by Jason Price.
Well, what I did was look at the Data-->Parameters(Collection)...and the TableNumber had the word "Original_" in front of it...
What should be my next investment in futhering my knowledge in C# and .Net?
Thanx
|
|
|
|
|
we are using an object database for our OLTP but for reporting we have got some issues about performance as the cpu becames a bottleneck.And we want to be able to run on low end computers...
One of our team members suggested to replicate the object database to a SQL table.But just a single one.The most denormalized thing ever.
is this the fastest way we can get in reporting?
*we dont want a harddisk,ram,cpu bottleneck.( run on cheap staff)
|
|
|
|
|
erdsah88 wrote: The most denormalized thing ever. is this the fastest way we can get in reporting?
Reporting databases tend to be denormalised because what is efficient for reporting is the complete opposite to what is efficient for OLTP.
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
|
|
|
|
|
Colin Angus Mackay wrote: Reporting databases tend to be denormalised because what is efficient for reporting is the complete opposite to what is efficient for OLTP.
thanks. I will give it a try.I am getting use to the gray aeras in my code.
|
|
|
|
|
I want to produce a table of counts, by month, for each part type I have in inventory. Here is my table defintion:
tbl_MainReport.ReportID - primary key
tbl_MainReport.Author - text
tbl_MainReport.POI_Time - purchased date
tbl_MainReport.PartType - 1..5 (1=wrench,2=hammer,3=driver,4=plier,5=knife)
tbl_MainReport.PartSubType - 1..8
tbl_MainReport.Description - memo
I have created a SQL statement that comes close, but groups by the "raw" date (MM/DD/YYYY HH:mm:ss) and not just by months:
TRANSFORM Count(*) AS TotalByPartType
SELECT tbl_MainReportInfo.POI_Time
FROM tbl_MainReportInfo
GROUP BY tbl_MainReportInfo.POI_Time
ORDER BY tbl_MainReportInfo.POI_Time
PIVOT tbl_MainReportInfo.PartType;
This creates a listing like this:
POI_Time | 1 | 2 | 3 | 4 | 5
m/d/yyyy h:mm:ss #
m/d/yyyy h:mm:ss #
m/d/yyyy h:mm:ss #
...
But I want something like this:
POI_Time | 1 | 2 | 3 | 4 | 5
JAN ## ##
FEB ## ##
...
Can you help? Should I just revert to coding up a "Recordset"?
Thanks,
Johnny
|
|
|
|
|
john john mackey wrote: Can you help? Should I just revert to coding up a "Recordset"?
No. Your SQL Statement is almost there.
You need to group by month - which you don't have. So you'll have to create it from the POI_Time which you do have.
Your GROUP BY clause needs to look something like this:
GROUP BY datepart(year, POI_Time), datepart(month, POI_Time)
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
|
|
|
|
|
Thanks for the clue, but I'm getting an error when I do:
TRANSFORM Count(*) AS TotalByPartType
SELECT tbl_MainReportInfo.POI_Time
FROM tbl_MainReportInfo
GROUP BY DatePart("m",tbl_MainReportInfo.POI_Time)
ORDER BY tbl_MainReportInfo.POI_Time
PIVOT tbl_MainReportInfo.PartType;
Error is: You tried to execute a qry that does NOT include the specified expression 'POI_Time' as part of an aggregate function.
I have also tried removing the ORDER BY and changing the SELECT to SELECT datepart("m",tbl_MainReportInfo.POI_Time)...
but have not been successful.
Johnny
|
|
|
|
|
If got a table named Horses with column Id and Horse_Name
and a table named Riders with column Id and Rider_Name.
Now i want a dataset using the third table named Stables and column IdHorses, IdRiders and Rider_NF.
How should i create a select statement selecting the Horse_Name and Rider_Name sorted on Rider_NF
Anny suggestions and code are welcom
|
|
|
|
|
Your SQL statement will look like this:
SELECT h.Horse_Name, r.Rider_Name, s.Rider_NF
FROM Stables AS s
INNER JOIN Horses AS h ON h.Id = s.IdHorses
INNER JOIN Riders AS r on r.Id = s.IdRiders
ORDER BY s.Rider_NF
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
|
|
|
|
|
It gives a error on Horses and Riders as a invalid object name.
I produced something like this and it seems to work:
SELECT *, Boekelo_Riders.Rider_Given_Name + ' ' + Boekelo_Riders.Rider_Family_Name
AS Rider_Full_Name
FROM Boekelo_Horses, Boekelo_Riders, Stables
WHERE Stables.IdHorse=Boekelo_Horses.Id
AND Stables.IdRider=Boekelo_Riders.Id
ORDER BY Stables.rider_NF, Boekelo_Horses.horse_name
The only thing i am missing is to add a extra column that contains a substitute for the rider_NF that is contained in a Country table.
In that table is the fullname of the riders national federation.
It may be that my solution contains a pitfall.
|
|
|
|
|
Sorry but found the problem now.
SELECT Boekelo_Horses.Horse_Name, Boekelo_Riders.Rider_Family_Name, Stables.Rider_NF, Country.LangEng FROM Stables AS Stables
INNER JOIN Boekelo_Horses
AS Boekelo_Horses ON Boekelo_Horses.Id = Stables.IdHorse
INNER JOIN Boekelo_Riders
AS Boekelo_Riders ON Boekelo_Riders.Id = Stables.IdRider
INNER JOIN Country
AS Country ON Country.NFid = Stables.Rider_NF
ORDER BY Stables.Rider_NF, Boekelo_Horses.Horse_Name
But still one question remains, if a Rider_NF is not on the Country table it should be on the list, ?use a other type of join
|
|
|
|
|
gharry wrote: But still one question remains, if a Rider_NF is not on the Country table it should be on the list, ?use a other type of join
LEFT OUTER JOIN because you want everything from Stables regardless if it is joined with other tables. In the case where there in no join with other tables then those columns will appear as null in the result set.
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
|
|
|
|
|
What is a named variable and why use it?
The variable @namedVariable is a "named variable."
The "@" prefix is required for SQL Server named parameters.
|
|
|
|
|
kenn_rosie wrote: What is a named variable and why use it?
It can be used like a variable in a stored procedure, or passed as a parameter to a query.
Why use a variable in any programming language?
Also, for security in passing data through from client applications. See SQL Injection Attacks and Tips on How to Prevent Them[^]
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
|
|
|
|
|
Hi,
Whenever a user modifies a customers data I create a log with only the id’s(int) and the datemodified(datetime). Then I want to view the 10 latest customers that the user modified, sorted by datemodyfied and with unique customernames.
So I want to select Customers.customerid and Customers.customername sorted by entries in table CustomerLogs, sorted by CustomerLogs.datemodified. No doubles of customername allowed.
I’ve tried some different joins but nothing works exactly as I want.
In a WHERE clause I have author_userid=2 AND owner_companyid=1
Can somebody give some advice, please?
I use SQL Server 2000
TABLE Customers
Customerid | customername
TABLE CustomerLogs
customerlogid | customerid | author_userid | datemodified | owner_companyid
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
