How to handle sqlcommand parameter that could be null?

I have this sql sqlcommand:

string sql = "INSERT Zones (ZName, TypeID, Description) VALUES (@dotnet.itags.org.ZName, @dotnet.itags.org.typeID, @dotnet.itags.org.description)";

command.Parameters.Add("@dotnet.itags.org.description", SqlDbType.NVarChar, 100).Value = description;

The column Description is nullable. But if the variable description is null, it seems the paramter @dotnet.itags.org.description can't be passed correctly.

How can I pass a null parameter to sql server?

Thanks!

You can send DBNull.Value as a value, that is it.

how to handle sql with selecting attributes from multiple tables

a sql like: "select t1.a, t2.b from t1, t2 ...",
how does ado.net handle the query result from this kind of sql?
thx.
hongyuyour question is unclear. but, for ado.net doesn't look any difference that
result is coming from one table or 10 tables as far as retrieveal is the
only aim.
say, it gives the same result as query analyzer/sql * plus gives, if you
write this query in that tools.
Rajesh Patel
"Jeff Lu" <hl_107@.yahoo.com> wrote in message
news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
> a sql like: "select t1.a, t2.b from t1, t2 ...",
> how does ado.net handle the query result from this kind of sql?
> thx.
> hongyu
>
One ResultSet, one DataTable
"Jeff Lu" <hl_107@.yahoo.com> wrote in message
news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
> a sql like: "select t1.a, t2.b from t1, t2 ...",
> how does ado.net handle the query result from this kind of sql?
> thx.
> hongyu
>
if i use DataReader to read the query results, how to get the meta data
of the columns from different tables?
thx.
Greg wrote:
> One ResultSet, one DataTable
>
> "Jeff Lu" <hl_107@.yahoo.com> wrote in message
> news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
>>a sql like: "select t1.a, t2.b from t1, t2 ...",
>>how does ado.net handle the query result from this kind of sql?
>>thx.
>>hongyu
>>
>
>
suppose there are 2 tables with 1-m relationship,
Order and Transaction(with orderId as the foreign key).
i want to get some Tx columns as well as some Order columns within
a single sql, which can be "select tx.attr1, tx.attr2, o.attr1 from
Transaction tx, Order o where tx.seq=? and tx.orderId=o.orderId".
with the query result from such a query, i can build a entity(let's
say a ReportingEntity) through some O-R mapping mechanism. The O-R
mapping layer needs meta data of the query result to build/bind
the entity. so, for each column/value of the DataReader, i
need to know its DB table name, DB column name and maybe its type.
DataReader.GetSchemaTable returns a DataTable, i didn't see a API
for getting the meta data across multiple tables mentioned above.
i guess i must have missed sth.
thx for your help.
William Ryan wrote:
> If you are using tables like that, you may want to consider using a
> DataRelation and pulling the tables individually, connecting them after
> you've grabbed them from the DB. If you are joining tables, you are pulling
> over redundant data that eats up bandwidth and depending on size, can really
> hinder performance. That's not to say that you should never join tables
> server side...but make sure that's really the way you want to do it. From
> your later post, you mention the meta data. I'm not sure what platform you
> are referring to, or what specific meta data you are referring to, but
> DataReader.GetSchemaTable will give you basic information about the fields
> pulled over.
> What specifically in the MD are you looking for? Let me know and I can
> probably be of more help.
> Bill
> "Jeff Lu" <hl_107@.yahoo.com> wrote in message
> news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
>>a sql like: "select t1.a, t2.b from t1, t2 ...",
>>how does ado.net handle the query result from this kind of sql?
>>thx.
>>hongyu
>>
>
>

How to handle postback on gridview without mixing records.

I have a griview to list a list of records, on each record, I have set status/delete button, they have commandname/commandargument, once they are clicked, record will be marked or deleted, those actions will not go to a different page.

The problem is when there is a new record added by others when one user click for example "set status" button, when it postbacks one more record is added on top of the list, and then the command will fire on the record above the record the user clicked on since the whole list shifted down since there is a new one added on top.

Any idea how to handle this?

thanks

looks like you disabled the view state. And also check for IsPostback before databinding the gridview.

like

Page_load()

{

if( ! isPostback )

{

GridView.DataBind();

}

}

thanks

right , I disabled viewstate.

If do like you said, how to refresh the gridvew, also, after user click the button, if they click F5, it seems it will fire the click again

oh, since I disable viewstate, I have to databind in every load, right?

but if you databind on every load you will end up in the same problem you mentioned. I would suggest enable view state and databind once you do update or delete functionality.

I see, you suggested me to turn on viewstate. That for sure is a solution.

Page_load()

{

if( ! isPostback )

{

GridView.DataBind();

}

}

Doesn't look like you read the other replies, how does that work without viewstate?

How to Handle Oracles XMLElement output in VS.NET 2005

Hi,

Oracle 9i, VS.NET 2005

I have a query that uses Oracle's XMLElement function. It returns XML. What I don't know, is how I use VS.NET to handle the outupt. I just want to take this output and write it to a file. Has anyone done this before? How is this accomplished?

I'm trying something like this but I've no idea if this is correct or not:

// connect to oracle

OracleConnection oConn =newOracleConnection("Data Source=databasename;Persist Security Info=True;User ID=userid;Password=pwd");OracleCommand oCommand = oConn.CreateCommand();

oCommand.CommandText =

"SELECT XMLElement( a huge multiline, multi join statement.....";

oCommand.CommandType =

CommandType.Text;

oConn.Open();

// retrieve the output.OracleDataReader oread = oCommand.ExecuteReader();

while (oread.Read())

{

Response.Write(oread.GetString(0) + "<br />" );

}

oread.Close();

oConn.Close();

The query runs in SQLPlus.

However, I'm slightly mystified as to what to use to handle the output from Oracle's XMLElement. Should I use an OracleDataReader?

Thanks in advance,

Paul

Hi,

I think you can return as a string to the c# code in the form XML string.Then use XMLDOM object loadXML(XML string) method so that you get dom object with xml data then if wanted to load it into dataset use DOM object's DOM.dataset() method.

It may help you out

How to handle online event registration

We have a database that keeps track of the people that attend our events. We want to add the ability for people to register online. I'm just looking for any thoughts or suggestions on how the database should/could be setup to handle this. We have a client table and an event fee table in our database now.

I thought I would revisit this post to see if anyone new is out there with any ideas they can share.

How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet

I am creating Excel Sheet using Microsoft.Jet.OLEDB.4.0 Create Table Query. It works well and I can transfer my data to Excel Sheet.

The problem is whenever I have some numeric or date data and it is null then it passes nothing in query so there is error of Insert Into.

Whenever I have stateid is null then it forms query like Insert Into City (CityID,City,StateID) Values (1,'Mumbai',).

How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet?

Excel is not true structured database. In a case if columns inside of the spreadsheet contain mixed data, provider will start to return and store NULL values if it cannot detect type of the cell. For example, assuming spreadsheet has column A1 where some values are strings and other one dates. First provider scans first N rows and detects datatype of the column. If it detects that column contains strings, then it will work with the dates as with NULLs and vice versa. The only thing you could do with Jet 4.0 is to force it to treat all the values as strings, if you add IMEX=1 to the Extended Properties of the connection string. But in this case you will lose data types completely. I faced this issue long time ago and I decided to go my own way of creating component for it. Another way is to use Office Tools from Microsoft, but they still COM based and require a lot of the resources during run-time

how to handle nulls in a RadioButtonList

My webform RadioButtonList is bound to a column from a Sql Server dataSource. The radioButtonList is in a formView which starts up in edit mode. My question is how to handle null values because the only way I could get the page to come up when the columnn value is null is to have an item in the radio list which is setup for nulls such as, "Unknown" in the Text property and -1 in the value property. I understand that the RadioButtonList control is supposed to default to nulls which means that nothing in the radio list is selected - this is the behavior that I want, but I get the error below complaining that null (-1) is not valid because it does not exist as a valid value in the radio list: I have tried -1, '', etc. with no success.

'RadioButtonList2' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

How should I handle nulls at page startup?

Thanks.

While setting SelectedValue = null will normally allow no default selection for the RadioButtonList control, trying to set it to null in binding (using either Eval or Bind) doesn't work and throws an exception. Going through a method, however, seems to work as follows:

[ASPX]

<ItemTemplate>

<asp:RadioButtonListID="RadioButtonList1"runat="server"SelectedValue='<%# myFunc(Eval("NumValue").ToString()) %>'>

<asp:ListItemValue="1"Text="Item 001"></asp:ListItem>

<asp:ListItemValue="2"Text="Item 002"></asp:ListItem>

<asp:ListItemValue="3"Text="Item 003"></asp:ListItem>

<asp:ListItemValue="4"Text="Item 004"></asp:ListItem>

<asp:ListItemValue="5"Text="Item 005"></asp:ListItem>

</asp:RadioButtonList>

</ItemTemplate>

[code-behind: C#]

publicstring myFunc(string val)

{

if (val =="")

returnnull;

else

return val;

}

Thank you, the myFunc worked great for handling nulls, But it caused another problem with the update statement. My update statement no longer works because it has lost the identity of the bound variable somehow.

The default SelectedValue works fine:

aspx:

SelectedValue='<%# Bind("myField") %>'

UpdateCommand="update myTable setmyField=@.myField

But with your custom SelectedValue code I get the error at the bottom:

SelectedValue='<%# myFunc(Eval("r1").ToString()) %>'

UpdateCommand="update myTable setmyField=@.myField

here is the error:

Must declare the variable'@.myField'.

[SqlException (0x80131904): Must declare the variable'@.myField'.]

The customization to SelectedValue caused this, any idea why?

Thanks.

I can see why this would be an issue when trying to update since it deviates from the binding expected by the framework. Interesting situation you have here. I'll have to look into this before giving you an answer. I wouldn't be surprised if someone already faced such situation and resolved it.
Thanks. I am appreciative of your help.
I tried to see if there's a workaround for this issue, but I'm afraid there isn't as far as I can see. I thought that there might be a way to bind a NULL value into SelectedValue property (which can be done if a direct null value is assigned), but when binding to a table column, it rejects it for some reason. This problem/behavior may be related to the fact that RadioButtonList isn't meant to provide an option for "no selection" (i.e. once a selection is made, you cannot go back to a no-selection state), although the starting point allows no selection state. Perhaps the DropDownList control serves your purpose better since you can have a default value that represents no selection. Another option might be to add another item for your RadioButtonList that represents no selection and default what would have been null value to that choice. Sorry I don't have a better news for you.

Here's another way to handle this. Create a user control with your RadioButtonList in it, and create a property in it called SelectedValue such as following:

public string SelectedValue
{
get
{
return RadioButtonList1.SelectedValue;
}
set
{
if (value != "")
RadioButtonList1.SelectedVAlue = value;
}
}

This way you conditionally avoid setting the RadioButtonList value when it's null. Then you can happily use the SelectedValue='<%# Bind("FieldName") %>' syntax in your user control tag declaration.