|
Hi
at the moment, I'm trying to delete all records in a table.
(1) What i do is create a dataAdapter that drags out all records and fill it into a dataset.
(2) go through each record in the dataset, and mark it as Delete
(3) Update the Database using Update from the Data Adapter.
This method seems rather slow and inefficient.
I was wondering is there another way of deleting all rows in a table without having to populate a dataset?
thanks
|
|
|
|
|
Try to use this:
Dim Conn As New SqlConnection<br />
Dim cmd As New SqlCommand<br />
Conn.ConnectionString = "Data Source=Server; Database = DB ; Integrated Security=SSPI"<br />
Conn.Open()<br />
cmd.Connection = Conn<br />
cmd.CommandType = CommandType.Text<br />
cmd.CommandText = "Delete From TableName"<br />
cmd.ExecuteNonQuery()
Regards
Javed
|
|
|
|
|
thanks a lot
your help is appreciated.
|
|
|
|
|
Hi
Say i have two tables in Access
Order and Order_Items where Order is the parent and Order_Item is the child and it is linked by the columns order_ID.
order_ID in the Order table is the primary key and done by autonumbering whilst order_ID in the Order_Items is a foreign key.
my problem is this. i have both tables and schemas put onto a dataset, where i create new rows, but i do not assign an order_ID for any of the rows i create because Access would autonumber them when i call an Update.
The problem is that how do i link Order_Items record with Order record in the dataset? would i have to update the database every time i create an Order record in order to get assigned an order_id before i can then create child items in the Order_Item table that link to this order_ID or is there some sort of feature in ADO.NET that allow you to link two records together without having to assign a value to the linking columns?
Thanks.
|
|
|
|
|
csharp_boy wrote: how do i link Order_Items record with Order record in the dataset?
well regarding linking the two tables ,I assume that u are already using a typed dataset and an xml schema with the relation for that schema as you have mentioned .
so in order to link the tables for creating new items or basically to ADD a row in a table that has a forign key you need to do this:
//define a new row just for adding it in the forignKey Field in the child table
//so my Typed Dataset is "DataSet_tll_tables"
//and you have to make the row from the class of Your DataSet
//and intilize it with DataSet Object that you are using
WindowsApplication1.DataSet_all_tables.OrderRow temp_row_for_adding = dataSet_all_tables1.Order.NewOrderRow();
//for loop to get values of the parent row in the parent table //just for adding a new row\\
for(int i=0;i
|
|
|
|
|
I'm trying to figure out why I keep getting a error searching my database with ado. Here is my search button
<br />
Private Sub CmdSearch_Click()<br />
Dim SQlnameSearch As String<br />
SQlnameSearch = txtSearch.Text<br />
Adodc1.RecordSource = " Select * FROM Customers WHERE (ContactLastName)= '" & SQlnameSearch & "' "<br />
Adodc1.Recordset.Requery <-- errors here <br />
Adodc1.Refresh <-- if the above line is commented out it errors here<br />
' want to fill the datagrid with the results of the search from above.<br />
DBGrid1.Visible = True<br />
End Sub<br />
Now if i build mt own connection like so
<br />
Dim cnn As New ADODB.Connection<br />
Dim rst As New ADODB.Recordset<br />
Dim fld As ADODB.Field<br />
<br />
' Open the connection<br />
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br />
"Data Source=" & App.Path & "\databases\PoolApp.mdb"<br />
<br />
strSearchCustomer = FrmSearch.Searchtxt.Text<br />
rst.Open _<br />
"SELECT ContactLastName FROM Customers WHERE ContactLastName = '" & strSearchCustomer & "' ", _<br />
cnn<br />
<br />
<br />
' Print the values for the fields in<br />
' the first record in the debug window<br />
For Each fld In rst.Fields<br />
<br />
MsgBox rst.Fields.Count <-- I get 1 record but unable to fill the datagrid. <br />
<br />
Next<br />
<br />
' Close the recordset<br />
rst.Close<br />
Any help would begreatly appreciated
Help is great only if you ask correctly
|
|
|
|
|
jlawren7 wrote: Help is great only if you ask correctly
You said it yourself! You say this code generates errors, but you never say what those errors are. It's kind of impossible to help you without knowing what the errors are.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
This error comes when using the adodc1.recordsource =
Runtime error '91'
object varible or With block varible not set
This line is highlighted in Yellow
Adodc1.Recordset.Requery
the other section ( built my own connection results in nothing )
Help is great only if you ask correctly
-- modified at 14:44 Sunday 5th February, 2006
|
|
|
|
|
You're calling Requery on a RecordSet object that is null, or Nothing in VB. You cal only call this method on a RecordSet object that has been returned by a previous query, if that previous query returned anything at all.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Well since i have tried to go a different route
Now I'm dtuck with filling a dtagrid witrh the table contents. Then once that is working I would like to use a sql search to get the records i want
here is what I have now
<br />
Option Explicit<br />
<br />
' Create a Recordset<br />
Dim rst As ADODB.Recordset<br />
<br />
Private Sub Command1_Click()<br />
<br />
Set rst = New ADODB.Recordset<br />
rst.CursorLocation = adUseClient<br />
<br />
' Add columns to the Recordset<br />
rst.Fields.Append "Customer ID", adInteger<br />
rst.Fields.Append "First Name", adVarChar, 40, adFldIsNullable<br />
rst.Fields.Append "Last Name", adVarChar, 40, adFldIsNullable<br />
rst.Fields.Append "Address", adVarChar, 60, adFldIsNullable<br />
rst.Fields.Append "Phone Number", adInteger<br />
' Open the Recordset<br />
rst.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Testing\v003\databases\db1.mdb; Persist Security Info=False"<br />
<br />
<br />
' Populate the Data in the DataGrid<br />
Set DataGrid1.DataSource = rst<br />
End Sub <br />
<br />
A simple datagrid, command button on a form <br />
|
|
|
|
|
Man, you are really confused about these object works.
You didn't perform a query against that database at all. All you did was create a blank recordset, add some columns to it, then bound the empty recordset to a datagrid.
You need to actually execute a query against the database. Something like this will return all the records in the specified table:
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Program Files\Testing\v003\databases\db1.mdb;Persist Security Info=False"
rs.CursorLocation = adUseClient
rs.Open "tableName", conn, adOpenStatic, adLockReadOnly, adCmdTable
Set DataGrid1.DataSource = rs
This probably won't run as listed. I haven't used VB6 in over 5 years now...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
connection is dao, os is winxp, I want to disable SSPI. do you know how it is possible?
|
|
|
|
|
this query show error
SELECT distinct jr.txtjcode FROM jobmast J,JobsRound JR WHERE j.txtjcode=jr.txtjcode and and convert(smalldatetime,j.dtcompletion)>= CONVERT(nvarchar(11),getdate()) ORDER BY jr.txtjcode asc, j.datecompletion asc
erro is
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
i want to show jr.txtjcode in asc order of datecompletion is a date
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
SELECT distinct jr.txtjcode,j.datecompletion FROM jobmast J,JobsRound JR WHERE j.txtjcode=jr.txtjcode and convert(smalldatetime,j.dtcompletion)>= CONVERT(nvarchar(11),getdate()) ORDER BY jr.txtjcode asc, j.datecompletion asc
include the j.datecompletion field in the select list.
|
|
|
|
|
In Sql server management studio, after creating the stored procedure, it saves it in the default folder 'Projects'. Is that where the stored procs would be saved? If I wrote it in VS, where would I save it?
|
|
|
|
|
SQL Server itself stores the stored procedures in the database. While it is always nice to have your own text file version for easy editing there isn't anyway that I've found that is a great solution. All methods I've used have all been adequate and I'm still trying to find the great method that would actually save me time and make it easy to manage.
Both methods that I've found that work adequately are both deployment script driven - in other words the stored procedures are written in such a way that deployment is easy. However, the draw back is that code maintenance is slightly more difficult.
The first solution is to bundle all the stored procedures (or bundled by subsystem [however you want to define that]) into one script. When it comes time to deploy the application to the live/production environment all you have to do is run that script to upload the stored procedures. (I tend to have separate scripts for data model changes, additions, lookup/meta data, user defined functions, stored procedures and permissions). This solution suffers from the fact that the stored procedure script becomes bloated on a large system. However, it makes it easy to deploy manually by a DBA.
The second solution puts each of the scripts in various folders. The folders are numbered so that the can be followed in sequence. One of these folders will be for stored procedures. You can put all the stored procedures into the folder and name each file as per the stored procedure. This can produce potentially hundreds (or thousands) of files so a DBA is not going to go through each folder running each script manually. I've also found that concatenating the files for deployment can sometime produce strange effects, so the solution that I found was to create an install class to add to the setup of the application that follows the folders' numbering in sequence to deploy everything to the server. However this is also difficult to maintain.
I know this does not really answer your questions - but then again, VS still doesn't have a good solution to database deployment still (unless someone wishes to prove me wrong). I also realise you didn't ask about deployment, but it is an important thing to think about when you start as it will make everything easier in the long run.
Bottom line, your SQL Scripts should be stored with your Visual Studio Solution, unless the solution covers many different technologies in which case you may have to figure out a compromise solution.
NOTE: solution (with small "s") and Solution (with a capital "S") mean different things. The latter specifically refers to a Visual Studio Solution
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi,
I am trying to insert a datetime datatype into the Sql Server 2005 from the code in my file. The query is:
"Insert into xx (date, day) Values (" + currentdate + "," + currentday+")"
I get an error of not being able to convert the datetime to System.timespan. I dont know what it means.
can anyone please tell me what I am diong wrong
|
|
|
|
|
You haven't posted enough code to make a real diagnosis. However, from what you have said there is a DateTime object that somewhere is being converted to a TimeSpan object and it isn't able to do that - sorry, that is just paraphrasing the error message, but without the code I can't help much.
Also, the little code that you do show has security holes in it. You may like to read about SQL Injection Attacks and tips on how to solve them[^]
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hello All,
I have in my database two columne number colume and varchar and i need to add this columne in one columne i try cast but it gives me invaled express.
please help
hoho
|
|
|
|
|
It is very difficult to read your request because it is just one very long sentence.
You have two columns in your database.
A number column, and a varchar column.
You need to add [some column] in one single column.
You try to cast [some column] but it gives you an error message saying invalid expression.
I hope that is correct. I've put in square brackets where there was not sufficient context in order to parse your request.
Please supply the missing context from your request. Please also supply the code that you currently have.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi everybody,
I am kind of new in this area... So, I need to restore some .dbf files on SQL. Could you tell me which is the best way to do that?
P.S. I don't even know the .dbf file's structure. 
Thanks a lot,
Cristina
|
|
|
|
|
Hello,
I posted this over at sqlserverentral.com but have not had any reply's.
If you are a member here is the link:
Code[^]
What this will do:
Allows you to show the usage of any table, procedure, column, or view in any database on the server where the code is running.
Example:
exec spFindObjectUsage 'MyTableOfParts'
This will return all the views and procedures that call out MyTableOfParts.
Here is the code:
<br />
<br />
CREATE PROCEDURE spFindObjectUsage<br />
@ObjectToFind NVARCHAR(100) = ''<br />
,@ResultMessage VARCHAR(200) = '' OUTPUT<br />
AS<br />
DECLARE<br />
@ReturnCode INT<br />
,@StringToExecute NVARCHAR(1500)<br />
,@DBToProcess INT<br />
,@ServerName VARCHAR(200)<br />
,@TableWithServer VARCHAR(200)<br />
,@NameOfDatabase VARCHAR(50)<br />
--<br />
DECLARE @ServerDatabaseTables TABLE<br />
(TempTblID INT NOT NULL IDENTITY(1,1)<br />
,DBName VARCHAR(100) NOT NULL DEFAULT ''<br />
,Processed BIT NOT NULL DEFAULT 0)<br />
--<br />
CREATE TABLE #ServerDatabaseObjectUsage <br />
(UsageID INT NOT NULL IDENTITY(1,1)<br />
,DBName VARCHAR(100) NOT NULL DEFAULT ''<br />
,ObjectUsedIn VARCHAR(200) NOT NULL DEFAULT ''<br />
,TypeOfObject VARCHAR(50) NOT NULL DEFAULT ''<br />
,IsColumnOfTable BIT NOT NULL DEFAULT 0)<br />
--<br />
-- first get all the databases on the current server<br />
--<br />
INSERT INTO @ServerDatabaseTables<br />
(DBName)<br />
SELECT<br />
name<br />
FROM master.dbo.sysdatabases<br />
--<br />
SET @TableWithServer = ''<br />
SET @NameOfDatabase = ''<br />
--<br />
SET NOCOUNT ON<br />
-- each database has it's own listing of System Objects so inorder to get<br />
-- a correct listing we will need to go through every database.<br />
-- the only way I know to do this is using sqlexec.<br />
-- I know it is not the best way but we will need the ability to dynamically<br />
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect<br />
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN<br />
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)<br />
--<br />
SELECT @ServerName = DBName + '.dbo.'<br />
,@NameOfDatabase = DBName<br />
FROM @ServerDatabaseTables<br />
WHERE TempTblID = @DBToProcess<br />
--<br />
SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +<br />
'(DBName' +<br />
',ObjectUsedIn' +<br />
',TypeOfObject' + <br />
',IsColumnOfTable) ' +<br />
'SELECT DISTINCT ' +<br />
char(39)+@NameOfDatabase+char(39)+ <br />
',obj.NAME' + <br />
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) + <br />
' THEN ' + char(39) + 'PROCEDURE' + char(39) +<br />
' WHEN ' + char(39) + 'V' + char(39) + <br />
' THEN ' + char(39) + 'VIEW' + char(39) +<br />
' WHEN ' + char(39) + 'U' + char(39) + <br />
' THEN ' + char(39) + 'USER TABLE' + char(39) +<br />
' WHEN ' + char(39) + 'D' + char(39) + <br />
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +<br />
' WHEN ' + char(39) + 'F' + char(39) + <br />
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +<br />
' WHEN ' + char(39) + 'IF' + char(39) + <br />
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +<br />
' WHEN ' + char(39) + 'FN' + char(39) + <br />
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +<br />
' WHEN ' + char(39) + 'TF' + char(39) + <br />
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +<br />
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +<br />
',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +<br />
'THEN 1 ELSE 0 END)' + <br />
'FROM ' + @ServerName + 'sysobjects as obj ' + <br />
'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' + <br />
'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' + <br />
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + <br />
' OR syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + <br />
' ORDER BY obj.name '<br />
SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))<br />
PRINT LEN(@StringToExecute)<br />
--<br />
exec sp_executesql @StringToExecute<br />
--<br />
IF (@@ERROR != 0) BEGIN<br />
SET @ReturnCode = 1<br />
GOTO END_PROCEDURE<br />
END<br />
--<br />
UPDATE @ServerDatabaseTables<br />
SET Processed = 1<br />
WHERE TempTblID = @DBToProcess<br />
END<br />
--<br />
SELECT * FROM #ServerDatabaseObjectUsage<br />
--<br />
DROP TABLE #ServerDatabaseObjectUsage<br />
--<br />
SET @ReturnCode = 0<br />
<br />
END_PROCEDURE:<br />
IF (@ReturnCode != 0) BEGIN<br />
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))<br />
END ELSE BEGIN<br />
SET @ResultMessage = 'OK'<br />
END<br />
RETURN @ReturnCode<br />
I am looking for feed back on the code:
If you like it?
Is there a better way to do this?
And any other comments or suggestions are welome.
Please let me know,
William O'Malley
I hate users. Not all of them, just the ones who talk.CP member: Al Einstien
|
|
|
|
|
Would you be so kind as to put your 2 cents in on this code?
Thanks,
Will
I hate users. Not all of them, just the ones who talk.CP member: Al Einstien
|
|
|
|
|
Hi,
As a relative newbie to SQL Server/ASP.NET I'm hoping someone here
can help with my problem. I'm developing a timesheet application in
ASP.NET C# using Visual Studio 2003 with a database built in MSDE.
One of my forms needs to return a simple list of resources from my
database. I have followed the guide on the MSDN libraries, but for
some reason I continuously get the same error message.
What I've done so far is create the database, tables, and populate
with some sample data using using Server Explorer in Visual Studio. I have
connected to the database (using integrated security) and I am
trying to get the contents of the Resource table to appear on my
form. I have then created a DataAdapter (tested the connection, set
the SQL as a simple SELECT * from Resource, etc), which also generates an sqlConnection for me. To test this I have previewed the generated data, and it returns what I want, so I have chosen to generate a DataSet of this. I am then trying to get this data into a simple DataGrid. On the properties of the DataGrid I have changed the DataSource to point at my Dataset. As I
understand it, I then have to add the following to my Page Load
section of my code.
sqlConnection1.Open();
this.sqlDataAdapter1.Fill(this.dsResource);
DataGrid1.DataBind();
sqlConnection1.Close();
The form builds fine, but when I browse to the particular form I get
the following error for the sqlConnection1.Open(); line. If I remove this line the error simply moves to the line below.
Exception Details: System.Data.SqlClient.SqlException: Cannot open
database requested in login 'SCMS'. Login fails. Login failed for
user 'AL-NOTEPAD\ASPNET'.
To me this is an error with my connection string. My database
instance is actually 'AL-NOTEPAD\VSDOTNET'. However the properties
for sqlConnection1 are pointing to the correct datasource. I do not
know why the application is looking for user 'AL-NOTEPAD\ASPNET'. It does not exist, to my knowledge.
Any help with this would be greatly appreciated, as I get the same
error with my code for forms-based login...
Thanks in advance..
|
|
|
|
|
Check the web.config file if you have added the connectionstring there, and the source for the SqlConnecton1 again to double where it is pointing to.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
