|
hi i create school database and i make sp to insert employees information but
i can't insert date i want to know how to write the dates in sql server
like #1/1/2005# or '1/1/2005' or what
second
i want to modify the date apperance when i reterive it from table instead of
mdy it display ion dmy
answer me quickly thanks
ma_refay
|
|
|
|
|
SQL Server can accept dates in various formats, but for consistency and locale neutrality I always use ISO Format - 'YYYY-MM-DD'
ma_refay wrote: i want to modify the date apperance when i reterive it from table instead of
mdy it display ion dmy
Retrieve it to where? In .NET applications the date is retrieved into a DateTime object which will display it in what ever format you like.
It is a poor design descision to get the database to perform UI functions (such as rendering the date in a specific format).
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"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
|
|
|
|
|
Hello all, I have been looking everywhere for a solution to this, but I couldn't find it.
I am trying to access a database using OLEDB Consumer Templates (of which I have little experience) in a WTL project.
Ok, the problem is that when I open a CCommand object passing a DBPropSet structure I get no rows in return.
Let me try to explain with the code..
With the following code everything works fine. I get columns and rows correctly (I removed a lot of stuff such as error handling, to simplify). The code it’s more or less the same used in the MSDN sample DBViewer:
<code>
CCommand<CManualAccessor> oCmd;
oCmd.Open(m_session, sSQL, NULL, &nRows, DBGUID_DEFAULT, FALSE)
if (oCmd.m_spRowset == NULL)
{
//ERROR STUFF
}
if (oCmd.GetColumnInfo(&ulColumns, &pColumnInfo, &pStrings) != S_OK)
ThrowOleDBError(oCmd.m_spRowset, IID_IColumnsInfo);
struct MYBIND* pBind = new MYBIND[ulColumns];
oCmd.CreateAccessor(ulColumns, &pBind[0], sizeof(MYBIND)*ulColumns);
for (ULONG l = 0; l < ulColumns; l++)
oCmd.AddBindEntry(l + 1, DBTYPE_STR, sizeof(TCHAR)*255, &pBind[l].szValue, NULL, &pBind[l].dwStatus);
oCmd.Bind();
ULONG ulFields = oCmd.GetColumnCount();
while(oCmd.MoveNext() == S_OK)
{
for (ULONG j = 1; j <= ulFields; j++)
{
lpszString = pBind[j-1].szValue;
// PRINTING STUFF
}
}
</code>
Now, if I add properties to the command using this:
<code>
CDBPropSet pset( DBPROPSET_ROWSET );
pset.AddProperty(DBPROP_IRowsetView, true);
pset.AddProperty(DBPROP_IRowsetScroll, true);
pset.AddProperty(DBPROP_IRowsetChange, true);
pset.AddProperty(DBPROP_UPDATABILITY,
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_DELETE);
pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
/*…*/
oCmd.Open(m_session, sSQL, &pset, &nRows, DBGUID_DEFAULT, FALSE)
/*…*/
</code>
I get the columns shifted right (like if there is an empty column before all the others), and no records.
The m_spRowset member however it’s not null, as I check for it after the call to Open()
Can you please help me out, or at least tell me why this happens?
Thank you very much!
Guybrush Threepwood
-- modified at 12:14 Saturday 21st January, 2006
|
|
|
|
|
The application is running behind a proxy server which is needed for internet access. The database needed for the application is on a remote server.
How can I make a connection to the database through this proxy?
The browser (IE) that is set up for proxy has access to the internet that way.
When I try the application from a computer that doesn't use proxy, it's working just fine.
I've tried something like:
<br />
System.Net.GlobalProxySelection.Select = New System.Net.WebProxy("proxy.xxxxx.xx.xx.xx", 8080)<br />
Dim conn As SqlConnection = New SqlConnection("server=xxxx;uid=testdata;pwd=sa;database=testdb")<br />
conn.Open()
but, it doesn't work...
Thank you in advance!
Veljko
|
|
|
|
|
Hi..
I have installed SQL server 2000 personal edition but i couldnt find the Northwind database how could i install it and where could i find it?
|
|
|
|
|
Did you try looking on the front page of Microsoft's SQL Server 2000 Downloads[^] site?
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 0:08 Sunday 22nd January, 2006
|
|
|
|
|
You are right i found them there..
I needed those samples to understand my SQL books lessons
thank you very very much
|
|
|
|
|
Good morning
First, the error message is free translated because I'm german and I could not find the english translation.
My Problem. I have three table
- Temp_HD_serial_Number with the fields > ID & Harddisk_Number
- Temp_Media_Stored_Path with the fields > ID & Media_Path
- Temp_Media_Base with the fields > ID & Rel_HD_Serial_Number & Rel_Media_Stored_Path & MediaName
Table Temp_HD_serial_Number field ID has a 1:n relation to
table Temp_Media_Base field Rel_HD_Serial_Number.
Table Temp_Media_Stored_Path field ID has a 1:n relation to
table Temp_Media_Base field Rel_Media_Stored_Path.
My code
Dim rs, SQL, AutoID
rs = CreateObject("ADODB.Recordset")
SQL = "SELECT Temp_Media_Base.ID, Temp_Media_Base.Rel_HD_Serial_Number, _
Temp_Media_Base.Rel_Media_Stored_Path, Temp_Media_Base.MediaName FROM Temp_Media_Base;"
rs.open(SQL, ConString, 3, 3)
rs.AddNew()
rs.Fields("Rel_HD_Serial_Number").Value = G_HDserialID
rs.Fields("Rel_Media_Stored_Path").Value = arg_idMediaPath
rs.Fields("MediaName").Value = arg_MediaFile
rs.Update()
AutoID = rs.Fields("ID").Value
rs.Close()
rs = Nothing
The error -2147217887 occours during rs.Update(). The surprisingly, sometimes up to 20 records can be stored in the tables and sometimes less, but never 0.
I surved the internet a long time but this special behave I could not find. Is there anybody who can assist me to solve that problem ?
I'm a programming newbe.
Thanks in advance Theo
|
|
|
|
|
-2147217887 is 0x80040E21 in hex. Looking this up in oledberr.h in the Platform SDK shows that this error is DB_E_ERRORSOCCURRED . The text description of the error is: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
You should examine the Errors collection on your Recordset's Connection property to see the errors that actually occurred.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
And this is my problem. I'm not the messages in the error collector. (NewBie)
But I forgot to mention that I have 2 conection to the access database open.
I tried it with just one open connection.. and it works. That means I have to
access the table sequential and everything is working. But I don't know whether this
behave is OK. Do you have any idea why 2 connection parallel are not working ?
|
|
|
|
|
The problem is the way Access locks records. You can read about this on Technet[^]
Wout Louwers
|
|
|
|
|
Thanks for assistant. I will read this article.
|
|
|
|
|
I have a problem using ADO to run a Stored Procedure in SQL Server from Visual C++ 6.0.
In fact, the problem happens because the stored procedure is parameterized. I create one parameter, append to the command e run. It works fine.
But then I have to run another procedure using the same Command variable. But the parameter I have added to the command is stil there, so I get an error that the number of arguments is invalid for the stored procedure.
Let me show the code:
//Here I create a parameter and append to the command variable
m_pCommand->CommandText = "sp_ODMGroupList";
pParam1 = m_pCommand->CreateParameter ( _bstr_t ("AppID"), adVarChar, adParamInput, 5, _variant_t (m_appID.c_str ()));
m_pCommand->Parameters->Append (pParam1);
//Next, I repeat the step, with another stored procedure
m_pCommand->CommandText = "sp_ODMGroupParamList";
pParam1 = m_pCommand->CreateParameter ( _bstr_t ("AppID"), adVarChar, adParamInput, 5, _variant_t (m_appID.c_str ()));
m_pCommand->Parameters->Append (pParam1);
But when I execute, I get an error that the stored procedure has too many parameters.
So, how do I delete the first parameter?
I have tried this
m_pCommand->Parameters->Delete ((_variant_t) pParam1);
But it says that cannot find the item with this name.
So, how do I append and delete parameters in _CommandPtr type?
Thanks in advance!
|
|
|
|
|
The cleanest way of doing this is to use an entirely new Command object and build it with new parameters.
In your case here, since your reusing the same parameter name with the same type and size, all you need to do is change its value. But even then, I don't recommend doing this.
The reason being is code maintainability. If you change the first stored procedure and its accompanying C++ code, you now have to go and fix the code that modifies this object to be reused with the old 2nd stored procedure.
In short, don't reuse the database objects unless you absolutely have to. Just destroy the old ones and create new ones for each stored procedure you're going to execute.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I have a sql string that gets the top 50 items in a search.
Shomthing like:
<br />
string SQL = "SELECT TOP 50 U.strName " +<br />
"FROM tbUser U " +<br />
"WHERE U.strName LIKE '%om%' "<br />
DataManager dm = new DataManager();<br />
DataTable table = dm.GetDataTable(SQL);<br />
but I also want to find exact how many there are.
My result would be something like "There are 387 names with the searchstring 'om' here are the top 50: ...."
Can this be done with the "SELECT TOP" statement. I'm using MS SQL Server 2000.
Thanks
Thomas
-- modified at 13:15 Friday 20th January, 2006
|
|
|
|
|
You can always run a second query - "SELECT Count(*) From tbUser WHERE ... " - in your search procedure, and return that count with the results.
|
|
|
|
|
Try this
"SELECT TOP 50 U.strName , (Select Count(*) From tbUser) as TotalRecords " +<br />
"FROM tbUser U " +<br />
"WHERE U.strName LIKE '%om%' "
Regards
|
|
|
|
|
Hi,
I have the following SQL:
select a.codeid, b.codeidalias from tblcodes a
left outer join tblcodeslookup b on a.codeid = b.codeid
where a.codeid not in (select codeidalias from tblcodeslookup)
and it produces the following kind of data
codeid codeidalias
1 null
2 3
2 4
2 5
6 7
6 8
6 9
10 null
11 12
11 13
14 null
if you can see the pattern. I have been trying for hours to modify it but i cant . Basically, of the codeids that appear more than once, I just want the first appear, ie.
1 null
2 3
6 7
10 null
11 12
14 null
Can anyone help?
Thanks in advance
Eddie
|
|
|
|
|
Try this (with a MIN and GROUP BY added):
select a.codeid, MIN(b.codeidalias) as MinCodeIdalias from tblcodes a
left outer join tblcodeslookup b on a.codeid = b.codeid
where a.codeid not in (select codeidalias from tblcodeslookup)
group by a.codeid
|
|
|
|
|
Hey there database fans,
If I do this:
declare @stuff xml
set @stuff = '<xml><thing>Word</thing></xml>'
select @stuff.query('/xml/thing') I get returned:
<thing>Word</thing>
How do I make it so I get just:
Word
Any ideas?
Regards,
Rob Philpott.
|
|
|
|
|
Chage it to this:
declare @stuff xml
set @stuff = '<xml><thing>Word</thing></xml>'
select @stuff.value('/xml[1]', 'varchar(20)')
Your query will return an XML element, not a value. You have to call the value method on XML element returned get what you want. Value will only work on one element and return 1 value though, hence the [1] you see in the XQuery.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 12:56 Friday 20th January, 2006
|
|
|
|
|
Genius. Thank you!
Regards,
Rob Philpott.
|
|
|
|
|
I have a stored procedure that has two cursors running one after another. I say while @@fetch_status = 0 to enter the loop. when the first one finishes fetch_status is -1 and wont enter the 2nd cursor. can i manually set fetch_status?
Thanks in advance
Stephen
|
|
|
|
|
Let's see the code. It sounds like you haven't "primed the pump" so to speak. The way to do it is to setup your cursor, read the first record, THEN enter the loop controled by @@FETCH_STATUS. Something like this:
DECLARE curMasterGames CURSOR FOR
SELECT MasterGameID FROM MasterGames
OPEN curMasterGames
DECLARE @GameID bigint
-- Prime the pump.
FETCH NEXT FROM curMasterGames INTO @GameID
-- Check to see if the FETCH failed on the first record BEFORE we try
-- to do any processing on that records data.
WHILE @@FETCH_STATUS = 0
BEGIN
-- It seems the FETCH worked, do something with it.
EXEC UpdatePlayerScoresOnMasterGameID @GameID
-- Get the next record.
FETCH NEXT FROM curMasterGames INTO @GameID
END
CLOSE curMasterGames
DEALLOCATE curMasterGames
-- Do the same exact pattern for the next cursor you open.
-- Prime, test for failure, then process your loop.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 10:37 Friday 20th January, 2006
|
|
|
|
|
Nice one, thats exactly the problem, thank you ver much
|
|
|
|
|