|
Create a DataTable, fill it with sql data and
...
Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String)
DataTable2CSV(table, filename, vbTab)
End Sub
Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String, _
ByVal sepChar As String)
Dim writer As System.IO.StreamWriter
Try
writer = New System.IO.StreamWriter(filename)
' first write a line with the columns name
Dim sep As String = ""
Dim builder As New System.Text.StringBuilder
For Each col As DataColumn In table.Columns
builder.Append(sep).Append(col.ColumnName)
sep = sepChar
Next
writer.WriteLine(builder.ToString())
' then write all the rows
For Each row As DataRow In table.Rows
sep = ""
builder = New System.Text.StringBuilder
For Each col As DataColumn In table.Columns
builder.Append(sep).Append(row(col.ColumnName))
sep = sepChar
Next
writer.WriteLine(builder.ToString())
Next
Finally
If Not writer Is Nothing Then writer.Close()
End Try
End Sub
|
|
|
|
|
Hello everyone, I am working on this project, and I want to convert a VARCHAR into a BIT using the CONVERT function, but I cannot find any examples of this. If anyone can help, I would very much appreciate it.
This is what my code looks like
[code]
DROP PROC insertfarminginformation
GO
CREATE procedure insertfarminginformation
@FirstName varchar(50),
@LastName varchar(50),
@Address varchar(100),
@Address2 varchar(100),
@City varchar(50),
@State char(5),
@Zip varchar(10),
@Dup varchar(1),
@CASS varchar(1),
@DPV varchar(1)
as
CONVERT(BIT,@Dup)
CONVERT(BIT,@CASS)
CONVERT(BIT,@DPV)
insert into Farming_Information values (@FirstName, @LastName, @Address, @Address2, @City,
@State, @Zip,@Dup, @CASS, @DPV)
GO
[/code]
Thanks,
The Major Rager
-- modified at 20:52 Friday 21st October, 2005
|
|
|
|
|
You need to assign the return of the CONVERT function to a variable:
Sample:
declare @Dup varchar(1)
set @dup='1'
declare @b bit
Set @b=convert(Bit,@Dup)
Select @b
|
|
|
|
|
I have loaded a datatable from a SQL database and have bound it to a customized(through vs.net, not code) datagridview, which has textboxcolumns, checkboxcolumns, and one comboboxcolumn. My problem is this: In order to actually interact with the combobox in any column, the user has to click 3 times. Near as I can figure, these 3 clicks account for 1) selecting the datagridviewcell, 2) selecting the combobox in the datagridviewcell, and thirdly, actually opening the combobox for editing. Is there any way to speed this up so a click (I know its in column 5 of the datagridview) opens the combobox? I've tried the _onCellContentClick event handler, with casting the sender as a datagridviewcomboboxcell, but I could not see any quick way of selecting the combobox inside. Any ideas?
|
|
|
|
|
Hello guys,
I am writing a program that will read some data drom a *.mdb database file.
Suppose I have db2.mdb file and Table1 consists of next data
ID Name Surname<br />
<br />
1 nick Gates<br />
2 john Allen<br />
3 david Ellison
Now when I load this database in my application and for example delete the second record, in My Program the data is displayed(I use CListCtrl for displaying the data) in next way :
<br />
ID Name Surname<br />
<br />
1 nick Gates<br />
3 david Ellison
But I need that after deleting the second record , my program displayed it like this :
ID Name Surname<br />
<br />
1 nick Gates<br />
2 david Ellison<br />
As I guess After deleting the record I'll have to change the ID-s of all records in Database, and arrange them from beginning(1,2,3,..)
But I do not know how to do this , I am a beginner in Databases.
Can anyone help?
Thanks in advance
"Success is the ability to go from one failure to another with no loss of enthusiasm." - W.Churchill
-- modified at 12:21 Friday 21st October, 2005
|
|
|
|
|
Not a good design - imagine if you have another table linked to that table on ID (like, say, customer contacts) then when John Allen leaves, David Ellison loses all his own contacts and gets John Allens ones instead?
Do a Google search on "E.F. Codd" and "Normalization" for more on this subject.
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
I'm currently working on a project that use FileMaker Server and SQL Server 2000. On a button click, I sometime need to migrate data from SQL to FileMaker. So far everything is fine. But now the problem is with decimal values. Currently I just do a replace of (,) to (.). But FileMaker use . as a thousand separator ... So I try to send [34,78] as a value (the SQL statement is generated automatically and by no means we can use parameter), and the ODBC driver return an SQL Parser Error on this value.
So is there a way to send the (,) in an SQL statement without returning a parser error ?
Thank you very much !!
By the way, the ODBC driver is SEQUELINK 5.4
My Blog
-- modified at 11:15 Friday 21st October, 2005
|
|
|
|
|
I have created a database application that stores the thumbnails of images and videos on my system.I have used ADO.net and sql server for this.I want it to run on a system which doesnot have sql server installed.Can anyone help me to create a database without using sql servers.I have written the code in C#.
|
|
|
|
|
Hi,
unni_jeevan wrote: I have used ADO.net and sql server for this.I want it to run on a system which doesnot have sql server installed
Why don't you want to install the SQL-Server? There is a small version of SQL-Server2000 called MSDE. You can install this.
In Nov. MS will release SQL-Server2005 and the smaller version called SQL-Server2005 Expresss edtition. Take a look at this.
If u used a DataAcess-factory it is easy to switch to another database, which needs no installation, like FireBird embedded. In this case, the only thing you have to put with your application is a DLL and the database  That's cool  .
|
|
|
|
|
Hello guys,
I have a MFC dialog based application which Is loading some data from a *.mdb database file , but I have problems when I want to delete a record there .
Suppose we have next situtation. Table1 in my database file looks like this :
ID Name Surname<br />
1 nick Gates<br />
2 john Allen<br />
3 david Ellison
And I want to delete the secont record : 2 john Allen
So I am doing following :
void CMyDlg::OnDelete()
{
...
TRY
{
database.Open(NULL,false,false,sDsn);
CRecordset recset( &database );
SqlString.Format("DELETE from Table1 WHERE Name IS '%s'",sParam);
database.ExecuteSQL(SqlString);
}
CATCH(CDBException, e)
{
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;
...
}
I get error Message Box : "Invalid use of IS Operator in expression 'Name IS john''"
Can anyone help what's wrong with the IS directive above??
"Success is the ability to go from one failure to another with no loss of enthusiasm." - W.Churchill
-- modified at 9:41 Friday 21st October, 2005
|
|
|
|
|
Try
LIKE instead of IS. for eg DELETE from Table1 WHERE Name LIKE '%s'
Cheers!
<bold>Looney Tunezez
"If you build it....
.....BUGS will come!" -JB
Application.Run(new Form1(this.Dispose())); <--WHAT :wtf::confused::eek:
"Stability. What an interesting concept" - Chris Maunder
<bold>
|
|
|
|
|
thanks man it worked
"Success is the ability to go from one failure to another with no loss of enthusiasm." - W.Churchill
|
|
|
|
|
Looney Tunezez wrote: Application.Run(new Form1(this.Dispose()));
*ROFL* Where do you get this code? 
|
|
|
|
|
Hi Guys!!pls help,
I'm having a public holiday table storing all public holidays,so whenever is a holiday is then displayed on a portlet on my home page,so I want that portlet to also appear notifying if the next day is a holiday,
how can I do that??
|
|
|
|
|
you can use a query like this (SQL Server):
Select holidaydate from tablename
where datediff(day,getdate(),modifieddate) in (0,1)
|
|
|
|
|
I'm getting lost pls clarify a bit more,or is there any Jscript that I can use?pls help,my problem is that I really dont know how to do it for example when is friday and Monday is a holiday,the portlet has to appear on friday notifying that monday is a holiday....,and when is tuesday and wednesday is aholiday is has to appear on tuesday...
|
|
|
|
|
error
Line 10:
Line 11:
Line 12: val1 = BeginDate.SelectedDate
Line 13: val2 =EndDate.SelectedDate
Line 14:
val1 =BeginDate.SelectedDate
val2 =EndDate.SelectedDate
Sub Get_Click(Sender As Object, E As EventArgs)
'Dim MyConnection As SqlConnection
'Dim MyCommand As String
'Dim strin As String=tb.Text
'Dim strout As String=tb1.Text
Dim dbpath
dbpath=Server.MapPath("..\staff2\database\Hotelmgt.mdb")
Dim objConnection as OleDbConnection
objConnection = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &dbpath)
objConnection.Open()
Dim objCommand as OleDbCommand
objCommand = New OleDbCommand("SELECT HOTEL.Htlname, RATE.Roomtype, RATE.Datein, RATE.Dateout, RATE.Mealplan,RATE.Rate, RATE.Extnt FROM HOTEL INNER JOIN RATE
ON HOTEL.Htlid = RATE.Htlid WHERE (((RATE.Datein) >= val1)) AND ((RATE.Dateout) <= val2 ));",objConnection)
' objCommand = New OleDbCommand("SELECT HOTEL.Htlname, RATE.Roomtype, RATE.Datein, RATE.Dateout, RATE.Mealplan,RATE.Rate, RATE.Extnt FROM HOTEL INNER JOIN
'RATE ON HOTEL.Htlid = RATE.Htlid WHERE RATE.Datein Between [3/2/2005] And [6/1/2006] ORDER BY RATE.Datein",objConnection)
' bjCommand = New OleDbCommand("SELECT * FROM RATE WHERE (((RATE.Datein)>=#1/1/2004#) AND ((RATE.Dateout)<=#10/01/2005#));",objConnection)
'objCommand.SelectCommand.CommandType = CommandType.objCommand
'objCommand.SelectCommand.Parameters.Add(New Parameter("@Beginning_Date", DbType.DateTime))
'objCommand.SelectCommand.Parameters("@Beginning_Date").Value = BeginDate.SelectedDate
'("@Beginning_Date").Value = BeginDate.SelectedDate
'objCommand.SelectCommand.Parameters.Add(New Parameter("@Ending_Date", DbType.DateTime))
'objCommand.SelectCommand.Parameters("@Ending_Date").Value = EndDate.SelectedDate
'("@Ending_Date").Value = EndDate.SelectedDate
Dim objDataReader as OleDbDataReader
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
MyDataGrid.DataSource = objDataReader
MyDataGrid.DataBind()
objDataReader.Close()
End Sub
Parameterized Stored Proc Select to a DataGrid Control
Start Date
<asp:calendar id="BeginDate"
="" borderwidth="2" bordercolor="lightblue" font-size="8pt" titlestyle-font-size="8pt" titlestyle-backcolor="#cceecc" dayheaderstyle-backcolor="#ddffdd" dayheaderstyle-font-size="10pt" weekenddaystyle-backcolor="#ffffcc" selecteddaystyle-backcolor="lightblue" runat="server">
| End Date
<asp:calendar id="EndDate"
="" borderwidth="2" bordercolor="lightblue" font-size="8pt" titlestyle-font-size="8pt" titlestyle-backcolor="#cceecc" dayheaderstyle-backcolor="#ddffdd" dayheaderstyle-font-size="10pt" weekenddaystyle-backcolor="#ffffcc"
="" selecteddaystyle-backcolor="lightblue" runat="server">
|
| <asp:datagrid id="MyDataGrid" runat="server"
="" width="500" backcolor="#ccccff" bordercolor="black" showfooter="false" cellpadding="3" cellspacing="0" font-name="Verdana" font-size="8pt" headerstyle-backcolor="#aaaadd" enableviewstate="false">
|
how to get the calander value and using in database
jebin
|
|
|
|
|
I need to build a search function for my site. So there is a single text box for the users to type in their search string. I have been asked that I need to break the user's search string into separate words. So if the user enters: "This is my search query", I need to break it into: "This" "is" "my" "search" "query" and then search for all these words.
- Can I break a string into separet words using SQL?
- How do I remove funny / dangerous characters from the search string?
- If I have to break the search string using a programming language, I would have to run the search query for each word. If I run the search query for each word, How do I combine the search result for the user. For instance, if I search for "my" and find some result, then search for "search" and find some results, how do I display a SINGLE search result to the user.
thanks
|
|
|
|
|
Hi,
I was wondering if there was a better way of reading, updating and writing back text contained in a row in a DataTable in memory.
I need to read the text in a DataRow, and replace one character with another. For example, replace periods with backslashes.
Currently my code does this.
<br />
....<br />
DataTable convertperiodtoslash = new DataTable();<br />
sqlDataAdapter.Fill(convertperiodtoslash);<br />
foreach(DataRow dr in convertperiodtoslash.Rows)<br />
{<br />
foreach(DataColumn col in convertperiodtoslash.Columns)<br />
{<br />
dr[col] = dr[col].ToString().Replace(".", "\\");<br />
}<br />
}
Everything works fine, except that when there are 50,000 rows it takes around 10 mins to process. For smaller number of rows, say around 2000 or so it only takes seconds. So I was wondering if there was a better way of doing this update.. better meaning faster/more efficient code.
I found that the part which slows down the whole process is the writing back into the DataTable. If I write the modified text back into a string array it takes only 5-10 seconds to process even 50,000 rows.
dr[col] = (modified text)
^
|----> bottleneck
Any suggestions/recommendations will be greatly appreciated !!
Thanks in advance!
C# Noob.. 
|
|
|
|
|
Have you tried an update query?
UPDATE tbl SET tbl.Col = "\\"
WHERE tbl.Col Like "%.%";
DEBUGGING : Removing the needles from the haystack.
-- modified at 4:57 Friday 21st October, 2005
|
|
|
|
|
Hi sfdougl ! thanks for responding.
Would, what you suggested work on a DataTable in memory? or how would you do that to a DataTable in memory I guess is my question. I do not want to modify the source (sql) table and be able to update this list and spit it out into an excel file or text file. I also need to display this table in a datagrid to determine extra processing.
Here is my goal..
1. Read sql table..
2. Example row (always a string) "one.two.three"
3. Pull this data from a sql table and store into a DataTable and display in a DataGrid.
4. Output row "one\two\three" by replacing '.' with '\'
5. Also, if need be modify it to "one.two.three.four" - has to be determined at runtime.
6. Output this to a text file. I cannot modify the source.
|
|
|
|
|
What database technology are you using?
Which technology are you using to access the database?
I know ADO (pre .NET). I might have an option for you but without knowing what you’re using kind of hard to know the answer.
It should be possible to pull the data you seek from the db save it to XML and then modify that to suit the users needs (without looping).
Or you could always create a temp table to modify and drop the table after the data has been modified.
(sorry its taken so long to get back to you, I dont use the computer on the weekends  )
DEBUGGING : Removing the needles from the haystack.
-- modified at 4:15 Monday 24th October, 2005
|
|
|
|
|
I am using a SQL server backend, and connecting using the SQLClient dataadapter in .NET (Visual Studio .NET 2003). I don't mind creating a temp table to modify, but again I'm unsure of how to bulk modify an entire table without looping. In fact even now I am only using a temp table which i populate using my select query.
I did consider XML, but didn't get the chance to play with it as yet.
thanks again for responding !
|
|
|
|
|
See my other post about using the Replace command as part of the SQL query. That should do the trick.
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
blackhole077 wrote: 1. Read sql table..
2. Example row (always a string) "one.two.three"
3. Pull this data from a sql table and store into a DataTable and display in a DataGrid.
4. Output row "one\two\three" by replacing '.' with '\'
Simple solution, should work for most RDBMS engines (cant believe I forgot about this one).
If you don’t mind transforming the data when pulling it out of the db use the ‘Replace’ TSQL command. This will not change the underlining data, only its presentation.
SELECT Replace([txt],".","/") AS epr
FROM tbl;
You indicated the display will be determined at runtime. So just do a simple switch or if to determine which way to pull the data. Hows that sound?
Transact-SQL Reference Replace[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
