|
Hi i am havin a bit of trouble with the joins in this query as some of the values stored in the main table may be entered into the table as nulls so
when i try to join the another table using these values it will not work an i dont get back any rows does anyone have any ideas how to solve this problem
Thanks in advance Tim
select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as Name, i.Diagnosis as Injury,
SUBSTRING(CAST(i.Date as nvarchar), 0 ,12) as Date, im2.Name as Prognosis, imgoi.Name as GradOfInjury, impob.Name as PartOfBody,
imtt.Name as TissueType, imact.Name as Activity, imsur.Name as Surface, imsurcon.Name as SurfaceCondidtions, imfw.Name as Footwear,
'Recovered' = CASE WHEN i.Date IS NOT NULL and (i.DateRecovered IS NULL)
THEN 'No' ELSE 'Yes' END from injury i
join Person p on p.Id = i.PersonID
join InjuryMapping im2 on im2.ID = i.Prognosis
join InjuryMapping imgoi on imgoi.ID = i.GradeOfInjury
join InjuryMapping impob on impob.ID = i.PartOfBody
join InjuryMapping imtt on imtt.ID = i.TissueType
join InjuryMapping imact on imact.ID = i.Activity
join InjuryMapping imsur on imsur.ID = i.Surface
join InjuryMapping imsurcon on imsurcon.ID = i.SurfaceConditions
join InjuryMapping imfw on imfw.ID = i.Footwear
where (p.ID = 3 and i.ID = 87) or (p.ID = 7 and i.ID = 82)
order by p.Lastname
|
|
|
|
|
Use an OUTER JOIN, a LEFT OUTER JOIN or a RIGHT OUTER JOIN - Also, it is often better to write INNER JOIN rather than just JOIN as it makes the intent clearer.
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
|
|
|
|
|
|
I have MSDE 2000 on my system. I have a boxed version of VS 2005 Pro, which comes with SQL server 2005 Developer.
I installed VS Pro but chose to exclude SQL Server 2005 Express.
Then I tried installing SQL Developer. It complains that SQL Database Services (which I assume is the basic SQL Server Engine) cannot be installed, as there's no upgrade from MSDE 2000 to SQL Developer.
What's the recommended way of doing this? Get SQL Express to upgrade the MSDE and then install SQL Developer? Or just uninstall MSDE 2000 first? I'd prefer the former if possible.
Reason I opted not to install Express is because various people have reported issues with subsequently going to one of the the full SQL Servers.
On my laptop I did go for the default VS install, followed by SQL Server Developer. This went fine, but there was no previous MSDE database on my laptop. It did, however, miss out Management Studio initially, so I had to run the install again.
Kevin
|
|
|
|
|
Dear all,
I have a problem with connection pooling. I programmed in VB.NET with ADO.NET and SQL Server. Here is my code in one form:
Dim conn as new SqlConnection(strConnectionString)
conn.Open
....
conn.Close
There are 3 SqlConnection instance in my code.
But If I run the form several times (about 25 times), it occurred an error: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
I am sure that I have closed conn as soon as posible, so I do not know why that error happend.
Any comment are welcome.
Thanks for reading.
|
|
|
|
|
It will only be caused if you have some connection left open
Akif
|
|
|
|
|
Thanks for answering. At last, I found the source of problem, beacause I turn on SQL Debuging mode in Visual Studio IDE. If I turn off that mode, the problem did not apprear again.
Thanks
|
|
|
|
|
how to import a data (.xls) in sqlserver using vb codes. tnx in advance.
september
-- modified at 19:35 Wednesday 4th January, 2006
|
|
|
|
|
Hi
u try this
Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim sqlcon As New ADODB.Connection
Dim rs As New ADODB.Recordset
' Excel sheet
Path = "C:\ramani\book1.xls"
ssql = "Provider=Microsoft.jet.oledb.4.0;Data Source=" & Path & ";Extended Properties=Excel 8.0"
If con.State = 1 Then con.Close
con.Open ssql
ssql = "select * from [sheet1$]"
If rs.State = 1 Then rs.Close
rs.Open ssql, con
'Sql server
ssql = "Provider=sqloledb;Data Source=IBMX225;Initial Catalog=Master;User Id=sa;Password=;"
If sqlcon.State = 1 Then sqlcon.Close
sqlcon.Open ssql
While Not rs.EOF
sstr = "insert into excel values(" & rs(0) & ",'" & rs(1) & "')"
sqlcon.Execute sstr
rs.MoveNext
Wend
End sub
-ramani
|
|
|
|
|
I use this code to setup a connection to a CSV file:
ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("Sql\C_Registered_horses.csv") & ";Extended Prperties=""text;HDR=Yes;FMT=Delimited"""
Dim QuerySQL As String
QuerySQL = "SELECT * FROM C_Registered_horses.csv"
Dim objConn As New OleDbConnection(ConnectionString)
Dim objCmd As OleDbCommand
objCmd = New OleDbCommand
objCmd.CommandText = QuerySQL
objCmd.Connection = objConn
But when i execute the objConn.Open() i get the error:
System.Data.OleDb.OleDbException: Could not find installable ISAM
Any idea's
|
|
|
|
|
Im guessing the problem is with the connection string.
snipt from ADO Connection Strings[^]
strConnection = _T("Driver={Microsoft Text Driver (*.txt; *.csv)};"
"Dbq=C:\\DatabasePath\\;Extensions=asc,csv,tab,txt;");
If you are using tab delimited files, you must create the schema.ini file, and you must inform the Format=TabDelimited option in your connection string.
for more help with connection strings see ConnectionStrings.com/[^]
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
-- modified at 1:34 Thursday 5th January, 2006
|
|
|
|
|
I am sorry to say but using:
ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & Server.MapPath("Sql\") & ";Extensions=asc,csv,tab,txt;"
gives a error:
An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.
What does the _T mean on the start of the sequence
|
|
|
|
|
taken from connectionstrings.com
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
Hmm, I see this is VB.NET, right? First example wont work. Hows this work?
Connection="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/Sql\") & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
|
|
|
|
|
Seems i forgot somehow the extra "". Would think that these extra's might not work in a string. It now works thanks
|
|
|
|
|
Your welcome.
The extra "" are escape quotes so it includes one of the quotes as part of the string. Without those it doesnt get a fully qualified string (for the oledb connection)
I always love helping people with languages I haven't seen. 
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
|
|
|
|
|
Now i would like to display all the data in a Datagrid using:
Try
objConn.Open()
dg1.DataSource = objCmd.ExecuteReader()
dg1.DataBind()
objConn.Close()
Catch Err As Exception
ltlError.Text = Err.ToString()
Finally
objConn.Close()
End Try
but i only get the first column and not the 4 other. How come.
|
|
|
|
|
gharry wrote: but i only get the first column and not the 4 other. How come.
Not sure to tell you the truth, I dont know VB.NET. If I had to venture a guess thought the problem probally related to whats being pulled from the CVS file.
Create the DataReader and loop through it output the results to the debuger. Eliminate that as a posible cause.
My guess would be that when getting the data from the CVS file its not getting all of the rows / cols. Or there is a problem with the Columns in the CVS file. Hmm then again you might have to experiment with the datagrid itself. Is it expecting to recieve the data in the manor in which your providing it? 
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
|
|
|
|
|
i have written a code to insert value below
------------------------------------------------------
Dim cmdselusers As SqlCommand
cmdselusers = New SqlCommand("insert into st(a,b,c) values @a,@b,@c)",con)
cmdselusers.Parameters.Add("@a", "ass")
cmdselusers.Parameters.Add("@b", 0)
cmdselusers.Parameters.Add("@c", "ss")
con.Open()
cmdselusers.ExecuteNonQuery()
con.Close()
-------------------------------------------------
but i get the following error
Prepared statement '(@a nvarchar(4000),@b bigint,@c nvarchar(4000))insert into st ' expects parameter @b, which was not supplied
wht may be the reason for tht
help?????????
|
|
|
|
|
You have a missing opening bracket after values
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
|
|
|
|
|
I cant get import anything to my SQL DB. spISQLServer is working fine bec it has been tested. I cant figure it out why didnt it import? Did I forgot something to add? Please tell me bec I am so extremely frustrated!!
spITable = spIDatabase->Tables->Item("HoleDiameters");
spIBulkCopy.CreateInstance(__uuidof(BulkCopy));
spIBulkCopy->DataFilePath = L"C:\\tesss.XLS";
spIBulkCopy->DataFileType = SQLDMODataFile_TabDelimitedChar;
spIBulkCopy->ErrorFilePath = "c:\errores.log";
spIBulkCopy->RowDelimiter = "\n";
spIBulkCopy->MaximumErrorsBeforeAbort = 1000;
spIBulkCopy->ServerBCPDataFileType = SQLDMOBCPDataFile_Native;
spIBulkCopy->UseBulkCopyOption = true;
spIBulkCopy->IncludeIdentityValues = true;
//for import
spITable->ImportData(spIBulkCopy);
spISQLServer->Close();
|
|
|
|
|
I've just been posed a challenge: to update a DataGrid on one client when another client changes the source table for that grid on a central database, all three connected on a standard TCP/IP network.
Now I vaguely (probably incorrectly) remember something about earlier versions of ADO being able to detect changes in source data, but I may be senile already, so I said, "Yes, it's possible!"[1]
Assuming there is no built-in way to detect these changes, I envisage several possible extrinsic solutions:
1. When other clients update the row, inform them it has changed since they read it.
2. Have each client broadcast the table/row ID to all others when they change it.
3. Have the database broadcast the table/row ID top all clients when it changes.
The last two then involve interested clients fetching the current version of the row if necessary.
It all seems rather messy, but this is not an uncommon problem, so what do I do?
[1] It's not official work, so I'm allowed to do that.
The DJ's took pills to stay awake and play for seven days. - Jim Morrison, Black Polished Chrome.
|
|
|
|
|
The solution depends upon how close you need to keep the synchronization. If 5-10 seconds is fine, you can just keep a TimeStamp on the rows. Every so often, send the client to request any rows that exceed it latest TimeStamp. It will usually return zero records and nothing needs to be done. This does require that rows be marked for deletion instead of physically deleted immediately.
The other solution I have used is to create a service and all clients access the data through it. It is then easy to send update notices down the socket to each client. I try to avoid this solution since it will complicate future maintenance.
|
|
|
|
|
Michael Potter wrote: The other solution I have used is to create a service and all clients access the data through it. It is then easy to send update notices down the socket to each client. I try to avoid this solution since it will complicate future maintenance.
Never mind complicating current development.
Thanks, I think I'll look into the first solution. I suppose I could get a separate DataSet of changed rows and merge it into the DataSet behind the display, no?
The DJ's took pills to stay awake and play for seven days. - Jim Morrison, Black Polished Chrome.
|
|
|
|
|
Can we insert a record directly into ms-access table using sql stored procedure?
could anyone help me.
|
|
|
|
|
Add the Access database to the SQL Server as a "Linked Server" then you should be able to write an update statement from a regular SQL statement.
"People who never make mistakes, never do anything."
My Blog
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
