db connection through coding

[Zeshan]

hi

1)how can i use a variable in the rs() to go through the fields, e.g

dim a as integer
for a =1 to 3
text1.text=rs(a)
next a

2)also how can i take the name of a table as input n create it
using the sql query

3)i ve "ID" as pri key, but when i run the following, n giving an input for the field "ID" but leaving the field "Name" as blank, an error=(table1.name cannot be a zro length string ) occurs.how do i resolve this err

rs2.Open "insert into zee(ID,Name,Phone) values(' " & MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, MSFlexGrid1.Col) & " ' , '" & MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, MSFlexGrid1.Col + 1) & "')", cn, adOpenStatic, adLockOptimistic


thankyou

[vbcodec]

hi

> 1)how can i use a variable in the rs() to go through the fields, e.g

dim a as integer
for a =1 to 3
text1.text=rs(a)
next a

your question is very off to me, but I consider that you mean how to get values from recordset to get all values so you can use this
Dim A()
Dim B as long
B = rs.recordcount
redim A(B)
rs.movefirst
for I = 1 to B
A(I) = rs.Fields("TableName")

'Or you can also say
A(I) = rs!TableName

'or you can give index name

A(I) = rs.Fields(0)
rs.movenext
next I


>2)also how can i take the name of a table as input n create it
using the sql query
well you can name it or rename it when you're selecting like

SELECT FieldName FROM Table <<<< here the name is FieldName and indexed to zero

SELECT FieldName as Rename FROM Table <<< here we rename it by AS (Alias) to Rename



>3)i ve "ID" as pri key, but when i run the following, n giving an input for the field "ID" but leaving the field "Name" as blank, an error=(table1.name cannot be a zro length string ) occurs.how do i resolve this err

rs2.Open "insert into zee(ID,Name,Phone) values(' " & MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, MSFlexGrid1.Col) & " ' , '" & MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, MSFlexGrid1.Col + 1) & "')", cn, adOpenStatic, adLockOptimistic

this is not really the right way to add new record, what's your database ? Access ? definately you may had set the require to Yes which means it will ask for it and cannot be empty

but when you're adding new fields into a table have to open it from and say

rs2.addnew
rs2!ID = <IDVariable>
rs2!Name = <NameVariable>
rs2!Phone = <PhoneVariable>
rs2.Updatebatch

[twistedthoughts]

Zeshan

For ur Q no. 2, If you are trying to create a Table through Code, you can try the following

'Assuming cn as adodb.connection 
Private Function CreateTable(cTableName as String)
   cn.Execute "CREATE TABLE " & cTableName & ";"
   cn.Execute "ALTER TABLE " & cTableName & " ADD COLUMN Field1 Number, Field2 Text(15);"
End Function

[twistedthoughts]

Zeshan

Q3. First of all, IMHO you should change the way you insert data into your table.

something like the following would be much easier to read & debug:-

strSQL = "INSERT INTO TableName (Field1, Field2) VALUES (" _
               & txtField1.text & ",'" & txtField2.text & "');"
YourConnection.Execute strSQL

[Zeshan]

thanx everyone 4 ur replies. i greatly appriciate it