|
Data Adapters and SQLCommands
What's that?
How to do them?
Thanks!;P
|
|
|
|
|
Insert into table1 values ('''','hhhh')
i.e. replace ' by '' (quote by quote quote)
|
|
|
|
|
hi all,
I am getting following error..
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll. There is no problem with my store procedure as I checked from Query Analyzer.
public static void EditTreatInstrument(int Id,string Name,string BarCode,string PDACode,DateTime ExpireTime,
int Method )
{
SqlCommand cmd = WesServer.GetSqlCommand.Get( Conn , CommandType.StoredProcedure ,
"pr_EditTreatInstrument" );
cmd.Parameters.Add( GetSqlParameter.Get( "@intId", SqlDbType.Int, Id ) );
cmd.Parameters.Add( GetSqlParameter.Get( "@nvchrName", SqlDbType.NVarChar, Name ) );
cmd.Parameters.Add( GetSqlParameter.Get( "@vchrBarCode", SqlDbType.VarChar, BarCode ) );
cmd.Parameters.Add( GetSqlParameter.Get( "@vchrPDACode", SqlDbType.VarChar, PDACode ) );
cmd.Parameters.Add( GetSqlParameter.Get(" @dtExpireDate", SqlDbType.DateTime, ExpireTime ));
cmd.Parameters.Add( GetSqlParameter.Get( "@intMethod", SqlDbType.Int, Method ) );
cmd.ExecuteNonQuery();
}
What are the condition when such error can occer ???
Thanks, Sudeep
======
You need a head to program. Cool, fast and sharp.
|
|
|
|
|
The SqlException object will contain more information.
The Message property and, if multiple error occurred, the Errors property will give lots of good information.
If you cannot see from the exception message then post back to the forum and I'm sure someone will know. Good luck!
"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
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
After doing try catch within the error block I caught such exception:
System.Data.SqlClient.SqlException: @dtExpireDate is not a parameter for procedure pr_EditTreatInstrument. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at.... so on
and when I excluded @dtExpireDate, then I see exception like
System.Data.SqlClient.SqlException: Procedure 'pr_EditTreatInstrument' excepts parameter '@dtExpireDate', which was not supplied. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at... so on
Thank you, Sudee
======
You need a head to program. Cool, fast and sharp.
|
|
|
|
|
I have just noticed a problem in my db with the date format. The problem is that some servers are set to interpret dates differently, eg mm/dd/yy or dd/mm/yy. My problem is to find out which it needs and which to supply. I have also been told that the format of dates is an int, and just adds to 1/1/1900 to get the desired result. I am not sure how this works, but it sounds like it could fix my problem.
If you know of a way that I can parse a date to the db, and it will then decide how to format it, or whatever solution you have in mind, please let me know.
|
|
|
|
|
I just tend to use descriptive method of adding a date to my database.
e.g. "INSERT INTO [MyTable] ([type],[myDate]) VALUES (23, 'Sept 23 2004')"
I then let the DBMS handle everything else. This way there are no confusion over which is the day and which is the month.
|
|
|
|
|
The most reliable and cross-platform compatible way of handling dates that i have found it to use the format "YYYY-MM-DD", optionally "YYYY-MM-DD HH:MM:SS". It's unambiguous, and every DBMS i've used handles it fine.
If you're using MS SQL Server though, you can use any format you like, and remove ambiguity before your query using "set dateformat". see books online for that.
jon
using System.Beer;
|
|
|
|
|
I'm with Jon. Either use an ODBC Canonical date (YYYY-MM-DD HH:mm:SS.ffff) or use an ISO standard date (YYYYMMDD HH:mm:SS.ffff) Any DB server, regardless of regional settings, should correctly handle either of these formats.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
|
I think the source file is not include with this article or plan to include in the future. If we follow step by step the some of the source file already include in its documents.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Thanks. But I dont think it's in the article though. I haven't gone thru it all yet. But read up to the section on ALGORITHM class implementation (stub code) and the include files mentioned are nowhere to be found.
DmhMemory.h and DmhMemory.cpp
DmhLocalization.h and DmhLocalization.cpp
ParamHandler.h, and ParamHandler.cpp
StaticAlgorithmMetadata.h
dmalgo.h and oledbdm.h
And they're not in article or Analysis Services installation directory.
Norman Fung
|
|
|
|
|
 my connection string
"server=localhost;initial catalog=MyDatabase;user id=;pwd=;trusted_connection=true"
but this string no longer works after installing SP2.
Just wondering if this is normal?
|
|
|
|
|
|
I have an access database, and I have a small program that is using an OLEDBDataAdapater to retrieve the information. The problem is that it pulls multiple items (which it must be able to do.) I need to be able to bind the results to a textbox or some control that allows for multiple selection with copy ability. Any help would be greatly appriciated. TIA
BTW the coding of the program is very simple.
oledbdataadapter1 (select string)
dsups1.clear()
oledbdataadapter1.fill(dsups1)
|
|
|
|
|
OK. I figured this part out, and was able to display the data in a listbox. Now I need some help with being able to copy a selection from the listbox so that I can paste it into another program. TIA
|
|
|
|
|
Hi all!
I'm facing the challenge to distribute a SQLServer 2000 database with my application and was wondering what's the best way to do this.
Up to now I have been working with an access database that could simply be deployed by copying the .mdb file and adjusting the path in the connect string.
Now I have a working SQLServer 2000 Database on my development machine and need to transfer the database structure as well as several database entries onto a target machine.
I already tried creating a SQL script with Enterprise Manager, but didn't find a way to include the data in the database.
Saving the database and restoring it on the target machine doesn't work either because the restore process chokes when the SQLServer instance is different or installed in a different location.
I guess I'm missing something really trivial, can anybody help?
TIA,
mav
|
|
|
|
|
Do you have access to both SQL servers on a LAN. If so, I use the DTS Wizard (Import/Export) to transfer structure and data between servers.
Michael
CP Blog [^]
|
|
|
|
|
Thanks for your suggestion,
unfortunately that won't work in my case.
I'm distributing the application on CD and want it to install a complete SQLServer database on the target machine along with the application itself.
So no way will the target machine have access to the SQLServer on my development machine.
mav
|
|
|
|
|
mav.northwind wrote:
So no way will the target machine have access to the SQLServer on my development machine.
A lot depends on how much data. In the cases where I don't have direct access to the second Sql box. I tend to create a DTS package that exports to a CSV/XML or other file. Then have a DTS package that imports the data.
I often resort to having a VB app doing all the work as the DTS package can be created as VB source code
In other cases I have my data in an XML format and use SQL Commands to insert the data into another server.
There is also the BCP[^] tool that comes with SQL Server.
Michael
CP Blog [^]
|
|
|
|
|
Thank you, Michael!
I've actually found an application doing something like this here on CP, but with Graham's tip (see below) we can save all this 'voodoo'  and just detach the live database, deploy it and than re-attach. Seems much easier.
Regards,
mav
|
|
|
|
|
You could detach the database on your development machine (you then get a standalone .mdf file), ship that with your app, and attach it to the target SQL server. That way, you'll get everything in the original database (because it's a direct copy).
Attching/detaching the database is easy - just use the sp_attach_db and sp_detach_db stored procedures.
|
|
|
|
|
Thanks Graham!
That really seems to be the easiest way.
Don't know why I missed sp_attach_db while browsing the documentation...
Best regards,
mav
|
|
|
|
|
Can we write in one script file that copy the .mdf file and .ldf file to the target machine and attach it to SQL server? This process should be done during or after the process of install the application. If it is possible, then it would be greate!! 
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Sure, I've done it this way now.
It consists of two parts:
1) One InstallerClass that can copy files from the installation source directory to the target directory, replacing certain parameters in the process
2) Calling osql with the script created in step 1
The database files are included as regular files in the setup project and installed in a 'Database' subdirectory of the target directory.
Then I've added a custom action that, after all the files have been installed successfully, copies an sql template file to the target directory and fills in the real path to the database files.
Another custom action then calls osql -E -i <scriptCreatedInStep1>.
The script contents are just:
exec sp_attach_db @dbname='OurDatabaseName', @filename1='$(TRGDIR)\Database\MyDB.mdf', @filename2='$(TRGDIR)\Database\MyDB_Log.ldf'
go
and $(TRGDIR) gets replaced by the directory my program gets installed to.
Works great.
Regards,
mav
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
