Command to create a database in sqlite

[mozaharul]

Hi,

A silly asking. I found the command to create a table using sqlite,but what about to
create a database?

The table would be stored in the sqlite_master table.

What is the "full path" of the database/table to connect using Basic4ppc 6.05.


Please help.


regards,

 

[giannimaione]

MyPath = AppPath :' or any path .... MyPath="c:\MyJobs\Alfa\Beta\Gamma"
Connect.New1
Command.New1 ("",Connect.Value)
Connect.Open("Data Source = " & MyPath & "\MyDB.sl3"):' here open or create database named MyDb.sl3
Text="create table if not exists MyTable (Field1 text, Field2 text)"
Command.CommandText=Text
Command.ExecuteNonQuery
'here open or create a simple table

.
.
.
.

of course, use SQLDevice.dll or SQLDesktop.dll

 

[mozaharul]

Hi Gianni Maione,

Thanks a lot for the help.

 

[mozaharul]

Another question:

I created the connection to a database as like:

Sub App_Start
Form1.Show
'Setting connection to the database
con.New1
cmd.New1("",con.Value)
con.Open("data source =" & AppPath & "\kamlapur.sl3")

..........

end sub

I want use a button to save the data from few combo boxes and text boxes to the above database. How shold I write the INSERT command in the click event of the button? I tried some thing like:

Sub msave_Click
If cmbstar.SelectedIndex = -1 Then
Msgbox ("No staturm Selected","Error")
cmbstar.Focus
Else If cmbclu.SelectedIndex= -1 Then
Msgbox ("No Cluster Selected","Error")
cmbclu.Focus
Else If txthhid.Text="" Then
Msgbox ("No Structure Selected","Error")
txthhid.Focus
Else If txtiid.Text="" Then
Msgbox ("No HH No. Selected","Error")
txtiid.Focus
Else If txthhidtn.Text="" Then
Msgbox ("No HH ID Selected","Error")
txthhidtn.Focus
Else
cmd.CommandText=insert into kamlapur.sl3 values(cmbstar.SelectedIndex,cmbclu.SelectedIndex,txthhid.Text,txtiid.Text,txthhidtn.Text)
cmd.ExecuteTable(kamlapur.sl3,500)
'table1.AddRow (cmbstar.SelectedIndex,cmbclu.SelectedIndex,txthhid.Text,txtiid.Text,txthhidtn.Text)
'table1.SaveCSV(dataFile,",",true)
End If
End Sub

But it gives error message.
could you suggest the correct syntax?

your help is highly appreciated.


regards,

 

[giannimaione]

Sub App_Start
Form1.Show
'Setting connection to the database
con.New1
cmd.New1("",con.Value)
con.Open("data source =" & AppPath & "\kamlapur.sl3"):' ok open database
.
.
end sub


Sub msave_Click
.
.
text="insert into NameTable (Field1, Field2) values ("
text=text & MyData1 & "," & MyData2 & ")"
msgbox(text):'only for test
cmd.CommandText=text
cmd.ExecuteNonQuery
End Sub

 

[mozaharul]

Hi Gianni Maione,

I made following changes in the code:

Sub App_Start
Form1.Show
dbpath="C:\Documents and Settings\mozaharul\Desktop"
con.New1
cmd.New1("",con.Value)
con.Open("data source =" & dbpath & "\kamlapur.sl3")
TFields="strat numeric, cluster numeric, struct numeric, hhid numeric,hpid numeric"
Txt="create table if not exists geo (" & TFields & ")"
cmd.CommandText=Txt
cmd.ExecuteNonQuery

end sub

... ...
... ...

then :

Sub msave_Click
If cmbstar.SelectedIndex = -1 Then
Msgbox ("No staturm Selected","Error")
cmbstar.Focus
Else If cmbclu.SelectedIndex= -1 Then
Msgbox ("No Cluster Selected","Error")
cmbclu.Focus
Else If txthhid.Text="" Then
Msgbox ("No Structure Selected","Error")
txthhid.Focus
Else If txtiid.Text="" Then
Msgbox ("No HH No. Selected","Error")
txtiid.Focus
Else If txthhidtn.Text="" Then
Msgbox ("No HH ID Selected","Error")
txthhidtn.Focus
Else
cmd.CommandText = "insert into geo values(cmbstar.Item(cmbstar.SelectedIndex)","cmbclu.Item(cmbclu.SelectedIndex)","txthhid.Text","txtiid.Text","txthhidtn.Text)"
cmd.ExecuteNonQuery
End If
End Sub

Sub Form1_Close
con.Close
End Sub


the code executes without any error,but no data is being saved in the "geo" table in the desktop.

Could you help finding the error in the syntax?


regards,