|
Well, perhaps you should still look at using an update statement, but then using a scalar function. You could pass in some value you would help the function know what serial number to return. Just a thought.
Ben
|
|
|
|
|
I have written a class for operating the SQLCE database with OLE DB, it work perfect on INSERT ,UPTDATE and so on, but when I use 'select' statement to get values from database it doesn’t return values properly even though the GetNextRows method succeeds.
HRESULT hr = NOERROR;
m_pIRowset = NULL;
m_prghRows = &m_rghRows[0];
hr = pICmdText->SetCommandText(DBGUID_SQL, pwszQuery);
if(!FAILED(hr))
{
hr = pICmdText->Execute(NULL, IID_IRowset, NULL, NULL, (IUnknown **)&m_pIRowset);
if (FAILED(hr)){
AfxMessageBox(_T("failed"));
m_pIRowset = NULL;
}
// Get IColumnsInfo interface
hr = m_pIRowset->QueryInterface(IID_IColumnsInfo, (void**)&m_pIColumnsInfo);
if(FAILED(hr)){
AfxMessageBox(_T("failed"));
}
// Get the column metadata m_ulNumCols//m_pDBColumnInfo
hr = m_pIColumnsInfo->GetColumnInfo(&m_ulNumCols, &m_pDBColumnInfo,&m_pStringsBuffer);
if(FAILED(hr) || 0 == m_ulNumCols){
AfxMessageBox(_T("failed"));
}
// Create a DBBINDING array.
m_dwBindingSize = m_ulNumCols;
m_prgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*m_dwBindingSize);
if (NULL == m_prgBinding){
hr = E_OUTOFMEMORY;
AfxMessageBox(_T("Memory allocation failure"));
}
// Set initial offset for binding position
m_dwOffset = 0;
// Prepare structures to create the accessor
UINT lCount;
ULONG dwOrdinal;
for (lCount = 0; lCount< m_ulNumCols; lCount++)
{
dwOrdinal = m_pDBColumnInfo[lCount].iOrdinal;
m_prgBinding[lCount].iOrdinal = dwOrdinal;
m_prgBinding[lCount].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
m_prgBinding[lCount].obLength = m_dwOffset;
m_prgBinding[lCount].obStatus = m_prgBinding[lCount].obLength + sizeof(ULONG);
m_prgBinding[lCount].obValue = m_prgBinding[lCount].obStatus + sizeof(DBSTATUS);
m_prgBinding[lCount].wType = m_pDBColumnInfo[lCount].wType;
m_prgBinding[lCount].pTypeInfo = NULL;
m_prgBinding[lCount].pObject = NULL;
m_prgBinding[lCount].pBindExt = NULL;
m_prgBinding[lCount].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
m_prgBinding[lCount].dwFlags = 0;
m_prgBinding[lCount].bPrecision = m_pDBColumnInfo[lCount].bPrecision;
m_prgBinding[lCount].bScale = m_pDBColumnInfo[lCount].bScale;
switch(m_prgBinding[lCount].wType){
case DBTYPE_WSTR:
m_prgBinding[lCount].cbMaxLen = sizeof(WCHAR)*(m_pDBColumnInfo[lCount].ulColumnSize + 1); // Extra buffer for null terminator
break;
default:
m_prgBinding[lCount].cbMaxLen = m_pDBColumnInfo[lCount].ulColumnSize;
break;
}
// Calculate the offset, and properly align it
m_dwOffset = m_prgBinding[lCount].obValue + m_prgBinding[lCount].cbMaxLen;
m_dwOffset = ROUND_UP(m_dwOffset, 8);
}
// Get IAccessor interface
hr = m_pIRowset->QueryInterface(IID_IAccessor, (void**)&m_pIAccessor);
if(FAILED(hr))
{
AfxMessageBox(_T("failed"));
}
// Create the accessor
//
hr = m_pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, m_dwBindingSize,
m_prgBinding, 0, &m_hAccessor,NULL);
if(FAILED(hr))
{
AfxMessageBox(_T("failed"));
}
// Allocate data buffer.
//
m_pData = (BYTE*)CoTaskMemAlloc(m_dwOffset);
if (NULL == m_pData)
{
hr = E_OUTOFMEMORY;
CString errors;
errors.Format(_T("%d"),m_pDBColumnInfo[2].ulColumnSize);
MessageBox(NULL,errors,NULL,MB_OK);
AfxMessageBox(_T("failed"));
}
// Retrive a row
//
hr = m_pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &m_cRowsObtained,&m_prghRows);
if (SUCCEEDED(hr) && hr != DB_S_ENDOFROWSET)
{
// Set data buffer to zero
//
memset(m_pData, 0, m_dwOffset);
// Fetch actual data
hr = m_pIRowset->GetData(m_prghRows[0], m_hAccessor, m_pData);
if (FAILED(hr))
{
m_pIRowset->ReleaseRows(1, m_prghRows, NULL, NULL, NULL);
AfxMessageBox(_T("failed"));
}
}
if (hr == DB_S_ENDOFROWSET)
{
m_bIsEOF = TRUE;
}
}
venkat
|
|
|
|
|
how can i let SQL server 2005 accept remote connections
|
|
|
|
|
The Surface Area Configurator
|
|
|
|
|
Dear All,
I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End upgraded with service pack 3 and analysis service.
Now I am trying to install sql server 2000 report services. But I run SQL2KRSSP1-ENG.EXE setup
Then following error occurs
The upgrade patch can not be installed by window installer because the program to be upgraded may be missing , or the upgrade patch may update a different version of the program Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch.
I redownload the same exe to other location. And again run but getting same error
Please Guide me or atleast give some help full link.
thanks
regards
imran khan
|
|
|
|
|
Hi All,
I am getting error server msg:18025 when I try to execute extended stored procedure [ xp_Semdmail ]. Please do suggest way out.
EXEC master..xp_sendmail '1',
'New title, mention in the next report to distributors.','','','navneethegde@gmail.com
Develop2Program & Program2Develop
|
|
|
|
|
How, I do DTS in SQL SERVER 2005
Mohan Balal
|
|
|
|
|
|
i am planning to do an extensive data transfer from tables in one database to different database that has a different table structure can someone point me in the direction i should be reading up where i can accomplish this?
kenny
|
|
|
|
|
INSERT INTO Table1
([Table2] (Field1,Field2)
)
SHAPE {
OPENQUERY([Adventure Works DW],'SELECT Field1,Field2
FROM database.Table') As Table2
}
Best Regards,
Chetan Patel
|
|
|
|
|
I have developed an application that connects to a SQL Server 2003 database. My connection string is stored in the application configuration file. In development it is connecting to our company server but when I installed the application on site it will connect to their own SQL Server 2007 database. So when I installed the application on site I went into the configuration file and changed the server name to the new name. Well, for some reason my application cannot connect. Is there an easy way to get the connection string needed to connect to the new database?
|
|
|
|
|
dptalt wrote: SQL Server 2007
Is not out yet, so I assume you really meant SQL Server 2005.
For SQL server 2005, remote protocols (TCP, Named pipes, HTTP) are not enabled by the default installation.
Depending on what they have enabled (if anything) and how you were trying to connect, thing might very well not work that
worked fine with SQL Server 2000. In other words, there is likely nothing wrong with where you are storing the connection string,
but the protocol you are trying to connect with may not be active on their server (TCP for instance - dbmssocn).
|
|
|
|
|
Hi all
I use stored procedure to insert to a table but the ERROR_MESSAGE function return 1 and anything else.The stored procedure like this:
CREATE PROCEDURE Insert_Project
(@pNumber_M int,
@pRow_Agreement int,
@pRow_Machine int,
@pRow_Language int)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Number_O int;
BEGIN TRY
SELECT @Number_O=Number-sum(Number_A) FROM Machine_View WHERE Row=@pRow_Machine GROUP BY Number
SET @Number_O=@Number_O-@pNumber_M
INSERT INTO Project
(Number_A,Number_D,Row_Agreement,Row_Machine,Row_Language)
VALUES
(@pNumber_M,@Number_O,@pRow_Agreement,@pRow_Machine,@pRow_Language)
UPDATE Project
SET Number_D=@Number_O
WHERE Row_Machine=@pRow_Machine
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
Can anyone help me
Thanks a lot
|
|
|
|
|
Comment out the try/catch block and see what error is given then.
|
|
|
|
|
Lately I have be using Strongly typed datasets and adapters and have been liking them. However I have come across a few stumbling blocks that I was wondering if someone could help me with.
1. I'm making a code library that does various things, I guess you would call it something of a 'business layer.' I have strongly typed data adapters, with my own sql fuctions. In my library I use these data adapters and also enforce a few key rules like setting timestamps and logging user names. If someone could create their own instance of the data adapter and bypass my library it would threaten the integrity of the data. Is there some way to secure the dataset so that my libary would be the only thing that could create it?
2. Next thing, in my library each function creates a data adapter, calls its function or functions and then returns its value. Since it is a class I was looking at making a private static version of each of these data adapters. Are there any pitfalls that I should be aware of, (adapters not clearing data, or disconnecting, etc....) or is this a pretty common practice?
3. Lastly, is there some way to enforce rules into the data adapter itself. For instance if i have a function called "Add_Record(recorddata, username, timestamp)" do something to enfoce that username must always be "HttpContext.Current.User.Identity.Name" and that the timestamp is "datetime.now"
|
|
|
|
|
anyone can help ?
Server Error in '/aspx' Application.
SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
Source Error:
The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:
1. Add a "Debug=true" directive at the top of the file that generated the error. Example:
<%@ Page Language="C#" Debug="true" %>
or:
2) Add the following section to the configuration file of your application:
Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.
Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.
Stack Trace:
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
ASP.Default_aspx.CreateDataView() +67
ASP.Default_aspx.Page_Load(Object sender, EventArgs e) +497
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
|
|
|
|
|
nothingbutcat wrote: [SqlException: SQL Server does not exist or access denied.]
Well, either you are attempting to connect to a SQL Server that does not exist. Or the SQL Server does exist but you don't have the authority to use it from an ASP.NET application.
Check your connection string. Does the server exist?
Are you using a trusted connection?
* If yes, does SQL Server permit access to the ASP.NET account?
* If no, have you set up the relevant user in SQL Server? And Is the password specified in the connection string correct?
|
|
|
|
|
Thank you. It's helpful.
It is our attitude towards life and work that makes our life 100%
|
|
|
|
|
Hi Everyone!
Is there any way by which I can convert .NET Framework types to SQL data type to be used by a Create Table command?
Some code to explain what I intend to do:
<code>public class SqlTableAdapter<T> where T : new()
...
PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
for (int indexProperty = 0; indexProperty < properties.Length; indexProperty++)
{
Object[] attributes = properties[indexProperty].GetCustomAttributes(typeof(DataObjectFieldAttribute), true);
if (attributes.Length > 0)
{
DataObjectFieldAttribute dataObjectField = attributes[0] as DataObjectFieldAttribute;
DataColumn dataColumn = new DataColumn(properties[indexProperty].Name, properties[indexProperty].PropertyType);
dataColumn.Unique = dataObjectField.IsIdentity;
dataColumn.AllowDBNull = dataObjectField.IsNullable;
if (properties[indexProperty].PropertyType == typeof(string))
{
dataColumn.MaxLength = dataObjectField.Length;
}
dataColumn.SqlType = SqlTypeConvert( properties[indexProperty].PropertyType );
dataColumns.Add(dataColumn);
}
}
...
...
DbCommand commandCreateTable = this.Connection.CreateCommand();
commandCreateTable.Connection = this.Connection;
commandCreateTable.CommandText = string.Format("CREATE TABLE [{0}] ({1})", typeof(T).ToString(), columnDefinition);
commandCreateTable.CommandType = CommandType.Text;
Thanks in advanced for your help
Gywox
-- modified at 10:29 Thursday 29th March, 2007
|
|
|
|
|
i have a column named ReceiveDate..that is nvarchar.and have a row '27/03/2007'
when i run this statement that have some problems:
select * from tablea where
convert(datetime,OrderDate,101) >= (CONVERT(datetime,'26/03/2006',101)) AND
convert(datetime,OrderDate,101) <= (CONVERT(datetime,'27/04/2007',101))
pls any body can halp me?if possible give the correct select statement here.
thanks.
|
|
|
|
|
Test270307 wrote: when i run this statement that have some problems
That's a bit vague. What sort of problems?
Test270307 wrote: select * from tablea where
convert(datetime,OrderDate,101) >= (CONVERT(datetime,'26/03/2006',101)) AND
convert(datetime,OrderDate,101) <= (CONVERT(datetime,'27/04/2007',101))
Why are you doing all these conversions?
SELECT *
FROM TableA
WHERE OrderDate >= '2006-03-26'
AND OrderDate <= '2007-04-27'
When writing a date in SQL always use ISO format. That way you don't have to worry about cultural differences and your code will work anywhere.
|
|
|
|
|
sorry dear,
ur select statement does not return any rows.
in my select statement problems was:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
|
|
|
|
|
Test270307 wrote: ur select statement does not return any rows
Then perhaps OrderDate was in an incorrect type. If you are going to store dates in the database then store them as a SMALLDATETIME or a DATETIME . Do not store them as anything else unless you have an exceptionally good reason.
|
|
|
|
|
convert function's style 101 represents mm/dd/yy
your dates are in the format dd/mm/yyyy
use 103 for the same like ....
select * from tablea where
convert(datetime,OrderDate,103) >= (CONVERT(datetime,'26/03/2006',103)) AND
convert(datetime,OrderDate,103) <= (CONVERT(datetime,'27/04/2007',103))
Regards
KP
|
|
|
|
|
|