Re: SQL and VB.NET identity insert problem. - Database Discussion Boards

First Prev Next

Re: Database Object.

Pdh098211-Jul-07 8:42

Pdh0982

11-Jul-07 8:42

I am sorry but now I would try to be more clear.

I have a webpage where the results are shown in more than one page(which is called paging in CF). Now, what I want to do is to put all the results in one page. so, basically no paging.

I hope this helps.
Thanks very much

Re: Database Object.

Harini N K11-Jul-07 19:01

Harini N K

11-Jul-07 19:01

Hi

Are you using ASP.Net(C#.Net or VB.Net)? Are you using any .Net server control to display data on the webpage?

Paging can be done by setting the attribute AllowPaging to TRUE in your .Net Server control. Check your code / HTML page of your web page and change it to FALSE.

Hope this helps.

Harini

SQL script [modified]

kibromg11-Jul-07 6:05

kibromg

11-Jul-07 6:05

Hi All,

I have a single table that contains the following Columns

As you can see i generated the data base on ASc on id's

Select * from Call_register order by id

Table Call_Register
id Call(in Hour) Seconds
76 00:04:23 263
76 00:05:58 358
76 00:06:38 398
77 00:06:22 382
77 00:01:29 89
77 00:10:28 628
77 00:01:18 78
78 00:03:35 215
79 00:07:26 446

I want to regroup them as follows : want to also keep track of number of time id occurs:Like

id occurance call(in Hour) totalSeconds
76 3 times 00:04:23 +00:05:58+00:0638(= 263+358+398=
77 4 times 382+89+628+78=1019seconds

Could you please help in getting me the script that enables me to generate in this format

Id number of times id appeard(frequency) : sum(totalHour) sum(seconds)
76 3 times
77 4 times
78

etc..

Please advice
Thanks

-- modified at 12:33 Wednesday 11th July, 2007

Re: SQL script

RepliCrux11-Jul-07 19:42

RepliCrux

11-Jul-07 19:42

Just in case you are still struggling:

select
id,
cast(count(id) as varchar(10)) + ' times' as 'Frequency',
sum(call) as 'TotalCALL', --this was int in my case
sum(seconds) as 'TotalSeconds' --this was int in my case

from Table
group by id

Cheers

SQL and VB.NET identity insert problem.

aqzman_11-Jul-07 5:45

aqzman_

11-Jul-07 5:45

Hi guys, I hope this is the right section, but as it's more of a SQL problem, it most likly is.

Anyway, my problem is that I get this error:

"DEFAULT or NULL are not allowed as explicit identity values."

Everytime I try to run my code, but of all the searhing I've done on Google and Google code search I really can't find anything amiss. I'll post my source (What I think is needed) at the bottem. If there are any important lines of code that I've left out ask and I'll promptly post them.

Dim mySelectQuery As String = "SELECT * FROM tblCallers SET IDENTITY_INSERT tblCallers ON"
Dim myInsertQuery As String = "INSERT INTO tblCallers (CallerID, Name, Address, PostalCode, City, PhoneNumber, EmailAddress) VALUES(DEFAULT, '" & Name & "', '" & Address & "', '" & PostalCode & "', '" & City & "', '" & PhoneNum & "', '" & EmailAddress & "')"
Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery, myConnection)
Dim myCommand2 As New Data.SqlClient.SqlCommand(myInsertQuery, myConnection)
Dim retvalue As Integer
myConnection.Open()
retvalue = myCommand2.ExecuteNonQuery()
Console.WriteLine(retvalue)
Dim myReader As Data.SqlClient.SqlDataReader
myReader = myCommand.ExecuteReader()
myReader.Close()
myConnection.Close()

I know that the code works because I use took it from another program I wrote and added the "SET IDENTITY_INSERT tblCallers ON" line. Also, the CallerID field is an Identity field.

Thanks,
aqzman

Re: SQL and VB.NET identity insert problem.

Colin Angus Mackay11-Jul-07 5:51

Colin Angus Mackay

11-Jul-07 5:51

aqzman_ wrote:
I know that the code works because I use took it from another program

Uh-huh...

aqzman_ wrote:
Dim myInsertQuery As String = "INSERT INTO tblCallers (CallerID, Name, Address, PostalCode, City, PhoneNumber, EmailAddress) VALUES(DEFAULT, '" & Name & "', '" & Address & "', '" & PostalCode & "', '" & City & "', '" & PhoneNum & "', '" & EmailAddress & "')"

You may wish to read about SQL Injection Attacks and how to prevent them[^] becuase your code is vulnerable right now.

My suggestion, don't include the column that will automatically receive the identity in the list of columns you are inserting.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

Ready to Give up - Your help will be much appreciated.

My website

Re: SQL and VB.NET identity insert problem.

aqzman_11-Jul-07 6:13

aqzman_

11-Jul-07 6:13

Thanks a lot for the reply!

I know my code might be a bit shakey, but I only started learning SQL, ASP.NET 2.0, and Visual Basic .NET last week. So sorry for any newbie mistakes, heh.

Thanks for the advice about SQL Injection, I've only ever heard of it. Never read about it myself. This program will only be going up on a in-office intranet though, so I'm not too worried about attackers. But I'll probably fix it up once I read the links you posted. Never too early to start learning good programming habits.

As for not including the column which will recieve the identity, that did the trick! Thanks a lot for your help!

aqzman

Re: SQL and VB.NET identity insert problem.

Colin Angus Mackay11-Jul-07 6:51

Colin Angus Mackay

11-Jul-07 6:51

aqzman_ wrote:
This program will only be going up on a in-office intranet though, so I'm not too worried about attackers.

Statistically, most attacks are from people within the organisation.

Re: SQL and VB.NET identity insert problem.

sidbaruah12-Jul-07 19:40

sidbaruah

12-Jul-07 19:40

true very true!!! Smile | :)

I was born dumb!!
Laugh | :laugh:

Programming made me laugh Laugh | :laugh:

!!!
--sid--

Re: SQL and VB.NET identity insert problem.

Giorgi Dalakishvili11-Jul-07 5:54

Giorgi Dalakishvili

11-Jul-07 5:54

If CallerID is auto-increment column then you don't need to specify it's value

#region signature
my articles
#endregion

Bulk Insert

Ashwiniyer11-Jul-07 3:28

Ashwiniyer

11-Jul-07 3:28

I need to know how one can insert a file(excel etc.) into the database using a query.
If some code to work around with will be much appreciated.

Thanks in advance.

Ashwin.

Re: Bulk Insert

andyharman11-Jul-07 6:14

andyharman

11-Jul-07 6:14

What type of database are you using (SQL-Server, MS-Access, etc)?

Re: Bulk Insert

Ashwiniyer11-Jul-07 6:30

Ashwiniyer

11-Jul-07 6:30

SQL Server 2000

Ashwin.

Re: Bulk Insert

andyharman11-Jul-07 11:33

andyharman

11-Jul-07 11:33

http://support.microsoft.com/kb/321686[^]

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Question .(Need immidiate reply Please)

Karuna Kumar11-Jul-07 2:30

Karuna Kumar

11-Jul-07 2:30

This is specifically for SQLServer 2005 Reporting Services.

1) How can i add a new folder in Reporting Services Solution(.sln)
2) How to add one more folder under 'Reports' Folder in Solution project
3)How to refer an .rdl file in one project(Proj1) from other project(Proj2)in same solution.
means i want to use the report(.rdl file) designed in one project folder in other reports in other project folders in same solution.

Karuna

Re: Question .(Need immidiate reply Please)

Colin Angus Mackay11-Jul-07 2:52

Colin Angus Mackay

11-Jul-07 2:52

Karuna Kumar wrote:
Need immidiate reply

If you need an immediate reply then I suggest you open a support ticket with Microsoft. They charge money, but you get an immediate reply. Here you will find willing people who want to help, but they do so on their own timetable because they don't get paid for it.

So, where are your priorities: Do you want it fast (in which case you have to pay) or do you want it free (in which case waiting times can be lengthy)

Re: Question .(Need immidiate reply Please)

Paul Conrad11-Jul-07 7:08

Paul Conrad

11-Jul-07 7:08

Colin Angus Mackay wrote:
So, where are your priorities: Do you want it fast (in which case you have to pay) or do you want it free (in which case waiting times can be lengthy)

Good point.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: Question .(Need immidiate reply Please)

Vasudevan Deepak Kumar11-Jul-07 3:56

Vasudevan Deepak Kumar

11-Jul-07 3:56

Karuna Kumar wrote:
immidiate

Typo

Karuna Kumar wrote:
Need immidiate reply Please

http://www.microsoft.com/services/microsoftservices/default.mspx[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Convert string in DB to DateTime in .NET

blackjack215011-Jul-07 1:58

blackjack2150

11-Jul-07 1:58

Hi.

In a few words, I have to convert a string retrieved from an Access database to a DateTime object.

I thought I'll use the Convert.ToDateTime() method. But there's the risk that the conversion will fail because the format will not be recognized. I think that this method uses the DateTime format taken from Contol Panel - Language Settings, so this would be tricky if the settings are different on another machine.

What would you do in this case?

Thanks.

Re: Convert string in DB to DateTime in .NET

kubben11-Jul-07 2:54

kubben

11-Jul-07 2:54

There is a DateTime.TryParse and a DataTime.TryParseExact in .net 2.0 These should help you converting strings to datetimes in .net.

Ben

Re: Convert string in DB to DateTime in .NET

Karuna Kumar11-Jul-07 2:56

Karuna Kumar

11-Jul-07 2:56

May it works for u check.

DateTime.ParseExact();

eg:
DateTime.ParseExact("01/05/2003", "dd/MM/yyyy", new CultureInfo("en-GB"));

agian convert it to string using Tostring()
Means provide culture specific format

Karuna

Re: Convert string in DB to DateTime in .NET

Pete O'Hanlon11-Jul-07 10:14