Compact and Repair Access database

Compact and Repair Access database

step 1 : Add reference of dll

"\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\dao.dll"

Step 2 : In Button Click event write following code

Try
Dim File_Path, compact_file As String
'Original file path that u want to compact
Dim str As String = ConfigurationSettings.AppSettings("dbname")
Dim _connection_string As String = System.Windows.Forms.Application.StartupPath() & "" & str

File_Path = _connection_string
'compact file path, a temp file
compact_file = AppDomain.CurrentDomain.BaseDirectory & "db1.mdb"
'First check the file u want to compact exists or not
If File.Exists(File_Path) Then
Dim db As New dao.DBEngine()
'CompactDatabase has two parameters, creates a copy of
'compact DB at the Destination path
db.CompactDatabase(File_Path, compact_file)
End If
'restore the original file from the compacted file
If File.Exists(compact_file) Then
File.Delete(File_Path)
File.Move(compact_file, File_Path)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try



File_path = " Main Database path"

Compact_file="Temp Database path for compact"

How to display combobox item or Join all combobox item with comma separate in single variable
With combobox1.Items
.Add("T1")
.Add("T2")
.Add("T3")
.Add("T4")
.Add("T5")
End With
Dim str As String
str = ""
For Each obj As Object In combobox1.Items
MsgBox(obj(0).ToString)
str = str & obj(0) & ","
Next
str = Mid(str, 1, str.Length - 1)

CascadingDropDown Method error 500 Solution

I got same error for fill DropDownlist using CascadingDropDown ajax control and web service.

I tried to solve this problem and i got the solution which i share with my friends.


I got the error because of two things

First I did not add attribute.

Second I wrote wrong logic.



Solution

• Add attribute
[System.Web.Script.Services.ScriptService]
. Check your logic in web method

Fill Dropdown list using CascadingDropDown Ajax Control and Web Service

I am taking example of fill dropdown list for state using CascadingDropDown and Web service.

1. Web Service

When we create web service its code file which is located in App_Code folder looks like.

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

‘WebService(Namespace:="http://tempuri.org/")’_

‘WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)’ _

‘Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()’ _

Public Class stateServices

Inherits System.Web.Services.WebService

‘WebMethod()’ _

Public Function HelloWorld() As String

Return "Hello World"

End Function

End Class

Import some other namespace listed below

Imports AjaxControlToolkit

Imports System.Data

Imports System.Collections

Imports System.Collections.Generic

Imports System.Collections.Specialized

After this add one more attribute abow to Class stateServices

System.Web.Script.Services.ScriptService()

Now I am going to create webmothod which return array of type CascadingDropDownNameValue() for fill up drop down list with state name.

‘WebMethod()’ _

Public Function GetState(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()

Dim tempTableAs New DataTable

With tempTable

.Columns.Add("id", GetType(String))

.Columns.Add("state_name", GetType(String))

End With

Dim drow As DataRow

drow = tempTable.NewRow()

drow("id") = "1"

drow("state_name") = "Gujarat"

tempTable.Rows.Add(drow)

drow = tempTable.NewRow()

drow("id") = "2"

drow("state_name") = "Maharastra"

tempTable.Rows.Add(drow)

drow = tempTable.NewRow()

drow("id") = "3"

drow("state_name") = "Rajastan"

tempTable.Rows.Add(drow)

Dim values As New List(Of CascadingDropDownNameValue)

For Each dr As DataRow In tempTable.Rows

values.Add(New CascadingDropDownNameValue(dr("state_name"), dr("id")))

Next

Return values.ToArray()

End Function

After finished all work our webservice file look like

For Vb.net

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports AjaxControlToolkit

Imports System.Data

Imports System.Collections

Imports System.Collections.Generic

Imports System.Collections.Specialized

‘WebService(Namespace:="http://tempuri.org/")’ _

‘WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)’ _

‘Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()’ _

‘System.Web.Script.Services.ScriptService()’ _

Public Class stateServices

Inherits System.Web.Services.WebService

‘WebMethod()’ _

Public Function GetState(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()

Dim tempTableAs New DataTable

With tempTable

.Columns.Add("id", GetType(String))

.Columns.Add("state_name", GetType(String))

End With

Dim drow As DataRow

drow = tempTable.NewRow()

drow("id") = "1"

drow("state_name") = "Gujarat"

tempTable.Rows.Add(drow)

drow = tempTable.NewRow()

drow("id") = "2"

drow("state_name") = "Maharastra"

tempTable.Rows.Add(drow)

drow = tempTable.NewRow()

drow("id") = "3"

drow("state_name") = "Rajastan"

tempTable.Rows.Add(drow)

Dim values As New List(Of CascadingDropDownNameValue)

For Each dr As DataRow In tempTable.Rows

values.Add(New CascadingDropDownNameValue(dr("state_name"), dr("id")))

Next

Return values.ToArray()

End Function

End Class

For C#

using System;

using System.Web;

using System.Collections;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Web.Services;

using System.Web.Services.Protocols;

using AjaxControlToolkit;

using System.Data;

using System.Data.SqlClient;

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.Web.Script.Services.ScriptService]

public class stateServices : System.Web.Services.WebService {

public stateServices () {

//Uncomment the following line if using designed components

//InitializeComponent();

}

[WebMethod]

public CascadingDropDownNameValue[] GetState(string knownCategoryValues, string category)

{

DataTable tempTable=new DataTable();

tempTable.Columns.Add("id", typeof(string));

tempTable.Columns.Add("state_name", typeof(string));

DataRow drow;

drow = tempTable.NewRow();

drow["id"] = "1";

drow["state_name"] = "Gujarat";

tempTable.Rows.Add(drow);

drow = tempTable.NewRow();

drow["id"] = "1";

drow["state_name"] = " Maharastra";

tempTable.Rows.Add(drow);

drow = tempTable.NewRow();

drow["id"] = "1";

drow["state_name"] = " Rajastan";

tempTable.Rows.Add(drow);

List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();

foreach (DataRow dr in tempTable.Rows)

{

values.Add(new CascadingDropDownNameValue (string)dr["state_name"], (string)dr["id"]));

}

return values.ToArray();

}

2. web Form

Add ScriptManager.

Add Dropdownlist rename it with ddstate.

Add CascadingDropDown Ajax Control rename it with cddstate.

Set TargetControlID Property of cddstate to ddstate

Set cddstate(CascadingDropDownproperty) properties for ddstate in its property window

BehaviorID property to cddstate

Category property to State

LoadingText property to Please wait ...........

PromptText property to Select State ............

ServiceMethod property to GetState

ServicePath property to stateServices.asmx

Start debugging application.

I use ‘ quote for attribute which is highlighted to use code replace starting quote with

'Greate then sign' and ending quote with 'Less then sign'