|
OK. After playing around with the SQLDMO COM objects for a little while, it looks like this project will be pretty easy.
I just started with toying with it using this[^] single page and was easily able to connect to the server, enumerate the databases, tables in them, columns, stored procedures, ... I was even able to Script all of these objects very easily.
On the StoredProcedure objects, there's an Alter method that you can use to give the stored procedure a new script.
OOOOORRRRRR
If you're using SQL Server 2005, you can use SMO to do this instead. I think the SMO version is going to be a bit more documented.
|
|
|
|
|
OK. Thanks for your answer.
Let me try it tonight at home.
Henry de Sousa.
|
|
|
|
|
Here i want to post the complete code i have finally developed this days to do what i posted a few days ago. I have not found the way to compile the script dinamically, now i have to copy and paste the code in the query analizer, when i find the way to compile the script generated i'll post it here, if someone knows the way to do it, it would be great he/she posts it.
<br />
Imports System<br />
Imports System.IO<br />
Imports SQLDMO<br />
<br />
Public Class SQL_DMO<br />
<br />
Dim oSqlServer As New SQLServer<br />
<br />
Private Sub SQL_DMO_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br />
'txt_bd.Text = "Northwind"<br />
'txt_password.Text = "123"<br />
'txt_servidor.Text = "(local)"<br />
'txt_usuario.Text = "sa"<br />
<br />
Dim i As Integer<br />
Dim oNames As SQLDMO.NameList<br />
Dim oSQLApp As SQLDMO.Application<br />
oSQLApp = New SQLDMO.Application<br />
<br />
oNames = oSQLApp.ListAvailableSQLServers()<br />
For i = 1 To oNames.Count<br />
txt_servidor.Items.Add(oNames.Item(i))<br />
Next i<br />
txt_servidor.Focus()<br />
End Sub<br />
<br />
Private Sub CrearTxt()<br />
Dim fs As FileStream = New FileStream("C:\Script.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite)<br />
Dim w As StreamWriter = New StreamWriter(fs)<br />
w.BaseStream.Seek(0, SeekOrigin.End)<br />
w.Write(txt_script.Text)<br />
w.Flush()<br />
w.Close()<br />
End Sub<br />
<br />
Private Sub cmd_conectar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_conectar.Click<br />
'Creamos la conexión<br />
Try<br />
oSqlServer.LoginSecure = False<br />
oSqlServer.Connect(txt_servidor.Text, txt_usuario.Text, txt_password.Text)<br />
'---------------------------------------------------------------------------<br />
Dim oDataBase As Database<br />
'Indicamos la BD a la cual deseamos conectarnos<br />
oDataBase = oSqlServer.Databases.Item(txt_bd.Text)<br />
<br />
'Leemos los procedimientos almacenados de la base de datos<br />
Dim oSP As StoredProcedure2<br />
For Each oSP In oDataBase.StoredProcedures<br />
lst_SPs.Items.Add(oSP.Name)<br />
Next<br />
'---------------------------------<br />
oSqlServer.DisConnect()<br />
cmd_aceptar.Enabled = True<br />
cmd_AT.Enabled = True<br />
cmd_A1.Enabled = True<br />
cmd_QT.Enabled = True<br />
cmd_Q1.Enabled = True<br />
Catch ex As Exception<br />
MsgBox("Ha ocurrido un error en la conexión con el servidor", MsgBoxStyle.Information, "Error")<br />
End Try<br />
End Sub<br />
<br />
Private Sub cmd_aceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_aceptar.Click<br />
If Me.lst_SPsAgr.Items.Count = 0 Then<br />
txt_StatusBar.Text = "NO ha seleccionado nigún procedimiento almacenado"<br />
txt_StatusBar.Refresh()<br />
MsgBox("NO ha seleccionado nigún procedimiento almacenado" & ControlChars.CrLf & "NO se la de de vivo, despiértese!!", MsgBoxStyle.Exclamation, "Atención")<br />
Return<br />
End If<br />
<br />
txt_StatusBar.Text = ""<br />
Try<br />
oSqlServer.LoginSecure = False<br />
oSqlServer.Connect(txt_servidor.Text, txt_usuario.Text, txt_password.Text)<br />
<br />
Dim oDataBase As Database<br />
Dim tot_proc As Integer<br />
tot_proc = 0<br />
'Indicamos la BD a la cual deseamos conectarnos<br />
oDataBase = oSqlServer.Databases.Item(txt_bd.Text)<br />
<br />
'Leemos los procedimientos almacenados de la base de datos<br />
Dim oSP As StoredProcedure2<br />
Dim Script As New System.Text.StringBuilder<br />
Script.Append("")<br />
For Each oSP In oDataBase.StoredProcedures<br />
If lst_SPsAgr.FindStringExact(oSP.Name, 0) <> -1 Then<br />
txt_StatusBar.Text = "Generando script " & Trim(Str(tot_proc + 1)) & " de " & Trim(Str(lst_SPsAgr.Items.Count)) & "... Procedimiento: " & oSP.Name<br />
txt_StatusBar.Refresh()<br />
<br />
Script.Append("/*-- Procedimiento # " & tot_proc + 1 & "--*/" & ControlChars.CrLf)<br />
Script.Append("if exists (select name from dbo.sysobjects where id = object_id(N'[dbo].[" & oSP.Name & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & ControlChars.CrLf & _<br />
" drop procedure [dbo].[" & oSP.Name & "]" & ControlChars.CrLf & _<br />
"GO" & ControlChars.CrLf & ControlChars.CrLf)<br />
Script.Append(oSP.Script())<br />
<br />
tot_proc = tot_proc + 1<br />
End If<br />
Next<br />
txt_StatusBar.Text = "Generando script final para un total de " & Trim(Str(tot_proc)) & " procedimientos, por favor espere..."<br />
txt_StatusBar.Refresh()<br />
<br />
txt_script.Text = Script.ToString<br />
<br />
oSqlServer.DisConnect()<br />
'txt_script.Text = Replace(txt_script.Text, ControlChars.CrLf & "AS", ControlChars.CrLf & "WITH ENCRYPTION" & ControlChars.CrLf & "AS", , , CompareMethod.Text)<br />
'txt_script.Text = Replace(txt_script.Text, "CREATE PROCEDURE", "ALTER PROCEDURE", , , CompareMethod.Text)<br />
cmd_seleccionar.Enabled = True<br />
''''CrearTxt()<br />
txt_StatusBar.Text = "Secuencia de comandos generada exitosamente para un total de " & Trim(Str(lst_SPsAgr.Items.Count)) & " procedimientos"<br />
txt_StatusBar.Refresh()<br />
MsgBox("Se generó exitosamente la secuencia de comandos" & ControlChars.CrLf & "para un total de " & tot_proc & " procedimientos almacenados", MsgBoxStyle.Information, "Información")<br />
Catch ex As Exception<br />
MsgBox("Ha ocurrido un error en la conexión con el servidor", MsgBoxStyle.Information, "Error")<br />
Finally<br />
End Try<br />
<br />
''If lst_SPsAgr.Items.Count > 0 Then<br />
'' Dim i As Integer<br />
'' Dim NombProce As String<br />
'' NombProce = ""<br />
'' i = 0<br />
'' For i = 0 To lst_SPsAgr.Items.Count - 1<br />
'' MsgBox(lst_SPsAgr.Items(i).ToString)<br />
'' Next<br />
''End If<br />
End Sub<br />
<br />
Private Sub cmd_seleccionar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_seleccionar.Click<br />
''txt_script.Select(0, Len(txt_script.Text))<br />
txt_script.SelectAll()<br />
txt_script.Copy()<br />
MsgBox("Se copió la secuencia de comandos en el portapapeles", MsgBoxStyle.Information, "Información")<br />
End Sub<br />
<br />
Private Sub lst_SPs_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lst_SPs.DoubleClick<br />
If lst_SPs.SelectedItems.Count > 0 Then<br />
lst_SPsAgr.Items.Add(lst_SPs.SelectedItem.ToString)<br />
lst_SPs.Items.Remove(lst_SPs.SelectedItem)<br />
End If<br />
End Sub<br />
<br />
Private Sub cmd_AT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_AT.Click<br />
Dim i As Integer<br />
For i = 0 To lst_SPs.Items.Count - 1<br />
lst_SPsAgr.Items.Add(lst_SPs.Items(i).ToString)<br />
Next<br />
lst_SPs.Items.Clear()<br />
End Sub<br />
<br />
Private Sub cmd_A1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_A1.Click<br />
'lst_SPsAgr.Items.Add(lst_SPs.SelectedItem.ToString)<br />
'lst_SPs.Items.Remove(lst_SPs.SelectedItem)<br />
Dim i As Integer<br />
For i = 0 To lst_SPs.SelectedIndices.Count - 1<br />
lst_SPsAgr.Items.Add(lst_SPs.SelectedItems(0))<br />
lst_SPs.Items.RemoveAt(lst_SPs.SelectedIndices(0))<br />
Next<br />
End Sub<br />
<br />
Private Sub cmd_Q1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_Q1.Click<br />
Dim i As Integer<br />
For i = 0 To lst_SPsAgr.SelectedIndices.Count - 1<br />
lst_SPs.Items.Add(lst_SPsAgr.SelectedItems(0))<br />
lst_SPsAgr.Items.RemoveAt(lst_SPsAgr.SelectedIndices(0))<br />
Next<br />
End Sub<br />
<br />
Private Sub lst_SPsAgr_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lst_SPsAgr.DoubleClick<br />
If lst_SPsAgr.SelectedItems.Count > 0 Then<br />
lst_SPs.Items.Add(lst_SPsAgr.SelectedItem.ToString)<br />
lst_SPsAgr.Items.Remove(lst_SPsAgr.SelectedItem)<br />
End If<br />
End Sub<br />
<br />
Private Sub cmd_QT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_QT.Click<br />
Dim i As Integer<br />
For i = 0 To lst_SPsAgr.Items.Count - 1<br />
lst_SPs.Items.Add(lst_SPsAgr.Items(i).ToString)<br />
Next<br />
lst_SPsAgr.Items.Clear()<br />
End Sub<br />
<br />
Protected Overrides Sub Finalize()<br />
oSqlServer.Close()<br />
MyBase.Finalize()<br />
End Sub<br />
<br />
Private Sub cmd_Salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_Salir.Click<br />
Close()<br />
End Sub<br />
<br />
End Class<br />
Henry de Sousa
|
|
|
|
|
Is it possible to change the size of a buttons in DataGridView ButtonColumn. That is, I want the width of the column to be wider than the width of the button in the column.
Thanks
EM
|
|
|
|
|
There is no property to change the size of the button. You'd probably have to make your own DataGridViewButtonColumn, and possibly, DataGridViewButtonCell classes to give you the ability to supply your own properties and drawing code to change the size of the button, without changing the size of the column.
Google for "custom DataGridViewButtonColumn" for examples.
|
|
|
|
|
Hi Friends,
I new to vb.net. In c#.net in GridView i use RowDataBound method and make a check if commandname is Delete i catch the linkbutton(Delete) by FindControl and on Click i give javascript method for Confirm. If Ok it goes to RowDeleting else Cancel.
I am trying something same in vb.net for DataGrid.
The button in DataGrid is
<asp:ButtonColumn Text="??a??af?" ButtonType="PushButton" CommandName="Delete"></asp:ButtonColumn>
Any idea how can Throw a Confirm Message on Click of This button?
Any help would be greatly appreicated
Thanks
Cheers
Menon
|
|
|
|
|
You should be asking this question in the ASP.NET forum.
To get a confirmation dialog, you'd probably be writing the code in JavaScript on the client-side.
|
|
|
|
|
Hi,
I have a mostly C++ background and have a small VB.NET problem and was hoping for some advice from you guru's out there in VB land. I am using VS.NET 2002, incase anyone know of a problem with that version.
I am trying to access functions in a unmanaged DLL from a managed VB application. I can get this to work fine until I use a DLL function that has a string as a parameter. When I call a function with a string as a parameter, although the DLL receives the string correcly, the return value of all future DLL calls is garbage. I have created a small sample application (DLL and VB) to demonstrate the problem. I have posted the code in this post (sorry about that but figured it would be nessecary).
Any help would be awesome?
Thanks in Advance,
Charles
Example follows, in the VB application the first two calls to fnTestNumber are handled fine, but the return value from fnTestString and the last fnTestNumber are random.
VB Application:
Imports System.Runtime.InteropServices<br />
<br />
Module Module1<br />
<br />
<DllImport("TestDLL.DLL", _<br />
CharSet:=CharSet.Ansi, CallingConvention:=CallingConvention.Cdecl)> _<br />
Function fnTestNumber(ByVal iTest As Long) As Long<br />
End Function<br />
<DllImport("TestDLL.DLL", _<br />
CharSet:=CharSet.Ansi, CallingConvention:=CallingConvention.Cdecl)> _<br />
Function fnTestString(<MarshalAs(UnmanagedType.LPStr)> ByVal szTest As String) As Long<br />
End Function<br />
<br />
Sub Main()<br />
Dim retcode As Long<br />
<br />
retcode = fnTestNumber(9876)<br />
retcode = fnTestNumber(7654)<br />
retcode = fnTestString("DATAFILE1.DAT")<br />
retcode = fnTestNumber(1234)<br />
End Sub<br />
<br />
End Module
C++ DLL:
__declspec(dllexport) int fnTestNumber(int iTest)<br />
{<br />
return iTest;<br />
}<br />
<br />
__declspec(dllexport) int fnTestString(const char* szTest)<br />
{<br />
return 42;<br />
}
|
|
|
|
|
Hi,
I am used to use P/Invoke between managed C# and native C, here are some suggestions:
- passing a read-only string from managed to unmanaged works fine, it does not even need
MarshalAs(UnmanagedType.LPStr
- by default managed strings are Unicode and get marshaled as 16-bit char collections;
adding CharSet:=CharSet.Ansi should fix that were appropriate.
- I avoid returning a string, since that would mean the unmanaged world needs to create
an object that can continue to exist in the managed world; instead:
- when needing a string result, I tend to pass a StringBuilder object, with its
initial capacity appropriately set, and also pass that capacity; then the unmanaged
world can treat it as a char buffer of specified length.
- always: your calling conventions must match; since I fail to remember the default, I
always make it explicit on both sides.
- most things you do wrong in P/Invoke result in an exception, quite often corresponding
to an access violation.
Hope this helps.
Luc Pattyn [Forum Guidelines] [My Articles]
This month's tips:
- before you ask a question here, search CodeProject, then Google;
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get;
- use PRE tags to preserve formatting when showing multi-line code snippets.
|
|
|
|
|
In addition to what Luc said, you're using and returning 32-bit numbers in/from your C++ functions, but your VB.NET declarations are Longs. The Long type in VB.NET is a 64-bit signed integer. I think you're actually looking for the Integer type, which is 32-bit signed.
<DllImport("TestDLL.DLL", _
CharSet:=CharSet.Ansi, CallingConvention:=CallingConvention.Cdecl)> _
Function fnTestNumber(ByVal iTest As Integer) As Integer
End Function
<DllImport("TestDLL.DLL", _
CharSet:=CharSet.Ansi, CallingConvention:=CallingConvention.Cdecl)> _
Function fnTestString(ByVal szTest As String) As Integer
End Function
|
|
|
|
|
Right. I missed that one, although I have made similar mistakes myself.
Luc Pattyn [Forum Guidelines] [My Articles]
This month's tips:
- before you ask a question here, search CodeProject, then Google;
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get;
- use PRE tags to preserve formatting when showing multi-line code snippets.
modified on Tuesday, January 15, 2008 1:41:17 PM
|
|
|
|
|
Ah...... I knew it would be something really annoyingly simple. Thank you both very much for the help. You have no idea the hours of frustration that saved me.
Thanks again,
Charles
|
|
|
|
|
hi guys,
i'm having one word document(2003) with many pages [eg.. 100 pages],
each page has different headers informations
and footers information, and different body text.
now what i want is,
To read the content of each page and write it to another file [Chk1.doc].
one more thing while writing to a file,
while writing in another file, i don't want header and footer instead i want the every page header information to be written at the top every page and footer information to be written at the end of every page and body of the page in the middle.
i want to do this task in vb.net
Pls help me.
Thnks,
regards,
P.Prakash
|
|
|
|
|
I haven't done any Office Interop work in the last 10 years, so I can't tell you exactly how to do it. But, the Visual Studio Tools for Office[^] may be of some help to you.
You may also want to Google for "vb.net retrieve text from Word document" that may give you some close examples.
|
|
|
|
|
|
Please help!!
I need to send email to multiple users within a SQL database. How can I create an email class to send mail using SMTP and get all the users from the database to send it to??
I have never worked with VB and would appreciate the assistance...
Thank you in advance
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
Google results for "send eail from vb.net[^]". Depending on the version of VB.nET you're using, the code can change. You either looking for the System.Web.Mail namespace (.NET 1.x) or the System.Net.Mail namespace (.NET 2.0 and above).
There's a bunch of ways to get data out of SQL server. Google for "get data from sql server in vb.net[^]" for some of the various methods.
|
|
|
|
|
Salam,
I've added a button in my form, from which i close the selected tabpage of my tabcontrol , but in some applications i've seen that there are some tabcontrols that comes with a built in X closing button on the right hand side, just like the one in the VS.Net, also i've realized that the tabpages are not normal, they have a good crazy design, i don't know if this is a separate control or an advaned tabcontrol .
Would you help me please ??
Thank you very much
try to be good if you can't be the best
|
|
|
|
|
I don't really know what you're question is, but I'll take a guess at what you're getting at.
This functionality isn't in the standard TabControl in the toolbox. This is either a customized control or a control in a commercially available library.
|
|
|
|
|
Hey,
Just thougt I'd post a message if anyone was wondering about this project. I deleted it late December 2007, mainly because it was taking too much of my time - in hindsight it was a silly thing todo (as well as pretty selfish), but the code is back online now.
It's still a half baked project (I was going to wait until I had finished it, but since it's an open source project, getting opinions on the software before it's finished is one of the major aspects of open source development!) and I still need to write heaps of documentation for it.
It's got the SQL databases as well this time, so you can run it on either SQL Server or Access (the MySQL database is in the works).
Sorry about any confusion with deleting the project!
Cheers,
Tyron
|
|
|
|
|
Please is it possible to convert .net 2.0 code/program made with vs2005 to .net 3.0 in vs2008 to use some features of .net 3.0?
Thanks
phatkin
|
|
|
|
|
prubyholl wrote: Please is it possible to convert .net 2.0 code/program made with vs2005 to .net 3.0 in vs2008 to use some features of .net 3.0?
Open the VS2005 solution in VS2008 and the wizard will convert it for you.
If you want to make use of .NET 3.0 language features, you will have to modify the code yourself.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
You can open the solution/project in VS2008 and it'll convert the solution/project files to the 2008 format, but it will NOT convert the code for you.
The code should run without issues in most cases. But, if you want to use the .NET 3.0, and above, features, you'll have to rewrite the code by hand to use them. There is no conversion tool that will look for patterns in your code and rewrite it for you.
|
|
|
|
|
Good Morning All
Firstly i would love to thank Dave for the Contribution he made into my Programming skills. number two, lets get to the problem.
i have Succeeded coding without any wizard involved.Thanks to Dave, now i have a Datagrid and am updating only one Field "Tshwane_Prediction", now in my Datagrid i have a button that will Search the for records based on an input, well that works fine and i will update one record from the records and click on Save, the Save button will Confidently send me a Update message and Walla!!!! its done, but my Problem here is that the Returned records will all assign one values, that i have assign to one Record. that means there is something wrong with my update Query.
my Update looks like this
<br />
Dim strUpdate As String = "UPDATE Property_mass SET Tshwane_Prediction = @Tshwane_Prediction" & _<br />
" WHERE (Tshwane_Prediction = @Original_Tshwane_Prediction OR @Original_Tshwane_Prediction IS NULL AND Tshwane_Prediction IS NULL) " & _<br />
" ;SELECT Primary_ID, Boundary_area, Attrib_Code, Lis_Key, Func_Key," & _<br />
" Actual_Extent, Improved_Value, Site_Value, Purchase_price," & _<br />
" Purchase_date, prop_id, confidence_score, fin_ls_prediction, " & _<br />
" new_lis_key, new_func_key, Tshwane_Prediction, matchflag," & _<br />
" source, flagtype FROM Property_mass WHERE (Primary_ID = @Original_Primary_ID)" & _<br />
" ; Update Property_Mass " & _<br />
" set Locked = Null " & _<br />
" WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key And Locked=1) "<br />
<br />
If you have any Article or Tutorial that Explain this Step please send me a link.
Thanks you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hill i solved it, but part of it
i should have included the Primary_key Field that will be mapped in my vb.net code to the original source like this
<br />
Dim strUpdate As String = "UPDATE Property_mass SET Tshwane_Prediction = @Tshwane_Prediction" & _<br />
" WHERE (Primary_ID =@Original_Primary_ID) And (Tshwane_Prediction = @Original_Tshwane_Prediction OR @Original_Tshwane_Prediction IS NULL AND Tshwane_Prediction IS NULL) " & _<br />
" ;SELECT Primary_ID, Boundary_area, Attrib_Code, Lis_Key, Func_Key," & _<br />
" Actual_Extent, Improved_Value, Site_Value, Purchase_price," & _<br />
" Purchase_date, prop_id, confidence_score, fin_ls_prediction, " & _<br />
" new_lis_key, new_func_key, Tshwane_Prediction, matchflag," & _<br />
" source, flagtype FROM Property_mass WHERE (Primary_ID = @Original_Primary_ID)" & _<br />
" ; Update Property_Mass " & _<br />
" set Locked = Null " & _<br />
" WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key And Locked=1) "<br />
<br />
<br />
<br />
<br />
<br />
but Below as you can see there is a Query that updates the Lock if the transaction is Successfull. its Supposed to update the Field to Null when ever the update has taken place. The Problem is that am trying to update based on Search criteria used in the select, so my mistake was that i recreated the Parameter and expected it to carry the value of the sql parameter in the Select statement of the Selected command. So i need to know how to Carry that Value So that i can update the records based on it
Thanks
Please help
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
modified on Tuesday, January 15, 2008 5:13:20 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
