Re: Handling null values with SqlDataReader - Database Discussion Boards

ASPnoob1-Aug-07 10:44

1-Aug-07 10:44

Hi, how do I convert select statement and the connection variable in the following line into a stored procedure.

Dim AD as New OdbcDAtaAdapter("SELECT ResumeID, Resumes From Professions", myConnection)

Thank you in advance for your help.

-- modified at 17:35 Wednesday 1st August, 2007

Re: Convert to Stored Proc

Mike Dimmick1-Aug-07 11:42

Mike Dimmick

1-Aug-07 11:42

CREATE PROCEDURE procname ( parameters ) AS batch of statements

Stability. What an interesting concept. -- Chris Maunder

Handling null values with SqlDataReader

eggie51-Aug-07 7:24

eggie5

1-Aug-07 7:24

See the below C# snippet that's itterating over a SqlDataReader:

while (reader.Read())
           {
               c.ID = reader.GetInt32(0);
               c.Category.ID = reader.GetInt32(1);
               c.Subject = reader.GetString(2).Trim();
               c.Body = reader.GetString(3).Trim();
               c.SourceVideoClip = reader.GetString(4).Trim();
               c.StillImage = reader.GetString(5).Trim();
               c.Notes = reader.GetString(6).Trim();
               c.UploadTime = reader.GetDateTime(7);
               c.AdID = reader.GetInt32(8);
               c.ValidAfter = reader.GetDateTime(9);
               c.InvalidAfter = reader.GetDateTime(10);
               c.BillingCode = reader.GetInt32(11);
               c.Name= reader.GetString(12).Trim();
               c.Birth = reader.GetDateTime(13);
               c.Category.Name = reader.GetString(14);
           }

Everything runs fine until it gets to reader.GetString(4).Trim();, which is null, so it throws an SqlNullValueException exception.

How can I gracefully handle these null values. It seems GetString & GetInt just throw exceptions when they encounter null values. I guess the solution I'm looking for would return null or 0 for a db null value.

How am I supposed to deal with this. Do I have to put if statements around each GetXX to check if it's null?

/\ |_ E X E GG

Re: Handling null values with SqlDataReader

Pete O'Hanlon1-Aug-07 9:05

Pete O'Hanlon

1-Aug-07 9:05

I tend to use a set of wrappers to handle these. For instance:

public static string GetValue(IDataReader reader, string columnName)
{
   int pos = reader.GetOrdinal(columnName);
   if (reader.IsDBNull(pos))
      return string.Empty;
   return reader.GetString(columnName).Trim();
}

This would make your code easier to maintain because you could do:

c.Notes = Utility.GetValue(reader, "Notes");

Deja View - the feeling that you've seen this post before.

Re: Handling null values with SqlDataReader

Pete O'Hanlon1-Aug-07 9:31

Pete O'Hanlon

1-Aug-07 9:31

Another way to do this is to use a generic function:

public static T GetData<T>(IDataReader reader, string column)
{
  if (reader.IsDBNull(reader.GetOrdinal(column)))
    return default(T);
  return (T)reader[reader.GetOrdinal(column)];
}

Then, call it using

c.Category.Name = Utility.GetData<string>(reader, "categoryName");

Deja View - the feeling that you've seen this post before.

Re: Handling null values with SqlDataReader

eggie51-Aug-07 18:46

eggie5

1-Aug-07 18:46

I like this!

/\ |_ E X E GG

Re: Handling null values with SqlDataReader

Mike Dimmick1-Aug-07 11:48

Mike Dimmick

1-Aug-07 11:48

If you can't handle NULL values in the client code, don't let them get into the databases. Set the column's nullable property to false (if creating the table in SQL, use columnname datatype NOT NULL).

I tend to use a cast with the indexed property which returns an object, which will leave a reference variable (such as a string) set to null, but cause a NullReferenceException if casting to a value type like int.

In general I prefer not to allow NULLs in the columns, at least in part to ensure that you can detect an outer join that didn't have a match (useful for doing a multi-column NOT IN equivalent).

Stability. What an interesting concept. -- Chris Maunder

Re: Handling null values with SqlDataReader

Pete O'Hanlon2-Aug-07 10:02

Pete O'Hanlon

2-Aug-07 10:02

The issue isn't so much with the OP being unable to handle nulls in his code, but more to do with him looking for a simple way to handle them in the minimum amount of code.

Now, nulls definitely cause some contention - they almost seem to cause religious hysteria among developers. At their most basic, they are useful for identifying the absence of a value. Suppose that you wanted to capture if a person was married or not, it is valid to have three conditions; Yes, No and Unkown (or null). This could be because somebody didn't ask the question so you don't know whether or not they are. Oh well, that's enough rambling about nulls. It's time for me to go get a life again.

Deja View - the feeling that you've seen this post before.

sql 2005 not installing [modified]

costavo1-Aug-07 2:35

costavo

1-Aug-07 2:35

hi all,
i am trying to install sql server 2005 standard edition but an error message is still coming on which says:
SQL server cannot install files to the compressed or encrepted folder: c:\\..............

...........

does any one has an idea
10x

costavo

-- modified at 8:49 Wednesday 1st August, 2007

for sure i am an Admin
i dont know which and what folder is encrepted. i copied the installation files from the dvd to the hard disk
10x agian

Re: sql 2005 not installing

originSH1-Aug-07 2:47

originSH

1-Aug-07 2:47

Is the folder compressed or encrypted? Are you running as administrator?

SQL 2000 & 2005

Saranya B1-Aug-07 1:39

Saranya B

1-Aug-07 1:39

in my system, SQL Server 2000 and SQL Server 2005 are there.
But I couldnt connect to SQL Server 2000.
Does anybody know why and how can i resolve this??

Thanks,
Saran

Re: SQL 2000 & 2005

kubben1-Aug-07 2:13

kubben

1-Aug-07 2:13

If you have both sql 2000 and sql 2005 loaded on the same server one of them probably has an instances name. So if you sql server name is sqlserver1, only one of the sql servers can use that name the other one has to be an instance name. So I am guessing you know the name to one of the serves, but you need to figure out the name to the other one.

Hope that helps.
Ben

auto run a query in ms sqlserver

asdf17531-Aug-07 1:19

asdf1753

1-Aug-07 1:19

hi is there any one help me to run a query in MS Sqlserver periodical for example delete some data every 24hour that be independent from asp or php and etc...
i want that MS Sqlserver run it itself.

thanks

Re: auto run a query in ms sqlserver

Pete O'Hanlon1-Aug-07 2:21

Pete O'Hanlon

1-Aug-07 2:21

Sql Server has the ability to schedule tasks inside and run them itself. If you are using SQL Server 2000, you will need to expand the Management node under your database, then expand SQL Server Agent and select Jobs. Right click and select New Job...

In the General tab enter a name. Select the Steps tab and New... to create a new task. Suppose that you want to run a particular delete (delete from myTab where @created > dateadd(dd, getdate(), 2)). Give the step a name and leave the type as Transact-SQL Script (TSQL). Choose the database that you want to run the task against and enter the SQL in the Command textbox. Click OK.

Next, go to the Schedules tab and create a new schedule. To change the times when it runs, select the Change button and set your schedule as appropriate.

Deja View - the feeling that you've seen this post before.

Benefits of Access 2007

Jason Stamp31-Jul-07 23:23

Jason Stamp

31-Jul-07 23:23

We have been using an Access 2000 mdb database with a VB.NET Windows application. We have recently ported from .NET 1.1 to .NET 2.0 and also upgraded to Office 2007.

Due to some of the users of our software, we have used Access for data storage instead of SQL Server, most of our clients did not want the server and liked having lots of smaller Access databases which they can look at outside of our software.

We are now looking at the option of either changing our database to an Access 2007 accdb format or even using SQL Server express which can be used on a users own machine or their server.

I have been looking on the Internet to find some stats on the performance benefits of either of these routes, particluarly to have a justified argument to change. Does anyone know what the performance benefits of an accdb database over an mdb database in terms of concurrent users, number of records that can be stored and general speed at higher volumes?

Also it would be good to know what else we gain over and above an accdb database using SQL Server express.

Any information or links to some stats would be much appreciated.

Jason

Re: Benefits of Access 2007

Aaron VanWieren1-Aug-07 4:24

Aaron VanWieren

1-Aug-07 4:24

I don't know about the stats, but a great plus is that you have access to using Sql Server Management objects for working with database level requirements, such as creating tables and databases. Another plus that might not be useful immediately is the ability to create CLR stored procedures that can be debugged in VS2005 and use more common language functions.

We were using Access and ran into issues with performance and updatability of data within the database. We have since moved to Sql Express and I can tell you it is great being able to work with it instead of access.

Hope this helps at least a little...
Aaron

_____________________________________________________________________

Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.

The Enlightenment Project (paraphrased comment)

Visit Me at GISDevCafe

Creating Stored Proc in Text Editor [modified]

ASPnoob31-Jul-07 21:17

ASPnoob

31-Jul-07 21:17

Hi, Is it possible to create a stored procedure using a text editor like note pad? I have three more questions for you.

 If I can create it with a text editor, what extension do I give it and what folder must I put it in to make it work? <br />
<br />
If I can create it with notepad and I make changes to it, will SQL Server give error messages, or must I create a new stored proc? <br />
<br />
Is the syntax for MySQL be compatible with SQL?

The reason I ask is because if I can create a stored proc with a text editor, then it would really be convenient. If you know of a good tutorial on this please point me to it, thank you in advance for your help.

-- modified at 3:50 Wednesday 1st August, 2007

Re: Creating Stored Proc in Text Editor

originSH31-Jul-07 22:10

originSH

31-Jul-07 22:10

ASPnoob wrote:
If I can create it with a text editor, what extension do I give it and what folder must I put it in to make it work?

Stored Procedures are stored internally inside SQL Server.

You should use the Sql Server Management Studio for 2005 or the Enterprise Manager for 2000.

You can create the SQL code in a text file and save it as a .sql file then import it into whichever sql management app you use but you might as well use the editor provided in the management app as it'll have more features than notepad :P.

ASPnoob wrote:
Is the syntax for MySQL be compatible with SQL?

I assume you mean Sql Server ... SQL is a language, Sql Server is a database engine.

And kinda, if you use just the most basic of the Sql Syntax then it'll be ok but each database engine has it's own extentions. Sql Server uses T-SQL and MySql uses something I don't know about :P

Nested Select statement

Prakash_Mishra31-Jul-07 20:34

Prakash_Mishra

31-Jul-07 20:34

Hi all

I have an issue with select statement. Like in one form i have 5 fields all combo box and after selecting all we have search option so in a data grid i am displaying the records which satisfies all criteria in the combo box. It works fine.

But now i want is if user select only one combo box and press search the grid should show the related records which satisfies the combo box condition. I have tried with AND OR condition in query but not getting the proper records.

Pl help

Regards

Prakash Mishra(Banglore,India)

Re: Nested Select statement

kubben1-Aug-07 2:18

kubben

1-Aug-07 2:18

There are several ways to solve this problem, I will suggest one of them here. If you have five parameters you need to set up your where clause to allow each of the parameters to be optional. All of this is done in the where clause. So the code would look like this if a parameter is being passed in then you need to set it to a default value. I will assume that all your parameters are strings.

Where (param1 = '' or param1 = @param1) and (param2 = '' or param2 = @param2) and (param3 = '' or param3 = @param3) and (param4 = '' or param4 = @param4) and (param5 = '' or param5 = @param5)

So the idea is if the user only selects one combo box, just set the rest of the parameters to '' and your query should work.

Hope that helps.
Ben

Re: Nested Select statement

Prakash_Mishra1-Aug-07 3:05

Prakash_Mishra

1-Aug-07 3:05

Thanks Ben i solved the problem of my own at last from morning i was trying and didn't got any reply. I am new to sql,

Regards

Prakash Mishra(Banglore,India)

Re: Nested Select statement

Prakash_Mishra1-Aug-07 3:02

Prakash_Mishra

1-Aug-07 3:02

I was waiting for reply from 5 long hours but didn't got a single one at last i solved the problem of my own. for for others reference i am posting the solution

select * from table name (must condition and 1st condition)or (must condition and 2nd condition)or (must condition and 3rd condition)....

Prakash Mishra(Banglore,India)

Syntax error converting character string to smalldatetime data type...,

Member 387988131-Jul-07 20:00

Member 3879881

31-Jul-07 20:00

Hi when i parse this query i am getting sucesfull command...,
When i execute the query getting error like "Syntax error converting character string to smalldatetime data type"...,

Thsi is my procedure:

declare
@mthinc as tinyint,
@years as smallint,
@mth as varchar(50),
@yr as varchar(50)

set @mthinc=6
set @mth='@mthinc'
set @years=2007
set @yr='@years'

begin

select empid,max(edate) as edate from tblsalarydetails
where edate < @mth + '/01/' + @yr group by empid

end

I think to assign the tinyint & smallint value to varchar variable place i am getting error...,
How to solve that...,
Plz hlp me...,
Advance thanks,

Regards,
Magi

Re: Syntax error converting character string to smalldatetime data type..., [modified]

Pete O'Hanlon1-Aug-07 0:45

Pete O'Hanlon

1-Aug-07 0:45

Hmm - this seems to be a little bit overcomplicated. Drop the varchar fields and change your where condition to

where edate < cast(@mthinc as varchar) + '/01/' + cast(@years as varchar)

I really should point out here that you would be better off explicitly converting the condition into a smalldatetime to give the query optimizer a better chance of using an index on the column.

-- modified at 14:44 Wednesday 1st August, 2007

Deja View - the feeling that you've seen this post before.

Help with update query

student_rhr31-Jul-07 19:36

student_rhr

31-Jul-07 19:36

I have Table called Customers with CustomerId as Primay key, FirstName and LastName. I also have a table called CustomerUpdates.

CustomerUpdates table has a foreign key of CustomerID. It also contains FirstName and LastName columns.

How can I write a query to update the FirstName and LastName in Customers table with
the FirstName and LastName in CustomerUpdates?

Please help.

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