adding sql parameter in VB

I have a details view with several parameters set up in myasp.net 2.0 code, I want to add a parameter before the sql parameter is executed. I need to use the find control of the details view because I am using items/edit item templates in my details view control.
I tried this(see below) as well as the detailsview item command event args to no avail. It doesn't see the other parameters that have already been declared in my asp.net code. I don't want to have to declare all my varibles that are already in my asp.net code. I just want to add another parameter.

Sub InsertNew(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _
Handles dvEvents.ItemInserting

Dim dvr As DetailsViewRow

For Each dvr In dvEvents.Rows

Dim CatIDup As Integer = CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue
sdsevents.InsertParameters.Add("evCatID", CatIDup)
sdsevents.Insert()

Handle the sdsevents.Inserting event.

e.Command.Parameters.Add("@.evCatID",SqlDbType.Int).Value={something}

Also, in your prior code, it's confusing, because you are iterating a set of rows in dvr, and dvr hasn't been set. Even if it was, why would you want to add a parameter for each row?

|||

I tried what you suggested and i get sqldbtype is not declared. see below

As far as iiterating through the rows, I could not find a better way to do that if i did not iterate the rows i would get the error

about my dvr not being set if i did the for each i did not get that error.
I am happy to hear a better suggestion of how to do this.
Thanks

Sub InsertNew(ByVal senderAsObject,ByVal eAs SqlDataSourceCommandEventArgs) _

Handles sdsevents.Inserting

Dim dvrAs DetailsViewRow

ForEach dvrIn dvEvents.Rows

' Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue

e.Command.Parameters.Add("@.evCatID",SqlDbType.Int).Value={CatIDup}

Next

EndSub

|||Sub InsertNew(ByVal senderAsObject,ByVal eAs SqlDataSourceCommandEventArgs) _

Handles sdsevents.Inserting

Dim dvAs DetailsView=detailsview1

' Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dv.FindControl("ddlCat"), DropDownList).SelectedValue

e.Command.Parameters.Add("@.evCatID",System.Data.SqlDbType.Int).Value=CatIDup

EndSub

That assumes your detailsview is called detailsview1.

You can also do it from the detailsview events, but you need to reference the parameter collection that is passed to you. In the ItemInserting event, it would be e.Values. In the ItemUpdating event, it would be e.NewValues. Normally I would declaritively define the parameter (In design view... To assign the type, nullability, default value, etc etc). Then I would either set the value in e.Values/e.NewValues or e.Command.Parameters("@.evCatID").Value= to set the value of the already existing parameter.

|||

I got it I did it this way

thanks for your help!!

PublicSub InsertNew(ByVal senderAsObject,ByVal eAs DetailsViewInsertEventArgs) _

Handles dvEvents.ItemInserting

Dim dvAs DetailsView = dvEvents

' 'Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dv.FindControl("ddlCat"), DropDownList).SelectedValue

sdsevents.InsertParameters.Add("evCatID", CatIDup)

EndSub

Adding data to more than one table

Hi there,

I am currently setting up a registration system where customers can registers their details and the details of the product, using ASP.net and MS SQL.

There is a column called customerID in the Custoemrs table, and a column of the same name in the Products table, so that I can have relationships between the tables.

For obvious reasons (ie. people that quit half-way through), I want to hold all the information until the end. The ID in the Customer table is unique, and auto-increasing, and therefore not assigned until the data enters the database.

However, I wish to submit information to the Products table at the same time, but what shall I put in for the custoemrID (which hasn't yet been assigned)

Thank you in advance for your help,

Nathair

It's a Referential Integrity issue. You need a ForeignKey with Cascading on UPDATE/DELETE to maintain the Referential Integrity on UPDATE/DELETE: take CustomerID column on Customer table as PrimaryKey, and CustomerID on Products table as ForeignKey, you can refer to this link:

http://msdn2.microsoft.com/en-us/library/ms177463.aspx

To maintain the Referential Integrity when INSERT, you can create an INSRET Trigger on the Customer table as following:

create trigger trg_Customer on Customer for insert
as
insert into Products select CustomerId,'myProduct' from inserted
go

You can take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

|||Thanks for that. UPDATE/DELETE isn't really gonna be an issue.

Normally I hard-core my SQL statement into the actual page - however, this time I'm thinking of using an SP in MS SQL. For both ways, I am unsure on how to incorporate the trigger?

Thanks,
Nathair

Adding an image to details view

I'm some what of a noob when it comes to ASP.NET 2.0 and SQL Server 2005 Express editions, so please bare with me.

I have created a basic database that is only one table. One of the fields is LogPicture with type of image.

I just want to go into the table and manually place a picture in the table, but it seems like I can not get the format down. I have tried including the picture I want into the project, but no dice. I've also tried putting the physical pathname (on the harddrive) and the web address of this field in there. Suggestions please?

*bump*

I am basically just looking for the syntax of how an image is inserted. Anyone..... please?|||

Image data is stored as binary stream in SQL Server. You can either use bcp.exe utility to insert BLOB data (image/text/ntext) or write your own code to input binary stream to the column. You can take a look at this post:

http://forums.asp.net/thread/1257487.aspx

And here is a article that introduces bcp utility:

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Adding a web reference to a Report Server project

Hi there,

My company has a centralised user profile repository that all new applications worldwide use for authorization and user details; we access it from the various apps via a web service.

What I'd like to do in a report is to pass a user's ID to the web service and return their name and contact details to display in the header. I imagine I'd need to do this in a custom function and I can see where I can add a reference to use in this function but not where to add a web reference. Is this possible?

Regards,

Uzum4k1i.

One possible solution to the issue is to add custom code to the report: custom code (e.g., a C# library project in which a web reference could be added) calls the web service and exposes the web service call result for the report to reference. the assembly containing custom code needs to be deployed on the report server. More details are available here:

http://msdn2.microsoft.com/en-us/library/ms252130.aspx

Another possible option is to configure the web service as an XML data source in which the web service URL is the connection string. Details here:

http://msdn2.microsoft.com/en-us/library/ms159741.aspx

http://msdn2.microsoft.com/en-us/library/ms345338.aspx

|||Cheers Tau Liang, both of those answers sound like they'll fit the bill nicely

Adding a web reference to a Report Server project

Hi there,

My company has a centralised user profile repository that all new applications worldwide use for authorization and user details; we access it from the various apps via a web service.

What I'd like to do in a report is to pass a user's ID to the web service and return their name and contact details to display in the header. I imagine I'd need to do this in a custom function and I can see where I can add a reference to use in this function but not where to add a web reference. Is this possible?

Regards,

Uzum4k1i.

One possible solution to the issue is to add custom code to the report: custom code (e.g., a C# library project in which a web reference could be added) calls the web service and exposes the web service call result for the report to reference. the assembly containing custom code needs to be deployed on the report server. More details are available here:

http://msdn2.microsoft.com/en-us/library/ms252130.aspx

Another possible option is to configure the web service as an XML data source in which the web service URL is the connection string. Details here:

http://msdn2.microsoft.com/en-us/library/ms159741.aspx

http://msdn2.microsoft.com/en-us/library/ms345338.aspx

|||Cheers Tau Liang, both of those answers sound like they'll fit the bill nicely