|
I need to teach some 'users' a few basics of SQL. They are picking up most of the concepts fairly easily. That was until we came to JOINS. Because of the structure of the database, they'll often have to do some LEFT JOINS in their queries.
Does anybody know of a book / website that explains joins in a simple easy to understand way. (Pictures would be nice)
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
|
Hi!
:-OHow can I select from
InvId Company
------------------
1 a
23 b
56 c
12 a
21 c
this data set:
Company Invoices
-----------------------
a 1,12
b 23
c 56,21
Many thanks in advance!
_____________
stefan bornuz
|
|
|
|
|
|
Thanks Jeremy!
I just hoped that MS SQL has a trick for doing this with a 'simple' query, without the need of procedural processing.
Thanks again,
_____________
stefan bornuz
|
|
|
|
|
Typical situation; Project spec calls for MS SQL 2000, come to install at client, client is running MS SQL 7.
There should not be much of a problem, they are not that different (not like 6.5 was to 7.)
However there is a problem; I backed up the SQL 2000 database. When I come to restore it on SQL 7 it gives an error (Error 3624, tableIndex < *****_ARRAYLEN.)
So I need to know: Can I convert a 2000 backup to 7? Is there a switch I need to set in SQL 2000 when I backup?
Any other bright ideas? Doing a script is no good because there is plenty of data in the database that needs to be retained.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
Shog9:
Paul "The human happy pill" Watson
|
|
|
|
|
FYI.
The answer is: No, Microsoft SQL 7 cannot restore a backup made by Microsoft SQL 2000.
Doh.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
Shog9:
Paul "The human happy pill" Watson
|
|
|
|
|
Hi
I am using fallowing table, storedprocedure, function and event handlers.
My problem is that I am abled to update one row successfully only.
If there are rows more than one that satisfy the matching criteria (i-e status=1 in this case, let 9 rows) then
I get the fallowing Error Message:-
"Concurrency Violation : the updateCommand affected 0 records"
What should I do to avoid this Error?
My task was to run a storedprocedure and get the description of
updated rows though event handler but I am doing it for one only,
how multiple rows can be updated in my situation?
What am I doing wrong?
Regards
Mairaj
'////////////
Table1 :-
Column Type
---------------------------
id Auto-Number
Description varchar(50)
satus bit
'/////////////////// Storedprocedure:-
CREATE PROCEDURE dbo.[Test_Update]
AS
UPDATE Table1 SET status = 0, Description = "UpDate test"
WHERE status=1;
GO
'////////// function :-
Public Function Excecute_Test_Update()
Dim MyTrans As SqlTransaction
Dim strConnString As String = "data source=(local);initial catalog=" & "Test" & ";uid=sa;password=;"
Dim connDB As New SqlClient.SqlConnection(strConnString)
Dim da As New SqlClient.SqlDataAdapter()
Dim ds As New DataSet()
Dim dtDB As DataTable
Try
connDB.Open()
Dim cmdDB2 As New SqlClient.SqlCommand()
cmdDB2.CommandType = CommandType.Text
cmdDB2.CommandText = "SELECT status, Description, Id from Table1"
cmdDB2.Connection = connDB
da.SelectCommand = cmdDB2
da.Fill(ds, "Table1")
connDB.Close()
dtDB = ds.Tables("Table1")
Dim strExpr As String
strExpr = "status" & " = " & 1
Dim foundRows() As DataRow
' Use the Select method to find all rows matching the filter.
foundRows = dtDB.Select(strExpr)
da.UpdateCommand = New SqlCommand("Test_Update")
da.UpdateCommand.CommandType = CommandType.StoredProcedure
da.UpdateCommand.Connection = connDB
Dim Len As Integer = foundRows.Length - 1
Dim i As Integer
For i = 0 To Len
foundRows(i)("status") = 0
foundRows(i)("Descrition") = "UpDate test"
Next
MsgBox("Updated count:" & dtDB.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent).Length)
connDB.Open()
AddHandler dtDB.RowChanged, New DataRowChangeEventHandler(AddressOf Row_Changed)
AddHandler da.RowUpdated, AddressOf OnRowUpdated
MyTrans = connDB.BeginTransaction()
da.UpdateCommand.Transaction = MyTrans
da.Update(ds, "Table1")
MyTrans.Commit()
Catch exp As Exception
MyTrans.Rollback()
MsgBox("Exception: " & exp.Message)
End Try
RemoveHandler dtDB.RowChanged, AddressOf Row_Changed
RemoveHandler da.RowUpdated, AddressOf OnRowUpdated
MyTrans.Dispose()
connDB.Close()
End Function
'///////////// handlers :-
' handler for rowchanged event
Private Sub Row_Changed(ByVal sender As Object, ByVal e As DataRowChangeEventArgs)
MsgBox("row changed")
End Sub
' handler for RowUpdated event
Protected Sub OnRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
MsgBox("Id " & e.Row("Id").ToString() & " Description " & e.Row("Description").ToString())
End Sub 'OnRowUpdated
|
|
|
|
|
Hi all.
QUESTION 1:
I want to know how Microsoft's data mining solution measures up to those provided by other vendors.
QUESTION 2:
OLAP/Analysis Service comes with Microsoft SQL Server. In addition to the regular data drilling and presentation/charting layer support, it supports two analytical components: Microsoft Clustering (Expectation Maximization) and Microsoft Decision Tree (predictive, classification).
QUESTION 3:
I have no experience with Oracle, PL-SQL, digimine, Cognos, SAS... Can anyone tell me how these products compared to OLAP that comes with SQL server? I know some applications are more specialized for a very specific purpose - clickstream for example.
QUESTION 4:
Software like SPSS, minitab. mathematica offers a whole bunch of analytical tools - from simple column average to clustering to regression. Does any of the OLAP solutions comes with these capability?
QUESTION 5:
What are the most important features that one can expect from a BI solution?
QUESTION 6:
general/specific comment, case studies/application, price and licencing cost estimates will be helpful.
thanks a bunch.
norm
|
|
|
|
|
I have a list of names in my SQL database("Books"), datatable("BookName") that I would like to use to create directories on my computer. The following code work excellent using a .txt file of the book names, however the SQL database has the same identical list of names. I'd like to use the SQL instead of the .txt file approach... Help...........
string strSource = (@"C:\bookName.txt");
DirectoryInfo source = new DirectoryInfo(strSource);
string strTarget = (@"C:\books\");
DirectoryInfo target = new DirectoryInfo(strTarget);
StreamReader sr = File.OpenText(strSource);
string read;
while ((read = sr.ReadLine()) != null)
{
target.CreateSubdirectory(read);
}
sr.Close();
keivn@upgrade1.com
|
|
|
|
|
Try something like:
string strTarget = @"C:\books\";
string connection = "server=(local);database=books;trusted_connection=true;";
using(SqlConnection cn = new SqlConnection(connection))
{
cn.Open();
using(SqlCommand cmd = new SqlCommand("SELECT [Name] FROM Books", cn))
using(SqlDataReader rst = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while(rst.Read())
{
DirectoryInfo book = new DirectoryInfo(
Path.Combine(strTarget, rst.GetString(0));
if (!book.Exists)
book.Create();
}
rst.Close();
}
cn.Close();
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
you could try this
set @filepath = 'c:\windows\new_dir'
SET @String = 'mkdir ' + @filepath + '\'
exec master..xp_cmdshell @String
thanks
si
|
|
|
|
|
I have this code :
Static ConnectionString As String = SQL_CONNECTION_STRING
Dim WarehouseConnection As New SqlConnection(ConnectionString)
Dim strSQL As String = "SELECT * " & _
"FROM Orders " & _
"WHERE OrderID = '" & (CType(DataGrid1(currentrow, 0), String)) & "'"
Dim ProductAdapter As New SqlDataAdapter(strSQL, WarehouseConnection )
I get the order number from a double click on datagrid. Now .. I just want to "chain" out to my file .. Orders .. and bring back that single records data. I have the sqlcmd statement but my connection string keeps erroring out. I keep on getting errors ..
Thanks
__________________
William E Gollnick
Gollnick
|
|
|
|
|
What specific errors are you getting?
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hello.
Does MS Access support BatchUpdates?
I have a project in which im using following code but it gives me an error when there is
more than 2 records in the recordset which have been changed
IDispatch error # 3156 :Number of rows with pending changes exceeds the limit.
//////////
_variant_t v(m_pProject->m_ptrConnection.GetInterfacePtr());
// Open recordset
m_pRstBugs->CursorType = adOpenKeyset;
m_pRstBugs->LockType = adLockBatchOptimistic;
m_pRstBugs->Open((LPCTSTR)ProjectName, v,
adOpenKeyset,adLockBatchOptimistic,adCmdTableDirect);
if (m_pRstBugs->Supports(adIndex) && m_pRstBugs->Supports(adSeek))
{
// im making an index
m_pRstBugs->Index = DBtags::m_sProjectIndexName;
}
// ...
// here is the problem: Error -> "Number of rows with pending changes exceeds the limit."
m_pRstBugs->MoveFirst();
while(!m_pRstBugs->adoEOF)
{
_variant_t x("test");
m_pRstBugs->Fields->Item["description"]->Value = x;
m_pRstBugs->MoveNext();
}
m_pRstBugs->UpdateBatch(adAffectAll);
// end test
|
|
|
|
|
i am trying to develop a search function. user can search by entering a few types of information. the search will display all the results matching the words entered by the user.
the problem is:
if i enter the barcode number only, all the documents are displayed. and if i leave the txtBarCode blank and enter description, no document is displayed, even when the document table in my database contains the word. why is this happening?
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2">Bar Code Number</font></div>
<td width="56%"><asp:textbox id="txtBarCode" runat="server" TextMode="SingleLine"></asp:textbox></td>
<td height="25"><font face="Arial, Helvetica, sans-serif" size="2">Description</font>
</td>
<td><asp:textbox id="txtDesc" runat="server" TextMode="SingleLine"></asp:textbox></td>
aspx.vb
-------
Dim strConn As String = "Provider=SQLOLEDB..."
Dim objConn As New OleDb.OleDbConnection(strConn)
Dim searchStr2, criteriaArray, queryCriteria,i, query1, rst1,rst2 objConn.Open()
searchStr2 = fp.Searchsyntax
criteriaArray = Split(searchStr2, ",")
queryCriteria = " [tblDocument].BarCodeNo IS NOT NULL"
For i = 1 To UBound(criteriaArray)
queryCriteria = queryCriteria & " AND "
If criteriaArray(i) = fp.Barcode Then
queryCriteria = queryCriteria & "[tblDocument]." & criteriaArray(i) & " LIKE '%" & fp.Barcode & "%'"
ElseIf criteriaArray(i) = fp.Desc Then
queryCriteria = queryCriteria & "[tblDocument]." & criteriaArray(i) & " LIKE '%" & fp.Majordesc & "%'"
End If
Next
query1 = "SELECT * FROM [tblDocument] WHERE " & queryCriteria & " ORDER BY [tblDocument].BarCodeNo"
Laine
|
|
|
|
|
Hi can anyone here help with this?
I have a dataset that is populated with data from two related tables. I create a DataViewManager to manage views of the data based on dynamically created queries which are passed to the DataViewManager.RowFilter. The DataViewManager is bound to a datagrid which displays the results of the queries. The datagrid also displays the child data relating to each row by clicking on the + sign at the start of each row.
Currently the queries I am adding to the RowFilter are based on the parent table, for example;
dvManager.DataViewSettings["client"].RowFilter = "((region = 'Blah') OR ((region = 'Bluh')) AND ((area = 'Here') OR (area = 'There')) AND (gender = 'Male')";
- This approach works well, however I now need to filter records based on values in the child table. If I wanted to do this by going back to the {MSAccess}database I would be creating an SQL statement such as the one below;
SELECT DISTINCT client.*
FROM clientcat AS clientcat_1, clientcat AS clientcat_2, client
INNER JOIN clientcat ON client.clientid = clientcat.clientid
WHERE ((client.clientid=[clientcat].[clientid]) AND (clientcat.category='Literature'))
AND ((client.clientid=[clientcat_1].[clientid]) AND (clientcat_1.category='Art Administration'))
AND ((client.clientid=[clientcat_2].[clientid]) AND (clientcat_2.category='Performance'));
According to MSDN the DataViewManager is capable of this functionality, although they do not offer any examples of exactly how to do it.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data06142001.asp[^]
My question is - Is it possible to query a DataViewManager in this way and can someone show me how?
|
|
|
|
|
Hi, This is a repost of mine from Lounge.
I would like to know the implications of using dynamic sql with in a stored procedure, how it affects execution plan, what is the pros and cons of EXECUTE IMMEDIATE, the oracle native dynamic sql execution command.
please give me pointers to relevant resources.
Cheers,
Venkatraman Kalyanam
Chennai - India
"Being Excellent is not a skill, it is an attitude"
|
|
|
|
|
I can't seem to figure out/ find a website that shows me how to setup my own sql server. I am using services provided by http://no-ip.com[^]. What port do I need to forward? (I'm using the linksys Cable/Dsl Router) Do I need to configure anyything on the server itself?
Steve
McLenithan
Is Bert Evil?
|
|
|
|
|
What problems are you having? Are you able to connect to your box from the outside at all? That should be your first step. The router you are using will allow you to access one of your computers internally, but that is disabled by default (thankfully so). A router is routing traffice between your local (private/internal) network and the outside world. By default, people should not be able to access your machine on the inside from the outside.
I believe that the linksys routers have a setting for a DMZ box. This machine would be available from the outside if you enable this setting on your router (look for DMZ in your router settings). I think to get to the settings of your router, you open a browser to 192.168.1.1. The default login is an empty username and "admin" as the password--unless you've changed this from the factory defaults.
Once you do get the router set up properly, you will probably have to enable the Sql Server port on your router as well. I wouldn't recommend this if you are deploying a website on it, though. It is less secure this way. If you only allow DB traffic from your web box, you'll have a more secure situation.
Now, as far as getting this all to work with your dynamic DNS service, I don't know anything about it. You should probably contact the service provider and see what they suggest. I'm sure you're not the first person who wanted to do this. In fact, once you do figure it out, you should post an article on CP with the steps you took to accomplish this task.
I hope this helps. Let me know if you have other questions.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Thanks for the tips.
Steve
McLenithan
Is Bert Evil?
|
|
|
|
|
CREATE PROCEDURE UpdateCompanyProfile
(
@AccNo varchar(50),
@CompanyName varchar(50),
@Address1 varchar(50),
@Address2 varchar(50),
@City varchar(50),
@State varchar(50),
@ZipCode varchar(50)
)
AS
UPDATE tblCompany SET
CompanyName = @CompanyName,
Address1 = @Address1,
Address2 = @Address2,
City = @City,
State = @State,
ZipCode = @ZipCode
WHERE AccountNo = @AccNo
RETURN
my tables are:
tblCompany
---------
CompanyName (primary Key)
AccNo
Address1
Address2
City
State
ZipCode
tblCustomer
-----------
Name
Email
Phone
Job
CompanyName (foreign key)
when i try to update the CompanyName from the site, i get this error message:
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_tblCustomer_tblCompany'. The conflict occurred in database 'test', table 'tblCustomer', column 'CompanyName'. The statement has been terminated.
how do i edit the sp above to update the CompanyName column in my customer table as well?
Laine
|
|
|
|
|
Change your constraint FK_tblCustomer_tblCompany to include:
on update cascade
General its considered good practice to use a primary key which isn't subject to change.
|
|
|
|
|
Ixpah wrote:
General its considered good practice to use a primary key which isn't subject to change.
Agreed... There should be some other type of identifier for the company name (even if it is a field that autoincrments as you add company names) that is used as the primary key and the forigen key in the other.
In the example given, once the company name is used somewhere, what happens if it ever changes?
Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
Jörgen Sigvardsson wrote:
If the physicists find a universal theory describing the laws of universe, I'm sure the a**hole constant will be an integral part of that theory.
|
|
|
|
|
Hello,
I have a problem with ASP.net page to access an Access database over the network. I get following error:
Microsoft OLE dB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
I tried to add user ASPNET to IIS computer but I had to add it though Active directory and it did not work. I can connect from the computer which is installed .net framework and has ASPNET by installing to this database over the network.
Is is same to add user through active directory and having ASPNET user by installing framework?
If you have any idea, it would be great!!
thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
