|
Hi
I have used this for the slq statem
SELCT FirtName, LastName, ID,From Coustomer WHERE FirstName LIKE '[A-Z]'
C#
private void button1_Click(object sender, System.EventArgs e)
{
oleDbConnection1.Open();
oleDbDataAdapter2.SelectCommand.Parameters["LastName"].Value = textBox1.Text;
dataSet11.Clear();
oleDbDataAdapter2.Fill(dataSet11)
oleDbConnection1.Close();
}
But when I run it gave a and error
can u help me anyone
Harrison Brock
|
|
|
|
|
You're not going to get anywhere with SQL unless you spell statements correctly.
When posting questions, you really need to copy and paste code accurately (ideally formatting with the <pre> tag), and report error messages exactly.
I can currently see at least four errors with your SELECT statement, which (assuming that the data schema is spelled correctly) should look more like
SELECT FirstName, LastName, ID
FROM Customer
WHERE FirstName LIKE '[A-Z]' Note spelling of SELECT, FirstName, Customer, and the removal of the comma before FROM.
|
|
|
|
|
Well, I am new to C++. For past some time I have been facing a problem.
I have a stored procedure residing on the IBM DB2 database v 7.2.
This stored procedure has one input parameter and two output parameters. All these three parameters are of type datetime.
Whenever I call SQLBindParameter function, the return code is -1. I am unable to figure out why.
Will be grateful to anyone who can show me the right path. Here is the code snippet:
-----------------------------------------------------------------------
SQLHANDLE hStmt = NULL;
DATE_STRUCT dsCurDate,dsIPEffDate,dsPrExpDate;
SQLINTEGER cbDate = 0;
if(!OpenConnection(MakeConnectionString())) return FALSE;
SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_STMT,m_pDatabase->m_hdbc,&hStmt);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
return FALSE;
}
ret = SQLPrepare(hStmt,(SQLCHAR*)"CALL BSCPROC.PCI040(?,?,?)",SQL_NTS);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//Input parameter
ret=
SQLBindParameter(hStmt , //Statement handle
1 , //Parameter number
SQL_PARAM_INPUT, //Parameter direction
SQL_C_TYPE_DATE, //SQL C-datatype
SQL_TYPE_DATE , //SQL datatype
0 , //size
0 , //Decimal digits
&dsCurDate , //The input data
0 , //Input data length
&cbDate);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//First output parameter
ret=
SQLBindParameter(hStmt , //Statement handle
2 , //Parameter number
SQL_PARAM_OUTPUT , //Parameter direction
SQL_C_TYPE_DATE , //SQL C-datatype
SQL_TYPE_DATE , //SQL datatype
0 , //size
0 , //Decimal digits
&dsIPEffDate , //The input data
0 , //Input data length
&cbDate);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//Second output parameter
ret=
SQLBindParameter(hStmt , //Statement handle
3 , //Parameter number
SQL_PARAM_OUTPUT , //Parameter direction
SQL_C_TYPE_DATE , //SQL C-datatype
SQL_TYPE_DATE , //SQL datatype
0 , //size
0 , //Decimal digits
&dsPrExpDate , //The input data
0 , //Input data length
&cbDate);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
//Specify input parameter
dsCurDate.year = 2003;
dsCurDate.month= 10;
dsCurDate.day = 5;
ret = SQLExecute(hStmt);
if(ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO)
{
PrintError(hStmt,ret,FALSE);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
CloseConnection();
return TRUE;
|
|
|
|
|
i'm buying a book in order to learned sql server, my first choice is "SYBEX mastering SQL server 2000", can anyone give his/her comments regarding this book, and may i also ask for other book titles?! thank you;P 
nevhile.net
|
|
|
|
|
|
|
The following is the code written in win32 console application using ADO
in C++.
on execution i get the error as COleSafeArray is an undeclared identifier....
is there any need to incluide any headre files for this??
when i tried including AFXDISP.H...it says windows.h is already included and mfc can't include windows.h again.
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 2);
// Fill the fields name now
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("Name")) );
lArrayIndex[0] = 1;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("HTML")) );
// Create an array of values to be added
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, );
//Fill the values in each field
lArrayIndex[0] = 0;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("RK")) );
lArrayIndex[0] = 1;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("myHTML")) );
// call Addnew of recordset now
pRecordset->AddNew( vaFieldList, vaValueArray );
pRecordset->Close();
ranjani
|
|
|
|
|
This is more of a C++ problem.
If you're not currently using MFC, adding it in only to support COleSafeArray is very much a sledgehammer/walnut combination. However, if you must do it, it's best to put the MFC headers into the precompiled header file (probably called StdAfx.h).
MFC is very dependent on the order in which its own and the Windows headers are included, so you must remove any #include <windows.h> line, or at least ensure that #include <afx.h> appears before it.
Alternatively, you could use SAFEARRAY directly, using SafeArrayCreate or SafeArrayCreateVector, or try searching for a different wrapper class. ATL 7.x has CComSafeArray, which is a template class.
|
|
|
|
|
i'm thinking of learning sql server, but i'm afraid that my efforts of learning sql server 2000 will be wasted when yukon is released. Will there be a massive change from 2000 to yukon?
nevhile.net
|
|
|
|
|
Learning SQL Server 2000 will not be a waste of time. The T-SQL language will still be the better way of manipulating or retrieving sets of data.
|
|
|
|
|
If with the words Learning sql server you would say that you are about to start learning T-SQL you don't have to worry, T-SQL has new implementations on Yukon but it's still there and will stay there.
So when Yukon will be released you'll need to learn the new features included in the language not a new language.
If your question is about the IDE and server side manipulation instruments, like Enterprise Manager and Query Analyzer, there is a larger revolution in Yukon - better say EVOLUTION - but it won't let you down if you decide to learn the present IDE instruments, because the Yukon IDE will be the Visual Studio.NET IDE with many new features like intellisense and gadgets to let you develop your databases and all objects faster and cleaner. It will make your life easier and it is not so different from what you are used to.
(much easier if you use Visual Studio.NET).
If your question is about server administration it is the same, there are many new features in Yukon relative to security, objects, schemas and other things but it is an Evolution not something like - throw all you know out of a window and restart from zero -
From whom do I have this informations?
Well I've been to a presentation conference at Microsoft Italy and had a look to the Beta 1 version.
As I say it's an Evolution not a Revolution, give us DBAdministrators and Programmers many new features making SQL Server better.
From this i can say in my opinion you can spend some time on SQL 2000 and it's not a waste of time.   
------------------------
Sabrina C.
FOR You
Information Technologies
Italy
|
|
|
|
|
Is there anyway (using msde) to backup a database from a mssql 2000 server? or any program, code etc. I would like to back up my database off my website localy onto my own computer (Just in case anything happens or I move providers). Thanks for any help you can provide.
|
|
|
|
|
|
thanks  so I run this as a nonquery?
|
|
|
|
|
I keep getting an error about an "Unrecognized escape sequence" this is the line
SqlCommand cmd1 = new SqlCommand("EXEC sp_addumpdevice 'disk','databasedump','C:\temp\database.dat")
What am I doing wrong?
|
|
|
|
|
If you're using C#, you need to either prefix the string with @, or you need to double the backslash characters - the \ character begins a special character combination in all C-based languages. See the documentation for string[^] or the specification for string literals[^] (although this is probably too much detail!)
Specifically, the '\t' will be interpreted as a TAB character and '\d' is causing the error you mention (since this is not a valid escape).
You also appear to be missing a single-quote mark to close the last string parameter.
|
|
|
|
|
ok I thought that's what it was but I got sidetracked on another project and never had time for it untill today (what good timing huh  )
I am trying to do the backup command you showed me and I am delaring them like this
<br />
SqlCommand cmd1 = new SqlCommand("EXEC sp_addumpdevice 'disk','localBackup','C:\\temp\\database.dat'",sqlConnection1);<br />
<br />
SqlCommand cmd2 = new SqlCommand("BACKUP DATABASE databaseName TO localBackup",sqlConnection1);<br />
I am calling them from within this try block
<br />
try<br />
{<br />
cmd1.ExecuteNonQuery();<br />
cmd2.ExecuteNonQuery();<br />
tbError.Text = "Finished";<br />
}<br />
and running them as non queries (I tried them as transactions but it gave me this error "The procedure 'sp_addumpdevice' cannot be executed within a transaction")
so I took them out and make them queries and now I get a problem with the Execute command saying I need a transaction property.
"Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."
Now I am really confused 
|
|
|
|
|
I suspect you've still got a BeginTransaction call outstanding on the sqlConnection1 object.
|
|
|
|
|
As the subject suggests, I'm simply updating the database with SqlDataAdapter.Update and sometimes no update occurs, but usually it does. If it never worked, I'd know it was a problem with my code, but running twice the same steps doesn't always produce the same results.
I've read on the web that others have seen this behavior but haven't seen any fix or workaround. Anyone here experienced this or know a workaround?
-- James --
|
|
|
|
|
So I'm happy to report the problem was with my code and not with the .NET SqlDataAdapter. But I think the circumstances of the problem bear repeating so that others might avoid it.
Form A creates some SqlDataAdapters that fill a dataset and periodically refreshes it. At some point, due to user interaction, an instance of "Form B" is created and passed a reference to the SqlDataAdapters. Form B then finds a DataRow in one of the tables of the dataset, does some work on it, and then calls SqlDataAdapter.Update to put the changed DataRow in the database. And this worked _most_ of the time, but occasionally it would not - no data would be updated to the database.
It turns out that during Form A's periodic update, it would clear the datatable and the refill it. This process would cause Form B's datarow it was working on to get to a state of DataRowState.Detached. Since the DataRow was now detached, when form B would call SqlDataAdapter.Update, the info from the row would not go to the database because the DataRow is not associated with the DataSet that the adapter was working with. And there was no error to be reported in this circumstance.
So the moral of the story is this: Beware what's happening to your DataSet or DataTables while working with a DataRow. And it doesn't hurt to make sure the row hasn't somehow become detatched when you weren't watching.
-- James --
|
|
|
|
|
I have 2 tables:Table1 and Table2 where I have ID and Dateentered.
The both tables can have the same id but different date.
If record in table 1 is oldest then in table2 select data in table1.
How do I do this?
|
|
|
|
|
Assumption: you mean that you want to select the rows which have the lowest date, whichever table they come from.
On this assumption, I think doing a UNION of the tables is probably about right.
SELECT [ID], MIN( Dateentered ) AS MinDate
FROM Table1 UNION ALL Table2
GROUP BY [ID] If you actually want to insert rows or update rows in table 2, that's a bit trickier, and would have to be done in two stages:
-- Insert any rows into Table2 that are in Table1
-- but not in Table2
INSERT INTO Table2
SELECT T1.[ID], T1.Dateentered
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.[ID] = T2.[ID]
WHERE T2.[ID] IS NULL
-- Set Table2's entries to the date in Table1,
-- if table1's are earlier
UPDATE Table2
SET Dateentered = T1.Dateentered
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.[ID] = T2.[ID]
WHERE
[ID] = T1.[ID] AND
T1.Dateentered < T2.Dateentered
|
|
|
|
|
Hello,
I wonder if anyone can help me with this.
I try to insert a record in a DataTable, and everything works absolutely perfect. UNTIL I create a custom DataView object based on the table.
Here is a small sample, when you take the DataView myView = new DataView(table); line out of comment the record isn't at the correct position anymore.
Any help is much appreciated !!!!
DataTable table = new DataTable("Table");<br />
<br />
DataColumn colID = table.Columns.Add("ID", typeof(int));<br />
DataColumn colName = table.Columns.Add("Name", typeof(String));<br />
<br />
colID.AllowDBNull = false;<br />
<br />
table.Rows.Add(new object[] {1, "Row 1"});<br />
table.Rows.Add(new object[] {2, "Row 2"});<br />
table.Rows.Add(new object[] {4, "Row 4"});<br />
<br />
table.AcceptChanges();<br />
<br />
<br />
foreach(DataRowView row in table.DefaultView)<br />
{<br />
Console.WriteLine(row["Name"]);<br />
}<br />
<br />
Console.WriteLine("-------------------------------------------------------");<br />
<br />
DataRowView cindyView = table.DefaultView.AddNew();<br />
cindyView["ID"] = 3;<br />
cindyView["Name"] = "Row 3";<br />
<br />
Object[] ItemArray = cindyView.Row.ItemArray;<br />
cindyView.CancelEdit();<br />
<br />
DataRow cindy = table.NewRow();<br />
cindy.ItemArray = ItemArray;<br />
cindy.EndEdit();<br />
table.Rows.InsertAt(cindy, 2);<br />
<br />
table.DefaultView.RowStateFilter = DataViewRowState.None;<br />
table.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;<br />
<br />
foreach(DataRowView row in table.DefaultView)<br />
{<br />
Console.WriteLine(row["Name"]);<br />
}
|
|
|
|
|
It would help if you posted the actual output from this code and what you expected to see.
IIRC, unless you've applied a primary key constraint, or some kind of sort criteria, your view will show the records in the order they were inserted.
|
|
|
|
|
I get the following output if I don't create the additional DataView:
Row 1
Row 2
Row 4
-----
Row 1
Row 2
Row 3
Row 4
And this is the output when the DataView object is created based on the table:
Row 1
Row 2
Row 4
-----
Row 1
Row 2
Row 4
Row 3
I don't see, why the DevaultView of the table changes the order of the DataViewRows, just because I create another DataView object based on this DataTable. Also, when I go in debug mode and check the Rows collection of the DataTable, the rows appear as one would expect (1, 2, 3, 4).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
