|
hi all,
i am retrieving values from 5 tables, for that i've written joins and tried to retrieve,
but here my problem is Even if one value is null the query is not working and returning 0 rows.
so i want to approch in such a way that if anywhere if any value is null, the query should return a null value
and ultimatly query should work with the fields that have values
Please help me
thnks in advance
|
|
|
|
|
Can u please show your query?
The name is Sandeep
|
|
|
|
|
here's my query
select t.date_value, start_time,end_time,upper(r.last_name)+', '+r.first_name teacher_name,
datediff(hh,t.start_time,t.end_time)*t.rate total_rate,t.remarks
FROM dbo.ttcpCustomizedSchedule t left outer join dbo.ResourcePersons r
on r.teacher_id=t.teacher_id where t.tload_id = 5724 order by date_value
i want to make the start_time and endtime using a string format of:
"hh:mm tt"
please help me...
i am using mssql server 2000
|
|
|
|
|
Try: Substring(convert(varchar, StartTime, 100), 13, 8)
|
|
|
|
|
|
Hi,
I would like to know how i can perform update or delete by using Update method of sql data adapter. I have done the insert operation using update method. The code follows...
<br />
public Boolean SetStorageArea(int UID, DataTable dtStorageArea)<br />
{<br />
string strSql;<br />
strSql = "SELECT [Active],[WarehouseID],[StorageAreaID],[StorageAreaShortName],[StorageAreaName], " +<br />
" [Alias],[Notes],[CreatedBy],[CreatedOn],[LastModifiedBy],[LastModifiedOn] FROM [StorageArea]";<br />
try<br />
{<br />
DataSet dsStorageArea = new DataSet();<br />
dsStorageArea.Tables.Add(dtStorageArea);<br />
SqlDataAdapter sda = new SqlDataAdapter(strSql, sqlcon);<br />
SqlCommandBuilder sqb = new SqlCommandBuilder(sda);<br />
sda.Update(dsStorageArea, "StorageArea");<br />
return true;<br />
<br />
}<br />
catch (Exception Ex)<br />
{<br />
<br />
throw new Exception("SetStorageArea: Could not get the records of all Storage Areas.\nOriginal message: " + Ex.Message);<br />
}<br />
}<br />
.
awaiting for your valuable suggestions..
Thanks in advance
Sebastian
|
|
|
|
|
See this , i did this in vb.net
'save the datasource Changes
If mblnIsDirty Then
If MessageBox.Show("Do you want to Save the Changes?", "Property", _
MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
' Checking for Error in the dataset
Try
SqlDataAdapter1.Update(DataSet11, "Property")
Catch
MessageBox.Show("Error saving Data in the DataBase", "Property")
checkerrors()
End Try
End If
End If
<br />
<br />
The Check Errors will tell you where did you go wrong<br />
here is code for checkErrors<br />
<code><br />
Private Sub checkerrors()<br />
<br />
Dim table As Data.DataTable<br />
Dim row As Data.DataRow<br />
SqlDataAdapter1.Update(DataSet11, "Property")<br />
If DataSet11.HasErrors Then<br />
<br />
For Each table In DataSet11.Tables<br />
If table.HasErrors Then<br />
<br />
For Each row In table.Rows<br />
If row.HasErrors Then<br />
MessageBox.Show("Dataset has Errors")<br />
' Process error here.<br />
<br />
End If<br />
Next<br />
ElseIf table.HasErrors = False Then<br />
MessageBox.Show("Dataset has no Errors")<br />
End If<br />
Next<br />
End If<br />
End Sub<br />
<br />
And this is the insert
<br />
'save the New Record for an Add or Edit<br />
If mblnAdding Then<br />
Try<br />
Dim newRow As DataRow = DataSet11._PROPERTY.NewRow<br />
newRow("Num_key") = CStr(txtnumkey.Text)<br />
newRow("Extension") = CInt(txtextension.Text)<br />
newRow("Cell_ID") = CInt(txtcellid.Text)<br />
newRow("ACTUAL_EXTENT") = CInt(txtactualextent1.Text)<br />
newRow("Lis_key") = CStr(txtliskey.Text)<br />
newRow("Func_key") = CStr(txtfunckey.Text)<br />
newRow("PROP_CATEGORY_ID") = CInt(Prop_Category_ID)<br />
newRow("Geocode") = CStr(txtgeocode.Text)<br />
newRow("Rateable") = Rateable<br />
newRow("Non_Discreet_Valid") = Non_Discreet_Valid<br />
DataSet11._PROPERTY.Rows.Add(newRow)<br />
Catch exc As Exception<br />
MessageBox.Show("Unable to add the Record." & _<br />
ControlChars.NewLine & exc.Message, "Property")<br />
<br />
End Try<br />
mblnAdding = False<br />
lblRecordNumber.Text = "Record Added at the end of the Table"<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Hope this Helps
Vuyiswa
|
|
|
|
|
Hi,
have table configured like this:
CREATE TABLE `tblDane` (
`ID_DANE` bigint(20) NOT NULL,
`BAZA` varchar(50) default NULL,
`ID_ORG` varchar(50) default NULL,
`NAZWISKO` varchar(100) default NULL,
`TELEFON` varchar(50) default NULL
PRIMARY KEY (`ID_DANE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
my connections string looks like: "Server=192.168.1.18;Database=testtable;Uid=test;Pwd=test;charset=latin2;"
When retrive some data from table everything is ok - mean got polish chars like śćńł, but after update got only '??????' not a polish one
Any idea what can be wrong?
Greetings
|
|
|
|
|
|
Wow,
thank you  its works... but ther is a little trick...I had to delete and create new table - when just changed encoding to old one its not work - any ide why?
Greetings
|
|
|
|
|
Where did you change encoding to old one? In the mysql table or the connection string? In connection string it must be utf8 otherwise you will have ??? there
|
|
|
|
|
Changed charset in connection string and in table and column options. Its funny cause until i do not drop table and create new one its not work
|
|
|
|
|
In order to display unicode chars and manage them you need to have
1.a table in mysql database which can store unicode chars.
2.connection string which supports unicode so that you retrieve and/or insert unicode chars in the database
|
|
|
|
|
Hi,
I have some bulk data on Sybase. Is there anyway that I can export the data to Excel? If anybody knows, please help me.
Thanks
Meeram395
|
|
|
|
|
Hi,
I am a novice to SQL 2005 Reporting Services.
I want to integrate the SQL 2005 Reporting Services wtih MSVS.net 2005.
I would be very thankful if someone can please help as to which what version can I find the project type "Business Intelligence Project" in .net 2005.
Currently I have already installed MSSQL 2005 Developer Edition alongwith SQL Server Management Studio Express. However, I am not able to open Report Wizard in .Net 2005
Am I going somewhere wrong??
Am I missing something??
Please help me with any guidelines / link which instructs the installation / configuration procedure of MSSQL2005 Reporting Services and also its integration with .Net 2005 (Framework 2.0 / 3.0)
Thanking you in Advance
Regards
Pratik Shah
|
|
|
|
|
You need to install Business Intelligence Studio. It should be on your SQL Server 2005 dev disc.
Cheers,
Martin.
|
|
|
|
|
Hi,
All done perfectly.
Also, Can I have SQL 2000 and SQL2005RS together?????
Thanks
Thanking you in Advance
Kind Regards
Pratik Shah
|
|
|
|
|
I've never tried it, but in theory you should be able to.
|
|
|
|
|
I have two tables. With the following setup.
Name: Call Log
Columns: ID, Sales Rep, Date, Comments
Name: Static SalesReps
Columns: ID, Value
The Call Log table with have Sales Rep being the ID of the Sales Rep for the Static SalesRep table. What Select statement do I need to return all the data from the Call Log table, but have the Static Sales Rep Value column data substituted for the Sales Rep ID from the Call Log table.
If that doesn't make sense I can try to explain it better for anyone willing to help.
Thanks for any and all assitance!
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
|
|
|
|
|
Select b.Value, a.SalesRep, a.Date,a.Comments from call_log as a, Satic_SalesReps as b where a.id = b.id
This statements checks the condition a.id = b.id, if both ids match then the row in table a will be returned.Since we have selected b.value, it will return the value from table b.
Hope this helps u
Regards,
Priya
|
|
|
|
|
select c.ID, s.Value, c.Date, c.Comments<br />
from [Call Log] c<br />
inner join [Static SalesReps] s on s.ID = c.[Sales Rep]
---
single minded; short sighted; long gone;
|
|
|
|
|
Anyone know what data type I should use to store a long in sql 2005? The number will average being set at long max about 25% of the time, but will never go over.
Thanks for any help
Pablo
-- modified at 19:01 Wednesday 27th June, 2007
Never mind guys I guess a bigint would work fine. Thanks anyways.
Apparently it's not OK to start a bonfire of Microsoft products in the aisles of CompUSA even though the Linuxrulz web site says so
|
|
|
|
|
|
Thanks
Apparently it's not OK to start a bonfire of Microsoft products in the aisles of CompUSA even though the Linuxrulz web site says so
|
|
|
|
|
I am trying to create a search for a sql database i have been writing. i have managed to get the search to search "firstName". if i replace this with any of my other column names it also works fine, but i am trying to get it to search multiple columns at once.
i have tried searching on google but dont really know what to search for.
i thought it might have been an AND or an OR statement but couldnt get these working either.
this is my code;
Sub BindDataGrid1()
Dim CS As String
CS = _
"data source=(local)\SQLEXPRESS;" & _
"initial catalog=mus_search_test;"
Dim objCN As SqlConnection
objCN = New SqlConnection(CS)
Dim objDA As SqlDataAdapter
objDA = New SqlDataAdapter("Select * from Musician_Search", objCN)
Dim DS_SearchMusician_Search As DataSet
DS_SearchMusician_Search = New DataSet
objDA.Fill(DS_MS11, "Musician_Search")
Dim objDV As DataView
objDV = DS_MS11.Tables("Musician_Search").DefaultView
' sql statement for row filter
MySearch = "firstName LIKE '*" & txtSearch.Text & "*'"
objDV.RowFilter = MySearch
RecNum = objDV.Count.ToString
John Michael Kinsella
kinsellajohn@hotmail.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
