|
greekius@hotmail.com wrote: ;User ID=SA;
Your connecting to a database using SA? 
That is VERY unsecure! Highly unrecommended.
Connection strings http://www.connectionstrings.com/[^]
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
hello every one,
well I'm working on making a master detail form here. I've binded the data correctly and now I want to add the update, del, and insert new record functionality to the detail form. I'm using .net 2.0 C# and I'm working in windows Forms. I've used DataGridView class to display the detail data information.
For saving the record, I wanted to cast the datasource of DataGridView into a datatable first and then use it to iterate thru the data but it returns a null so I get the nullreferenceException. Plz tell me how to iterate thru the detail table here.
I actually deleted a few columns from the detail table ;like the PK columns and Foreign Key columns coz the user doesnt need to see those. But I do wanna have access to those esp when updatin the record. So what can I do.
if its possible plz give me a sample code or application if u can.
thanks in advance...
Rocky
|
|
|
|
|
Hi all,
just got to find an easier way, can any tell me if you can update the entire database with a new time in the timestamp field. i've got this project but the data is 14month old, it would be good if the data could be 3 or 4 days old just to debug or test a few things.
Anyway, ive been using this:-
select dateadd(d,430,timestamp)from alarm_log
this brings back the original table plus 430 days. So how do i update this data back into the table, cause i have nothing in the table to generate a whereclause.
secondly, once ive got one table updated... i could use sysobjects to find all the tables in the database and then loop through them...but how! - im sure i read somewhere that sql doesn't store a row number, there is no such thing, but surely there must be a way to interate through a table list?
Cheers Andy
|
|
|
|
|
1. If you are updating ever row in the table you don't need a where clause
<br />
update alarm_log set timestamp = dateadd(d,430,timestamp) <br />
2. Use a cursor. See Google or help in Query Analyzer for more info.
Mike Lasseter
|
|
|
|
|
It is a strange practice to name your field timestamp and then fill them with a datetime or smalldatetime data type. I'm assuming that your timestamp field is in fact a datetime or smalldatetime, because the timestamp data type is binary - and would not update without error using the dateadd as you show. Plus, the timestamp data type is updated automaticaly when you change the value of a field.
Anyway, if your timestamp fields are in fact dates, this should work:
DECLARE @cSQL varchar(100)
DECLARE @cTableName varchar(100)
SELECT @cSQL = ''
DECLARE TableNames CURSOR FOR
SELECT DISTINCT table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'timestamp'
OPEN TableNames
FETCH NEXT FROM TableNames INTO @cTableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('UPDATE ' + @cTableName + ' SET timestamp = DATEADD(d,430,timestamp)')
FETCH NEXT FROM TableNames INTO @cTableName
END
CLOSE TableNames
DEALLOCATE TableNames
--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
|
|
|
|
|
Hi All
Very new to SQL and ASP.Net but need to populate a lable from a SQL query.
<br />
Dim sqlDbAdapter1 As New Data.SqlClient.SqlDataAdapter("SELECT COUNT(*) AS myCount FROM dbo.IssueTb ", sqlconn)<br />
sqlDbAdapter.Fill(sqlDataset, "IssueTb")<br />
Dim Issuedv As DataView = sqlDataset.Tables("IssueTb").DefaultView<br />
The above loads a Dataset and DataView with the value but I don't know to bind to the label on the web page.
Databinding used on vb.net was my first try but this was wrong so could someone please help.
Thanks Heaps...
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
Because you are only returning one column in one row you would be better off without the Data Adapter
Dim cmd AS New SqlCommand()
cmd.CommandText = "SELECT COUNT(*) FROM dbo.IssueTb"
cmd.Connection = sqlConn
Dim result As Int32
result = cmd.ExecuteScalar()
...
SomeLabel.Text = result.ToString()
|
|
|
|
|
Thanks Heaps
This worked out great...
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
Hi,
I have the followig table:
Name Data
---- ----
Jim 3
Jim 4
Joe 5
Ann 7
Joe 9
How can I print the names and the number of times they show up? something like
Name Counter
---- -------
Jim 2
Joe 2
Ann 1
second question, how can i print the following?
Name Data
---- ----
Jim 3,4
Joe 5,7
Ann 9
thanx
-- modified at 23:49 Thursday 24th August, 2006
|
|
|
|
|
kozu wrote: How can I print the names and the number of times they show up?
SELECT Name, COUNT(*) AS Counter
FROM MyTable
GROUP BY Name
kozu wrote: second question, how can i print the following?
Printing results of query is something the calling application should be doing.
|
|
|
|
|
Colin answered your first question. Here is a solution for the second:
Make a User Defined Function:
CREATE FUNCTION GetData (@cName varchar(100))
RETURNS varchar(100) AS
BEGIN
DECLARE @nData int
DECLARE @cDataHold varchar(100)
SELECT @cDataHold = ''
DECLARE DataCursor CURSOR FOR
SELECT Data
FROM TestTable
WHERE Name = @cName
OPEN DataCursor
FETCH NEXT FROM DataCursor INTO @nData
WHILE @@FETCH_STATUS = 0
BEGIN
select @cDataHold = @cDataHold + case when len(@cDataHold) > 0 then ', ' else '' end + CAST(@nData as varchar(100))
FETCH NEXT FROM DataCursor INTO @nData
END
CLOSE DataCursor
DEALLOCATE DataCursor
RETURN(@cDataHold)
END
Call it like this:
select name, dbo.getdata(name) as Data from (select distinct name from TestTable) as tbl
--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
|
|
|
|
|
|
I am writing an application that acesses a SQL server, logging on with SQL server authentication and would like to set the menu options acording to the permission the logon user or group has, is there any way of interigating the SQL server for user permissions.
Any help would be appreciated and many thanks in advance
-- modified at 18:00 Thursday 24th August, 2006
|
|
|
|
|
perhaps
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
I have a column that is a bit. I am looking to Select a row where this column has a null value. I have tried SELECT * From Table Where x = NULL...that doesn't work. When I look at the table through the manager, I see <null> as the value.
How do I program this?
Thank You,
Jude
|
|
|
|
|
TheJudeDude wrote: I have tried SELECT * From Table Where x = NULL...that doesn't work.
SELECT * FROM Table WHERE x IS NULL
|
|
|
|
|
|
Hi,
Select * from TABLE Where X Is Null
Pandian S
|
|
|
|
|
Greetings:
I am just cutting my teeth on the business of programming databases using C# and .NET. It doesn't really matter what language because I suspect that the questions I have apply no matter what language is used.
I have found that it is easy enough to use data adaptors or SQL command objects to insert a row into a database table, delete a row or change an existing one. But I have yet to figure out how one creates a table where there was not one before. I find that to create a data adapter, I need to specify the database and the select command I intend to use BEFORE I create the adapter. But what if the table does not yet exist? What if the purpose of the procedure in question is to check for existence of a certain table and create it if its not there?
Could somebody point me to a working example of this being done, if, in fact, it can be done...
Thank you,
Mark
|
|
|
|
|
Jethro63 wrote: But what if the table does not yet exist? What if the purpose of the procedure in question is to check for existence of a certain table and create it if its not there?
You can put just about any SQL commands you like into a SqlCommand. So if you can write it in Query Analyser you can pretty much shove it in a SqlCommand too. The exception is "GO" which is a preprocessor symbol used by Query Analyser to tell it where to split the statements into separate batches. In .NET, the equivalent is to split the SQL up into separate SqlCommands.
So, to check if a table exists:
IF EXISTS (SELECT * FROM INFORMATION_SCHEME.TABLES WHERE TABLE_NAME = 'MyTableName')
BEGIN
-- Do stuff based on the existance of the table
END
ELSE
BEGIN
-- Do stuff based on the nonexistance of the table
END
|
|
|
|
|
Hi Colin. Thanks for your help.
I'm using SQL Desktop server at the moment, so it is difficult for me to try out SQL commands. In an effort to have some sort of proving ground for SQL commands, I use MS-Access and build queries in SQL mode. I realize that there are some differences between the two SQL implementations, but I am not a particularly advanced user and I don't often expect to run into the subleties too often.
Anyway, under Access, I was able to create a table using:
CREATE TABLE StationData;
This worked fine. I'll worry about creating the columns later.
I then attempted to use an SqlCommand object to execute the same statement against my SQL database under C#. I've run into problems here. I am getting SQL exceptions.
First of all, it is not clear to me if I can indeed execute a "CREATE TABLE" SQL statement using SqlCommand. Can I? Which method should I use? "ExecuteNonQuery", "ExecuteScalar"? None of these work.
So, at this point, I don't know if it is simply, my SQL syntax, or I am miss-using SqlCommand object, or if I simply cannot do this with an SqlCommand object.
Can you shed some light? Your help is greatly appreciated.
Cheers,
Mark
|
|
|
|
|
Jethro63 wrote: First of all, it is not clear to me if I can indeed execute a "CREATE TABLE" SQL statement using SqlCommand. Can I?
Yes, you can use any SQL in a SqlCommand.
Jethro63 wrote: Which method should I use? "ExecuteNonQuery", "ExecuteScalar"?
If you don't expect a result set back (as in the case of creating a table) then use ExecuteNonQuery
Jethro63 wrote: So, at this point, I don't know if it is simply, my SQL syntax, or I am miss-using SqlCommand object, or if I simply cannot do this with an SqlCommand object.
Your SQL Syntax is incorrect. You cannot create an empty table, it must have at least one column. You should get a SqlException thrown, it will tell you the error message.
|
|
|
|
|
Thanks Colin. It worked.
The SQL statement that worked was:
CREATE TABLE StationData (Name CHAR(50), Province CHAR(20));
I have one remaining question, if you don't mind:
Since "ExecuteNonQuery" does not return anything, how can I detect if the database already contains the table? I have tried it and it throws an exception and the exception text says that the table already exists. So, is the accepted approach just to catch the exception and make sure that the exception number corresponds to the "Table already exists" error code"?
Thanks again!
Mark
|
|
|
|
|
Jethro63 wrote: how can I detect if the database already contains the table?
You can do it all in SQL if you want:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='MyTableName')
BEGIN
-- Put table creation code here
END
If you want your .NET application to know about it then something like this:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MyTableName'
And use ExecuteScalar(). A result of zero means the table doesn't exist. Any other number indicates the number of tables with that names (you have to add an AND SCHEMA_NAME='NameOfSchema' (e.g. 'dbo') if you have multiple schemas.)
|
|
|
|
|
Hi Colin:
I have set up the following SqlCommand object:
<br />
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = StationData<br />
Visual Studio automatically altered the text of this command to read:
<br />
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = [StationData]<br />
When I execute this using ExecuteScaler, an exception is thrown. The exception text reads: "Invalid column name 'StationData'".
This is puzzling, as the SQL command is clearly referring to a table, not a column.
Mark
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
