|
Hi,
I would like to find out how to delete, or at least modify a stored procedure. I'm using MS Access DB, and add stored procedure using C#.
Thanks!
|
|
|
|
|
I may be mistaken but: MS Access doesn't have stored procedures.
|
|
|
|
|
MS Access does have a kind of stored procedure. I did a test on it before and it worked.
The stored procedures are basically queries that can receive parameters, so you should find out how to create queries in Access using C#.
I specified SQL statements to create these 'stored-procedures' before and I suppose you can pass statements to create them from C#.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thank you for your answer. However, I do know how to create stored procedures, I don't know how to delete them or modify them when they are defined already...
Thanks anyway...
|
|
|
|
|
|
Thank you so much! on both answers!
You are a life-saver.
|
|
|
|
|
I'm trying to use the following code to create a backup set that would store the backup as 20040608.bak
USE master
GO
DECLARE @curdate as INT
SET @curdate = (SELECT CONVERT(char(12), GETDATE(), 112))
EXEC sp_addumpdevice 'disk','MyNwind_1', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @curdate + ".dat"
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
I'm getting the following error
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
Why am I unable to concat the @curdate tot the string
|
|
|
|
|
Just a guess, but is it since you have @Curdate defined as an Int, and you're attempting to do a string concatenation on it?
Like I say - just a guess, but would personally try CAST ing or CONVERT ing @CurDate first....
BTW - there is also no need to create the dump device first - you can use the syntax:
<br />
BACKUP DATABASE NWind TO DISK='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\'....<br />
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
".dat" may need to be changed to '.dat' depending upon your SQL Configuration.
|
|
|
|
|
try this:
USE master
GO
DECLARE @curdate as char(8)
DECLARE @filePath as varchar(1024)
SET @curdate = (SELECT CONVERT(char(8), GETDATE(), 112))
SET @filePath = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @curdate + '.bak'
EXEC sp_addumpdevice 'disk','MyNwind_1', @filePath
There are a couple of problems with your code. First, as already stated, you're trying to add an int to a varchar, which would cause problems. This isn't the error you're getting, but it would have been the next.
When you pass parameters into a proc via EXEC, you have to pass constants or variables. You can't pass expressions that must be evaluated. ("Path\" + @file + ".bak" would be an expression.)
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
Thank you all for the comments and support. I'm new to T-SQL. Need hours and hours of practice
|
|
|
|
|
I use C++, COM,
so sample from VB is good.
first- I need to enumerate SQL servers on network,
Especially interesting now:
and secondly to enumerate data bases within some SQL server,
how to do it?
Thanks.
|
|
|
|
|
|
When iam extracting a date from SQL Svr 7 and i want it to be in a specific format, isn't there a SQL command that will do the formatting?
system date '2004-06-09 13:43:41.363' i want this date in 'mm\dd\yy' format
If so, could you give me the syntax and the associated style codes.
Thanks
syed saba
|
|
|
|
|
this works on 2000
check it on 7 :
select convert(varchar(10),DateValue,1)
|
|
|
|
|
I concur with hspc. There are about 20 options for formatting the date. Look at the help on convert in SQL which will give you the parameters and examples.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi,
I have a question. I'm inserting a row into a table in Access DB (without using the command builder), one of the coloumns (the primary key, actually) in the table is an AutoNumber - which gets updated when I insert.
Does anyone know how can I get the coloumn's value?
thanks!
|
|
|
|
|
SELECT @@IDENTITY works for Access 2000 and later.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thank you so much! on both answers!
|
|
|
|
|
Hi ..
please see the code below..
I have two tables Centrals and Devices both having Central_Id as a common field..I am able to display the Centals table value in the first datagrid (dataGirdCentral).However on click of each cell on the central_id i want the corresponding entries of Devices table to be shown in the 2nd datagrid dataGridDevices..how do i do that...
private void loadCentralDevices()
{
ds = new DataSet();
//Getting schema of Centrals table
DataTable centralDt = new DataTable("Centrals");
String query = "Select * from Centrals";
OleDbDataAdapter da = new OleDbDataAdapter(query,ConfigurationSettings.AppSettings["MsAccess_ConnectString"]);
da.FillSchema(centralDt, SchemaType.Source);
ds.Tables.Add(centralDt);
//Getting schema of Devices table
DataTable deviceDt = new DataTable("Devices");
String query1 = "Select * from Devices";
da = new OleDbDataAdapter(query1,ConfigurationSettings.AppSettings["MsAccess_ConnectString"]);
da.FillSchema(deviceDt,SchemaType.Source);
ds.Tables.Add(deviceDt);
DataRelation dr = new DataRelation("Central_Devices_Relation",
centralDt.Columns["Central_ID"], deviceDt.Columns["Central_Id"]);
ds.Relations.Add(dr);
//create a dataview of the data
DataView centralVw = new DataView(ds.Tables["centralDt"]);
//giving access to Centrals table
centralVw.AllowDelete=true;
centralVw.AllowEdit = true;
centralVw.AllowNew = true;
//set the grid source to the author view
dataGridCentral.DataSource = centralVw;
//hook up the event handler
dataGridCentral.CurrentCellChanged+= new EventHandler(this.dataGridCentral_CellChanging);
}
private void dataGridCentral_CellChanging(object sender, EventArgs eArgs)
{
????? what do i write here to get the corresponding values?
}
Breath dot net
|
|
|
|
|
Hi All,
I have an issue with inserting new rows into a typed dataset. The dataset has two datatables (e.g. Order, OrderDetails). Both tables have a primary key (not nullable) and there is a foreign key constraint (and relation) between the OrderDetails and Order datatable.
The problem comes when trying to insert a new row into the Order (parent) table. Because I haven't been to the DB to get the next sequence number (i'm using Oracle), there is no value on the primary key. Obviously I can't add this row to the datatable unless i turn constraints off which I don't want to do. I also don't want to have to go to the database everytime I add a new row to the datatable as it could result in a stack of roundtrips to the database (each orderdetail insert would require another round trip)
Surely there is another way that I'm not aware of? What would be good is that for any new rows that are created, I put a GUID or something in the PK which would allow me to insert and relate child rows etc.. but I don't know if this is even possible or if it's best practice.
Thanks in advance for any help!
|
|
|
|
|
I updated the parent table for my application before using the PK as FK for the child table, but you might want to try this:
There is a property called DataRow.SetParentRow that sets the parent row for the child table.
I always specify this property before I update the child table, e.g.
<code>parentRow.Key = KeyValue
parentRow.Field1 = Value1
parentRow.Field2 = Value2</code>
<code>childRow.Key = parentRow.Key
childRow.Field1 = Value1
childRow.Field2 = Value2
childRow.SetParentRow(parentRow)</code>
This might help you link the two tables along with Key Fields and Foreign Key Fields setting in the dataset.
Remember that you need to update the parent table before the child table for insertion.
I hope it helps.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi, I am debugging a COM Dll that uses ADO thru smart pointers (#import) and when recordsets are used, they were being Released with Release (); I have used recordsets in the past thru smart pointers and always used to call Close (); and then assign NULL to the recordset pointer,
so what I did with the "recordset->Release ();" lines was to change them to: "recordset = NULL;", but I wonder if I should explicitly Close them?
does anybody know how this should be? thanks a lot,
Nemike
Nemike
|
|
|
|
|
i have add reporting services to my production machine and the home page does not display its toolbar so i can not manage my reports? why do i not have a toolbar in my reporserver?
|
|
|
|
|
How to add/change/read GUID data type field on SQL server with ADO (C++)
|
|
|
|