Datasets and iff statements

Hello experts,

I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...

Code Snippet

=iif(Left(Fields!TestName.Value,4)="VTAM","VTAM Logon",

iif(Left(Fields!TestName.Value,4)="CICS","VTAM Logon",

iif(Left(Fields!TestName.Value,4)="Shaw","SHAW Main Menu",

iif(Left(Fields!TestName.Value,7)="Inquiry","Inquiry Menu",

iif(Left(Fields!TestName.Value,6)="Search","Search Menu",

iif(Left(Fields!TestName.Value,6)="SEAX A","SEAX Auxiliary Search Menu",

iif(Left(Fields!TestName.Value,8)="SEAX - B","SEAX",

iif(Left(Fields!TestName.Value,4)="STLN","STLN","Other Value"))))))))

Not sure where I put the dataset name in this.

Thanks,

C

Answered myself. Choose the data set in the matrix properties. Leave the expression alone.

dataset and identity of new record inserted

Hi,

I have 2 tables in my databasePrescriptionHeader and PrescriptionDetails.

My PrescriptionHeader table has the following fields:

PrescriptionID -identity field

PatientID

PatientfName

Patientlname

PrescriptionDetails table has the following fields:

PrescriptionDetailID -identity

PrescriptionID -from PrescriptionHeader table

MedicineDosage

The functionInsertPrescription inserts values into the tablePrescriptionHeader. I want the same function to then insert the value ofMedicineDosage intoPrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?

Thanks

Function InsertPrescription(ByVal PatientIDAsString, _

ByVal PatientFnameAsString, _

ByVal PatientlnameAsString, ByValMedicineDosage as String)

Dim DBAdapterAs SqlDataAdapter

Dim DBDataSetAs DataSet

Dim SQLStringAsString

Dim DBCommandBuilderAs SqlCommandBuilder

SQLString ="SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"

DBAdapter =New SqlDataAdapter(SQLString, DBConnection)

DBDataSet =New DataSet

DBAdapter.Fill(DBDataSet)

Dim AddedRowAs DataRow = DBDataSet.Tables(0).NewRow()

AddedRow("PatientID") = PatientID

AddedRow("PatientfName") = PatientFname

AddedRow("Patientlname") = Patientlname

DBDataSet.Tables(0).Rows.Add(AddedRow)

DBCommandBuilder =New SqlCommandBuilder(DBAdapter)

DBAdapter.Update(DBDataSet)

EndFunction

The following article is very helpful in your case

Inserting relational data using DataSet and DataAdapter

HTH
Regards

DataGridView/SQL Express - Updating record using seperate form

Hi,

I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:

Level 0 Level 1 Level 2

1:N 1:N
Furniture --> Component --> Component

You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.

I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.

My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.

What would be the proper way to do it? I would certainly appreciate to hear you view on this.

Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?

Thanks in advance,

Stphane

Hi Stephane,

It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.

Mike

|||

Hi Mike,

I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.

I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.

Any hint or useful links?

Thanks again for your help,

Stphane

|||

Hi Stephane,

First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.

That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.

I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.

Mike

|||

Hi Mike,

Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!

I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.

Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:

MSDN Forums Windows Forms Windows Forms Data Controls and Databinding

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Hope I will be able to get any hint from there!

Thanks for your help,

Stphane

|||

Hi Stephanie,

You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.

VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.

There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.

Mike

|||

Hi again Mike,

Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...

Stphane

DataGridView/SQL Express - Updating record using seperate form

Hi,

I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:

Level 0 Level 1 Level 2

1:N 1:N
Furniture --> Component --> Component

You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.

I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.

My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.

What would be the proper way to do it? I would certainly appreciate to hear you view on this.

Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?

Thanks in advance,

Stphane

Hi Stephane,

It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.

Mike

|||

Hi Mike,

I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.

I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.

Any hint or useful links?

Thanks again for your help,

Stphane

|||

Hi Stephane,

First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.

That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.

I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.

Mike

|||

Hi Mike,

Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!

I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.

Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:

MSDN Forums Windows Forms Windows Forms Data Controls and Databinding

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Hope I will be able to get any hint from there!

Thanks for your help,

Stphane

|||

Hi Stephanie,

You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.

VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.

There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.

Mike

|||

Hi again Mike,

Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...

Stphane

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

DatabaseName is blank in Trace

I am using SQL 2000.
I am taking trace and execute an sp_trace_setevent for all
the events I want to record. One of the fields I want is
the DatabaseName, so I execute:
exec sp_trace_setevent @.TraceID, 10, 35,1
When I view the trace, I get all the column names (server,
DBID, DatabaseName, etc.). There is data in all the
columns except the DatabaseName field is blank.
I know I can work around this by doing a join with the
DBID and Sysdatabases, but I find it strange that the
DatabaseName field is blank.
Has anyone else experienced this? Anyone have any ideas?
Thanks.It's not unusual - the names for DatabaseName (as well as
ObjectName) can't always be obtained as they aren't
available for every event. Use DatabaseID instead.
-Sue
On Wed, 1 Oct 2003 08:17:47 -0700, "dschrier"
<deena_schrier@.viarail.ca> wrote:
>I am using SQL 2000.
>I am taking trace and execute an sp_trace_setevent for all
>the events I want to record. One of the fields I want is
>the DatabaseName, so I execute:
>exec sp_trace_setevent @.TraceID, 10, 35,1
>When I view the trace, I get all the column names (server,
>DBID, DatabaseName, etc.). There is data in all the
>columns except the DatabaseName field is blank.
>I know I can work around this by doing a join with the
>DBID and Sysdatabases, but I find it strange that the
>DatabaseName field is blank.
>Has anyone else experienced this? Anyone have any ideas?
>Thanks.
>
>
>

Database update issues

I have a simple, one table, database to hold an internal telephonedirectory. All is working well, except for the update. If Iedit a record, and for example, enter a value in a field that is blank,and then update, nothing happens! However, if i alter data thatwas already in that field and update, the changes take effect!!

Any help with this would be gratefully received,

Thanks in advance,

Vern.

I have been able to do this in Visual Web Developer 2005 Express. I just make a gridview or detailview and when going through the wizard for configuring the source for my SELECT I check *, then I click "Advanced" and select the Generate UPDATE, INSERT, DELETE ( i dont remember exactly what the name of it is, but its the only radio button you can select under advanced as long as the SELECT is set as *. After that just click next through the wizard, test your query if you want and click finish. Run your site and it should work.

I hope this helps, again I did this all with Visual Web Developer 2005 Express and didn't need to write any code, I am not sure how you are approaching this but if you want to use this was very easy, probably took literally 45 seconds to get working.

Good luck,

Brian

|||

Thanks for the reply Brian.

However, that exactlyhow I set it up. The strange thing is that I have now set thedefault value for a field to '-'. This means there is alwayssomething ing the field to start with. The field will then updatewhen the - is deleted and new data entered. What still is notworking, is that if you delete the data and then update, leaving thefield empty, you cannot then edit that field in the same way!

Database Trigger on Update Table

Dear All,
I want to create a trigger on a table (A) which can copy the records
into new table(B) whenever it updates or any new record inserted into
the table (A).
Kindly create this trigger for me.
TIACREATE TRIGGER A_IU
ON A
FOR INSERT, UPDATE
AS
INSERT B
SELECT *
FROM Inserted
GO
On 28 Jun 2006 04:54:08 -0700, "Atif Iqbal" <aatif.iqbal@.gmail.com>
wrote:

>Dear All,
>I want to create a trigger on a table (A) which can copy the records
>into new table(B) whenever it updates or any new record inserted into
>the table (A).
>Kindly create this trigger for me.
>TIA|||Hi
Marco
create trigger tr_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0 return
insert MyAuditTable
select i.ID, d.MyColumn, i.MyColumn from inserted i join deleted d on
d.ID = o.Id
"Atif Iqbal" <aatif.iqbal@.gmail.com> wrote in message
news:1151495648.866045.307900@.75g2000cwc.googlegroups.com...
> Dear All,
> I want to create a trigger on a table (A) which can copy the records
> into new table(B) whenever it updates or any new record inserted into
> the table (A).
> Kindly create this trigger for me.
> TIA
>