|
hello forum,
i want to store c# objects in a MS SQL database. I think the best way is to serialize the object to a byte array but i have problems to insert a c# byte array into the database...
First, the equivalent type of datum in MS SQL is the binary or varbinary type, isn´t it?
well, my code:
<br />
<br />
MyObject mo = new MyObject();<br />
MemoryStream ms= new MemoryStream();<br />
BinaryFormatter bf = new BinaryFormatter();<br />
b.Serialize(ms,mo);<br />
<br />
SqlConnection sc = new SqlConnection("server=localhost;uid=sa;pwd=;database=myDB");<br />
sc.Open();<br />
<br />
SqlDataAdapter sda = new SqlDataAdapter("select * from myTable",sc);<br />
DataSet ds = new DataSet();<br />
ad.Fill(ds,"tableFile");<br />
DataTable dt = ds.Tables["tableFile"];<br />
DataRow dr = dt.NewRow();<br />
<br />
dr["object"] = ms.GetBuffer();
dt.Rows.Add(dbRow);<br />
<br />
SqlCommandBuilder scb = new SqlCommandBuilder(sda);<br />
sda.Update(ds, "tableFile");<br />
<br />
The instruction:
dr["object"] = ms.GetBuffer();
throws the message exception "String or binary data would be truncated"
could you help me, please?
thanks in advance.
|
|
|
|
|
If the error says the string will be truncated, the size of the field you want to store it in, is to small.
|
|
|
|
|
ok. thank you. that was the problem! but ... another question:
if the maxium size of a varbinary is 8000, how could i store an object with a greater lenght (for example, a file)?
thank you. bye.
|
|
|
|
|
By using "blob" as the field's type:
http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer_20461.html
|
|
|
|
|
By using "blob" as the field's type:
http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer_20461.html
Even better, just save a reference to the file i.e. save it's path as a string in the database. If you have a file server to store your files on, just have your frontend app copy the images there and then save the path (//FILESERVER/SQLSERVERFILES/file1.jpg) in the database. Although SQL server has better performance serving up images (streamed), if a lot of people start using the app at once, the server might get stuck at just streaming files all the time. By just saving the reference, you place the task of getting the image at the client.
|
|
|
|
|
I create a frmCustomer that contains of txtCustID, txtCustFName,txtCustLName,txtCustDob and txtCustIC.... and the Add button, Search button and Exit button. When the user click on the Search button, the frmSearch will show. The user can search for the customer by select the alphabet in a combobox. The details of the customer whose last name start with that alphabet will display in the datagrid.
The datagrid in ths frmSearch contains of CustID, CustName, CustDoB. When the user double click on the specific row of the datagrid. The data in the selected row will bind to all the textbox in frmCustomers. Then, the user can do the edit or delete of the record by pressing the button in frmCustomers.
But,i am having a problem that it always get the original datasource instead of the filtered datasource when i double click on the selected row in the datagrid. Please help if you know how to solve this problem.
part of the coding is as follows:
Private Sub btnSearchName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchName.Click
With Me.DataSet11.Tables("Customers")
'carrier()
With DataGrid1
.CaptionText = "Search by Last Name"
End With
If cboLastName.Text = "ALL" Then
.DefaultView.RowFilter = "CustLName like '%'"
Else
.DefaultView.RowFilter = "CustLName like '" & cboLastName.Text & "%'"
End If
If .DefaultView.Count = 0 Then
MessageBox.Show("Data not found !.", "VideoMate", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Else
'MessageBox.Show("Number of data : " & .DefaultView.Count.ToString(), "VideoMate", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
If BindingContext(Me.DataSet11.Customers).Position < 0 Then
With DataGrid1
.CaptionText = "Database is empty !"
End With
End If
Me.DataGrid1.DataSource = .DefaultView
End With
End Sub
Private Sub DataGrid1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.DoubleClick
Dim CM1 As CurrencyManager = CType(Me.BindingContext(Me.DataGrid1.DataSource, Me.DataGrid1.DataMember), CurrencyManager)
If CM1.Position > -1 Then
Me._CM.Position = CM1.Position
Me.Close()
End If
End Sub
Thank you
|
|
|
|
|
It's the same output as
select *
from employee_table
-Thomas
|
|
|
|
|
First, it might be good idea to get a basic SQL book... The query you asked about will return all fields for the specified employee.
|
|
|
|
|
Actually I want to intstall multi instances of the dame database from the DTS package but with different name to each instance. How can this be ahieved for SQL server 2000?
Harmeet
|
|
|
|
|
I have a query that is as follows:
select * from contact c, contact_emails ce where ce.email = 'EMAIL' and c.password = 'PASSWORD' and ce.contact_id = c.id.
The query works but for what I need to do, I need the table names to be displayed in the header information.
Right now the header looks like this:
| id | first | last | password | id | email |
What I need is so that is looks like this:
| c.id | c.first | c.last | c.password | ce.id | ce.email |
I need to accomplish this by using the wildcard * (In other words the query cannot change).
Any ideas???
|
|
|
|
|
Can't be done, AFAIK.
If you can say a little more about:
1) what you are using the query for
2) what you are trying to accomplish
someone might be able to suggest a work-around.
Bill
|
|
|
|
|
I think the title says it all...oh except in VB.NET
Thanks in advance
David
|
|
|
|
|
Hi
I am a .net programmer writing database codes.
my qestion is:
Every time I do a project I should go to customer's office and install SQL Server on his computer , because my codes uses "sql server" as DBMS.
now...
CAN You help me to do something to avoid this?
for example a way to packaging sql server engine in my program install package?
regards
|
|
|
|
|
You can package MSDE, but I'll bet it's not legal to do it with SQL-Server as your clients needs licenses for it.
You can do it with MSDE, and just make it install in silent mode, using it's commandline switches.
- Anders
Money talks, but all mine ever says is "Goodbye!"
ShotKeeper, my Photo Album / Organizer Application[^]
My Photos[^]
New developersite: RealDevs.Net
|
|
|
|
|
Well, I'd still consider myself a newbie here. I was hoping someone would be
able to rewrite the following monster to be more efficient.
Thank You and Good Luck! 
CREATE PROCEDURE usp_Calls
@SexID char,
@RaceID char,
@LBirth datetime,
@UBirth datetime,
@SiteID varchar(5),
@DD datetime,
@CallActivity datetime
AS
declare @CAct table (CallStamp datetime, TesterID char(7), StudyNo char(15), QueStatusID varchar(2))
declare @CallFT table (CallFrom datetime, CallTo datetime, TesterID char(7))
insert @CAct
select distinct tResultQue.CallStamp, tResultQue.TesterID, tResultQue.StudyNo, tResultQue.QueStatusID
from tResultQue
where tResultQue.CallStamp >= @CallActivity
insert @CallFT
select distinct tTesterContactInfo.ContactBestFrom as CallFrom, tTesterContactInfo.ContactBestTo as CallTo, tTesterContactInfo.TesterID
from tTesterContactInfo
IF @SexID = '*' AND @RaceID = '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status,TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID) AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID = '*' AND @RaceID = '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID = '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID = '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status , TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID <> '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID <> '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID = '*' AND @RaceID <> '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID = '*' AND @RaceID <> '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
GO
|
|
|
|
|
This looks like some sort of dynamic search procedure. Stored procedures are a particularly poor fit for this sort of thing. It is far easier to write dynamic SQL on the client.
If you absolutely have to write it in a stored procedure, then you should consider using the exec command inside the stored proc. That will let you execute dynamically constructed SQL, which would be much simpler.
By this I mean that you can dynamically build the SQL string (based on the parameters), much as you could in a normal programming language.
my blog
|
|
|
|
|
Just my 2 cents....
Sql S 2000?
I would put this part into a table valued function:
For sex I would use and in (i.e WHERE ttester.sexid in(@sexid):
IF @sexid = '*' SET @sexid = 'm,f'
That would narrow it down a bit
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID
Then I would call the function:
SELECT Distinct dayspast,etc*
From dbo.myfunction(@sexid,@vars...etc) f
WHERE not exists (
SELECT tTester.TesterID (don't need distinct or group.)
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
and f.testerid = tTester.TesterID
)
AND
f.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = f.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = f.TesterID)
order by f.LastName
|
|
|
|
|
To All,
I have written a database application using MS Visual C++ 6.0 and MFC. I am using the CDao classes (and using the Jet 3.5 engine/MS Access 97).
The application seems to work very well and I have many installations of the application and satisfied customers.
Now I am switching to Visual Studio .NET. I’d like to continue to use MFC and write my code using Visual C++, however, I have read that DAO is becoming obsolete and when I compile using .NET I get the ‘deprecated’ warning whenever the compiler sees a reference to DAO.
So I need to switch to a new DB technology. I liked DAO because I could directly read and write to an Access database without having to register the DB as an ODBC.
Is there any recommendation on the correct technology to use? The database/application needs to allow for multiple users from multiple computers. It is a client/server type of application and must initially read and write to an Access database. The long term plan is to go to an SQL Server so this should be taken into consideration.
Thank You
|
|
|
|
|
KMerker wrote:
It is a client/server type of application and must initially read and write to an Access database. The long term plan is to go to an SQL Server so this should be taken into consideration.
Is there any particular reason why it must read/write to Access?
Given that you've been using Access as your database server until now and you want to move to SQL Server I'd suggest MSDE which uses the SQL Server engine, but is somewhat limited (no GUI admin tools, limited numbers of users and so on). You can also link it to MS Access if necessary, and you can use SQL Server's admin tools to connect to an MSDE database.
Does this help?
"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
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I highly recommend using ADO. It almost as simple as DAO and it offers greater flexibility.
Regarding the need for ODBC, I don't believe is it required for ADO to work. But i might confuse things with DSN. Regardless, you can use Microsoft Jet to connect directly to ADO.
Its the same technology as used by .NET or even VBScript/JScript on web servers. I've learnt to use it mostly with MSDN examples. The concept seems obscure at first, but its COM+ related.
|
|
|
|
|
hi everyone
my problem is that i can't update any field in the datalist ..i use the update event handeler ...when i try to update any field ...the field remains with its initial value...and when i debug the page...i noticed that the value of
((TextBox)cell[x].cotrol[0]).text is equal to ""..that it can't feel the value i insert...how can i solve this..
thx alot
|
|
|
|
|
1.can i create a table from stored proc?
2.can i insert/delete/update a table from sp?
3.wots that extended sp can do that ordinary sp cannot?
NOTE: I dont have access to SQL server of any type.
|
|
|
|
|
xcavin wrote:
NOTE: I dont have access to SQL server of any type.
Then what do you care?
1) Yes
2) Yes
3) Extended sp can access non-TSQL code and therefore system resourses other than the SQL Server itself.
|
|
|
|
|
Michael Potter wrote:
Then what do you care?
Learning SQL programing without access to SQL server.
Coz nothing can stop me.;)
|
|
|
|
|
xcavin wrote:
Learning SQL programing without access to SQL server.
Then how will you know you are learning correctly?
Why now download MSDE and use that? Or if you want to learn all the GUI tools also there is also a 120 day trial you can download also.
"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
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
