Re: SQL 2005 CLR trigger and TCPClient - Database Discussion Boards

Re: Incorrect Time in DateTime column with MS Access database

Scott Page1-Mar-07 5:25

1-Mar-07 5:25

The DataSet and INSERT statement where both created by the designer using a DateTime type for the column in code. The database is set to for Date/Time datatype. Even when I have tried to update it manually using SQL and oledb commands, the resulting query returns the same... Correct date, 12:00:00 AM time. There is a workaround somewhere, I've used it a few years back, but not recently enough to remeber what it was.

In the mean time, I changed the field datatype in the database to Numeric - Double. In my code I add a column at runtime and convert the OADate (Double) to a .NET DateTime type and disply the DateTime.ToShortDateString in generated column, then map that to my ComboBox DisplayMember property.

It works, but shouldn't be that way.

Thanks for the help

"Some people spend an entire lifetime wondering if they made a difference. The Marines don't have that problem."
( President Ronald Reagan)

Re: Incorrect Time in DateTime column with MS Access database

Scott Page8-Mar-07 1:53

Scott Page

8-Mar-07 1:53

Problem solved.

Solution:
Change all DataAdapter DateTime columns Input and Update parameters OleDbType to DBTimeStamp. By default the designer sets any Date/Time column to DBDate which only formats the data to store the date.

This goes for DataAdapters created by the designer and for manually coded OleDbParameters.

"Some people spend an entire lifetime wondering if they made a difference. The Marines don't have that problem."
( President Ronald Reagan)

Getting a COUNT from one table and joining it to results from another.

Red_Wizard_Shot_The_Food28-Feb-07 6:13

Red_Wizard_Shot_The_Food

28-Feb-07 6:13

Say I have a table with Trucks on it

TRUCK_ID, DriverName
e.g:
211, John
112, Sarah
456, Philip

And another table showing when a parcel was loaded on and off.

Tuck_IDOFF, TRUCK_IDON

e.g

211, 112 (Loaded off of John and onto Sarah)
456, 211 (Loaded off of Philip and onto John)

Now I want to run a query that will return: Tuck_ID, DriverName, Total number of offloaded (COUNT of where TRUCK_ID = Tuck_IDOFF) total number loaded (COUNT of where TRUCK_ID = Tuck_IDON)

so it would return:

211, John, 1, 1
112, Sarah, 0, 1
456, Philip, 1, 0

I know how to return the data form table 1 just fine, and the counts from table two ....
It's joining the 2 results into one set of results that it causing me problems.

Cheers.

Re: Getting a COUNT from one table and joining it to results from another.

andyharman28-Feb-07 7:04

andyharman

28-Feb-07 7:04

select T.Truck_ID, T.DriverName,
  (select count(*) from TruckLoad where Truck_IDOFF = T.Truck_ID) OffloadedCount,
  (select count(*) from TruckLoad where Truck_IDON = T.Truck_ID) LoadedCount
from Truck T
order by T.Truck_ID

SQL 2005 CLR trigger and TCPClient [modified]

Nico Patitz28-Feb-07 4:43

Nico Patitz

28-Feb-07 4:43

Hi,

I´m trying ot get a CLR-Trigger running. I Created the trigger, its insert correctly in the database, but when the SQLTrigger tries to open a tcpclient Connection, I get a Security Exception:

"{System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Triggers.Trigger1()
The action that failed was:
Demand
The type of the first permission that failed was:
System.Net.DnsPermission"

How / Where can I give the Trigger the needed Permissions?

-- modified at 10:58 Wednesday 28th February, 2007

Re: SQL 2005 CLR trigger and TCPClient

Mark J. Miller28-Feb-07 10:26

Mark J. Miller

28-Feb-07 10:26

The permissions referred to are Code Access Security permissions. If you created your assembly with the default settings in Visual Studio then the assembly is set to SAFE. In order to access external resources you need to increase the assembly to EXTERNAL_ACCESS. If you are using Visual Studio this is done using the project properties. Otherwise, if you are scripting the creation of your assembly use WITH EXTERNAL_ACCESS instead of WITH SAFE (or nothing - default)

Re: SQL 2005 CLR trigger and TCPClient

Nico Patitz28-Feb-07 22:23

Nico Patitz

28-Feb-07 22:23

I´m using standard .Net assembly... the Exception occures when the trigger tries to execute the line
TcpClient client = new TcpClient("127.0.0.1",13000);

The TcpClient is Part of the System.dll assembly, and this assembly should be usaly registered correctly?

Re: SQL 2005 CLR trigger and TCPClient

Mark J. Miller1-Mar-07 5:43

Mark J. Miller

1-Mar-07 5:43

The system.dll assembly isn't "registered" with Sql Server, it's part of the hosted CLR. Certain classes w/in the CLR have attributes called Host Protection attributes which indicate the type of functionality the class and it's methods expose. SQL Server restricts access to some these classes based upon how your assembly is created. In order for your assembly to access (it will compile fine, but it won't run) these classes you must create (register) your assembly using WITH EXTERNAL_ACCESS instead of WITH SAFE.

Dispalying a blank instead of null.

minkinin28-Feb-07 1:34

minkinin

28-Feb-07 1:34

Hi all.
1.pls help,I want to display a blank column on the grid when my field is null in the DB instead of NULL,and when theres a value the grid should display that value.

2.I have a button on my interface and I need it to dissappear after 20sec,how can I do that,I tried to use a timer but it doesn't dissappear,
pls help with a code.

Thanks.

Re: Dispalying a blank instead of null.

kubben28-Feb-07 2:53

kubben

28-Feb-07 2:53

1. In your query you need to do something like isnull(fieldname,'') as fieldname
So if you query currently is:
select fieldname from table
It should be:
select isnull(fieldname,'') as fieldname from table

2. I am not sure why you would want a button to dissappear after 20 secs. That sounds a little weird. Anyway, it would depend of if this is a windows app or a web app. If it is a windows app, after you set the button to false in the timer you also probably need to call Application.DoEvents(). If you are in a we app it is a lot more difficult. I have some ideas, but I will assume for now you are talking about a windows app.

Hope that helps.
Ben

Re: Dispalying a blank instead of null.

minkinin28-Feb-07 19:35

minkinin

28-Feb-07 19:35

Thanks for your efforts,but it doesn't work the way I want it,maybe is because my question wasn't clear enough,
ok my data in the Db is like this:

Name TimeIN TimeOUT
me 08:15 Null

so I want it to be like this on a grid:

Name Time TimeOUT
Me 08:15

but when the TimeOUt field has a value in the DB it should display that field on the grid eg

Name Time TimeOUT
Me 8:15 4:30

I hope this clarify my question now.

Re: Dispalying a blank instead of null.

sam#28-Feb-07 20:27

sam#

28-Feb-07 20:27

Edit NullText property of your Data Grid Column give a space there.

Re: Dispalying a blank instead of null.

kubben1-Mar-07 1:18

kubben

1-Mar-07 1:18

I am suprised it doesn't work since I use the techique all over the place. So if your existing query is:
Select Name, TimeIN, TimeOUT from yourTable

All you should have to do is:
Select Name, TimeIN, IsNull(TimeOut,'') as 'TimeOut' from yourTable

If you are using Sql server. If you ask me it is really best to handle this sort of thing on the database side in what you are passing to .net then try to handle it in .net.

Ben

databound controls

Kunal P28-Feb-07 1:18

Kunal P

28-Feb-07 1:18

hi.

i am developing a web based application in Visual WeB Developer EXPRESS.

I have a Drop down list that which is databound to a particular table. Now i want that wen i select a particular field from the Drop Down List, a table of the same name shud be accessed, and a particular column be filled in the Listbox Control.

i hope i am clear..

Kunal Piyush

Re: databound controls

Sylvester george28-Feb-07 21:24

Sylvester george

28-Feb-07 21:24

Write a Stored procedure with argumnet (dropdownlist value)
call the stored procedure by passing the value
inseide the stored procedure write a select query with condition

bind the output of stored procedure with listbox

Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com

Score it if you like my post

Re: databound controls

Kunal P1-Mar-07 1:51

Kunal P

1-Mar-07 1:51

hey

never wrote a stored procedure.. all i do is simply specify the query within the code.. and get it executed..

so could you please elaborate on this.

sorryy..

Kunal Piyush

Re: databound controls

Kunal P1-Mar-07 3:33

Kunal P

1-Mar-07 3:33

hi
i wrote
this piece of code... and i am getting a error stating.. "Invalid Syntax near [valuename]"

string connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["diag"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);

try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "Select * from '" + DropDownList1.SelectedValue + "'";
SqlDataReader dread = cmd.ExecuteReader();

dread.Read();

while (dread.Read())
{
ListBox1.Items.Add(dread["TestName"].ToString());
}

conn.Close();

}
catch(Exception ex)
{
failed.Text = ex.Message;
}

Kunal Piyush

Re: databound controls

Sylvester george1-Mar-07 6:58

Sylvester george

1-Mar-07 6:58

cmd.CommandText = "Select * from [" + DropDownList1.SelectedValue + "]";

Regards,
Sylvester G
sylvester_g_m@yahoo.com

Re: databound controls

Kunal P1-Mar-07 19:23

Kunal P

1-Mar-07 19:23

sylvesterg wrote:
cmd.CommandText = "Select * from [" + DropDownList1.SelectedValue + "]";

i tried it.. now the error is not coming.. but still no retrival of rows to the listbox as well..

Kunal Piyush

String Replace in SQL Server 2000

Bulky Fellow28-Feb-07 1:18

Bulky Fellow

28-Feb-07 1:18

SQL Server 2000.

A table T has three columns A, B, C.

B is a char column of length 50, containing short phrases, like Thank MC22, MC22 are welcome,
A new mail for MC22 etc.

In this column, all MC22's should be substituted with the word 'you', so that phrases become
meaningful, like Thank you, You are welcome.

Note: whenever MC22 is encountered, EVEN IF IN THE MIDDLE OF A WORD, like abcmc22def,
it should be substituted to abcyoudef.

Everything is case insensitive.

Can someone please write the update statement for me?

Thanks in advance.

ASP - AJAX is SEXY. PERIOD.

Re: String Replace in SQL Server 2000

Sylvester george28-Feb-07 1:43

Sylvester george

28-Feb-07 1:43

select replace('ThankMC22','MC22','you' )

Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com

Score it if you like my post

Re: String Replace in SQL Server 2000

Amit Agarrwal28-Feb-07 1:53

Amit Agarrwal

28-Feb-07 1:53

Hello Sylvester
I want to know the username and password of SqlExpress.
So i continue my work Pls Help me and let me know
Its Urgent
Thanks and Regards
Aavesh

Dotnet

Re: String Replace in SQL Server 2000

Bulky Fellow28-Feb-07 1:56

Bulky Fellow

28-Feb-07 1:56

No, I don't think you got me.

I have many rows in that table. Here's the scenario: (the actual table)

<code>
Neuron A Neuron B Neuron C
-------- -------------------------------- --------

Passive Thank MC22 Learner
Active MC22 are welcome Learner
Pnode MC22 need a break Veteran
Bnode Hey MC22 Baby</code>

That's what my table is like. Now IN ALL ROWS, IN COLUMN B,
MC22 should be substituted by 'you'.

Thanks in advance.

ASP - AJAX is SEXY. PERIOD.

Re: String Replace in SQL Server 2000

Sylvester george28-Feb-07 2:01

Sylvester george

28-Feb-07 2:01

update TableName
set [Neuron B] = replace([Neuron B],'MC22', 'You')

Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com

Score it if you like my post

Re: String Replace in SQL Server 2000

mareers28-Feb-07 21:44

mareers

28-Feb-07 21:44

update table_name set column_name = replace(column_name, 'xxx', 'you');

sreeram

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