|
Jeremy Pullicino wrote:
SQL Queries
Some syntaxt for ACCESS and SQLServer are different so you should be careful for that. If you use ADOX I think the only difference is connection string but maybe some functions are supported for one of them. there is no 100% way to do the same code..
Mazy
No sig. available now.
|
|
|
|
|
ADOX sucks.
Been a whole day on it and did not even manage to create a primary key...
I will be try SQL queries now... maybe I should have gone for that option in the first place.
Why do MS keep ADOX if it does not work?
Jeremy Pullicino
C++ Developer
Homepage
|
|
|
|
|
I was just doing some searching for SQL stuff here on CP and saw some things using SQL-DMO. After looking through the SQL Server Books Online that I downloaded I had a quick question. SQL-DMO doesn't seem like a good tool for retrieving information from a database, but more for administering it (adding tables, columns, etc). Am I right in that assesment? Is the best way for an MFC app to use CRecordset, and related ODBC classes?
Thanks.
- monrobot13
|
|
|
|
|
I typically use ADO to run stored procedures and sql statements. I use DMO to write administrative utilities that run backups and script objects...etc.
I would agree with your assessment, at least from my perspective.
~LizardWiz()
|
|
|
|
|
Nice to hear that my assesment was correct. You brought another question to my mind. Is there MFC classes or include files/libs that I need to use ADO? I keep reading about it, but I've no idea how I use it. Thanks for the help.
- monrobot13
|
|
|
|
|
I have a simple ASP.NET page that takes a couple of text string (login, username, password) and stores in a SQL database using ADO.NET.
If I try to store username "Conan O'brien" into the SQL database, I get the following error:
"Incorrect syntax near 'brien'. Unclosed quotation mark before the character string"
Is there any good way to work around this?
Thanks,
Trond Borg
|
|
|
|
|
You must use 2 quotes..
INSERT INTO Table(Column1)
VALUES('Conan O''brien')
Free your mind...
|
|
|
|
|
Use parameters.
Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO tbl VALUES (@username)"
Dim param As SqlParameter = _
New SqlParameter( "@username", SqlDbType.VarChar, 20 )
param.Value = "Conan O'brien"
cmd.Parameters.Add( param ) The data provider takes care of transmitting the parameter.
|
|
|
|
|
Hi Trond Borg
just place another " ' " before the place where u want to palce '
i.e
if u want to save Conan O'brien just write a code look like
INSERT Table1 (chColumnName)
VALUES ('Conan O''brien')
|
|
|
|
|
hi, we know how to search by letter
but it retrive all data contents that letter
for ex:
if i'm looking for the word "data"
the search resultes will be "data" + any character contents the same letters without order
i use this code for search
dataview1.rowfilter = " [discrabtion] like '" * data * "'"
if there is another code for this please send it to us
|
|
|
|
|
Use '%' as a wildcard character.
dataview1.rowfilter = " [discrabtion] like '%" + data + "%'"
Michael
|
|
|
|
|
Has anyone ever figured out a way to use "sp_executesql" on a stored procedure that returns an output parameter. It seems as though when you execute "sp_executesql" SQL server compiles the syntax using a different profile. I can't seem to access any variables declared in the SQL string (even using an optional declaration parameter) nor can I access any variables declared within a stored procedure from within the "sp_executesql" string.
It just doesn't seem possible. Anyone got any creative ideas?
example:
sp_executesql "exec sp_name @varname output"
or:
sp_executesql "exec sp_name @varname output", @varname int
~LizardWiz()
|
|
|
|
|
acording to the SQLServer BOL, that can't be done, variables used within the query being executed by sp_executesql are local to the query therefore they can't be accessed by the query that executed it in the first place
i hope i made myself clear with that
|
|
|
|
|
Actually you can do it.
What you are missing is the that it needs to be an nvarchar.
this is from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q262499
\CREATE PROCEDURE Myproc
@parm varchar(10),
@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT
AS
SELECT @parm1OUT='parm 1' + @parm
SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
@parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT
SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc
Barbara, MCP
|
|
|
|
|
WOW! Works great! Thanks alot....I thought I was a T-SQL GURU but I acquiesce the title to you.
~LizardWiz()
|
|
|
|
|
thanks for pointing that out, i made some test myself and by lookign at your code i see that i was missing the OUTPUT statement in the parameters that were returning a value
|
|
|
|
|
kinda nasty... but check this out
http://www.sqlmag.com/Forums/messageview.cfm?catid=22&threadid=722
|
|
|
|
|
plz i need the way how to creat table by sql statmen . my project is to read data from xml put it in databas i want to use sql server , how i can creat table dynamicly
hi
|
|
|
|
|
samo7a wrote:
creat table by sql statmen
Use CREATE TABLE SQL command and run it with SQLCommand if you use .net application.
samo7a wrote:
my project is to read data from xml put it in databas i want to use sql server
Write a stored procedure and pass a text of your xml to it, in that procedure use EXEC sp_xml_preparedocumnet and OPENXML to update your table . If you asearch for these keywords in SQLServer online book you can find a VB sample for it.
Mazy
No sig. available now.
|
|
|
|
|
I have a question about using ExecutiveNonQuery() in C# (using Access).
The following code would return 0, when I specify "@myMax".Value = 1 (see below).
However, if I changed @myMax to a constant (ie. not using a variable), everything worked fine and it returned 1 as the result.
The problem is that since it doesn't throw an exception, and only returns 0, I have no idea what's wrong with the "Update" SQL statement. Thank you and appreciate any feedbacks!!
<br />
OleDbCommand myUpdate = new OleDbCommand("UPDATE Product SET Prod_max = @myMax, Prod_desc = @myNote WHERE Prod_id = @myID", oleDbConnection1);<br />
<br />
myUpdate.Parameters.Add("@myNotes", OleDbType.LongVarWChar);<br />
myUpdate.Parameters.Add("@myID", OleDbType.VarChar);<br />
myUpdate.Parameters.Add("@myMax", OleDbType.SmallInt);<br />
<br />
myUpdate.Parameters["@myNotes"].Value = Notestxtbox.Text.ToString();<br />
myUpdate.Parameters["@myID"].Value = ProdIDtxtbox.Text.ToString();<br />
myUpdate.Parameters["@myMax"].Value = 1;<br />
<br />
try<br />
{<br />
textBox1.Text = myUpdate.ExecuteNonQuery().ToString();<br />
}<br />
catch (Exception ex)<br />
{<br />
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);<br />
}
|
|
|
|
|
HI,
Can someone help me with following SQL?
I have an array of about 200 string IDs in my program (C++).
I need to delete these IDs from a database table.
I would like to use the most efficent method of doing this.
Any suggestions on the best SQL query?
Thanks
Jeremy Pullicino
C++ Developer
Homepage
|
|
|
|
|
If your id columns is numeric, try this...
DELETE FROM [Table] WHERE [ID_Column] IN (1,2,3,...,n)
If it's string...
DELETE FROM [Table] WHERE [ID_Column] IN ('1','2','3',...,'n)
Free your mind...
|
|
|
|
|
Use the In Clause to execute the query.
DELETE FROM [TableName]
WHERE ID IN (1,2,3,4,5,6,etc...200)
I know Sysbase 11 had a limit of 255 values in the In Clause. However, I do not think SQL Server or Access has that limitation.
Michael
|
|
|
|
|
Otherday I installed SQL Server on my personal computer, but now I forgot the password for 'sa' userid. How to retrive it? or How can I reset it?
Promise only what you can do. And then deliver more than what you promised. This signature was created by "Code Project Quoter".
|
|
|
|
|
Do you have Enterprise Manger? As much as I think it logged in automaticlly and in Security-->Login node you can change password(I don't remembr if it asked old pass),if it asks, you can backup from your databases there and uninstall-install SQLServer again.
Mazy
No sig. available now.
|
|
|
|