Re: What wrong it this script?? [modified] - Database Discussion Boards

Re: retrieving the value in a column using a dataTable

Frank Kerrigan31-Jul-06 22:27

Frank Kerrigan

31-Jul-06 22:27

This seems a very hard way to do a simple task.

Run following sql code to get a last membership number

<br />
select top 1 MembershipNumber<br />
from myclubDataSet<br />
ORDER BY MembershipNumber DESC<br />

This will no however stop two user from trying to create the same membership number after getting the same last number from the database.

What you really want to do is return the membership number after creation

Make 'MembershipNumber' and identity column on your database and use similar codeas below,

<br />
insert membername, memberdetails<br />
VALUES ('Joe Bloggs', '15 Anyplace Road')<br />
<br />
SELECT @@Indentity<br />
<br />

The select @@Indentity will get the newly created memebership number. Try it in SQL before you try and code it.

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: retrieving the value in a column using a dataTable

Glen Harvy1-Aug-06 0:39

Glen Harvy

1-Aug-06 0:39

Thanks for your asistance - much appreciated.

Frank Kerrigan wrote:
This seems a very hard way to do a simple task.

That seems to be the way I do everything nowadays Mad | :mad:

.

I have some database/SQL experience with designing websites that use databases and understand your suggestions pretty well BUT I need to achieve my aim without going back to the database. I really want to learn how to retrieve the value of a column in each row or specific row.

With a bit of help from elsewhere I've got as far as:-

DataTable table = myclubDataSet.Tables["MemberDetails"];
DataRow[] foundRows;
foundRows = table.Select("MembershipNumber > 1 ");

for (int i = 0; i < foundRows.Length; i++)
{
int num = System.Convert.ToInt32(foundRows["MembershipNumber"]);
MessageBox.Show("Membership number is: " + num);
}

But this leads to a build error message:

Error Cannot implicitly convert type 'string' to 'int'

with the culprit being MembershipNumber even though that column is defined as an Int32 in the datatable.

I hope you can see what I'm trying to learn to do and can just help me over this hurdle Smile | :)

Thanks,

Glen Harvy

Re: retrieving the value in a column using a dataTable

Frank Kerrigan2-Aug-06 10:09

Frank Kerrigan

2-Aug-06 10:09

OK read my article on ADO datagrids for getting stuff from to a db, you should be able to alter the code to meet your needs

http://www.codeproject.com/aspnet/EasyADODgrids.asp[^]

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: retrieving the value in a column using a dataTable

Glen Harvy2-Aug-06 15:29

Glen Harvy

2-Aug-06 15:29

After much, much searching and a bit of a hint here, a bit of false leading there and a lot of building I ended up achieving what I wanted as follows:

private void defaults()
{
this.memberDetailsTableAdapter.Fill(this.myclubDataSet.MemberDetails);

int totalRows = myclubDataSet.MemberDetails.Rows.Count;
short maxMemNum = 0;
short nextMemNum = 0;
for (int i = 0; i < totalRows; i++)
{
short MemNum = (short)myclubDataSet.Tables["MemberDetails"].Rows[i]["MembershipNumber"];
if (MemNum >= maxMemNum)
{
maxMemNum = MemNum;
nextMemNum = ++maxMemNum;
MemNum = nextMemNum;
}
}
this.editMembershipNumber.Text = nextMemNum.ToString();
}

Poke tongue | ;-P

Glen Harvy

What wrong it this script??

Skanless31-Jul-06 9:17

Skanless

31-Jul-06 9:17

Use Master

GO

RESTORE DATABASE Central Coast from disk = 'C:\centralcoast_db_200602020948.BAK'
with MOVE 'centralcoast_Data'
TO 'd:Z_centralcoast_Data.MDF',
MOVE 'centralcoast_Log' TO 'd:\Z_centralcoast_Log.LDF',

stats = 1

STOPAT = 'Feb 02, 2006 06:00 AM'

I'm trying to restore this Database up to a specific date and time but form some reason I geting an error saying "error missinf syntax after 'FROM' "

Greg

Coding makes the world go round!!!

Re: What wrong it this script??

Chris Meech31-Jul-06 9:46

Chris Meech

31-Jul-06 9:46

Just guessing that the space character between Central and Coast is causing the problem. Try

RESTORE DATABASE 'Central Coast' from disk = 'C:\centralcoast_db_200602020948.BAK'

or is the database called 'CentralCoast'?

Chris Meech

I am Canadian. [heard in a local bar]

Re: What wrong it this script??

Skanless31-Jul-06 10:07

Skanless

31-Jul-06 10:07

Yeah, the DB's name is CentralCoast.

Greg

Coding makes the world go round!!!

Re: What wrong it this script?? [modified]

Eric Dahlvang31-Jul-06 9:52

Eric Dahlvang

31-Jul-06 9:52

Try placing a comma after stats=1.

RESTORE DATABASE 'Central Coast' from disk = 'C:\centralcoast_db_200602020948.BAK'
with MOVE 'centralcoast_Data'
TO 'd:Z_centralcoast_Data.MDF',
MOVE 'centralcoast_Log' TO 'd:\Z_centralcoast_Log.LDF',
stats = 1,
STOPAT = 'Feb 02, 2006 06:00 AM'

[edit] and the space in dbname, like Chris said [/edit]
-- modified at 15:53 Monday 31st July, 2006

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Insert Question

PyroManiak31-Jul-06 8:47

PyroManiak

31-Jul-06 8:47

How do I Insert a copy of a record when I want to change ONLY one field..

INSERT INTO tb
SELECT field1, field2, field3, field4, field5
FROM tb AS tb_1
WHERE (field1 = @Value)

None of the selected items are the primary key, and this insert works fine for a perfect clone, but I need to be able to modify field1 with a textbox the user can type in.

I know there is VALUE keyword.. but I can't seem to get it to work for just field1.

Re: Insert Question

Ennis Ray Lynch, Jr.31-Jul-06 9:27

Ennis Ray Lynch, Jr.

31-Jul-06 9:27

INSERT INTO tb
SELECT @someValue, field2, field3, field4, field5
FROM tb AS tb_1
WHERE primaryKey = @primaryKey

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Insert Question

PyroManiak31-Jul-06 13:22

PyroManiak

31-Jul-06 13:22

Thank you.

ADO.NET OUTPUT Parameter

Carlos Paiva31-Jul-06 3:11

Carlos Paiva

31-Jul-06 3:11

Hi all

I'm moving an VB6 app to .NET and i'm faced with the following problem:

Having the following stored procedure in SQL SERVER 2000, that returns a dataset (Read only) AND has an OUTPUT parameter at same time, which of the command methods should i use, since it seems that ExecuteReader (Thats seems to be the only that returns a rowset) doesnt update the OUTPUT parameter.
Im I missing something?, Is there Any work Around?

'==============================================================================
ALTER PROCEDURE spGetResourceText (@frmName varchar(20),@MaxIndex int OUTPUT)
AS
SET NOCOUNT ON

SELECT @MaxIndex = Max(Id) FROM tblResourceText WHERE frmName = @frmName
SELECT * FROM tblResourceText WHERE frmName = @frmName

RETURN
'==============================================================================

Thanks in advance for your time!
Carlos Paiva

Re: ADO.NET OUTPUT Parameter

ToddHileHoffer31-Jul-06 7:35

ToddHileHoffer

31-Jul-06 7:35

I'm not sure why the output parameter has not been updated but there are many workarounds.

1. Use two stored procs and call them each.
2. Change select statements to
SELECT @MaxIndex = Max(Id) FROM tblResourceText WHERE frmName = @frmName
SELECT *, @MaxIndex as MaxIndex FROM tblResourceText WHERE frmName = @frmName

then the MaxIndex is returned in your dataset or datareader.

I could go on but I think you'll figure it out.

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Re: ADO.NET OUTPUT Parameter

Carlos Paiva1-Aug-06 4:29

Carlos Paiva

1-Aug-06 4:29

Hi ToddHileHoffer

Thanks for your answer.
I've already go for the work around, and i fix it, but stiil would like to know why doesnt the parameter get updated! For me it doesn't make any sense, since in several stored procedures, we need to return several variables and simultaniously a dataset. It dosn't make any sense to make sevaral calls to the server, when we can do it in a single shot! The idea of appending them to the returned dataset, is a good idea!
The biggest problem, is that i have several applications, that all use the same base system database, a few designed in VB6 and others that gonna be ported to .NET, and i'm trying to make transparent to the server application (view the same in VB6 and VB.NET).
This implies no changes to the stored procedures! So by now, i'm trying another aproach to retrieve some of the parameters, with a new sp that runs in the begining of Client Start.

Thanks for your time,
Carlos

complex SQL statement?

giddy_guitarist30-Jul-06 23:06

giddy_guitarist

30-Jul-06 23:06

hie ,

i'm developing a booking software for a hotel...... its going to have a date control and a flexgrid.The user puts in an entry... .. theres a STAYING FROM field and a STAYING TILL field.Which according to its names store the dates between which the customer will stay. Now , say theres a customer staying from the 1st of Aug. to the 10th.... if the user clicks any day BETWEEN 1st - 10th on the date control... the flex grid should show the status of the rooms.. on THAT DAY... so ..yep this customer's entry MUST SHOW up!!

So how should i compose my SQL statement!???? D'Oh! | :doh:

also i am new to this so - wats the difference between the flexgrid and the MSHflexgrid??

Thanx!
Gideon

Re: complex SQL statement?

Colin Angus Mackay30-Jul-06 23:36

Colin Angus Mackay

30-Jul-06 23:36

giddy_guitarist wrote:
So how should i compose my SQL statement!????

More detail about your data model would be useful. You have said nothing about how room information is stored, for example. Please give details of all tables where information is to appear in the output.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Delete Carriage Return

oskardiazdeleon30-Jul-06 21:13

oskardiazdeleon

30-Jul-06 21:13

How do I delete a carriage return () in SQL Server 2000? I am importing file from a UNIX box and for some reason I have to import it into Access to remove then re-import into SQL Server.

Is there a replace statement that will take care of this?

Re: Delete Carriage Return

Eric Dahlvang31-Jul-06 3:37

Eric Dahlvang

31-Jul-06 3:37

SQL Server Books Online

REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

Arguments
'string_expression1'

Is the string expression to be searched. string_expression1 can be of character or binary data.

'string_expression2'

Is the string expression to try to find. string_expression2 can be of character or binary data.

'string_expression3'

Is the replacement string expression string_expression3 can be of character or binary data.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Copy two xml doc in to a table in sql

kalyan_vb30-Jul-06 17:43

kalyan_vb

30-Jul-06 17:43

Hi,

I have 2 xml documents which contains the data, and I want to read the data from these two xml docs and copy to a table. How to do this, can any one post the syntax. I want to do this in sqlserver.

Regards,
Kalyan

Re: Copy two xml doc in to a table in sql

tanya foster2-Aug-06 10:52

tanya foster

2-Aug-06 10:52

Kalyan,
Try reading through

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=382583&SiteID=1

for how to parse through an xml document and read your elements and attributes. Where the example displays the elements/attributes to a console or listbox, you would have an sql insert statement.
Hope this helps.
tanya

Office Wob Components: PivotTable, SpreadSheet ...

hamidreza_buddy30-Jul-06 4:22

hamidreza_buddy

30-Jul-06 4:22

Hi
I placed a PivotTable in my form Application.
it is a AxOWC11.AxPivotTable.
it is easy to make it display a Table with its property Wizard but i want to make it Show every kind of Table.
i want to give data to it at RunTime programmaticaly.

it has a Property axPivotTable1.DataSource. but it is of type "msdatasrc.DataSource" and i don't know how should i assign data to it.
thanks

get the difference of two attributes in two tables

00thilani29-Jul-06 20:23

00thilani

29-Jul-06 20:23

I have a table called "Issue" and table called "Receive"
and both have same attributes

they are "code,curr_val,date,coin_note,no_of_items,amount,operator_id,officer_id"And also code ,curr_val, and date are superkeys.

data types are char(8),datetime,char(1)if t is a coin c,if it is a note n,money,char(8),char(8)

I'm using sql server 2000

I want to
get the difference of currency values (ex no of $10notes,$50 notes).In other words I want to get the difference of Items in the "no_of items"attribute.

I tried toget notes value and no of notes items in receive table.

>SELECT curr_val,count(*)AS Items ,SUM (amount)AS Amount_Rs FROM RECEIVE AS R WHERE coin_note='n'GrOUP BY curr_val

I tried toget notes values and no of notes items in issue table

>SELECT curr_val,count(*)AS Items ,SUM (amount)AS Amount_Rs FROM ISSUE AS I WHERE coin_note='n'GrOUP BY curr_val .

What I want is the difference betveen the items values of R and I with their currency value

please help ASAP

Thanx
Thilani

Re: get the difference of two attributes in two tables

Colin Angus Mackay29-Jul-06 22:53

Colin Angus Mackay

29-Jul-06 22:53

00thilani wrote:
both have same attributes

Attributes? Do you mean "columns"?

00thilani wrote:
superkeys

I am unfamiliar with that term. Do you mean "primary key"? Or perhaps "foreign key"?

Perhaps this will work:

SELECT 
    curr_val, 
    Items_Rs - Items_Is AS items_difference, 
    Amount_Rs - Amount_Is AS difference_amount
FROM (
      SELECT curr_val, count(*) AS Items_Rs, SUM(amount) AS Amount_Rs 
      FROM RECEIVE 
      WHERE coin_note='n'
      GROUP BY curr_val
     ) AS Received
INNER JOIN (
            SELECT curr_val, count(*) AS Items_Is, SUM(amount) AS Amount_Is 
            FROM ISSUE 
            WHERE coin_note='n'
            GROUP BY curr_val
           ) AS Issued
ON Issued.curr_val = Received.curr_val

Embeddable SQL database

Jörgen Sigvardsson29-Jul-06 12:38

Jörgen Sigvardsson

29-Jul-06 12:38

I'm looking for an embeddable SQL database. So far I've found SQLite[^], and it does look promising. Are there any other software out there with the same functionality I should be aware of? I like free. Smile | :)

--
-= Proudly Made on Earth =-

Re: Embeddable SQL database

S Douglas29-Jul-06 19:59

S Douglas

29-Jul-06 19:59

Jörgen Sigvardsson wrote:
Are there any other software out there with the same functionality I should be aware of

Depending on what you’re looking for there is always Firebird, There is even an article here on CP about Firebird.

Embedded Firebird: Full-Featured Embedded Database[^]
I asked the author about the differences between Firebird and SQLLite. Here is his answer.

Embedded Firebird: Question, Differences[^]

I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.