DataSet Update Problem

I know this is very very silly.
But I am stuck and don't know what to do.

I have a simple form which has 1 textbox and a 'Save' button.

I have created a Database connection and an Adapter with Dataset with
the help of Wizards.

I have binded by text box control to one of the database field.

When the form gets loaded it displays the field information correctly.
"Me.SqlDataAdapter1.Fill(DsBasicData1)"

However, when I press the save button it does not update the database
behind it.
I have given the following code to the click event of save button.
"Me.SqlDataAdapter1.Update(DsBasicData1)"

Strangely I don't know why the code does not work.patels (patels@.india.com) writes:
> I know this is very very silly.
> But I am stuck and don't know what to do.
> I have a simple form which has 1 textbox and a 'Save' button.
> I have created a Database connection and an Adapter with Dataset with
> the help of Wizards.
> I have binded by text box control to one of the database field.
> When the form gets loaded it displays the field information correctly.
> "Me.SqlDataAdapter1.Fill(DsBasicData1)"
> However, when I press the save button it does not update the database
> behind it.
> I have given the following code to the click event of save button.
> "Me.SqlDataAdapter1.Update(DsBasicData1)"
>
> Strangely I don't know why the code does not work.

And we don't know your database, or what's in that Update command.
Anyway, I think you should ask about this in a group devoted to
ADO .Net or Visual Studio .Net, as this is not really an
SQL Server issue per se.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Dataset query with alias column and allow searches

I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.
In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)

Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet

If InStr(Trim(searchClientNameText), "%")>0 Then
searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"
Else
searchStatement = "WHERE ClientName = @.searchClientNameText"
End If

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
searchStatement

........
End Function

(1) You would search on each of the columns that comprise the "ClientName".
searchValue = "%" & searchvalue & "%"

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
WHERE
([People].[Given_Name] IS NULL OR [People].[Given_Name] LIKE @.searchvalue)
OR ([People].[Family_Name] IS NULL OR [People].[Family_Name] LIKE @.searchvalue)
OR ( [Bus].[Organisation_Name] IS NULL OR [Bus].[Organisation_Name] LIKE @.searchvalue)
(2) You should be using Parameterized Query instead of hardcoding the values into the SQL Statement to prefent your sever from SQL Injection attack. Google for more info.

|||I don't see any reason why you should not go for a stored procedure for this type of situation. I would highly recommend that.
Thanks

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

Dataconversion: STRING (yyyymmdd) to DATE

Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.

I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?

Any hint for me is very

Thanks in Advance

ulrike

You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

setDATEFORMAT ymd

selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2

|||

You could use CONVERT function with 112 style:

Code Snippet

declare @.dt varchar(20)

set @.dt ='20070507'

selectconvert(datetime,@.dt,112)

|||

Hello again,

Thanks for your advise.

I tried to run the following code:

selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

What’s going wrong? Please give me some mor advise.

Thanks in advance,

ulrike

|||

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB

|||

ulrikeG wrote:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.

|||Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors

DataBinding to radio button

I have a windows form containing some text boxes and radio buttons bount to an SQL Server database, with code like the following:

this.tbBorrowerLastName.DataBindings.Add (new Binding("Text", dsData.Tables["Results"],"BorrowerLastName"();

this.rbMale.DataBindings.Add(new Binding("Checked", dsData.Tables["Results"],"Male"))

If I add a new row and try to move there using the code below, nothing happens (neither movement to a new position nor error message), unless I remove the radio button bindings. (The same thing occurs if I attempt to move to any record that has a null value in the database field for a radio button).

myLastRow = dsData.Tables["Results"].NewRow();

dsData.Tables["Results"].Rows.Add(myLastRow);

I attempt to move by changing the value of the position property of the BindingContext object.

How can I retain the radio button bindings and still be able to add and move to a new row?

Any help would be appreciated.

I was able to figure my own solution.

The problem was a null value for the checked property of the radio buttons. I tried adding a default value to Visual Studio and to the database, but neither of those solved the problem.

Inbetween the two lines of code in my earlier post (beginning myLastRow and dsData.Tables respectively), I added:

myLastRow["Male"]=true;

myLastRow["Female"]=false;

Problem solved.

Databases

Hi i am trying to connect two databases together in sql server 2000, how would i go about doing this, as i would like two tables form different databases reading information to carry outs statements

Cheers if anyone can help

Quote:

Originally Posted by Taftheman

Hi i am trying to connect two databases together in sql server 2000, how would i go about doing this, as i would like two tables form different databases reading information to carry outs statements

Cheers if anyone can help

I would imagine you would need to use a JOIN. Is this for a query, stored procedure or is a scripting language (ASP) used for web?|||

Quote:

Originally Posted by Taftheman

Hi i am trying to connect two databases together in sql server 2000, how would i go about doing this, as i would like two tables form different databases reading information to carry outs statements

Cheers if anyone can help

Ycan refer to the full name of the table using the database name to start:

From [databasename].[dbo].[tablename]

database: mssql

I am conducting a database design and I managed to reach the 3rd normal form. My question is, if i change my primary key, for example '0001' to '0002', is it possible to propagate the change/update to the other linked table.
if you require more info, please let me know. Thank you in advance.If I understand correctly then yes -
If you have 2 tables:
Customer - With Field CustomerId
And Order with Field OrderId and CustomerId
And you want to change the CustomerId Field then you would do the following:
SET IDENTITY_INSERT [dbo].[Customer] ON
GO
ALTER TABLE [dbo].[Order] NOCHECK CONSTRAINT ALL
GO
-- Add a column to hold the old ID
ALTER TABLE CUSTOMER WITH CHECK ADD OldID INT
GO
UPDATE CUSTOMER SET OldID = CustomerId
GO
-- Add Code to Change CustomerId Here
UPDATE O
Set O.CustomerId = C.CustomerId
From Order O
INNER JOIN Customer C
ON O.CustomerId = C.OldId
GO
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT ALL
GO
|||

You can use declarative referential integrity and cascade updates popagate the changes. Sample code below.

Regards

set nocount on
go

use tempdb
go

create table parent(p int constraint p_pk primary key)
go

create table child(
c int constraint c_pk primary key
, p int constraint c_fk foreign key references parent(p) on update cascade
)
go

insert into parent values (1)
go
insert into child values (1,1)
go

update parent set p = 2 where p = 1
go

select * from child
go

drop table child
go
drop table parent
go

|||Hi,
In addition to that. If your using sql2k you could just create a diagram and link the tables that you want in Enterprise Manager...
cheers,
Paul June A. Domag